How do you do Bind variables in INSERT statement when using ODAC in VB.NET
Hi,
We are new to VS2008\VB.NET and we have written a program where the values of two string variables have gotten too large, we get error ORA-01704: string literal too long. We have created an XML string that probably exceeds the max character count in "tmpString" combined with all the other strings that are concatenated together. I need to modify the code below to replace the "tmpString" and "tranBody" string variables with bind variables so the insert command is not so long that it exceeeds Oracle's limit. Are there some examples somewhere on how to set up the insert and how to do the bind variable substitution?
Thanks in advance:
Bruce
' insert the record into the database
Dim tmpString As String
Dim tranBody As String
Dim appName As String
Dim wkString As String
Dim myTransaction As OracleTransaction
Try
myConnection.ConnectionString = objInetUtils.getConnectionString("ITRAN")
myConnection.Open()
myTransaction = myConnection.BeginTransaction
Dim sqlCommand As String
sqlCommand = "insert into my_table (transactionid, ip_address,orig_source,orig_date,chg_source,chg_date,application,notification_comments, notification_subject, transaction_body) " _
& "values(to_number('" _
& (Str(tmpInt) & "'),'" _
& objInetUtils.myIpAddress & "','" _
& appName & "'," _
& "to_date('" & DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss") & "', 'mm/dd/yyyy hh24:mi:ss'),'" _
& appName & "'," _
& "to_date('" & DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss") & "', 'mm/dd/yyyy hh24:mi:ss'),'" _
& appName & "','" _
& Replace(tmpString, "'", "''") & "','" _
& Replace(wkString, "'", "''") & "','" _
& Replace(tranBody, "'", "''") & "'" _
& ")")
Dim myDataCommand As New OracleCommand
myDataCommand.CommandType = CommandType.Text
myDataCommand.Connection = myConnection
myDataCommand.CommandText = sqlCommand
myDataCommand.ExecuteNonQuery()
myTransaction.Commit()
Catch ex As Exception
myTransaction.Rollback()
Throw ex
Finally
myConnection.Close()
myConnection.Dispose()
End Try
Edited by: user11218662 on Jun 18, 2009 2:31 PM
Edited by: user11218662 on Jun 18, 2009 2:31 PM