Using combo box as parameter in query.... 2

[blounty]

Hi all,

i ave only been walking through the valley of vb.net for a short time (sorry its been a long night!) and i have come to a brickwall which i have not been able to scale! (metaphors all over the place!) i am trying to use a value of a combobox as a parameter for a query which i turn feeds another combobox .. so hence if someone selects for example [apple] in the first combobox it only shows types of apple in the second combobox.

I am using VS 2005 if it of any use.

Hope you guys can point me in the correct direction, thank you in advance.

Alex

 

[mansii]

Have you tried using the combobox's Text property?

 

[blounty]

hi there,

the issue is passing that value to the sql query...

thanks for your reply.

A

 

[mansii]

That's it. Something like

sqlString = "SELECT * FROM FruitTypes WHERE Fruit='" & _
   ComboBox1.Text & "'"

Cheers.

 

[blounty]

thats great thank you. how would i then apply this to the data source of another combobox?

is there another way of doing this by parametarised queries?

 

[mansii]

If you Use a DataTable as its datasource then just change the SelectCommand and re-fill it.
Or, if you use a DataView then change it's RowFilter.

Check the MSDN in how to use DataTable and DataView.

Good luck.

 

[blounty]

Hi All,

I was hoping to do this by using the parameters.add("@test",etc etc) method. i am completely lost with this and cannot find anything in any books that walks you the whole way through. thanks guys!!

A

 

[Mastakilla]

hi,

you could do something similar to:

'In the selectedindexchanged event handler of the first combo

'initialze your connection and command objects 
'......

'create parameter
  cmd.Parameters.AddWithValue("TheFruitType", firstcombo.text)
'The actual sql query string
  cmd.CommandText = "SELECT * FROM FruitTypes WHERE Fruit= @TheFruitType"

'Clear theOtherCombo's items
 theOtherCombo.Items.Clear()

 Dim reader As IDataReader = cmd.ExecuteReader()
 While reader.read
  theOtherCombo.Items.Add(reader.Item("TheFieldThatHoldsWhatYouWantToDisplay"))
 End While

 

[Keyth]

sqlString = "SELECT * FROM FruitTypes WHERE Fruit = @FruitType"
Dim cmd as new sqlCommand(sqlString, Connection)
cmd.parameters.add("@FruitType", sqldbtype.nvarchar).value = ComboBox1.Text

 

[blounty]

thank you so much guys! how do i then get that to effect (fill) my dataset? and hence my second combobox?

Cheers All,

A

 

[Keyth]

sqlString = "SELECT * FROM FruitTypes WHERE Fruit = @FruitType"
Dim cmd as new sqlCommand(sqlString, Connection)
cmd.parameters.add("@FruitType", sqldbtype.nvarchar).value = ComboBox1.Text

dim ds as new dataset
dim adap as new sqldataadapter(cmd)
adap.fill(ds, tablename)

If ds.tables(0).rows.count > 0 then
    'loop through the rows in the dataset
    Dim dv as new dataview
    dv.table = ds.tables(0)
    
    For each myrow in dv
      MyComboBox2.items.add(mr("FieldName").tostring)
    Next
end if

 

[Mastakilla]

ok, you should have a dataadapter, let's say its name is da:

da.selectcommand = cmd 'the command that is used in the previous example

da.Fill(YourDataSet) 'should do the trick

 

[Keyth]

Ooops, mistake, should have read...

sqlString = "SELECT * FROM FruitTypes WHERE Fruit = @FruitType"
Dim cmd as new sqlCommand(sqlString, Connection)
cmd.parameters.add("@FruitType", sqldbtype.nvarchar).value = ComboBox1.Text

dim ds as new dataset
[b]dim myrow as datarowview[/b]
dim adap as new sqldataadapter(cmd)
adap.fill(ds, tablename)

