Using a Parameterized SelectCommand with OleDBDataAdapter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I'm having trouble using a parameters with the SelectCommand of an
OleDBDataAdapter. The situation is this...

1) I'm populating a DataSet that has three OleDBDataAdapters that feed it.
The Tables that they feed are table R1, R1History, and R1Allocations. The
R1 table is the main table. It has a 1 to many relationship with the other
two tables. All threee OleDBDataAdapters are read-only (no Delete, Update,
or Insert commands).

2) The SelectCommand for the OleDBDataAdapter that retrieves R1 has a parm.
The CommandText is...

?oleDbDataAdapter1.SelectCommand.CommandText
"Select * from R1 INNER JOIN RWeeks ON RDate = DayDate WHERE PeriodId = ?"

3) My code that calculates "period" and sets the parm seems to work.

?oleDbDataAdapter1.SelectCommand.Parameters(0).Value
200544 {Integer}
[Integer]: 200544 {Integer}

200544 is the correct value

4) I don't get any exceptions when oleDbDataAdapter.Fill is called, but I
don't get any records either. I know there are records because I ran the
"same" Select command in the Query Analyzer (SQL Server 2000 is the Db).

Select * from R1 INNER JOIN RWeeks ON RDate = DayDate WHERE PeriodId = 200544

returns one row, which is the correct answer.

Any suggestions / observations? BTW, I'm using the OleDbDataAdapter because
the parent form was created using the Data Form Wizard and it did the
original selection of the components to use. I'm extending this form. I'm
considering changing everything to use SqlDataAdapters, but I'd rather not if
I can make this work.

One more thing, I don't explicitly map all the fields that the query
returns. The "MissingMapping" property of the DataAdapter is set to
"Passthrough", Missing Schema is set to "Add".

Thanks for your help.

Steve
 
1) If you're accessing SQL Server you really should use the SqlClient .NET
data provider. It's far faster, better tuned to SQL Server and does not
require any COM components.

2) I would run the profiler while executing the code to see what OleDb is
sending SQL Server. This should isolate the problem.


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Hi Bill,

Thank you for your response.

As you suggested, I ran the Profiler while running my app.

1) The statement executed by SQL Server is...

exec sp_executesql N'Select Id, ResType, ResOkDate, Reference, SKU,
Sequence, Season, Style, Color, Dimension, Size, Warehouse, Quantity,
Balance, LastChange FROM Resources INNER JOIN ATSWeeks ON ResOkDate = DayDate
WHERE PeriodId = @P1', N'@P1 int', 200544

This looks OK to me. (BTW, I changed my CommandText to explicitly call out
the column names thinking that might be the problem - no help).

2) I pasted this into the Query Analyzer and exectued it. Returns one row
as it should.

3. When I query the DataSet I get no rows returned...

?AllAlcsDS1.Resources.Count
0

I'm stumped. Any ideas as to what else might be wrong before I start
changing out the DataAdapters?

I think I'll try "hard coding" the parm value into the CommandText and see
what happens.

Thanks.

BBM
William (Bill) Vaughn said:
1) If you're accessing SQL Server you really should use the SqlClient .NET
data provider. It's far faster, better tuned to SQL Server and does not
require any COM components.

2) I would run the profiler while executing the code to see what OleDb is
sending SQL Server. This should isolate the problem.


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

BBM said:
Hi,

I'm having trouble using a parameters with the SelectCommand of an
OleDBDataAdapter. The situation is this...

1) I'm populating a DataSet that has three OleDBDataAdapters that feed
it.
The Tables that they feed are table R1, R1History, and R1Allocations.
The
R1 table is the main table. It has a 1 to many relationship with the
other
two tables. All threee OleDBDataAdapters are read-only (no Delete,
Update,
or Insert commands).

2) The SelectCommand for the OleDBDataAdapter that retrieves R1 has a
parm.
The CommandText is...

?oleDbDataAdapter1.SelectCommand.CommandText
"Select * from R1 INNER JOIN RWeeks ON RDate = DayDate WHERE PeriodId = ?"

