I'm trying to write some code that will create an order number by searching through an order_detail table for the highest numbered order number. Once found, I would like to pass this number to the FillOrder sub routine, which inserts it into the order_detail table. It looks like the findMax routine works, since the response.write statement a the end of it prints out the correct number, but nothing happens after the findMax sub routine listed above. It is as if it isn't even invoking. Also, if I take the response.write("Order Num = " & ordNum) statement out, it still displays the text when the button is clicked. Why does this persist even though the statement is out? Does it also have something to do with the FillOrder sub routine apparantly doing nothing?
However, the response.write statements in the fillorder routine do not display, nor does the table get populated, either.
(by the way, I've tried passing variables b/w subs, and changed it to a global variable in this case. Either way, it does the same thing)
In short - I'm lost.
Thanks for your insights.
Tom
<code>
Public Sub btnCheckout_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCheckout.Click
Dim strCount As String
Dim strChkSQL As String
' Dim numOrders As Int32
Const strChkConn As String = "data source=ENVISAGENT1;initial catalog=tsproductsSQL3;integrated security=SSPI;persist security info=False;workstation id=ENVISAGENT1;packet size=4096"
Dim objChkConn As New SqlConnection(strChkConn)
Dim dsCart As DataSet
Dim daCart As SqlDataAdapter
dsCart = New DataSet()
'count the number of rows in cart
'for each row, copy the info into order table
'create a unique order number
'search for the highest number in the order_header table and add 1 to it
'count the number of rows in the order_header table
'loop through order_header to the end of table and find the highest.
'add 1 to it.
Dim dsCheckout As DataSet
dsCheckout = New DataSet()
strCount = "SELECT COUNT(*)as numOrders FROM order_header"
Dim Cmd As New SqlCommand(strCount, objChkConn)
objChkConn.Open() 'open connection
'run SQL command
Cmd.ExecuteNonQuery()
objChkConn.Close() 'close connection
txtTestMessage.Text = ordNum
findMax()
End Sub
Public Sub findMax()
Dim highest As Int32
Dim strMax As String
Const strMaxConn As String = "data source=ENVISAGENT1;initial catalog=tsproductsSQL3;integrated security=SSPI;persist security info=False;workstation id=ENVISAGENT1;packet size=4096"
Dim objMaxConn As New SqlConnection(strMaxConn)
Dim dsOrderHead As DataSet
Dim daOrderHead As SqlDataAdapter
dsOrderHead = New DataSet()
strMax = "SELECT MAX(order_num) as ordNum FROM order_header"
Dim CmdMax As New SqlCommand(strMax, objMaxConn)
If numOrders = 0 Then
ordNum = ordNum + 1
FillOrder()
End If
If numOrders > 0 Then
objMaxConn.Open() 'open connection
'run SQL command
CmdMax.ExecuteNonQuery()
objMaxConn.Close() 'close connection
FillOrder()
End If
Response.Write ("Order Num = " & ordNum)
End Sub
'populate order_detail table
Public Sub FillOrder()
'findMax()
Response.Write("order num " & ordNum)
Const strFillOrdConn As String = "server=localhost;database=tsproductssql3;uid=sa;pwd=sa"
Dim objFillOrdConn As New SqlConnection(strFillOrdConn)
Dim daFillOrd As SqlDataAdapter
Dim strFillOrdSQL As String
Dim detNum As Int32 = 0
Response.Write("DetailNum " & detNum)
Response.Write("PRoduct " & grdCart.Items(1).Cells(1).Text)
'********
strFillOrdSQL = "INSERT INTO order_detail(order_num,detail_num,product_num,quantity,line_item_price) values (@ordNum,@detNum,@pnum,@quan,@price)"
Dim CmdFillOrd As New SqlCommand(strFillOrdSQL, objFillOrdConn)
CmdFillOrd.Parameters.Add(New SqlParameter("@ordNum", SqlDbType.Int))
CmdFillOrd.Parameters.Add(New SqlParameter("@detNum", SqlDbType.Int))
CmdFillOrd.Parameters.Add(New SqlParameter("@pnum", SqlDbType.Int))
CmdFillOrd.Parameters.Add(New SqlParameter("@quan", SqlDbType.Int))
CmdFillOrd.Parameters.Add(New SqlParameter("@price", SqlDbType.Decimal))
CmdFillOrd.Parameters(0).Value = ordNum
objFillOrdConn.Open()
' Iterate through all rows within shopping cart list
Response.Write("Cart Items= " & grdCart.Items.Count)
Dim i As Integer
For i = 0 To grdCart.Items.Count - 1
'for each item in cart,
'insert current order #, detail#,product, quantity & price
'into order_detail
detNum = detNum + 1
Response.Write("detNum = " & detNum)
CmdFillOrd.Parameters(1).Value = detNum
Dim pnum As Int32 = Convert.ToInt32(grdCart.Items(i).Cells(1).Text)
Dim price As Decimal = Convert.ToDecimal(grdCart.Items(i).Cells(3).Text)
Dim quan As Int32 = Convert.ToInt32(grdCart.Items(i).Cells(4).Text)
CmdFillOrd.Parameters(2).Value = pnum
CmdFillOrd.Parameters(3).Value = quan
CmdFillOrd.Parameters(4).Value = price
CmdFillOrd.ExecuteNonQuery()
Next
'********
objFillOrdConn.Close()
</code>
However, the response.write statements in the fillorder routine do not display, nor does the table get populated, either.
(by the way, I've tried passing variables b/w subs, and changed it to a global variable in this case. Either way, it does the same thing)
In short - I'm lost.
Thanks for your insights.
Tom
<code>
Public Sub btnCheckout_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCheckout.Click
Dim strCount As String
Dim strChkSQL As String
' Dim numOrders As Int32
Const strChkConn As String = "data source=ENVISAGENT1;initial catalog=tsproductsSQL3;integrated security=SSPI;persist security info=False;workstation id=ENVISAGENT1;packet size=4096"
Dim objChkConn As New SqlConnection(strChkConn)
Dim dsCart As DataSet
Dim daCart As SqlDataAdapter
dsCart = New DataSet()
'count the number of rows in cart
'for each row, copy the info into order table
'create a unique order number
'search for the highest number in the order_header table and add 1 to it
'count the number of rows in the order_header table
'loop through order_header to the end of table and find the highest.
'add 1 to it.
Dim dsCheckout As DataSet
dsCheckout = New DataSet()
strCount = "SELECT COUNT(*)as numOrders FROM order_header"
Dim Cmd As New SqlCommand(strCount, objChkConn)
objChkConn.Open() 'open connection
'run SQL command
Cmd.ExecuteNonQuery()
objChkConn.Close() 'close connection
txtTestMessage.Text = ordNum
findMax()
End Sub
Public Sub findMax()
Dim highest As Int32
Dim strMax As String
Const strMaxConn As String = "data source=ENVISAGENT1;initial catalog=tsproductsSQL3;integrated security=SSPI;persist security info=False;workstation id=ENVISAGENT1;packet size=4096"
Dim objMaxConn As New SqlConnection(strMaxConn)
Dim dsOrderHead As DataSet
Dim daOrderHead As SqlDataAdapter
dsOrderHead = New DataSet()
strMax = "SELECT MAX(order_num) as ordNum FROM order_header"
Dim CmdMax As New SqlCommand(strMax, objMaxConn)
If numOrders = 0 Then
ordNum = ordNum + 1
FillOrder()
End If
If numOrders > 0 Then
objMaxConn.Open() 'open connection
'run SQL command
CmdMax.ExecuteNonQuery()
objMaxConn.Close() 'close connection
FillOrder()
End If
Response.Write ("Order Num = " & ordNum)
End Sub
'populate order_detail table
Public Sub FillOrder()
'findMax()
Response.Write("order num " & ordNum)
Const strFillOrdConn As String = "server=localhost;database=tsproductssql3;uid=sa;pwd=sa"
Dim objFillOrdConn As New SqlConnection(strFillOrdConn)
Dim daFillOrd As SqlDataAdapter
Dim strFillOrdSQL As String
Dim detNum As Int32 = 0
Response.Write("DetailNum " & detNum)
Response.Write("PRoduct " & grdCart.Items(1).Cells(1).Text)
'********
strFillOrdSQL = "INSERT INTO order_detail(order_num,detail_num,product_num,quantity,line_item_price) values (@ordNum,@detNum,@pnum,@quan,@price)"
Dim CmdFillOrd As New SqlCommand(strFillOrdSQL, objFillOrdConn)
CmdFillOrd.Parameters.Add(New SqlParameter("@ordNum", SqlDbType.Int))
CmdFillOrd.Parameters.Add(New SqlParameter("@detNum", SqlDbType.Int))
CmdFillOrd.Parameters.Add(New SqlParameter("@pnum", SqlDbType.Int))
CmdFillOrd.Parameters.Add(New SqlParameter("@quan", SqlDbType.Int))
CmdFillOrd.Parameters.Add(New SqlParameter("@price", SqlDbType.Decimal))
CmdFillOrd.Parameters(0).Value = ordNum
objFillOrdConn.Open()
' Iterate through all rows within shopping cart list
Response.Write("Cart Items= " & grdCart.Items.Count)
Dim i As Integer
For i = 0 To grdCart.Items.Count - 1
'for each item in cart,
'insert current order #, detail#,product, quantity & price
'into order_detail
detNum = detNum + 1
Response.Write("detNum = " & detNum)
CmdFillOrd.Parameters(1).Value = detNum
Dim pnum As Int32 = Convert.ToInt32(grdCart.Items(i).Cells(1).Text)
Dim price As Decimal = Convert.ToDecimal(grdCart.Items(i).Cells(3).Text)
Dim quan As Int32 = Convert.ToInt32(grdCart.Items(i).Cells(4).Text)
CmdFillOrd.Parameters(2).Value = pnum
CmdFillOrd.Parameters(3).Value = quan
CmdFillOrd.Parameters(4).Value = price
CmdFillOrd.ExecuteNonQuery()
Next
'********
objFillOrdConn.Close()
</code>