ASP Add/Update/Delete [Recordset err]

Need Help

[Shuvi]

I keep getting this error
Error Type:
ADODB.Recordset (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

And here is my code:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
Option Explicit
Response.Expires = 0
%>
<%
<!--#include file="../../common/include/ADOVBS.INC" -->
Dim objConn, WebcastMail, strQuery, objRS
Dim UserId, FirstName, LastName, UserName, Password, FromDate, Users
Dim adOpenKeySet, adLockOptimistic, adCmdTable

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = Application("WebcastMail")
objConn.Open

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open "UPDATE Users SET FirstName = '" & FirstName & "', " & "LastName = '" & LastName & "', " & "UserName = '" & UserName + "', " & "Password = '" & Password & "', " & "FromDate = #" & FromDate & "# ", objConn, adOpenKeySet, adLockOptimistic, adCmdTable
objRS.AddNew
UserId = Request.Form("UserID")
objRS("FirstName") = Request.Form("FirstName")
objRS("LastName") = Request.Form("LastName")
objRS("UserName") = Request.Form("UserName")
objRS("Password ")= Request.Form("Password")
objRS("FromDate") = Request.Form("FromDate")
objRS.Update
UserId = objRS("UserID")

objRS.Close()
objConn.Close()
%>

Can anyone help me? I really need this to work.
Thanks, Shuvi

[txbakers]

Comment out your update commands and put a Response.Write so you can see the actual SQL statement generated before it goes to the DB. Most likely you'll find the syntax error in the code.

Also, you don't need the concantenation for each of the field names:

objRS.Open "UPDATE Users SET FirstName = '" & FirstName & "', LastName = '" & LastName & "', UserName = '" & UserName + "', Password = '" & Password & "', FromDate = #" & FromDate & "# ", objConn, adOpenKeySet, adLockOptimistic, adCmdTable

[RossWal]

Welcome to WebmasterWolrd Shuvi!

In addition to what txbakers says, have a look at your date formats. Try to get the SQL working in a query tool, then move it into asp.

HTH,
Ross

[Shuvi]

I tried it in Access Query and it ran fine.
Here's what I've got after a few changes.
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = Application("WebcastMail")
objConn.Open
UserId = Request.Form("UserID")
FirstName = Request.Form("FirstName")
LastName = Request.Form("LastName")
UserName = Request.Form("UserName")
Password = Request.Form("Password")
FromDate = Request.Form("FromDate")

strQuery = "UPDATE Users SET FirstName = '" + FirstName + "', " + "LastName = '" + LastName + "', " + "UserName = '" + UserName + "', " + "Password = '" + Password + "', " + "FromDate = '" + FromDate + "' "
strQuery = strQuery + "where UserID = " + UserId + ";"
on error resume next
objConn.Execute strQuery
if err<>0 then
response.Write("No update permissions!")
else
response.Write("Record " & UserId & " was updated!")
end if

And now that I'm error checking it give me the "No update permissions!" message.
Still no clue what I'm doing wrong.
Thanks for the help!

[mattglet]

instead of response.write("no update permissions"), do:
response.write err.message

this will give you the exact cause of the problem, then we can go from there.

-Matt

[Shuvi]

I did the err.description and got the following error:
Syntax error in UPDATE statement.

And here is my update statement:

strQuery = "UPDATE Users SET FirstName = '" & FirstName & "', " & "LastName = '" & LastName & "', " & "UserName = '" & UserName & "', " & "Password = '" & Password & "', " & "FromDate = '" & FromDate & "' "
strQuery = strQuery & "where UserID = " & UserId & ";"

When I print on the screen I get:

UPDATE Users SET FirstName = 'SHUVI', LastName = 'POWERS', UserName = 'SPOWERS', Password = 'SPOWERS', FromDate = '12/9/2003 7:55:52 AM' where UserID = 1;

I also tried putting # around the date parameter, and still got the same error.
Thoughts?

[txbakers]

with access you need to have write permissions on the MDB file itself.

that might explain the error also. The query looks good, but you will need the # around the dates for access.

[Shuvi]

Which permissions? Table permission? My database permission have full access to users.

I'm not sure I understand.

[txbakers]

permission on the database file itself, not to get into the database.

[Shuvi]

How do I do that? I went to the properties for the mdb file and made sure that all users had full control. Is there something else I should do?

[Shuvi]

Now I don't get any errors and it acts like it's updating but when I go to the database there's no change.
I tried the insert and got the same results.
Any suggestions?
I know my database connection works because I'm able to view the table on my view page.

I'd really appreciate any help I could get.

[RossWal]

Are you able to do the update via SQL when you're not in Access?

I don't know Access syntax so I can't help there. Can you set up a simple table with just char or numeric data and see if you can update that from ASP? Or make the date column nullable and try your update without the date.

If you page is set-up in IIS as "Allow Anonymous" the I think the user hitting the database is IIS_USER, if that means anything.

Good Luck.

[Shuvi]

Thanks for all the good advice.
I got it work. It turns out that I needed to have [] around the word Password. Must be a reserved word or something. But it works like a charm now.

Thanks,
Shuvi