Take a look at sql statement

[false420]

Ok, im new to sql. dont have much time to learn until our stuff is migrated and running. its almost done but ive ran into a small issue. ive been looking at this for 2 hours and cant figure it out. whats wrong with this:

Dim strIsApproved
strIsApproved = "SELECT * FROM InvoiceApproval WHERE Month_Date >= '" & Option_Date & "' AND Month_Date < '" & toDate & "' AND Service = " & service & " AND Cost_Center = " & Service_CC_hold & " AND Carrier = " & Service_Car_hold & " AND Account = " & Service_Acc_hold & " AND Disapproved = false"
Dim objRS_IsApproved
Set objRS_IsApproved = Server.CreateObject("ADODB.Recordset")
objRS_IsApproved.Open strIsApproved, objConnHD, adOpenKeyset


This is my error: ODBC driver does not support the requested properties

also does anyone know a good site with some sql statement examples?

 

[false420]

im connecting to sql server 2000 here is my connection string

Set objConnHD = Server.CreateObject("ADODB.Connection")
objConnHD.ConnectionString = "DSN=CarHD;UID=***;PWD=***"
objConnHD.Open

the thing is that, this same kind of connection string works for other clients of the site.

 

[tortoisehalfbreed]

I don't know then mate - I'm beat too. The only other thing I can suggest is check the DSN is set up correctly. Just because it works elsewhere, it doesn't mean it'll work how it should in your environment.
I dunno too much about DSN connections because I just use literal connection strings.

 

[false420]

is literal faster for queries and such. im gonna redo the dsn for this company real quick. if anyone has any other ideas please post, if i finbd out whats going on and fix it i will post what happened

 

[tortoisehalfbreed]

I use literals coz I don't have full access to the servers to setup DSNs and such. I don't know if they're any faster.
Sorry I couldn't help mate. I'd love to know the outcome though.

 

[JamesLean]

Try using a DSN-less connection:

objConnHD.ConnectionString = "Provider=SQLOLEDB;Data Source=myserver;Initial Catalog=mydb;User ID=myuser;Password=mypassword"

--James

 

[false420]

im going to try it real quick, thanks for help0ing

 

[false420]

ADODB.Connection error '800a0e79'

Operation is not allowed when the object is open.

maybe i open it somewhere else and that was causing the other error too

 

[false420]

oops nm, i get same error

 

[JamesLean]

I think this is becoming more of an ASP problem than an issue with your SQL. I would suggest posting in the ASP forum for more detailed help.

--James

 

[tortoisehalfbreed]

false420, could you post the entire code?

 

[false420]

If InvoiceApproval = 1 Then
'See if the current row is checked.
If Request("InvoiceApproval" & RadioButton_counter) = "yes" Then
'See if already approved in the database.
Dim strIsApprovedBefore, IsInTable
IsInTable = 0
strIsApprovedBefore = "SELECT * FROM InvoiceApproval WHERE Month_Date >= '" & Option_Date & "' AND Month_Date < '" & toDate & "' AND Service = " & service & " AND Cost_Center = " & Service_CC_hold & " AND Carrier = " & Service_Car_hold & " AND Account = " & Service_Acc_hold & " AND Disapproved = false"
Dim objRS_IsApprovedBefore
Set objRS_IsApprovedBefore = Server.CreateObject("ADODB.Recordset")
objRS_IsApprovedBefore.Open strIsApprovedBefore,objConnHD,adOpenKeyset
If objRS_IsApprovedBefore.RecordCount = 0 Then
IsInTable = 1
End If
objRS_IsApprovedBefore.Close
Set objRS_IsApprovedBefore = Nothing
'Insert approval into database for items that are checked and submitted.
If IsInTable = 1 Then
Dim strAddApproval
strAddApproval = "SELECT Service, Cost_Center, Carrier, Account, Month_Date, UserID FROM InvoiceApproval"
Dim objRS_AddApproval
Set objRS_AddApproval = Server.CreateObject("ADODB.Recordset")
objRS_AddApproval.Open strAddApproval,objConnHD, ,adLockPessimistic
objRS_AddApproval.AddNew
objRS_AddApproval("Service") = service
objRS_AddApproval("Cost_Center") = Service_CC_hold
objRS_AddApproval("Carrier") = Service_Car_hold
objRS_AddApproval("Account") = Service_Acc_hold
objRS_AddApproval("Month_Date") = Service_Date_hold
objRS_AddApproval("UserID") = strSession_UID
objRS_AddApproval.Update

