ado .net 2005 datagridview and a combo box issue

[anf]

hi

what im trying to accomplish is to have the parent table in the combo box so that when the user selects an item the datagridview will fill with its child table's items. ex, if selecting someones name from the combo box, it would then display the persons info in the datagridview...


Imports System.Data
Imports System.Data.OleDb
Public Class Form1
Public objDataSetVendo As New DataSet
Dim dr As DataRelation

Friend WithEvents OleDbConnection As System.Data.OleDb.OleDbConnection
Friend WithEvents DaInfo As System.Data.OleDb.OleDbDataAdapter
Friend WithEvents DaItems As System.Data.OleDb.OleDbDataAdapter
Friend WithEvents OleDbSelectCommand1 As System.Data.OleDb.OleDbCommand

Friend WithEvents OleDbSelectCommand2 As System.Data.OleDb.OleDbCommand


Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.OleDbConnection = New System.Data.OleDb.OleDbConnection
Me.OleDbConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\Anf\My Documents\Visual Studio 2005\Projects\dbVendo.mdb;"

Me.DaInfo = New System.Data.OleDb.OleDbDataAdapter
Me.DaItems = New System.Data.OleDb.OleDbDataAdapter
Me.OleDbSelectCommand1 = New System.Data.OleDb.OleDbCommand

Me.OleDbSelectCommand2 = New System.Data.OleDb.OleDbCommand
'connection
'command 1
Me.OleDbSelectCommand1.CommandText = "SELECT VendoId, VendoName FROM tblMachines"
Me.OleDbSelectCommand1.Connection = Me.OleDbConnection

Me.DaInfo.SelectCommand = Me.OleDbSelectCommand1

'command 2
Me.OleDbSelectCommand2.CommandText = "SELECT ItemId, VendoId, ItemName FROM tblItems WHERE VendoId = VendoId"
Me.OleDbSelectCommand2.Connection = Me.OleDbConnection
Me.OleDbSelectCommand2.Parameters.Add(New OleDbParameter("VendoId", OleDbType.Numeric, 256, "tblMachines.VendoId"))


Me.DaItems.SelectCommand = Me.OleDbSelectCommand2
Try
Me.DaInfo.Fill(objDataSetVendo, "tblMachines")
Me.DaItems.Fill(objDataSetVendo, "tblItems")
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
dr = New DataRelation("bla", objDataSetVendo.Tables("tblMachines").Columns("VendoId"), objDataSetVendo.Tables("tblItems").Columns("VendoId"))
objDataSetVendo.Relations.Add(dr)

Me.cboInfo.DataSource = Me.objDataSetVendo.Tables("tblMachines")
Me.cboInfo.DisplayMember = "VendoId"





End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Me.Close()
End Sub
Private Sub cboInfo_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboInfo.Click
Try
objDataSetVendo.Clear()
Me.OleDbSelectCommand2.CommandText = "SELECT ItemId, VendoId, ItemName FROM tblItems WHERE VendoId = VendoId"
Me.OleDbSelectCommand2.Connection = Me.OleDbConnection
'Me.DaItems.SelectCommand.Parameters("VendoId").Value = cboInfo.Text
Me.DaInfo.Fill(objDataSetVendo, "tblMachines")
Me.DaItems.Fill(objDataSetVendo, "tblItems")
'Me.cboInfo.Focus()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
dgvItems.DataSource = objDataSetVendo.Tables("tblItems")
End Sub

 

[jmcilhinney]

Use the SelectedIndexChanged event of the ComboBox.

 

[jmcilhinney]

Please post each question once only in the most appropriate forum. Your duplicate thread has been deleted.

 

[anf]

Use the SelectedIndexChanged event of the ComboBox.

my problem is not the event at the moment, the same thing happens with either the click event or the selectedIndexChanged, its what the combo box and the datagrid do during this point that im more interested in... the event fires on either the click or the selectedIndex but i need it to then say what the selected index is use that in an sql query and send back which items have that ID...

 

[jmcilhinney]

You are literally searching for records where the VendoId field contains the value the value VendoId, which is obviously not going to work. You look like you were half on the track with the parameter. You should include a parameter in your SQL code and then set its Value:

[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].OleDbSelectCommand2.CommandText = [/SIZE][SIZE=2][COLOR=#800000]"SELECT ItemId, VendoId, ItemName FROM tblItems WHERE VendoId = @VendoId"


[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].OleDbSelectCommand2.Parameters.Add("[/SIZE][SIZE=2][COLOR=#800000]@VendoId[/COLOR][/SIZE][SIZE=2]", valueToSearchFor)[/SIZE]

There are a few things to note. First this is code for VB.NET 2003. In VB 2005 it will be slightly different. Also, the valueToSearchFor must be of the correct type. If VendoId is an integer field then it must ba an integer, not a string. There are several overloaded ways to add parameters but this is probably the simplest.

 

[anf]

You are literally searching for records where the VendoId field contains the value the value VendoId, which is obviously not going to work. You look like you were half on the track with the parameter. You should include a parameter in your SQL code and then set its Value:

[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].OleDbSelectCommand2.CommandText = [/SIZE][SIZE=2][COLOR=#800000]"SELECT ItemId, VendoId, ItemName FROM tblItems WHERE VendoId = @VendoId"
 
 
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].OleDbSelectCommand2.Parameters.Add("[/SIZE][SIZE=2][COLOR=#800000]@VendoId[/COLOR][/SIZE][SIZE=2]", valueToSearchFor)[/SIZE]

There are a few things to note. First this is code for VB.NET 2003. In VB 2005 it will be slightly different. Also, the valueToSearchFor must be of the correct type. If VendoId is an integer field then it must ba an integer, not a string. There are several overloaded ways to add parameters but this is probably the simplest.

Hi thanx for the advice i will try that, i didnt realise that the paramater syntax had changed too.