Problem with a selective sql statement

[mrmotocross]

hello, I am having a problem with an sql statement. Below is the code i'm using:


Query6 = "SELECT * FROM EmpDetail where Activity = '" & HoldActivity & "' ORDER by Activity, Date;"


Select Case ReportNumber

Case 0
SQL = Query1
Case 1
SQL = Query2
Case 2
SQL = Query3
Case 3
SQL = Query4
Case 4
SQL = Query5
Case 5
SQL = Query6
Case 6
SQL = Query1
Case 7
SQL = Query7
Case 8
SQL = Query8
Case Else
Debug.Print " "
End Select


Dim QryConn As New ADODB.Connection
QryConn.ConnectionString = NesConnString
QryConn.Open

Dim QryRst As New ADODB.Recordset

Dim QryComm As New ADODB.Command
QryComm.ActiveConnection = QryConn
QryComm.CommandText = SQL
QryComm.CommandType = adCmdText
Set QryRst = QryComm.Execute

QryRst.MoveFirst

On the execute i receive 'bof or eof encounterd' or therefor it is not finding any records. If i use a sql statement using 'select *' it works fine. What am i doing wrong?

Thanks, liboy1

 

[Swi]

Do a check to see if the recordset is empty before doing anything with the recordset:

If QryRst.BOF And QryRst.EOF Then
Msgbox "No records returned for the criteria specified!", vbCritical
Exit Sub
Else
QryRst.MoveFirst
End If

Swi

 

[WebGuyToo]

Try adding...

Dim QryRst As ADODB.Recordset

WebGuyToo

 

[WebGuyToo]

sorry, you already did that.

Swi is correct on checking to see if the rs is empty first. Youcan also use:

If Not QryRst Is Nothing Then

QryRst.MoveFirst

Do Until QryRst.EOF

code...

QryRst.MoveNext
Loop

 

[mrmotocross]

hi mrmotocross again. I think i thru you off as to what my problem is. What i actually meant was that i should have received records back from the sql statement and i wanted to know what i was doing wrong in not getting the records.
Do you see any problem with the code? Thanks again, bob

 

[fredericofonseca]

is Activity a numeric field or a alphanumeric one? If numeric then remove the "'" from around the HoldActivity value.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[mrmotocross]

mr motocross here. The Activity field is alphanumeric and it is being compared to a string field, 'HoldActivity'. thanks, bob

 

[DrJavaJoe]

debug.print Query6 and then place the results in Query Analyser and run to see if it is a valid SQL statement. You will probably find that the value of HoldActivity has extra spaces or isn't what you think it is.



Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'.

 

[tudogs]

Have you tried brackets around your table names and field names?

Good Luck

Bob


Daughters are Gods Vengeance on Fathers for being Men.

 

[Swi]

As tudogs states, add brackets around the table and field names. Date in your WHERE clause is a reserved word.

Swi

 

[Swi]

I'm sorry, I meant in your ORDER BY clause.

Swi

 

[mrmotocross]

Hello mr motocross here, is there anyone out there who has a sample of an sql statement that contains a WHERE clause with a comparison to a variable? thanks, bob

 

[HarleyQuinn]

WHERE YOURFIELD = '" & strYourstring & "'"

Harleyquinn

---------------------------------
For tsunami relief donations

 

[fredericofonseca]

Query6 = "SELECT * FROM EmpDetail where Activity = '" & HoldActivity & "' ORDER by Activity, [Date]"



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com