|
 |
asp_databases thread: ASP with Access - Code attached
Message #1 by "ALAN" <AlanHughesy@A...> on Tue, 13 Jun 2000 19:47:3
|
|
I have posted this problem previously, but this time I am attaching the
code I'm using, If anyone can spot why I'm getting a:
'Microsoft Jet Database Error: Operation must be an updateable Query.'
Please let me know why you think it's happening, The database file has has
all the neccessary permissions read/write/execute etc and the path is also
correct.
<%
Dim strconn, strAccCode, StrCompNm, Conn, Cmd, rs, sql, iRecordsAffected,
DateA
strAccCode = Request.Form("txtAccessCode")
StrCompNm = Request.Form("txtCompName")
If strAccCode = "" Then
Response.Write "<Body BGColor = mediumslateblue><B><FONT SIZE=18
COLOR=WHITE>You Must Enter an Access Code</FONT></B>"
Response.Write "<P><Input id=GB Type=Button Name=GoBack OnClick=GoBack
Value=Enter_a_Code>"
Elseif strCompNm = "" Then
Response.Write "<Body BGColor = mediumslateblue><B><FONT SIZE=18
COLOR=WHITE>You Must Enter a Company Name</FONT></B>"
Response.Write "<P><Input id=GB Type=Button Name=GoBack OnClick=GoBack
Value=Enter_a_Company_Name>"
Else
Set Conn = Server.CreateObject("Adodb.Connection")
strconn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
Server.MapPath("/ACDBF/ACDB.mdb")
Conn.open strconn
Set rs = Conn.Execute("Select * from AccCodesTBL Where AccessCode = " & "'"
& strAccCode & "'" & " AND CompanyName = 'NoName'")
If rs.eof then
Response.Write "<P><Body BGColor = mediumslateblue>
<B>
<FONT SIZE=18 COLOR=WHITE>
Sorry " & strAccCode & " is not a Valid Code
</FONT>
</B>"
Response.Write "<P>
<Input id=GB Type=Button Name=GoBack OnClick=GoBack Value=Re-Enter_Code>"
Else
Conn.Execute "Update AccCodesTBL Set CompanyName = " & "'" & strCompNm &
"'" & " Where AccessCode = " & "'" & strAccCode & "'"
Response.Write "<Body BGColor = mediumslateblue><B><FONT SIZE=18
COLOR=WHITE>Your Code has been Validated Please Press The Start
Button</FONT></B>"
Response.Write "<P><Input id=SC Type=Button Name=StartCourse
OnClick=StartCourse Value=Start BackGround-Color=red Color=white>"
End if
Conn.CLOSE
Set Conn = Nothing
End If
%>
Sorry I couldn't format very well here
Thanks for your help.
Alan
Message #2 by "Mukul Jain" <jainmukul@h...> on Wed, 14 Jun 2000 12:48:35 IST
|
|
check the directory for the permission where ur database is stored. U should
have read + write perm over it ..
Mukul
From: "ALAN" <AlanHughesy@A...>
Reply-To: "ASP Databases" <asp_databases@p...>
To: "ASP Databases" <asp_databases@p...>
Subject: [asp_databases] ASP with Access - Code attached
Date: Tue, 13 Jun 2000 19:47:3
I have posted this problem previously, but this time I am attaching the
code I'm using, If anyone can spot why I'm getting a:
'Microsoft Jet Database Error: Operation must be an updateable Query.'
Please let me know why you think it's happening, The database file has has
all the neccessary permissions read/write/execute etc and the path is also
correct.
<%
Dim strconn, strAccCode, StrCompNm, Conn, Cmd, rs, sql, iRecordsAffected,
DateA
strAccCode = Request.Form("txtAccessCode")
StrCompNm = Request.Form("txtCompName")
If strAccCode = "" Then
Response.Write "<Body BGColor = mediumslateblue><B><FONT SIZE=18
COLOR=WHITE>You Must Enter an Access Code</FONT></B>"
Response.Write "<P><Input id=GB Type=Button Name=GoBack OnClick=GoBack
Value=Enter_a_Code>"
Elseif strCompNm = "" Then
Response.Write "<Body BGColor = mediumslateblue><B><FONT SIZE=18
COLOR=WHITE>You Must Enter a Company Name</FONT></B>"
Response.Write "<P><Input id=GB Type=Button Name=GoBack OnClick=GoBack
Value=Enter_a_Company_Name>"
Else
Set Conn = Server.CreateObject("Adodb.Connection")
strconn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
Server.MapPath("/ACDBF/ACDB.mdb")
Conn.open strconn
Set rs = Conn.Execute("Select * from AccCodesTBL Where AccessCode = " & "'"
& strAccCode & "'" & " AND CompanyName = 'NoName'")
If rs.eof then
Response.Write "<P><Body BGColor = mediumslateblue>
<B>
<FONT SIZE=18 COLOR=WHITE>
Sorry " & strAccCode & " is not a Valid Code
</FONT>
</B>"
Response.Write "<P>
<Input id=GB Type=Button Name=GoBack OnClick=GoBack Value=Re-Enter_Code>"
Else
Conn.Execute "Update AccCodesTBL Set CompanyName = " & "'" & strCompNm &
"'" & " Where AccessCode = " & "'" & strAccCode & "'"
Response.Write "<Body BGColor = mediumslateblue><B><FONT SIZE=18
COLOR=WHITE>Your Code has been Validated Please Press The Start
Button</FONT></B>"
Response.Write "<P><Input id=SC Type=Button Name=StartCourse
OnClick=StartCourse Value=Start BackGround-Color=red Color=white>"
End if
Conn.CLOSE
Set Conn = Nothing
End If
%>
Sorry I couldn't format very well here
Thanks for your help.
Alan
Message #3 by AlanHughesy@a... on Wed, 14 Jun 2000 06:15:55 EDT
|
|
I have read write permissions on the database file and the directory
containing the database file. I also check with my hosting company who also
verified that bot the directory and database file had read write permissions.
Thanks
Alan
Message #4 by "Ken Schaefer" <ken.s@a...> on Wed, 14 Jun 2000 22:46:09 +1000
|
|
Alan,
This is the number 1 troubleshooting tip for SQL problems...
NEVER do this:
Conn.Execute "Update AccCodesTBL Set CompanyName = " & "'" & strCompNm &
"'" & " Where AccessCode = " & "'" & strAccCode & "'"
ALWAYS do this:
strSQL = "Update AccCodesTBL Set CompanyName = " & "'" & strCompNm &
"'" & " Where AccessCode = " & "'" & strAccCode & "'"
objConn.execute(strSQL)
That way, you can comment out the objConn.execute statement, and instead put
in:
Response.Write(strSQL).
Cut and paste that output into an email message and send it to the list so
we can see exatcly what you are sending to your database... (for example you
might have a ' embedded in the strCompNm)
Also, your code leaves a little to be desired...
You are creating a recordset to see if there is a match in the database, and
if there is, you are updating that record.
Why not try to do the update, and use the RecordsAffected Paramter to see if
any records are affected, and if they are, write a message saying that the
relevant records where updated, and if none where, then you can write a
message saying strAcCode is not a valid code...
Cheers
Ken
----- Original Message -----
From: "ALAN"
To: "ASP Databases" <asp_databases@p...>
Sent: Tuesday, June 13, 2000 7:00 PM
Subject: [asp_databases] ASP with Access - Code attached
> I have posted this problem previously, but this time I am attaching the
> code I'm using, If anyone can spot why I'm getting a:
>
> 'Microsoft Jet Database Error: Operation must be an updateable Query.'
>
> Please let me know why you think it's happening, The database file has
has
> all the neccessary permissions read/write/execute etc and the path is also
> correct.
>
> <%
> Dim strconn, strAccCode, StrCompNm, Conn, Cmd, rs, sql, iRecordsAffected,
> DateA
>
> strAccCode = Request.Form("txtAccessCode")
> StrCompNm = Request.Form("txtCompName")
>
> If strAccCode = "" Then
> Response.Write "<Body BGColor = mediumslateblue><B><FONT SIZE=18
> COLOR=WHITE>You Must Enter an Access Code</FONT></B>"
> Response.Write "<P><Input id=GB Type=Button Name=GoBack OnClick=GoBack
> Value=Enter_a_Code>"
> Elseif strCompNm = "" Then
> Response.Write "<Body BGColor = mediumslateblue><B><FONT SIZE=18
> COLOR=WHITE>You Must Enter a Company Name</FONT></B>"
> Response.Write "<P><Input id=GB Type=Button Name=GoBack OnClick=GoBack
> Value=Enter_a_Company_Name>"
> Else
> Set Conn = Server.CreateObject("Adodb.Connection")
>
> strconn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
> Server.MapPath("/ACDBF/ACDB.mdb")
>
> Conn.open strconn
>
> Set rs = Conn.Execute("Select * from AccCodesTBL Where AccessCode = " &
"'"
> & strAccCode & "'" & " AND CompanyName = 'NoName'")
>
> If rs.eof then
> Response.Write "<P><Body BGColor = mediumslateblue>
> <B>
> <FONT SIZE=18 COLOR=WHITE>
> Sorry " & strAccCode & " is not a Valid Code
> </FONT>
> </B>"
> Response.Write "<P>
> <Input id=GB Type=Button Name=GoBack OnClick=GoBack Value=Re-Enter_Code>"
> Else
> Conn.Execute "Update AccCodesTBL Set CompanyName = " & "'" & strCompNm &
> "'" & " Where AccessCode = " & "'" & strAccCode & "'"
> Response.Write "<Body BGColor = mediumslateblue><B><FONT SIZE=18
> COLOR=WHITE>Your Code has been Validated Please Press The Start
> Button</FONT></B>"
> Response.Write "<P><Input id=SC Type=Button Name=StartCourse
> OnClick=StartCourse Value=Start BackGround-Color=red Color=white>"
> End if
>
> Conn.CLOSE
> Set Conn = Nothing
>
> End If
> %>
>
>
>
>
> Sorry I couldn't format very well here
> Thanks for your help.
>
> Alan
>
>
Message #5 by "vijay ramamoorthi" <vijay69@h...> on Wed, 14 Jun 2000 08:51:31 EDT
|
|
Hi,
I got the same error message for an Update query, but it was in ACCESS97.
When I checked the Knowledge Base for Access97, I found that when Access2.0
or 7.0 is converted to Access97, the DISTINCTROW predicate is set to "NO".
This is the default in ACCESS97 and the default in ACCESS 2.0 is "Yes"
All I had to do was, change the DISTINCTROW predicate to "Yes" in the
Access97 query and it worked. Not sure how it affects asp/ACCESS.
Try changing your Update query to ...
Conn.Execute "Update DISTINCTROW AccCodesTBL Set CompanyName = ...
Hope this works.
- Vijay
|
|
|
View Cart
