Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Database Problem

Posted on 2001-09-05
14
Medium Priority
?
192 Views
Last Modified: 2008-03-10
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/adovbs.inc"-->
<%
     'Open up a connection to our Access database
     'that stores the info about the users
     Dim objConn
     Set objConn = Server.CreateObject("ADODB.Connection")
     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

0
Comment
Question by:yeggstry
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
14 Comments
 
LVL 19

Expert Comment

by:daveamour
ID: 6456693
Try changing this line

objRS.Open "TblUsers", objConn, , adLockOptimistic, adCmdTable
         
To

objRS.Open "TblUsers", objConn, 2, 3, adCmdTable

Dave
         
0
 

Expert Comment

by:shastry_yedavalli
ID: 6456696
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.
0
 
LVL 19

Expert Comment

by:daveamour
ID: 6456702
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Expert Comment

by:LOBO
ID: 6456703
Try this code:

<%
function safeforsql(sqlstring)
 do until instr(1,sqlstring,",",1) = 0
  sqlstring= left(sqlstring,instr(1,sqlstring,",",1)-1) & ";" & right (sqlstring,len(sqlstring) - instr(1,sqlstring,",",1))
 loop
 do until instr(1,sqlstring,"'",1) = 0
  sqlstring= left(sqlstring,instr(1,sqlstring,"'",1)-1) & "`" & right (sqlstring,len(sqlstring) - instr(1,sqlstring,"'",1))
 loop
 safeforsql = sqlstring
end function


Dim objConn
    Set objConn = Server.CreateObject("ADODB.Connection")
    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_SEPERATED_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
0
 
LVL 19

Expert Comment

by:daveamour
ID: 6456708
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
0
 
LVL 19

Expert Comment

by:daveamour
ID: 6456710
shastry I think teh error you would get in your suggestion would be that the Database is read only

Dave
0
 

Author Comment

by:yeggstry
ID: 6456795
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
0
 

Author Comment

by:yeggstry
ID: 6456804
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?
0
 
LVL 19

Expert Comment

by:daveamour
ID: 6456844
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
0
 

Author Comment

by:yeggstry
ID: 6456916
I don't have access to the admin for the server, so what can I do?
0
 
LVL 19

Expert Comment

by:daveamour
ID: 6456937
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
0
 

Author Comment

by:yeggstry
ID: 6456997
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!!!
0
 
LVL 19

Accepted Solution

by:
daveamour earned 400 total points
ID: 6457051
Thanks
0
 

Expert Comment

by:sharad2
ID: 6463156
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
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question