?
Solved

Datatype conversion in a C# Web App that uses Dynamic Stored Procedure Parameters

Posted on 2006-06-16
2
Medium Priority
?
237 Views
Last Modified: 2008-02-01
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


0
Comment
Question by:FastEddie___
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 7

Accepted Solution

by:
pradeepsudharsan earned 1500 total points
ID: 16926027
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
 

Expert Comment

by:hieukhtn
ID: 16927444
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question