jojay

i thought that what jojay meant was that he was not getting any result..yeah the syntax is correct. jojay- you may wanna check other parts of the code..

<{POST_SNAPBACK}>

select * from page1 where cname like '%cname%'

or what is the best way to search a field name? I have done in the paste and dont remember now.

Hi, Can someone please help with the following syntax:

sql1 = "Select Cname from page1 where Cname like '%Cname%'"

Above code isnt working. Cname is a text field and would like to know the syntax for the above sql statement using Like. Thanks.

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}>

There's no difference at all, both work just the same. The only difference is the readability of your code (I personally like to see "adOpenStatic" as opposed to "3", its more descriptive).I apologize, but I don't know what you're asking, or what you're trying to get your script to do.

<{POST_SNAPBACK}>

Hmmm...I haven't the faintest clue why the code isn't working. However, if it's giving you problems, then just go back to executing two seperate queries like you were doing originally.However, make sure when you response.redirect "resultpage.asp?cid=something", you request("CID") on your resultpage.asp to get the passed value.

<{POST_SNAPBACK}>

rs.open sql1,con,3,3

and this:

const adUseClient = 3const adOpenStatic = 3const adLockOptimisitic = 3set connset rsrs.open sql1, adOpenStatic, adLockOptimistic

Because in my opinion, in the first case, using the const directly without declaring. In the second case, it is declaring and using them. Please correct if I am wrong. Would like to know the difference? My next question, now the querystring is working but still not able to bring all my data to the resultpage.asp. Should I open the connection and using rs, bring the data from the database or just use response.write(fieldname) and pass the data. Confused.

Jojay, theres nothign wrong with the code I wrote, because its a copy/paste from one my own sites. However I made the presumption that you were using ADOVBS.inc (most ASP programmers include that file at the top of their pages by default). Without that file, ASP won't recognize some of the constants like AdUseClient and so on.See 4GuysFromRolla FAQ - ADOVBS.inc, its a good thing to get in the habit of using ADOVBS.inc. Just copy this text file to your root directory (save it as adovbs.inc or adovbs.asp), and add <!--#include file="adovbs.inc"--> to the top of any page that uses ADO, and then the code I wrote above will work.

<{POST_SNAPBACK}>

rs.CursorLocation = AdUseClient

If I comment that line, then the error is going to:

rs.Open sql1, con, adOpenStatic, adLockOptimisitic

Now, the error is showing up in result.asp and data is not inserting to the database.I really dont know what is causing my problem not passing the value.

result.asp

Dim Conn, RS, SQL, LastInsertedIDSet Conn = Server.CreateObject("ADODB.Connection")Set RS = Server.CreateObject("ADODB.Recordset")Conn.Open your_connection_string	rs.CursorLocation = AdUseClient ' this also gives error so I removed that line	sql1 = "Select * From Your_Table Where 0=1"	rs.Open sql1,conn, 3,3  RS.AddNew  rs("Field1")	= Field1_Value  rs("Field2")	= Field2_Value  rs("Field3")	= Field3_Value  rs("Field4")	= Field4_Value  rs("Field5")	= Field5_Value  RS.Update  LastInsertedID = rs("CID")	RS.closeConn.Closeresponse.redirect "resultpage.asp?CID=" & LastInsertedID

[resultpage.asp]

CID = request.querystring("CID")

<{POST_SNAPBACK}>

Can someone tell how to pass a value from one page to another?I am trying to populate the form in form.asp and send those info to result.asp, dump into the database and response.write that info to the third page which is resultpage.asp. I am able to populate and send to database and it goes to resultpage.asp but it is not response.writing the info.result.asp

request.form fieldnamesconnection and recordset stringrs.open "Insert into table...."rs.open "Select Top 1 CID from cform Order BY CID Asc"new_id = rs.fields.item("CID").valuers.closecon.closeand set both = nothingresponse.redirect("resultpage.asp?CID = " & New_id)

resultpage.asp

CID = request.querystring("new_id")request.form formfieldsresponse.write formfields

My question is, should I have to create <form></form> and input fields to pass the values to the resultpage.asp. Anyway, I dont want to give a submit button in result.asp to pass the value to resultpage.asp. Any suggestins? Thanks.

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}>

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).

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}>

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

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}>

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

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.

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}>

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.

Hi there, I just wrote an easy code earlier today that could help you.<body><script type="text/vbscript"> url= "http://www.google.com"document.write(url)location.href=url</script></body>Give that a try, it worked well for me. I hope it does what you need too.

<{POST_SNAPBACK}>

Thanks for the reply.  I will keep looking for the answer.  If I find it, I will post it.

<{POST_SNAPBACK}>

I'm not real sure what you're doing with the recordset object.  I think it's just easiest to use SQL statements, instead of using the methods of the recordset object to deal with the database.  So just use queries to do everything:

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

<{POST_SNAPBACK}>

rs.addnewrs.field1rs.field2rs.updatenew_id = rs.fields.item("ID").value%><%rs.closeSet rs=nothingcon.closeSet con=nothing%>br /><br /><%response.redirect("resultpage.asp?ID=" & new_id)

on the resultpage.asp:

ID = request.querystring("new_id")

I don't really like the way ASP interacts with databases, but this is how I do it:

var week_con = Server.CreateObject("ADODB.recordset");week_con.ActiveConnection = ts_dbcon_str;week_con.open("SELECT * FROM week WHERE startDate=" + sDate.valueOf() + " AND endDate=" + eDate.valueOf());if (!week_con.eof){  ...}week_con.close();

I only use the recordset object, not a connection (despite the name).  I'm not sure what code you are working with now or what problems you are having.  If you want help paste the code you are using.  For the SQL statement, just use one query to get the new ID, and a second query to get all of the information about that ID.

