Hi
I have a JSP/JDBC program that processes sql statements from other JSPs to an Oracle 8.16 database. I need to modify this program to retreive the data that was just inserted (from different tables, with different keys and different INSERT statement structures).
The best for me would be to perform a SELECT *... for the inserted record... but I just can't figure out how to retreive this record and it's becoming frustrating !!
I've been searching for a way to do this with PL/SQL 'INSERT... RETURNING...' but everything I found on the web isn't clear and i'm quite new to SQL and JDBC.
Could someone PLEASE clearly explain to me if it's possible and HOW... if not, is there any way I can ever achieve this without having to tear down the INSERT statement and build some sort of a SELECT statement out of it ??Hi,
the syntax is
insert into table (column1,column2,column3)
values(1,2,3)
returning column1,column2 into variable1,variable2
but it doesn't work with multitable-inserts and it is really slowly (on 9i)
the faster way is
select primary_key from sequence.nextval into variable...
then insert with this pk an reselect the values inserted...
good luck|||In general it shouldn't be necessary to SELECT to find out what you just inserted - you know what you just inserted! The only exceptions are values set by DEFAULT clauses or triggers.
It would be a good idea (good practice) to take the insert statements out of the JSP code and put them in PL/SQL packaged procedures. These procedures can then have OUT arguments to return the required data. However, I imagine that is a big change from where you are now.|||I see no problem with the performance of INSERT RETURNING on 9i database. It appears to perform rather better that SELECT then INSERT even WITHOUT further SELECT to retrieve row.
Frankly I'm disappointed when I see people making assertions of this kind without any evidence. There's enough Oracle misconceptions floating around without adding to the steaming pile.
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> CREATE TABLE table_name (column_name NUMBER (10));
Table created.
SQL> CREATE UNIQUE INDEX index_name ON table_name (column_name);
Index created.
SQL> CREATE SEQUENCE sequence_name INCREMENT BY 1 CACHE 10000;
Sequence created.
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 v_start_time INTEGER := 0;
3 v_column_name NUMBER (10);
4 v_sequence_no NUMBER (10);
5 v_iterations INTEGER := 10000;
6 BEGIN
7 DBMS_OUTPUT.PUT_LINE ('Case 1: SELECT then INSERT');
8 v_start_time := DBMS_UTILITY.GET_TIME;
9 FOR i IN 1..v_iterations LOOP
10 SELECT sequence_name.NEXTVAL
11 INTO v_sequence_no
12 FROM dual;
13 INSERT INTO table_name (column_name)
14 VALUES (v_sequence_no);
15 END LOOP;
16 DBMS_OUTPUT.PUT_LINE ('Hsecs: ' ||
17 (DBMS_UTILITY.GET_TIME - v_start_time));
18
19 DBMS_OUTPUT.PUT_LINE ('Case 2: INSERT RETURNING');
20 v_start_time := DBMS_UTILITY.GET_TIME;
21 FOR i IN 1..v_iterations LOOP
22 INSERT INTO table_name (column_name)
23 VALUES (sequence_name.NEXTVAL)
24 RETURNING column_name INTO v_column_name;
25 END LOOP;
26 DBMS_OUTPUT.PUT_LINE ('Hsecs: ' ||
27 (DBMS_UTILITY.GET_TIME - v_start_time));
28 END;
29 /
Case 1: SELECT then INSERT
Hsecs: 202
Case 2: INSERT RETURNING
Hsecs: 129
PL/SQL procedure successfully completed.
SQL> /
Case 1: SELECT then INSERT
Hsecs: 196
Case 2: INSERT RETURNING
Hsecs: 153
PL/SQL procedure successfully completed.
SQL> /
Case 1: SELECT then INSERT
Hsecs: 199
Case 2: INSERT RETURNING
Hsecs: 118
PL/SQL procedure successfully completed.
SQL>
Showing posts with label program. Show all posts
Showing posts with label program. Show all posts
Wednesday, March 28, 2012
Monday, March 19, 2012
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
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
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|\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<philhey@.googlemail.com> wrote in message
news:1174340504.674994.304420@.e65g2000hsc.googlegroups.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
> >0 there is a transaction.
> 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.googlegroups.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|\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
>|||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.googlegroups.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|\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
>|||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|||philhey@.googlemail.com wrote:
> 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
>
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 Schlüter Persson
Database Administrator / System Administrator|||On 20 Mar, 08:50, "Steen Schl=FCter Persson (DK)"
<steen@.REMOVE_THIS_asavaenget.dk> wrote:
> phil...@.googlemail.com wrote:
> > 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 =3D SCOPE_IDENTITY()
> > COMMIT TRAN
> 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 Schl=FCter 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.|||philhey@.googlemail.com wrote:
> On 20 Mar, 08:50, "Steen Schlüter Persson (DK)"
> <steen@.REMOVE_THIS_asavaenget.dk> wrote:
>> phil...@.googlemail.com wrote:
>> 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
>> 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 Schlüter 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.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||> 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|||philhey@.googlemail.com wrote:
>> 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
>
Hi Phil,
Since you've been able to successfully run the stored proc alone, I
assume that's ok. I'm not a programmer so unfortunately I'm not able to
tell if your VB code looks ok or not. It might be a good idea to let
somebody in a VB group look a the code - unless somebody else in here
can verify it's ok?
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||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|\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 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|\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<philhey@.googlemail.com> wrote in message
news:1174340504.674994.304420@.e65g2000hsc.googlegroups.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
> >0 there is a transaction.
> 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.googlegroups.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|\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
>|||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.googlegroups.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|\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
>|||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|||philhey@.googlemail.com wrote:
> 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
>
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 Schlüter Persson
Database Administrator / System Administrator|||On 20 Mar, 08:50, "Steen Schl=FCter Persson (DK)"
<steen@.REMOVE_THIS_asavaenget.dk> wrote:
> phil...@.googlemail.com wrote:
> > 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 =3D SCOPE_IDENTITY()
> > COMMIT TRAN
> 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 Schl=FCter 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.|||philhey@.googlemail.com wrote:
> On 20 Mar, 08:50, "Steen Schlüter Persson (DK)"
> <steen@.REMOVE_THIS_asavaenget.dk> wrote:
>> phil...@.googlemail.com wrote:
>> 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
>> 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 Schlüter 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.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||> 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|||philhey@.googlemail.com wrote:
>> 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
>
Hi Phil,
Since you've been able to successfully run the stored proc alone, I
assume that's ok. I'm not a programmer so unfortunately I'm not able to
tell if your VB code looks ok or not. It might be a good idea to let
somebody in a VB group look a the code - unless somebody else in here
can verify it's ok?
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||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|\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
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=|DataDirect
ory|\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<philhey@.googlemail.com> wrote in message
news:1174340504.674994.304420@.e65g2000hsc.googlegroups.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. I
f
> 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.googlegroups.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=|DataDirect
ory|\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
>|||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.googlegroups.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=|DataDirect
ory|\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
>|||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|||philhey@.googlemail.com wrote:
> 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
>
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|||On 20 Mar, 08:50, "Steen Schl=FCter 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 Schl=FCter 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.|||philhey@.googlemail.com wrote:
> On 20 Mar, 08:50, "Steen Schlter Persson (DK)"
> <steen@.REMOVE_THIS_asavaenget.dk> wrote:
> 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.
Regards
Steen Schlter Persson
Database Administrator / System Administrator|||
> 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
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=|DataDirect
ory|\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<philhey@.googlemail.com> wrote in message
news:1174340504.674994.304420@.e65g2000hsc.googlegroups.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. I
f
> 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.googlegroups.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=|DataDirect
ory|\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
>|||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.googlegroups.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=|DataDirect
ory|\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
>|||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|||philhey@.googlemail.com wrote:
> 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
>
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|||On 20 Mar, 08:50, "Steen Schl=FCter 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 Schl=FCter 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.|||philhey@.googlemail.com wrote:
> On 20 Mar, 08:50, "Steen Schlter Persson (DK)"
> <steen@.REMOVE_THIS_asavaenget.dk> wrote:
> 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.
Regards
Steen Schlter Persson
Database Administrator / System Administrator|||
> 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
Subscribe to:
Posts (Atom)