|
 |
asp_databases thread: problem with INSERT query
Message #1 by "jason shope" <epohs@h...> on Tue, 16 Oct 2001 12:15:26 -0400
|
|
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 "Ken Schaefer" <ken@a...> on Wed, 17 Oct 2001 11:14:44 +1000
|
|
You are not creating your SQL string properly, eg:
: strInsert = "INSERT INTO sightings "
: 'strInsert = strInsert & "(Pseudonym"
: strInsert = strInsert & "(Pseudonym,Email"
results in the same field name being used *twice* with no comma separating
them. I suggest you do a
Response.Write testerSQL
Response.End
(which is your final SQL string), and put that into the Access QBE. If you
still can't work out what the problem is, post the output of that
Response.Write to the list(s)
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "jason shope" <epohs@h...>
To: "ASP Databases" <asp_databases@p...>
Sent: Wednesday, October 17, 2001 2:15 AM
Subject: [asp_databases] problem with INSERT query
: 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.
:
<snipped>
Message #3 by "Michael Seils" <mseils@s...> on Wed, 17 Oct 2001 01:29:39
|
|
See the reply under your posting on asp_access.
> 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 #4 by gbrown@c... on Wed, 17 Oct 2001 11:22:30
|
|
Hi
If it were a badly formed SQL statement I would expect to see syntax
errors.
I think that updateable query errors point to rights (they have on every
occurrence here anyway!). This has tripped me up on numerous occasions
particularly when installing from CD or on Win2k machines.
Would still suggest checking for syntax errors anyway as ASP tends to be a
pain in tracking things down.
> 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.
>
<snipped>
Regards
Graham
Message #5 by "Drew, Ron" <RDrew@B...> on Wed, 17 Oct 2001 08:53:12 -0400
|
|
I think you are missing the closing ")" for both strInsert and strValues. I
commented out the db stuff and did a response.write and did not show the
closing ) on either
Message #6 by "Ken Schaefer" <ken@a...> on Thu, 18 Oct 2001 18:34:42 +1000
|
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: I think that updateable query errors point to rights (they have on every
: occurrence here anyway!).
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Not always. A malformed INSERT or UPDATE query can generate this type of
error (with older MDACs anyway...)
Cheers
Ken
|
|
|
View Cart