<{POST_SNAPBACK}>

<%Set con = Server.createobject("Adodb.Connection")con.open"dsn=Candidate"Set rs = Server.createobject("Adodb.Recordset")Set rs = con.execute ("Select   Max(CID) from Cform")rs.AddNewrs("Cssn") = Cssnrs("Cjobno") =  Cjobnors.updatenew_id = rs.fields.item("CID").value%><%rs.closeSet rs=nothingcon.closeSet con=nothing%>response.redirect("resultpage.asp?Cid=" + new_id)

So far so good. But I am not able to get to the sql statement your way. Any help would be greatful. Thanks again.

I am having problem with this code. When I use as this:

rs.open "Select Max(ID) as myid from cform", con, 3, 3

It is not recognizing the other fields in the database and I am getting an error such as "Item not to be found for the requested........."So, I changed the Select statement using all the fields such as:

"Select Max(CID), Cssn, ......from cform"

Now, I am getting this error message:Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'Cssn' as part of an aggregate function. I understand since we are using the Max function, it is showing up that error. What is the round about to do this? Thanks for the help.

I am still having problems with my code. I am getting the following error:Microsoft OLE DB Provider for ODBC Drivers error '80040e10' [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. I have few questions:Why do you have use :

con.open "Select Max(ID) as myid from cform"

should it be :

rs.open "Select Max(ID) as myid from cform", con, 3, 3

because I am already opening the con.open "dsn=candidate"the error points at:

new_id = con.fields.item("myid").value

after the above code, giving rs.addnew.....so on...

Directly after you insert everything into the database (as in the statement right after the SQL query), you need to get the highest ID number:

sqlcon.open("SELECT MAX(id) AS id FROM table");new_id = sqlcon.fields.item("id").value;...response.redirect("processpage.asp?id=" + new_id);processpage.asp:id = request.querystring("id");

<{POST_SNAPBACK}>

Form.aspID field as hiddenother formfields....Submit button------result.aspformfields = request.form("formfields")Set con = server.createobject("ADODB.Connection")con.open "dsn=mydsn"Set rs = server.createobject("ADODB.Recordset")rs.open "select * from tablename where 0=1",3,3addnew method to insert to databasers.updatecon.open("Select Max(id) as myid from tablename");new_id = con.fields.item("id").value [COLOR=red]'I dont understand this line...what is con.fields.item...is the fieldname from the table...[/COLOR]connection.closerecordset.closeresponse.redirect("resultpage.asp?id=" + new_id);-------resultpage.aspCID = Request.querystring("CID")response.write("formfields")Print command to print.

Thank you for your help.

Hi,I tried to work this around and I am almost there but with little problem. What I did is, is to keep the form.asp and direct the result to result.asp. In result.asp, i am giving the response.redirect to resultpage.asp and also included the <input type="hidden" name="ID">. In resultpage.asp, i am trying to get the results from form.asp through the hidden ID. In resultpage.asp, the codes are:

ID = Request.form("ID")<input type="hidden" name="ID" value="<%response.write("ID")%>">then table with form fields as response.write(request.form("fieldname"))

As you can see, I havent given connection to the database since I am just extracting the result to this page through response.write. But I am getting an error such as below:"object required".what does it mean? Do  I need connection in this page? thanks.

<{POST_SNAPBACK}>

Form.aspID field as hiddenother formfields....Submit button------result.aspformfields = request.form("formfields")Set connection..Set Recordset...addnew method to insert to database<input type="hidden" name="CID" value="<%=rs("CID")%>">connection.closerecordset.closeresponse.redirect("resultpage.asp")-------resultpage.aspCID = Request.Form("CID")response.write("formfields")Print command to print.

Now, since I have given autonumber as datatype for CID, each time when I sent the data to the database, the autonumber increments. How will I get the CID from the database to show up in resultpage.asp. Should I open again the connection and retreive the CID? Please help.

Hi,I tried to work this around and I am almost there but with little problem. What I did is, is to keep the form.asp and direct the result to result.asp. In result.asp, i am giving the response.redirect to resultpage.asp and also included the <input type="hidden" name="ID">. In resultpage.asp, i am trying to get the results from form.asp through the hidden ID. In resultpage.asp, the codes are:

ID = Request.form("ID")<input type="hidden" name="ID" value="<%response.write("ID")%>">then table with form fields as response.write(request.form("fieldname"))

As you can see, I havent given connection to the database since I am just extracting the result to this page through response.write. But I am getting an error such as below:"object required".what does it mean? Do I need connection in this page? thanks.

At the end.  Here is my uber-detailed schematic of process.asp:

<get responses from POST> username = request.form("username")<validate responses> user entered a string for a number? bad user! smack them to the ground!<insert into database><get id for new data>response.redirect("thanks.asp?id=" + new_id)

Since you will probably want to validate the variables, it might be a good idea to have the form page do all the processing also.  So, the form page would show the form, and would also do the above, get all the data, validate it, insert into the database, and redirect.  You can go one step further, and also have the form page redirect back to itself.  That way you only use 1 page (although the code might be a little confusing).  It doesn't matter which page you redirect to, as long as a redirect happens.  That way they refresh the redirected page (which didn't do any processing) instead of the page that inserted into the db.

<{POST_SNAPBACK}>

It's probably best to have form.asp submit to process.asp, and have process put everything in the database and then redirect to something like result.asp, and send result the ID number of the record that was just inserted in the database.  That way, process can look up the info from the database and display it.

<{POST_SNAPBACK}>

You can always insert a database processing page.form.asp   fill out the form and submitprocess.asp  insert into the database, response.redirect to result.asp, or back to form.aspSince the process page does a response.redirect, they will be refreshing the redirected page, not the page that did the processing.

<{POST_SNAPBACK}>