jojay Posted May 11, 2006 Author Share Posted May 11, 2006 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. Link to comment Share on other sites More sharing options...
justsomeguy Posted May 11, 2006 Share Posted May 11, 2006 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. Link to comment Share on other sites More sharing options...
jojay Posted May 11, 2006 Author Share Posted May 11, 2006 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. Link to comment Share on other sites More sharing options...
justsomeguy Posted May 11, 2006 Share Posted May 11, 2006 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. Link to comment Share on other sites More sharing options...
jojay Posted May 11, 2006 Author Share Posted May 11, 2006 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 Link to comment Share on other sites More sharing options...
justsomeguy Posted May 11, 2006 Share Posted May 11, 2006 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 Link to comment Share on other sites More sharing options...
jojay Posted May 11, 2006 Author Share Posted May 11, 2006 Try thisSet 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 Link to comment Share on other sites More sharing options...
justsomeguy Posted May 11, 2006 Share Posted May 11, 2006 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 Link to comment Share on other sites More sharing options...
jojay Posted May 12, 2006 Author Share Posted May 12, 2006 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). Link to comment Share on other sites More sharing options...
justsomeguy Posted May 15, 2006 Share Posted May 15, 2006 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? Link to comment Share on other sites More sharing options...
jojay Posted May 16, 2006 Author Share Posted May 16, 2006 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. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now