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 Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Take a look at sql statement

Status
Not open for further replies.
false420

false420

Programmer
Joined
Mar 31, 2005
Messages
87
Location
US
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?
 
Sort by date Sort by votes
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Try using a DSN-less connection:

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

--James
 
Upvote 0 Downvote
im going to try it real quick, thanks for help0ing
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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

%>
 
Upvote 0 Downvote
<% 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
 
Upvote 0 Downvote
Im gonna try the vbscript or asp form in a littl ebit
 
Upvote 0 Downvote
I hate it how the error dont tell me too much
 
Upvote 0 Downvote
<!--#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
 
Upvote 0 Downvote
Sorry mate,
I'm going to have to admit defeat on this one. Should work. Nothing looks out of place. Weird
 
Upvote 0 Downvote
I know....its crazy. thanks for helping all.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

MathiasB
  • Locked
  • Question Question
Parameterised sql server query
Replies
8
Views
902
Chriss Miller
C
Steve Yu
  • Locked
  • Question Question
How secure is MS SQL on the Internet vs behind firewall inside a LAN
Replies
3
Views
889
Chris Miller
Chris Miller
gbaughma
  • Locked
  • Question Question
SQL Performance
Replies
11
Views
1K
pjw001
pjw001
grnzbra
  • Locked
  • Question Question
Create Function Within IF statement.
Replies
4
Views
2K
mikrom
mikrom
V
  • Locked
  • Question Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
1K
Volvere
V

Part and Inventory Search

Sponsor

Back
Top