objRS_AddApproval.Close
Set objRS_AddApproval = Nothing
End If
'See if the current row is checked as disapproved.
ElseIf Request("InvoiceApproval" & RadioButton_counter) = "no" Then
'See if already disapproved in the database.
Dim strIsDisapprovedBefore
IsInTable = 0
strIsDisapprovedBefore = "SELECT * FROM InvoiceApproval WHERE Month_Date >= '" & Option_Date & "' AND Month_Date < '" & toDate & "' AND Service = " & service & " AND Cost_Center = " & Service_CC_hold & " AND Carrier = " & Service_Car_hold & " AND Account = " & Service_Acc_hold & " AND Disapproved = true"
Dim objRS_IsDisapprovedBefore
Set objRS_IsDisapprovedBefore = Server.CreateObject("ADODB.Recordset")
objRS_IsDisapprovedBefore.Open strIsDisapprovedBefore, objConnHD, adOpenKeyset
If objRS_IsDisapprovedBefore.RecordCount = 0 Then
IsInTable = 1
End If
objRS_IsDisapprovedBefore.Close
Set objRS_IsDisapprovedBefore = Nothing
'Insert disapproval into database for items that are checked and submitted.
If IsInTable = 1 Then
Dim strAddDisapproval
strAddDisapproval = "SELECT Disapproved, Service, Cost_Center, Carrier, Account, Month_Date, UserID FROM InvoiceApproval"
Dim objRS_AddDisapproval
Set objRS_AddDisapproval = Server.CreateObject("ADODB.Recordset")
objRS_AddDisapproval.Open strAddDisapproval,objConnHD, ,adLockPessimistic
objRS_AddDisapproval.AddNew
objRS_AddDisapproval("Service") = service
objRS_AddDisapproval("Cost_Center") = Service_CC_hold
objRS_AddDisapproval("Carrier") = Service_Car_hold
objRS_AddDisapproval("Account") = Service_Acc_hold
objRS_AddDisapproval("Month_Date") = Service_Date_hold
objRS_AddDisapproval("UserID") = strSession_UID
objRS_AddDisapproval("Disapproved") = true
objRS_AddDisapproval.Update

objRS_AddDisapproval.Close
Set objRS_AddDisapproval = Nothing
End If

End If

Dim strIsApproved
strIsApproved = "SELECT * FROM InvoiceApproval WHERE Month_Date >= '" & Option_Date & "' AND Month_Date < '" & toDate & "' AND Service = " & service & " AND Cost_Center = " & Service_CC_hold & " AND Carrier = " & Service_Car_hold & " AND Account = " & Service_Acc_hold & " AND Disapproved = false"
Response.write strIsApproved
Dim objRS_IsApproved
Set objRS_IsApproved = Server.CreateObject("ADODB.Recordset")
objRS_IsApproved.Open strIsApproved, objConnHD, adOpenKeyset

Dim strIsDisapproved
strIsDisapproved = "SELECT * FROM InvoiceApproval WHERE Month_Date >= '" & Option_Date & "' AND Month_Date < '" & toDate & "' AND Service = " & service & " AND Cost_Center = " & Service_CC_hold & " AND Carrier = " & Service_Car_hold & " AND Account = " & Service_Acc_hold & " AND Disapproved = true"
Dim objRS_IsDisapproved
Set objRS_IsDisapproved = Server.CreateObject("ADODB.Recordset")
objRS_IsDisapproved.Open strIsDisapproved, objConnHD, adOpenKeyset

