By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,842 Members | 2,368 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,842 IT Pros & Developers. It's quick & easy.

whats wrong with my insert statement

P: 23
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

Expand|Select|Wrap|Line Numbers
  1. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  2. <html xmlns="http://www.w3.org/1999/xhtml">
  3. <head>
  4. <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  5. <title>Submit Document</title>
  6. </head>
  7. <body>
  8. <%
  9. Set conn = Server.CreateObject("ADODB.Connection")
  10. objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
  11. Server.MapPath ("laptop\omar.mdb") & ";"
  12. objConn.Open
  13.  
  14. sql="INSERT INTO webform (txtAmount,txtPurpose,"
  15. sql=sql & "txtResidentialStatus,txtTitle,txtFirstName,txtSurname,txtContactNumber,txtEveningNumber,txtEmailAddress)"
  16. sql=sql & " VALUES "
  17. sql=sql & "('" & Request.Form("txtAmount") & "',"
  18. sql=sql & "'" & Request.Form("txtPurpose") & "',"
  19. sql=sql & "'" & Request.Form("txtResidentialStatus") & "',"
  20. sql=sql & "'" & Request.Form("txtTitle") & "',"
  21. sql=sql & "'" & Request.Form("txtFirstName") & "',"
  22. sql=sql & "'" & Request.Form("txtSurname") & "',"
  23. sql=sql & "'" & Request.Form("txtContactNumber") & "')"
  24. sql=sql & "'" & Request.Form("txtEveningNumber") & "')"
  25. sql=sql & "'" & Request.Form("txtEmailAddress") & "')"
  26.  
  27. on error resume next
  28. conn.Execute sql,recaffected
  29. if err<>0 then
  30.   Response.Write("No update permissions!")
  31. else 
  32.   Response.Write("<h3>" & recaffected & " record added</h3>")
  33. end if
  34. conn.close
  35. %>
  36.  
  37.  
  38. </body>
  39. </html>
  40.  
can some one help please?
Jan 3 '09 #1
Share this Question
Share on Google+
7 Replies


ck9663
Expert 2.5K+
P: 2,878
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
Jan 3 '09 #2

P: 23
this is what i'm getting on the next page

<% Set conn = Server.CreateObject("ADODB.Connection") ConnectionString = "Provider = SQLNCLI;" & _ "Data Source = LAPTOP\\SQLEXPRESS;" & _ "Initial Catalog = zion.mdb;" & _ "User ID = ej2_sleeper;" & _ "Password = jennifer1;" Server.MapPath ("laptop\omar.mdb") & ";" conn.Open txtAmount = Trim(Request.Form("txtAmount")) txtPurpose = Trim(Request.Form("txtPurpose")) txtResidentialStatus = Trim(Request.Form("txtResidentialStatus")) txtTitle = Trim(Request.Form("txtTitle")) txtFirstName = Trim(Request.Form("txtFirstName")) txtSurname = Trim(Request.Form("txtSurname")) txtContactNumber = Trim(Request.Form("txtContactNumber")) txtEveningNumber = Trim(Request.Form("txtEveningNumber")) txtEmailAddress = Trim(Request.Form("txtEmailAddress")) sql="INSERT INTO dbo.tbl_customer (txtAmount,txtPurpose,txtResidentialStatus,txtTitl e,txtFirstName,txtSurname,txtContactNumber,txtEven ingNumber,txtEmailddress)" sql=sql & " VALUES ('"&txtAmount&"','"&txtPurpose&"', '"&txtResidentialStatus& "','"&txtTitle&"','"&txtFirstName&"','"&txtSurname &"','"&txtContactNumber&"', '"&txtEveningNumber&"', '"&txtEailAddress&"')" conn.execute(sql) '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..
Feb 22 '09 #3

ck9663
Expert 2.5K+
P: 2,878
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
Feb 23 '09 #4

P: 23
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.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")
ConnectionString = "Provider = SQLNCLI;" & _
"Data Source = LAPTOP\\SQLEXPRESS;" & _
"Initial Catalog = zion.mdb;" & _
"User ID = ej2_sleeper;" & _
"Password = jennifer1;"
Server.MapPath ("laptop\omar.mdb") & ";"

conn.Open
txtAmount = Trim(Request.Form("txtAmount"))
txtPurpose = Trim(Request.Form("txtPurpose"))
txtResidentialStatus = Trim(Request.Form("txtResidentialStatus"))
txtTitle = Trim(Request.Form("txtTitle"))
txtFirstName = Trim(Request.Form("txtFirstName"))
txtSurname = Trim(Request.Form("txtSurname"))
txtContactNumber = Trim(Request.Form("txtContactNumber"))
txtEveningNumber = Trim(Request.Form("txtEveningNumber"))
txtEmailAddress = Trim(Request.Form("txtEmailAddress"))

sql="INSERT INTO dbo.tbl_customer (txtAmount,txtPurpose,txtResidentialStatus,txtTitl e,txtFirstName,txtSurname,txtContactNumber,txtEven ingNumber,txtEmailddress)"
sql=sql & " VALUES ('"&txtAmount&"','"&txtPurpose&"', '"&txtResidentialStatus& "','"&txtTitle&"','"&txtFirstName&"','"&txtSurname &"','"&txtContactNumber&"', '"&txtEveningNumber&"', '"&txtEailAddress&"')"

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


</body>
</html>
Feb 23 '09 #5

ck9663
Expert 2.5K+
P: 2,878
Check your connection string.

Here are more about it.


-- CK
Feb 23 '09 #6

P: 23
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 (OleDbConnection)
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 :)
Feb 23 '09 #7

ck9663
Expert 2.5K+
P: 2,878
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
Feb 23 '09 #8

Post your reply

Sign in to post your reply or Sign up for a free account.