SQL Database & ASP's 1

[kirstenlargent]

I have an SQL database in which I need to update using an HTML form & an ASP page. The table has 17 fields where one of the fields is an AutoNumber. My questions are:
In my SQL statement, how do I update the AutoNumber? Does the SQL database do this automatically?
Do I need a WHERE clause in my SQL statement since there’s no condition?
And finally, in the request.form(“Qust?”) statements, is the word “form” strictly “form” or is it supposed to be the name of the html form from which the values are entered? I appreciate any help I can get. Thanks!

Following is the code that I’m using.

<%
On Error Resume Next

' Database Connection
Set conn=server.createobject(&quot;adodb.connection&quot
conn.provider = &quot;sqloledb&quot;
provStr = &quot;Server=server;Database=dbName;Description=AppDesc;UID=userID;PWD=passWd;APP=EUC&quot;
conn.Open provStr

' Declare Variables
dim Qust1
dim Qust2
dim Qust3
dim Qust4
dim Qust5
dim Qust6
dim Qust7
dim Qust8
dim Qust9
dim Qust10
dim Qust11
dim Qust12
dim Qust13
dim Qust14
dim Qust15
dim Qust16
dim Comments

' Assign Values
Qust1 = request.form(&quot;Qust1&quot
Qust2 = request.form(&quot;Qust2&quot
Qust3 = request.form(&quot;Qust3&quot
Qust4 = request.form(&quot;Qust4&quot
Qust5 = request.form(&quot;Qust5&quot
Qust6 = request.form(&quot;Qust6&quot
Qust7 = request.form(&quot;Qust7&quot
Qust8 = request.form(&quot;Qust8&quot
Qust9 = request.form(&quot;Qust9&quot
Qust10 = request.form(&quot;Qust10&quot
Qust11 = request.form(&quot;Qust11&quot
Qust12 = request.form(&quot;Qust12&quot
Qust13 = request.form(&quot;Qust13&quot
Qust14 = request.form(&quot;Qust14&quot
Qust15 = request.form(&quot;Qust15&quot
Qust16 = request.form(&quot;Qust16&quot
Comments = request.form(&quot;Comments&quot

' SQL Update Statement
SQL = &quot;UPDATE tblSurvey SET &quot;
SQL = SQL + &quot;Qust1='&quot; & Qust1 & &quot;',Qust2='&quot; & Qust2 & &quot;', &quot;
SQL = SQL + &quot;Qust3='&quot; & Qust3 & &quot;',Qust4='&quot; & Qust4 & &quot;', &quot;
SQL = SQL + &quot;Qust5='&quot; & Qust5 & &quot;',Qust6='&quot; & Qust6 & &quot;', &quot;
SQL = SQL + &quot;Qust7='&quot; & Qust7 & &quot;',Qust8='&quot; & Qust8 & &quot;', &quot;
SQL = SQL + &quot;Qust9='&quot; & Qust9 & &quot;',Qust10='&quot; & Qust10 & &quot;', &quot;
SQL = SQL + &quot;Qust11='&quot; & Qust11 & &quot;',Qust12='&quot; & Qust12 & &quot;', &quot;
SQL = SQL + &quot;Qust13='&quot; & Qust13 & &quot;',Qust14='&quot; & Qust14 & &quot;', &quot;
SQL = SQL + &quot;Qust15='&quot; & Qust15 & &quot;',Qust16='&quot; & Qust16 & &quot;', &quot;
SQL = SQL + &quot;Comments='&quot; & Comments

' Executes Data Update
Set dRS = conn.execute(SQL)

' Error Handlers
If err.number>0 Then
response.write &quot;VBScript Errors Occured:&quot; & &quot;<P>&quot;
response.write &quot;Error Number=&quot; & err.number & &quot;<p>&quot;
response.write &quot;Error Descr.=&quot; & err.description & &quot;<p>&quot;
response.write &quot;Help Context=&quot; & err.helpcontext & &quot;<p>&quot;
response.write &quot;Help Path=&quot; & err.helppath & &quot;<p>&quot;
response.write &quot;Native Error=&quot; & err.nativeerror & &quot;<p>&quot;
response.write &quot;Source=&quot; & err.source & &quot;<p>&quot;
response.write &quot;SQLState=&quot; & err.sqlstate & &quot;<p>&quot;
End If

If conn.Errors.Count>0 Then
response.write &quot;Database Errors Occured&quot; & &quot;<p>&quot;
response.write SQL & &quot;<p>&quot;
For counter= 0 to conn.Errors.Count
response.write &quot;Error #&quot; & conn.Errors(counter).Number & &quot;<p>&quot;
response.write &quot;Error desc. -> &quot; & conn.Errors(counter).Description & &quot;<p>&quot;
Next
Else
response.write &quot;<b>Everything Went Fine! Record is now UPDATED!</b>&quot;
response.write &quot;<br>&quot; & SQL
End If

' Clean up RecordSet
dRS.close
Set dRS = nothing
%>

 

[GaryC123]

1. If you are adding a new record then Autonumber will assign the next no., you cant assign this yourself. So if the last record added had an id of 1234 the next record added will have an id of 1235
2. Where you are updating the record you need to tell it which record to update, so the final line of your SQL statement should be something like this
SQL = SQL + &quot;ID='&quot; & ID
3. Form is just Form, not the name of the form itself, there is two options available - request.form when you use Post action (Post does not show the variables) on the previous form and request.querystring when you use Get (Get passes the variables in the URL string) Saturday 12.00

20.00

3.00am

Sunday [img http

 

[kirstenlargent]

GaryC123, thanks for the info! I'm actually trying to add a new record. So now I'm thinking the UPDATE method isn't what I need to use. Would you agree? As you can probably tell, I'm a total amateur. Thanks again for your help.

 

[mozingod]

If you set the field as an Autoupdate on the SQL Server, then just completely ommit it in your update statement and the server will take care of it. Darrell Mozingo
MCSA, A+, Network+, i-Net+, MOUS 2000 Master

 

[GaryC123]

As in your example above you could do it this way (you're right Update isnt what you need above)

sql= INSERT INTO tblSurvey (Qust1, Qust2,....)
VALUES (request.form(&quot;Qust1&quot, request.form(&quot;Qust2&quot,...)

You could also omit the field names but you must specify a value for each field and it will follow the order that the fields are placed in the table

sql= INSERT INTO tblSurvey VALUES (request.form(&quot;Qust1&quot, request.form(&quot;Qust2&quot,...) Saturday 12.00

20.00

3.00am

Sunday [img http

 

[kirstenlargent]

Gary123, Thanks again. Can I do the following since I've already assigned the form values to the variables Qust1, Qust2, and so on. Or should I do what you have above. Also should I use different names for the variables (or does it matter)?

SQL = &quot;INSERT INTO tblSurvey (Qust1, Qust2, Qust3, Qust4, Qust5, Qust6, Qust7, Qust8, Qust9, Qust10, Qust11, &quot;
SQL = SQL + &quot;Qust12, Qust13, Qust14, Qust15, Qust16, Comments) VALUES (Qust1, Qust2, Qust3, Qust4, Qust5, &quot;
SQL = SQL + &quot;Qust6, Qust7, Qust8, Qust9, Qust10, Qust11, Qust12, Qust13, Qust14, Qust15, Qust16, Comments)&quot;

Also, the first field of a record in my table is the Autonumber. And judging by the prior threads, I guess I just leave that one out of my sql string, right?

Thanks again.

 

[GaryC123]

What you've got above is perfect, I was just reducing your code a bit, like where you've got

dim Qust1
dim Qust2
dim Qust3

could be written
Dim Qust1, Qust2, Qust3.... Saturday 12.00

20.00

3.00am

Sunday [img http

 

[abig99]

OK, just so you know, SQL behaves slightly differently to most databases where autonumbers are concerned, autonumber is only populated after the focus has left that record, can be a problem if u try requesting autonumber as soon as record has been added.

P.S. you can use update or insert into.
you need to create a recordset with update though, insert into is quick, but you will have less control.