More than one parameter??

[jy836]

I am trying to display a bunch of rows from a database in a DataGrid, and I
want to sort them by year. I decided to use the "WHERE (yearID BETWEEN ? And
?)" query in the OleDbDataAdapter to get only the rows from the database
that were between two different years (which are specified at run-time).

The problem I am having is with this line:

OleDbDataAdapter4.SelectCommand.Parameters("yearID ").Value = [What should I
put here?]

Since I need two distinct numbers to use as parameters, how can I assign
them both to "yearID"? Thanks in advance.

Jay

[Brian Henry]

if you need two numbers why not use use two parameters and add more then one
parametere to the parameteres collection? I've never seen it done the way
you want to do it ever
"jy836" <mo***@nospam.com> wrote in message
news:7FFQb.116014$nt4.475219@attbi_s51...

I am trying to display a bunch of rows from a database in a DataGrid, and I want to sort them by year. I decided to use the "WHERE (yearID BETWEEN ? And ?)" query in the OleDbDataAdapter to get only the rows from the database
that were between two different years (which are specified at run-time).

The problem I am having is with this line:

OleDbDataAdapter4.SelectCommand.Parameters("yearID ").Value = [What should I put here?]

Since I need two distinct numbers to use as parameters, how can I assign
them both to "yearID"? Thanks in advance.

Jay

[jy836]

"Brian Henry" <brianiup[remove-me]@adelphia.net> wrote in message
news:%2******************@TK2MSFTNGP12.phx.gbl...

if you need two numbers why not use use two parameters and add more then one parametere to the parameteres collection? I've never seen it done the way
you want to do it ever

I'm not sure I understand. It's not that I need two numbers from the
database; I'm trying to display only the records from a certain span of
years. So I used the "BETWEEN" statement in my parameter for the "yearID"
field, but I don't know how to assign two numbers to one parameter.

[Brian Henry]

with SQL server you can use named parameters like @startdate @enddate... I
don't remember if you can force OLEDB to do that, but you may want to try
named parameters like that, then assign a value to each parametere name
"jy836" <mo***@nospam.com> wrote in message
news:oOGQb.116376$nt4.477885@attbi_s51...

"Brian Henry" <brianiup[remove-me]@adelphia.net> wrote in message
news:%2******************@TK2MSFTNGP12.phx.gbl...

if you need two numbers why not use use two parameters and add more then

one

parametere to the parameteres collection? I've never seen it done the way you want to do it ever

I'm not sure I understand. It's not that I need two numbers from the
database; I'm trying to display only the records from a certain span of
years. So I used the "BETWEEN" statement in my parameter for the "yearID"
field, but I don't know how to assign two numbers to one parameter.

[jy836]

"Brian Henry" <brianiup[remove-me]@adelphia.net> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

with SQL server you can use named parameters like @startdate @enddate... I
don't remember if you can force OLEDB to do that, but you may want to try
named parameters like that, then assign a value to each parametere name

Hmm...sorry, I'm very new to VB .NET. What exactly are "named" versus
"unnamed" parameters? And how would I go about using them? Thanks a bunch!
You guys have been a great help... :-D

Jay

[Brian Henry]

named parameters can have multiple values for the same column when checking
like what you are trying to do when unnamed is based on the column name it's
in refrence to
"jy836" <mo***@nospam.com> wrote in message
news:g0HQb.113838$sv6.589635@attbi_s52...

"Brian Henry" <brianiup[remove-me]@adelphia.net> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

with SQL server you can use named parameters like @startdate @enddate... I don't remember if you can force OLEDB to do that, but you may want to try named parameters like that, then assign a value to each parametere name

Hmm...sorry, I'm very new to VB .NET. What exactly are "named" versus
"unnamed" parameters? And how would I go about using them? Thanks a bunch!
You guys have been a great help... :-D

Jay

[Brian Henry]

named parameters you can do stuff like this

SELECT * FROM TABLE WHERE A = @value1 and A < @value2 or A > @value3

each parameter @value1 @value2 @value3 can have diffrent values.. you would
make a new parametere in the parameteres collection for the sql command like
this

cmdSQLSelect.parameters.add("@value1",data.sqldata type.int).value = 12
cmdSQLSelect.parameters.add("@value2",data.sqldata type.int).value = 46
cmdSQLSelect.parameters.add("@value3",data.sqldata type.int).value = 1
"jy836" <mo***@nospam.com> wrote in message
news:g0HQb.113838$sv6.589635@attbi_s52...

"Brian Henry" <brianiup[remove-me]@adelphia.net> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

with SQL server you can use named parameters like @startdate @enddate... I don't remember if you can force OLEDB to do that, but you may want to try named parameters like that, then assign a value to each parametere name

Hmm...sorry, I'm very new to VB .NET. What exactly are "named" versus
"unnamed" parameters? And how would I go about using them? Thanks a bunch!
You guys have been a great help... :-D

Jay

[Jay B. Harlow [MVP - Outlook]]

Jay,
In addition to Brian's comments.

If you want to select records for a given year you should not be using
BETWEEN, I would use equals "=" instead.

However if you want to select records for a span of years, including
possible one year, then you should use between.

' Where (YearID = ?)
' only supports selecting one year.

OleDbDataAdapter4.SelectCommand.Parameters("P1").V alue = 2004

' Where (YearID Between ? and ?)
' if you want to select two years you would use: OleDbDataAdapter4.SelectCommand.Parameters("P1").V alue = 2003
OleDbDataAdapter4.SelectCommand.Parameters("P2").V alue = 2004
' if you want to select one year (with between) you would use: OleDbDataAdapter4.SelectCommand.Parameters("P1").V alue = 2004
OleDbDataAdapter4.SelectCommand.Parameters("P2").V alue = 2004
Where YearID happens to be a field on the table you are running the select
against, while P1 & P2 are parameters to the SelectCommand (the names used
in the Parameters collection). Note, P1 could literally be the string
yearID, however this is not required. Instead of using the text name you can
use ordinal position (0, 1, 2...)

If you do not have it, I would strongly recommend you purchase David
Sceppa's book "Microsoft ADO.NET - Core Reference" from MS Press. It is both
a good tutorial on ADO.NET as well as a good desk reference once you know
ADO.NET.

Hope this helps
Jay


"jy836" <mo***@nospam.com> wrote in message
news:7FFQb.116014$nt4.475219@attbi_s51... I am trying to display a bunch of rows from a database in a DataGrid, and I want to sort them by year. I decided to use the "WHERE (yearID BETWEEN ? And ?)" query in the OleDbDataAdapter to get only the rows from the database
that were between two different years (which are specified at run-time).

The problem I am having is with this line:

OleDbDataAdapter4.SelectCommand.Parameters("yearID ").Value = [What should I put here?]

Since I need two distinct numbers to use as parameters, how can I assign
them both to "yearID"? Thanks in advance.

Jay

[jy836]

Whew! I finally got it to work. Much thanks to Brian Henry and Jay B. Harlow
for your help! I'd still be trying to figure out how to make it work without
your help....thanks again! :-)

Jay

P.S. I'm sure I'll be back for more questions soon! :-)

[Chris Dunaway]

On Sun, 25 Jan 2004 20:42:46 GMT, jy836 wrote:

I'd still be trying to figure out how to make it work without your help

That's why they get paid the big bucks!

--
Chris

To send me an E-mail, remove the underscores and lunchmeat from my E-Mail
address.