Solved

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

Posted on 2006-06-16
2
221 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___
2 Comments
 
LVL 7

Accepted Solution

by:
pradeepsudharsan earned 500 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Extention Methods in C# 3.0 by Ivo Stoykov C# 3.0 offers extension methods. They allow extending existing classes without changing the class's source code or relying on inheritance. These are static methods invoked as instance method. This…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

776 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