Sql Server SP does not work in code

J

Joel Finkel

Folks,

I have a Sql Server SP that takes several values and stores them as a new row in a single table. It works just fine within the Sql Server Query Analyzer and within Visual Studio.NET's Server Explorer. I used Visual Studio.NET to create the SqlCommand, set it to a Stored Procedure, and let it create the Parameters, which an examination of the code shows that it did correctly.

A WebForm is used to gather the data, and a button is used to trigger the execution of the code that sets the values of the Parameters and execute the SqlCommand. No exception is thrown, but the SqlCommand returns -1, and no row is stored in the database. Using the debugger, I have confirmed that the Parameters are, in fact, being set correctly before the SqlCommand is executed.

I am working in a client/server environment, and debugging is a bit weird. I have to attach to the worker process on the server in order to debug the code. I have not been able to set up Sql Server to allow debugging (although I have tried).

I wonder if there are any suggestions as to what may be going wrong, or what I can do to further debug the problem. Here is the pertinent code:

Thank you in advance.

Joel Finkel
(e-mail address removed)

int num;
SqlTransaction myTrans;

sqlCommand_insert.Parameters["@ID"].Value = memberID; // will be coerced to int4
sqlCommand_insert.Parameters["@RECEIVED_DATE"].Value = NewPaymentDate.SelectedDate.ToString();
sqlCommand_insert.Parameters["@RECEIVED_AMOUNT"].Value = NewPaymentAmt.Text;
sqlCommand_insert.Parameters["@info"].Value = NewPaymentInfo.Value;

if (sqlConnection1.State.ToString() != "Open")
{
sqlConnection1.Open();
}

try
{
myTrans = sqlConnection1.BeginTransaction();
sqlCommand_insert.Connection = sqlConnection1;
sqlCommand_insert.Transaction = myTrans;

num = sqlCommand_insert.ExecuteNonQuery();

if (num == 1)
{
myTrans.Commit();
}
else
{
myTrans.Rollback();
}

Server.Transfer("WebForm1.aspx");

}
catch (SqlException ex)
{
Label1.Text = ex.ToString();
}
 
W

William Ryan

Joel, If you had the proc it might be a little easier...but I do see one thing...

You are rolling the transaction back anytime records affected isn't one, so if two or some other number is returned you'll roll back the transaction. If you raise an exception, you don't throw an exception. I'd move the rollback down to the exception handler, and commit regardless of the return value. <Also, on general principle, I'd wrap the open in a Try Catch b/c if you don't get a clean open, no need contining with the rest and you'll get an unhandled exception unless you have a handler outside this code. Open is always potentially risky).

Give this a try and see if it doesn't fix it.

Cheers,

Bill
Folks,

I have a Sql Server SP that takes several values and stores them as a new row in a single table. It works just fine within the Sql Server Query Analyzer and within Visual Studio.NET's Server Explorer. I used Visual Studio.NET to create the SqlCommand, set it to a Stored Procedure, and let it create the Parameters, which an examination of the code shows that it did correctly.

A WebForm is used to gather the data, and a button is used to trigger the execution of the code that sets the values of the Parameters and execute the SqlCommand. No exception is thrown, but the SqlCommand returns -1, and no row is stored in the database. Using the debugger, I have confirmed that the Parameters are, in fact, being set correctly before the SqlCommand is executed.

I am working in a client/server environment, and debugging is a bit weird. I have to attach to the worker process on the server in order to debug the code. I have not been able to set up Sql Server to allow debugging (although I have tried).

I wonder if there are any suggestions as to what may be going wrong, or what I can do to further debug the problem. Here is the pertinent code:

Thank you in advance.

Joel Finkel
(e-mail address removed)

int num;
SqlTransaction myTrans;

sqlCommand_insert.Parameters["@ID"].Value = memberID; // will be coerced to int4
sqlCommand_insert.Parameters["@RECEIVED_DATE"].Value = NewPaymentDate.SelectedDate.ToString();
sqlCommand_insert.Parameters["@RECEIVED_AMOUNT"].Value = NewPaymentAmt.Text;
sqlCommand_insert.Parameters["@info"].Value = NewPaymentInfo.Value;

if (sqlConnection1.State.ToString() != "Open")
{
sqlConnection1.Open();
}

try
{
myTrans = sqlConnection1.BeginTransaction();
sqlCommand_insert.Connection = sqlConnection1;
sqlCommand_insert.Transaction = myTrans;

num = sqlCommand_insert.ExecuteNonQuery();

if (num == 1)
{
myTrans.Commit();
}
else
{
myTrans.Rollback();
}

Server.Transfer("WebForm1.aspx");

}
catch (SqlException ex)
{
Label1.Text = ex.ToString();
}
 
J

Joel Finkel

Bill,

Thank you for your suggestions. I have tested the code without exception handling so that it would invoke that last chance exception handler. No exception is thrown. I have modified the code as follows. No code in the catch clause is never executed, indicating that an exception is never thrown.


