ExecuteScalar --> How To Get the OrderID(Identity) from a table to another Table ?

I am new to asp.net and studying on book.. currently i am stuck with a problem which not understand what is it !! Can anyone help me ?? I trying a shopping cart "Check Out" method, and when i am done the process.. My order_lines Table can update the OrderID which just generated !! What wrong with the statement ??

Protected Sub Wizard1_FinishButtonClick(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.WizardNavigationEventArgs) Handles Wizard1.FinishButtonClick
' Insert the order and order lines into the database
Dim conn As SqlConnection = Nothing
Dim trans As SqlTransaction = Nothing
Dim cmd As SqlCommand

Try
conn = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
conn.Open()
trans = conn.BeginTransaction
cmd = New SqlCommand()
cmd.Connection = conn
cmd.Transaction = trans

' set the order details
cmd.CommandText = "INSERT INTO Orders(MemberName, OrderDate, Name, Address, City, State, PostCode, Country, Total) VALUES (@.MemberName, @.OrderDate, @.Name, @.Address, @.City,@.State, @.PostCode, @.Country, @.Total)"
cmd.Parameters.Add("@.MemberName", Data.SqlDbType.VarChar, 50)
cmd.Parameters.Add("@.OrderDate", Data.SqlDbType.DateTime)
cmd.Parameters.Add("@.Name", Data.SqlDbType.VarChar, 50)
cmd.Parameters.Add("@.Address", Data.SqlDbType.VarChar, 255)
cmd.Parameters.Add("@.City", Data.SqlDbType.VarChar, 50)
cmd.Parameters.Add("@.State", SqlDbType.VarChar, 50)
cmd.Parameters.Add("@.PostCode", Data.SqlDbType.VarChar, 15)
cmd.Parameters.Add("@.Country", Data.SqlDbType.VarChar, 50)
cmd.Parameters.Add("@.Total", Data.SqlDbType.Money)

cmd.Parameters("@.MemberName").Value = User.Identity.Name
cmd.Parameters("@.OrderDate").Value = DateTime.Now()
cmd.Parameters("@.Name").Value = CType(Wizard1.FindControl("txtName"), TextBox).Text
cmd.Parameters("@.Address").Value = CType(Wizard1.FindControl("txtAddress"), TextBox).Text
cmd.Parameters("@.City").Value = CType(Wizard1.FindControl("txtCity"), TextBox).Text
cmd.Parameters("@.State").Value = CType(Wizard1.FindControl("txtState"), TextBox).Text
cmd.Parameters("@.PostCode").Value = CType(Wizard1.FindControl("txtPostCode"), TextBox).Text
cmd.Parameters("@.Country").Value = CType(Wizard1.FindControl("txtCountry"), TextBox).Text
cmd.Parameters("@.Total").Value = Profile.Basket.Total

Dim OrderID As Integer
OrderID = Convert.ToInt32(cmd.ExecuteScalar()) <-- Is it wrong or need to add wat ?
' change the query and parameters for the order lines
cmd.CommandText = "INSERT INTO OrderLines(OrderID, ProductID,Quantity, Price) VALUES (@.OrderID, @.ProductID, @.Quantity, @.Price)"
cmd.Parameters.Clear()
cmd.Parameters.Add("@.OrderID", Data.SqlDbType.Int)
cmd.Parameters.Add("@.ProductID", Data.SqlDbType.Int)
cmd.Parameters.Add("@.Quantity", Data.SqlDbType.Int)
cmd.Parameters.Add("@.Price", Data.SqlDbType.Money)
cmd.Parameters("@.OrderID").Value = OrderID

For Each item As CartItem In Profile.Basket.Items
cmd.Parameters("@.ProductID").Value = item.ProductID
cmd.Parameters("@.Quantity").Value = item.Quantity
cmd.Parameters("@.Price").Value = item.UnitPrice
cmd.ExecuteNonQuery()
Next
' commit the transaction
trans.Commit()
Catch SqlEx As SqlException
' some form of error - rollback the transaction
' and rethrow the exception
If trans IsNot Nothing Then
trans.Rollback()
End If
' Log the exception
Throw

Finally
If conn IsNot Nothing Then
conn.Close()
End If
End Try
' we will only reach here if the order has been created successfully
' so clear the cart
Profile.Basket.Items.Clear()
End Sub

Hi, there is no SELECT query in your SQL statement, so ExecuteScalar has nothing to return. Try this instead:

' set the order details
cmd.CommandText = "INSERT INTOOrders(MemberName, OrderDate, Name, Address, City, State, PostCode,Country, Total) VALUES (@.MemberName, @.OrderDate, @.Name, @.Address,@.City,@.State, @.PostCode, @.Country, @.Total; SELECT SCOPE_IDENTITY())"

|||

Thanks tmorton for your reply.. i try with ur suggested code can return an error

Incorrect syntax near ';'.
Incorrect syntax near ')'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Incorrect syntax near ';'.
Incorrect syntax near ')'.

Line 84: End If
Line 85: ' Log the exception
Line 86: Throw
Line 87:
Line 88: Finally

I am noob for this case, is it other way to retrieve the new data identity ?

|||cmd.CommandText = "INSERT INTO Orders(MemberName, OrderDate, Name, Address, City, State, PostCode, Country, Total) VALUES (@.MemberName, @.OrderDate, @.Name, @.Address, @.City,@.State, @.PostCode, @.Country, @.Total); SELECT SCOPE_IDENTITY()"|||

Motley wrote:

cmd.CommandText = "INSERT INTO Orders(MemberName, OrderDate, Name, Address, City, State, PostCode, Country, Total) VALUES (@.MemberName, @.OrderDate, @.Name, @.Address, @.City,@.State, @.PostCode, @.Country, @.Total); SELECT SCOPE_IDENTITY()"

Thanks for fixing my keying error, Motley :-)