Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
VB.NET 2002/2003 Basics For coders who are new to Visual Basic, working in .NET versions 2002 or 2003 (1.0 and 1.1).
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB.NET 2002/2003 Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 9th, 2007, 07:11 AM
Authorized User
 
Join Date: Dec 2006
Location: Accra, , Ghana.
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default Parameterized Query

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)

 
Old July 9th, 2007, 07:45 AM
Friend of Wrox
Points: 3,060, Level: 23
Points: 3,060, Level: 23 Points: 3,060, Level: 23 Points: 3,060, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: , , .
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

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
 
Old July 9th, 2007, 07:57 AM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

Your SQL query has code in it that the database engine won't understand.
Quote:
quote:Originally posted by asters
                 chmcmd = New OleDbCommand("SELECT * FROM IncomeManager WHERE Date >= StartDateTimePicker.Value AND Date <= EndDateTimePicker.Value", chmcon)
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
 
Old July 9th, 2007, 10:14 AM
Authorized User
 
Join Date: Dec 2006
Location: Accra, , Ghana.
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?

 
Old July 9th, 2007, 10:16 AM
Authorized User
 
Join Date: Dec 2006
Location: Accra, , Ghana.
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by Shasur
 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

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

 
Old July 9th, 2007, 12:08 PM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

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




Similar Threads
Thread Thread Starter Forum Replies Last Post
Parameterized Property surendraparashar C# 2005 4 October 17th, 2007 11:33 PM
DateTime Typed Paramater on Parameterized Query Mutia VB.NET 2002/2003 Basics 1 July 25th, 2007 08:57 AM
Calling Parameterized Update Query from C# fcortes C# 6 July 11th, 2007 08:47 AM
Parameterized SQLDataAdapter boyshey VB.NET 2002/2003 Basics 1 April 14th, 2006 07:08 AM
Sql Parameterized query, @ or ? Pauline BOOK: Beginning ASP.NET 1.0 2 January 26th, 2005 11:33 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.