DateTime type in Sql server

[bexoan211c]

Hi, I have a problem with DateTime type in ADO.NET & Sql Server
I have the following stored procedure and I executed it successfully in Query Analysis of Sql server. However, if i use it in ADO.NET, it didn't return me any results and it didn't throw any errors.

This is my stored procedure:
CREATE PROCEDURE dbo.GetMessages
(
@RoomName VARCHAR (100),
@TimeFrom DATETIME,
@TimeTo DATETIME
)
AS
SET NOCOUNT ON

SELECT msg.Content, msg.TimeSent
FROM Message msg INNER JOIN Room r ON msg.RoomID = r.RoomID
WHERE r.RoomName = @RoomName
AND TimeSent >= @TimeFrom AND TimeSent <= @TimeTo


And this is my method in C#:

public DataTable GetMessages(string strRoomName, DateTime dtFrom, DateTime dtTo) {
SqlConnection connection = new SqlConnection(dsn);

SqlCommand command = new SqlCommand("GetMessages", connection);
command.CommandType = CommandType.StoredProcedure;

SqlParameter paramRoomName = new SqlParameter("@RoomName", strRoomName);
command.Parameters.Add(paramRoomName);

SqlParameter paramTimeFrom = new SqlParameter("@TimeFrom", SqlDbType.DateTime);
paramTimeFrom.Value = dtFrom;
paramTimeFrom.Direction = ParameterDirection.Input;
command.Parameters.Add(paramTimeFrom);

SqlParameter paramTimeTo = new SqlParameter("@TimeTo", SqlDbType.DateTime);
paramTimeTo.Direction = ParameterDirection.Input;
paramTimeTo.Value = dtTo;
command.Parameters.Add(paramTimeTo);

SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = command;
DataTable tblMessages = new DataTable();

adapter.Fill(tblMessages);
connection.Close();
return tblMessages;
}

I think there is a difference between DateTime type of C# and Sql Server, but i don't know how to solve it. Please help me!!! Thanks in advance.

[Krzemo]

I ques that this is a problem:

SqlParameter paramRoomName = new SqlParameter("@RoomName", strRoomName,100);


??????


If it fails:

First U must locate the problem....
Change SQL procedure to (for example):

 
CREATE PROCEDURE dbo.GetMessages
(
@RoomName VARCHAR (100),
@TimeFrom DATETIME,
@TimeTo DATETIME
)
AS
SET NOCOUNT ON

SELECT TOP 10 msg.Content, msg.TimeSent
FROM Message msg INNER JOIN Room r ON msg.RoomID = r.RoomID
-- WHERE r.RoomName = @RoomName 
--AND TimeSent >= @TimeFrom AND TimeSent <= @TimeTo

and see if tblMessages is filled with data

If it is than try: WHERE r.RoomName = @RoomName
etc
U can also change number of parameters in SP and in code just to find which parameter generates the problem

When U finally isolate the problem, U usualy find it easy to solve.

From my point of view DateTime parameters works perfect in MsSQL.
I hav'nt any problems with them.

Best regards,
Krzemo.

[bexoan211c]

hi, I also try it but i can't find any errors. It is always successful without the comparation of DateTime type.

[Krzemo]

Your problem is not located in code U supply!

I 've changed 1 linie in code:

 
SqlConnection connection = new SqlConnection("Data Source=127.0.0.1;User ID=sa;Password=MyPassword;Initial Catalog=Northwind;");

And executed (attached with this message) SQL script against Northwind database.

Than I made call to your function like this:

 
DateTime dtFrom=new DateTime(1996,7,19,0,0,0),dtTo=new DateTime(2000,1,1,0,0,0);
 
DataTable dt=GetMessages("FOLKO",dtFrom,dtTo);
 
MessageBox.Show(dt.Rows.Count.ToString());
 

And it shows 19 records (the same as from SQL directly)!

Be sure that U supply valid data in your parameters!

Best regards
Krzemo.