[tbzserge]

Hi,i'm having trouble connecting Oracle using vb6 ADODB.

I just want to return the column data in table at corresponding textbox. I get the error message saying that there is no table pointed to, so i did change to
MyRecSet.Open ,"Table1",strSQL, MyConnect, , , adCmdTable
in below code.

But it gave this compile error instead.
Wrong number of arguments or invalid property assignment.

Below is a snippet of my code for refer.

Private Sub txtCTN_KeyDown(KeyCode As Integer, Shift As Integer)
Dim MyConnect As New ADODB.Connection
Dim MyRecSet As New ADODB.Recordset
Dim strSQL As String
Dim conn As String

 If KeyCode = 13 Then

 Set MyConnect = New ADODB.Connection
 MyConnect.ConnectionString = "Driver={Microsoft ODBC for Oracle};" & _
 "UID = username; PWD = password"
 MyConnect.Open
 
 If Trim(txtCTN.Text = "") Then
                MsgBox "Please Scan the CTN!", vbOKOnly, "Field Required!"
                txtCTN.SetFocus
           Else

           strSQL = "select DATE from TABLE1 where CTN = " & txtCTN.Text
           MyRecSet.CursorType = adOpenStatic
           MyRecSet.CursorLocation = adUseClient
           MyRecSet.LockType = adLockOptimistic
           'The error points to below this line
           MyRecSet.Open strSQL, MyConnect, , , adCmdTable
      
           
           'Check if whether the CPN is in the table or not
           If MyRecSet.EOF = False And MyRecSet.BOF = False Then
           txtDate.Text = Trim(MyRecSet.Fields("" & Trim(GetINISetting("Db_Data", "F_Date", App.Path & "\SETTINGS.INI")) & ""))
 
           'other code below here
           '....
End Sub

Any help would be much appreciated. Thanks.

[andrewsw]

The code in your posted-text:

MyRecSet.Open ,"Table1",strSQL, MyConnect, , , adCmdTable

has an incorrectly placed comma at the beginning, which makes the first argument null, or missing. However, this is different to the line that appears in your posted code.

Check the ORDER AND NUMBER OF ARGUMENTS required for the .Open method. Learn to debug your code as well, as that error message is quite clear.

Also, DATE should not be used as a field-name, and would need to be surrounded with, possibly, square brackets or back-ticks.

This post has been edited by andrewsw: 17 March 2013 - 02:47 AM

[BobRodes]

I'm looking at your code, and the defect that Andrew mentions isn't in your example. However, the error is pretty explicit: it suggests that you don't have a table called "TABLE1" in your database. Which you probably don't--Table1 isn't a good name for a table in a database after all. You're probably trying to open an Oracle table that doesn't exist, since that's what the error is saying.

[andrewsw]

strSQL = "select DATE from TABLE1 where CTN = " & txtCTN.Text

This may require apostrophes around txtCTN.Text, if it is a text-value rather than a number. And, as mentioned, DATE is a reserved word in Oracle. I don't know whether Oracle uses a delimiter (such as square brackets or back-ticks) to allow the use of reserved-words as identifiers, but it is preferable to rename the field.

I am not very familiar with Oracle so Bob might correct me.

@BobRodes I was suspicious of the discrepancy between the post-text and posted-code so just suggested the OP double-check the order and number of arguments for .Open

[tbzserge]

Thanks for the reply.

I took the advice and have changed my table name to RTS and column name for date as DAY.
When i change the code to as below,

MyRecSet.Open "RTS",MyConnect, , , adCmdTable

The textbox did return a date value however it didn't run the query for strsql as i didn't put it.

What confusing me is i couldn't find a way to integrate the query here.

strSQL = "select DAY from RTS where CTN = " & txtCTN.Text

I have also double-check the order and number of arguments for .Open and what i had done so far is match with it.

Hopefully i can get advices from here again. I'm new to oracle too actually.Thanks.

[BobRodes]

You didn't need to change strSQL to "RTS". You needed to do what you did in your second code example. If it doesn't work for you, then follow Andrew's advice and put the single quotes around Text1.Text.

And please don't stop thinking for yourself. You're beginning to wander.