Hello

I have a C# program that calls an SQL Server Stored Procedure. First I
pass DateTime.Now to the stored procedure and it returns -1 because all rows
in the table are old therefore it doesn't exist. However 5/3/2009 is an
existing old record in the table but even though I hard code the 5/3/2009 as
a value in the parameter the return value of -1 still is returned it should
not be -1 because this record does exist in the table. I pasted the C# Code
below that passes the parameter to the Stored procedure and beneath it I
pasted the stored procedure:

First is the stored procedure:
*****************************

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Alter PROCEDURE DateExists

@RegDate DateTime

AS
BEGIN

Declare @RetDate int
if not exists(select Date from tblRegistReport where Date = @RegDate)
Begin
set @RetDate = -100
return @RetDate
End

END
GO

Now below is the C# code that passes the date:

**********************************************
namespace recexist
{
public partial class Form1 : Form
{
DateTime RegDate;
//DateTime RetDate;
int RetDate ;
public Form1()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter();
DataTable tblRegistReport = new DataTable();
//RegDate = DateTime.Now;
RegDate = Convert.ToDateTime("5/3/2009");
RetDate = 0;

SqlConnection connectionString = new SqlConnection("Data
Source=SNYCBOECO0032;Initial
Catalog=VoterRegistration;Uid=sa;pwd=igetalife2");
connectionString.Open();

SqlCommand com = new SqlCommand();
com.Connection = connectionString;

com.CommandText = "DateExists";
com.CommandType = CommandType.StoredProcedure;

com.Parameters.Add("@RegDate", SqlDbType.DateTime);
com.Parameters["@RegDate"].Direction = ParameterDirection.Input;
com.Parameters["@RegDate"].Value = RegDate;

RetDate = com.ExecuteNonQuery();

MessageBox.Show("the number is " + " " + RetDate);
com.Parameters.Clear();

}
}
}

:

--
JB

Hi,
bcz the datetime field in SQL Server include the time part. maybe when you
insert data to the [Date] field, you used System.DateTime.Now or getdate()
function.
it made your Date field include the time part, like : "5/3/2009
03:45:33.009" , in this case, you can not use "=" to match the Date field.
you can using "datediff" function.

try this:
select [Date] from tblRegistReport where datediff(DAY,[Date] , @RegDate)=0



