inserting data to a database

pinkcat_02

02-06-2003, 04:52 PM

hi all,

i have managed to get rid of all the error messages in the code but still it is not putting data into database :(
any idea???

<%@ Language = "VBScript"%>
<%

'Declare all local variables
dim conn
dim rs
dim strconn
dim strSQL
Dim strCustomer_Name
Dim strCustomer_Address
Dim strCustomer_Postcode
Dim strCustomer_Phonenumber
Dim strCustomer_Email

strCustomer_Name = Request.Form("Customer_Name")
strCustomer_Address = Request.Form("Customer_Address")
strCustomer_Postcode = Request.Form("Customer_Postcode")
strCustomer_Phonenumber = Request.Form("Customer_Phonenumber")
strCustomer_Email = Request.Form("Customer_Email")

If Len (Customer_Name) > 0 Then

Set conn = Server.CreateObject("ADODB.Connection")
'set connection string to local variable-I use a DSN-less connection
'Need to modify just this line.Point to right path to database file.
conn.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("\anticus\fyp\deneme.mdb"))
'build the sql statement based on the input from the form
'strSQL = "INSERT INTO Customer Contact Details(Customer_Name, Customer_Address, Customer_Postcode, Customer_Phonenumber, Customer_Email ) Values('" & strCustomer_Name & "','" & strCustomer_Address & "','" & strCustomer_Postcode & "','" & strCustomer_Phonenumber & "','" & strCustomer_Email & "')
conn.Execute(strSQL)

conn.close
set conn = nothing
end if

%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>

<form action="customer details.asp" method="post">

<p>Your name: <input type="text" name="Customer_Name"></p>
<p>Your address: <input type="text" name="Customer_Address"> </p>
<p>Your postcode: <input type="text" name="Customer_Postcode"> </p>
<p>Your Phone Number: <input type="text" name="Customer_Phone"> </p>
<p>Your e-mail address: <input type="text" name="Customer_Email"></p>
<input type="submit" name="Submit" value="Submit">

<p> </p>
</form>
</body>
</html>

when i check the database i can't really see the datas i have inserted through the web page. :(

justame

02-06-2003, 05:42 PM

pin...
/me doesnt just a do® this kinda stuff.../mes just a knowledge® is limited to 'comparing'...
n' what /me does see??? is youve called your phone variable one thing 'cept in the 'formfield'??? youve got it called just a something® else...

Dim strCustomer_Phonenumber

strCustomer_Phonenumber = Request.Form("Customer_Phonenumber")

n' thennn youve just a got®...
Your Phone Number: <input type="text" name="Customer_Phone"> </p>

pinkcat_02

02-06-2003, 05:48 PM

ok i have changed the Phonenumber bit but I couldn't really understand what you were telling in the first few lines.

can you pls explain more clearly?

thanks

arnyinc

02-06-2003, 07:03 PM

You left out the "str" in front of strCustomer_Name in your if-statement. So, none of your DB code is executing.

I always do error checking with some basic response.write statements. For example, put a response.write inside your if-statement to make sure it is evaluating true.

If Len (Customer_Name) > 0 Then
response.write "did this work?"
...

end if

Then you would look at your if-statement to see why it isn't evaluating the way you expect it and notice that the variable name is incorrect.

Don't forget to uncomment out your SQL string also.

pinkcat_02

02-06-2003, 07:18 PM

I have tried adding str to the Customer_Name in my if statement. I have added this text "doe that work?" and i can't see it when i submit the form.

I think it does not read the code after the if statement and I have no idea what causes that?

do u have any idea?

arnyinc

02-06-2003, 07:30 PM

Try this and see what output you get.

response.write "Customer Name:"&strCustomer_Name&"<br>"
response.write "Length of Customer Name:"&Len(strCustomer_Name)&"<br>"

If Len(strCustomer_Name) > 0 Then
response.write "testing"
..

end if

Hope I didn't make any typos... :)

pinkcat_02

02-06-2003, 07:38 PM

it gives an error of which points to :

conn.execute(strSQL)

Microsoft OLE DB Provider for ODBC Drivers error '80040e0c'

Command text was not set for the command object.

arnyinc

02-06-2003, 07:47 PM

That sounds like the if-statement is returning true and executing your database code then.

Is your strSQL statement still commented out as in the original example? You might be executing a null string.

Also, I never use table names with spaces in them. I think you need to surround them with square brackets.

strSQL = "INSERT INTO [Customer Contact Details](Customer_Name, Customer_Address, Customer_Postcode, Customer_Phonenumber, Customer_Email ) Values('" & strCustomer_Name & "','" & strCustomer_Address & "','" & strCustomer_Postcode & "','" & strCustomer_Phonenumber & "','" & strCustomer_Email & "')

edit: if you're still getting an error, do a response.write strSQL before you execute your SQL statement so we can see what SQL statement you're passing in.

pinkcat_02

02-06-2003, 08:10 PM

<%@ Language = "VBScript"%>
<%

