I am try to print a report using a start date and an end date to run my query. My select statement works perfect at the database but in my vb.net application/code i have a parameter query. Any time i try to generate a report i get the error "Parameter ? _2 has no default value".
Please see my code below - especially the Parameter Query.
What am i failing to see?


Try
                Connect()
                chmcon.Open()
                chmcmd = New OleDbCommand("SELECT * FROM IncomeManager WHERE Date >= StartDateTimePicker.Value AND Date <= EndDateTimePicker.Value", chmcon)
                'chmcmd = New OleDbCommand("SELECT * FROM IncomeManager WHERE Date = StartDateTimePicker.Value ", chmcon)
                'StartDateTimePicker
                'EndDateTimePicker
                Dim startDate As String = StartDateTimePicker.Value
                Dim endDate As String = EndDateTimePicker.Value
                MsgBox(startDate)
                MsgBox(endDate)
                IncRep1OleDbDataAdapter.SelectCommand.Parameters.I tem("Date").Value = startDate
                IncRep1OleDbDataAdapter.SelectCommand.Parameters.I tem("Date").Value = endDate
                IncRep1DataSet28.Clear()
                IncRep1OleDbDataAdapter.Fill(IncRep1DataSet28)
            Catch ex As Exception
                MessageBox.Show(ex.Message)

Hi Asters

You are passing the start and end date to the same parameter

IncRep1OleDbDataAdapter.SelectCommand.Parameters.I tem("Date").Value = startDate
IncRep1OleDbDataAdapter.SelectCommand.Parameters.I tem("Date").Value = endDate

Hope you get it right now

Cheers
Shasur

http://www.dotnetdud.blogspot.com

Your SQL query has code in it that the database engine won't understand.
I'll make the assumption that "Date" in your query is the field you want to test. You are passing what appears to be .NET object code into the SQL query as literal text which will be interpreted as T-SQL syntax.

"StartDateTimePicker.Value" and "EndDateTimePicker.Value" won't be understand by the database engine.

Where are your parameters? I would expect to see something like "@StartDate" (for SQL server) or "?" (for OleDb) in the appropriate locations. Such as:

"SELECT * FROM IncomeManager WHERE Date >= ? AND Date <= ?"

or better still:

"SELECT * FROM IncomeManager WHERE Date BETWEEN ? AND ?" (unless your logic dictates otherwise)

But also as Shasur pointed out, you are using the same name for parameters. Instead, try this (adjust the actual syntax accordingly):

IncRep1OleDbDataAdapter.SelectCommand.Parameters.A dd("@StartDate", startDate)
IncRep1OleDbDataAdapter.SelectCommand.Parameters.A dd("@EndDate", endDate)

(Note that I think for the OleDb command, the actual name of the parameter doesn't link back into the database as it does in SqlClient.SqlCommand. However, use the name for clarification and possible later reference.)

-Peter

I have only one column in my database with Date.
The query is trying to report from a start date "a" to an end date "c", hence the code:
IncRep1OleDbDataAdapter.SelectCommand.Parameters.I tem("Date").Value = startDate
IncRep1OleDbDataAdapter.SelectCommand.Parameters.I tem("Date").Value = endDate

What then do i do with this Item("Date") parameters?

I have only one column in my database with Date.
The query is trying to report from a start date "a" to an end date "c", hence the code:
IncRep1OleDbDataAdapter.SelectCommand.Parameters.I tem("Date").Value = startDate
IncRep1OleDbDataAdapter.SelectCommand.Parameters.I tem("Date").Value = endDate

What then do i do with this Item("Date") parameters?

-John

I understand you have only 1 column in the table. You are attempting to call a parameter by the same name as that column. You can't do that. And you are putting unrecognizable T-SQL ("StartDateTimePicker.Value") in the query as I already pointed out.

I also just realized that I don't see where you are assigning the command object you created to the data adapter. Either you are missing it or haven't posted it.

Please try these changes:

chmcmd = New OleDbCommand("SELECT * FROM IncomeManager WHERE Date >= ? AND Date <= ?", chmcon)
...
IncRep1OleDbDataAdapter.SelectCommand.Parameters.A dd(startDate)
IncRep1OleDbDataAdapter.SelectCommand.Parameters.A dd(endDate)

The ? in the T-SQL serve as the parameter locations. Then you add the parameters to the select command and the command object handles the proper construction of the full query for you using the command text and the parameter values provided.

-Peter