Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

anyone a ADODB.Connection expert?

Status
Not open for further replies.
aolb

aolb

Programmer
Apr 16, 2002
180
GB

I am having problems updating SQL Server tables from ASP. I know the SQL side is ok coz I can take the query that the ASP produces and past it into the query analyser signed on with the ASP logon and update my table!!

I have the adovbs.inc and ADODB.Connection.

set ConnODBC = Server.CreateObject("ADODB.Connection")

ThisServer = Application("SERVER")
ThisUID = Application("UID")
ThisPWD = Application("PWD")

objDBConn= "DRIVER={SQL Server};SERVER=" & ThisServer & ";UID=" & ThisUID & ";PWD=" &ThisPWD

ConnODBC.Open objDBConn

I suspect there is a problem when I run the query.

rs.Open strSQL, ConnODBC, adOpenStatic, adLockReadOnly, adCmdText


any thoughts as to what else I can check?
 
Sort by date Sort by votes
when I try to delete a record i get the error message

ADODB.Recordset error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

when i try to insert or amend i don't get any error message the update to the table is not done!!

here is my asp page, grateful for any input

steve


<%Response.Expires = 0%>
<html>

<head>
<title>City Of Westminster Missed Collection Complaint Types</title>
</head>
<%
Dim ConnODBC, objDBConn, rs, strSQL, ThisServer, ThisUID, ThisPWD, ThisDB, RecCnt


set ConnODBC = Server.CreateObject(&quot;ADODB.Connection&quot;)

ThisServer = Application(&quot;SERVER&quot;)
ThisUID = Application(&quot;UID&quot;)
ThisPWD = Application(&quot;PWD&quot;)

objDBConn= &quot;DRIVER={SQL Server};SERVER=&quot; & ThisServer & &quot;;UID=&quot; & ThisUID & &quot;;PWD=&quot; &ThisPWD
ConnODBC.Open objDBConn

%>
<body>

<!-- #include file= &quot;../include/adovbs.inc&quot; -->
<!-- #include file= &quot;../include/WireLogo.inc&quot; -->
<H1 ALIGN=&quot;center&quot;>Environmental Health<br>
Complaint Type Maintenance - Add Complaint Type Report Code</H1>
<%
Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot;)
rs.ActiveConnection = ConnODBC

if Request.Form(&quot;txtStatatus&quot;) = &quot;Add/Amend Complaint Report Code&quot; then

strSQL = &quot;SELECT COUNT(*) AS RecCnt FROM tblcomplainttypes &quot; _
& &quot;WHERE ComplaintTypeID = &quot; & Request.Form(&quot;txtID&quot;)
'Response.Write strSQL & &quot;<p>&quot;

rs.Open strSQL, ConnODBC, adOpenStatic, adLockReadOnly, adCmdText
'response.write rs(&quot;RecCnt&quot;)

if rs(&quot;RecCnt&quot;) = 1 then
'Update query
sqlstr = &quot;UPDATE tblComplaintTypes &quot; _
& &quot;SET ComplaintType = '&quot; & Request.Form(&quot;txtComplaintType&quot;) _
& &quot;', ServiceType = '&quot; & Request.Form(&quot;txtServiceType&quot;) _
& &quot;', ReportFunction = &quot; & Request.Form(&quot;cboReportCode&quot;) _
& &quot; WHERE ComplaintTypeID = &quot; & Request.Form(&quot;txtID&quot;)
elseif rs(&quot;RecCnt&quot;) = 0 then
'insert query
sqlstr = &quot;INSERT INTO tblComplaintTypes &quot; _
& &quot;(ComplaintTypeID, ComplaintType, ServiceType, ReportFunction) &quot; _
& &quot;VALUES (&quot; & Request.Form(&quot;txtID&quot;) _
& &quot;, '&quot; & Request.Form(&quot;txtComplaintType&quot;) _
& &quot;', '&quot; & Request.Form(&quot;txtServiceType&quot;) _
& &quot;', &quot; & Request.Form(&quot;cboReportCode&quot;) & &quot;)&quot;
end if

rs.close
else
sqlstr = &quot;DELETE FROM tblComplaintTypes WHERE ComplaintTypeID = &quot; & Request.Form(&quot;txtID&quot;)
end if
Response.Write sqlstr
rs.Open strSQL, ConnODBC, adOpenStatic, adLockReadOnly, adCmdText

rs.close
set rs= Nothing
set ConnODBC = Nothing
%>
</body>
</html>
 
Upvote 0 Downvote
is this the variable name &quot;txtStatatus&quot; correct and giving the value as &quot;Add/Amend Complaint Report Code&quot;
 
Upvote 0 Downvote
not to worry i've found the problem, dyslexia strikes again!!!
 
Upvote 0 Downvote
You are opening the recordset in readonly mode:

rs.Open strSQL, ConnODBC, adOpenStatic, adLockReadOnly, adCmdText

Try to use this instead:

rs.Open strSQL, ConnODBC, adOpenStatic, adLockOptimistic, adCmdText

Larsson
 
Upvote 0 Downvote
Nope, the problem was dyslexia!!! if you look at the page my sql string for the connection is called sometimes called sqlstr and other times strsql.

I think adLockReadOnly is to do with locking the record whilst updating the table to stop 2 updates at the same time and not opening the recordset in readonly mode.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
Parse a database field that is comma delimited and input the results into another table
Replies
0
Views
409
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
Wrong code in a href ?
Replies
1
Views
419
leifoet
leifoet
leifoet
  • Locked
  • Question
How to check if a datatable is empty ?
Replies
0
Views
434
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
Start loop from a specified point
Replies
0
Views
381
penguinspeaks
penguinspeaks
DiDYRO_
  • Locked
  • Question
How do I make a VBS file that can collect dialog-type answers in a local .log file?
Replies
0
Views
371
DiDYRO_
DiDYRO_

Part and Inventory Search

Sponsor

Back
Top