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.ToString();
      string strStreetName = txtStreetName.Text.ToString();
      string strKeymapPage = txtKeyMapPage.Text.ToString();
      string strNeighborhoodCode = txtNeighborhoodCode.Text.ToString();
      string strCountyName = txtCountyName.Text.ToString();
      string intYearBuilt = txtYearBuilt.Text.ToString();
      string intYearBuiltTo = txtYearBuiltTo.Text.ToString();
      string strGrade = selGrade.SelectedItem.ToString();
      string fltAssessmentRatio = txtAssessmentRatio.ToString();
      string fltAssessmentRatioTo = txtAssessmentRatioTo.ToString();
      string intSaleDate = txtSaleDate.ToString();     // this is where the problem starts
      string intSaleDateTo = txtSaleDateTo.ToString(); // this is where the problem starts

      string CONN = ConfigurationSettings.AppSettings["DB_CONN"];
      SqlConnection connObj = new SqlConnection(CONN);
      SqlCommand cmdObj = new SqlCommand("MLS_GetData", connObj);
      cmdObj.CommandType = CommandType.StoredProcedure;

      if (txtStreetNumber.Text != "")
      {
            cmdObj.Parameters.Add(new SqlParameter("@strStreetNum", strStreetNum));
      }
      if (txtStreetName.Text != "")
      {
            cmdObj.Parameters.Add(new SqlParameter("@strStreetName", strStreetName));
      }
      if (txtKeyMapPage.Text != "")
      {
            cmdObj.Parameters.Add(new SqlParameter("@strKeymapPage", strKeymapPage));
      }
      if (txtNeighborhoodCode.Text != "")
      {
            cmdObj.Parameters.Add(new SqlParameter("@strNeighborhoodCode", strNeighborhoodCode));
      }
      if (txtCountyName.Text != "")
      {
            cmdObj.Parameters.Add(new SqlParameter("@strCountyName", strCountyName));
      }
      if (txtYearBuilt.Text != "")
      {
            cmdObj.Parameters.Add(new SqlParameter("@intYearBuilt", intYearBuilt));
      }
      if (txtYearBuiltTo.Text != "")
      {
            cmdObj.Parameters.Add(new SqlParameter("@intYearBuiltTo", intYearBuiltTo));
      }
      if (selGrade.SelectedItem.Text != "")
      {
            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("@intSaleDateTo", intSaleDateTo));
      }

            SqlDataAdapter da = new SqlDataAdapter ( cmdObj ) ;
            DataSet dsMLS = new DataSet("MLS");
            da.SelectCommand.CommandTimeout = 0;
            da.Fill(dsMLS, "MLS");
            dgCustomFindResults.DataSource =  dsMLS.Tables["MLS"].DefaultView;
            dgCustomFindResults.DataBind();

}
====================================================


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_NoticedTotalValue/a.sale_price) 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


FastEddie___Asked:
Who is Participating?
 
pradeepsudharsanConnect With a Mentor Commented:
Hi,
First convert the string to DateTime format
Construct new Sql DateTime parameter and assign the converted datetime value to it.
Pass it to stored procedure.

string intSaleDate =txtSaleDate.Text;      // this is where the problem starts
                           
DateTime a;
a=DateTime.Parse(intSaleDate);
SqlCommand cmdObj = new SqlCommand("MLS_GetData", connObj);
cmdObj.CommandType = CommandType.StoredProcedure;


if (txtSaleDate.Text != "") ///////////// here is the FROM date //////////////
{
    SqlParameter var1=  new SqlParameter("@a",SqlDbType.DateTime);
    var1.Value=a ;
     cmdObj.Parameters.Add(var1);
}


Regards
Pradeep
0
 
hieukhtnCommented:
Before u execute the stored proc, run the 'set dateformat ' first !
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.