C# passes parameter but return value always -1

  • Thread starter Thread starter JB
  • Start date Start date
J

JB

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();

}
}
}

:
 
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
 
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 said:
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();

}
}
}

:
Click to expand...
 
Back
Top