try
{
sqlConnection1.Open();
myTrans = sqlConnection1.BeginTransaction();
sqlCommand_insert.Connection = sqlConnection1;
sqlCommand_insert.Transaction = myTrans;

num = sqlCommand_insert.ExecuteNonQuery();

myTrans.Commit();
Server.Transfer("WebForm1.aspx");
}
catch (SqlException ex)
{
myTrans.Rollback();
Label1.Text = ex.ToString();
}

Even though no exception is thrown, no data are stored in the databse. Here is a copy of the SP. Again, this works fine from Sql Server Query Analyser and from within Visual Studio.Net Server Explorer. In both cases, legal values are stored and illegal values are trapped correctly.

Thanks again for any suggestions.

/Joel Finkel
(e-mail address removed)

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

CREATE PROCEDURE INSERT_MEMBER_PAYMENT
( @ID int,
@RECEIVED_DATE varchar(10),
@RECEIVED_AMOUNT varchar(10),
@PAYMENT_TYPE varchar(80),
@info varchar(100)
)

AS BEGIN

DECLARE @LID AS int
DECLARE @LDT AS smalldatetime

IF ISDATE(@RECEIVED_DATE) = 0 BEGIN
RETURN 0
END

SET @LID = (SELECT TOP 1 ID FROM dbo.MEMBERS WHERE ID = @ID)
IF @LID = '' BEGIN
RETURN 0
END

IF @RECEIVED_DATE = ''
SET @LDT = NULL
ELSE
SET @LDT = CONVERT(smalldatetime,@RECEIVED_DATE)

INSERT INTO dbo.FINANCES
(ID,
PAYMENT_ON,
AMOUNT,
PAYMENT_TYPE,
INFO,
TY_SENT,
MODIFIED_ON)
VALUES
(@ID,
@LDT,
CONVERT(smallmoney,@RECEIVED_AMOUNT),
@PAYMENT_TYPE,
@info,
'N',
GETDATE())

IF (@@ERROR <> 0)
RETURN @@ERROR
ELSE
RETURN 1

END

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=




Joel, If you had the proc it might be a little easier...but I do see one thing...

You are rolling the transaction back anytime records affected isn't one, so if two or some other number is returned you'll roll back the transaction. If you raise an exception, you don't throw an exception. I'd move the rollback down to the exception handler, and commit regardless of the return value. <Also, on general principle, I'd wrap the open in a Try Catch b/c if you don't get a clean open, no need contining with the rest and you'll get an unhandled exception unless you have a handler outside this code. Open is always potentially risky).

Give this a try and see if it doesn't fix it.

Cheers,

Bill
Folks,

I have a Sql Server SP that takes several values and stores them as a new row in a single table. It works just fine within the Sql Server Query Analyzer and within Visual Studio.NET's Server Explorer. I used Visual Studio.NET to create the SqlCommand, set it to a Stored Procedure, and let it create the Parameters, which an examination of the code shows that it did correctly.

A WebForm is used to gather the data, and a button is used to trigger the execution of the code that sets the values of the Parameters and execute the SqlCommand. No exception is thrown, but the SqlCommand returns -1, and no row is stored in the database. Using the debugger, I have confirmed that the Parameters are, in fact, being set correctly before the SqlCommand is executed.

I am working in a client/server environment, and debugging is a bit weird. I have to attach to the worker process on the server in order to debug the code. I have not been able to set up Sql Server to allow debugging (although I have tried).

I wonder if there are any suggestions as to what may be going wrong, or what I can do to further debug the problem. Here is the pertinent code:

Thank you in advance.

Joel Finkel
(e-mail address removed)

int num;
SqlTransaction myTrans;

sqlCommand_insert.Parameters["@ID"].Value = memberID; // will be coerced to int4
sqlCommand_insert.Parameters["@RECEIVED_DATE"].Value = NewPaymentDate.SelectedDate.ToString();
sqlCommand_insert.Parameters["@RECEIVED_AMOUNT"].Value = NewPaymentAmt.Text;
sqlCommand_insert.Parameters["@info"].Value = NewPaymentInfo.Value;

if (sqlConnection1.State.ToString() != "Open")
{
sqlConnection1.Open();
}

try
{
myTrans = sqlConnection1.BeginTransaction();
sqlCommand_insert.Connection = sqlConnection1;
sqlCommand_insert.Transaction = myTrans;

num = sqlCommand_insert.ExecuteNonQuery();

if (num == 1)
{
myTrans.Commit();
}
else
{
myTrans.Rollback();
}

Server.Transfer("WebForm1.aspx");

}
catch (SqlException ex)
{
Label1.Text = ex.ToString();
}
 
J

Joel Finkel

Folks,

I figured out the problem. The date field input paremeter in the SP was defined as varchar(10), which was too small. It needed to be varchar(22), which is the maximum length returned by the calendar component I am using that allows the user to enter the date.

Thanks for your attention.

/Joel Finkel
(e-mail address removed)

Bill,

Thank you for your suggestions. I have tested the code without exception handling so that it would invoke that last chance exception handler. No exception is thrown. I have modified the code as follows. No code in the catch clause is never executed, indicating that an exception is never thrown.


