Visual Basic 6 connection to Oracle Database

I have VB6 program that is quering an Oracle Database.  The connection string works but my first SQL statement freezes and then kicks me out of the program.  This code is the same being used in other locations that works.

Do you have any guidance on why this would occur at this specific location?

The code is as follows:

Dim myconn as New ADODB.Connection
Dim rs As New ADODB.Recordset

sqlcon$ = "Driver={Oracle in OraClient11g_home1};DBQ=IMS11;Uid=Me;Pwd=Password;"

myconn.Open sqlcon$

*************The myconn.Open works************************

h1$ = "select *"
h2$ = " FROM IMS.SAMPLES"
h3$ = " WHERE IMS.SAMPLES.HSN = 2000003001"
hi$ = h1$ & h2$ & h3$
sqls1$ = hi$

rs.Open sqls1$, myconn, adOpenStatic, adLockOptimistic

*************The rs.Open statement freezes and then kicks me out of the program ***
AndrewBanferAsked:
Who is Participating?
 
ltlbearand3Connect With a Mentor Commented:
@vadimrapp1 - I agree that normally it should not cause an issue like the code being hung.  As you stated if the object is not nothing then the property should either return a number or -1.  It should not hang like is being reported.  Just as a coding best practice I would use EOF and BOF and not RecordCount > 0 and I am curious to see how the code behaves with that setup.

@AndrewBanfer - Please post your more of your code (or full code of this section) as we obviously don't see everything and also post the requested debug.print statements.  Give the EOF/BOF a try and report on the results.  That will help us help you.  Thanks.

-Bear
0
 
eemitCommented:
Have you tried:
h3$ = " WHERE IMS.SAMPLES.HSN = '2000003001'"
0
 
AndrewBanferAuthor Commented:
HSN is a numeric field.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
AndrewBanferAuthor Commented:
Actually the line h3$ is the problem.

h1$ = "select *"
h2$ = " FROM IMS.SAMPLES"
h3$ = " WHERE IMS.SAMPLES.HSN = 2000003001"
hi$ = h1$ & h2$ & h3$
sqls1$ = hi$

I'm sending an array to build the h3$ line.  This doesn't work.

h3$ = " WHERE IMS.SAMPLES.HSN = " & samplearray(i%)

samplearray(1) = 2000003001

If I use h3$ without the sample array then it works:

h3$ = " WHERE IMS.SAMPLES.HSN = 2000003001"
0
 
ajexpertCommented:
I am not sure why have you included i%

h3$ = " WHERE IMS.SAMPLES.HSN = " & samplearray(i)

Open in new window

0
 
AndrewBanferAuthor Commented:
It's an array like so:

For i% = 1 To List1.ListCount

    h1$ = "select *"
    h2$ = " FROM IMS.SAMPLES"
    h3$ = " WHERE IMS.SAMPLES.HSN = " & sampleidarray(i%)
    hi$ = h1$ & h2$ & h3$
    sqls1$ = hi$

    rs.Open sqls1$, myconn, adOpenStatic, adLockOptimistic
    ....
    rs.close
next i%
0
 
ajexpertCommented:
If I remember correctly, VB arrays start from 0
try the following:

For i% = 0 To List1.ListCount-1

    h1$ = "select *"
    h2$ = " FROM IMS.SAMPLES"
    h3$ = " WHERE IMS.SAMPLES.HSN = " & sampleidarray(i%)
    hi$ = h1$ & h2$ & h3$
    sqls1$ = hi$

    rs.Open sqls1$, myconn, adOpenStatic, adLockOptimistic
    ....
    rs.close
next i% 

Open in new window

0
 
Vadim RappCommented:
Specify increased command timeout:

myconn.commandtimeout=600  ' in seconds; default=30

for the connection; or for this specific command:

Dim cmd As New ADODB.Command, rs As Recordset
cmd.CommandTimeout = 600 ' in seconds; default=30
cmd.CommandText = sqlcon
Set cmd.ActiveConnection = myconn
Set rs = cmd.Execute

Also, handle the errors, so you see what error occurs. The above assumes that the command timed out.

on error goto errorhandler
<your code>
errorhandler:msgbox err.description
0
 
AndrewBanferAuthor Commented:
Hi Ajexpert,

It is not the array that is the issue.  If you look at h3$ line above sampleidarray(1) will get a value of  2000003001.

So,  the h3$ line becomes " WHERE IMS.SAMPLES.HSN = 2000003001"

Then the rs.Open command doesn't work.

If I remove the h3$ line with the array and replace it with the line:

h3$ = " WHERE IMS.SAMPLES.HSN = 2000003001"

Then the rs.Open command works.

