store procedure don't return any data asp.net sql server 2008 r2

Question

I am coding a filter search functionality. that is, user will search data on the basis of any record either datetime or name, number.

here I am also returning the data from other table. So i have implemented a inner join too. For this purpose my store procedure is as follows-

ALTER PROC [dbo].[spFilterSearch]

@var1 VARCHAR(10),
@var2 VARCHAR(10),
@Printdate DATETIME,
@ToDate varchar(10),
@FromDate varchar(10)

AS BEGIN
    DECLARE @td DateTime SET @td=CONVERT(DATETIME,@ToDate,105)
    DECLARE @fd DateTime SET @fd= CONVERT(DATETIME,@FromDate,105)
    if(@Printdate <> NULL)
        BEGIN
            SELECT cVar1, cVar2, Printdate,
            bg.Fname AS Name
            FROM dbo.Table1 
            INNER JOIN dbo.jTable2 jt ON Table1.id = jt.id
            WHERE(cVar1 LIKE @var1+'%') AND
            (cVar2 LIKE @var2+'%') 
            AND (convert(datetime,Printdate,105)=@FPdate)
        END
    ELSE
        BEGIN
            SELECT cVar1, cVar2, Printdate,
            bg.Fname AS Name
            FROM dbo.Table1 
            INNER JOIN dbo.jTable2 jt ON Table1.id = jt.id
            WHERE(cVar1 LIKE @var1+'%') AND
            (cVar2 LIKE @var2+'%') 
            AND (convert(DATETIME,Printdate,105)>=@fd AND convert(DATETIME,Printdate,105)<=@td)

        END

END

(this procedure don't return any record) At front end I have coded like, to send null date I have created FPdate property as below public DateTime? FPdate { get; set; }

and in this way I am using it in search event handler

if (txtPrintedOn.Text != "")
          {
              string[] dtt = txtPrintedOn.Text.Split(' ');
              objprop.FPdate = Convert.ToDateTime(dtt);
          }
          else
          {
              objprop.FPdate = null;
          }

and to send it in the form of parameter from the business layer I have wrote code like this-

if (prop.FPdate.Equals(null))
        {
            cmd.Parameters.AddWithValue("@Printdate", SqlDbType.DateTime).Value = Convert.DBNull;
        }
        else
        {
            cmd.Parameters.Add("@Printdate", SqlDbType.DateTime).Value = prop.FPdate;
        }
if (prop.ToDate != null)
            cmd.Parameters.AddWithValue("@ToDate", SqlDbType.VarChar).Value = prop.ToDate;
        else cmd.Parameters.Add("@ToDate", SqlDbType.VarChar).Value = Convert.DBNull;

At the end Data set is being return empty. I am banging my head against the wall since many hours but not getting any clue

where I am going wrong. Please suggest a way to make it run.

Thanks

@Mukund while typing here i made that mistake , however i m not missing any parameter — Nikhil G, Oct 10 '14 at 05:27

score 0 · Answer 1 · edited May 23 '17 at 10:25

0

Change SP to use following

if(@Printdate is not NULL)

Checkout these links -

Is there any difference between IS NULL and =NULL

SQL is null and = null

Use this

cmd.Parameters.Add("@FPdate", SqlDbType.DateTime).Value = prop.FPdate.Value;

edited May 23 '17 at 10:25

Community

1
1

answered Oct 10 '14 at 05:20

Arindam Nayak

7,346
4
32
48

i have made changes as you have suggested, still this procedure don't return any record – Nikhil G Oct 10 '14 at 05:23
Have you tried the sp directly in management studio? , also use `.Value` for nullable property. – Arindam Nayak Oct 10 '14 at 05:23
yes i did, would you please give me an example where should i add this .Value – Nikhil G Oct 10 '14 at 05:26
i have also attached `Convert.DBnull' ..is it not sufficient to send null value from front end.. – Nikhil G Oct 10 '14 at 05:29
I think as long as procedure could not return any data, i can't get it on the front end... :) – Nikhil G Oct 10 '14 at 05:30
@NikhilG , you have not answered my question, are you able to execute SP , get result from management console using `null` value as well as proper value. – Arindam Nayak Oct 10 '14 at 05:32
yes, i m able to execute this procedure, there it don't return any data on supplying actual values. – Nikhil G Oct 10 '14 at 05:33
From code , you are passing `@ToDate` as null, but in SP , there is no check for this. I think you need to add default value as `NULL` to SP params. – Arindam Nayak Oct 10 '14 at 05:35
`USE [BMDDB] GO DECLARE @return_value int EXEC @return_value = [dbo].[ProdFSearch] @var1 = NULL, @var2 = NULL, @FPdate = NULL, @ToDate = N'2014-09-22 00:00:00.000', @FromDate = N'2014-07-02 00:00:00.000' SELECT 'Return Value' = @return_value GO` – Nikhil G Oct 10 '14 at 05:37
I expect, you would be doing `ExecuteReader` and save that in `DataReader` and used `.Read` to read those!, are you getting any SQL error for this? – Arindam Nayak Oct 10 '14 at 05:40
ArindamNayak sir, I have also `Convert.DBnull` ..is it not able to supply `null value` as `NULL` – Nikhil G Oct 10 '14 at 05:41
it is `SqlDataAdapter` ...!! – Nikhil G Oct 10 '14 at 05:41
You can directly pass `null` and try! – Arindam Nayak Oct 10 '14 at 05:42
If this date value is creating any problem N'2014-07-02 00:00:00.000'?? – Nikhil G Oct 10 '14 at 05:48
i have made all parameters as =NULL and still it don't yield any result – Nikhil G Oct 10 '14 at 05:48
If you are not getting SQL error , while executing and filling, then there can be either issue in code which executes SP or Filling data adapter, if you can post FULL code, that will be helpful. – Arindam Nayak Oct 10 '14 at 05:51
would you please provide me your email id where i can reach to you. thanks! or add me as [email protected] – Nikhil G Oct 10 '14 at 06:00

store procedure don't return any data asp.net sql server 2008 r2

1 Answers1