FastEddie___
asked on
Datatype conversion in a C# Web App that uses Dynamic Stored Procedure Parameters
Hello,
I have a C# web app that takes in stored procedures dynamically for textbox input fields. For the most part things are working OK except when I'm tying to pass in a datetime value. I'm getting this error:
" Error converting data type nvarchar to datetime. "
What is the proper way to pass in a date to a dynamic stored procedure?
Should it be handled at the .aspx.cs section or in the stored procedure itself?
Is it best to cast or convert?
Here is the aspx.cs page:
========================== ========== ========== =======
private void CustomFindEngage(object sender, System.EventArgs e)
{
string strStreetNum = txtStreetNumber.Text.ToStr ing();
string strStreetName = txtStreetName.Text.ToStrin g();
string strKeymapPage = txtKeyMapPage.Text.ToStrin g();
string strNeighborhoodCode = txtNeighborhoodCode.Text.T oString();
string strCountyName = txtCountyName.Text.ToStrin g();
string intYearBuilt = txtYearBuilt.Text.ToString ();
string intYearBuiltTo = txtYearBuiltTo.Text.ToStri ng();
string strGrade = selGrade.SelectedItem.ToSt ring();
string fltAssessmentRatio = txtAssessmentRatio.ToStrin g();
string fltAssessmentRatioTo = txtAssessmentRatioTo.ToStr ing();
string intSaleDate = txtSaleDate.ToString(); // this is where the problem starts
string intSaleDateTo = txtSaleDateTo.ToString(); // this is where the problem starts
string CONN = ConfigurationSettings.AppS ettings["D B_CONN"];
SqlConnection connObj = new SqlConnection(CONN);
SqlCommand cmdObj = new SqlCommand("MLS_GetData", connObj);
cmdObj.CommandType = CommandType.StoredProcedur e;
if (txtStreetNumber.Text != "")
{
cmdObj.Parameters.Add(new SqlParameter("@strStreetNu m", strStreetNum));
}
if (txtStreetName.Text != "")
{
cmdObj.Parameters.Add(new SqlParameter("@strStreetNa me", strStreetName));
}
if (txtKeyMapPage.Text != "")
{
cmdObj.Parameters.Add(new SqlParameter("@strKeymapPa ge", strKeymapPage));
}
if (txtNeighborhoodCode.Text != "")
{
cmdObj.Parameters.Add(new SqlParameter("@strNeighbor hoodCode", strNeighborhoodCode));
}
if (txtCountyName.Text != "")
{
cmdObj.Parameters.Add(new SqlParameter("@strCountyNa me", strCountyName));
}
if (txtYearBuilt.Text != "")
{
cmdObj.Parameters.Add(new SqlParameter("@intYearBuil t", intYearBuilt));
}
if (txtYearBuiltTo.Text != "")
{
cmdObj.Parameters.Add(new SqlParameter("@intYearBuil tTo", intYearBuiltTo));
}
if (selGrade.SelectedItem.Tex t != "")
{
cmdObj.Parameters.Add(new SqlParameter("@strGrade", strGrade));
}
if (txtSaleDate.Text != "") ///////////// here is the FROM date //////////////
{
cmdObj.Parameters.Add(new SqlParameter("@intSaleDate ", intSaleDate));
}
if (txtSaleDateTo.Text != "") /////////// here is the TO date //////////////
{
cmdObj.Parameters.Add(new SqlParameter("@intSaleDate To", intSaleDateTo));
}
SqlDataAdapter da = new SqlDataAdapter ( cmdObj ) ;
DataSet dsMLS = new DataSet("MLS");
da.SelectCommand.CommandTi meout = 0;
da.Fill(dsMLS, "MLS");
dgCustomFindResults.DataSo urce = dsMLS.Tables["MLS"].Defaul tView;
dgCustomFindResults.DataBi nd();
}
========================== ========== ========== ======
Here is the stored procedure. I think the problem is in the stored procedure:
========================== ========== ========== =======
CREATE PROCEDURE dbo.MLS_GetData
@strStreetNum varchar (6) = NULL,
@strStreetName varchar (30) = NULL,
@strKeymapPage nvarchar (6) = NULL,
@strNeighborhoodCode varchar (10) = NULL,
@strCountyName varchar (255) = NULL,
@intYearBuilt int = NULL,
@intYearBuiltTo int = NULL,
@strGrade char (1) = NULL,
@intSaleDate datetime = NULL, ------- here it is
@intSaleDateTo datetime = NULL, ------ here is another
@debug bit = 0
AS
DECLARE @sql nvarchar(4000),
@paramlist nvarchar(4000)
SELECT @sql =
'SELECT TOP 1000 a.cad_account_number, a.date_sold, a.county_name, a.sale_price,
b.txroll_streetNumber, b.txroll_StreetName, b.txroll_YearBuilt, b.Txroll_Keymap,
b.txroll_NeighborhoodCode, b.txroll_year, b.txroll_GBA, b.txroll_NoticedLandValue, b.txroll_NoticedTotalValue ,
b.txroll_Grade,
(b.txroll_NoticedTotalValu e/a.sale_p rice) AS ratio,
(a.sale_price - b.txroll_NoticedLandValue) /b.txroll_ GBA AS imp
FROM dbo.MLS a
INNER JOIN dbo.tbl_TaxRoll b
ON a.cad_account_number = b.txroll_CadAccountNumber
WHERE b.txroll_NeighborhoodCode IS NOT NULL AND b.txroll_GBA > 0 AND a.sale_price > 0'
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
IF @strStreetNum IS NOT NULL
SELECT @sql = @sql + ' AND b.txroll_streetNumber = @x_StreetNum'
IF @strStreetName IS NOT NULL
SELECT @sql = @sql + ' AND b.txroll_StreetName = @x_StreetName'
IF @strKeymapPage IS NOT NULL
SELECT @sql = @sql + ' AND b.txroll_Keymap = @x_KeymapPage'
IF @strNeighborhoodCode IS NOT NULL
SELECT @sql = @sql + ' AND b.txroll_NeighborhoodCode = @x_NeighborhoodCode'
IF @strCountyName IS NOT NULL
SELECT @sql = @sql + ' AND a.county_name = @x_CountyName'
IF @intYearBuilt IS NOT NULL
SELECT @sql = @sql + ' AND b.txroll_YearBuilt >= @x_YearBuilt'
IF @intYearBuiltTo IS NOT NULL
SELECT @sql = @sql + ' AND b.txroll_YearBuilt<= @x_YearBuiltTo'
IF @strGrade IS NOT NULL
SELECT @sql = @sql + ' AND b.txroll_Grade LIKE @x_Grade + ''%'''
IF @intSaleDate IS NOT NULL
SELECT @sql = @sql + ' AND a.date_sold >= CAST(@x_DateSold AS datetime)'
IF @intSaleDateTo IS NOT NULL
SELECT @sql = @sql + ' AND a.date_sold <= CAST(@x_DateSoldTo AS datetime)'
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------
SELECT @sql = @sql + ' ORDER BY b.txroll_NeighborhoodCode, a.date_sold DESC'
IF @debug = 1
PRINT @sql
SELECT @paramlist =
'@x_StreetNum varchar(6),
@x_StreetName varchar (30),
@x_KeymapPage nvarchar (6),
@x_NeighborhoodCode varchar (10),
@x_CountyName varchar (255),
@x_YearBuilt int,
@x_YearBuiltTo int,
@x_Grade char (1),
@x_DateSold datetime,
@x_DateSoldTo datetime'
EXEC sp_executesql @sql, @paramlist,
@strStreetNum, @strStreetName, @strKeymapPage, @strNeighborhoodCode, @strCountyName, @intYearBuilt, @intYearBuiltTo, @strGrade, @intSaleDate, @intSaleDateTo
RETURN
GO
========================== ========== ========== ========== ========== ======
Any help would be most appreciated.
-Eddie
I have a C# web app that takes in stored procedures dynamically for textbox input fields. For the most part things are working OK except when I'm tying to pass in a datetime value. I'm getting this error:
" Error converting data type nvarchar to datetime. "
What is the proper way to pass in a date to a dynamic stored procedure?
Should it be handled at the .aspx.cs section or in the stored procedure itself?
Is it best to cast or convert?
Here is the aspx.cs page:
==========================
private void CustomFindEngage(object sender, System.EventArgs e)
{
string strStreetNum = txtStreetNumber.Text.ToStr
string strStreetName = txtStreetName.Text.ToStrin
string strKeymapPage = txtKeyMapPage.Text.ToStrin
string strNeighborhoodCode = txtNeighborhoodCode.Text.T
string strCountyName = txtCountyName.Text.ToStrin
string intYearBuilt = txtYearBuilt.Text.ToString
string intYearBuiltTo = txtYearBuiltTo.Text.ToStri
string strGrade = selGrade.SelectedItem.ToSt
string fltAssessmentRatio = txtAssessmentRatio.ToStrin
string fltAssessmentRatioTo = txtAssessmentRatioTo.ToStr
string intSaleDate = txtSaleDate.ToString(); // this is where the problem starts
string intSaleDateTo = txtSaleDateTo.ToString(); // this is where the problem starts
string CONN = ConfigurationSettings.AppS
SqlConnection connObj = new SqlConnection(CONN);
SqlCommand cmdObj = new SqlCommand("MLS_GetData", connObj);
cmdObj.CommandType = CommandType.StoredProcedur
if (txtStreetNumber.Text != "")
{
cmdObj.Parameters.Add(new SqlParameter("@strStreetNu
}
if (txtStreetName.Text != "")
{
cmdObj.Parameters.Add(new SqlParameter("@strStreetNa
}
if (txtKeyMapPage.Text != "")
{
cmdObj.Parameters.Add(new SqlParameter("@strKeymapPa
}
if (txtNeighborhoodCode.Text != "")
{
cmdObj.Parameters.Add(new SqlParameter("@strNeighbor
}
if (txtCountyName.Text != "")
{
cmdObj.Parameters.Add(new SqlParameter("@strCountyNa
}
if (txtYearBuilt.Text != "")
{
cmdObj.Parameters.Add(new SqlParameter("@intYearBuil
}
if (txtYearBuiltTo.Text != "")
{
cmdObj.Parameters.Add(new SqlParameter("@intYearBuil
}
if (selGrade.SelectedItem.Tex
{
cmdObj.Parameters.Add(new SqlParameter("@strGrade", strGrade));
}
if (txtSaleDate.Text != "") ///////////// here is the FROM date //////////////
{
cmdObj.Parameters.Add(new SqlParameter("@intSaleDate
}
if (txtSaleDateTo.Text != "") /////////// here is the TO date //////////////
{
cmdObj.Parameters.Add(new SqlParameter("@intSaleDate
}
SqlDataAdapter da = new SqlDataAdapter ( cmdObj ) ;
DataSet dsMLS = new DataSet("MLS");
da.SelectCommand.CommandTi
da.Fill(dsMLS, "MLS");
dgCustomFindResults.DataSo
dgCustomFindResults.DataBi
}
==========================
Here is the stored procedure. I think the problem is in the stored procedure:
==========================
CREATE PROCEDURE dbo.MLS_GetData
@strStreetNum varchar (6) = NULL,
@strStreetName varchar (30) = NULL,
@strKeymapPage nvarchar (6) = NULL,
@strNeighborhoodCode varchar (10) = NULL,
@strCountyName varchar (255) = NULL,
@intYearBuilt int = NULL,
@intYearBuiltTo int = NULL,
@strGrade char (1) = NULL,
@intSaleDate datetime = NULL, ------- here it is
@intSaleDateTo datetime = NULL, ------ here is another
@debug bit = 0
AS
DECLARE @sql nvarchar(4000),
@paramlist nvarchar(4000)
SELECT @sql =
'SELECT TOP 1000 a.cad_account_number, a.date_sold, a.county_name, a.sale_price,
b.txroll_streetNumber, b.txroll_StreetName, b.txroll_YearBuilt, b.Txroll_Keymap,
b.txroll_NeighborhoodCode,
b.txroll_Grade,
(b.txroll_NoticedTotalValu
(a.sale_price - b.txroll_NoticedLandValue)
FROM dbo.MLS a
INNER JOIN dbo.tbl_TaxRoll b
ON a.cad_account_number = b.txroll_CadAccountNumber
WHERE b.txroll_NeighborhoodCode IS NOT NULL AND b.txroll_GBA > 0 AND a.sale_price > 0'
--------------------------
IF @strStreetNum IS NOT NULL
SELECT @sql = @sql + ' AND b.txroll_streetNumber = @x_StreetNum'
IF @strStreetName IS NOT NULL
SELECT @sql = @sql + ' AND b.txroll_StreetName = @x_StreetName'
IF @strKeymapPage IS NOT NULL
SELECT @sql = @sql + ' AND b.txroll_Keymap = @x_KeymapPage'
IF @strNeighborhoodCode IS NOT NULL
SELECT @sql = @sql + ' AND b.txroll_NeighborhoodCode = @x_NeighborhoodCode'
IF @strCountyName IS NOT NULL
SELECT @sql = @sql + ' AND a.county_name = @x_CountyName'
IF @intYearBuilt IS NOT NULL
SELECT @sql = @sql + ' AND b.txroll_YearBuilt >= @x_YearBuilt'
IF @intYearBuiltTo IS NOT NULL
SELECT @sql = @sql + ' AND b.txroll_YearBuilt<= @x_YearBuiltTo'
IF @strGrade IS NOT NULL
SELECT @sql = @sql + ' AND b.txroll_Grade LIKE @x_Grade + ''%'''
IF @intSaleDate IS NOT NULL
SELECT @sql = @sql + ' AND a.date_sold >= CAST(@x_DateSold AS datetime)'
IF @intSaleDateTo IS NOT NULL
SELECT @sql = @sql + ' AND a.date_sold <= CAST(@x_DateSoldTo AS datetime)'
--------------------------
SELECT @sql = @sql + ' ORDER BY b.txroll_NeighborhoodCode,
IF @debug = 1
PRINT @sql
SELECT @paramlist =
'@x_StreetNum varchar(6),
@x_StreetName varchar (30),
@x_KeymapPage nvarchar (6),
@x_NeighborhoodCode varchar (10),
@x_CountyName varchar (255),
@x_YearBuilt int,
@x_YearBuiltTo int,
@x_Grade char (1),
@x_DateSold datetime,
@x_DateSoldTo datetime'
EXEC sp_executesql @sql, @paramlist,
@strStreetNum, @strStreetName, @strKeymapPage, @strNeighborhoodCode, @strCountyName, @intYearBuilt, @intYearBuiltTo, @strGrade, @intSaleDate, @intSaleDateTo
RETURN
GO
==========================
Any help would be most appreciated.
-Eddie
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Eg : da.ExecuteNonQuery("set dateformat mdy");
(mdy means month then day then year, e.g 1/13/2005, change this appropriately to ur date format on ur web)
VGN