"JB" <(E-Mail Removed)> 写入消息
news:7E39E270-7D18-4E61-9C9C-(E-Mail Removed)...
> Hello
>
> I have a C# program that calls an SQL Server Stored Procedure. First I
> pass DateTime.Now to the stored procedure and it returns -1 because all
> rows
> in the table are old therefore it doesn't exist. However 5/3/2009 is an
> existing old record in the table but even though I hard code the 5/3/2009
> as
> a value in the parameter the return value of -1 still is returned it
> should
> not be -1 because this record does exist in the table. I pasted the C#
> Code
> below that passes the parameter to the Stored procedure and beneath it I
> pasted the stored procedure:
>
> First is the stored procedure:
> *****************************
>
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
>
> Alter PROCEDURE DateExists
>
> @RegDate DateTime
>
> AS
> BEGIN
>
> Declare @RetDate int
> if not exists(select Date from tblRegistReport where Date = @RegDate)
> Begin
> set @RetDate = -100
> return @RetDate
> End
>
> END
> GO
>
> Now below is the C# code that passes the date:
>
> **********************************************
> namespace recexist
> {
> public partial class Form1 : Form
> {
> DateTime RegDate;
> //DateTime RetDate;
> int RetDate ;
> public Form1()
> {
> InitializeComponent();
> }
>
> private void button1_Click(object sender, EventArgs e)
> {
> DataSet ds = new DataSet();
> SqlDataAdapter da = new SqlDataAdapter();
> DataTable tblRegistReport = new DataTable();
> //RegDate = DateTime.Now;
> RegDate = Convert.ToDateTime("5/3/2009");
> RetDate = 0;
>
> SqlConnection connectionString = new SqlConnection("Data
> Source=SNYCBOECO0032;Initial
> Catalog=VoterRegistration;Uid=sa;pwd=igetalife2");
> connectionString.Open();
>
> SqlCommand com = new SqlCommand();
> com.Connection = connectionString;
>
> com.CommandText = "DateExists";
> com.CommandType = CommandType.StoredProcedure;
>
> com.Parameters.Add("@RegDate", SqlDbType.DateTime);
> com.Parameters["@RegDate"].Direction =
> ParameterDirection.Input;
> com.Parameters["@RegDate"].Value = RegDate;
>
> RetDate = com.ExecuteNonQuery();
>
> MessageBox.Show("the number is " + " " + RetDate);
> com.Parameters.Clear();
>
> }
> }
> }
>
> :
>
> --
> JB

=?Utf-8?B?SkI=?= <(E-Mail Removed)> wrote in
news:7E39E270-7D18-4E61-9C9C-(E-Mail Removed):

> SNIPPED
>
> RetDate = com.ExecuteNonQuery();


ExecuteScalar, not ExecuteNonQuery.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

******************************************
| Think outside the box! |
******************************************

The problem here is that when you use RETURN to send back an integer (and
only an integer) from a stored procedure, it can only be captured with a
Parameter whose direction is set to Direction.ReturnValue as in (VB.NET)...

cmd.Parameters.Add("@ReturnValue", SqlDbType.Int).Direction =
ParameterDirection.ReturnValue

Yes, ExecuteNonQuery is correct. The scalar approach only works if you're
sending back a single value with a SELECT. Of course, you could take this
approach if you changed the SP to do a SELECT of the @RetDate value.
However, OUTPUT and RETURN Parameters are far faster to process.

Perhaps I need to translate my book to C#...

hth
--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________



"JB" <(E-Mail Removed)> wrote in message
news:7E39E270-7D18-4E61-9C9C-(E-Mail Removed)...
> Hello
>
> I have a C# program that calls an SQL Server Stored Procedure. First I
> pass DateTime.Now to the stored procedure and it returns -1 because all
> rows
> in the table are old therefore it doesn't exist. However 5/3/2009 is an
> existing old record in the table but even though I hard code the 5/3/2009
> as
> a value in the parameter the return value of -1 still is returned it
> should
> not be -1 because this record does exist in the table. I pasted the C#
> Code
> below that passes the parameter to the Stored procedure and beneath it I
> pasted the stored procedure:
>
> First is the stored procedure:
> *****************************
>
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
>
> Alter PROCEDURE DateExists
>
> @RegDate DateTime
>
> AS
> BEGIN
>
> Declare @RetDate int
> if not exists(select Date from tblRegistReport where Date = @RegDate)
> Begin
> set @RetDate = -100
> return @RetDate
> End
>
> END
> GO
>
> Now below is the C# code that passes the date:
>
> **********************************************
> namespace recexist
> {
> public partial class Form1 : Form
> {
> DateTime RegDate;
> //DateTime RetDate;
> int RetDate ;
> public Form1()
> {
> InitializeComponent();
> }
>
> private void button1_Click(object sender, EventArgs e)
> {
> DataSet ds = new DataSet();
> SqlDataAdapter da = new SqlDataAdapter();
> DataTable tblRegistReport = new DataTable();
> //RegDate = DateTime.Now;
> RegDate = Convert.ToDateTime("5/3/2009");
> RetDate = 0;
>
> SqlConnection connectionString = new SqlConnection("Data
> Source=SNYCBOECO0032;Initial
> Catalog=VoterRegistration;Uid=sa;pwd=igetalife2");
> connectionString.Open();
>
> SqlCommand com = new SqlCommand();
> com.Connection = connectionString;
>
> com.CommandText = "DateExists";
> com.CommandType = CommandType.StoredProcedure;
>
> com.Parameters.Add("@RegDate", SqlDbType.DateTime);
> com.Parameters["@RegDate"].Direction =
> ParameterDirection.Input;
> com.Parameters["@RegDate"].Value = RegDate;
>
> RetDate = com.ExecuteNonQuery();
>
> MessageBox.Show("the number is " + " " + RetDate);
> com.Parameters.Clear();
>
> }
> }
> }
>
> :
>
> --
> JB