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



Question:

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


Solution:1

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.


Solution:2

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.


Solution:3

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.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »