views:

42

answers:

4

I am passing parameters to a stored proc. The parameters code block on the asp.net side is:

SqlConnection con = new SqlConnection(strConn);  
string sqlItemSearch = "usp_Item_Search";  
SqlCommand cmdItemSearch = new SqlCommand(sqlItemSearch, con);  
cmdItemSearch.CommandType = CommandType.StoredProcedure;  

cmdItemSearch.Parameters.Add(new SqlParameter("@Item_Num", SqlDbType.VarChar, 30));  
cmdItemSearch.Parameters["@Item_Num"].Value = txtItemNumber.Text.Trim();  

cmdItemSearch.Parameters.Add(new SqlParameter("@Search_Type", SqlDbType.Int));  
cmdItemSearch.Parameters["@Search_Type"].Value = ddlSearchType.SelectedItem.Value;  

cmdItemSearch.Parameters.Add(new SqlParameter("@Vendor_Num", SqlDbType.VarChar, 10));  
cmdItemSearch.Parameters["@Vendor_Num"].Value = txtVendorNumber.Text.Trim();  

cmdItemSearch.Parameters.Add(new SqlParameter("@Search_User_ID", SqlDbType.Int));  
cmdItemSearch.Parameters["@Search_User_ID"].Value = ddlSeachUser.SelectedItem.Value;  

if (!string.IsNullOrEmpty(txtStartDate.Text))  
{  
    cmdItemSearch.Parameters.Add(new SqlParameter("@StartDate", SqlDbType.DateTime));  
    cmdItemSearch.Parameters["@StartDate"].Value = Convert.ToDateTime(txtStartDate.Text.Trim());  
}  
else  
{  
    cmdItemSearch.Parameters.Add(new SqlParameter("@StartDate", SqlDbType.DateTime));  
    cmdItemSearch.Parameters["@StartDate"].Value = Convert.ToDateTime("01/01/1996");  
}  

if (!string.IsNullOrEmpty(txtEndDate.Text))  
{  
    cmdItemSearch.Parameters.Add(new SqlParameter("@EndDate", SqlDbType.DateTime));  
    cmdItemSearch.Parameters["@EndDate"].Value = Convert.ToDateTime(txtEndDate.Text.Trim());  
}  
else  
{  
    cmdItemSearch.Parameters.Add(new SqlParameter("@EndDate", SqlDbType.DateTime));  
    cmdItemSearch.Parameters["@EndDate"].Value = Convert.ToDateTime(DateTime.Now);  
}  
con.Open();  

SqlDataAdapter ada = new SqlDataAdapter(cmdItemSearch);  
DataSet ds = new DataSet();  
ada.Fill(ds);  

gvSearchResults.DataSource = ds;  
gvSearchResults.DataBind();

I tried using

DateTime.ParseExact(this.Text, "dd/MM/yyyy", null);

but I get the same error. The corressponding param in SQL is DateTime. I am currently passing blank fields for @StartDate and @EndDate, so the default values are passed as parameters. The error occurs at ada.Fill(ds) line. What would be causing the error?

A: 

http://stackoverflow.com/questions/2193012/string-was-not-recognized-as-a-valid-datetime-format-dd-mm-yyyy/3141060#3141060

this can help you

Serkan Hekimoglu
Should be posted as a comment - you have enough rep.
ChrisF
sorry Im new on Stack Over Flow. Will be careful next time. Thank you.
Serkan Hekimoglu
tried the "DateTime.ParseExact" but still getting the same error :-(
DotNetRookie
you are getting your datetime value from textBox right? if yes, do it after getting the value. DateTime newDate = new DateTime(Convert.ToInt32(textBox.Text.Substring(x,y))),Convert.ToInt32(textBox.Text.Substring(x,y))),Convert.ToInt32(textBox.Text.Substring(x,y)));new DateTime( requires 3 parameters which are Year,Month,Day )with using substring put the required parameters in new DateTime(HERE)
Serkan Hekimoglu
why would you convert to Int32?
DotNetRookie
Because you are trying to get a numeric value from textBox.Text. If you dont Convert it to Int32, you get an exception.
Serkan Hekimoglu
but I am converting it to DateTime.
DotNetRookie
you are not converting it to datetime. With my code, you are creating a new date time with the numeric values getting from textBox.
Serkan Hekimoglu
DotNetRookie
at your code, you get an exception in this part Convert.ToDateTime(txtEndDate.Text.Trim());and you solve the problem, with what I wrote.
Serkan Hekimoglu
That line is NOT executed. The ELSE section gets executed.
DotNetRookie
Seriously, I dont understand how Convert.ToDateTime(DateTime.Now) and DateTime.Now throws exception. because this error message Conversion failed when converting datetime from character string. looks like throwing from textBox.Text.Trim() part. I gonna search this.
Serkan Hekimoglu
A: 

Check the culture the database is configured to use and make sure the date/time being passed in (as configured by the CurrentCulture), is compatible.

If you don't have control over the culture the database is using, you can force it to accept a specific format by prepending SET DATEFORMAT yada yada to your script, e.g.:

SET DATEFORMAT ymd;

SELECT ... WHERE [StartDate] = @StartDate
Matthew Abbott
A: 

The default .NET DateTime isn't a valid SQL DateTime value; that's what the error is coming from. If you're going to pass a DateTime parameter for search,but don't have a specific value to search on, you should provide something in SQL's DateTime value range that will work for all your searches.

Cylon Cat
can you provide an sample string?
DotNetRookie
The minimum value for SQL Server DateTime values is January 1, 1753. If you're looking for a minimum date, that's it. Also, for default DateTime values, rather than converting a text string, just create a new DateTime, like this: DateTime defaultDate = new DateTime(1753, 1, 1);
Cylon Cat
A: 

and the solution is.......

cmdItemSearch.Parameters["@EndDate"].Value = DateTime.Now;

not the Convert.ToDateTime(DateTime.Now);

DotNetRookie