Pass variable between sub routines

Hi,

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. However, the response.write statements in the fillorder routine do not display, nor does the table get populated. I think the first problem is that I may not be passing the "highest" variable correctly.

I know the code is probably hack, but I would appreciate help/advice.

Thanks in advance.

Tom

 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 highest FROM order_header"
 Dim CmdMax As New SqlCommand(strMax, objMaxConn)
 If numOrders = 0 Then
 highest = numOrders + 1
 End If
 If numOrders > 0 Then
 objMaxConn.Open() 'open connection
 'run SQL command
 CmdMax.ExecuteNonQuery()
 objMaxConn.Close() 'close connection
 End If
 Response.Write(highest)
 FillOrder(highest)
 End Sub
 'populate order_detail table
 Public Sub FillOrder(ByVal ordNum As Int32)
 'findMax()
 Const strFillOrdConn As String = "data source=ENVISAGENT1;initial catalog=tsproductsSQL3;integrated security=SSPI;persist security info=False;workstation id=ENVISAGENT1;packet size=4096"
 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 order_num,detail_num,product_num,quantity,line_item_price INTO order_detail values (ordNum,detNum,pnum,quan,price)"
 Dim CmdFillOrd As New SqlCommand(strFillOrdSQL, objFillOrdConn)
 objFillOrdConn.Open()
 ' Iterate through all rows within shopping cart list
 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
 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.ExecuteNonQuery()
 Next
 objFillOrdConn.Close()

Hi Tom,
There is no embeded variables in ADO.NET. You have to create command with parameters and use them. Alter your code this way (check types of parameters):

' ...
strFillOrdSQL = "INSERT order_num,detail_num,product_num,quantity,line_item_price INTO order_detail 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
 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
 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

Thank-you. I'll give it a try.
Ok I finally got around to trying the code - it looks like it may work, but again, nothing happens after the findMax sub routine listed above. It is as if it isn't even invoking. Also, I took the response.write("Highest = " & highest) statement out, and 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 routing apparantly doing nothing?

Thanks

Tom
There are only two possible explanations for persisting results of response.write:
1. Page is cached and not executed at all
2. You are running wrong version of page
How would I determine which one? I've tried deleting cache files to no avail.
Try to change something visual on page (add some text for example) and see if you receive changes when page is served.
I did all of that.

If you look at the connection strings in the code above, are they creating a cache that persists?

I created new pages and copied the code over to them. I changed the connection strings to "server=localhost;database=tsproductssql3;uid=sa;pwd=sa" and ran it. I got rid of the persistent response.write, and was able to display all of the others. However, I tried again, and got a database (primary key) violation which I have to sort out to see if the problems persist. I'll re-post then.

Thanks for your replies.

Tom
OK, cleared up the persist problem.

If you (or someone) could tell me if the original connection string (above) that I had would have created this cache & persist problem, that would be great. I'm still getting a lot of roadrash fumbling my way through my first ASP.NET project.

Thanks

Tom