Click here to Skip to main content
14,877,713 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more: , +
Hello,
I have a strange behaviour of an sql search query, I have added parameters to an oledbcommand object in an sql query, I use a combobox to select the appropriate parameter, the strange thing is at design time when I preview the data and give value to one of the parameters it displays the data correctly, but at runtime when I use the combobox itemindex it works only for 3 of the parameters other 5 it dosn't give any results but at design time it gives results. Any help?

Thank you in advance
Posted
Comments
Sandeep Mewara 24-Oct-12 11:53am
   
That's not the normal behavior. Without the related code snippet, it woul be difficult to suggest anything.
Member 3892343 25-Oct-12 2:06am
   
Here is the code but I wonder if it has to do that vb 2008 is without service pack 1, maybe I must update?

sql command in oledbcommand (note that in design time "preview data" the command works)



SELECT id, car_num, owner, afm1, address, phone, owner2, afm2, address2, phone2, owner3, afm3, address3, phone3, comments, photo, plaisio_num,
speciment_of_vechicle, type_of_vechicle, ead, product_facilities, onoma_patros, doy
FROM dbo.Table_1
WHERE (car_num LIKE '%' + ? + '%') OR
(owner LIKE ? + '%') OR
(afm1 LIKE ? + '%') OR
(address LIKE ? + '%') OR
(phone LIKE ? + '%') OR
(owner2 LIKE ? + '%') OR
(owner3 LIKE ? + '%') OR
(plaisio_num LIKE ? + '%') OR
(ead LIKE ? + '%')
and here is the serach button code (combo index of 0,5,7,8 works and gives the results)


Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Select Case ComboBox1.SelectedIndex
Case 0
OleDbConnection1.Open()
OleDbDataAdapter1.SelectCommand.Parameters(0).Value = TextBox1.Text
OleDbDataAdapter1.Fill(DataSet51, "Table_1")
OleDbConnection1.Close()
Case 1
OleDbConnection1.Open()
OleDbDataAdapter1.SelectCommand.Parameters(1).Value=TextBox1.Text OleDbDataAdapter1.Fill(DataSet51, "Table_1")
OleDbConnection1.Close()
Case 2
OleDbConnection1.Open()
OleDbDataAdapter1.SelectCommand.Parameters(2).Value = TextBox1.Text
OleDbDataAdapter1.Fill(DataSet51, "Table_1")
OleDbConnection1.Close()
Case 3
OleDbConnection1.Open()
OleDbDataAdapter1.SelectCommand.Parameters(3).Value = TextBox1.Text
OleDbDataAdapter1.Fill(DataSet51, "Table_1")
OleDbConnection1.Close()
Case 4
OleDbConnection1.Open()
OleDbDataAdapter1.SelectCommand.Parameters(4).Value = TextBox1.Text
OleDbDataAdapter1.Fill(DataSet51, "Table_1")
OleDbConnection1.Close()
Case 5
OleDbConnection1.Open()
OleDbDataAdapter1.SelectCommand.Parameters(5).Value = TextBox1.Text
OleDbDataAdapter1.Fill(DataSet51, "Table_1")
OleDbConnection1.Close()
Case 6
OleDbConnection1.Open()
OleDbDataAdapter1.SelectCommand.Parameters(6).Value = TextBox1.Text
OleDbDataAdapter1.Fill(DataSet51, "Table_1")
OleDbConnection1.Close()
Case 7
OleDbConnection1.Open()
OleDbDataAdapter1.SelectCommand.Parameters(7).Value = TextBox1.Text
OleDbDataAdapter1.Fill(DataSet51, "Table_1")
OleDbConnection1.Close()
Case 8
OleDbConnection1.Open()
OleDbDataAdapter1.SelectCommand.Parameters(8).Value = TextBox1.Text
OleDbDataAdapter1.Fill(DataSet51, "Table_1")
OleDbConnection1.Close()
End Select
End Sub

1 solution

Here is the code but I wonder if it has to do that vb 2008 is without service pack 1, maybe I must update?

sql command in oledbcommand (note that in design time "preview data" the command works)


SQL
SELECT     id, car_num, owner, afm1, address, phone, owner2, afm2, address2, phone2, owner3, afm3, address3, phone3, comments, photo, plaisio_num, 
                      speciment_of_vechicle, type_of_vechicle, ead, product_facilities, onoma_patros, doy
FROM         dbo.Table_1
WHERE     (car_num LIKE '%' + ? + '%') OR
                      (owner LIKE ? + '%') OR
                      (afm1 LIKE ? + '%') OR
                      (address LIKE ? + '%') OR
                      (phone LIKE ? + '%') OR
                      (owner2 LIKE ? + '%') OR
                      (owner3 LIKE ? + '%') OR
                      (plaisio_num LIKE ? + '%') OR
                      (ead LIKE ? + '%')


and here is the serach button code (combo index of 0,5,7,8 works and gives the results)

VB
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Select Case ComboBox1.SelectedIndex
            Case 0
                OleDbConnection1.Open()
                OleDbDataAdapter1.SelectCommand.Parameters(0).Value = TextBox1.Text
                OleDbDataAdapter1.Fill(DataSet51, "Table_1")
                OleDbConnection1.Close()
            Case 1
                OleDbConnection1.Open()
               OleDbDataAdapter1.SelectCommand.Parameters(1).Value=TextBox1.Text                                OleDbDataAdapter1.Fill(DataSet51, "Table_1")
                OleDbConnection1.Close()
            Case 2
                OleDbConnection1.Open()
                OleDbDataAdapter1.SelectCommand.Parameters(2).Value = TextBox1.Text
                                OleDbDataAdapter1.Fill(DataSet51, "Table_1")
                OleDbConnection1.Close()
            Case 3
                OleDbConnection1.Open()
                OleDbDataAdapter1.SelectCommand.Parameters(3).Value = TextBox1.Text
                               OleDbDataAdapter1.Fill(DataSet51, "Table_1")
                OleDbConnection1.Close()
            Case 4
                OleDbConnection1.Open()
                OleDbDataAdapter1.SelectCommand.Parameters(4).Value = TextBox1.Text
                OleDbDataAdapter1.Fill(DataSet51, "Table_1")
                OleDbConnection1.Close()
            Case 5
                OleDbConnection1.Open()
                OleDbDataAdapter1.SelectCommand.Parameters(5).Value = TextBox1.Text
                OleDbDataAdapter1.Fill(DataSet51, "Table_1")
                OleDbConnection1.Close()
            Case 6
                OleDbConnection1.Open()
                OleDbDataAdapter1.SelectCommand.Parameters(6).Value = TextBox1.Text
                OleDbDataAdapter1.Fill(DataSet51, "Table_1")
                OleDbConnection1.Close()
            Case 7
                OleDbConnection1.Open()
                OleDbDataAdapter1.SelectCommand.Parameters(7).Value = TextBox1.Text
                OleDbDataAdapter1.Fill(DataSet51, "Table_1")
                OleDbConnection1.Close()
            Case 8
                OleDbConnection1.Open()
                OleDbDataAdapter1.SelectCommand.Parameters(8).Value = TextBox1.Text
                OleDbDataAdapter1.Fill(DataSet51, "Table_1")
                OleDbConnection1.Close()
        End Select
    End Sub
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900