Hi

I have made the following code, this file is opened when my HTML form is submitted. When run, it does go through it, and works without error BUT DOES NOT ADD ANYTHING TO THE DATABASE!

Please help


<%
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsAddComments 'Holds the recordset for the new record to be added
Dim strSQL 'Holds the SQL query to query the database
'Create an ADO connection object

Set adoCon = Server.CreateObject("ADODB.Connection")
'Set an active connection to the Connection object using a DSN-less connection

adoCon.Open "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=test;USER=***;PA SSWORD=***;OPTION=3;"
'Create an ADO recordset object

Set rsAddComments = Server.CreateObject("ADODB.Recordset")
'Initialise the strSQL variable with an SQL statement to query the database



strSQL ="SELECT tblUsers.User_Name, tblUsers.User_passwd, tblUsers.User_level, tblUsers.NAME, tblUsers.MAIL_ID, tblUsers.Place, tblUsers.COUNTRY FROM tblUsers;"

'Set the cursor type we are using so we can navigate through the recordset

rsAddComments.CursorType = 3

'Set the lock type so that the record is locked by ADO when it is updated

rsAddComments.LockType = 3
'Open the recordset with the SQL query

rsAddComments.Open strSQL, adoCon
'Tell the recordset we are adding a new record to it

strSQL="INSERT INTO tblUsers (User_Name,User_passwd,User_level,NAME,MAIL_ID,Pla ce,COUNTRY)"
strSQL=strSQL & " VALUES "
strSQL=strSQL & "('" & Request.Form("name") & "',"
strSQL=strSQL & "'" & Request.Form("comments") & "',"
strSQL=strSQL & "'" & ("= 1") & "',"
strSQL=strSQL & "'" & Request.Form("USERNAME") & "',"
strSQL=strSQL & "'" & Request.Form("MAIL_ID") & "',"
strSQL=strSQL & "'" & Request.Form("Place") & "',"
strSQL=strSQL & "'" & Request.Form("COUNTRY") & "')"



'Write the updated recordset to the database
rsAddComments.Update

'Reset server objects

rsAddComments.Close

Set rsAddComments = Nothing

Set adoCon = Nothing
'Redirect to the asp page
Response.Redirect "main.asp"

%>

THE HTML has the form:


User Name:
Password:
YOUR NAME:
Email:
CITY:
COUNTRY:

AND A SUBMIT BUTTON

Hai..

Do you get any error from the page..or does it execute without any error ?

Ok you can do one think, just print your query strSQL before calling the update method of recordset and then copy the query and execute it in the mysql client and see whether there is any error...

Hope this helps..Do let me know whether this solved your problem.

When this ASP file is run, there is no error, and as you see in the end of the ASP, it redirects to the new page with error.

But it does not add anything to the DATABASE.
I used the SQL code to place it straight in the MYSQL CLient without problem.
So i beleive it has something to do with the UPDATE part.

What do you mean by printing the strSQL before calling the update : do you mean placing some print code in the ASP?

Thanks
Suraj

--------------------------------------------------------------------------------

Hai..

Do you get any error from the page..or does it execute without any error ?

Ok you can do one think, just print your query strSQL before calling the update method of recordset and then copy the query and execute it in the mysql client and see whether there is any error...

Hope this helps..Do let me know whether this solved your problem.
__________________
With regards,
Sudar

--
My Blogs

My SQL Blog
Any thing Interesting
Personal

I meant to print the contents of the variable strSQL and then exit the script before calling the update method. And then copy the content of the strSQL variable and then execute in the MySQL client. By doing so u can findout if there is any problem with the formating the query(like mismatch in quotes etc..).

try that.. and do let me know what happened...

I am not sure have to go ahead with this. I ended the script before the update method, and dont know how to place strSQL on the client, because its in ASP. If i put the sql directly in, it has no problems and creates a record.

I am not sure how to go about the code above as i cant figure out the problem.

So i tried the following change in code and get the following error

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[MySQL][ODBC 3.51 Driver][mysqld-4.1.7-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.`tblUsers` ( User_Name,User_passwd,User_level,NAME,MAIL_ID,Plac

/logins/addnewuser.asp, line 34

<%
'Dimension variables
Dim adoCon 'Holds the Database Connection Object

Dim rsAddComments 'Holds the recordset for the new record to be added

Dim strSQL 'Holds the SQL query to query the database

'Create an ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=test;USER=xyz;PA SSWORD=***;OPTION=3;"

'Create an ADO recordset object
Set rsAddComments = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT tblUsers.User_Name, tblUsers.User_passwd, tblUsers.NAME, tblUsers.MAIL_ID, tblUsers.Place, tblUsers.COUNTRY FROM tblUsers;"

'Set the cursor type we are using so we can navigate through the recordset
rsAddComments.CursorType = 2
'Set the lock type so that the record is locked by ADO when it is updated
rsAddComments.LockType = 3

'Open the recordset with the SQL query
rsAddComments.Open strSQL, adoCon
'Tell the recordset we are adding a new record to it

rsAddComments.AddNew
'Add a new record to the recordset
rsAddComments.Fields("User_Name") = Request.Form("name")
rsAddComments.Fields("User_passwd") = Request.Form("comments")
rsAddcomments.Fields("NAME") = Request.Form("USERNAME")
rsAddcomments.Fields("MAIL_ID") = Request.Form("MAIL_ID")
rsAddcomments.Fields("Place") = Request.Form("Place")
rsAddcomments.Fields("COUNTRY") = Request.Form("COUNTRY")

'Write the updated recordset to the database
rsAddComments.Update

'Reset server objects
rsAddComments.Close
Set rsAddComments = Nothing
Set adoCon = Nothing
'Redirect to the guestbook.asp page
Response.Redirect "main.asp"

%>

As per the above, i did the following after the sql

Response.Write strSQL

and got

INSERT INTO tblUsers (User_Name,User_passwd,NAME,MAIL_ID,Place,COUNTRY) VALUES ('s1','s2','s3','s4','s5','s5')

Which when placed in mysql client creates a record