3) My code that calculates "period" and sets the parm seems to work.

?oleDbDataAdapter1.SelectCommand.Parameters(0).Value
200544 {Integer}
[Integer]: 200544 {Integer}

200544 is the correct value

4) I don't get any exceptions when oleDbDataAdapter.Fill is called, but I
don't get any records either. I know there are records because I ran the
"same" Select command in the Query Analyzer (SQL Server 2000 is the Db).

Select * from R1 INNER JOIN RWeeks ON RDate = DayDate WHERE PeriodId =
200544

returns one row, which is the correct answer.

Any suggestions / observations? BTW, I'm using the OleDbDataAdapter
because
the parent form was created using the Data Form Wizard and it did the
original selection of the components to use. I'm extending this form.
I'm
considering changing everything to use SqlDataAdapters, but I'd rather not
if
I can make this work.

One more thing, I don't explicitly map all the fields that the query
returns. The "MissingMapping" property of the DataAdapter is set to
"Passthrough", Missing Schema is set to "Add".

Thanks for your help.

Steve
Click to expand...
Click to expand...
 
Hi Bill,

Please ignore my last message. In looking at the program I realized that
the DataSet I was testing to see if rows were returned was the wrong one (the
DataForm Wizard, as I'm sure you're aware, uses a "temp" DataSet to read data
and then merges it with the real one)!!

I was completely faked out my main display which shows blank, but the query
is returning rows. I think the error I'm seeing comes from the other
DataAdapters NOT filtering rows based on the main row selected. The other
two adapters are returning TOO MANY rows (rows whose foreign key values don't
match the key of the main row - which would explain the error message I'm
getting.

"Failed to enable constraints. One or more rows contain values violating
non-null, unique, or foreign-key constraints."

So I need to RTFM on how to get the child tables to populate correctly.

Thanks for your help. Any other observations you might have are welcome.

BBM

William (Bill) Vaughn said:
1) If you're accessing SQL Server you really should use the SqlClient .NET
data provider. It's far faster, better tuned to SQL Server and does not
require any COM components.

2) I would run the profiler while executing the code to see what OleDb is
sending SQL Server. This should isolate the problem.


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

BBM said:
Hi,

I'm having trouble using a parameters with the SelectCommand of an
OleDBDataAdapter. The situation is this...

1) I'm populating a DataSet that has three OleDBDataAdapters that feed
it.
The Tables that they feed are table R1, R1History, and R1Allocations.
The
R1 table is the main table. It has a 1 to many relationship with the
other
two tables. All threee OleDBDataAdapters are read-only (no Delete,
Update,
or Insert commands).

2) The SelectCommand for the OleDBDataAdapter that retrieves R1 has a
parm.
The CommandText is...

?oleDbDataAdapter1.SelectCommand.CommandText
"Select * from R1 INNER JOIN RWeeks ON RDate = DayDate WHERE PeriodId = ?"

3) My code that calculates "period" and sets the parm seems to work.

?oleDbDataAdapter1.SelectCommand.Parameters(0).Value
200544 {Integer}
[Integer]: 200544 {Integer}

200544 is the correct value

4) I don't get any exceptions when oleDbDataAdapter.Fill is called, but I
don't get any records either. I know there are records because I ran the
"same" Select command in the Query Analyzer (SQL Server 2000 is the Db).

Select * from R1 INNER JOIN RWeeks ON RDate = DayDate WHERE PeriodId =
200544

returns one row, which is the correct answer.

Any suggestions / observations? BTW, I'm using the OleDbDataAdapter
because
the parent form was created using the Data Form Wizard and it did the
original selection of the components to use. I'm extending this form.
I'm
considering changing everything to use SqlDataAdapters, but I'd rather not
if
I can make this work.

One more thing, I don't explicitly map all the fields that the query
returns. The "MissingMapping" property of the DataAdapter is set to
"Passthrough", Missing Schema is set to "Add".

Thanks for your help.

Steve
Click to expand...
Click to expand...
 
Back
Top