ASP Error

Access DB Execute error

[brett2_UMBC]

I am using this code:

<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/umbcems.asp" -->
<%
' *** Edit Operations: declare variables
Dim MM_editAction
Dim MM_abortEdit
Dim MM_editQuery
Dim MM_editCmd
Dim MM_editConnection
Dim MM_editTable
Dim MM_editRedirectUrl
Dim MM_editColumn
Dim MM_recordId
Dim MM_fieldsStr
Dim MM_columnsStr
Dim MM_fields
Dim MM_columns
Dim MM_typeArray
Dim MM_formVal
Dim MM_delim
Dim MM_altVal
Dim MM_emptyVal
Dim MM_i
MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
 MM_editAction = MM_editAction & "?" & Request.QueryString
End If
' boolean to abort record edit
MM_abortEdit = false
' query string to execute
MM_editQuery = ""
%>
<%
' *** Redirect if username exists
MM_flag="MM_insert"
If (CStr(Request(MM_flag)) <> "") Then
 MM_dupKeyRedirect="user_error.asp"
 MM_rsKeyConnection=MM_umbcems_STRING
 MM_dupKeyUsernameValue = CStr(Request.Form("username"))
 MM_dupKeySQL="SELECT user_name FROM user_pass WHERE user_name='" & MM_dupKeyUsernameValue & "'"
 MM_adodbRecordset="ADODB.Recordset"
 set MM_rsKey=Server.CreateObject(MM_adodbRecordset)
 MM_rsKey.ActiveConnection=MM_rsKeyConnection
 MM_rsKey.Source=MM_dupKeySQL
 MM_rsKey.CursorType=0
 MM_rsKey.CursorLocation=2
 MM_rsKey.LockType=3
 MM_rsKey.Open
 If Not MM_rsKey.EOF Or Not MM_rsKey.BOF Then 
 ' the username was found - can not add the requested username
 MM_qsChar = "?"
 If (InStr(1,MM_dupKeyRedirect,"?") >= 1) Then MM_qsChar = "&"
 MM_dupKeyRedirect = MM_dupKeyRedirect & MM_qsChar & "requsername=" & MM_dupKeyUsernameValue
 Response.Redirect(MM_dupKeyRedirect)
 End If
 MM_rsKey.Close
End If
%>
<%
' *** Insert Record: set variables
If (CStr(Request("MM_insert")) = "Add") Then
 MM_editConnection = MM_umbcems_STRING
 MM_editTable = "user_pass"
 MM_editRedirectUrl = "add_success.asp"
 MM_fieldsStr = "name¦value¦email¦value¦username¦value¦pass¦value"
 MM_columnsStr = "Name¦',none,''¦[Email Address]¦',none,''¦user_name¦',none,''¦password¦',none,''"
 ' create the MM_fields and MM_columns arrays
 MM_fields = Split(MM_fieldsStr, "¦")
 MM_columns = Split(MM_columnsStr, "¦")
 
 ' set the form values
 For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
 MM_fields(MM_i+1) = CStr(Request.Form(MM_fields(MM_i)))
 Next
 ' append the query string to the redirect URL
 If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then
 If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString <> "") Then
  MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
 Else
  MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
 End If
 End If
End If
%>
<%
' *** Insert Record: construct a sql insert statement and execute it
Dim MM_tableValues
Dim MM_dbValues
If (CStr(Request("MM_insert")) <> "") Then
 ' create the sql insert statement
 MM_tableValues = ""
 MM_dbValues = ""
 For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
 MM_formVal = MM_fields(MM_i+1)
 MM_typeArray = Split(MM_columns(MM_i+1),",")
 MM_delim = MM_typeArray(0)
 If (MM_delim = "none") Then MM_delim = ""
 MM_altVal = MM_typeArray(1)
 If (MM_altVal = "none") Then MM_altVal = ""
 MM_emptyVal = MM_typeArray(2)
 If (MM_emptyVal = "none") Then MM_emptyVal = ""
 If (MM_formVal = "") Then
  MM_formVal = MM_emptyVal
 Else
  If (MM_altVal <> "") Then
  MM_formVal = MM_altVal
  ElseIf (MM_delim = "'") Then ' escape quotes
  MM_formVal = "'" & Replace(MM_formVal,"'","''") & "'"
  Else
  MM_formVal = MM_delim + MM_formVal + MM_delim
  End If
 End If
 If (MM_i <> LBound(MM_fields)) Then
  MM_tableValues = MM_tableValues & ","
  MM_dbValues = MM_dbValues & ","
 End If
 MM_tableValues = MM_tableValues & MM_columns(MM_i)
 MM_dbValues = MM_dbValues & MM_formVal
 Next
 MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & ") values (" & MM_dbValues & ")"
 If (Not MM_abortEdit) Then
 ' execute the insert
 Set MM_editCmd = Server.CreateObject("ADODB.Command")
 MM_editCmd.ActiveConnection = MM_editConnection
 MM_editCmd.CommandText = MM_editQuery
 MM_editCmd.Execute
 MM_editCmd.ActiveConnection.Close
 If (MM_editRedirectUrl <> "") Then
  Response.Redirect(MM_editRedirectUrl)
 End If
 End If
