P
philhey
Hi,
I have a problem where an insert stored procedure does not commit to
the database from a vb.net program. I can run the stored procedure
fine through the IDE, but when I use the following vb code the
message
box shows the next ID number but when I check the database no new row
has been added. Any ideas?
Phil
*******STORED PROCEDURE
CREATE PROCEDURE MYSP_InsertEposTransaction
@TransactionDate AS DATETIME, @CustomerID AS Integer,
@TransactionTypeID AS Integer, @UserID AS Integer,
@PaymentTypeID AS Integer
AS
SET NOCOUNT ON
BEGIN TRAN
INSERT EposTransaction
(
TransactionDate,
CustomerID,
TransactionTypeID,
UserID,
PaymentTypeID
)
VALUES
(
@TransactionDate,
@CustomerID,
@TransactionTypeID,
@UserID,
@PaymentTypeID
)
RETURN SCOPE_IDENTITY()
COMMIT TRAN
*VB CODE
Dim conn As New SqlConnection()
conn.ConnectionString = "Data Source=.
\SQLEXPRESS;AttachDbFilename=|DataDirectory|\EposTill.mdf;Integrated
Security=True;User Instance=True"
Dim cmd As New SqlCommand()
cmd.Connection = conn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "MYSP_InsertEposTransaction"
' Create a SqlParameter for each parameter in the stored
procedure.
Dim transDateParam As New SqlParameter("@TransactionDate",
Now())
Dim customerIDParam As New SqlParameter("@CustomerID", 1)
Dim transactionTypeParam As New
SqlParameter("@TransactionTypeID", 1)
Dim userIDParam As New SqlParameter("@UserID", 1)
Dim paymentTypeParam As New SqlParameter("@PaymentTypeID", 1)
cmd.Parameters.Add(transDateParam)
cmd.Parameters.Add(customerIDParam)
cmd.Parameters.Add(transactionTypeParam)
cmd.Parameters.Add(userIDParam)
cmd.Parameters.Add(paymentTypeParam)
Dim previousConnectionState As ConnectionState = conn.State
Try
If conn.State = ConnectionState.Closed Then
conn.Open()
End If
MsgBox(cmd.ExecuteScalar)
Finally
If previousConnectionState = ConnectionState.Closed Then
conn.Close()
End If
End Try
I have a problem where an insert stored procedure does not commit to
the database from a vb.net program. I can run the stored procedure
fine through the IDE, but when I use the following vb code the
message
box shows the next ID number but when I check the database no new row
has been added. Any ideas?
Phil
*******STORED PROCEDURE
CREATE PROCEDURE MYSP_InsertEposTransaction
@TransactionDate AS DATETIME, @CustomerID AS Integer,
@TransactionTypeID AS Integer, @UserID AS Integer,
@PaymentTypeID AS Integer
AS
SET NOCOUNT ON
BEGIN TRAN
INSERT EposTransaction
(
TransactionDate,
CustomerID,
TransactionTypeID,
UserID,
PaymentTypeID
)
VALUES
(
@TransactionDate,
@CustomerID,
@TransactionTypeID,
@UserID,
@PaymentTypeID
)
RETURN SCOPE_IDENTITY()
COMMIT TRAN
*VB CODE
Dim conn As New SqlConnection()
conn.ConnectionString = "Data Source=.
\SQLEXPRESS;AttachDbFilename=|DataDirectory|\EposTill.mdf;Integrated
Security=True;User Instance=True"
Dim cmd As New SqlCommand()
cmd.Connection = conn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "MYSP_InsertEposTransaction"
' Create a SqlParameter for each parameter in the stored
procedure.
Dim transDateParam As New SqlParameter("@TransactionDate",
Now())
Dim customerIDParam As New SqlParameter("@CustomerID", 1)
Dim transactionTypeParam As New
SqlParameter("@TransactionTypeID", 1)
Dim userIDParam As New SqlParameter("@UserID", 1)
Dim paymentTypeParam As New SqlParameter("@PaymentTypeID", 1)
cmd.Parameters.Add(transDateParam)
cmd.Parameters.Add(customerIDParam)
cmd.Parameters.Add(transactionTypeParam)
cmd.Parameters.Add(userIDParam)
cmd.Parameters.Add(paymentTypeParam)
Dim previousConnectionState As ConnectionState = conn.State
Try
If conn.State = ConnectionState.Closed Then
conn.Open()
End If
MsgBox(cmd.ExecuteScalar)
Finally
If previousConnectionState = ConnectionState.Closed Then
conn.Close()
End If
End Try