Oracle Date problem?

Whats wrong with this code:

Public wldCn As New ADODB.Connection
Public wldRs As New ADODB.Recordset
Public wldQry As New ADODB.Command

Private Sub cmdOK_Click()
wldCn.Open "DSN=ORACLE7;uid=TESTUSER;pwd=TESTUSER;"

wldQry.CommandText = "Insert into prm (PRM_CODE,PRM_DESC,PRM_VALUE,PRM_CNAME,PRM_CDATE,PRM_UNAME,PRM_UDATE) values (?,?,?,?,?,?,?)"
wldQry.Parameters(0) = glbParamCode
wldQry.Parameters(1) = txtDesc.Text
wldQry.Parameters(2) = txtParamVal.Text
wldQry.Parameters(3) = "TESTUSER"
wldQry.Parameters(4) = UCase(Format(Now, "dd-MMM-yy"))
wldQry.Parameters(5) = "TESTUSER"
wldQry.Parameters(6) = UCase(Format(Now, "dd-MMM-yy"))

'Insert the data into the table
wldRs.CursorLocation = adUseClient
wldRs.Open wldQry, , adOpenStatic, adLockReadOnly
wldRs.Close
end sub

This fails on the wldrs.open with ORA-01858: a non-numeric character was found where a numeric character was expected.

I have alse tried:
wldQry.Parameters(6) = Now

but get the same error.  The desc of the table is:

 Name                            Null?    Type
 ------------------------------- -------- ----
 PRM_CODE                        NOT NULL CHAR(10)
 PRM_DESC                                 VARCHAR2(30)
 PRM_VALUE                                VARCHAR2(80)
 PRM_CNAME                       NOT NULL VARCHAR2(32)
 PRM_CDATE                       NOT NULL DATE
 PRM_UNAME                       NOT NULL VARCHAR2(32)
 PRM_UDATE                       NOT NULL DATE



LVL 2
pmcgivernAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JHausmannCommented:
I would imagine your problem results from:

wldQry.Parameters(4) = UCase(Format(Now, "dd-MMM-yy"))

_and_

wldQry.Parameters(6) = UCase(Format(Now, "dd-MMM-yy"))

which, I believe, is formatting the data as (using 02/09/2000):

09-FEB-00

Have you tried changing the "MMM" to "mm"?


0
JHausmannCommented:
As I look at it, Oracle might not like the hyphen, too (don't know, I use SQL Server 6.5/7.0). You might try replacing hyphens with "/"
0
SpriCommented:
I created a table and worked with the sample code that you posted.  The date that Oracle stores the information in in the 'dd-MMM-yy' format that you are using.

I implemented your version and recieved the error.  When I ran the query in SQLPlus it showed that it did not like:

'Insert into prm values('test','test1','test2','TESTUSER',09-Feb-00,'TESTUSER',09-Feb-00)'

It gave me an error at the second date and it place the * at the F in Feb.

I made a few changes to the setup in VB and came up w/ this and it worked.

I used "testX" for the text that you recieve from other places.

wldQry.CommandText = "Insert into prm values('" & "test" & "','" & "test1" & "','" & "test2" & "','" & "TESTUSER" & "','" & Format(Now, "dd-MMM-yy") & _
'    "','" & "TESTUSER" & "','" & Format(Now, "dd-MMM-yy") & "')"

Hope this helps.  If not, let me know.






0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

rovermCommented:
Just a tought: Isn't parm(0) the return parm ?
0
ceroCommented:
Why don't u use to_date function

p = to_date(format(Mydate,"dd-mm-yyyy"),"dd-mm-yyyy")

0
ceroCommented:
Well, with a INSERT INTO SQL.
"INSERT INTO ... Values(to_date(...),...)

0
pmcgivernAuthor Commented:
I've tried all of these suggestions already and still can't get it to work, although if I print the commandtext and paste it into SQLPLUS and run it there it works!!  Any other ideas?
0
mdouganCommented:
I saw a few things that are different from what I normally do.  First of all, if you are inserting into the database, do you really need to use a recordset object?

