VB and PSQL issues

[datenis]

I'm trying to create a select statement to Pervasive using VB6 and ODBC

the issues I'm having is that for some reason when I use the following code:

sqlString = "Select  * from ORDER_DTL where ORD_ORDER_NO like '%" & ORD_ORDER_NO & "%'"   
rs.Open sqlString, cnBV, , , adCmdTable

there is a "Select * from" automatically inserted at the beginning of the sqlString and executed in this fashion, and therefore I get a syntax error:

"Syntax Error: select * from Select<<???>> * from ORDER_DTL where ORD_ORDER_NO like '%700500%'"

However, when I print out sqlString as text and run it on the PSQL query analyzer, the query ran fine without the extra select * from.

I was able to step around this issue by setting the rs.source spearately and then call rs.open after.

However, even if the query runs it doesn't return any records, but if I run the text on sqlString on the PSQL query analyzer it returns me the appropriate records.

Please help

- d

 

[datenis]

I take part of the above post back, the CMDadTable should not have been there at the first place, so the first problem is solved.

However, I'm still having issues with not returning the proper records in vb. However, if I change sqlString to "Select * from ORDER_DTL" it returns the approriate records.

 

[mirtheil]

The adCmdTable option tells ADO that you are passing a table name and the PSQL driver addes the extra "select *".
What do you mean by "not returning the proper records"? Are you seeing missing records or incorrect records?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[datenis]

To clarify, I'm not getting any records if I do a rs.open, but I was able to get the proper records when I run the output of sqlString on the PSQL query analyzer

 

[mirtheil]

How do you determine that you are getting no records? What's your code look like?


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[datenis]

    Dim cnBV As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim sqlString As String   
    
    cnBV.Open "BVE"
    sqlString = "Select  * from ORDER_DTL where ORD_ORDER_NO like '%" & ORD_ORDER_NO & "%'"
    rs.Open sqlString, cnBV
    getOrderDetailSequence = rs(1)
    
    Set rs = Nothing
    Set cnBV = Nothing

I get an EOF error when the program hits getOrderDetailSequence = rs(1)

 

[mirtheil]

How many records do you get if you run the statement in the Pervasive COntrol Center?
What happens if you hard code a value instead of using "ORD_ORDER_NO"?
Is BVE your DSN name? Is it possible you're pointing to a different version of the data?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com