If ds.tables(0).rows.count > 0 then
    'loop through the rows in the dataset
    Dim dv as new dataview
    dv.table = ds.tables(0)
    
    For each myrow in dv
      MyComboBox2.items.add([b]myrow[/b]("FieldName").tostring)
    Next
end if

 

[blounty]

when i use the code above i get sqlstring not defined. what do i dim this as?

cheers guys you are all great!!!

 

[Mastakilla]

as string

 

[blounty]

Hi guys,

I really appreciate all of your help with this, i know i am probably taking up to much time but hope this will be the last post.
my code is as follows:

 Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        Dim sqlstring As String

        sqlstring = "SELECT * FROM Devices WHERE Brand = @Brand"
        Dim cmd As New SqlCommand(sqlstring, [b]"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Devices.mdb;Persist Security Info=True;Jet OLEDB:Database Password=bag"[/b])
        cmd.Parameters.Add("@Brand", SqlDbType.NVarChar).Value = ComboBox1.Text

        Dim ds As New DataSet
        Dim myrow As DataRowView
        Dim adap As New sqldataadapter(cmd)
        adap.Fill(ds, [b]Devices[/b])

        If ds.Tables(0).Rows.Count > 0 Then
            'loop through the rows in the dataset
            Dim dv As New DataView
            dv.Table = ds.Tables(0)

            For Each myrow In dv
                ComboBox2.Items.Add(myrow("Model").ToString)
            Next
        End If

The bold lines are erroring (underlined). again i am stuck! sorry guys... thanks again.

A

 

[Mastakilla]

Hi,

why don't you use oledb objects since your user an oledb provider for an access database?

'error 1 can be solved with
Dim Connection As New OleDb.OleDbConnection
Connection.ConnectionString = ""
Dim cmd As New OleDb.OleDbCommand(sqlString, [b]Connection[/b]) 'here put the connection object, not the connection string
'to know how to build a connection string, go to [URL unfurl="true"]www.connectionstrings.com[/URL]

'error 2
 adap.Fill(ds, [b]"Devices"[/b]) 'table name should be provided as a string (between double quotes) or as a string variable that contains the table name .... as string

 

[blounty]

Thanks for your reply Mastakilla,

I am now getting an error here:

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        Dim sqlstring As String

        Dim Connection As New OleDb.OleDbConnection
        Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Devices.mdb;Persist Security Info=True;Jet OLEDB:Database Password=crapbag"


       
        sqlstring = "SELECT * FROM Devices WHERE Brand = @Brand"

        Dim cmd As New OleDb.OleDbCommand(sqlstring, Connection)

        cmd.Parameters.Add("@Brand", SqlDbType.NVarChar).Value = ComboBox1.Text

        Dim ds As New DataSet
        Dim myrow As DataRowView
        [u][b]Dim adap As New SqlDataAdapter(cmd)[/b][/u]
        adap.Fill(ds, "Devices")

        If ds.Tables(0).Rows.Count > 0 Then
            'loop through the rows in the dataset
            Dim dv As New DataView
            dv.Table = ds.Tables(0)

            For Each myrow In dv
                ComboBox2.Items.Add(myrow("Model").ToString)
            Next
        End If





    End Sub

with the following error:

Error 2 Value of type 'System.Data.OleDb.OleDbCommand' cannot be converted to 'System.Data.SqlClient.SqlCommand'. C:\Documents and Settings\blounta\My Documents\Visual Studio 2005\Projects\Cap Lite1\Cap Lite1\Form1.vb 31 40 Cap Lite1

i assume i have to dim it as something other than an sqladapter but i cannot figure out what.

thanks guys.

A

 

[Mastakilla]

you should use oledb object with oledb objects, therefore, your adapter should be an oledbDataAdapter:

Dim adap As New Oledb.OledbDataAdapter(cmd)

Hope it did the trick

 

[blounty]

Mastakilla,

You rock so hard!! thanks so much! you deserve many many stars!!!

Thanks again.

A