how tp pass parameter in vb.net for oracle database statement

Question

0.00/5 (No votes)

See more:

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.

VB

    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

Posted 9-Jun-13 22:40pm

gogole_yuna

Updated 9-Jun-13 22:41pm

Zoltán Zörgő

v2

Add a Solution

Comments

gogole_yuna 10-Jun-13 4:58am

i insert this but still i cant get result :

sqlCommand.Parameters.Add(New OracleParameter(":ListBox1", OracleType.VarChar)).Value = ListBox1
sqlCommand.Parameters.Add(New OracleParameter("strMaterial", OracleType.VarChar)).Value = strMaterial

2 solutions

Solution 2

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:

VB

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

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.

Posted 9-Jun-13 22:53pm

Zoltán Zörgő

Comments

gogole_yuna 10-Jun-13 5:13am

if i query this out through oracle, its come out >>

select * from abc where tran_dttm <= to_date('7/4/2012','MM/DD/YYYY') and tran_dttm > to_date('7/3/2012','MM/DD/YYYY')

so i want to replace >> '7/4/2012' and '7/3/2012' with the user input which i assign as bdp1

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Abhinav S · Accepted Answer · 2013-06-09T22:42:00

Solution 1

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

Posted 9-Jun-13 22:42pm

Abhinav S