Problem injecting a VB parameter into a stored procedure (FireBird)

Everyone here has always been such great help, either directly or indirectly. And it is with grand hope that this, yet again, rings true.

For clarification sakes, the Stored Procedure is running under FireBird and the VB is of the .NET variety

I have a stored procedure (excerpt below, important bit is the WHERE)

  select pn, pnm.description, si_number, entry_date, cmp_auto_key, 
  parts_flat_price,    labor_flat_price, misc_flat_price, woo_auto_key, 
  wwt_auto_key
  from parts_master pnm, wo_operation woo
 where pn like :i_pn || '%'
   and pnm.pnm_auto_key = woo.pnm_auto_key
  into :pn, :description, :work_order, :entry_date, :cmp, :parts_price,
       :labor_price, :misc_price, :woo, :wwt

I am trying to pass a parameter from a vb app, that uses the parameter I_PN, the code of which follows below (The variables for MyServer and MyPassword are determined form an earlier part of the code.)

    Try
        Dim FBConn As New FirebirdSql.Data.FirebirdClient.FbConnection()
        Dim FBCmd As FirebirdSql.Data.FirebirdClient.FbCommand

        Dim MyConnectionString As String
        MyConnectionString = _
        "datasource=" & MyServer & ";database=" & TextBox4.Text & "; & _
        user id=SYSDBA;password=" & MyPassword & ";initial catalog=;"

        FBConn = New FirebirdSql.Data.FirebirdClient. & _
        FbConnection(MyConnectionString)

        FBConn.Open()
        FBConn.CreateCommand.CommandType = CommandType.StoredProcedure

        FBCmd = New FirebirdSql.Data.FirebirdClient. & _
        FbCommand("WIP_COSTS", FBConn)

        FBCmd.CommandText = "WIP_COSTS"

        FBConn.CreateCommand.Parameters. & _
        Add("@I_PN", FirebirdSql.Data.FirebirdClient.FbDbType.Text). & _
        Value = TextBox1.Text

        Dim I_PN As Object = New Object()
        Me.WIP_COSTSTableAdapter.Fill(Me.WOCostDataSet.WIP_COSTS, @I_PN)
        FBConn.Close()
    Catch ex As System.Exception
        System.Windows.Forms.MessageBox.Show(ex.Message)
    End Try

When I execute the VB.App and try to run the program, I get the following Error:

Dynamic SQL Error
SQL Error Code = -206
Column Unknown
I_PN
At Line 1, column 29

And I can't quite put my finger on what the actual problem is. Meaning, I don't know if my logic is incorrect on the VB side, or, on the Stored Procedure.

Any coding that is included is kludged together from examples I have found with various bits of code found during long sojourns of GoogleFu.

As anyone with more than a month or two of experience (unlike me) with VB can attest with merely a glance - my code is probably pretty crappy and not well formed - certainly not elegant and most assuredly in operational. I am certainly entertaining all flavors of advice with open arms.

As usual, if you have further questions, I will answer them to the best of my ability.

Thanks again.

Jasoomian

From stackoverflow

• Can you show entire stored procedure here? What version of Firebird do you use?

• Try changing this:

  FBConn.CreateCommand.Parameters. & _
          Add("@I_PN", FirebirdSql.Data.FirebirdClient.FbDbType.Text). & _
          Value = TextBox1.Text
  

  ... to this:

  FBCmd.Parameters.AddWithValue("@I_PN", TextBox1.Text)
  

  Basically, you want to add stored procedure parameters to the Command object, not the Connection object.

  Jasoomian : HardCode - still no joy. Still receiving the same error at run time.

  HardCode : I don't know the syntax of Firebird's SPs, but this statement - where pn like :i_pn || '%' - looks like it is saying "LIKE the parameter OR %" - instead of - "LIKE the parameter concatenated with %". Should it maybe be "where pn like :i_pn + '%'"

  Jasoomian : In Firebird, the || is the concatenation character, so, the code is already doing what you suggested (at least in that manner.)

