more newbie combobox blues... 1

[embryo]

Hi All-

Let me first start by saying that this is my first foray into the world of VB.NET application programming, though I have over 10 years experience with VBScript/ASP/SQL Server programming.

I've read what seems like a million posts on how to use a combobox's selected value as a parameter for an underlying database query, and appear to be very close to success, but still no cigar....

I am using the VB.NET 2005 Express Edition and connecting to a remote SQL Server 2000 database, so using the built-in database connection wizard and dataset designer, etc.. are not an option.

I have a form with a combobox which selects clientID, clientname, city, and state. The selected item's ValueMember should be the clientID field so I can use it in my database query on the subsequent sub routine that gets fired on button click on my form.

Through playing with the selecteditem, valuemember and selectedindex properties, I've caused a number of different results, none of which that I was hoping for.

Currently, the closest I have come to perfect is getting my combobox to pass the last(final) clientID in the combobox's recordset...regardless of which item is selected.

Any help would be greatly appreciated. Below is my code for the combobox:

Public Class QueryBuilder

    Private Sub QueryBuilder_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


        Dim cbo As New ComboBox
        cbo = Me.clientid
        Dim strSQL As String = "select clientid, clientname, city, state from tblclients where clientid=261 or parent=261 order by state,city"
        Dim conn As New OleDb.OleDbConnection("Provider=sqloledb;Data Source=myconnectionstringremovedforsecuritypurposes")
        Dim cmd As New OleDb.OleDbCommand(strSQL, conn)
        conn.Open()
        Dim reader As OleDb.OleDbDataReader = cmd.ExecuteReader()
        If reader.HasRows Then
            cbo.SelectedItem = ""
            While reader.Read()
                cbo.Items.Add(reader(2).ToString & ", " & reader(3).ToString & " --- " & reader(1).ToString)
                cbo.ValueMember = reader(0)
                'I didn't have any  different resaults using this WITH block..
                'With cbo
                '.ValueMember = reader(0)
                '.DisplayMember = reader(2).ToString & ", " & reader(3).ToString & " --- " & reader(1).ToString
                'End With
            End While

            cbo.SelectedText = ""
        Else
            MsgBox("Please report Error Code#  000x-CID", MsgBoxStyle.Critical)
        End If
        reader.Close()

    End Sub

This is my SelectedIndexChanged event code:

    Private Sub clientid_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles clientid.SelectedIndexChanged
        Dim clientid As Integer
        clientid = Me.clientid.ValueMember
        MsgBox(clientid.ToString)
    End Sub

And finally, this is the buttonclick code I'm writing to fetch the appropriate data from the database(not quite finished yet) Just ignore everything except the If/Then that handles a specific clientID being passed to it:

    Private Sub Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button.Click

        Dim strWhere As String
        Dim strAnd As String
        Dim strSelect As String
        Dim strOrderBy As String
        Dim strFilterA As Date
        Dim strFilterB As Date

        Dim clientid As Integer
        clientid = Me.clientid.ValueMember

        Dim DateRange As CheckBox = Me.DateRange
        Dim theDateRange As Boolean

        Dim BeginDate As New DateTimePicker
        Dim theBeginDate As Date
        theBeginDate = BeginDate.Value
        BeginDate.Value = theBeginDate

        Dim EndDate As New DateTimePicker
        Dim theEndDate As Date
        theEndDate = EndDate.Value
        EndDate.Value = theEndDate

        Dim AllBranches As CheckBox = Me.AllBranches
        Dim theAllBranches As Boolean

        Dim CreditorAcctNumbers As New TextBox
        Dim theCreditorAcctNumbers As String
        theCreditorAcctNumbers = "'"
        theCreditorAcctNumbers = theCreditorAcctNumbers & CreditorAcctNumbers.Text
        theCreditorAcctNumbers = Replace(CreditorAcctNumbers.Text, ",", "','")
        theCreditorAcctNumbers = theCreditorAcctNumbers & "'"
        strSelect = "Select [ClientID],[Branch],[CreditorAcctNumber],[AccountName],[Status],[AmountPaid],[DatePaid],[OriginalBalance],[CurrentBalance],[PaidToDate] FROM [wrs_dbui]"
        strAnd = ""
        strWhere = ""
        strOrderBy = " Order By Branch,CreditorAcctNumber"

        If theAllBranches = True Then
            Dim theMasterID As Integer
            theMasterID = 261
            strWhere = strWhere & " ClientID=" & theMasterID & " or parent=" & theMasterID
            strAnd = " And"
        Else
            strWhere = strWhere & " ClientID =" & clientid
            strAnd = " And"
        End If


        If Me.CreditorAcctNumber.Text <> "" Then
            strWhere = strWhere & strAnd & " CreditorAcctNumber IN (" & theCreditorAcctNumbers & ")"
            strAnd = " And"
        End If


        If theDateRange = True Then
            strFilterA = theBeginDate
            strFilterB = theEndDate
            strWhere = strWhere & strAnd & " DatePaid BETWEEN CONVERT(DATETIME, '" & strFilterA & "', 102) AND CONVERT(DATETIME, '" & strFilterB & "', 102)"
        End If


        Dim connectionstring1 As New OleDb.OleDbConnection("Provider=sqloledb;Data Source=myconnectionstringremovedforsecuritypurposes")
        Dim sqlcom As New OleDb.OleDbCommand(strSelect, connectionstring1)
        Dim adapt As New OleDb.OleDbDataAdapter(sqlcom)
        Dim DataGridView1 As New DataGridView
        Dim ds As New DataSet
        Dim dt As New DataTable
	Dim ss As New SaveFileDialog
        ss.Filter = "XML File|*.xml"
        ss.CheckPathExists = True
        ss.Title = "Saving Your Data..."
        ss.FileName = "Data_Import_Results"
        ss.ShowDialog(Me)
        sqlcom.Connection.Open()

        Try
            adapt.Fill(ds)
            DataGridView1.DataSource = dt
            ds.Tables.Add(dt)
            ds.WriteXml(ss.FileName)
        Catch ex As Exception
        End Try

        Exit Sub

