Value of type 'String' cannot be converted to 'System.Data.OleDb.OleDbComma

[CodingSource]

Hello guys,

I am trying to add date range with the combobox value when a user clicks the "Enter" button. This is my code so far:

Dim con As New OleDbConnection(Get_Constring)
        Dim dt As New DataTable
        con.Open()
        Dim da As New OleDbDataAdapter("SELECT * from EncodingComplete where Date_Bill BETWEEN @startDate AND @endDate AND Client like @client Order by Date_Bill desc" , con)
        With da.SelectCommand.Parameters
            With da.SelectCommand.Parameters
                .AddWithValue("@startDate", DateTimePicker1.Value)
                .AddWithValue("@endDate", DateTimePicker2.Value)
                .AddWithValue("@client", ComboBox1.Text & "%")
            End With
        End With
        da.Fill(dt)
        DataGridView1.DataSource = dt
        con.Close()

The error is in my SQL statement, which says:

Error 1 Value of type 'String' cannot be converted to 'System.Data.OleDb.OleDbCommand'. C:\Users\david\Documents\Visual Studio 2008\Projects\Golden Dragon Billing System\Golden Dragon Billing System\AfterfrmMain\frmSOA.vb 100 40 Golden Dragon Billing System

Please please help me!!

[djjeavons]

Hi

Your syntax is a little wrong as you are not closing the string for the SQL statement. It should be:

    Dim da As New OleDbDataAdapter("SELECT * from EncodingComplete where Date_Bill BETWEEN @startDate AND @endDate AND Client like @client Order by Date_Bill desc", con)

HTH

[CodingSource]

OK so I corrected my code but the problem is the datagridview goes blank. Please help me.

[djjeavons]

Do you have any data returned?

If you place a breakpoint on your da.Fill(dt) line, step over and then inspect the dt variable you will be able to determine if it contains any data. Another method would be to output the value of your SQL statement to the immediate window and run this against your database to see if the statement is correct.

[Reverend Jim]

Parameterrs in OleDB are specified with ? rather than &parm. See here for a working example.

[Santanu.Das]

The statement was quite right. Only between clause may be coverded by parenthesis. The statement would be

Dim da As New OleDbDataAdapter("SELECT * from EncodingComplete where (Date_Bill BETWEEN @startDate AND @endDate) AND Client like @client Order by Date_Bill desc" , con)

From my opinion, the exception arises form prarmeter declaration codes. I am surprising why ** With da.SelectCommand.Parameters** is used two times at the line no 5 & 6. Codeblocks are quite right. Only line no 6 and 10 must be removed.

[Reverend Jim]

For OLEDb the synax would be

Dim da As New OleDbDataAdapter("SELECT * from EncodingComplete where (Date_Bill BETWEEN ? AND ?) AND Client like ? Order by Date_Bill desc" , con)

Please note that the parameter values must be added in the order in which they appear in the query.

[djjeavons]

Hi

Still new here so not sure how to quote a reply but while I agree that parameters in OleDb should be ?, they can also be named parameters. The only caveat is (as you have already mentioned) that the parameters be applied in the order that they appear in the query.

Personally, I prefer the named parameters as they make the code much easier to read. Also makes moving to other database providers that support named parameters natively a lot easier.

[Reverend Jim]

Just put a ">" at the start of a line and it will quote the text. You can also select the text and click on the quote tool.