insert stored procedure does not commit to the database

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 IMPLICIT_TRANSACTIONS OFF
INSERT EposTransaction
(
TransactionDate,
CustomerID,
TransactionTypeID,
UserID,
PaymentTypeID
)
VALUES
(
@.TransactionDate,
@.CustomerID,
@.TransactionTypeID,
@.UserID,
@.PaymentTypeID
)
SELECT SCOPE_IDENTITY() AS ID
*VB CODE
Dim conn As New SqlConnection()
conn.ConnectionString = "Data Source=.
\SQLEXPRESS;AttachDbFilename=|DataDirectory|\EposT ill.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
<philhey@.googlemail.com> wrote in message
news:1174340504.674994.304420@.e65g2000hsc.googlegr oups.com...
> 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?
>
Is there an existing transaction running in the connection? Perhaps a
System.Transactions.TransactionScope? Or some other bit of code running a
BEGIN TRANSACTION? You can check @.@.trancount in your procedure to see. If
>0 there is a transaction.
David
|||On 19 Mar, 21:52, "David Browne" <davidbaxterbrowne no potted
m...@.hotmail.com> wrote:

> Is there an existing transaction running in the connection? Perhaps a
> System.Transactions.TransactionScope? Or some other bit of code running a
> BEGIN TRANSACTION? You can check @.@.trancount in your procedure to see. If
> David
Hi David,
Thanks for the reply, I dont think so, I'm running this bit of code as
the first thing my application does in the Form Load Code. I changed
the last line of the SP to
SELECT @.@.trancount AS ID , and the message box returned a blank or
empty string, is this how I was supposed to check?
|||Phil,
With "SET IMPLICIT_TRANSACTIONS OFF" you need to somewhere explicitly commit
the transaction. Your VB code looks like you are closing you connection
without ever issuing a commit.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
<philhey@.googlemail.com> wrote in message
news:1174340504.674994.304420@.e65g2000hsc.googlegr oups.com...
> 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 IMPLICIT_TRANSACTIONS OFF
> INSERT EposTransaction
> (
> TransactionDate,
> CustomerID,
> TransactionTypeID,
> UserID,
> PaymentTypeID
> )
> VALUES
> (
> @.TransactionDate,
> @.CustomerID,
> @.TransactionTypeID,
> @.UserID,
> @.PaymentTypeID
> )
> SELECT SCOPE_IDENTITY() AS ID
>
> *VB CODE
> Dim conn As New SqlConnection()
> conn.ConnectionString = "Data Source=.
> \SQLEXPRESS;AttachDbFilename=|DataDirectory|\EposT ill.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
>
|||General Issues:
1) SET NOCOUNT ON should be first line of almost any sproc.
2) Use an OUTPUT variable for a single row, single value return from a
sproc.
Specific Question Issues:
3) Get rid of SET IMPLICIT_TRANSACTIONS OFF
4) Use BEGIN TRAN..DML..Check for Error..COMMIT/ROLLBACK TRAN methodology in
your sproc.
5) if the above doesn't work, it could be something funky with the MSGBOX
workings. Set a variable equal to the sproc return and then display that
variable in the msgbox.
TheSQLGuru
President
Indicium Resources, Inc.
<philhey@.googlemail.com> wrote in message
news:1174340504.674994.304420@.e65g2000hsc.googlegr oups.com...
> 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 IMPLICIT_TRANSACTIONS OFF
> INSERT EposTransaction
> (
> TransactionDate,
> CustomerID,
> TransactionTypeID,
> UserID,
> PaymentTypeID
> )
> VALUES
> (
> @.TransactionDate,
> @.CustomerID,
> @.TransactionTypeID,
> @.UserID,
> @.PaymentTypeID
> )
> SELECT SCOPE_IDENTITY() AS ID
>
> *VB CODE
> Dim conn As New SqlConnection()
> conn.ConnectionString = "Data Source=.
> \SQLEXPRESS;AttachDbFilename=|DataDirectory|\EposT ill.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
>
|||Ok, here is my SProc, and I have changed my VB code to display the
output parameter in the message box, but still no joy, it give me the
next ID number in the table, but when I check the table still nothing
there.
ALTER PROCEDURE MYSP_InsertEposTransaction
@.TransactionDate AS DATETIME, @.CustomerID AS Integer,
@.TransactionTypeID AS Integer, @.UserID AS Integer,
@.PaymentTypeID AS Integer , @.ID AS INTEGER OUTPUT
AS
SET NOCOUNT ON
BEGIN TRAN
INSERT EposTransaction
(
TransactionDate,
CustomerID,
TransactionTypeID,
UserID,
PaymentTypeID
)
VALUES
(
@.TransactionDate,
@.CustomerID,
@.TransactionTypeID,
@.UserID,
@.PaymentTypeID
)
SET @.ID = SCOPE_IDENTITY()
COMMIT TRAN
|||On 20 Mar, 08:50, "Steen Schlter Persson (DK)"
<steen@.REMOVE_THIS_asavaenget.dk> wrote:
> phil...@.googlemail.com wrote:
>
>
> I know it's a very stupid question, but are you sure that you look for
> the new record in the correct table?
> What if you extend your code to do a SELECT for the record with the
> actual @.ID? If that gives you a record, it should be in the database.
> --
> Regards
> Steen Schlter Persson
> Database Administrator / System Administrator- Hide quoted text -
> - Show quoted text -
Hi Steen,
thanks for replying, yes I did consider that, and will try it when I
get home tonight, however I'm sure that I'm on the right database
because I always get the ID no 10, every time I run it, and in the
table that I check the last ID number was 9.
I'm starting to think maybe its some sort of permissions problem, but
I dont really know much about security in SQL 2005. The fact the the
procedure works fine when I try it in the IDE is the bit that is
really confusing me.
|||
> Maybe I'm misunderstanding what you are saying, but if you get the same
> ID every time it looks like it's not inserting anything? If the insert
> is succesfully, the @.ID should be incremented with the IDENTITY
> increment value. If the ID stays the same everytime you run the proc is
> looks like it's not inserting anything.
Hi Steen,
Yes that is the problem, despite my stored procedure returning the
next ID number like the data has been entered, when I check the table
nothing new has actually been added.
Does the proc look like it is correct? If so then I think maybe I will
try a VB.Net newsgroup and see if there is a problem with my code, but
it all looks ok to me.
Phil
|||Do you check for execution errors on return?
Also, just to be safe, I consider it good practice to wrap the entire
SP in a BEGIN/END pair. I don't recall if that's an issue when doing
this stuff thru VB, but it could be.
J.
On 19 Mar 2007 14:41:44 -0700, philhey@.googlemail.com wrote:

>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
BEGIN

>SET IMPLICIT_TRANSACTIONS OFF
>INSERT EposTransaction
>(
>TransactionDate,
>CustomerID,
>TransactionTypeID,
>UserID,
>PaymentTypeID
>)
>VALUES
>(
>@.TransactionDate,
>@.CustomerID,
>@.TransactionTypeID,
>@.UserID,
>@.PaymentTypeID
>)
>SELECT SCOPE_IDENTITY() AS ID
END

>
>*VB CODE
>Dim conn As New SqlConnection()
> conn.ConnectionString = "Data Source=.
>\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Epos Till.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