Pass Through Query f/ VB to SQL

[Kostas]

I'm trying to run a pass-through query from a VB form in Excel to SQL Server 7.0. Does my code look right? I'm getting an error saying
"No Command has been set for the Command Object"



private Sub cmdQuery_Click()
Dim Start as date
Dim Finish as date
Dim SQL as string

Dim cmd as ADODB.Command
Dim rs as ADODB.Recordset
Dim cnn1 as ADODB.Connection
' connection object

' on error GoTo errhandle

Start = txtDate1
Finish = txtDate2

SQL = "SELECT ..." & _
"FROM ..." & _
"WHERE (date >= #" & Start & "# AND" & _
"date < #" & Finish & "#)"


' connect to your DB
set cnn1 = new ADODB.Connection
cnn1.Open "Provider=sqloledb;Data Source=server;Initial Catalog=DB;User Id=user;Password=pass;"

set cmd = new ADODB.Command
set cmd.ActiveConnection = cnn1 ' set the command object to point to your opened connection

With cmd
.CommandTimeout = 600 '10 minutes
'.CommandType = adCmdStoredProc
'.CommandText = "lucent_interval_historical"
.Parameters.Append .CreateParameter("@start", adDBTimeStamp, adParamInput)
.Parameters.Append .CreateParameter("@finish", adDBTimeStamp, adParamInput)
.Parameters("@start").Value = Start
.Parameters("@finish").Value = Finish
End With

set rs = cmd.Execute(SQL)

rs.Close
cnn1.Close

Exit Sub

' Select Case Err.Number
' Case else
' Call MsgBox(Err.Number & " - " & Err.Description)
' Exit Sub
'End Select

End Sub




Is my SQL statement being sent at the right place or should I put it in my CommandText?

[Robert Moy]

Hello:

You can try this code to get SQL query. I am using Oracle 8i as my database software. I hope this can help.
I use this code to connect to the database:
Adodc1.ConnectionString = "Provider=MSDAORA. 1;Password=" & Text2.Text & ";User ID=" & Text1.Text & ";Persist Security Info=True"
Adodc1.CommandType = adCmdUnknown

Then I use this code to do the query:
Adodc1.RecordSource = "Select " & Text3.Text & _
" from " & Text4.Text & _
" WHERE " & Text5.Text & "=" & Text6.Text
Adodc1.Refresh

I use Adodc1.Record to put a SQL query.

Good Luck

[jignasu]

I m also facing the same problem.Since i m using MS access.I think that problem is on recordset side.U first set all the required properties of the recordset and then try the same.
All the best