Sql Server SP does not work in code

  • Thread starter Thread starter Joel Finkel
  • Start date Start date
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();
}
 
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();
}
 
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();
}
 
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();
}
 
Back
Top