ASP Session value

Question

Hi there,

I'm setting a Session of MM_CustomerID in my code and then further down the page I need to insert the value of that session into a table. But each time I try to do this it comes up with an Invalid column name 'varCustomerID'.

At the top of the page I have this code;

<% 
set rscustomerid = Server.CreateObject("ADODB.Recordset")
rscustomerid.ActiveConnection = CmdAddCustomer.ActiveConnection
rscustomerid.Source = "SELECT @@IDENTITY as MaxCustomersID  FROM Customers"
rscustomerid.CursorLocation = 2
rscustomerid.LockType = 3
rscustomerid.Open()
Session("MM_CustomerID")=rscustomerid("MaxCustomersID")
Session("MM_UserAuthorization") = "5"
%>

Then further down, i'm trying to set a variable of varCustomerID to be equal to the MM_CustomerID session;

<%
varCustomerID = Session("MM_CustomerID")
%>

And then try inserting the value of that variable varCustomerID into the Orders table as follows;

<% 
'Insert record into Orders recordset when form is submitted
'and store the unique OrderID
'Version Date: 09 August 2009
set CmdAddOrder = Server.CreateObject("ADODB.Command")
CmdAddOrder.ActiveConnection = MM_dbconn_STRING
CmdAddOrder.CommandText = "INSERT INTO Orders (OrderCustomer,OrderGrandTotal,OrderStatus) VALUES (varCustomerID,0.00,3)"
CmdAddOrder.CommandType = 1
CmdAddOrder.CommandTimeout = 0
CmdAddOrder.Prepared = true
CmdAddOrder.Execute()
%>

I wondered if anyone might be able to help? Perhaps there's an easier way of just inserting the session value into the table, instead of creating a variable for it?

Thanks.

Answer 1

You need to change the sql command text to:

"INSERT INTO Orders (OrderCustomer,OrderGrandTotal,OrderStatus) VALUES (" + varCustomerID + ",0.00,3)"

However you could be vulnerable to SQL injection... really you should parametrise this query:

http://aspnet101.com/aspnet101/tutorials.aspx?id=1

Answer 2

You need to cast varCustomerID to a string for the query. Slight change to Paul's answer should get you working but like he says you need to be careful of injection attacks.

"INSERT INTO Orders (OrderCustomer,OrderGrandTotal,OrderStatus) VALUES (" & varCustomerID & ",0.00,3)"