How to give the value to a where

san22 · March 27th, 2006, 06:56 PM

Hi There,
I have a onclick event on my vb.net form.
I have two text box, OTBOX AND OTEND.
I want the users to enter two order #s one one each of the two text box. I want to use these order #s as input to my querry which has a WHERE clause with has a between criteria.
I have hard coded two WO# FOR THE BETWEEN CONDITION TO CHECK. IT was fine, but I want to be able to use the input from the text box.

System.Data.OleDb.OleDbDataAdapter("Select [WO #], [Dash #], Req AS [Order DT], Approved, Code, [Item#], Cus,ID from Approve where [WO #] between 136300 and 136400 ORDER by [WO #] ", myConnection)

I WANT TO BE ABLE TO USE THE USERS INPUT ON OTBOX.TEXT AND OTEND.TEXT
IN PLACE OF 136300 AND 136400.
I would appreciate if anybody can tell me how I can do that.
Please find below the entire onclick event.

Please find my code below

Private Sub ORDBTN_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ORDBTN.Click
        'lboResults.Items.Clear()

        Try

        ' create a connection string
        Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\ USE.mdb"
        Dim myConnection As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection
        myConnection.ConnectionString = connString
            ' create a data adapter

            Dim da As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter("Select [WO #], [Dash #], Req AS [Order DT], Approved, Code, [Item#], Cus,ID from Approve where [WO #] between 136300 and 136400 ORDER by [WO #] ", myConnection)

I tried this doesn't work

            ' da.SelectCommand.Parameters("[WO #]").Value() = OTBOX.Text ' create a new dataset
        Dim ds As DataSet = New DataSet
        ' fill dataset
            da.Fill(ds, "Approve")

            Me.DataGrid1.DataSource = ds.Tables("Approve").DefaultView

        Catch ex As Exception
            MsgBox(ex.Message + vbCrLf + ex.Source & vbCrLf & vbCrLf & ex.StackTrace, MsgBoxStyle.Critical, "Error")
        End Try
    End Sub

BrianWren · May 5th, 2006, 11:55 AM

Code:

Imports System.Data.OleDb

Private Sub ORDBTN_Click(ByVal sender As System.Object, _
                         ByVal e As System.EventArgs) Handles ORDBTN.Click

    Try       
        ' Create a connection string 
        Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\ USE.mdb"
        Dim myConnection As OleDbConnection = _
                        New OleDbConnection
        myConnection.ConnectionString = connString
        ' Create a data adapter 

        Dim da As OleDbDataAdapter = _
              New OleDbDataAdapter("SELECT  [WO #], [Dash #], Req AS [Order DT], " & _
                                   "        Approved, Code, [Item#], Cus,ID " & _
                                   "FROM Approve " & _
                                   "WHERE [WO #] BETWEEN " & OTBOX.TEXT & " AND " & OTEND.TEXT  " " & _
                                   "ORDER BY [WO #] ", _
                                   myConnection)
        ' I am presuming that [WO #] is not text.  If it is, use:
        ' . . .BETWEEN '" & OTBOX.TEXT & "' AND "' & OTEND.TEXT  "' . . . "
        '              ^                  ^      ^                ^

        Dim ds As DataSet = New DataSet

        ' Fill dataset 
        da.Fill(ds, "Approve")

        Me.DataGrid1.DataSource = ds.Tables("Approve").DefaultView

    Catch ex As Exception
        ' When concatenating strings, use â&,â not â+.â  If any of the pieces
        ' are Null, the whole concatenation will be Null if â+â is used, but
        ' the Null will be treated as an empty string by â&.â
        MsgBox(ex.Message + vbCrLf + ex.Source & vbCrLf & vbCrLf & ex.StackTrace, _
               MsgBoxStyle.Critical, _
               "Error")
    End Try

End Sub