Private Sub bsa_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bsa.Click
If tpl.Items.Count > 0 Then
If MessageBox.Show("Do you want to save this Order?", "Confirm", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
tr = Conn.BeginTransaction()
sb.Remove(0, sb.Length)
sb.Append("INSERT INTO Orders (CustomerID,OrderDate,Total)")
sb.Append(" VALUES ('" & tcid.Text & "','" & DateTime.Today & "','" & tt.Text & "')")
Dim sqlSave As String = sb.ToString()
Dim com = Conn.CreateCommand()
With com
.CommandType = CommandType.Text
.CommandText = sqlSave
.Connection = Conn
.Transaction = tr
.ExecuteNonQuery()
End With
sb.Remove(0, sb.Length)
sb.Append("SELECT TOP 1 OrderID FROM Orders")
sb.Append(" ORDER BY OrderID DESC")
sqlSave = sb.ToString()
Dim LastOrderID As Integer
With com
.CommandType = CommandType.Text
.CommandText = sqlSave
.Connection = Conn
dr = .ExecuteReader()
If dr.HasRows Then
dr.Read()
LastOrderID = dr.GetInt32(dr.GetOrdinal("OrderID"))
Else
LastOrderID = 10000
End If
End With
dr.Close()
sqlSave = sb.ToString()
With com
.CommandText = sqlSave
.ExecuteNonQuery()
End With
Next
tr.Commit()
sb.Remove(0, sb.Length)
sb.Append("SELECT OrderID")
sb.Append(" FROM Orders")
sb.Append(" WHERE (" & LastOrderID & ")")
Dim sqlOrder As String = sb.ToString
Dim dtOrder As DataTable
With com
.CommandType = CommandType.Text
.CommandText = sqlOrder
.Connection = Conn
dr = .ExecuteReader()
If dr.HasRows Then
dtOrder = New DataTable()
dtOrder.Load(dr)
MessageBox.Show("Save Order done.", "Result", MessageBoxButtons.OK, MessageBoxIcon.Information)
End If
End With
tpl.Clear()
ClearProductData()
ClearCutomerData()
tt.Text = "0"
tpid.Focus()
End If
End If
End Sub
For i As Integer = 0 To Listview1.Items.Count - 1
Dim Upstock As String = ""
Upstock = ("Update Products")
Upstock &= (" set Stock = Stock - " & Listview1.Items(i).SubItems(4).Text & " where ProductID = @ProID")
With cm
.CommandType = CommandType.Text
.CommandText = Upstock
.Connection = cn
.Parameters.Clear()
.Parameters.Add("@ProID", SqlDbType.NVarChar, 10).Value = Listview1.Items(i).SubItems(1).Text
.ExecuteNonQuery()
End With
Next
Load balance : Server 02
