INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

VB and PSQL issues

VB and PSQL issues

(OP)
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:

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

RE: VB and PSQL issues

(OP)
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.

RE: VB and PSQL issues

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

RE: VB and PSQL issues

(OP)
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

RE: VB and PSQL issues

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

RE: VB and PSQL issues

(OP)

CODE

    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)

RE: VB and PSQL issues

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close