Re: Oracle and Visual Basic

In article <48b0d3$qci_at_cwis.isu.edu>, nelson_at_gollum.isu.edu says...
>
>Recently we have been trying to build some user interfaces in Visual Basic
>4.0 that would connect to our oracle 7 server (RS 6000 running AIX) using
>ODBC. We have the ODBC software configured correctly now and can
>manipulate the Oracle data using Microsoft Access. I have written code in
>Visual Basic to connect to an Access database file, but for some reason
>can't get the connection to the Oracle database to work correctly. I
>currently have an Access database file with a connection made to the
>Oracle database in it. If I try to perform a standard select statement in
>VB on a table that was created with Access it works correctly. If I
>perform the same select statement on one of the Oracle linked tables, I
>get the following error:
>
>Run time error '3061'
>
>Too few parameters. Expected 1
>
>This error seems like nonsense to me. Does anyone have a suggestion.
>This looks like a VB or Access rather than an Oracle problem, but I
>thought that someone else dealing with Oracle might have tried this
>before.
>
>Here is a shortened version of the Visual Basic code I am using:
>
>Private Sub Command3_Click()
>Dim Db As Database
>Dim MySQL As String
>
>
>Connect$ = "C:\Pharmacy.mdb"
>Set Db = OpenDatabase(Connect$)
>
>MySQL = "SELECT PAT_ID, GEN_NAME, PROV_NO, PRESCRIBER_NO, NDC_CODE,
>F_DATE_SERVICE FROM RXHX WHERE [GEN_NAME] = " & Chr$(39) & List2.List(0) &
>Chr$(39)
>
>For I% = 1 To (List2.ListCount - 1)
> MySQL = MySQL & " OR [GEN_NAME] = " & Chr$(39) & List2.List(I%) &
>Chr$(39)
>Next I%
>
>MySQL = MySQL & " ORDER BY [PAT_ID],[F_DATE_SERVICE]"
>
>Set MyQuery = Db.OpenRecordset(MySQL, dbOpenSnapshot)
>
>{Code omitted for sake of bandwidth}
>
>Thanks.
>
>Travis Nelson
>Idaho State University
>Center for Decision Support
>

Travis,

The problem lies in your sql statement. A "parameter" is an input field - effectively a variable. Some field name or syntax is causing a problem inside the jet engine. Bad names are seen as arbitrary parameters needing a value rather than as errors.
Personnaly I always use the passthru flag and send sql native to oracle. I've found that ACCESS and ORACLE will except the same query 95% of the time. Without passthru, translations inside JET can adversely effect performance.

Joe McDermott Received on Sun Nov 19 1995 - 00:00:00 CET