try
{
sqlConnection1.Open();
myTrans = sqlConnection1.BeginTransaction();
sqlCommand_insert.Connection = sqlConnection1;
sqlCommand_insert.Transaction = myTrans;

num = sqlCommand_insert.ExecuteNonQuery();

myTrans.Commit();
Server.Transfer("WebForm1.aspx");
}
catch (SqlException ex)
{
myTrans.Rollback();
Label1.Text = ex.ToString();
}

Even though no exception is thrown, no data are stored in the databse. Here is a copy of the SP. Again, this works fine from Sql Server Query Analyser and from within Visual Studio.Net Server Explorer. In both cases, legal values are stored and illegal values are trapped correctly.

Thanks again for any suggestions.

/Joel Finkel
(e-mail address removed)

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

CREATE PROCEDURE INSERT_MEMBER_PAYMENT
( @ID int,
@RECEIVED_DATE varchar(10),
@RECEIVED_AMOUNT varchar(10),
@PAYMENT_TYPE varchar(80),
@info varchar(100)
)

AS BEGIN

DECLARE @LID AS int
DECLARE @LDT AS smalldatetime

IF ISDATE(@RECEIVED_DATE) = 0 BEGIN
RETURN 0
END

SET @LID = (SELECT TOP 1 ID FROM dbo.MEMBERS WHERE ID = @ID)
IF @LID = '' BEGIN
RETURN 0
END

IF @RECEIVED_DATE = ''
SET @LDT = NULL
ELSE
SET @LDT = CONVERT(smalldatetime,@RECEIVED_DATE)

INSERT INTO dbo.FINANCES
(ID,
PAYMENT_ON,
AMOUNT,
PAYMENT_TYPE,
INFO,
TY_SENT,
MODIFIED_ON)
VALUES
(@ID,
@LDT,
CONVERT(smallmoney,@RECEIVED_AMOUNT),
@PAYMENT_TYPE,
@info,
'N',
GETDATE())

IF (@@ERROR <> 0)
RETURN @@ERROR
ELSE
RETURN 1

END

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=




Joel, If you had the proc it might be a little easier...but I do see one thing...

You are rolling the transaction back anytime records affected isn't one, so if two or some other number is returned you'll roll back the transaction. If you raise an exception, you don't throw an exception. I'd move the rollback down to the exception handler, and commit regardless of the return value. <Also, on general principle, I'd wrap the open in a Try Catch b/c if you don't get a clean open, no need contining with the rest and you'll get an unhandled exception unless you have a handler outside this code. Open is always potentially risky).

Give this a try and see if it doesn't fix it.

Cheers,

Bill
Folks,

I have a Sql Server SP that takes several values and stores them as a new row in a single table. It works just fine within the Sql Server Query Analyzer and within Visual Studio.NET's Server Explorer. I used Visual Studio.NET to create the SqlCommand, set it to a Stored Procedure, and let it create the Parameters, which an examination of the code shows that it did correctly.

A WebForm is used to gather the data, and a button is used to trigger the execution of the code that sets the values of the Parameters and execute the SqlCommand. No exception is thrown, but the SqlCommand returns -1, and no row is stored in the database. Using the debugger, I have confirmed that the Parameters are, in fact, being set correctly before the SqlCommand is executed.

I am working in a client/server environment, and debugging is a bit weird. I have to attach to the worker process on the server in order to debug the code. I have not been able to set up Sql Server to allow debugging (although I have tried).

I wonder if there are any suggestions as to what may be going wrong, or what I can do to further debug the problem. Here is the pertinent code:

Thank you in advance.

Joel Finkel
(e-mail address removed)

int num;
SqlTransaction myTrans;

sqlCommand_insert.Parameters["@ID"].Value = memberID; // will be coerced to int4
sqlCommand_insert.Parameters["@RECEIVED_DATE"].Value = NewPaymentDate.SelectedDate.ToString();
sqlCommand_insert.Parameters["@RECEIVED_AMOUNT"].Value = NewPaymentAmt.Text;
sqlCommand_insert.Parameters["@info"].Value = NewPaymentInfo.Value;

if (sqlConnection1.State.ToString() != "Open")
{
sqlConnection1.Open();
}

try
{
myTrans = sqlConnection1.BeginTransaction();
sqlCommand_insert.Connection = sqlConnection1;
sqlCommand_insert.Transaction = myTrans;

num = sqlCommand_insert.ExecuteNonQuery();

if (num == 1)
{
myTrans.Commit();
}
else
{
myTrans.Rollback();
}

Server.Transfer("WebForm1.aspx");

}
catch (SqlException ex)
{
Label1.Text = ex.ToString();
}
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

System.Data.SqlClient.SqlException: SQL Server does not exist???? 6
Using Output Parameters from SQL Server Stored Procedure to ASP.NET SQLCommand 1
Saving to a DateTime field in Sql Server 2000 using ADO.Net 14
sp does not return right value but query analyzer does 5
Stored procedure not returning a value in @RETURN_VALUE 3
Passing output parameters to SQL Server stored procedures through an SqlCommand object's Parameters 8
Problems with my SQL Server connection string 2
SQL Server Error 8179: Could not find prepared statement with handle x 2

Top