bind variables and sql

[cptkirkh]

I a trying to store certain sql queries in a table to reference
later. I want to perform a sql query to see if they are in the table
and i want to use bind variables. HOw can i do this?

Here is a sample of my code:
Dim sqltorun As String = "select max(ec_export_number) from
event_campaign " & _
" where EC_PURPOSE
= :event_purpose AND EC_ITEM_SENT = :item_sent" & _
" AND EC_SQL = :sql_totalcount "




Dim cmd As New OracleCommand(sqltorun, myConnection)



Try
cmd.Connection.Open()
Catch ex As Exception
MsgBox("Please close the program and reopen to establish a
new connection and if it happens again call MIS" + export_date,
MsgBoxStyle.OkOnly, title)
End Try

cmd.Parameters.Add(":sql_totalcount", OracleDbType.Varchar2).Value =
"select cli_rid from dual"
cmd.Parameters.Add(":event_purpose",
OracleDbType.Varchar2).Value = "20070511TXAM"
cmd.Parameters.Add(":item_sent", OracleDbType.Varchar2).Value
= "POSTCARD"

Try 'go and get the export number if false no query has been run
before


export_num = cmd.ExecuteScalar

When i get to this step i get illegal number or vairable as the
exception.
This sql code works just fine when i run it in sql plus or toad.
Thanks for your help.

 

[Guest]

cptkirkh,

Try naming your parameters without the colon. For example:

cmd.Parameters.Add("sql_totalcount", ...

Kerry Moorman

 

[cptkirkh]

cptkirkh,

Try naming your parameters without the colon. For example:

cmd.Parameters.Add("sql_totalcount", ...

Kerry Moorman












- Show quoted text -

Kerry,
When i do that i now get "conversion from type DBnull to string
is not valid" This tells me that the query is returning nothing. If
I change my code to look like this it returns the value I am looking
for:

Dim sqltorun As String = "select max(ec_export_number) from
event_campaign " & _
" where EC_PURPOSE
= :event_purpose AND EC_ITEM_SENT = :item_sent" & _
" AND EC_SQL = 'select cli_rid from dual' "




Dim cmd As New OracleCommand(sqltorun, myConnection)



Try
cmd.Connection.Open()
Catch ex As Exception
MsgBox("Please close the program and reopen to establish a
new connection and if it happens again call MIS" + export_date,
MsgBoxStyle.OkOnly, title)
End Try





'cmd.Parameters.Add("sql_totalcount",
OracleDbType.Varchar2).Value = "select cli_rid from dual"
cmd.Parameters.Add("event_purpose",
OracleDbType.Varchar2).Value = "20070511TXAM"
cmd.Parameters.Add("item_sent", OracleDbType.Varchar2).Value =
"POSTCARD"


Try 'go and get the export number if false no query has been
run before




export_num = cmd.ExecuteScalar

 

[Guest]

cptkirkh,

Shouldn't the select subquery be in parentheses, not single quotes?

Kerry Moorman

 

[cptkirkh]

cptkirkh,

Shouldn't the select subquery be in parentheses, not single quotes?

Kerry Moorman












- Show quoted text -

No maybe i am saying this a little confusing. I have an application
that allows a user to export info from our database that the user can
choose the criteria he wishes and to track what they extracted i have
created a table that stores what SQL query they ran in the past. That
way if they run the same query in the future it tells them that they
ran this same query before by looking up the column that holds the sql
and comparing it what they are running at that time. So with that in
mind it isn't a subquery or at least i don't think it should be. Does
all that make sense?