yeggstry
asked on
Database Problem
Hi there
I am fairly new to ASP, and I am stuck on this very annoying problem I have.
The following code:
<%@ Language=VBSCRIPT %>
<% Option Explicit %>
<!--#include virtual="/HumanR/SPTR/adov bs.inc"-->
<%
'Open up a connection to our Access database
'that stores the info about the users
Dim objConn
Set objConn = Server.CreateObject("ADODB .Connectio n")
objConn.ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" &_
"DBQ=" & Server.MapPath("SPTR.mdb")
objConn.Open
'Read in the characters the user entered
Dim Uname, Pword, Pword2
Uname = Trim(Request("Username"))
Pword = Request("Password")
Pword2 = Request("Password2")
If Pword <> Pword2 OR Uname = "" OR Pword = "" OR Pword2 = "" Then
Response.Write"You have typed in incorrect information, or you have not typed in anything. Please <A HREF='newuser.asp'>Return to the new User Page.</A>"
Else
'Create a recordset object instance, and add the entries into the table
Dim objRS
Set objRS = Server.CreateObject("ADODB .Recordset ")
objRS.Open "TblUsers", objConn, , adLockOptimistic, adCmdTable
objRS.AddNew
objRS("Username") = Uname
objRS("Password") = Pword
objRS.Update
Response.Write"The Username " & Uname & " and password " & pword & " have been added. <A HREF='default.asp'>Click here to go to Login Page.</A>"
'Clean Up our ADO objects
ObjRS.Close
Set objRS = Nothing
End If
objConn.Close
Set objConn = Nothing
%>
</BODY>
</HTML>
Creates the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query.
/HumanR/SPTR/useradd.asp, line 28
And I don't know why
Please help
Lewis a.k.a. Yeggstry
I am fairly new to ASP, and I am stuck on this very annoying problem I have.
The following code:
<%@ Language=VBSCRIPT %>
<% Option Explicit %>
<!--#include virtual="/HumanR/SPTR/adov
<%
'Open up a connection to our Access database
'that stores the info about the users
Dim objConn
Set objConn = Server.CreateObject("ADODB
objConn.ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" &_
"DBQ=" & Server.MapPath("SPTR.mdb")
objConn.Open
'Read in the characters the user entered
Dim Uname, Pword, Pword2
Uname = Trim(Request("Username"))
Pword = Request("Password")
Pword2 = Request("Password2")
If Pword <> Pword2 OR Uname = "" OR Pword = "" OR Pword2 = "" Then
Response.Write"You have typed in incorrect information, or you have not typed in anything. Please <A HREF='newuser.asp'>Return to the new User Page.</A>"
Else
'Create a recordset object instance, and add the entries into the table
Dim objRS
Set objRS = Server.CreateObject("ADODB
objRS.Open "TblUsers", objConn, , adLockOptimistic, adCmdTable
objRS.AddNew
objRS("Username") = Uname
objRS("Password") = Pword
objRS.Update
Response.Write"The Username " & Uname & " and password " & pword & " have been added. <A HREF='default.asp'>Click here to go to Login Page.</A>"
'Clean Up our ADO objects
ObjRS.Close
Set objRS = Nothing
End If
objConn.Close
Set objConn = Nothing
%>
</BODY>
</HTML>
Creates the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query.
/HumanR/SPTR/useradd.asp, line 28
And I don't know why
Please help
Lewis a.k.a. Yeggstry
HTTP access is done via the account IUSR_xxxx account. If this user does not have a write access to the folder or file you get such errors.
You have two solutions.
1. Place the MDB file into cgi-bin directory and try again.
2. If this fails, then create an ODBC connection and establish DB connection via ODBC.
The first solution may not always work. But the second solution is a sure solution and works.
You have two solutions.
1. Place the MDB file into cgi-bin directory and try again.
2. If this fails, then create an ODBC connection and establish DB connection via ODBC.
The first solution may not always work. But the second solution is a sure solution and works.
I shoul dhave explained why this might work. Its basically what kind of cursor you use. Some are read only for example.
Check this out
http://www.learnasp.com/learn/adocursortypes.asp
Dave
Check this out
http://www.learnasp.com/learn/adocursortypes.asp
Dave
Try this code:
<%
function safeforsql(sqlstring)
do until instr(1,sqlstring,",",1) = 0
sqlstring= left(sqlstring,instr(1,sql string,"," ,1)-1) & ";" & right (sqlstring,len(sqlstring) - instr(1,sqlstring,",",1))
loop
do until instr(1,sqlstring,"'",1) = 0
sqlstring= left(sqlstring,instr(1,sql string,"'" ,1)-1) & "`" & right (sqlstring,len(sqlstring) - instr(1,sqlstring,"'",1))
loop
safeforsql = sqlstring
end function
Dim objConn
Set objConn = Server.CreateObject("ADODB .Connectio n")
objConn.ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" &_
"DBQ=" & Server.MapPath("SPTR.mdb")
objConn.Open
'Read in the characters the user entered
Dim Uname, Pword, Pword2
Uname = Trim(Request("Username"))
Pword = Request("Password")
Pword2 = Request("Password2")
If Pword <> Pword2 OR Uname = "" OR Pword = "" OR Pword2 = "" Then
Response.Write"You have typed in incorrect information, or you have not typed in anything.
Please <A HREF='newuser.asp'>Return to the new User Page.</A>"
<%
Response.End
else
sql="INSERT INTO YOUR_TABLES_NAME (YOUR_DATABASE_FIELDS_SEPE RATED_BY_A _COMMA)"
sql=sql & " VALUES ( "
sql = sql & "'" & safeforsql(Username) & "', "
sql = sql & "'" & safeforsql(Password) & "') "
Set insert = Conn.Execute(SQL)
Response.Write"The Username " & Uname & " and password " & pword & " have been added. <A HREF='default.asp'>Click
here to go to Login Page.</A>"
'Clean Up our ADO objects
ObjRS.Close
Set objRS = Nothing
End If
objConn.Close
Set objConn = Nothing
<%
Response.End
End If
%>
LoBo
<%
function safeforsql(sqlstring)
do until instr(1,sqlstring,",",1) = 0
sqlstring= left(sqlstring,instr(1,sql
loop
do until instr(1,sqlstring,"'",1) = 0
sqlstring= left(sqlstring,instr(1,sql
loop
safeforsql = sqlstring
end function
Dim objConn
Set objConn = Server.CreateObject("ADODB
objConn.ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" &_
"DBQ=" & Server.MapPath("SPTR.mdb")
objConn.Open
'Read in the characters the user entered
Dim Uname, Pword, Pword2
Uname = Trim(Request("Username"))
Pword = Request("Password")
Pword2 = Request("Password2")
If Pword <> Pword2 OR Uname = "" OR Pword = "" OR Pword2 = "" Then
Response.Write"You have typed in incorrect information, or you have not typed in anything.
Please <A HREF='newuser.asp'>Return to the new User Page.</A>"
<%
Response.End
else
sql="INSERT INTO YOUR_TABLES_NAME (YOUR_DATABASE_FIELDS_SEPE
sql=sql & " VALUES ( "
sql = sql & "'" & safeforsql(Username) & "', "
sql = sql & "'" & safeforsql(Password) & "') "
Set insert = Conn.Execute(SQL)
Response.Write"The Username " & Uname & " and password " & pword & " have been added. <A HREF='default.asp'>Click
here to go to Login Page.</A>"
'Clean Up our ADO objects
ObjRS.Close
Set objRS = Nothing
End If
objConn.Close
Set objConn = Nothing
<%
Response.End
End If
%>
LoBo
Perhaps I should have also explained that I use the values rather than the constant names...
'---- CursorTypeEnum Values ----
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3
'---- LockTypeEnum Values ----
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4
Hope this helps!
Dave
'---- CursorTypeEnum Values ----
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3
'---- LockTypeEnum Values ----
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4
Hope this helps!
Dave
shastry I think teh error you would get in your suggestion would be that the Database is read only
Dave
Dave
ASKER
daveamour, I have tried your suggestion, and the error that now comes up is:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access Driver] Cannot update. Database or object is read-only.
/HumanR/SPTR/useradd.asp, line 28
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access Driver] Cannot update. Database or object is read-only.
/HumanR/SPTR/useradd.asp, line 28
ASKER
shstry, if I was to create an ODBC connection and establish DB connection via ODBC, how would I do it, and where would I put it in my code?
Ok yeq, now the problem is that the IUSR_ServerName Account does not have write permissions on the database (as shastry_yedavalli) mentioned.
Do you have access to the administration of this server?
If so you need to assign Change permissions to IUSR_ServerName. You can do this by right clicking on teh database then lciking properties and then permissions. Find and Add the IUSR_ServerName and assign change permissions for the database.
Hope this helps
Dave
Do you have access to the administration of this server?
If so you need to assign Change permissions to IUSR_ServerName. You can do this by right clicking on teh database then lciking properties and then permissions. Find and Add the IUSR_ServerName and assign change permissions for the database.
Hope this helps
Dave
ASKER
I don't have access to the admin for the server, so what can I do?
You can ask whoever does have access I guess or you can host it somewhere else, perhaps www.brinkster.com who do free ASP hosting with database support.
Dave
Dave
ASKER
Thanks for the suggestion davearmour, you deserve the points, especially as you have given me a free ASP hosting site that I did not know about!!!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
make "TblUsers" a sql query like this
tblusers="select * from table where 1=2"
it will give you blank recordset which you can add and update
tblusers="select * from table where 1=2"
it will give you blank recordset which you can add and update
objRS.Open "TblUsers", objConn, , adLockOptimistic, adCmdTable
To
objRS.Open "TblUsers", objConn, 2, 3, adCmdTable
Dave