Solved

Visual Basic 6 connection to Oracle Database

Posted on 2013-05-21
28
667 Views
Last Modified: 2013-06-02
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 ***
0
Comment
Question by:AndrewBanfer
  • 10
  • 7
  • 3
  • +3
28 Comments
 
LVL 15

Expert Comment

by:eemit
ID: 39187238
Have you tried:
h3$ = " WHERE IMS.SAMPLES.HSN = '2000003001'"
0
 

Author Comment

by:AndrewBanfer
ID: 39187339
HSN is a numeric field.
0
 

Author Comment

by:AndrewBanfer
ID: 39190371
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
 
LVL 14

Expert Comment

by:ajexpert
ID: 39190465
I am not sure why have you included i%

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

Open in new window

0
 

Author Comment

by:AndrewBanfer
ID: 39190525
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
 
LVL 14

Expert Comment

by:ajexpert
ID: 39190577
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
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39190588
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
 

Author Comment

by:AndrewBanfer
ID: 39190598
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
 
LVL 14

Expert Comment

by:ajexpert
ID: 39190617
What type is sampleidarray(i%) ?
0
 

Author Comment

by:AndrewBanfer
ID: 39190630
Type is String
0
 

Author Comment

by:AndrewBanfer
ID: 39190641
Hi vadimrapp1,

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

I have an error handler but no error appears.

Thanks,
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39190667
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
 
LVL 45

Expert Comment

by:aikimark
ID: 39190684
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39190710
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
 

Author Comment

by:AndrewBanfer
ID: 39190819
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
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39190886
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
 

Author Comment

by:AndrewBanfer
ID: 39192350
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
 
LVL 45

Expert Comment

by:aikimark
ID: 39192404
did you add the Option Explicit statement?
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39192416
> Is there any chance this could have something to do with hard drive memory available?

No.
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 39193085
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
 

Author Comment

by:AndrewBanfer
ID: 39203496
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
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39204374
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
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 39206607
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
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39207779
@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
 
LVL 20

Accepted Solution

by:
ltlbearand3 earned 500 total points
ID: 39209679
@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
 

Author Closing Comment

by:AndrewBanfer
ID: 39215309
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

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
Having just graduated from college and entered the workforce, I don’t find myself always using the tools and programs I grew accustomed to over the past four years. However, there is one program I continually find myself reverting back to…R.   So …
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now