Avuya Mxoli

Avuya Mxoli

  • NA
  • 236
  • 209k

SqlDateTime overflow

Oct 14 2011 10:50 AM
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);

Answers (7)