On Aug 8, 8:42 am, Kerry Moorman
<KerryMoor...@discussions.microsoft.com> wrote:
> cptkirkh,
>
> Shouldn't the select subquery be in parentheses, not single quotes?
>
> Kerry Moorman
>
>
>
> "cptkirkh" wrote:
> > On Aug 7, 5:12 pm, Kerry Moorman
> > <KerryMoor...@discussions.microsoft.com> wrote:
> > > cptkirkh,
>
> > > Try naming your parameters without the colon. For example:
>
> > > cmd.Parameters.Add("sql_totalcount", ...
>
> > > Kerry Moorman
>
> > > "cptkirkh" wrote:
> > > > 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.- Hide quoted text -
>
> > > - 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- Hide quoted text -
>
> - 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?
|
Similar Threads
