Insert New Record

[stevelibby]

Hi All
This answer will probably be a doddle for you clever lot.
I have created a table on ms sql server and i am trying to follow the w3schools below.

[w3schools.com...]

So Far so good, but i cant seem to create new records?
There are 3 fields 1 being id that is an "int" and i have set it as table indentity column in the manage indexes.
Have i done all right, what have i done wrontg.

[martyt]

Leave the identity column out of the insert statement. It'll get set automatically.

[stevelibby]

Hi
This is what i have done
sql="INSERT INTO Users (Fname,Lname"
sql=sql & "Fname,Lname)"
sql=sql & " VALUES "
sql=sql & "('" & Request.Form("Fname") & "',"
sql=sql & "'" & Request.Form("Lname") & "',"
on error resume next
objconn.Execute sql,recaffected
if err<>0 then
Response.Write("No update permissions!")
else
Response.Write("<h3>" & recaffected & " record added</h3>")
end if
objconn.close
%>

Which in the info on the form, why doesnt it go into my db?

[martyt]

The sql string you're building has all sorts of syntax errors - column names duplicated, commas in the wrong place, missing closing paren, etc.

Print out the string you've built and see if you can execute the query directly in SQL Query Analyzer or Access or whatever you're using.

Or, better yet, figure out what the SQL query needs to look like using Query Analyzer or Access, then change the code so that it builds that string for you.

[stevelibby]

i have copied the query exactly as the web site has shown, so how can it be wrong?

[martyt]

sql="INSERT INTO Users (Fname,Lname) "
sql=sql & " VALUES "
sql=sql & "('" & Request.Form("Fname") & "',"
sql=sql & "'" & Request.Form("Lname") & "')"

[Easy_Coder]

Just a suggestion...

This technique

sql=sql & "('" & Request.Form("Fname") & "',"

could potentially get you into trouble.

I'd suggest isolating the request items in variables, checking them for illegal characters and then processing them into your sql command accordingly.

[aspdaddy]

If you are using SQL server, you can avoid that technique of creating SQL altogether and just use stored procedures.

They are much simpler to write & debug, execute faster and more secure.

Create dbo.User_SAVE
@Fname varchar(25),
@Lname varchar(25)
AS
INSERT INTO Users (Fname,LName)
VALUES(@Fname,@LName)
GO

Then in the ASP:

objConn.Execute ("exec dbo.User_SAVE " & Request.Form("Fname") & " " & Request.Form("Lname") )