Solved

Datatable created with the DATASET designer return 0 row

Posted on 2014-10-07
6
127 Views
Last Modified: 2014-10-20
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)

                    // 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) 

Open in new window


I am using the same method with many other tableadapter and they all working perfectly.

viewoffice.JPG
Query Builder
QueyBuilder.JPG
Preview Data
PreviewData.JPG
Thanks for any help
viewoffice.JPG
0
Comment
Question by:SergeGregoire
  • 3
  • 2
6 Comments
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 40368351
Almost certainly there's something wrong with the parameters, so I would start with examining their properties and ensuring that they correspond to the datatype in the database, and have correct length.

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.
0
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 40368786
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.
0
 
LVL 1

Author Comment

by:SergeGregoire
ID: 40387375
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.
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 1

Author Comment

by:SergeGregoire
ID: 40392233
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:

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'

Open in new window


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'

Open in new window


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())
                            {

Open in new window


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.
0
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 500 total points
ID: 40392315
I would check the Parameter of the query in Designer, chances are it has specified maximum length=6, so it gets cut off.
sample
0
 
LVL 1

Author Closing Comment

by:SergeGregoire
ID: 40392774
That was the problem. You are the King.

Thank you very much.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Google Directions API to Map URL -C#? 3 27
SQL Replication question 9 43
insert wont work in SQL 14 22
How come this regular expression allows "e"? 14 38
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question