'Declare all local variables
dim conn
dim rs
dim strconn
dim strSQL
Dim strCustomer_Name
Dim strCustomer_Address
Dim strCustomer_Postcode
Dim strCustomer_Phonenumber
Dim strCustomer_Email

strCustomer_Name = Request.Form("Customer_Name")
strCustomer_Address = Request.Form("Customer_Address")
strCustomer_Postcode = Request.Form("Customer_Postcode")
strCustomer_Phonenumber = Request.Form("Customer_Phonenumber")
strCustomer_Email = Request.Form("Customer_Email")

If Len (strCustomer_Name) > 0 Then

Set conn = Server.CreateObject("ADODB.Connection")
'set connection string to local variable-I use a DSN-less connection
'Need to modify just this line.Point to right path to database file.
conn.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("\anticus\fyp\deneme.mdb"))
'build the sql statement based on the input from the form
'strSQL = "INSERT INTO [Customer Contact Details](Customer_Name, Customer_Address, Customer_Postcode, Customer_Phonenumber, Customer_Email ) Values('" & strCustomer_Name & "','" & strCustomer_Address & "','" & strCustomer_Postcode & "','" & strCustomer_Phonenumber & "','" & strCustomer_Email & "')
'Response.Write(strSQL)
conn.execute(strSQL)

Session("Customer_Name") = strCustomer_Name
conn.close
set conn = nothing

end if

%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>

<form action="customer details.asp" method="post"name="RegisterForm">

<p>Your name: <input type="text" name="Customer_Name"></p>
<p>Your address: <input type="text" name="Customer_Address"> </p>
<p>Your postcode: <input type="text" name="Customer_Postcode"> </p>
<p>Your Phone Number: <input type="text" name="Customer_Phonenumber"> </p>
<p>Your e-mail address: <input type="text" name="Customer_Email"></p>

<input type="SUBMIT" name="SUBMIT" value="SUBMIT">

</form>
</body>
</html>

this is the last form of the code and it still gives the error of
Microsoft OLE DB Provider for ODBC Drivers error '80040e0c'

Command text was not set for the command object.

for

'Response.Write(strSQL)
this time :(
i am giving u a headache :(

arnyinc

02-06-2003, 08:25 PM

Don't comment out those two lines where you set strSQL equal to the your INSERT statement and where you response.write(strSQL). The first line is vital and the output will show any errors.

strSQL = "INSERT INTO [Customer Contact Details](Customer_Name, Customer_Address, Customer_Postcode, Customer_Phonenumber, Customer_Email ) Values('" & strCustomer_Name & "','" & strCustomer_Address & "','" & strCustomer_Postcode & "','" & strCustomer_Phonenumber & "','" & strCustomer_Email & "')

Response.Write(strSQL)

conn.execute(strSQL)

I don't get headaches.

pinkcat_02

02-06-2003, 08:43 PM

if i do what u told then it gives me the error:

Microsoft VBScript compilation error '800a0409'

Unterminated string constant

/anticus/fyp/customer details.asp, line 33

strSQL = "INSERT INTO [Customer Contact Details](Customer_Name, Customer_Address, Customer_Postcode, Customer_Phonenumber, Customer_Email ) Values('" & strCustomer_Name & "','" & strCustomer_Address & "','" & strCustomer_Postcode & "','" & strCustomer_Phonenumber & "','" & strCustomer_Email & "')

is that what u were expecting?

arnyinc

02-06-2003, 08:45 PM

Yes, that means there is a quotation mark missing at the end of that line that I didn't notice.

strSQL = "INSERT INTO [Customer Contact Details](Customer_Name, Customer_Address, Customer_Postcode, Customer_Phonenumber, Customer_Email ) Values('" & strCustomer_Name & "','" & strCustomer_Address & "','" & strCustomer_Postcode & "','" & strCustomer_Phonenumber & "','" & strCustomer_Email & "')"

pinkcat_02

02-06-2003, 08:50 PM

this is the result after doing the changes:

INSERT INTO [Customer Contact Details](Customer_Name, Customer_Address, Customer_Postcode, Customer_Phonenumber, Customer_Email ) Values('a','a','a','a','a')
Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query.

/anticus/fyp/customer details.asp, line 35

which points to:
conn.execute(strSQL)

hope this is the last problem :(

arnyinc

02-06-2003, 08:58 PM

Sorry I've never had that error before. If I was to guess, it sounds like a problem with permissions. You have to give everyone read/write access to the folder where your Access Database file is stored so that they can edit it. Other than that, it might have something to do with the database connection mode (which I don't personally use), but this microsoft article gives some details:

http://support.microsoft.com/default.aspx?scid=KB;en-us;q175168

pinkcat_02

02-06-2003, 09:19 PM

thanks vermy much

it is sorted out. thi problem was beacuse just one of my folder are allowed for reading and writing which is db and my database was in another folder on the server, now i carried it to that one and it is working fine...

brilliant:thumbsup: