Solved

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

Posted on 2006-06-16
2
226 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 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
"Emulate" TAB key when press Enter Key 3 92
itextsharp with c# 3 33
locate sql commands in C# visual studio Project 6 67
Example code 13 42
This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

737 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