run time error 3065: Can not execute select quer

[rkinfo]

I am trying to indert record in oracle database and getting error run time error 3065: Can not execute select query
Any idea?
strIN = "INSERT INTO xdatafactorS( MeterID, DATE_, AFApplied, ISDefault, CALCDATETIME, PeriodGrpID)" & _
"VALUES (" & Meterid & ", to_date(" & dateset & ",""dd/mm/yyyy""), 1, 1, to_date(" & dateset & ",""dd/mm/yyyy"") , " & crntperiodgrpid & " );"

Set qdf = db.CreateQueryDef("inrec1", strIN)
qdf.Connect = "ODBC;DSN=GDRSS;UID=GDRSS;SERVER=GDRSS"
Date1 = CStr(dateset)
date2 = CStr(Now())
MsgBox Date1
MsgBox date2
qdf.sql = strIN
qdf.ODBCTimeout = 0
qdf.Execute
qdf.Close

 

[Zion7]

maybe...

strIN = "INSERT INTO xdatafactorS( MeterID, DATE_, AFApplied, ISDefault, CALCDATETIME, PeriodGrpID)" & _
"VALUES (" & Meterid & ",#" & to_date(" & dateset & ",""dd/mm/yyyy"") & "#, 1, 1, #" & to_date(" & dateset & ",""dd/mm/yyyy"") & "# , " & crntperiodgrpid & " );"

actually, I retract.
What is, to_date(" & dateset & ",""dd/mm/yyyy"")?

 

[HarleyQuinn]

Zion7 - to_date is an oracle function to convert a string into a date.

rkinfo - you may want to use some single quotes in this as it is Oracle. e.g.

"INSERT INTO xdatafactorS( MeterID, DATE_,  AFApplied, ISDefault, CALCDATETIME, PeriodGrpID)" & _
           "VALUES (" & Meterid & ", to_date([red]'[/red]" & dateset & "[red]'[/red],[red]'[/red]dd/mm/yyyy[red]'[/red]),  1, 1, to_date([red]'[/red]" & dateset & "[red]'[/red],[red]'[/red]dd/mm/yyyy[red]'[/red])  , " & crntperiodgrpid & " );"

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Zion7]

Thank-you Harley-Quinn.

Bear with me here, not being familiar with the to_date function, I'm assuming it's a conversion & formatting function. should it not read to_date(dateset,'dd/mm/yyy')?

If that is the case then...

VALUES (" & Meterid & ",#" & to_date(dateset,'dd/mm/yyyy') & "#, 1, 1, #" & to_date(dateset,'dd/mm/yyyy') & "# , " & crntperiodgrpid & " );"

DON"T FORGET THE HASH MARKS

 

[HarleyQuinn]

Zion7,

You are correct, it is a conversion and formatting function.

From reading the OP I believe that dateset is a variable storing a text value so needs to be included as I did in my original post

to_date('" & dateset & "','dd/mm/yyyy')

If the value is indeed text then it will need to be inclosed in 's (as above).
As it's been a while since I stored DATE values in Oracle I can't remember if you do NEED the hashes, I'm pretty sure that in my last insert I didn't use them and it was inserted fine. Can't confirm until I'm back at work on Monday though.

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Zion7]

Yes HarleyQuinn, I'm following now.

Sorry about that, I was paying the right amount of attention, to the initial posts.

Thanks for the clarification!

 

[HarleyQuinn]

Zion7,

No problem. Just hope between us we've manged to help solve rkinfo's problem!

Cheers

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[rkinfo]

Now I am getting error runtime error 3146 odbc-call failed I tried both the syntax
strin = "INSERT INTO xdatafactorS( MeterID, DATE_, AFApplied, ISDefault, CALCDATETIME, PeriodGrpID)" & _
"VALUES (" & Meterid & ",# to_date(" & dateset & ",'dd/mm/yyyy')#, 1, 1, #to_date(" & dateset & ",'dd/mm/yyyy')# , " & crntperiodgrpid & " );"

or

strin = "INSERT INTO xdatafactorS( MeterID, DATE_, AFApplied, ISDefault, CALCDATETIME, PeriodGrpID)" & _
"VALUES (" & Meterid & ", to_date(" & dateset & ",'dd/mm/yyyy'), 1, 1, to_date(" & dateset & ",'dd/mm/yyyy') , " & crntperiodgrpid & " );"

 

[rubbernilly]

Perhaps add a space in the string after the first line and before the word "VALUES"?

 

[rkinfo]

It did not work. I think it has some issue with date format as I was able to insert a record if I try to insert string/varchar2 instead of ddate.

 

[PHV]

As you don't use a passthru query, you may try something like this:
strin = "INSERT INTO xdatafactorS( MeterID, DATE_, AFApplied, ISDefault, CALCDATETIME, PeriodGrpID)" & _
"VALUES (" & Meterid & ", #" & Format(dateset, "yyyy-mm-dd") & "#, 1, 1, #" & Format(dateset, "yyyy-mm-dd") & "#, " & crntperiodgrpid & " );"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rkinfo]

Finally: It worked
strIN = "INSERT INTO xdatafactorS( MeterID, DATE_, AFApplied, ISDefault, CALCDATETIME, PeriodGrpID) " & _
"VALUES (" & Meterid & ", TO_DATE('" & Format(dateset, "MM/DD/YYYY") & "','MM/DD/YYYY'), 1, 1, TO_DATE('" & Format(Now(), "MM/DD/YYYY HH:MM:SS") & "','MM/DD/YYYY HH24:MI:SS')," & crntperiodgrpid & " );"

Set qdf = db.CreateQueryDef("inrec", strIN)
qdf.Connect = "ODBC;DSN=ORAACC;UID=MSGDRSS;SERVER=GDRSS"
Date1 = CStr(dateset)
date2 = CStr(Now())

qdf.sql = strIN
qdf.ODBCTimeout = 0
qdf.ReturnsRecords = False
qdf.Execute
qdf.Close
For Each qdf In db.QueryDefs
If qdf.Name = "inrec" Then
db.QueryDefs.Delete qdf.Name
End If
Next qdf