oledbcommand sql query strange behaviour

Question

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 24-Oct-12 0:29am

Member 3892343

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Member 3892343 · Answer 1 · 2012-10-24T20:05:00

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