Inserting using ADO in ASP

[whyiswyh]

I have a question about inserting/... in ADO for database in ASP for web programming. I sometimes get the result I wanted but after that when it comes to the real implementation it gets screwed up, it won't allow me to update. Did I miss out on some important steps like resetting the database or purely there is something amiss with my PC or more probably my skills. I am a beginner. Is it me or the settings?

This is my coding:
<%

set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "C:\Inetpub\wwwroot\rayu\rayu.mdb"


' Compose the INSERT statement.
sql = "INSERT INTO gbook " & _
"(username, fname, password, cpassword, email, cemail, country, comment) " & _
" VALUES (" & _
"'" & Request.Form("username") & "', " & _
"'" & Request.Form("fname") & "', " & _
"'" & Request.Form("password") & "', " & _
"'" & Request.Form("cpassword") & "', " & _
"'" & Request.Form("email") & "', " & _
"'" & Request.Form("cemail") & "', " & _
"'" & Request.Form("country") & "', " & _
"'" & Request.Form("comment") & "'" & _
")"

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

[brazenskies]

do you get any errors?

Also you'll need to post the html for your form

[whyiswyh]

Well it didn't work out for all my SQL statements I think.I threw out the error handling and I got this:

"Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in UPDATE statement.
/busana/demo_db_submit.asp, line 25"

I even tried using the debugging technique by adding these two lines

"response.write sql
response.end"

and got this string

(UPDATE guestbook SET Edit/Delete='1', First name='Ali', Last name='11ffdgf', Username='fgffd', Password='dsfsd', Email='ersf', country='dfsf', dateadded='3/27/2009 11:49:17 PM' WHERE guestbook.[Edit/Delete]=1")
Is there anything wrong with the update statement?

[brazenskies]

I think it's because you have spaces in your field names.

Is it an access database? If so try adding square brackets around your field names

[brazenskies]

that isn't the sql string in your first post though, what happens when you write it to the screen?

[whyiswyh]

Yup...I got a little lost there coz they have the same problem.Here's the printed statement for INSERT:

INSERT INTO gbook (username, fname, password, cpassword, email, cemail, country, comment) VALUES ('', '', '', '', '', '', '', '')

[whyiswyh]

It is kind of solved now.I better check back my UPDATE statement as well.
It is not reading the first_name and last_name

UPDATE guestbook SET First_name='', Last_name='', Username='fgffd', Password='dsfsd', Email='ersf', country='dfsf', dateadded='3/28/2009 1:29:25 AM' WHERE guestbook.[no]=1

[Old Pedant]

PASSWORD is a keyword in Access SQL. You must put [...] around it.

It can never hurt to put [...] around any field name, so always do so when in doubt.

As for this output:

INSERT INTO gbook (username, fname, password, cpassword, email, cemail, country, comment) VALUES ('', '', '', '', '', '', '', '')

*CLEARLY* there is something wrong in your <FORM> on the prior page and/or in your use of Request.Form on this page.

As somebody else posted, show the <FORM> code, too. At least the actual <FORM> line.

[brazenskies]

are you using method="get" in your form? If so then you'd need to either change it to POST to use request.form

[Old Pedant]

Or you can just use

Request("username")
et al. If you don't specify querystring or form, ASP automatically searches all the possible Request collections for a match:
-- Request.QueryString
-- Request.Form
-- Request.Cookies
-- Request.ServerVariables
-- Request.ClientCertificates
I think that's t he right order, but if not it's in the IIS documentation. In any case I know it's querystring and then form. And ASP stops when it finds the first match, if any.

[whyiswyh]

Hmm..ya...actually I found the solution and it was really the SQL statement. I did some edited and it worked.Thank branz$..and $man$.Sorry for not replying earlier

[shakir]

I have a question about inserting/... in ADO for database in ASP for web programming. I sometimes get the result I wanted but after that when it comes to the real implementation it gets screwed up, it won't allow me to update. Did I miss out on some important steps like resetting the database or purely there is something amiss with my PC or more probably my skills. I am a beginner. Is it me or the settings?

This is my coding:
<%

set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "C:\Inetpub\wwwroot\rayu\rayu.mdb"


' Compose the INSERT statement.
sql = "INSERT INTO gbook " & _
"(username, fname, password, cpassword, email, cemail, country, comment) " & _
" VALUES (" & _
"'" & Request.Form("username") & "', " & _
"'" & Request.Form("fname") & "', " & _
"'" & Request.Form("password") & "', " & _
"'" & Request.Form("cpassword") & "', " & _
"'" & Request.Form("email") & "', " & _
"'" & Request.Form("cemail") & "', " & _
"'" & Request.Form("country") & "', " & _
"'" & Request.Form("comment") & "'" & _
")"

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

Check the database security if NTFS file system...put all for every one or iwam user

[brazenskies]

Or you can just use

Request("username")
et al. If you don't specify querystring or form, ASP automatically searches all the possible Request collections for a match:
-- Request.QueryString
-- Request.Form
-- Request.Cookies
-- Request.ServerVariables
-- Request.ClientCertificates
I think that's t he right order, but if not it's in the IIS documentation. In any case I know it's querystring and then form. And ASP stops when it finds the first match, if any.

True, but I don't think it's the most efficient way of doing things

[Old Pedant]

Brazen: Yes, you pay a minor performance penalty. A few microseconds. Because if the field is actually in Request.Form then you force it to search Request.QueryString first, finding nothing.

The worst offense is when you use Request("xxx") and it's in none of the collections.

But personally, I like using METHOD=GET when I am developing a page, so that I can look in the URL to be sure the right info is being passed. Then I change to METHOD=POST when ready to deploy. For me, using Request("xxx") thus provides the ideal solution. I wouldn't use it on a really really busy page on a really really busy site. But how many such pages do you, personally, work on? For me, only a handful now and then.

[brazenskies]

i actually work on 2 such sites currently so try to avoid using request("xxx") but as you said, I'm sure it wouldn't degrade the system too much