refresh the page

[jojay]

Use the code I gave you, and add your own SQL query there to do the insert.  Don't use the rs.addnew and rs.update and things like that.  You don't need them.

<{POST_SNAPBACK}>

I am not inserting any data in the last level, only displaying what I just inserted in result.asp.Scenerio:(I am creating a form to input data to send to database. form.asp takes the input of data and sends to result.asp and that page inserts the data and creates a variable to pass to the next page which is resultpage.asp and display the info).

form.aspformfields....submit button-------------------------result.asprequest.form formfieldsconnectioninsert into the databasecreate variable for id to store the value of id from this page to pass to the next pagerecordset and connection closeresponse.redirect to resultpage.asp-------------------------------resultpage.asprequest.querystring idformfields display

And the code for the last part is:

result.aspnew_id = rs.fields.item("ID").valueresultpage.aspID = request.querystring("new_id")

I guess I have to create another connection and recordset to retrieve the info from database. will try.

[justsomeguy]

I know what you are trying to do, I'm trying to tell you how to do it. This is the code I gave earlier:

Set rs = Server.createobject("Adodb.Recordset")rs.ActiveConnection = your_connection_stringrs.open "INSERT INTO Cform ..."rs.open "SELECT MAX(CID) as CID FROM Cform"new_id = rs.fields.item("CID").valuers.close

This is the code you gave:

rs.addnewrs.field1rs.field2rs.updatenew_id = rs.fields.item("ID").value

Why do you have all the rs methods being called? What are you trying to do with them? You don't need to use addnew, and you don't need to use update. And a statement like 'rs.field1' doesn't do anything to begin with. The reason new_id is blank is because you are doing extra things between the code I pasted and the code you pasted. Use the code I pasted, write your own SQL insert statement (I don't have the table structure), and watch it work.

[jojay]

I know what you are trying to do, I'm trying to tell you how to do it.  This is the code I gave earlier:

Set rs = Server.createobject("Adodb.Recordset")rs.ActiveConnection = your_connection_stringrs.open "INSERT INTO Cform ..."rs.open "SELECT MAX(CID) as CID FROM Cform"new_id = rs.fields.item("CID").valuers.close

This is the code you gave:

rs.addnewrs.field1rs.field2rs.updatenew_id = rs.fields.item("ID").value

Why do you have all the rs methods being called?  What are you trying to do with them?  You don't need to use addnew, and you don't need to use update.  And a statement like 'rs.field1' doesn't do anything to begin with.  The reason new_id is blank is because you are doing extra things between the code I pasted and the code you pasted.  Use the code I pasted, write your own SQL insert statement (I don't have the table structure), and watch it work.

<{POST_SNAPBACK}>

rs.addnewrs.field1rs.field2rs.update

is another way of inserting data to the database rather update kind. I am more comfortable using addnew method becoz there is no much extra strings to add and it is clean especially sending data to the database. Anyway, will try you way of insert method.

[justsomeguy]

I'm just a little confused by things like this:

Set rs = con.execute ("Select   Max(CID) from Cform")rs.AddNewrs("Cssn") = Cssnrs("Cjobno") =  Cjobnors.update

What does it mean logically to execute a select statement to create the recordset, and then start using methods to insert the data? You can just change the select statement to an insert statement, then you don't need the next 4 lines of code. You also don't need to set the recordset to the value, an insert query does not return anything.

[jojay]

I'm just a little confused by things like this:

Set rs = con.execute ("Select   Max(CID) from Cform")rs.AddNewrs("Cssn") = Cssnrs("Cjobno") =  Cjobnors.update

What does it mean logically to execute a select statement to create the recordset, and then start using methods to insert the data?  You can just change the select statement to an insert statement, then you don't need the next 4 lines of code.  You also don't need to set the recordset to the value, an insert query does not return anything.

<{POST_SNAPBACK}>

Exactly. First I also had the same question but it is not allowing me to send the info to the databse and moreover getting an error such as "Item not found in ......" and if I do a select statement with all the field names it is fine. Anyway, I tried your way:Here is the problem. I am getting an error.

ADODB.Recordset error '800a0e7d' The connection cannot be used to perform this operation. It is either closed or invalid in this context. result.asp, line 38  ' it is pointing to the insert statement.

Here is my code:

Set con = Server.createobject("Adodb.Connection")con.open"dsn=Candidate"Set rs = Server.createobject("Adodb.Recordset")rs.open "Insert into Cform (Cssn, Cjobno Values("&Cssn&","&Cjobno&"))"rs.open ("Select Max(ID) from Cform"), con, 3, 3

[justsomeguy]

Try this

Set rs = Server.createobject("Adodb.Recordset")rs.ActiveConnection = "dsn=Candidate"rs.open "Insert into Cform (Cssn, Cjobno) Values("&Cssn&","&Cjobno&")"rs.open "Select Max(ID) as ID from Cform"new_id = rs.fields.item("ID").value

[jojay]

Try this

Set rs = Server.createobject("Adodb.Recordset")rs.ActiveConnection = "dsn=Candidate"rs.open "Insert into Cform (Cssn, Cjobno) Values("&Cssn&","&Cjobno&")"rs.open "Select Max(ID) as ID from Cform"new_id = rs.fields.item("ID").value

<{POST_SNAPBACK}>

I tried like the above code and also tried like the one below:

Set con = Server.Createobject("ADODB.Connection")con.open "dsn=Candidate"rs.Activeconnection = conrs.open "Insert into Cform (Cssn, Cjobno) Values("&Cssn&","&Cjobno&")"rs.open "Select Max(ID) as ID from Cform"

Getting this error for both:Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Mc@gmail.com'. ' it is email input type = text

[justsomeguy]

OK, try quoting all of the parameters then. I thought the ssn and job number would be numbers, but just quote everything to be sure:

Set rs = Server.createobject("Adodb.Recordset")rs.ActiveConnection = "dsn=Candidate"rs.open "Insert into Cform (Cssn, Cjobno) Values('"&Cssn&"','"&Cjobno&"')"rs.open "Select Max(ID) as ID from Cform"new_id = rs.fields.item("ID").value

[jojay]

OK, try quoting all of the parameters then.  I thought the ssn and job number would be numbers, but just quote everything to be sure:

Set rs = Server.createobject("Adodb.Recordset")rs.ActiveConnection = "dsn=Candidate"rs.open "Insert into Cform (Cssn, Cjobno) Values('"&Cssn&"','"&Cjobno&"')"rs.open "Select Max(ID) as ID from Cform"new_id = rs.fields.item("ID").value

<{POST_SNAPBACK}>

I am getting an error when using the select statement:

rs.open "Select Max(Cid) from Cform" ' getting error when using Max(id) as CID

error msg:ADODB.Fields error '800a0cc1' Item cannot be found in the collection corresponding to the requested name or ordinal. It looks like it is not recogizing Max(Cid).

[justsomeguy]

It's giving you the error on the line with the SQL statement? That error looks like it should be indicating another line, one where you are trying to access a certain field or something that doesn't exist. That error says something doesn't exist ('Item cannot be found in the requested collection'), it's not a SQL error from what I can tell. Are you sure that error is indicating the SQL statement line and not another line?

[jojay]

It's giving you the error on the line with the SQL statement?  That error looks like it should be indicating another line, one where you are trying to access a certain field or something that doesn't exist.  That error says something doesn't exist ('Item cannot be found in the requested collection'), it's not a SQL error from what I can tell.  Are you sure that error is indicating the SQL statement line and not another line?

<{POST_SNAPBACK}>

Thank you. I fixed the issue. Thank you again for your help.