bfider2004
asked on
Check new username combined with Insert record behavior not working in ASP... Stuck!
Can't get this! I have a form for users to create login info that inserts in to Access database. This works fine on its own. When I try to add DWMX's check new username behavior, I get an error—just when a unique username is entered. If I try a username that already exists in the DB, it redirects just fine.
The error is:
Microsoft JET Database Engine error '80004005'
Could not use ''; file already in use.
/bonedisease/login/create.
I think it is a opening/closing connection thing, but I don't know enough to see what I need to do. The code is below, and I've noted line 142...
Getting urgent..
--
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../Connections/connB
<%
' *** 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.ServerVariabl
If (Request.QueryString <> "") Then
MM_editAction = MM_editAction & "?" & Server.HTMLEncode(Request.
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="emailex
MM_rsKeyConnection=MM_conn
MM_dupKeyUsernameValue = CStr(Request.Form("Email")
MM_dupKeySQL="SELECT Email FROM tblLogin WHERE Email='" & MM_dupKeyUsernameValue & "'"
MM_adodbRecordset="ADODB.R
set MM_rsKey=Server.CreateObje
MM_rsKey.ActiveConnection=
MM_rsKey.Source=MM_dupKeyS
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
MM_dupKeyRedirect = MM_dupKeyRedirect & MM_qsChar & "requsername=" & MM_dupKeyUsernameValue
Response.Redirect(MM_dupKe
End If
MM_rsKey.Close
End If
%>
<%
' *** Insert Record: set variables
If (CStr(Request("MM_insert")
MM_editConnection = MM_connBone_STRING
MM_editTable = "tblLogin"
MM_editRedirectUrl = "welcome.asp"
MM_fieldsStr = "FirstName|value|LastName|
MM_columnsStr = "FirstName|',none,''|LastN
' 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_field
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")
Session("MM_Username") = Request("Email")
' 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
MM_editCmd.ActiveConnectio
MM_editCmd.CommandText = MM_editQuery
MM_editCmd.Execute
MM_editCmd.ActiveConnectio
If (MM_editRedirectUrl <> "") Then
Response.Redirect(MM_editR
End If
End If
End If
%>
The error is:
Microsoft JET Database Engine error '80004005'
Could not use ''; file already in use.
/bonedisease/login/create.
I think it is a opening/closing connection thing, but I don't know enough to see what I need to do. The code is below, and I've noted line 142...
Getting urgent..
--
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../Connections/connB
<%
' *** 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.ServerVariabl
If (Request.QueryString <> "") Then
MM_editAction = MM_editAction & "?" & Server.HTMLEncode(Request.
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="emailex
MM_rsKeyConnection=MM_conn
MM_dupKeyUsernameValue = CStr(Request.Form("Email")
MM_dupKeySQL="SELECT Email FROM tblLogin WHERE Email='" & MM_dupKeyUsernameValue & "'"
MM_adodbRecordset="ADODB.R
set MM_rsKey=Server.CreateObje
MM_rsKey.ActiveConnection=
MM_rsKey.Source=MM_dupKeyS
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
MM_dupKeyRedirect = MM_dupKeyRedirect & MM_qsChar & "requsername=" & MM_dupKeyUsernameValue
Response.Redirect(MM_dupKe
End If
MM_rsKey.Close
End If
%>
<%
' *** Insert Record: set variables
If (CStr(Request("MM_insert")
MM_editConnection = MM_connBone_STRING
MM_editTable = "tblLogin"
MM_editRedirectUrl = "welcome.asp"
MM_fieldsStr = "FirstName|value|LastName|
MM_columnsStr = "FirstName|',none,''|LastN
' 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_field
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")
Session("MM_Username") = Request("Email")
' 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
MM_editCmd.ActiveConnectio
MM_editCmd.CommandText = MM_editQuery
MM_editCmd.Execute
MM_editCmd.ActiveConnectio
If (MM_editRedirectUrl <> "") Then
Response.Redirect(MM_editR
End If
End If
End If
%>
ASKER
Yeah, all the research I've done on the error has pointed to problems with permissions. I'm pretty certain this is not a permissions issue. The record insertion works fine when I don't put that "Check New Username" behavior in. When I do add the "Check New Username" behavior, I get the error—but only when the username is NOT unique. The error points to the insert record code after the "Check New Username" code. (which comes at:
MM_editCmd.ActiveConnectio
)
I'm bound and determined to figure this out. Thanks for your help/
MM_editCmd.ActiveConnectio
)
I'm bound and determined to figure this out. Thanks for your help/
You are getting this error because the connection to the db is already open.
If you check the directory where the db is you will find a file named the same as the db but with a .ldb extension. This is what is causing your problem.
At the end of your page be sure you are closing the connection to the db and this error will go away...
If you check the directory where the db is you will find a file named the same as the db but with a .ldb extension. This is what is causing your problem.
At the end of your page be sure you are closing the connection to the db and this error will go away...
Well, in this part:
If Not MM_rsKey.EOF Or Not MM_rsKey.BOF Then
' the username was found - can not add the requested username
What is MM_rsKey.EOF and MM)rsKey.BOF? I don't see them getting set...
CoolATIGuy
If Not MM_rsKey.EOF Or Not MM_rsKey.BOF Then
' the username was found - can not add the requested username
What is MM_rsKey.EOF and MM)rsKey.BOF? I don't see them getting set...
CoolATIGuy
This page has info on databasing in ASP, including closing connections: http://www.tnsites.com/support/s_databaseconn.asp
CoolATIGuy
CoolATIGuy
ASKER
alexhogan - Like this? I can't tell if DW is opening a connection and not closing it, unbeknownst to me... at this point, I don't know a db connection from a pigskin.
</hml>
<%
MM_editConnection .Close()
Set MM_editConnection = Nothing
%>
</hml>
<%
MM_editConnection .Close()
Set MM_editConnection = Nothing
%>
ASKER
CoolATIGuy -
If Not MM_rsKey.EOF Or Not MM_rsKey.BOF Then
' the username was found - can not add the requested username
This is the DW added code for the "Check New Username" behavior... I've not had this prob with any similar DW code. Not saying it's not possible...
If Not MM_rsKey.EOF Or Not MM_rsKey.BOF Then
' the username was found - can not add the requested username
This is the DW added code for the "Check New Username" behavior... I've not had this prob with any similar DW code. Not saying it's not possible...
Make Sure All connections are Close to Database.
in your Asp Page on the Bottom Always Close the Connection like this..
Conn.Close
Set Conn = Nothing
And See if a Instance of MS Access is Already open.
if nothing helps, Restart your Computer.
in your Asp Page on the Bottom Always Close the Connection like this..
Conn.Close
Set Conn = Nothing
And See if a Instance of MS Access is Already open.
if nothing helps, Restart your Computer.
ASKER
I'll try this:
Conn.Close
Set Conn = Nothing
All this is happening on a remote server, so it's not that I have the database open, or anything. It must be an unclosed connection. If my connection is called connBone would I put:
connBone.Close
Set connBone = Nothing
Conn.Close
Set Conn = Nothing
All this is happening on a remote server, so it's not that I have the database open, or anything. It must be an unclosed connection. If my connection is called connBone would I put:
connBone.Close
Set connBone = Nothing
[snip]
connBone.Close
Set connBone = Nothing
[/snip]
Correct...
What you are doing is setting the objects method to close and then distroying the instance.
connBone.Close
Set connBone = Nothing
[/snip]
Correct...
What you are doing is setting the objects method to close and then distroying the instance.
ASKER
I put this at the end of the page:
<%
connBone.Close
Set connBone = Nothing
%>
and not only is it not working, but I'm getting error message at the bottom of the page:
Microsoft VBScript runtime error '800a01a8'
Object required: ''
/bonedisease/login/create.
--
Since I can't find any documentation on problems similar to this one, it is obviously only happening to me.
The form inserts into the database just fine on its own. But when I combine this with the check new username, i get the errors. I've never had DW open a connection and not close it by itself. This is pissing me off...
<%
connBone.Close
Set connBone = Nothing
%>
and not only is it not working, but I'm getting error message at the bottom of the page:
Microsoft VBScript runtime error '800a01a8'
Object required: ''
/bonedisease/login/create.
--
Since I can't find any documentation on problems similar to this one, it is obviously only happening to me.
The form inserts into the database just fine on its own. But when I combine this with the check new username, i get the errors. I've never had DW open a connection and not close it by itself. This is pissing me off...
ASKER
This appears to be where the Check New Username code is, and this appears to close it, correct?:
MM_rsKey.Close
Then in the Insert record code, I see this line:
MM_editCmd.ActiveConnectio
Very confused, perhaps I'm in over my head...
MM_rsKey.Close
Then in the Insert record code, I see this line:
MM_editCmd.ActiveConnectio
Very confused, perhaps I'm in over my head...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
while my code was reading:
MM_rsKey.Close
this apparantly was not enough. i added Set MM_rsKey = Nothing after it, and it worked. halelluia, it works....
thank you!
MM_rsKey.Close
this apparantly was not enough. i added Set MM_rsKey = Nothing after it, and it worked. halelluia, it works....
thank you!
CoolATIGuy