Dataset is empty error

Question

0.00/5 (No votes)

See more:

Hi, i have the following codes which is to get data from stored procedure in my database and export it into a datatable. But i have found that the dataset is empty. It fetch no data. Therefore the datatable which is suppose to get the data from dataset is also empty. Below are my codes:

C#

var GeminiConnString = ConfigurationManager.ConnectionStrings["GeminiConnString"].ConnectionString;
        SqlConnection conn = null;
        SqlCommand command = null;
        DataTable dt = new DataTable();

        using (conn = new SqlConnection(GeminiConnString)) 
        {
            using (command = new SqlCommand("dbo.GetGeminiRecordByDate", conn)) 
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@startDate", StartDate));
                command.Parameters.Add(new SqlParameter("@endDate", EndDate));

                conn.Open();
                command.ExecuteNonQuery();

                SqlDataAdapter da = new SqlDataAdapter(command);
                DataSet ds = new DataSet();
                da.Fill(ds);
                dt = ds.Tables[0];

                if (ds.Tables[0].Rows.Count == 0) 
                {
                    Console.WriteLine("Empty");
                }
                foreach (DataRow dRow in dt.Rows) 
                {
                    Console.WriteLine(type);
                    Console.Write(dRow["issueid"].ToString());
                }
                conn.Close();
            } 
        }

Below are my stored procedure codes:

SQL

ALTER PROCEDURE [dbo].[GetGeminiRecordByDate]
	-- Add the parameters for the stored procedure here
	@startDate VARCHAR(MAX), 
	@endDate VARCHAR(MAX)
	
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE @SQL NVARCHAR(MAX)
	DECLARE @param NVARCHAR (MAX)
	
	SELECT @param = '@startDate VARCHAR(MAX), 
					 @endDate VARCHAR(MAX)'
					 
	SELECT @SQL = 'SELECT * FROM [rndbug].[dbo].[gemini_issuesview]
				   WHERE CONVERT(varchar(10), created, 120) BETWEEN ('+@startDate+') AND ('+@endDate+')'
	
	EXEC sp_executesql @SQL, @param, @startDate, @endDate
END

FIY, the @SQL and @param is same as above while @startDate is '2014-03-01' and @endDate is '2014-08-01'

Posted 4-Jun-14 16:46pm

Jamie888

Updated 4-Jun-14 18:11pm

v3

Add a Solution

Comments

ArunRajendra 4-Jun-14 23:23pm

Use the SQL profiler to check the parameters being passed.

Jamie888 4-Jun-14 23:29pm

i have checked it using sql profiler, the params has been passed in. But it still didnt return any result.

DamithSL 4-Jun-14 23:49pm

can you update the question with GetGeminiRecordByDate stored procedure code?

Jamie888 4-Jun-14 23:51pm

i have execute the stored procedure in sql server n it works fine.

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 4-Jun-14 23:56pm

Is your ConnectionString correct?

Jamie888 5-Jun-14 0:02am

yes. I have tried SQL Profiler to debug it and the parameters has been passed into the stored procedure.

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 5-Jun-14 0:07am

What is in sp_executesql procedure? Please add that by clicking on Improve Question link.

_Asif_ 5-Jun-14 1:25am

Why are you using ExecuteNonQuery??? This probably is the issue why you are not getting the data from SP

Jamie888 5-Jun-14 2:18am

@Syed, i have tried using the ExecuteReader() but it wont work either. Below is the solution to the problem. Just change the string into datetime format in both SP and C# program.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

DamithSL · Accepted Answer · 2014-06-04T18:16:00

C#

command.Parameters.AddWithValue("@startDate", StartDate); //StartDate should be a DateTime object
command.Parameters.AddWithValue("@endDate", EndDate);//EndDate should be a DateTime object

change your SP as below

SQL

ALTER PROCEDURE [dbo].[GetGeminiRecordByDate]
	-- Add the parameters for the stored procedure here
	@startDate DATETIME, 
	@endDate DATETIME
	
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE @SQL NVARCHAR(MAX)
	DECLARE @param NVARCHAR (MAX)
	
	SELECT @param = '@startDate DATETIME, 
					 @endDate DATETIME'
					 
	SELECT @SQL = 'SELECT * FROM [rndbug].[dbo].[gemini_issuesview]
				   WHERE created BETWEEN @startDate AND @endDate'
	
	EXEC sp_executesql @SQL, @param, @startDate, @endDate
END

if you need to avoid time part of the datetime, change the SQL as below

SQL

SELECT @SQL = 'SELECT * FROM [rndbug].[dbo].[gemini_issuesview]
				   WHERE CONVERT (DATE,created) BETWEEN CONVERT (DATE,@startDate) AND CONVERT (DATE,@endDate)'

Read How to get Date Part only from DateTime in Sql Server[^]