SQL Statement and Table connection issues

[irkahs]

Hello all,

I am using an SQL select statement to store the result into a variable. This variable then needs to get into a table. I am having issues with it. Kindly help. I am providing below the code.

'First table variables
Dim sql_insert, con, data_source

'Second table variables
Dim sql_insert2, con2, data_source2

'Opens connection for writing to Purchases
data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source = "&_
Server.MapPath("orders.mdb")

'Opens connection for writing to Suppliers
data_source2 = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source = "&_
Server.MapPath("orders.mdb")

'Statement that writes data to Supplier table (address comes from a form from before. Not an issue so far)
sql_insert2 = "insert into suppliers (address) values ('" & address & "') "
Set con2 = Server.CreateObject("ADODB.Connection")
con2.open(data_source2)
con2.Execute(sql_insert2)

'My trouble begins here. I need to now find the last value added into the Supplier table (from above) and make it a value into my next insertion of record into the Purchases table. And here is my logic that does not work.

sql_insert2 = "SELECT max(supplierID) FROM Supplier"
Set con2 = Server.CreateObject("ADODB.Connection")
con2.open(data_source2)

'this message prints back the message 'SELECT max(supplierID) from Supplier instead of the last value written. Supplier ID is an autonumber in the access table.So I need to capture this value into a local variable and write it to the Purchase table.

Response.Write sql_insert2

'This will write the new entry into Purchase Table along with latest Supplier ID which is not working.

sql_insert = "insert into purchase (username, description,price,qty,budgetline,supplierID)) values ('" & username & "', '" & description & "', '" & price & "', '" & qty & "', '" & budgetline & "', '" & supplierID & "') "

Set con = Server.CreateObject("ADODB.Connection")
con.open(data_source)
con.Execute(sql_insert)

Response.Write ("Record has been added successfully.")

con.Close
Set con=Nothing

%>

[shweta123]

Hi,

Response.Write sql_insert2
This statement will print sql statement and not the value of last SupplierID.

If you want to get the Max Supplier Id you should write

Dim rs
rs =server.CreateObject("Adodb.Recordset")
sql_insert2 = "SELECT max(supplierID) FROM Supplier"
set rs = con.Execute(sql_insert2)
''''''This will print Supplier ID
Response.write(rs(0))
rs.close

[markrawlingson]

There are two problems here...

1. Where is your recordset object? All you are doing here is setting your SQL statement into a variable, then opening a connection to your database. You're not passing your SQL statement into the actual database itself therefore no information is returned - you've just got another open connection object.

sql_insert2 = "SELECT max(supplierID) FROM Supplier"
Set con2 = Server.CreateObject("ADODB.Connection")
con2.open(data_source2)

2. You're setting the variable SupplierID into an insert statement to insert the value of the SupplierID variable into the database. However, You're not setting the value of supplierID. Therefore, SupplierID is NULL - so it gets set into your database column as a null or empty value. Even worse is if that particular column in your database does not allow null values to be entered - it will throw an error.

sql_insert = "insert into purchase (username, description,price,qty,budgetline,supplierID)) values ('" & username & "', '" & description & "', '" & price & "', '" & qty & "', '" & budgetline & "', '" & supplierID & "') "

In addition, and this is merely a suggestion.. You don't need to have two identical connection objects open on your page, and you don't need to open them several times. It can get confusing pretty quickly, and each open object just eats up more resources. You can just open one connection object at the top of your page and pass that same connection object into as many recordset objects or execution statements as you like.


Check the code below - it should solve your problem.

<%
'Set your datasource and open your connection object.
data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source = "&_
Server.MapPath("orders.mdb")
'Create the object and open it. We now have an open connection object that we can use as many times as we like.
Set con = Server.CreateObject("ADODB.Connection")
con.open(data_source)
 
'Set an SQL insert statement and execute it against the connection object.
sql_insert = "insert into suppliers (address) values ('" & address & "') "
con.Execute(sql_insert)
 
'Set an SQL statement to grab the MAX value of supplierID.
sql_insert = "SELECT max(supplierID) FROM Supplier"
'Create a recordset object which will hold the information that we are trying to retrieve from the database
Set rs = Server.CreateObject("ADODB.RecordSet")
'We open the recordset, passing the SQL statement and the connection string to it so it knows what to return.
rs.Open sql_insert, con, 3, 3
 
'We pull out the field we want and set it into a variable.
SupplierID = rs("SupplierID")
 
'We close and cleanup our recordset object because we no longer require it, we have the information we were looking for.
 
rs.Close
Set rs = nothing
'Insert a new record into the purchase table with the values that we have.
sql_insert = "insert into purchase (username, description,price,qty,budgetline,supplierID)) values ('" & username & "', '" & description & "', '" & price & "', '" & qty & "', '" & budgetline & "', '" & supplierID & "') "
con.execute(sql_insert)
 
'close and cleanup the connection object
con.close
Set con = nothing
%>
 

[irkahs]

Thanks Shweta and Mark.

Shweta,
Thank you for the rs(0) concept. I'd have never guessed that one. Works just fine.

Mark,
I actually did do something similar to what you wrote. Now it works perfectly.

Your timely help is truly appreciated.

Cheers!