Here is a sample of your code using a straight Command Object.  I also noticed that you were not setting the ActiveConnection of the wldQry to wldCn, I think you need to do this.  But, in any event, here is some code where I am specifically typing the Parms, using ADODB.Parameter objects, and also am formating the date in mm/dd/yyyy format - which works for me.

Private Sub cmdOK_Click()
Dim CMD As ADODB.Command
Dim Parm0 As ADODB.Parameter
Dim Parm1 As ADODB.Parameter
Dim Parm2 As ADODB.Parameter
Dim Parm3 As ADODB.Parameter
Dim Parm4 As ADODB.Parameter
Dim Parm5 As ADODB.Parameter
Dim Parm6 As ADODB.Parameter
Dim sSQL As String

'I'd open my connection elsewhere and keep it open for the life of the program
wldCn.Open "DSN=ORACLE7;uid=TESTUSER;pwd=TESTUSER;"
   
      'PRM_CODE
        Set Parm0 = New ADODB.Parameter
        With Parm0
            .Direction = adParamInput
            .Type = adChar
            .Size = 10
        End With
       
      'PRM_DESC
        Set Parm1 = New ADODB.Parameter
        With Parm1
            .Direction = adParamInput
            .Type = adVarChar
            .Size = 30
        End With
       
      'PRM_VALUE
        Set Parm2 = New ADODB.Parameter
        With Parm2
            .Direction = adParamInput
            .Type = adVarChar
            .Size = 80
        End With
         
      'PRM_CNAME  
        Set Parm3 = New ADODB.Parameter
        With Parm3
            .Direction = adParamInput
            .Type = adVarChar
            .Size = 32
        End With
      
      'PRM_CDATE        
        Set Parm4 = New ADODB.Parameter
        With Parm4
            .Direction = adParamInput
            .Type = adDate
        End With
      
      'PRM_UNAME
        Set Parm5 = New ADODB.Parameter
        With Parm5
            .Direction = adParamInput
            .Type = adVarChar
            .Size = 32
        End With

      'PRM_UDATE        
        Set Parm6 = New ADODB.Parameter
        With Parm6
            .Direction = adParamInput
            .Type = adDate
        End With
       
    'Inserts new record into MASTER_ACCT_NOTES table
       sSQL = "Insert into prm"
       sSQL = sSQL & " (PRM_CODE,PRM_DESC,PRM_VALUE,PRM_CNAME,PRM_CDATE,PRM_UNAME,PRM_UDATE)
       sSQL = sSQL & " values (?,?,?,?,?,?,?)"
   
        Set CMD = New ADODB.Command
        With CMD
            .CommandText = sSQL
            .CommandType = adCmdText
            .ActiveConnection = wldCn
            .Parameters.Append Parm0
            .Parameters.Append Parm1
            .Parameters.Append Parm2
            .Parameters.Append Parm3
            .Parameters.Append Parm4
            .Parameters.Append Parm5
            .Parameters.Append Parm6
        End With
       
        wldCn.CursorLocation = adUseClient
           
        Parm0 = glbParamCode
        Parm1 = txtDesc.Text
        Parm2 = txtParamVal.Text
        Parm3 = "TESTUSER"
        Parm4 = Format(Now, "mm/dd/yyyy")
        Parm5 = "TESTUSER"
        Parm6 = Format(Now, "mm/dd/yyyy")
 
        CMD.Execute
     
        Set Parm0 = Nothing
        Set Parm1 = Nothing
        Set Parm2 = Nothing
        Set Parm3 = Nothing
        Set Parm4 = Nothing
        Set Parm5 = Nothing
        Set Parm6 = Nothing
        Set CMD = Nothing
   
End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pmcgivernAuthor Commented:
One thing I did have to change though:

In
'PRM_UDATE
Set Parm6 = New ADODB.Parameter
With Parm6
..Direction = adParamInput
..Type = adDate
End With


I had to change to
..Type = adDBTimeStamp

to get the parameter accepted.

Thanks!
0
mdouganCommented:
Yes, I checked another piece of code later and saw a case where I used adDBTimeStamp too.  Glad it worked for you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.