whats wrong with my insert statement | Newbie | | Join Date: Mar 2008
Posts: 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 -
<!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?
|  | Expert | | Join Date: Jun 2007
Posts: 1,925
| | | re: whats wrong with my insert statement
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
| | Newbie | | Join Date: Mar 2008
Posts: 23
| | | re: whats wrong with my insert statement
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..
| | Expert | | Join Date: Jun 2007
Posts: 1,925
| | | re: whats wrong with my insert statement
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
| | Newbie | | Join Date: Mar 2008
Posts: 23
| | | re: whats wrong with my insert statement
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>
| | Expert | | Join Date: Jun 2007
Posts: 1,925
| | | re: whats wrong with my insert statement
Check your connection string. Here are more about it.
-- CK
| | Newbie | | Join Date: Mar 2008
Posts: 23
| | | re: whats wrong with my insert statement
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 :)
| | Expert | | Join Date: Jun 2007
Posts: 1,925
| | | re: whats wrong with my insert statement
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
|  |
Similar Microsoft SQL Server bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,223 network members.
|