Basically it is the same thing but when I concatenate the array to the line the rs.Open doesn't work.

Does the samplearray(i%) variable need to be assigned to a certain type of variable before concatenating?

Thanks,
0
 
ajexpertCommented:
What type is sampleidarray(i%) ?
0
 
AndrewBanferAuthor Commented:
Type is String
0
 
AndrewBanferAuthor Commented:
Hi vadimrapp1,

I have tried the increased command timeout.  It didn't help.

I have an error handler but no error appears.

Thanks,
0
 
Vadim RappCommented:
So, if you do it in the debugging mode, the rs.open runs for a while, and then what happens - VB crashes?

Did you try to run the same SQL manually, not from VB?
0
 
aikimarkCommented:
Add an Option Explicit in the General Declarations section of your module/form.  Rerun your test and post back the results in this thread.

You have used both samplearray(i%) and sampleidarray(i%) and I suspect the problem stems from lack of explicit variable declaration.
0
 
Vadim RappCommented:
In your initial post the sql statement was like this:

h3$ = " WHERE IMS.SAMPLES.HSN = 2000003001

and you said it did not work. Then later you said

If I use h3$ without the sample array then it works:
h3$ = " WHERE IMS.SAMPLES.HSN = 2000003001"

Please clarify, what exactly does not work. Throw out the variables, and try this:

rs.Open "select * FROM IMS.SAMPLES WHERE HSN = 2000003001", myconn, adOpenStatic, adLockOptimistic

Open in new window

Does that work?
0
 
AndrewBanferAuthor Commented:
Hi vadimrapp1,

Yes it works when I throw out the variables.

Doesn't work when I concatenate the sampleid array variable to the rest of the line.

h3$ = " WHERE IMS.SAMPLES.HSN = " & sampleidarray(i%)

Thanks,
0
 
Vadim RappCommented:
Try this:

dim MySqlStatement as string
MySqlStatement =  h1$ & h2$ & h3$
debug.print  MySqlStatement

rs.open MySqlStatement , myconn, adOpenStatic, adLockOptimistic

If rs.open does not work, inspect what debug.print has printed, and compare to the one that works, with the constant.
0
 
AndrewBanferAuthor Commented:
Ok, the first SQL command now works but the second doesn't.

Is there any chance this could have something to do with hard drive memory available?  

The drive that is running the program and SQL commands on only has <  1 GB.

Thanks,
0
 
aikimarkCommented:
did you add the Option Explicit statement?
0
 
Vadim RappCommented:
> Is there any chance this could have something to do with hard drive memory available?

No.
0
 
ltlbearand3Commented:
I have seen sometimes when connecting to different databases that you must "finish" off the string if it is numeric when creating the SQL statment.  Try this code.

Also please put the debug.print values (what is working and not working)

For i% = 0 To List1.ListCount-1

    h1$ = "select *"
    h2$ = " FROM IMS.SAMPLES"
    h3$ = " WHERE IMS.SAMPLES.HSN = " & sampleidarray(i%) & ""
    hi$ = h1$ & h2$ & h3$
    sqls1$ = hi$

    rs.Open sqls1$, myconn, adOpenStatic, adLockOptimistic
    ....
    rs.close
next i% 

Open in new window


-Bear
0
 
AndrewBanferAuthor Commented:
After trying all of the recommendations and other items it looks like the problem is due to using commands such as:

if rs.Recordcount > 0 then

The recordcount is causing the program to hang up and then it closes.
0
 
Vadim RappCommented:
Previously, we found that when you send the command as constant, it works, but when you send the same command in a variable, it does not; accordingly one of the recommendations was to debug.print the constant and the variable*, and compare them. Does your comment mean that you did that and found the strings equal?



*) In fact, even better to print them surrounded by some visible separators, to see any leading and trailing spaces: debug.print "|" & myvariable & "|"
0
 
ltlbearand3Commented:
I agree that we really need the debug.print statements.  Also, now we are looking at a slightly different issue.  The .RecordCount property can be picky at times depending on the cursor type used in your code and if you have navigated to any records.  I recommend using something like this instead:

If not (rs.EOF and rs.BOF) then

Open in new window


EOF is End of File and BOF is beginning of file.  If the EOF and BOF flags are both true then no records where returned.

-Bear
0
 
Vadim RappCommented:
@ltlbearand3 - "The .RecordCount property can be picky at times" - right, but I never heard about reading it causing any actual processing. If it's already populated, then it's there; if not, then it's -1 and you have to movelast to get the real number.
0
 
AndrewBanferAuthor Commented:
Hi Bear,

The following statement worked in place of "if rs.recordcount > 0 then"

if not rs.bof and not rs.eof then

Thanks for your help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.