Nullable datatyped and coalesce
-
Hi all i am using Coalesce function in sqlserver 2005 following is sp ALTER PROCEDURE [dbo].[GetAllBillDetails] ( @FromDate AS DATETIME, @ToDate AS DATETIME ) AS BEGIN DECLARE @Err int SELECT BillNo, SalesDate, CustName, Address, Discount, TotalAmount, NetPayable FROM Sales WHERE SalesDate BETWEEN COALESCE(@FromDate,SalesDate) AND COALESCE(@ToDate,SalesDate) SET @Err = @@Error RETURN @Err END following is code to in C# to call this sp private void BindGrid() { SqlCommand command = new SqlCommand("GetAllBillDetails", sqlConn); SqlDataAdapter ada = new SqlDataAdapter(); DataSet dataSet = new DataSet("Master"); ada.TableMappings.Add("Table", "Master"); command.CommandType = CommandType.StoredProcedure; DateTime? fdate; DateTime? todate; fdate = dtpFromDate.Value.Date; todate = dtpToDate.Value.Date; if (!dtpFromDate.Checked) { fdate = null; } if (!dtpToDate.Checked) { todate = null; } command.Parameters.Add(new SqlParameter("@FromDate", fdate)); command.Parameters.Add(new SqlParameter("@ToDate", todate)); ada.SelectCommand = command; ada.Fill(dataSet); } It is giving me error like procedure or function GetAllBillDetails requires parameter @FromDate which was not supplied. Can any one help me to solve this issue... Dipak Thesiya DRC Pvt Ltd...:confused:
dipak
-
Hi all i am using Coalesce function in sqlserver 2005 following is sp ALTER PROCEDURE [dbo].[GetAllBillDetails] ( @FromDate AS DATETIME, @ToDate AS DATETIME ) AS BEGIN DECLARE @Err int SELECT BillNo, SalesDate, CustName, Address, Discount, TotalAmount, NetPayable FROM Sales WHERE SalesDate BETWEEN COALESCE(@FromDate,SalesDate) AND COALESCE(@ToDate,SalesDate) SET @Err = @@Error RETURN @Err END following is code to in C# to call this sp private void BindGrid() { SqlCommand command = new SqlCommand("GetAllBillDetails", sqlConn); SqlDataAdapter ada = new SqlDataAdapter(); DataSet dataSet = new DataSet("Master"); ada.TableMappings.Add("Table", "Master"); command.CommandType = CommandType.StoredProcedure; DateTime? fdate; DateTime? todate; fdate = dtpFromDate.Value.Date; todate = dtpToDate.Value.Date; if (!dtpFromDate.Checked) { fdate = null; } if (!dtpToDate.Checked) { todate = null; } command.Parameters.Add(new SqlParameter("@FromDate", fdate)); command.Parameters.Add(new SqlParameter("@ToDate", todate)); ada.SelectCommand = command; ada.Fill(dataSet); } It is giving me error like procedure or function GetAllBillDetails requires parameter @FromDate which was not supplied. Can any one help me to solve this issue... Dipak Thesiya DRC Pvt Ltd...:confused:
dipak
Try passing
System.DBNull.Valuerather than justnull.
Upcoming FREE developer events: * Developer Day Scotland My website