Insert Query

[karlmcauley]

The following insert string is returning an error saying that there is a missing comma......i have tried the query in sql plus without the variables just hard coded values and it works no problem....

strDateTime = day(date) & "/" & month(date) & "/" & year(date)
strSQL1 = "INSERT INTO "
strSQL1 = strSQL1 & "web_based_reports_audit (WBRA_DATE_TIME_RUN, WBRA_REPORT_ID, WBRA_SQL) "
strSQL1 = strSQL1 & "VALUES('" & strDateTime & "', '" & lonReportID & "', '" & strSQL & "') "
objDBConn.Execute(strSQL1)

Is it the variables or mising commas or missing parenthesis?

Cheers

[rdoekes]

If the strSQL has quotes in it, you need to double these up. I do not know if you did this with the strSQL variable.

That's the only thing I can think of.

Hope this helps

[karlmcauley]

The variable strSQL is where the problem lies when trying to insert it into an Oracle database.

The strSQL variable is an sql string that is generated through a page which allows users to specify their own criteria for running a number of diferent reports interogating this Oracle database. For some reason Oracle does not like (via insert query) a single ' (quotation mark) in the strSQL variable.

As a result i have tried to format the strSQL before strSQL1 insert query gets executed via a function that replaces ' with `, but alas i get another problem when trying to pass strSQL to the function as it says there is a type mismatch. Can anyone help?

strDateTime = day(date) & "-" & month(date) & "-" & year(date)

strSQL1 = ""
strSQL1 = "INSERT INTO "
strSQL1 = strSQL1 & "web_based_reports_audit (WBRA_DATE_TIME_RUN, WBRA_REPORT_ID, WBRA_SQL) "
strSQL1 = strSQL1 & "VALUES(to_date('" & strDateTime & "', 'DD MM YYYY HH24:MI:SS'), " & lonReportID & ", '" & strSQL & "') "

strSQL1 = SQL_For_Insert(strSQL1)
objDBConn.Execute(strSQL1)

SQL_For_Insert (strSQL1) is as follows:
Function SQL_For_Insert(strSQL)
Dim iCount
Dim strText
Dim strNewWord

strText = ""
strNewSQL = ""

For iCount = 1 To Len(strSQL)
strText = Mid(strSQL, iCount, 1)

If strText = "'" Then
strNewSQL = strNewSQL & strText & "`"
Else
strNewSQL = strNewSQL & strText
End If
Next

SQL_For_Insert = strNewSQL

End Function

The function is placed in an include file on the same page that it is called from. Any ideas.

Cheers

[karlmcauley]

Sorry for being confusing.......

Just tried :

Replace strSQL,"'","`" placed before the insert string firstly and immediately after the insert string secondly (before it gets executed) and i coming up with the original error message:

Microsoft OLE DB Provider for Oracle error '80040e14'

ORA-00917: missing comma

/reports/report_generator.asp, line 95

this is line: objDBConn.Execute(strSQL1)

If i then response.write the strSQL and test it in sql plus it works fine and returns the data......if i response.write strSQL1 and test the entire insert query (that incorpoates strSQL i also get the above error.....

ORA-00917: missing comma

Now the replace strSQL,"'","'" does not seem to be working as there are no ' where there was ' previously in strSQL........when i change this in SQL Plus it works fine........but i cant change it in my original strSQL query as it is a normal select statement not an insert statement as strSQL1 is....

Am i making sense??

Many thx for your help....i have attached the results of strSQL1 in a text file.