"Jassim Rahma" <jrahma@hotmail.com> wrote in message
news:OijLUt05HHA.1484@TK2MSFTNGP06.phx.gbl...
> this is my full C# code followed by the database structure:
>
> private void getScheduleList()
> {
> data_table = new DataTable();
> // sql_connection = new SqlConnection("Data
> Source=.\\SQLEXPRESS;initial catalog=shefa;integrated security=true");
> sql_connection = new SqlConnection(public_var.sql_connection);
>
> sql_command = new SqlCommand("sp_get_staff_schedule",
> sql_connection);
> sql_command.CommandType = CommandType.StoredProcedure;
> sql_command.Parameters.Add("@id_number",
> SqlDbType.VarChar).Value = cboDoctor.Text.Substring(0, 9);
>
> sql_command.Parameters.Add("@starttime",
> SqlDbType.DateTime).Value = dateSchedule.Value;
>
> if (cboRange.Text == "DAY")
> sql_command.Parameters.Add("@endtime",
> SqlDbType.DateTime).Value = dateSchedule.Value;
> else if (cboRange.Text == "WEEK")
> sql_command.Parameters.Add("@endtime",
> SqlDbType.DateTime).Value = dateSchedule.Value.AddDays(7);
> else if (cboRange.Text == "MONTH")
> sql_command.Parameters.Add("@endtime",
> SqlDbType.DateTime).Value =
> dateSchedule.Value.AddDays(31).ToShortDateString();
> else
> sql_command.Parameters.Add("@endtime",
> SqlDbType.DateTime).Value = dateSchedule.Value;
>
> data_adapter = new SqlDataAdapter(sql_command);
> data_adapter.Fill(data_table);
> dataSchedule.DataSource = data_table;
>
> dataSchedule.Columns[0].HeaderText = "Type";
> dataSchedule.Columns[0].DefaultCellStyle.Alignment =
> DataGridViewContentAlignment.TopLeft;
> dataSchedule.Columns[0].Width = 180;
>
> dataSchedule.Columns[1].HeaderText = "Staff Name";
> dataSchedule.Columns[1].DefaultCellStyle.Alignment =
> DataGridViewContentAlignment.TopLeft;
> dataSchedule.Columns[1].Width = 180;
>
> dataSchedule.Columns[2].HeaderText = "From";
> dataSchedule.Columns[2].DefaultCellStyle.Alignment =
> DataGridViewContentAlignment.TopCenter;
> dataSchedule.Columns[2].DefaultCellStyle.Format = "ddd dd MMM
> yyyy HH:mm";
> dataSchedule.Columns[2].Width = 130;
>
> dataSchedule.Columns[3].HeaderText = "To";
> dataSchedule.Columns[3].DefaultCellStyle.Alignment =
> DataGridViewContentAlignment.TopCenter;
> dataSchedule.Columns[3].DefaultCellStyle.Format = "ddd dd MMM
> yyyy HH:mm";
> dataSchedule.Columns[3].Width = 130;
> }
>
>
> DATABASE STRUCTURE:
> [id] [int] IDENTITY(1,1) NOT NULL,
> [id_number] [varchar](50) NULL,
> [company_reg_number] [int] NULL,
> [resourceid] [int] NULL,
> [status] [int] NULL,
> [subject] [varchar](50) NULL,
> [description] [text] NULL,
> [label] [int] NULL,
> [starttime] [datetime] NULL,
> [endtime] [datetime] NULL,
> [schedule_type] [varchar](255) NULL,
> [location] [varchar](50) NULL,
> [allday] [bit] NULL,
> [eventtype] [int] NULL,
> [recurrenceinfo] [text] NULL,
> [reminderinfo] [text] NULL,
> [staff_file_no] [int] NULL,
>
>
>
>
>
>
>
> "Shiju Samuel" <shiju.samuel@gmail.com> wrote in message
> news:1188068246.511637.298140@q3g2000prf.googlegroups.com...
>> So What is the issue? Post your schema with sample data.
>>
>> I guess you need to query something like this
>> Select * from table1 where @inputdate between startdate and enddate +
>> 1
>>
>> Thanks
>> Shiju Samuel
>>
>>
>> On Aug 25, 11:25 pm, "Jassim Rahma" <jra...@hotmail.com> wrote:
>>> well, just to explain more, i ave two fields, starttime and endtime and
>>> i
>>> want to query dates within that range.
>>>
>>> The starttime the a datetimepicker control in windows form and the
>>> endtime
>>> is selected from a dropdownlist contaiins (one day, one week, one month)
>>> so
>>> I am assiging the endtime value as .AddDays.
>>>
>>> if one day:
>>> sql_command.Parameters.Add("@endtime", SqlDbType.DateTime).Value =
>>> dateSchedule.Value;
>>>
>>> if one week:
>>> sql_command.Parameters.Add("@endtime", SqlDbType.DateTime).Value =
>>> dateSchedule.Value.AddDays(7);
>>>
>>> if one month:
>>> sql_command.Parameters.Add("@endtime", SqlDbType.DateTime).Value =
>>> dateSchedule.Value.AddDays(31);
>>>
>>> "Jassim Rahma" <jra...@hotmail.com> wrote in message
>>>
>>> news:07B5E069-764D-4F2C-A1B0-8497DCCA1039@microsoft.com...
>>>
>>>
>>>
>>> > it's working but not when i use this is C#. why?
>>> > sql_command.Parameters.Add("@endtime", SqlDbType.DateTime).Value =
>>> > dateSchedule.Value.AddDays(7);
>>>
>>> > "Dan Guzman" <guzma...@nospam-online.sbcglobal.net> wrote in message
>>> >news:82050AE4-8EF0-495B-A4AB-8BBA82780050@microsoft.com...
>>> >>> and i want to SELECt all the 8/22/2007 dates..
>>>
>>> >>> how can i creates such stored procedure?
>>>
>>> >> Try something like:
>>>
>>> >> CREATE PROCEDURE dbo.MySearchProc
>>> >> @DateParameter datetime
>>> >> AS
>>> >> SELECT MyData
>>> >> FROM dbo.MyTable
>>> >> WHERE
>>> >> MyDateColumn >= @DateParameter AND
>>> >> MyDateColumn < DATEADD(day, 1, @DateParameter)
>>> >> GO
>>>
>>> >> EXEC dbo.MySearchProc @DateParameter = '20070822'
>>> >> GO
>>>
>>> >> --
>>> >> Hope this helps.
>>>
>>> >> Dan Guzman
>>> >> SQL Server MVP
>>>
>>> >> "Jassim Rahma" <jra...@hotmail.com> wrote in message