• Andreik,

  Here is the entire stored Procedure. And our Firebird is Version 1.5.3, written with IbExpert version 2006.12.13, Dialect 3

  Begin
  For
  select pn, pnm.description, si_number, entry_date, cmp_auto_key, parts_flat_price,
         labor_flat_price, misc_flat_price, woo_auto_key, wwt_auto_key
    from parts_master pnm, wo_operation woo
   where pn like :i_pn || '%'
     and pnm.pnm_auto_key = woo.pnm_auto_key
    into :pn, :description, :work_order, :entry_date, :cmp, :parts_price,
         :labor_price, :misc_price, :woo, :wwt
  
  Do begin
     labor_hours = null;
     work_type = null;
     parts_cost = null;
     labor_cost = null;
     ro_cost = null;
     customer = null;
  
     select company_name
       from companies
      where cmp_auto_key = :cmp
       into :customer;
  
     select work_type
       from wo_work_type
      where wwt_auto_key = :wwt
       into :work_type;
  
     select sum(sti.qty*stm.unit_cost)
       from stock_ti sti, stock stm, wo_bom wob
      where sti.wob_auto_key = wob.wob_auto_key
        and sti.stm_auto_key = stm.stm_auto_key
        and wob.woo_auto_key = :woo
        and sti.ti_type = 'I'
        and wob.activity <> 'Work Order'
        and wob.activity <> 'Repair'
       into :parts_cost;
  
     select sum(sti.qty*stm.unit_cost)
       from stock_ti sti, stock stm, wo_bom wob
      where sti.wob_auto_key = wob.wob_auto_key
        and sti.stm_auto_key = stm.stm_auto_key
        and wob.woo_auto_key = :woo
        and sti.ti_type = 'I'
        and wob.activity = 'Repair'
       into :ro_cost;
  
     select sum(wtl.hours*(wtl.fixed_overhead+wtl.variable_overhead+wtl.burden_rate)),
            sum(wtl.hours)
       from wo_task_labor wtl, wo_task wot
      where wtl.wot_auto_key = wot.wot_auto_key
        and wot.woo_auto_key = :woo
       into :labor_cost, :labor_hours;
  
     suspend;
     end
  End
  

  Hardcode - I responded in the comments to your suggestion.

• After a little rethinking and a bit more research, I finally got my code working..

          Try
  
          ' Code for checking server location and required credentials
  
          Dim FBConn As FbConnection
          ' Dim FBAdapter As FbDataAdapter
          Dim MyConnectionString As String
  
          MyConnectionString = "datasource=" _
                          & MyServer & ";database=" _
                          & TextBox4.Text & ";user id=SYSDBA;password=" _
                          & MyPassword & ";initial catalog=;Charset=NONE"
  
          FBConn = New FbConnection(MyConnectionString)
          Dim FBCmd As New FbCommand("WIP_COSTS", FBConn)
  
          FBCmd.CommandType = CommandType.StoredProcedure
          FBCmd.Parameters.Add("@I_PN", FbDbType.VarChar, 40)
          FBCmd.Parameters("@I_PN").Value = TextBox1.Text.ToUpper
  
  
          Dim FBadapter As New FbDataAdapter(FBCmd)
          Dim dsResult As New DataSet
          FBadapter.Fill(dsResult)
  
          Me.WIP_COSTSDataGridView.DataSource = dsResult.Tables(0)
  
          Dim RecordCount As Integer
          RecordCount = Me.WIP_COSTSDataGridView.RowCount
          Label4.Text = RecordCount
  
      Catch ex As System.Exception
          System.Windows.Forms.MessageBox.Show _
          ("There was an error in generating the DataStream, " & _
          "please check the system credentials and try again. " &_ 
          "If the problem persists please contact your friendly " &_ 
          "local IT department.")
      End Try
  
      ' // end of line
  

  I had also thought that I would need to make changes to the actual stored procedure, but, this turned out to be incorrect.

  The code may not be pretty, and I need to do more work in my TRY block for better error handling; but, it works.

  Thanks to all who chimed in and helped me get on track.

  J