Stephan Bourgeois
asked on
Datatable created with the DATASET designer return 0 row
I have a problem with a datatableadapter in a c# form using Visual Studio 2013.
All of my datatableadapter are working perfectly except one.
In the Query Builder when I construct the SQL query and testing the result, the data appear correctly. I am getting rows.
In the same datatableadapter, in the preview window, I am writing both parameters and it return zero row. When executing the code, it return zero row.
This is a query on a view.
SELECT CustID, OfficeRelationID, OfficeID, PostalBox, Facility, FloorCode, RoomNumber
FROM viewOffices
WHERE (CustID = @CustID) AND (Facility = @Facility)
I am using the same method with many other tableadapter and they all working perfectly.
Query Builder
Preview Data
Thanks for any help
viewoffice.JPG
All of my datatableadapter are working perfectly except one.
In the Query Builder when I construct the SQL query and testing the result, the data appear correctly. I am getting rows.
In the same datatableadapter, in the preview window, I am writing both parameters and it return zero row. When executing the code, it return zero row.
This is a query on a view.
SELECT CustID, OfficeRelationID, OfficeID, PostalBox, Facility, FloorCode, RoomNumber
FROM viewOffices
WHERE (CustID = @CustID) AND (Facility = @Facility)
// Set the Office Room Number
DataSet1TableAdapters.viewOfficesTableAdapter daOffice = new DataSet1TableAdapters.viewOfficesTableAdapter();
DataSet1.viewOfficesDataTable dtOffice = daOffice.GetData(profiles[0].CustID, profiles[0].PrimaryFacility);
if (dtOffice.Rows.Count > 0)
{
if (dtOffice[0].RoomNumber.Length > 0)
I am using the same method with many other tableadapter and they all working perfectly.
Query Builder
Preview Data
Thanks for any help
viewoffice.JPG
It is really hard to tell what the issue is from your screenshots and code snips.
I can only fashion a guess that perhaps, you need to make sure that the parameters you are passing to your report have the same case level as your sql.
For instance, in your sql, you have @Facility.
My experience tells me that if I pass @facility to my report, it won't work because it is looking for same proper case of F instead of f.
Also, make sure you are pointing to correct database and table.
I can only fashion a guess that perhaps, you need to make sure that the parameters you are passing to your report have the same case level as your sql.
For instance, in your sql, you have @Facility.
My experience tells me that if I pass @facility to my report, it won't work because it is looking for same proper case of F instead of f.
Also, make sure you are pointing to correct database and table.
ASKER
Hi,
Sorry, I am very busy.
I used a datareader as a work around, but I will try your ideas with the Dataset next week.
Sorry, I am very busy.
I used a datareader as a work around, but I will try your ideas with the Dataset next week.
ASKER
I ran the profiler and it looks like there is a bug in the dataset.
When I run the query from the Query Builder, I am getting the correct result and the data profiler show the following info:
When preview the data from the Table Adapter dataset design view, I am getting no result and noticed that the CustID is missing the last digit as shown in the following result.
I finally use a datareader as shown in the following code:
I can't explain why the last digit of the parameter is lost, but the datareader way is working fine and I will use this method.
Thanks for the suggestions.
When I run the query from the Query Builder, I am getting the correct result and the data profiler show the following info:
exec sp_executesql N'SELECT CustID, OfficeRelationID, OfficeID, PostalBox, Facility, FloorCode, RoomNumber FROM viewOffices WHERE (CustID = @CustID) AND (Facility = @Facility)',N'@CustID nvarchar(7),@Facility nvarchar(8)',@CustID=N'0005543',@Facility=N'MONTREAL'
When preview the data from the Table Adapter dataset design view, I am getting no result and noticed that the CustID is missing the last digit as shown in the following result.
exec sp_executesql N'SELECT CustID, OfficeRelationID, OfficeID, PostalBox, Facility, FloorCode, RoomNumber
FROM viewOffices
WHERE (CustID = @CustID) AND (Facility = @Facility)',N'@CustID nvarchar(6),@Facility nvarchar(50)',@CustID=N'000554',@Facility=N'MONTREAL'
I finally use a datareader as shown in the following code:
string queryString = "SELECT CustID, OfficeRelationID, OfficeID, PostalBox, Facility, FloorCode, RoomNumber FROM viewOffices WHERE (CustID = '" + strCustID + "') AND (Facility = '" + strFacility + "')";
using (var connection = new SqlConnection(connStr))
{
var command = new SqlCommand(queryString, connection);
connection.Open();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
I can't explain why the last digit of the parameter is lost, but the datareader way is working fine and I will use this method.
Thanks for the suggestions.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That was the problem. You are the King.
Thank you very much.
Thank you very much.
If that does not help, next I would try two things:
1. right-click on "getdata" in the code, and go to "definition"*; this will open up the actual code generated by the wizard for that. Inspect it closely.
2. run Profiler on sql server, and find out what query was sent by preview and what was sent by the code. Find out the difference.
#2 is somewhat more involved, but it's the most powerful, you will see the difference at once.
*) that's how it works in VB that I use. Maybe in C# it's different.