CODEWITHSUNDEEP
vb.net
user4687376
user4687376

Reputation:

VB.net Query wont retrieve data from access database

I have created a query using vb.net with parameters which should allow the query to retrieve data from my access database but however when I click on the button it only shows blank fields but no rows are retrieved from the database. Could you please help me what I am currently doing wrong.

Imports System.Data.OleDb

Public Class RouteToCruise

Private Sub RouteToCruise_Load(sender As Object, e As EventArgs) Handles MyBase.Load

End Sub

Private Sub Route_Btn_Click(sender As Object, e As EventArgs) Handles Route_Btn.Click
    Try


    Dim row As String
        Dim connectString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\DeepBlueTables.mdb"
    Dim cn As OleDbConnection = New OleDbConnection(connectString)
    cn.Open()
        Dim CruiseQuery As String = "SELECT Route.RouteName + ', ' + Cruise.CruiseID As CruiseRoute FROM Route INNER JOIN Cruise ON Route.RouteID = Cruise.RouteID  WHERE CruiseID = ?"


        Dim cmd As New OleDbCommand(CruiseQuery, cn)
        'cmd.Parameters.AddWithValue("CruiseID", OleDbType.Numeric).Value = Route_Txt.Text
        cmd.Parameters.AddWithValue(("CruiseID"), OleDbType.Numeric)

        Dim reader As OleDbDataReader = cmd.ExecuteReader


        'RCTable.Width = Unit.Percentage(90.0)                       
        RCTable.ColumnCount = 2
        RCTable.Rows.Add()
        RCTable.Columns(0).Name = "CruiseID"
        RCTable.Columns(1).Name = "RouteName"


        While reader.Read

            Dim rID As String = reader("RouteID").ToString()
            cmd.Parameters.AddWithValue("?", rID)


            row = reader("CruiseID") & "," & ("RouteName")

            RCTable.Rows.Add(row)

        End While

        reader.Close()
        cn.Close()

    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub

End Class

If the user enters route name in the text box then the rows should show cruise ID and route name for each of the selected routes. for example if users enters Asia in the text box, clicks on the button then the query should return the cruiseID for the cruises which are going to Asia.

Upvotes: 0

Views: 103

Answers (1)

jmcilhinney
jmcilhinney

Reputation: 54417

Your use of parameters makes no sense. First you call AddWithValue and provide no value, then you execute the query and then you start adding more parameters as you read the data. Either you call AddWithValue and provide a value, or you call Add and then set the Value on the parameter object created. Either way, it MUST be before you execute the query or it's useless.

myCommand.Parameters.AddWithValue("@ParameterName", parameterValue)

or

Dim myParameter = myCommand.Parameters.Add("@ParameterName", OleDbType.Numeric)

myParameter.Value = parameterValue

Upvotes: 1

Related Questions