Hi again,
I come with a new problem everyday!(see subject for problem) this time I have a problem with datetime. here is my code. the commented part is just how I was trying with parameters. I am using 3-tier architecture in my project so I have a businesslayer. What Im trying to do is to select a pool from the combobox and then the date from datetime picker then the game will be displayed in a grid. I am using stored procedure in sql server :ALTER PROCEDURE [dbo].[RemainingForPool]@Date datetime,@Pool nvarchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT * FROM Game
WHERE [Country 1] IN
(SELECT cName FROM Country WHERE [Pool] = @Pool)OR [Country 2] IN (SELECT cName FROM Country WHERE [Pool] = @Pool) AND [DateTime] > @Date
END
here is the business layer side:
using (SqlConnection conn = new SqlConnection(ConnString))
{
DataSet ds = new DataSet();
try
{
//DateTime tdate = DateTime.Today;
//DateTime thedate = (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue;
// date = tdate.ToString("yyyy-MM-dd HH:mm:ss.fff");
//SqlCommand cmd = new SqlCommand("RemainingForPool", conn);
//cmd.Parameters.AddWithValue("@Date", tdate);
//cmd.Parameters.AddWithValue("@Pool", pool);
DateTime tdate;
DateTime.TryParse(date, out tdate);
dbAdapter = new SqlDataAdapter();
dbAdapter.SelectCommand = new SqlCommand("RemainingForPool'" + "'" + date + "'" + pool + "'", conn);
dbAdapter.Fill(ds);
}
catch (SqlException sqlex)
{
MessageBox.Show("SQL exception: " + sqlex.Message);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return ds.Tables[0];
Then on the form.cs I have: DateTime date;
DateTime.TryParse(dtpToday.Text, out date);
dgvGames.DataSource = bl.remainingForPool(dtpToday.Text, cboPools.Text);
Loading
Avuya MxoliPosted Oct 15, 2011, 1:47 PM
Prabhu RajaPosted Oct 15, 2011, 1:57 AM
// put a break point here. and check stored value in string.
string sqlDateTimeAsString = date.ToString("yyyy-MM-ddTHH:mm:ss.fff")
Javeed M ShaikhPosted Oct 14, 2011, 12:32 PM
1. If you are passing the default date i would suggest pass null from the front date and let procedure handle the default date.
2. try converting the date to To Short DateTime String
3. Use Nullable with your date variable.
4. Format the date according to the culture you are using.
Avuya MxoliPosted Oct 14, 2011, 12:24 PM
Javeed M ShaikhPosted Oct 14, 2011, 12:11 PM
DateTime.TryParse(date, out tdate);
Avuya MxoliPosted Oct 14, 2011, 12:03 PM
Javeed M ShaikhPosted Oct 14, 2011, 11:09 AM