[2005] Combobox SelectedIndex and SelectedItem database SQL help

[mzbarz]

Hi... i need your help guys...
Database:"gnewdatabase" Global Variable
table name: Ac1Qn
the table is not pre-created... its created upon coding.....


I have successfully able to add new data corresponding to the combobox selected index and able to view the first combobox-with textfield data.. and save em... but when i wan to SELECT the other data using the last 5 data in the combobox... i cant view em...here's a code to show ya:

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        Dim sqlConn As SqlConnection
        sqlConn = New SqlConnection("Data Source=localhost;Initial Catalog=" & gnewdatabase & ";Integrated Security=true")
        sqlConn.Open()
        Dim cmd1 As SqlCommand = sqlConn.CreateCommand()

        ''check ID
        'cmd1.CommandText = "SELECT id from Ac1Qn"
        'checkId = cmd1.ExecuteScalar()
        'If checkId = Nothing Then
        '    Svbt.Show()
        '    Upbt.Hide()
        'Else
        '    Upbt.Show()
        '    Svbt.Hide()
        'End If

        cmd1.CommandText = "SELECT Col1 from Ac1Qn"
        Dim b As String = cmd1.ExecuteScalar
        If b = ComboBox1.SelectedItem Then
            Upbt.Show()
            Svbt.Show()
            'retrieving Question
            Dim cmd11 As SqlCommand = sqlConn.CreateCommand()
            cmd11.CommandText = "SELECT Col2 from Ac1Qn where id = 1 AND col1 = '" & ComboBox1.SelectedItem & "'"
            Dim a As String = cmd11.ExecuteScalar
            Name1.Text = a
            Dim cmd111 As SqlCommand = sqlConn.CreateCommand()
            cmd111.CommandText = "SELECT Col2 from Ac1Qn where id = 2 AND col1 = '" & ComboBox1.SelectedItem & "'"
            Dim a1 As String = cmd111.ExecuteScalar
            Name2.Text = a1
            Dim cmd10 As SqlCommand = sqlConn.CreateCommand()
            cmd10.CommandText = "SELECT Col2 from Ac1Qn where id = 3 AND col1 = '" & ComboBox1.SelectedItem & "'"
            Dim z As String = cmd10.ExecuteScalar
            Name3.Text = z
            Dim cmd2 As SqlCommand = sqlConn.CreateCommand()
            cmd2.CommandText = "SELECT Col2 from Ac1Qn where id = 4 AND col1 = '" & ComboBox1.SelectedItem & "'"
            Dim c As String = cmd2.ExecuteScalar
            Name4.Text = c
            Dim cmd3 As SqlCommand = sqlConn.CreateCommand()
            cmd3.CommandText = "SELECT Col2 from Ac1Qn where id = 5 AND col1 = '" & ComboBox1.SelectedItem & "'"
            Dim d As String = cmd3.ExecuteScalar
            Name5.Text = d
            Dim cmd4 As SqlCommand = sqlConn.CreateCommand()
            cmd4.CommandText = "SELECT Col2 from Ac1Qn where id = 6 AND col1 = '" & ComboBox1.SelectedItem & "'"
            Dim x As String = cmd4.ExecuteScalar
            Name6.Text = x
        Else
            Svbt.Show()
            Upbt.Hide()
            Name1.Text = ""
            Name6.Text = ""
            Name2.Text = ""
            Name3.Text = ""
            Name4.Text = ""
            Name5.Text = ""
            l = 1
        End If

        Dim cmd1a As SqlCommand = sqlConn.CreateCommand()
        cmd1a.CommandText = "SELECT Col1 from Ac1Qn"
        Dim ba As String = cmd1.ExecuteScalar

        If ba = ComboBox1.SelectedItem Then

            Dim cmd11a As SqlCommand = sqlConn.CreateCommand()
            cmd11a.CommandText = "SELECT Col3 from Ac1Qn where id = 1 AND col1 = '" & ComboBox1.SelectedItem & "'"
            Dim bg As String = cmd11a.ExecuteScalar
            Range1.Text = bg
            Dim cmd111a As SqlCommand = sqlConn.CreateCommand()
            cmd111a.CommandText = "SELECT Col3 from Ac1Qn where id = 2 AND col1 = '" & ComboBox1.SelectedItem & "'"
            Dim a1a As String = cmd111a.ExecuteScalar
            Range2.Text = a1a
            Dim cmd10a As SqlCommand = sqlConn.CreateCommand()
            cmd10a.CommandText = "SELECT Col3 from Ac1Qn where id = 3 AND col1 = '" & ComboBox1.SelectedItem & "'"
            Dim za As String = cmd10a.ExecuteScalar
            Range3.Text = za
            Dim cmd2a As SqlCommand = sqlConn.CreateCommand()
            cmd2a.CommandText = "SELECT Col3 from Ac1Qn where id = 4 AND col1 = '" & ComboBox1.SelectedItem & "'"
            Dim ca As String = cmd2a.ExecuteScalar
            Range4.Text = ca
            Dim cmd3a As SqlCommand = sqlConn.CreateCommand()
            cmd3a.CommandText = "SELECT Col3 from Ac1Qn where id = 5 AND col1 = '" & ComboBox1.SelectedItem & "'"
            Dim da As String = cmd3a.ExecuteScalar
            Range5.Text = da
            Dim cmd4a As SqlCommand = sqlConn.CreateCommand()
            cmd4a.CommandText = "SELECT Col3 from Ac1Qn where id = 6 AND col1 = '" & ComboBox1.SelectedItem & "'"
            Dim xa As String = cmd4a.ExecuteScalar
            Range6.Text = xa

        Else
            Svbt.Show()
            Upbt.Show()
            Range1.Text = ""
            Range2.Text = ""
            Range3.Text = ""
            Range4.Text = ""
            Range5.Text = ""
            Range6.Text = ""
            l = 1
        End If
        Svbt.Hide()
        Upbt.Show()
        sqlConn.Close()
    End Sub

My combobox has 6 Categories with different data in the textfield's(12) correspond to the combobox...: here the pic:



i'll be able to view data for cats.. but cant select the other 5 categories... is there something wrong with my coding??

[mzbarz]

Upzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz

[mzbarz]

In order for me to select the DATA's in the Table... i have to make a new connection, how am i suppose to select the data without making a connection to the database.... read.. its not make.. its select the data in table

[mzbarz]

then how am i suppose to make the combobox and the select database to work together?? cause i have like 6 categories with different data to enter and select corresponding to the combobox selected...

[mzbarz]

one more thing... u know anything about adding Count(id).. wanna make it count 1,2,3,4,5,6,7... cause my database now its 1-6 then repeats 1-6.... and remember.. i've done this programming a long time..... there's problems here and there and i need help correcting it......

[mzbarz]

well.. its something like this:
For Id increment when adding new data.. without duplicating...:

Private Sub count()
        Dim sqlConn As SqlConnection
        sqlConn = New SqlConnection("Data Source=localhost;Initial Catalog=Common;Integrated Security=true")
        sqlConn.Open()
        Dim cmd5 As SqlCommand = sqlConn.CreateCommand
        cmd5.CommandText = "Select count(id) from Common"
        f = cmd5.ExecuteScalar
        sqlConn.Close()
    End Sub