Steve
---------------------------------------
IF WebApplicationProgrammer = True Then
ElectronicSheepHerder = True
End If

 

[jebenson]

Well, one issue I see is how you are filling your combobox - you are making it unnecessarily complex. Try doing this instead:

    Private Sub QueryBuilder_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


        Dim cbo As New ComboBox
        [red]Dim da As SqlDataAdapter
        Dim dt As DataTable[/red]
        cbo = Me.clientid
        Dim strSQL As String = "select clientid, [red](city+', '+state+' --- '+clientname) as ClientInfo[/red] from tblclients where clientid=261 or parent=261 order by state,city"
        Dim conn As New OleDb.OleDbConnection("Provider=sqloledb;Data Source=myconnectionstringremovedforsecuritypurposes")

        [red]Try[/red]
            conn.Open()
            [red]
            da = New SqlDataAdapter(strSQL, conn)
            dt = New DataTable
            da.Fill(dt)
            cbo.DataSource = dt
            cbo.DisplayMember = "ClientInfo"
            cbo.ValueMember = "clientid"
            cbo.SelectedIndex = -1

        Catch[/red]
            MsgBox("Please report Error Code#  000x-CID", MsgBoxStyle.Critical)
        [red]End Try[/red]
        
         
    End Sub

Now in the combobox's SelectedIndexChanged event handler, to reference the selected record's clientid, do this:

SomeVariable = clientid.SelectedValue

Hope this helps. Post back if you need any further assistance.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[embryo]

Hi Jebenson-

When I copy in your code, I get a warning that "Type SqlDataAdapter is not defined"

Thanks,
Steve

Steve
---------------------------------------
IF WebApplicationProgrammer = True Then
ElectronicSheepHerder = True
End If

 

[jebenson]

Oh, sorry about that. Either dim it as System.Data.SqlClient.SqlDataAdapter, or at the very top of your class file, before everything else, put: Imports System.Data.SqlClient and leave the other code as it is.



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[embryo]

Hi again-

Still no cigar..

When I did that, it complained about the "Provider" keyword in my connectionstring, so instead of oledb, I switched it from this:

Dim conn As New OleDb.OleDbConnection("Provider=sqloledb;Data Source=myconnectionstringremovedforsecuritypurposes")
---------------------------------------------------------
...to this:

Dim conn As New SqlConnection("Data Source=myconnectionstringremovedforsecuritypurposes;")

---------------------------------------------------------

...but now it errors here:

Private Sub clientid_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles clientid.SelectedIndexChanged
        Dim clientid As Integer
        clientid = Me.clientid.SelectedValue
        MsgBox(clientid.ToString)
End Sub

..saying "Conversion from type 'DataRowView' to type 'Integer' is not valid."

I'd like to have the clientid variable continue to be an integer if possible so that I can more easily use it in my subsequent query filter criteria as such, so not sure casting it just to get past this hurdle is the wisest choice.

Any recommendations?

Thanks again!
Steve

Steve
---------------------------------------
IF WebApplicationProgrammer = True Then
ElectronicSheepHerder = True
End If

 

[jebenson]

Try this:

Private Sub clientid_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles clientid.SelectedIndexChanged
        Dim clientid As Integer
        [red]If Me.clientid.SelectedIndex > -1 Then[/red]
            clientid = Me.clientid.SelectedValue[red](0)[/red]
            MsgBox(clientid.ToString)
        [red]EndIf[/red]

End Sub

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[embryo]

doh!...I reallized earlier today that I didn't really even need that selectedindexchanged event except for the debugging to see what ID was being passed...I put a messagebox on the button's event to see it...works a charm.

Thanks a bunch!

Steve

Steve
---------------------------------------
IF WebApplicationProgrammer = True Then
ElectronicSheepHerder = True
End If