|
 |
access_asp thread: problems with INSERT query
Message #1 by epohs@h... on Tue, 16 Oct 2001 17:29:07
|
|
I recently purchased Beginning ASP Databases and am in the process of
working my way through it. I am able to read data from my MS Access
database, but, when I try to write to it I get errors.
1. for some reason I can't add a text field that is formatted as such:
"11:30" -you can see from my code below that I have commented that code
out
for development purposes.
2. I get this error: Operation must use an updateable query. which I
don't
understand. Everything seems to be fine other than that
HERE IS THE FULL ERROR MESSAGE:
____________________________________________________
Technical Information (for support personnel)
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver] Operation must use an
updateable
query.
/BlurryPhoto/Report/DataSubmited.asp, line 72
Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)
Page:
POST 243 bytes to /BlurryPhoto/Report/DataSubmited.asp
POST Data:
DateSubmited=10%2F14%
2F2001&Pseudonym=jason&Email=epohs@h...&HideEmai
l=True&Confidential=True&Month=05&Day=27&Year=01&Time=11%
3A30&TimeOfDay=pm&C
ounty=Buncombe&Type=UFO&Description=350+words+or
. . .
Time:
Sunday, October 14, 2001, 8:07:30 PM
______________________________________________________
AND HERE IS MY CODE
<%
'Declare Variables Needed
Dim strInsert
Dim strValues
Dim adCmdText
Dim testerSQL
'Set required variables
adCmdText = 1
'Start building the SQL INSERT string
strInsert = "INSERT INTO sightings "
'strInsert = strInsert & "(Pseudonym"
strInsert = strInsert & "(Pseudonym,Email"
strInsert = strInsert & ",HideEmail,Confidential"
strInsert = strInsert & ",Month,Day,Year"
'strInsert = strInsert & ",Time"
strInsert = strInsert & ",TimeOfDay"
'strInsert = strInsert & ",Time,TimeOfDay"
strInsert = strInsert & ",County,Type,Description"
strInsert = strInsert & ",DateSubmited"
'Start building the SQL VALUE string
strValues = " VALUES ('"
strValues = strValues & CStr(Request.Form("Pseudonym")) & "','"
strValues = strValues & CStr(Request.Form("Email")) & "','"
strValues = strValues & CBool(Request.Form("HideEmail")) & "','"
strValues = strValues & CBool(Request.Form("Confidential")) & "',"
strValues = strValues & CLng(Request.Form("Month")) & ","
strValues = strValues & CLng(Request.Form("Day")) & ","
strValues = strValues & CLng(Request.Form("Year")) & ",'"
'trValues = strValues & CStr(Request.Form("Time")) & "'"
'strValues = strValues & CStr(Request.Form("Time")) & "'"
strValues = strValues & CStr(Request.Form("TimeOfDay")) & "','"
strValues = strValues & CStr(Request.Form("County")) & "','"
strValues = strValues & CStr(Request.Form("Type")) & "','"
strValues = strValues & CStr(Request.Form("Description")) & "',#"
strValues = strValues & CDate(Request.Form("DateSubmited")) & "#"
'testerSQL = testerSQL & strInsert & strValues
Response.Write testerSQL
'Create and open the database object
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DSN=sightings"
'Response.Write objConn.Supports(AdAddNew)
'Create the command object
Set objCmd = Server.CreateObject("ADODB.Command")
'Set the command object Properties
Set objCmd.ActiveConnection = objConn
objCmd.CommandText = strInsert & ") " & strValues & ")"
Response.Write objCmd.CommandText
objCmd.CommandType = adCmdText
'Execute the command
objCmd.Execute
%>
____________________________________________________
the data is making it to this page but not to the database.
thank you for any help you can give me.
-js
Message #2 by "Michael Seils" <mseils@s...> on Wed, 17 Oct 2001 01:25:31
|
|
As far as the error message goes, it doesn't lie in your code but in the
rights of the page. Either your database is read only or your permissions
are set to read only for the file. This should fix the problem. Hope this
helps.
I didn't really look at the code too much so I don't know if you will get
any other errors after you fix that.
Mike.
> I recently purchased Beginning ASP Databases and am in the process of
> working my way through it. I am able to read data from my MS Access
> database, but, when I try to write to it I get errors.
>
> 1. for some reason I can't add a text field that is formatted as such:
> "11:30" -you can see from my code below that I have commented that code
> out
> for development purposes.
>
> 2. I get this error: Operation must use an updateable query. which I
> don't
> understand. Everything seems to be fine other than that
>
> HERE IS THE FULL ERROR MESSAGE:
> ____________________________________________________
>
> Technical Information (for support personnel)
>
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
> [Microsoft][ODBC Microsoft Access Driver] Operation must use an
> updateable
> query.
> /BlurryPhoto/Report/DataSubmited.asp, line 72
>
>
> Browser Type:
> Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)
>
> Page:
> POST 243 bytes to /BlurryPhoto/Report/DataSubmited.asp
>
> POST Data:
> DateSubmited=10%2F14%
> 2F2001&Pseudonym=jason&Email=epohs@h...&HideEmai
> l=True&Confidential=True&Month=05&Day=27&Year=01&Time=11%
> 3A30&TimeOfDay=pm&C
> ounty=Buncombe&Type=UFO&Description=350+words+or
> . . .
>
> Time:
> Sunday, October 14, 2001, 8:07:30 PM
> ______________________________________________________
>
> AND HERE IS MY CODE
>
> <%
> 'Declare Variables Needed
> Dim strInsert
> Dim strValues
> Dim adCmdText
>
> Dim testerSQL
>
> 'Set required variables
> adCmdText = 1
>
> 'Start building the SQL INSERT string
> strInsert = "INSERT INTO sightings "
> 'strInsert = strInsert & "(Pseudonym"
> strInsert = strInsert & "(Pseudonym,Email"
> strInsert = strInsert & ",HideEmail,Confidential"
> strInsert = strInsert & ",Month,Day,Year"
> 'strInsert = strInsert & ",Time"
> strInsert = strInsert & ",TimeOfDay"
> 'strInsert = strInsert & ",Time,TimeOfDay"
> strInsert = strInsert & ",County,Type,Description"
> strInsert = strInsert & ",DateSubmited"
>
>
> 'Start building the SQL VALUE string
> strValues = " VALUES ('"
> strValues = strValues & CStr(Request.Form("Pseudonym")) & "','"
> strValues = strValues & CStr(Request.Form("Email")) & "','"
> strValues = strValues & CBool(Request.Form("HideEmail")) & "','"
> strValues = strValues & CBool(Request.Form("Confidential")) & "',"
> strValues = strValues & CLng(Request.Form("Month")) & ","
> strValues = strValues & CLng(Request.Form("Day")) & ","
> strValues = strValues & CLng(Request.Form("Year")) & ",'"
> 'trValues = strValues & CStr(Request.Form("Time")) & "'"
> 'strValues = strValues & CStr(Request.Form("Time")) & "'"
> strValues = strValues & CStr(Request.Form("TimeOfDay")) & "','"
> strValues = strValues & CStr(Request.Form("County")) & "','"
> strValues = strValues & CStr(Request.Form("Type")) & "','"
> strValues = strValues & CStr(Request.Form("Description")) & "',#"
> strValues = strValues & CDate(Request.Form("DateSubmited")) & "#"
>
> 'testerSQL = testerSQL & strInsert & strValues
>
> Response.Write testerSQL
>
> 'Create and open the database object
> Set objConn = Server.CreateObject("ADODB.Connection")
> objConn.Open "DSN=sightings"
>
> 'Response.Write objConn.Supports(AdAddNew)
>
> 'Create the command object
> Set objCmd = Server.CreateObject("ADODB.Command")
>
> 'Set the command object Properties
> Set objCmd.ActiveConnection = objConn
> objCmd.CommandText = strInsert & ") " & strValues & ")"
> Response.Write objCmd.CommandText
> objCmd.CommandType = adCmdText
>
> 'Execute the command
> objCmd.Execute
>
> %>
> ____________________________________________________
> the data is making it to this page but not to the database.
>
> thank you for any help you can give me.
>
> -js
Message #3 by epohs@h... on Thu, 18 Oct 2001 01:45:37
|
|
Yeah, I kinda have narrowed it down to problems with my permissions.
However I can't seem to be able to set those permissions correctly. From
what I can tell my permissions are right but it still isn't working. In
fact I messed around with some things and now I get this message:
____________________________________________
The Microsoft Jet database engine cannot open the file '(unknown)'. It is
already opened exclusively by another user, or you need permission to view
its data.
____________________________________________
but as far as I can tell I haven't changed a thing that I didn't set back
exactly the same as before. Could someone please tell me how to update
privilages so that I can access and Add new records to my database.
Thanks,
js
Message #4 by "Ken Schaefer" <ken@a...> on Thu, 18 Oct 2001 18:29:56 +1000
|
|
Firstly what *are* your permissions? You say "I should have them right", but
what if you don't? It's easier if we can cast our eye over what you have and
say "there's the problem", rather than have to explain the whole thing to
you from scratch.
That said, the anonymous account needs RXW to the actual .mdb file, and I
believe RX to the folder that the file is in. That said, I usually assign
NTFS Change Permissions (RXWD) to the folder and file.
Secondly, as the error says, make sure that you haven't got this database
open elsewhere - eg in Access itself, or in InterDev
Cheers
Ken
|
|
|
View Cart
