ODBC & Recordset 1

[ChrisBurch]

I have just managed to be able to connect to my Progress database, and return data to VB6, by hardcoding the 'WHERE' part of the query into the select statement. Can someone give some guidance as to how set this up so that it is a variable. ie. so I can pull it from a textbox. In my code below, I need to replace 'Desp' with a variable.

Sub Update()
Dim rs As Recordset
Set rs = CreateObject("ADODB.Recordset&quot
rs.Open "select * from wc where wc = 'Desp'", "dsn=syteline4;uid=;pwd=;"
Text1.Text = rs![wc]
Text2.Text = rs![Description]
Text3.Text = rs![Calendar]
Text4.Text = rs![charfld1]
rs.Close
Set rs.ActiveConnection = Nothing
Set rs = Nothing
End Sub

Thanks for any assistance,

Chris

It worked yesterday.
It doesn't work today.
That's Windows!

 

[kunmun23]

create a string...like

Dim strsql as String
strsql="Select * From wc WHERE wc=" & Text0.text

then say,

rs.open strsql


Note that you can give all the other options like uid and pwd in separate statements...or u can even merge them together in the same string but make sure u take care of the double quotes and single quotes....the best way is to display it in a msgbox before the rs.open statement so u can see exactly what string is being passed to rs.open

post if u still can't figure it out.

 

[ChrisBurch]

Thanks kunmun,

I tried every complex way of doing it, and entirely missed the obvious. DUH!!!

Chris

It worked yesterday.
It doesn't work today.
That's Windows!

 

[ChrisBurch]

I've tried every which way to build the select statement from stings, and I fail every time with a Run time error 3709. Which states 'The connection cannot be used to perform this operation. it is either closed or invalid in this context'.

To try to isolate the problem, I've tried to simply reproduce the working statement from strings, but still hit the 3709 error.

My original statment was:-

rs.Open "select * from wc where wc = 'desp'", _
"dsn=Syteline4;uid=;pwd=;"

I have split this up as:-

MySel = Chr(34) & "select * from wc where wc = " & "'desp' " _
& Chr(34) & Chr(44) & Chr(34) & "dsn=Syteline4;uid=;pwd=;" & Chr(34)
MsgBox MySel
rs.Open MySel

When I compare MySel in the message box, and the original statement, they look identical, but I'm still getting the 3709 error.

Can anyone off more help.

Thanks,

Chris

It worked yesterday.
It doesn't work today.
That's Windows!

 

[kunmun23]

ok it seems the problem is u r not instantiating a new recordset or u r not instantiating a new connection....or..u r not setting the active connection for the recordset to the current connection...
if u r already connected to the database type this line before rs.open...

set rs.activeconnection=cm

where cm is ur connection object which connects to the database...post here if u still can't get it to work..

 

[AndyWatt]

MySel = "SELECT * FROM wc WHERE wc = '" & "desp" & "'" , _
"dsn=Syteline4;uid=;pwd=;"

Andy
"Logic is invincible because in order to combat logic it is necessary to use logic." -- Pierre Boutroux
"A computer program does what you tell it to do, not what you want it to do." -- Greer's Third Law