Insert Data Error Message

[jleggett]

I am getting the following error message when I am inserting data into the database:

-2147467259

Here is the insert code.

<%
connectionstring = "DRIVER={MySQL ODBC 3.51 Driver};Server=xxx;Database=xxx;uid=xxx;"
Set cn = Server.CreateObject("ADODB.Connection")
On error resume next
cn.Open connectionstring
if err <> 0 then
cn.Close
usrmsg = "Error opening connection to database."
Response.Redirect("/error.asp?err=" & err & "&usrmsg=" & usrmsg)
else
Set member = Server.CreateObject("ADODB.Recordset")
member.ActiveConnection = cn
'start of form
id = request.Form("id")
firstname = request.Form("firstname")
lastname = request.Form("lastname")
address1 = request.Form("address1")
address2 = request.Form("address2")
city = request.Form("city")
states = request.Form("states")
zip = request.Form("zip")
country = request.Form("country")
email = request.Form("email")
newsletter = request.Form("newsletter")
rdate = FormatDateTime(Date)

fields = ""
fields = fields & "id,"
fields = fields & "firstname,"
fields = fields & "lastname,"
fields = fields & "address1,"
fields = fields & "address2,"
fields = fields & "city,"
fields = fields & "states,"
fields = fields & "zip,"
fields = fields & "country,"
fields = fields & "email,"
fields = fields & "newsletter,"
fields = fields & "rdate"

values = ""
values = values & "'" & id & "',"
values = values & "'" & firstname & "',"
values = values & "'" & lastname & "',"
values = values & "'" & address1 & "',"
values = values & "'" & address2 & "',"
values = values & "'" & city & "',"
values = values & "'" & states & "',"
values = values & "'" & zip & "',"
values = values & "'" & country & "',"
values = values & "'" & email & "',"
values = values & "'" & newsletter & "',"
values = values & "'" & rdate & "'"

sqltext = ""
sqltext = sqltext & "insert into contact (" & fields & ") values (" & values & ")"
'Response.Write(sqltext & "<br>")
On error resume next
member.open sqltext
if err <> 0 then
usrmsg = "Error inserting record into database."
Response.Redirect("error.asp?err=" & err & "&usrmsg=" & usrmsg)
end if

end if
cn.close
%>

any help would be greatly appreciated.

[jimfraser]

the only thing I found on google for that number was a "sql server timeout" error. Does it take a long time to show up?

Also, instead of redirecting with "?err=" & err (which would print out that god awful and extremely annoying number) try
Response.Redirect("error.asp?err=" & err.message & "&usrmsg=" & usrmsg)

and see if that sheds a bit more light

[bbolte]

what does your sql query look like when you write it out? there's prolly a misplaced single quote.

[SSJ]

Whats your sql query prints out?

[jleggett]

what does your sql query look like when you write it out? there's prolly a misplaced single quote.

Thanks for the help. I just went into sql and added a record and pulled out the code:
INSERT INTO `contact` ( `id` , `firstname` , `lastname` , `address1` , `address2` , `city` , `states` , `zip` , `country` , `email` , `newsletter` , `rdate` )
VALUES (
'6', 'test', 'test', 'test', 'test', 'test', 'test', 'test', 'test', 'test', 'test', '1/30/07'
);


Thanks again for the help, I am sure that it is something easy that I'm just over looking.

[bbolte]

but you need to make sure that your sql query looks ok in your code. uncomment this line: 'Response.Write(sqltext & "<br>") and see what it looks like there.

[bbolte]

oh and in reality - you don't need to use a recordset object to do update, inserts or deletes. only use recordsets when returning data. use something like the connection object execute instead.

cn.execute sql

[jleggett]

but you need to make sure that your sql query looks ok in your code. uncomment this line: 'Response.Write(sqltext & "<br>") and see what it looks like there.

When I uncommented out this line : 'Response.Write(sqltext & "<br>")
I got the error message again -2147467259 can not insert records

[bbolte]

doh! put response.end right after that line. this will stop the script from continuing. then check your statement to make sure the syntax is correct.

and obviously, be sure to comment or remove that line when putting it all back.