whats wrong with my insert statement

[studentofknowledge]

im trying to add form data in to my local sql 2005 database but having a lot of trouble. I have searched the web but im coming across misleading information time and time again.

below is my code

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Submit Document</title>
</head>
<body>
<%
Set conn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath ("laptop\omar.mdb") & ";"
objConn.Open

sql="INSERT INTO webform (txtAmount,txtPurpose,"
sql=sql & "txtResidentialStatus,txtTitle,txtFirstName,txtSurname,txtContactNumber,txtEveningNumber,txtEmailAddress)"
sql=sql & " VALUES "
sql=sql & "('" & Request.Form("txtAmount") & "',"
sql=sql & "'" & Request.Form("txtPurpose") & "',"
sql=sql & "'" & Request.Form("txtResidentialStatus") & "',"
sql=sql & "'" & Request.Form("txtTitle") & "',"
sql=sql & "'" & Request.Form("txtFirstName") & "',"
sql=sql & "'" & Request.Form("txtSurname") & "',"
sql=sql & "'" & Request.Form("txtContactNumber") & "')"
sql=sql & "'" & Request.Form("txtEveningNumber") & "')"
sql=sql & "'" & Request.Form("txtEmailAddress") & "')"

on error resume next
conn.Execute sql,recaffected
if err<>0 then
  Response.Write("No update permissions!")
else 
  Response.Write("<h3>" & recaffected & " record added</h3>")
end if
conn.close
%>


</body>
</html>

can some one help please?

[ck9663]

1. What's the error that you're getting?

2. Display the value of the your sql variable and paste it to your query analyzer. See what error will show up.

-- CK

[studentofknowledge]

this is what i'm getting on the next page

<% Set conn = Server.CreateOb ject("ADODB.Con nection") ConnectionStrin g = "Provider = SQLNCLI;" & _ "Data Source = LAPTOP\\SQLEXPR ESS;" & _ "Initial Catalog = zion.mdb;" & _ "User ID = ej2_sleeper;" & _ "Password = jennifer1;" Server.MapPath ("laptop\omar.m db") & ";" conn.Open txtAmount = Trim(Request.Fo rm("txtAmount") ) txtPurpose = Trim(Request.Fo rm("txtPurpose" )) txtResidentialS tatus = Trim(Request.Fo rm("txtResident ialStatus")) txtTitle = Trim(Request.Fo rm("txtTitle") ) txtFirstName = Trim(Request.Fo rm("txtFirstNam e")) txtSurname = Trim(Request.Fo rm("txtSurname" )) txtContactNumbe r = Trim(Request.Fo rm("txtContactN umber")) txtEveningNumbe r = Trim(Request.Fo rm("txtEveningN umber")) txtEmailAddress = Trim(Request.Fo rm("txtEmailAdd ress")) sql="INSERT INTO dbo.tbl_custome r (txtAmount,txtP urpose,txtResid entialStatus,tx tTitle,txtFirst Name,txtSurname ,txtContactNumb er,txtEveningNu mber,txtEmaildd ress)" sql=sql & " VALUES ('"&txtAmount&" ','"&txtPurpose &"', '"&txtResidenti alStatus& "','"&txtTitle& "','"&txtFirstN ame&"','"&txtSu rname&"','"&txt ContactNumber&" ', '"&txtEveningNu mber&"', '"&txtEailAddre ss&"')" conn.execute(sq l) 'if err<>0 then ' Response.Write err.Description 'else ' Response.Write( "
record added
") 'end if conn.close %>

not sure how to respond to your 2nd point as I dont understand..

[ck9663]

Do something like:

Response.Write( sql)

It will display the sql text. Copy the entire text and open a query window. Paste it on the query window and execute it. It'll give you detailed information on the error.

-- CK

[studentofknowledge]

hi CK thanks for your reply

i have modified my code according to your suggestion - but I still get all this info displayed on the page? below is updated code

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dt d">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Submit Document</title>
</head>
<body>
<%
Set conn = Server.CreateOb ject("ADODB.Con nection")
ConnectionStrin g = "Provider = SQLNCLI;" & _
"Data Source = LAPTOP\\SQLEXPR ESS;" & _
"Initial Catalog = zion.mdb;" & _
"User ID = ej2_sleeper;" & _
"Password = jennifer1;"
Server.MapPath ("laptop\omar.m db") & ";"

conn.Open
txtAmount = Trim(Request.Fo rm("txtAmount") )
txtPurpose = Trim(Request.Fo rm("txtPurpose" ))
txtResidentialS tatus = Trim(Request.Fo rm("txtResident ialStatus"))
txtTitle = Trim(Request.Fo rm("txtTitle") )
txtFirstName = Trim(Request.Fo rm("txtFirstNam e"))
txtSurname = Trim(Request.Fo rm("txtSurname" ))
txtContactNumbe r = Trim(Request.Fo rm("txtContactN umber"))
txtEveningNumbe r = Trim(Request.Fo rm("txtEveningN umber"))
txtEmailAddress = Trim(Request.Fo rm("txtEmailAdd ress"))

sql="INSERT INTO dbo.tbl_custome r (txtAmount,txtP urpose,txtResid entialStatus,tx tTitle,txtFirst Name,txtSurname ,txtContactNumb er,txtEveningNu mber,txtEmaildd ress)"
sql=sql & " VALUES ('"&txtAmount&" ','"&txtPurpose &"', '"&txtResidenti alStatus& "','"&txtTitle& "','"&txtFirstN ame&"','"&txtSu rname&"','"&txt ContactNumber&" ', '"&txtEveningNu mber&"', '"&txtEailAddre ss&"')"

conn.execute(sq l)
if err<>0 then
Response.Write( sql)
else
Response.Write( "<h3> record added</h3>")
end if
conn.close
%>


</body>
</html>

[ck9663]

Check your connection string.

Here are more about it.


-- CK

[studentofknowledge]

thanks CK - maybe my connection string is incorrect after all. I've checked the site but not sure which string to use;

Providers to use when connecting to SQL Server 2005
» .NET Framework Data Provider for SQL Server (SqlConnection)
» SQL Native Client 9.0 OLE DB provider
» .NET Framework Data Provider for OLE DB (OleDbConnectio n)
» SQL Server Native Client 10.0 OLE DB Provider
» SQL Native Client 9.0 ODBC Driver
» SQL Server Native Client 10.0 ODBC Driver
» .NET Framework Data Provider for ODBC (OdbcConnection )
» SQLXML 4.0 OLEDB Provider
» Context Connection

I'm using SQL 2005 express with classic ASP on my vista laptop if that helps. Just trying to populate a table in my local SQL database via a local html webform.

please advise and many thanks in advance :)

[ck9663]

SQL Server family can be access by using the same connection string. Try one of these. You're doing it right using classic ASP.

Also, I'm not responding on your other post. Your insert on your other post will not work until you make your connection work.Try to make your connection work first, then do some simple SELECT. Then proceed to INSERT.

-- CK