More than one parameter??

  • Thread starter Thread starter jy836
  • Start date Start date
J

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
 
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
 
Brian Henry said:
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
Click to expand...

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.
 
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
 
Brian Henry said:
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
Click to expand...

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
 
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.sqldatatype.int).value = 12
cmdSQLSelect.parameters.add("@value2",data.sqldatatype.int).value = 46
cmdSQLSelect.parameters.add("@value3",data.sqldatatype.int).value = 1
 
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
 
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").Value = 2004
Click to expand...


' Where (YearID Between ? and ?)
' if you want to select two years you would use:
OleDbDataAdapter4.SelectCommand.Parameters("P1").Value = 2003
OleDbDataAdapter4.SelectCommand.Parameters("P2").Value = 2004
Click to expand...

' if you want to select one year (with between) you would use:
OleDbDataAdapter4.SelectCommand.Parameters("P1").Value = 2004
OleDbDataAdapter4.SelectCommand.Parameters("P2").Value = 2004
Click to expand...

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 said:
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
Click to expand...
 
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! :-)
 
Back
Top