将VB参数注入存储过程时出现问题(FireBird) [英] 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.

为澄清起见，存储过程在FireBird下运行，而VB是.NET的

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

我有一个存储过程(以下摘录，重要的一点是WHERE)

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

我正在尝试从vb应用程序传递一个使用参数I_PN的参数，其代码如下(MyServer和MyPassword的变量由代码的较早部分确定).

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

当我执行VB.App并尝试运行该程序时，出现以下错误:

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

动态SQL错误
SQL错误代码= -206
列未知
I_PN
在第1行的第29列

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

我不能完全确定实际问题是什么.意思是，我不知道我的逻辑在VB端还是在存储过程上是不正确的.

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.

我发现的示例中包含的所有编码都与在GoogleFu长期逗留期间发现的各种代码一起被弄糊涂了.

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

任何一个拥有一个或两个月以上VB经验(与我不同)的人都可以一目了然地证明-我的代码可能很烂，而且格式不正确-当然不优雅，而且在操作上最有把握.我当然会张开双臂接受各种建议.

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.

再次感谢.

茉莉花

推荐答案

经过一番重新思考和更多研究，我终于使我的代码生效了..

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.

代码可能不是很漂亮，为了更好地处理错误，我需要在TRY块中做更多的工作；但是，它有效.

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.

这篇关于将VB参数注入存储过程时出现问题(FireBird)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！