Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2006-06-16
2
Medium Priority
?
240 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

618 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