strSQL statement or Recordset problem 1

ceeleelewis · Sep 26, 2002

I have a subroutine that I am trying to use to delete, edit, & add requests ( in this case delete) based off the reqFormID (request form id). It seems like I have a problem with the record set object or strSQL statment. When I comment out the strSQL out,then replace it with objRS.open "CMCNTRreq", objConn, adOpenStatic, adLockOptimistic.. I get
a response but the problem is it only list the first record on the database.

I even change the obRS.open "CMCNTRreq", objConn, adOpenStatic,
adLockOptimistic to obRS.open "CMCNTRreq", objConn, adOpenDynamic,
adLockOptimistic and end up with the same record showing.

What am I doing wrong? Your direction will be helpful.

Below is the subroutine...thx

C. Lewis

'===========================================================
' Subroutine - Delete Record
'===========================================================
Sub subDeleteRecord

Dim i, intFieldCount

'Define SQL Query

strSQL = "SELECT * FROM [CMCNTRreq] WHERE [reqFormId]="
&Request.Querystring("Delete&quot

'Response.Write "OOOH YEAAH!!!"
'Open recordset passing the SQL to the connection object.

'objRS.open "CMCNTRreq", objConn, adOpenStatic, adLockOptimistic

objRS.Open "CMCNTRreq", strSQL, objConn, 1, 3
'Check for errors in objConn
subErrorCheck

intFieldCount = objRS.Fields.Count

%>
<%
If Request.Querystring("Confirmed&quot

= 1 Then
'Delete record:

objRS.Delete

'Check for errors in objConn
subErrorCheck

%>

<p align="center">Record #<%= Request.Querystring("Delete&quot

%>
has been deleted.</p>
<p align="center"><a href="commandopenreqmngr.asp">Return to
Command Center Open Request List</a></p>

<%

Else
'Confirm delete prompt:

%>
<p align="center">Are you sure you wish to delete the following
request?</p>
<table width="80%" align="center" cellspacing="0"
cellpadding="3" border="1">
<tr>
<% For i = 0 To intFieldCount -1 %>
<th><%= objRS(i).Name %></th>
<% Next %>
</tr>

<tr>
<% For i = 0 To intFieldCount -1 %>
<td><%= objRS(i)%></td>
<% Next %>
</tr>
</table>
<p align="center">
<a href="commandopenreqmngr.asp?Delete=<%=
Request.Querystring("Delete&quot

%>&Confirmed=1">Yes</a> |
<a href="commandopenreqmngr.asp">No</a>
</p>

<%
End If

End Sub
%>
<%

Areli · Sep 30, 2002

Your problem is that you have not defined the constants adOpenStatic and adLockOptimistic. VBScript converts any undefined variables into zero. So your code is interpreted as objRS.Open "CMCNTRreq", strSQL, 0, 0.

These constants along with others are defined in the msado15.dll. I recommend you use the type library in the META tag to import the msado15.dll:



The above META tag should be included on the page between the <HEAD></HEAD> tags. If your pages will be utlizing the
dll quite a bit, it's probably a good idea to insert the META tag as the first line in your global.asa file. That way it does not have to be included in each page that will utilize the dll.

FYI - be sure to use the path to where the dll is located on your computer.

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!

strSQL statement or Recordset problem 1

ceeleelewis

Programmer

Areli

Programmer

Similar threads

Part and Inventory Search

Sponsor