tp如何在vb.net中为oracle数据库语句传递参数 [英] how tp pass parameter in vb.net for oracle database statement

问题描述

您好我正在使用vb.net和oracle。从我的界面我使用基本日期选择器来选择日期，我将基本日期选择器指定为bdp1

我将如何传递参数，以便我的oracle db可以读取我的日期和显示出来。

Hello i am using vb.net and oracle. from my interface I'm using basic date picker for select the date and i assign that basic date picker as bdp1
how I'm going to pass the parameter so that my oracle db can read my date and display out.

    Private Function GetDate(ByVal strMaterial As String, ByVal ListBox1 As String, ByVal bdp1 As Date) As DataSet

    Dim connectionString As String = "Data Source = ***; User ID = ***; Password = **;"
    Dim sqlConnection As OracleClient.OracleConnection = New OracleClient.OracleConnection(connectionString)


    Dim queryString As String = "select * from abc where (tran_dttm <= to_date( '" & bdp1 & "' ,'MM/DD/YYYY') and tran_dttm > to_date( '" & bdp1 & "' ,'MM/DD/YYYY')and lpt = '" & ListBox1 & "' and device = '" & strMaterial & "')"

    Dim sqlCommand As OracleClient.OracleCommand = New OracleClient.OracleCommand(queryString, sqlConnection)
    sqlCommand.CommandTimeout = 0


    sqlCommand.Parameters.Add(New OracleParameter("bdp1", OracleType.DateTime)).Value = bdp1


    Dim dataAdapter As OracleClient.OracleDataAdapter = New OracleClient.OracleDataAdapter(sqlCommand)
    Dim dataSet As DataSet = New DataSet
    dataAdapter.Fill(dataSet)

    Return dataSet

End Function

推荐答案

您缺少第二个参数设备。只需像bdp1参数一样添加它。

You are missing the second parameter device your procedure. Just add it like the bdp1 parameter.

1）不要使用字符串连接来构建语句。

2）如我所见，你试过使用参数，但你以某种方式混合它。

这是一个很好的教程： Gotcha＃1161：在Oracle ODP.NET中使用命名参数 [ ^ ]



根据它，你的查询字符串应该是这样的：

1) Don't use string concatenation to build the statement.
2) As I see, you have tried to make use of parameters, but you mixed it somehow.
This is a good tutorial: Gotcha #1161: Using Named Parameters with Oracle ODP.NET[^]

According to it, your query string should look like this:

Dim queryString As String = "select * from abc where tran_dttm <= :bdp1 and tran_dttm > :bdp1 and lpt = :lpt and device = :device"

您应该像添加 bdp1 一样添加其他两个参数。



但是我发现这不会返回任何内容，因为（tran_dttm< = bdp1和tran_dttm> bdp1）永远不会成为现实。

And you should add the other two parameters as you did with bdp1.

But as I see this won't return anything, because (tran_dttm<=bdp1 and tran_dttm>bdp1) will never be true.

这篇关于tp如何在vb.net中为oracle数据库语句传递参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！