Dim strDisapprovedNoNote
strDisapprovedNoNote = "SELECT * FROM InvoiceApproval WHERE Month_Date >= '" & Option_Date & "' AND Month_Date < '" & toDate & "' AND Service = " & service & " AND Cost_Center = " & Service_CC_hold & " AND Carrier = " & Service_Car_hold & " AND Account = " & Service_Acc_hold & " AND Disapproved = true AND UserID = '" & strSession_UID & "'"
Dim objRS_DisapprovedNoNote
Set objRS_DisapprovedNoNote = Server.CreateObject("ADODB.Recordset")
objRS_DisapprovedNoNote.Open strDisapprovedNoNote, objConnHD, adOpenKeyset

If objRS_DisapprovedNoNote.RecordCount > 0 Then
If objRS_DisapprovedNoNote("DisapprovalDescNum") = 0 Then
DisapprovedNoNote = DisapprovedNoNote + 1
End If
End If
End If

%>

 

[false420]

<% If InvoiceApproval = 1 Then %><td align='left' bgcolor='ccff99'><input name='InvoiceApproval<%= RadioButton_counter %>' type='radio' value='yes' <% If objRS_IsDisapproved.RecordCount > 0 OR objRS_IsApproved.RecordCount > 0 OR Date > oldApprovalDate Then Response.Write "disabled='true'" End If %> <% If (objRS_IsApproved.RecordCount > 0) OR (AllChecked = "All") Then Response.Write "checked" End If %>>&nbsp;Yes</td><% End If %>
<% If InvoiceApproval = 1 Then %><td align='left' bgcolor='ff6666'><% If (objRS_IsDisapproved.RecordCount > 0) Then %><a href='a_invoicesDisplayDisapprovalNote.asp?Year=<%= Option_Year %>&Month_Year=<%= Option_Date %>&toDate=<%= toDate %>&E_C=<%= ExpandCompress %>&NoteNum=<%= objRS_IsDisapproved("DisapprovalDescNum") %>&CC=<%= Service_CC_hold %>&Car=<%= Service_Car_hold %>&Acc=<%= Service_Acc_hold %>' target='_self'><% End If %><input name='InvoiceApproval<%= RadioButton_counter %>' type='radio' value='no' <% If objRS_IsDisapproved.RecordCount > 0 OR objRS_IsApproved.RecordCount > 0 OR Date > oldApprovalDate Then Response.Write "disabled='true'" End If %> <% If (objRS_IsDisapproved.RecordCount > 0) Then Response.Write "checked" End If %>>&nbsp;No&nbsp;<% If (objRS_IsDisapproved.RecordCount > 0) Then %></a><% End If %></td><% End If %>
<% If InvoiceApproval = 1 Then %>
<td align='left'>
<% If objRS_IsApproved.RecordCount > 0 Then
Response.Write objRS_IsApproved("UserID")
ElseIf objRS_IsDisapproved.RecordCount > 0 Then
Response.Write objRS_IsDisapproved("UserID")
End If %>
</td>
<% End If %></tr>

<%
If InvoiceApproval = 1 Then
objRS_DisapprovedNoNote.Close
Set objRS_DisapprovedNoNote = Nothing
objRS_IsDisapproved.Close
Set objRS_IsDisapproved = Nothing
objRS_IsApproved.Close
Set objRS_IsApproved = Nothing
End If
index = index + 1

 

[false420]

Im gonna try the vbscript or asp form in a littl ebit

 

[false420]

I hate it how the error dont tell me too much

 

[tortoisehalfbreed]

Where is your adodb.connection object being setup?

 

[false420]

<!--#include virtual="/adovbs.inc"-->

<!--#include file="mainInclude.inc" -->
<%
incFile = "dataHD"


<% ElseIf incFile = "dataHD" Then %>

<!--#include file="Carpenter/DatabaseConnection_hd.inc"-->


Set objConnHD = Server.CreateObject("ADODB.Connection")
objConnHD.ConnectionString = "DSN=CarHD;UID=*****;PWD=*****"
objConnHD.Open

 

[tortoisehalfbreed]

Sorry mate,
I'm going to have to admit defeat on this one. Should work. Nothing looks out of place. Weird

 

[false420]

I know....its crazy. thanks for helping all.