End If
%>

For someodd reason I get this error in IE:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query.
/umbcems/add_member.asp, line 141

Line 141 is the MM_editCmd.Execute line. Anyone have any ideas as to why this is causing an error?

[ziggystardust]

This is most likely because you don't have write access to the database...

Right-click on the mdbfile, "Properties", "Security" and add "Everyone" to the list of permissions and it should work.

If all you have is an ftp account on a remote server, there's a chance the host set you up with a DB-directory, with all the perms set. If yes, then put your mdb-file in that dir.
I don't know if this is common practise amongst hosts tho. If you don't have one, ask them to make you one.

Good luck
//ZS

[duckhunter]

Here's the MSDN article on it: [support.microsoft.com ]

Says it could also be the connection mode (Conn.Mode = 3 '3 = adModeReadWrite)

[brett2_UMBC]

Thanks for the help guys, but I'm not getting anywhere. I read over the MSDN file, and tried everything and got nowhere. Although the connection thing didn't work, I can't find a "security" tab when I right click the file. I'm using XP. I tried in both Windows Explorer, IE, WS_FTP, and Macromedia Dreamweaver. Any help would be great.

[duckhunter]

In Windows Explorer, right click on the .mdb file then click Properties. You should then see a security tab listing users. Add the IUSR_<servername> and give R/W/C permissions

You also might try temporarily commenting out the Redirect and:
Response.Write "SQL: " & MM_editQuery '--to make sure your executing what you think you are.

[brett2_UMBC]

Ok, under windows Explorer, I right click the .mdb file, and there are two tabs "general" and "Summary". In the Advanced button there's just compression & archive and index options. That's it. Is there a way to make the security tab pop-up? Thanks for the help.

~Brett

[duckhunter]

Are you logged on as an Administrator of the box?

[brett2_UMBC]

I have administration rights in XP.

[aspdaddy]

Most likely permissions as mentioned already, but other times I have seen this -

When the db is read only, i.e if its been copied from a CD to the drive. Right click db and check.

If the column cant be updated because of a foreign key constraint or similar. Try a different query?

[duckhunter]

Can you post the editQuery so we can see the SQL statement?

Response.Write "SQL: " & MM_editQuery

[brett2_UMBC]

Here's the Edit Query:
MM_editQuery = ""

I've posted the code itself online:
userpages.umbc.edu/~brett2/addmemberASP.html

If I post it on here it would be too long.

~Brett

[edited by: Xoc at 3:12 pm (utc) on April 22, 2003]
[edit reason] delinked [/edit]

[duckhunter]

OK so MM_editQuery = "". There's nothing to execute. I'll take look at the code a little later. The kids are pulling me outside.

[duckhunter]

I think I have narrowed down what you are trying to do. Assuming MM_fieldsStr below is the array of request variables and you want to loop through these arrays building a syntacticaly correct insert statement. I think you overcomplicated a few things. I dropped the "none" and Step 2 stuff which got a little messy. Try this loop and see if it works.

One thing. Where are you opening your Connection?

This query string on my box:?fullname=duckhunter&email=myemailaddress&username=ducksUserName&passwd=quack&MM_insert=add

Produces this: MM_editQuery: insert into user_pass (Name, [Email Address], user_name, password) values ('duckhunter', 'myemailaddress', 'ducksUserName', 'quack')

<%
' *** Insert Record: set variables
If (CStr(Request("MM_insert")) = "add") Then
MM_editConnection = MM_umbcems_STRING
MM_editTable = "user_pass"
MM_editRedirectUrl = "add_success.asp"

'List of RequestVariables
fieldsStr = "fullname¦email¦username¦passwd"

'List of DB Column Names
columnsStr = "Name¦[Email Address]¦user_name¦password"

MM_fieldsStr = Split(fieldsStr, "¦")
MM_columnsStr = Split(columnsStr, "¦")

'Now we have our Arrays. Loop through the RequestVariables named MM_fields(x)
For x = 0 to UBound(MM_fieldsStr)
If x = 0 then
MM_tableValues = MM_columnsStr(x)
MM_dbValues = "'" & Request(MM_fieldsStr(x)) & "'"
Else
MM_tableValues = MM_tableValues & ", " & MM_columnsStr(x)
MM_dbValues = MM_dbValues & ", '" & Replace(Request(MM_fieldsStr(x)),"'", "''") & "'"
End If
Next

MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & ") values (" & MM_dbValues & ")"

Response.Write "MM_editQuery: " & MM_editQuery

End If
%>

[brett2_UMBC]

Ok, so I added your code. Problem is I get this error in IE:

Error Type:
Microsoft VBScript compilation (0x800A0411)
Name redefined
/umbcems/add_member.asp, line 42, column 4

Any help would be great. The code is linked in a previous post.

I think that I open the connection with this line:

MM_editConnection = MM_umbcems_STRING

in the ' ***Insert Record: Set Variables area of the code.

~Brett

[duckhunter]

You have the DIM variable blocks in there twice.

Dim MM_editAction
Dim MM_abortEdit
Dim MM_editQuery
Dim MM_editCmd
Dim MM_editConnection
Dim MM_editTable
Dim MM_editRedirectUrl
Dim MM_editColumn
Dim MM_recordId
Dim MM_fieldsStr
Dim MM_columnsStr
Dim MM_fields
Dim MM_columns
Dim MM_typeArray
Dim MM_formVal
Dim MM_delim
Dim MM_altVal
Dim MM_emptyVal
Dim MM_i

You're still doing the old Step 2 stuff. Let's stick to the basics right now and once you get it to work, then put in the traps.

[brett2_UMBC]

Ok, so I need to take out the step 2 stuff, and what with the DIM's?

I updated the script on the page:

userpages.umbc.edu/~brett2/addmemberASP.html

Thanks.

~Brett

{if you haven't figured it out yet, I am very new to ASP}

[edited by: Xoc at 3:12 pm (utc) on April 22, 2003]
[edit reason] delinked [/edit]

[duckhunter]

No problem. Glad to pass it along.

Now you need to remove your old code for now, it's running it after the new stuff and overwriting the variables. I know you're doing some redirect stuff afterwards but don't worry about it for now. Let's get the insert to work.

Remove the old code from:
' *** Insert Record: construct a sql insert statement and execute it
TO:
MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & ") values (" & MM_dbValues & ")"

What is the page resulting in now?

[brett2_UMBC]

Do you want me to take out everything between <% %> and replace it with just
<%
MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & ") values (" & MM_dbValues & ")"
%>
? Thanks

In deleting all the code and leaving the above aforementioned section, it just showed it at the top line of the page as the values:

MM_editQuery = insert into UMBCEMS (name,email_address,username,password) values (Brett,[e-mail],brett2_UMBC,this-sux)

Thanks for the help.

~Brett

[duckhunter]

OK, pretty good. At least we got the SQL string but it looks like we're missing the quotes around the values. Can you check or update the code on your site?

[brett2_UMBC]

Sorry it took so long... Had trouble...

userpages.umbc.edu/~brett2/addmemberASP.html

~Brett

[edited by: Xoc at 3:13 pm (utc) on April 22, 2003]
[edit reason] delinked [/edit]

[duckhunter]

Now let's open a connection and try to execute. Notice the On error resume next and error trap. This is so the user doesn't see an ugly ADO native error page.

'Add this after: Response.Write "MM_editQuery: " & MM_editQuery

On Error Resume Next
blnError = False
Set DBConn = Server.CreateObject("ADODB.CONNECTION")
DBConn.ConnectionString = MM_editConnection
DBConn.Open

DBConn.Execute(MM_editQuery)

If DBConn.Errors.Count > 0 Then
If DBConn.Errors.Item(0).Number <> 0 Then
blnError = True
Response.Write "<BR>Error: " & DBConn.Errors.Item(0).Description
End If
End if

DBConn.Close
Set DBConn = Nothing

If Not blnError Then
Response.Write "<BR>Successful. Do your redirect Now
End If

[brett2_UMBC]

Ok, so I did as you instructed. My new code will be posted by 12:45 am. Here's the output:

MM_editQuery: insert into user_pass (name, email_address, user_name, password) values ('Brett Patterson', '[e-mail]', 'brett3', 'wcvfd14')
Error: [Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query.

Thanks for the help.

I was told by the Server Owner that Dreamweaver can do all the stuff itself, all I have to do is use teh Server Script stuff. I tried taht and got the exact same error as that one. Do I need a query in MS Access? I didn't think I did. Thanks for the help.

~Brett

[duckhunter]

Does that record already exist in the DB? (ie: same Primary key)

[brett2_UMBC]

Does what record exist? The query? Yes I have a query named umbcems_user_query and there is no primary key in the database.

duckhunter, if you have IM, please sticky me your SN and we could prob do a lot better than just this board.

~Brett

[ziggystardust]

Not code related, just about that security tab in Windows XP...

...it *should* be enabled by default if your computer is part of a network or domain. If it's not, then try doing this:

To reveal the Security tab, open Windows Explorer, and choose Folder Options from the Tools menu. On the View tab, scroll to the bottom of the Advanced Settings and clear (click) the check box next to "Use Simple File Sharing." Click OK to apply the change, and you should now have a Security tab when viewing the properties of a file on an NTFS volume.
(from about.com)

Then give "Everyone" perms to the mdb-file.

//ZS

[brett2_UMBC]

Thanks guys that worked. I got it to update the database; however the page takes oogles of time to load. It seems to be hanging somewhere. IF you could tell me where that would be fantastic. The code is online:

userpages.umbc.edu/~brett2/addmemberASP.html

Thanks for any help you suggest. Thanks ZS, that was it.

~Brett

[edited by: Xoc at 3:13 pm (utc) on April 22, 2003]
[edit reason] delinked [/edit]

[duckhunter]

I would suggest putting a few Response.Write "Time: " & Now() throughout the page before and after Database Operations.

Access is not the fastest DB on the planet and using DSN's is even slower. Try a DSN-less connection. It's probably taking a long time to connect is my guess. Looks like your using an implied connection by passing the string to the ActiveConnection property. That would be better suited to pass an actual connection object (ADODB.CONNECTION)

[brett2_UMBC]

Well, I figured it out. In the page that loaded after they submitted the form, I had an email script that also called the form. That email script was causing problems. Now it works; however I can't get email replys. Is there any way to incorporate an email script other than what I have? Here's what I have:

Add member page with form and DB update script
¦
¦
v
Add member success page with form
¦
¦
v
You've been added page that submits an email.

Thanks for any help you can offer.

[duckhunter]

Add this function to your success page and call SendMail with the parameters after a successful insert. This is ASPMail. No need to add another redirect. Redirects are messy. You can do everything you're trying to do in one page.

Function SendMail(strFrom, strTo, strFromAddress, strSubject, strBody)
On error resume next
Set Mailer = Server.CreateObject("SMTPsvg.Mailer")
Mailer.FromName = strFrom
Mailer.FromAddress= strFromAddress
Mailer.RemoteHost = "your.mailserver.here"
Mailer.AddRecipient strTo, strTo
Mailer.Subject = strSubject
Mailer.BodyText = strBody

if Mailer.SendMail then
SendMail = True
else
SendMail = False
end if

Set Mailer = nothing
End Function

P.S. You should be error checking the Database operations or you will not know if your insert was successful or not. You might want to be emailing yourself when the database execution failed.

[brett2_UMBC]

If I use that code, what is wrong with this one? What is the difference? Is it possible to write the values from the form to be used in two different scripts? I would rather use the script that i have already:

<%
'Gets Form Input
'textarea input, for body of message.
Dim strName, strEmail, strUser, strPass
strName = Request.Form("fullname")
strEmail = Request.Form("email")
strUser = Request.Form("username")

'Backup validates the fields if JavaScript failed and redirects back to the form if they're empty
If strName="" or strUser="" or strEmail="" Then Response.Redirect("add_success.asp") End If

'Fire off the email
Dim objMail
Set objMail = CreateObject("CDONTS.NewMail")
With objMail
.From = strName (strEmail)
.To = "[e-mail]"
.Subject = "[UMBC EMS] Add Member " & strName & " [UMBC EMS]"
.Body = "Name: " & strName & vbCrLf & "Email: " & strEmail & vbCrLf & vbCrLf & "User: " & strUser & vbCrLf & vbCrLf & Date() & " at " & Time()
.Send
End With
Set objMail = Nothing
%>

That calls the form though. If I could put this in the add_success.asp page (the second one) that would be better because I would rather it be automated rather than having them input their info twice. Also, should I just say that:

strFrom = fullname
etc. etc. etc.

Thanks for the help.

~Brett