Inserting a Decimal - HELP

Im trying to insert a decimal into SQL 2000 using a stored procedure. I can successfully insert it using a direct T-SQL statement inside the code, but I cant insert it correctly when calling for a stored procedure.

For instance 12.5 will insert as 12.

Here is my SQL Parameter code:
[code]
MyCommand.Parameters.Add(New SqlParameter("@.Num", SqlDbType.Decimal))
MyCommand.Parameters("@.Num").Precision = 5
MyCommand.Parameters("@.Num").Scale = 2
MyCommand.Parameters("@.Num").Value = Convert.ToDecimal(TextBox1.Text)
[/code]

I also declared @.Num to be a decimal in my Stored Procedure.

As you can see I give the Parameter more than enough detail about the data type as well as convert the textbox value to a decimal, but it still rounds the value to an integer like data.

Again using a direct T-SQL statement inside the code works OK, but the stored procedure will not.
ANyone got any ideas why this is happening.Try running Profiler and tracing the transaction. Then you can determine if the precision is being lost when .NET sends the command to SQL Server, or when SQL Server calls the stored procedure. Have you tried not being so specific? Can you post the source code for your stored procedure?

MyCommand.Parameters.Add("@.Num", Convert.ToDecimal(TextBox1.Text))

Inserting 1:M relationship data via One Stored Procedure

Hi,

Uses: SQL Server 2000, ASP.NET 1.1;

I've the following tables which has a 1:M relationship within them:

Contact(ContactID, LastName, FirstName, Address, Email, Fax)
ContactTelephone(ContactID, TelephoneNos)

I have a webform made with asp.net, and have given the user to add maximum of 3 telephone nos for a contact (Telephone Nos can be either Mobile or Land phones). So I've used Textbox's in the following way for the appropriate fields:

LastName,
FirstName,
Address,
Fax,
Email,
MobileNo,
PhoneNo1,
PhoneNo2,
PhoneNo3.

Once the submit button is pressed, I need to take all of this values and insert them in the tables via a Single Stored Procedure. I need to know could this be done and How?

Eagerly awaiting a response.

Thanks,

The best reference for this kind of thing when you truly have a 1:M relationship is Erland's web page: http://www.sommarskog.se/arrays-in-sql.html

But if you have a max of 3, then just write the proc with 3 parameters (something like):

create procedure contact$insert
(
@.LastName,
...
@.MobileNo,
@.PhoneNo1,
@.PhoneNo2,
@.PhoneNo3
)
--add your own error handling of course or add SET XACT_ABORT ON that
--will stop the tran on any error

begin tran

insert into contact (lastName, ..., MobileNo) --note, assuming contactId is an identity
values (@.lastName, ..., @.MobileNo)

declare @.newContactId int
set @.newContactId = scope_identity()

insert into contactTelephone
select @.newContactId, @.phoneNo1
where @.phoneNo1 is not null
union all
select @.newContactId, @.phoneNo2
where @.phoneNo2 is not null
union all
select @.newContactId, @.phoneNo3
where @.phoneNo3 is not null

commit tran

|||

Hi Louis,

Thanks for the Response, this cleared my mind and the problem. Thank you again!

sql

Insert...exec doesn't work properly

Hello
MSSQL 2000 EE + SP3 on Windows 2000 AS + SP4
I have certain procedure which can't be reproduced
using my common technique.
CREATE table #t (text nvarchar(4000))
insert into #t exec sp_helptext 'objectname'
SELECT * FROM #T
DROP TABLE #T
exec sp_helptext 'objectname'
Two output resultsets are DIFFERENT!
Row order in temporary table doesn't coincide with real
row order (two rows with numbers 119 and 120 inserted
into positions 68 and 69).
This is critical for me!
Does anyone know the cause of this or any workaround?
Thanks,
Serge ShakhovThere is no way to totally control how rows are stored in a table. The
order in which you insert them has little to do with how they are stored or
placed into the pages on disk. Even with a clustered index you are not
guaranteed for everything to be physically and logically in order. So if
you want to get them out of a table in a specific order then you need to
specify an ORDER BY clause.
--
Andrew J. Kelly
SQL Server MVP
"Serge Shakhov" <REMOVETHIS_ACETYLENE@.mail.ru> wrote in message
news:48dgmb.5o3.ln@.proxyserver.ctd.mmk.chel.su...
> Hello
> MSSQL 2000 EE + SP3 on Windows 2000 AS + SP4
> I have certain procedure which can't be reproduced
> using my common technique.
> CREATE table #t (text nvarchar(4000))
> insert into #t exec sp_helptext 'objectname'
> SELECT * FROM #T
> DROP TABLE #T
> exec sp_helptext 'objectname'
> Two output resultsets are DIFFERENT!
> Row order in temporary table doesn't coincide with real
> row order (two rows with numbers 119 and 120 inserted
> into positions 68 and 69).
> This is critical for me!
> Does anyone know the cause of this or any workaround?
> Thanks,
> Serge Shakhov
>|||Hello
> MSSQL 2000 EE + SP3 on Windows 2000 AS + SP4
> I have certain procedure which can't be reproduced
> using my common technique.
> CREATE table #t (text nvarchar(4000))
> insert into #t exec sp_helptext 'objectname'
> SELECT * FROM #T
> DROP TABLE #T
> exec sp_helptext 'objectname'
> Two output resultsets are DIFFERENT!
I still don't know the reason of this behavior
but now I know that changing nvarchar datatype
to varchar forces server to work properly.
This can be considered as workaround.
Serge Shakhovsql

insert, update issue - stored procedure workaround

any stored procedure guru's around ?

I'm going nuts around here.
ok basically I've create a multilangual website using global en localresources for the static parts and a DB for the dynamic part.
I'm using the PROFILE option in asp.net 2.0 to store the language preference of visitors. It's working perfectly.

but Now I have some problems trying to get the right inserts.

basically I have designed my db based on this article:
http://www.codeproject.com/aspnet/LocalizedSamplePart2.asp?print=true

more specifically:
http://www.codeproject.com/aspnet/LocalizedSamplePart2/normalizedSchema.gif

ok now let's take the example of Categorie, Categorie_Local, and Culture

I basically want to create an insert that will let me insert categories into my database with the 2 language:

eg.
in categorie I have ID's 1 & 2
in culture I have:
ID: 1
culture: en-US
ID 2
culture: fr-Be

now the insert should create into Categorie_Local:

cat_id culture_id name
1 1 a category
1 2 une categorie

and so on...

I think this thing is only do-able with a stored procedure because:

1. when creating a new categorie, a new ID has to be entered into Categorie table
2. into the Categorie_local I need 2 rows inserted with the 2 values for 2 different cultures...

any idea on how to do this right ?
I'm a newbie with ms sql and stored procedures :s

help would be very very appreciated!
thanks a lotOK I got it ;)

it's not the best procedure out there I guess since I'm statically assigning my culture_id's but in this case 2 language are more than enough ;)

1 CREATEPROCEDURE [dbo].[InsCategories]2-- Add the parameters for the stored procedure here3@.cat_naam_envarchar(200),4@.cat_naam_nlvarchar(200),5@.cat_dateDateTime6AS7SET NOCOUNT ON;8BEGIN TRAN AddCategory9DECLARE @.cat_idint10Insert into Categorie11(Cat_date)12VALUES (@.cat_date)1314SELECT15@.cat_id=@.@.Identity1617--static: culture_id=1 for dutch18--static: culture_id=2 for english19Insert Into Categorie_Local20(cat_id, culture_id, catnaam)21VALUES (@.cat_id,1,@.cat_naam_nl)2223Insert Into Categorie_Local24(cat_id, culture_id, catnaam)25VALUES (@.cat_id,2,@.cat_naam_en)2627COMMIT Tran AddCategory28

sql

INSERT works in SQL server 2003 but NOT in SQL server 2000

I am new to Infopath 2003, SQL server 2000 and SQL server 2003. I am calling up a stored procedure with 2 variables from jscript in infopath 2003 to run a stored procedure in SQL server 2003 to copy a record (@.RecipetoCopy) and insert it with a new name(@.RecipeNew). It works fine with SQL 2003 but it will not work in SQL 2000. Below is my stored procedure for both.

Code for 2005 work fine:
__________________________________________________ ________
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[CopyInsert]@.RecipetoCopy varchar(10), @.RecipeNew varchar(10)

AS

INSERT INTO [Epmar].[dbo].[Formulas]
([FormulaNumber],[Type1],[RawMat1],[Preset1],[Message1])

SELECT@.RecipeNew,Type1,RawMat1,Preset1,Message1
fromFormulas
whereFormulas.FormulaNumber = @.RecipetoCopy

SELECT * from Formulas
whereFormulas.FormulaNumber = @.RecipeNew
__________________________________________________ __________

Code for SQL 2000 does not work
__________________________________________________ __________
CREATE PROCEDURE CopyInsert @.RecipetoCopy varchar(10), @.RecipeNew varchar(10)

AS

INSERT Formulas
([FormulaNumber],[Type1],[RawMat1],[Preset1],[Message1])

SELECT@.RecipeNew,Type1,RawMat1,Preset1,Message1
fromFormulas
whereFormulas.FormulaNumber = @.RecipetoCopy

SELECT * from Formulas
whereFormulas.FormulaNumber = @.RecipeNew
GO
__________________________________________________ _______

Quote:

Originally Posted by MMCI

I am new to Infopath 2003, SQL server 2000 and SQL server 2003. I am calling up a stored procedure with 2 variables from jscript in infopath 2003 to run a stored procedure in SQL server 2003 to copy a record (@.RecipetoCopy) and insert it with a new name(@.RecipeNew). It works fine with SQL 2003 but it will not work in SQL 2000. Below is my stored procedure for both.

Code for 2005 work fine:
__________________________________________________ ________
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[CopyInsert]@.RecipetoCopy varchar(10), @.RecipeNew varchar(10)

AS

INSERT INTO [Epmar].[dbo].[Formulas]
([FormulaNumber],[Type1],[RawMat1],[Preset1],[Message1])

SELECT@.RecipeNew,Type1,RawMat1,Preset1,Message1
fromFormulas
whereFormulas.FormulaNumber = @.RecipetoCopy

SELECT * from Formulas
whereFormulas.FormulaNumber = @.RecipeNew
__________________________________________________ __________

Code for SQL 2000 does not work
__________________________________________________ __________
CREATE PROCEDURE CopyInsert @.RecipetoCopy varchar(10), @.RecipeNew varchar(10)

AS

INSERT Formulas
([FormulaNumber],[Type1],[RawMat1],[Preset1],[Message1])

SELECT@.RecipeNew,Type1,RawMat1,Preset1,Message1
fromFormulas
whereFormulas.FormulaNumber = @.RecipetoCopy

SELECT * from Formulas
whereFormulas.FormulaNumber = @.RecipeNew
GO
__________________________________________________ _______

You've missed out the INTO on your INSERT statement in SQL 2000. It should be:

INSERT INTO Formulas
([FormulaNumber],[Type1],[RawMat1],[Preset1],[Message1])|||SQL server 2003 ?

From where you got that ?|||

Quote:

Originally Posted by

You've missed out the INTO on your INSERT statement in SQL 2000. It should be:

INSERT INTO Formulas
([FormulaNumber],[Type1],[RawMat1],[Preset1],[Message1])

It still does not work with the INSERT INTO Formulas.|||SQL server 2005 not 2003

insert Values and selected Items

Can this be done?

I have a procedure where I have Values and Selected Table items that have to be inserted into a nother table that require them to be stored in the same record to the data correctly placed?

This is a simple example.If this is u r requirement its ok. Other than that plz reply to me.

insert into y select * from x

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

sql

Insert to temporary table causes EXCEPTION_ACCESS_VIOLATION

I have a SQL database running on MSDE 2000 SP3 which uses a stored procedure
to update user entries in a table. As part of the update process, we keep a
change log. Since it is possible that an update may be disallowed between
determining what changes have been requested and actually doing the update,
we put the change log entries into a temporary table and then insert those
entries into the actual table once the update has successfully completed.
This code works on other copies of this database running on other servers
without any problems, but on this server any insert into the temporary table
causes the following error:
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Connection Broken
The relevant portions of the code are:
ALTER PROCEDURE dbo.tslNarrSessionUpdate
@.NarrSessionID varchar(40),
@.Title varchar(50) = NULL,
@.SubsystemID int = NULL,
@.HullNumber int = NULL,
@.Site varchar(25) = NULL,
@.Type varchar(30) = NULL,
@.Classification varchar(20) = NULL,
@.Section varchar(20) = NULL,
@.TestID varchar(40) = NULL,
@.nitssFunct varchar(4) = 'TSL'
AS
...
/* Create a temporary Change Log information table. If the *
* update is successful, this data will be copied to the *
* TSL change log table. */
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
ID=OBJECT_ID('tempdb..#ChangeLogs') AND xtype = 'u')
DROP Table #ChangeLogs
Create Table #ChangeLogs (
[OldValue] Text NULL ,
[NewValue] Text NULL ,
[FieldLabel] varchar (50) NOT NULL -- The label for the data that
the user sees (from the form)
)
/* Dummy insert statement for testing */
INSERT INTO #ChangeLogs -- Error is thrown at this
statement!
(OldValue, NewValue, FieldLabel)
Values('Old Val', 'New Val', 'My Field')
SELECT * FROM #ChangeLogs
DELETE FROM #ChangeLogs
Can anyone tell me what is happening and how to fix it?
TIA
Ron L> /* Create a temporary Change Log information table. If the *
> * update is successful, this data will be copied to the *
> * TSL change log table. */
> IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
> ID=OBJECT_ID('tempdb..#ChangeLogs') AND xtype
=> 'u')
> DROP Table #ChangeLogs
Why would your #temp table already exist, at the beginning of the procedure?
Have you ever actually come across this? Why does your procedure not have a
DROP TABLE #ChangeLogs at the end?
In any case, rather than perform a query directly against
tempdb..sysobjects, how about:
IF OBJECT_ID('tempdb..#ChangeLogs') IS NOT NULL
DROP TABLE #ChangeLogs
Essentially, this does the same thing, but I believe the optimizer / query
engine might behave a little differently. Also, your check for xtype is
redundant. What other kind of object is going to be named #ChangeLogs and
stored in tempdb?
> Can anyone tell me what is happening and how to fix it?
I can't reproduce, on 8.00.760, 8.00.859, 8.00.926, or 8.00.936.
What version are you using (SELECT @.@.VERSION)?
--
http://www.aspfaq.com/
(Reverse address to reply.)|||Can you post the output of the following command, on this server?
SELECT @.@.VERSION
GO
I tried on SQL2K SP3 and it worked fine.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ron L" <rlounsbury@.bogusAddress.com> wrote in message
news:%23zyWcePZEHA.2520@.TK2MSFTNGP12.phx.gbl...
I have a SQL database running on MSDE 2000 SP3 which uses a stored procedure
to update user entries in a table. As part of the update process, we keep a
change log. Since it is possible that an update may be disallowed between
determining what changes have been requested and actually doing the update,
we put the change log entries into a temporary table and then insert those
entries into the actual table once the update has successfully completed.
This code works on other copies of this database running on other servers
without any problems, but on this server any insert into the temporary table
causes the following error:
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Connection Broken
The relevant portions of the code are:
ALTER PROCEDURE dbo.tslNarrSessionUpdate
@.NarrSessionID varchar(40),
@.Title varchar(50) = NULL,
@.SubsystemID int = NULL,
@.HullNumber int = NULL,
@.Site varchar(25) = NULL,
@.Type varchar(30) = NULL,
@.Classification varchar(20) = NULL,
@.Section varchar(20) = NULL,
@.TestID varchar(40) = NULL,
@.nitssFunct varchar(4) = 'TSL'
AS
...
/* Create a temporary Change Log information table. If the *
* update is successful, this data will be copied to the *
* TSL change log table. */
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
ID=OBJECT_ID('tempdb..#ChangeLogs') AND xtype ='u')
DROP Table #ChangeLogs
Create Table #ChangeLogs (
[OldValue] Text NULL ,
[NewValue] Text NULL ,
[FieldLabel] varchar (50) NOT NULL -- The label for the data that
the user sees (from the form)
)
/* Dummy insert statement for testing */
INSERT INTO #ChangeLogs -- Error is thrown at this
statement!
(OldValue, NewValue, FieldLabel)
Values('Old Val', 'New Val', 'My Field')
SELECT * FROM #ChangeLogs
DELETE FROM #ChangeLogs
Can anyone tell me what is happening and how to fix it?
TIA
Ron L|||Aaron
Thanks for the reply, I will answer your questions in line, but I am
afraid that you are concentrating on the wrong portion of the code. The
CREATE TABLE works OK, it is the INSERT that dies. SELECT @.@.Version returns
the following:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 4)
Running this script
SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(15)) AS 'Version',
CAST(SERVERPROPERTY('ProductLevel') AS VARCHAR(15)) AS 'Level',
CAST(SERVERPROPERTY('Edition') AS VARCHAR(30)) AS 'Edition',
CAST(SERVERPROPERTY('InstanceName') AS VARCHAR(25)) AS 'Instance Name',
CAST(SERVERPROPERTY('IsIntegratedSecurityOnly') AS VARCHAR(8)) AS
'IsIntegratedSecurityOnly'
Gives:
Version Level Edition Instance Name
IsIntegratedSecurityOnly
-- -- -- --
-- --
8.00.760 SP3 Desktop Engine NULL
1
Thanks,
Ron L
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23r%23QU1PZEHA.2260@.TK2MSFTNGP12.phx.gbl...
> > /* Create a temporary Change Log information table. If the *
> > * update is successful, this data will be copied to the *
> > * TSL change log table. */
> > IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
> > ID=OBJECT_ID('tempdb..#ChangeLogs') AND
xtype
> => > 'u')
> > DROP Table #ChangeLogs
> Why would your #temp table already exist, at the beginning of the
procedure?
Just a safety measure to be sure that I don't attempt to recreate an
existing table and get an error from it
> Have you ever actually come across this?
I believe that we have seen this in the development phase while running the
SP from Query Analyzer (which keeps the connection open) if the SP dies
before the DROP TABLE
>Why does your procedure not have a DROP TABLE #ChangeLogs at the end?
OOPS!
> In any case, rather than perform a query directly against
> tempdb..sysobjects, how about:
> IF OBJECT_ID('tempdb..#ChangeLogs') IS NOT NULL
> DROP TABLE #ChangeLogs
We simply copied code (that works) from the code you get when you script a
table in SQL
> Essentially, this does the same thing, but I believe the optimizer / query
> engine might behave a little differently. Also, your check for xtype is
> redundant. What other kind of object is going to be named #ChangeLogs and
> stored in tempdb?
> > Can anyone tell me what is happening and how to fix it?
> I can't reproduce, on 8.00.760, 8.00.859, 8.00.926, or 8.00.936.
> What version are you using (SELECT @.@.VERSION)?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>|||Narayana,
Thanks for the response. SELECT @.@.Version returns the following:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 4)
Running this script
SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(15)) AS 'Version',
CAST(SERVERPROPERTY('ProductLevel') AS VARCHAR(15)) AS 'Level',
CAST(SERVERPROPERTY('Edition') AS VARCHAR(30)) AS 'Edition',
CAST(SERVERPROPERTY('InstanceName') AS VARCHAR(25)) AS 'Instance Name',
CAST(SERVERPROPERTY('IsIntegratedSecurityOnly') AS VARCHAR(8)) AS
'IsIntegratedSecurityOnly'
Gives:
Version Level Edition Instance Name
IsIntegratedSecurityOnly
-- -- -- --
-- --
8.00.760 SP3 Desktop Engine NULL
1
Thanks,
Ron L
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uBR8V4PZEHA.2216@.TK2MSFTNGP10.phx.gbl...
> Can you post the output of the following command, on this server?
> SELECT @.@.VERSION
> GO
> I tried on SQL2K SP3 and it worked fine.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Ron L" <rlounsbury@.bogusAddress.com> wrote in message
> news:%23zyWcePZEHA.2520@.TK2MSFTNGP12.phx.gbl...
> I have a SQL database running on MSDE 2000 SP3 which uses a stored
procedure
> to update user entries in a table. As part of the update process, we keep
a
> change log. Since it is possible that an update may be disallowed between
> determining what changes have been requested and actually doing the
update,
> we put the change log entries into a temporary table and then insert those
> entries into the actual table once the update has successfully completed.
> This code works on other copies of this database running on other servers
> without any problems, but on this server any insert into the temporary
table
> causes the following error:
> ODBC: Msg 0, Level 19, State 1
> SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> Connection Broken
> The relevant portions of the code are:
> ALTER PROCEDURE dbo.tslNarrSessionUpdate
> @.NarrSessionID varchar(40),
> @.Title varchar(50) = NULL,
> @.SubsystemID int = NULL,
> @.HullNumber int = NULL,
> @.Site varchar(25) = NULL,
> @.Type varchar(30) = NULL,
> @.Classification varchar(20) = NULL,
> @.Section varchar(20) = NULL,
> @.TestID varchar(40) = NULL,
> @.nitssFunct varchar(4) = 'TSL'
> AS
> ...
> /* Create a temporary Change Log information table. If the *
> * update is successful, this data will be copied to the *
> * TSL change log table. */
> IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
> ID=OBJECT_ID('tempdb..#ChangeLogs') AND xtype
=> 'u')
> DROP Table #ChangeLogs
> Create Table #ChangeLogs (
> [OldValue] Text NULL ,
> [NewValue] Text NULL ,
> [FieldLabel] varchar (50) NOT NULL -- The label for the data
that
> the user sees (from the form)
> )
> /* Dummy insert statement for testing */
> INSERT INTO #ChangeLogs -- Error is thrown at this
> statement!
> (OldValue, NewValue, FieldLabel)
> Values('Old Val', 'New Val', 'My Field')
> SELECT * FROM #ChangeLogs
> DELETE FROM #ChangeLogs
>
> Can anyone tell me what is happening and how to fix it?
> TIA
> Ron L
>
>|||> afraid that you are concentrating on the wrong portion of the code. The
> CREATE TABLE works OK, it is the INSERT that dies.
I wasn't suggesting it to fix the problem with this procedure. I was
suggesting a better approach for all your procedures.
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 4)
MSDE! Ah, okay, this may be important information, but I'm not sure. I
don't have a 760 MSDE around to test. If you were on a similar edition, but
a lower version other than the ones I tested on, I would have suggested
upgrading. Unfortunately, in this case, I can only suggest that you open a
case with PSS, unless someone with MSDE (@. 760) can reproduce this
problem...
Aaron|||I called MS on this problem. They had me download and install the MS03-031
patch. This brings SQL to version 8.00.818. This has fixed the problem,
although I haven't yet done a broad check to verify that it doesn't cause
any other problems.
Ron L
"Ron L" <rlounsbury@.bogusAddress.com> wrote in message
news:%23zyWcePZEHA.2520@.TK2MSFTNGP12.phx.gbl...
> I have a SQL database running on MSDE 2000 SP3 which uses a stored
procedure
> to update user entries in a table. As part of the update process, we keep
a
> change log. Since it is possible that an update may be disallowed between
> determining what changes have been requested and actually doing the
update,
> we put the change log entries into a temporary table and then insert those
> entries into the actual table once the update has successfully completed.
> This code works on other copies of this database running on other servers
> without any problems, but on this server any insert into the temporary
table
> causes the following error:
> ODBC: Msg 0, Level 19, State 1
> SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> Connection Broken
> The relevant portions of the code are:
> ALTER PROCEDURE dbo.tslNarrSessionUpdate
> @.NarrSessionID varchar(40),
> @.Title varchar(50) = NULL,
> @.SubsystemID int = NULL,
> @.HullNumber int = NULL,
> @.Site varchar(25) = NULL,
> @.Type varchar(30) = NULL,
> @.Classification varchar(20) = NULL,
> @.Section varchar(20) = NULL,
> @.TestID varchar(40) = NULL,
> @.nitssFunct varchar(4) = 'TSL'
> AS
> ...
> /* Create a temporary Change Log information table. If the *
> * update is successful, this data will be copied to the *
> * TSL change log table. */
> IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE
> ID=OBJECT_ID('tempdb..#ChangeLogs') AND xtype
=> 'u')
> DROP Table #ChangeLogs
> Create Table #ChangeLogs (
> [OldValue] Text NULL ,
> [NewValue] Text NULL ,
> [FieldLabel] varchar (50) NOT NULL -- The label for the data
that
> the user sees (from the form)
> )
> /* Dummy insert statement for testing */
> INSERT INTO #ChangeLogs -- Error is thrown at this
> statement!
> (OldValue, NewValue, FieldLabel)
> Values('Old Val', 'New Val', 'My Field')
> SELECT * FROM #ChangeLogs
> DELETE FROM #ChangeLogs
>
> Can anyone tell me what is happening and how to fix it?
> TIA
> Ron L
>

Insert the result of extended stored procedure into a table

Hi,
Can you tell me how can i put the result of an extended
stored procedure into a table?
I've done this by creating the table and then inserting
the result as follows:
create table FreeVolumeSpace(
Drive char(1),
MB_Free int)
insert into FreeVolumeSpace exec master..xp_fixeddrives
but im curious to know how can i do it using the
select into statement.
Best regardsNot quite sure why you want to do it but you can use OPENROWSET to loopback
to your local server e.g.
select a.* into #space
FROM OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=(local);',
'SET FMTONLY OFF exec master.dbo.xp_fixeddrives') AS a
go
select * from #space
go
drop table #space
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1bee001c45229$986a8130$a601280a@.phx
.gbl...
> Hi,
> Can you tell me how can i put the result of an extended
> stored procedure into a table?
> I've done this by creating the table and then inserting
> the result as follows:
> create table FreeVolumeSpace(
> Drive char(1),
> MB_Free int)
> insert into FreeVolumeSpace exec master..xp_fixeddrives
> but im curious to know how can i do it using the
> select into statement.
> Best regards

Insert the result of extended stored procedure into a table

Hi,
Can you tell me how can i put the result of an extended
stored procedure into a table?
I've done this by creating the table and then inserting
the result as follows:
create table FreeVolumeSpace(
Drive char(1),
MB_Free int)
insert into FreeVolumeSpace exec master..xp_fixeddrives
but im curious to know how can i do it using the
select into statement.
Best regards
Not quite sure why you want to do it but you can use OPENROWSET to loopback
to your local server e.g.
select a.* into #space
FROM OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=(local);',
'SET FMTONLY OFF exec master.dbo.xp_fixeddrives') AS a
go
select * from #space
go
drop table #space
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1bee001c45229$986a8130$a601280a@.phx.gbl...
> Hi,
> Can you tell me how can i put the result of an extended
> stored procedure into a table?
> I've done this by creating the table and then inserting
> the result as follows:
> create table FreeVolumeSpace(
> Drive char(1),
> MB_Free int)
> insert into FreeVolumeSpace exec master..xp_fixeddrives
> but im curious to know how can i do it using the
> select into statement.
> Best regards

Insert the result of extended stored procedure into a table

Hi,
Can you tell me how can i put the result of an extended
stored procedure into a table?
I've done this by creating the table and then inserting
the result as follows:
create table FreeVolumeSpace(
Drive char(1),
MB_Free int)
insert into FreeVolumeSpace exec master..xp_fixeddrives
but im curious to know how can i do it using the
select into statement.
Best regardsNot quite sure why you want to do it but you can use OPENROWSET to loopback
to your local server e.g.
select a.* into #space
FROM OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=(local);',
'SET FMTONLY OFF exec master.dbo.xp_fixeddrives') AS a
go
select * from #space
go
drop table #space
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1bee001c45229$986a8130$a601280a@.phx.gbl...
> Hi,
> Can you tell me how can i put the result of an extended
> stored procedure into a table?
> I've done this by creating the table and then inserting
> the result as follows:
> create table FreeVolumeSpace(
> Drive char(1),
> MB_Free int)
> insert into FreeVolumeSpace exec master..xp_fixeddrives
> but im curious to know how can i do it using the
> select into statement.
> Best regards

INSERT the OUTPUT of update statement - a neat trick that doesn't work?

Here's the code
ALTER procedure [dbo].[BalanceUpdate]
As
declare @.DateX datetime
set @.DateX = CONVERT(varchar(10),dateadd(hour,6,getda
te()),120)
--INSERT INTO Payment (Summ, UserID, Reason, RelatedOrderID, DT)
UPDATE [User] SET Balance = Balance - Cost, PaidThru = DATEADD(month,
1, ISNULL(PaidThru, @.DateX))
OUTPUT PayPlan.Cost, inserted.ID, 'Monthly payment blah blah blah',
null, @.DateX
FROM [User]
INNER JOIN PayPlan ON Payplan.ID = PayplanID
WHERE (PaidThru <= @.DateX or PaidThru is null) AND (Cost = 0 OR
(Balance >= Cost)) and Confirmed = 1
You see the commented insert statement - in theory it should work, in
practice it says syntac error. I tried surrounding the update with
SELECT * FROM (...) tmp but it's still syntax error.
Is there any way to make this trick work? I don't want to write an
ugly cursor! Woops. Problem solved, nm|||Could you elaborate? Where was the syntax error?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Sergei Shelukhin" <realgeek@.gmail.com> wrote in message
news:1182716922.462969.87000@.k79g2000hse.googlegroups.com...
> Woops. Problem solved, nm
>

INSERT the OUTPUT of update statement - a neat trick that doesn't work?

Here's the code
ALTER procedure [dbo].[BalanceUpdate]
As
declare @.DateX datetime
set @.DateX = CONVERT(varchar(10),dateadd(hour,6,getdate()),120)
--INSERT INTO Payment (Summ, UserID, Reason, RelatedOrderID, DT)
UPDATE [User] SET Balance = Balance - Cost, PaidThru = DATEADD(month,
1, ISNULL(PaidThru, @.DateX))
OUTPUT PayPlan.Cost, inserted.ID, 'Monthly payment blah blah blah',
null, @.DateX
FROM [User]
INNER JOIN PayPlan ON Payplan.ID = PayplanID
WHERE (PaidThru <= @.DateX or PaidThru is null) AND (Cost = 0 OR
(Balance >= Cost)) and Confirmed = 1
You see the commented insert statement - in theory it should work, in
practice it says syntac error. I tried surrounding the update with
SELECT * FROM (...) tmp but it's still syntax error.
Is there any way to make this trick work? I don't want to write an
ugly cursor!
Woops. Problem solved, nm
|||Could you elaborate? Where was the syntax error?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Sergei Shelukhin" <realgeek@.gmail.com> wrote in message
news:1182716922.462969.87000@.k79g2000hse.googlegro ups.com...
> Woops. Problem solved, nm
>

Insert stored procedure with output parameter

Hello everyone.

I need a stored procedure that excecutes a INSERT sentence.
That's easy. Now, what I need is to return a the key value of the just inserted record.

Someone does know how to do this?

In you SP use:

return SCOPE_IDENTITY()

Then in C# code:

comm = new SqlCommand("InsertANewRequest", conn);

comm.CommandType = CommandType.StoredProcedure;

SqlParameter newReqNumber = new SqlParameter("@.RETURN_VALUE", SqlDbType.Int);

comm.Parameters.Add(newReqNumber);

newReqNumber.Direction = ParameterDirection.ReturnValue;

try

{

// Open the connection

conn.Open();

// Execute the command

comm.ExecuteNonQuery();

int newReq = Convert.ToInt32(newReqNumber.Value);

}

|||

Thanks a lot!

While you posted this I solved it out using SELECT @.@.Identity

Is there any diference with the solution you gave me?

|||

Quote from BOL:

SCOPE_IDENTITY, IDENT_CURRENT, and @.@.IDENTITY are similar functions because they return values that are inserted into identity columns.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT (Transact-SQL).

SCOPE_IDENTITY and @.@.IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @.@.IDENTITY is not limited to a specific scope.

|||

Konstantin Kosinsky wrote:

In you SP use:

return SCOPE_IDENTITY()

Then in C# code:

comm = new SqlCommand("InsertANewRequest", conn);

comm.CommandType = CommandType.StoredProcedure;

SqlParameter newReqNumber = new SqlParameter("@.RETURN_VALUE", SqlDbType.Int);

comm.Parameters.Add(newReqNumber);

newReqNumber.Direction = ParameterDirection.ReturnValue;

try

{

// Open the connection

conn.Open();

// Execute the command

comm.ExecuteNonQuery();

int newReq = Convert.ToInt32(newReqNumber.Value);

}

insert stored procedure with error check and transaction function

Hi, guys
I try to add some error check and transaction and rollback function on my insert stored procedure but I have an error "Error converting data type varchar to smalldatatime" if i don't use /*error check*/ code, everything went well and insert a row into contract table.
could you correct my code, if you know what is the problem?

thanks

My contract table DDL:
************************************************** ***

create table contract(
contractNum int identity(1,1) primary key,
contractDate smalldatetime not null,
tuition money not null,
studentId char(4) not null foreign key references student (studentId),
contactId int not null foreign key references contact (contactId)
);

My insert stored procedure is:
************************************************** *****

create proc sp_insert_new_contract
( @.contractDate [smalldatetime],
@.tuition [money],
@.studentId [char](4),
@.contactId [int])
as

if not exists (select studentid
from student
where studentid = @.studentId)
begin
print 'studentid is not a valid id'
return -1
end

if not exists (select contactId
from contact
where contactId = @.contactId)
begin
print 'contactid is not a valid id'
return -1
end
begin transaction

insert into contract
([contractDate],
[tuition],
[studentId],
[contactId])
values
(@.contractDate,
@.tuition,
@.studentId,
@.contactId)

/*Error Check */
if @.@.error !=0 or @.@.rowcount !=1
begin
rollback transaction
print Insert is failed
return -1
end
print New contract has been added

commit transaction
return 0
goI recreated your environment including tables, DRI, and stored procedure in question. This is how I call it which successfully executes:

exec sp_insert_new_contract
@.contractDate = '01/01/2004',
@.tuition = 3000,
@.studentId = 'ABCD',
@.contactId = 1

insert stored procedure result into temporary table ?

I'm trying to insert the results of a stored procedure call into a temporary table, which is not working. It does work if I use a non-temporary table. Can anyone tell me if this is supported or what I am doing wrong.

Here is an example:

-- DROP PROCEDURE testProc
CREATE PROCEDURE testProc AS
BEGIN
SELECT '1111' as col1, '2222' as col2
END

-- this call will fail with message Invalid object name '#tmpTable'.
INSERT INTO #tmpTable EXEC testProc

-- DROP TABLE testTable
CREATE TABLE testTable (col1 varchar(5), col2 varchar(5))

-- this call will succeed
INSERT INTO testTable EXEC testProchow about defining your temp table before inserting into it?|||I'd really prefer to not create a hard dependency on the exact columns returned from the procedure. For instance, if I add another column to the resultset of the procedure, then the 'INSERT INTO EXEC' call will fail unless the predefined table for it is also updated.
I'm basically making a passthough procedure which will call another procedure and return the results as XML. So the base procedure may be called directly or via the passthrough.|||It is considered poor programming practice to use "SELECT *", which is essentially what you are doing when you don't pre-define your table layout.

If you add a column to your stored procedure, then your statement SHOULD fail. That ensures that you review your code to catch any other problems that might arise from the schematic change, and that is what the "testing" phase of development is all about.|||I don't think you know enough about what I'm doing to make that statement blindman. I am making a procedure that is just a passthough call to another procedure. The base procedure that is being called is where all the work is done. The passthrough procedure only exists because some clients will be calling the procedure via HTTP and expecting XML results rather than calling the procedure 'directly' via ODBC or JDBC. If I need to make a change to the base procedure, I don't want to have to also make that change to the passthough version. That creates more dependencies and unnecessary maintenance.
Ideally, a better design might be to add a parameter to the base procedure to tell it whether the results should be returned as XML or not... But I need more restrictive control over the HTTP-XML version for security reasons and it would be cleaner to go the passthrough route.|||I don't think you know enough about what I'm doing to make that statement blindman. I am making a procedure that is just a passthough call to another procedure. The base procedure that is being called is where all the work is done. The passthrough procedure only exists because some clients will be calling the procedure via HTTP and expecting XML results rather than calling the procedure 'directly' via ODBC or JDBC.That's exactly what I thought you were doing. Good coding practice calls for enumerating your datasets.

Insert Stored Procedure Help

OK I have a stored procedure that inserts information into a database table. Here is what I have so far:

I think I have the proper syntax for inserting everything, but I am having problems with two colums. I have Active column which has the bit data type and a Notify column which is also a bit datatype. If I run the procedure as it stands it will insert all the information correctly, but I have to manually go in to change the bit columns. I tried using the set command, but it will give me a xyntax error implicating the "=" in the Active = 1 How can I set these in the stored procedure?

1SET ANSI_NULLSON2GO3SET QUOTED_IDENTIFIERON4GO5-- =============================================6-- Author:xxxxxxxx7-- Create date: 10/31/078-- Description:Insert information into Registration table9-- =============================================10ALTER PROCEDURE [dbo].[InsertRegistration]1112@.Name nvarchar(50),13@.StreetAddressnchar(20),14@.Citynchar(10),15@.Statenchar(10),16@.ZipCodetinyint,17@.PhoneNumbernchar(20),18@.DateOfBirthsmalldatetime,19@.EmailAddressnchar(20),20@.Gendernchar(10),21@.Notifybit2223AS24BEGIN25-- SET NOCOUNT ON added to prevent extra result sets from26-- interfering with SELECT statements.27SET NOCOUNT ON;2829INSERT INTO Registration3031(Name, StreetAddress, City, State, ZipCode, PhoneNumber, DateOfBirth, EmailAddress, Gender, Notify)3233VALUES3435(@.Name, @.StreetAddress, @.City, @.State, @.ZipCode, @.PhoneNumber, @.DateOfBirth, @.EmailAddress, @.Gender, @.Notify)3637--SET38--Active = 13940END41GO

Can u post the data with execute method

Thank u

Baba

|||

You cannot set "Active" = 1 in t-sql.

That is because "Active" is not a valid variable name. It should be @.Active and, of course, you would have to declare it before doing so.

That said, I suspect you are trying to set the Active column value to 1 in the record you are inserting.

If so, you need to add (assuming Active is the column name) Active to the list of column names in the insert statement.

In the corresponding slot in the list of values in the insert statement, place a 1.

|||

It didn't correct the problem, but let me make sure I am on the right track (different table here):

1set ANSI_NULLSON2set QUOTED_IDENTIFIERON3GO4-- =============================================5-- Author:xxxxx

6-- Create date: 10/21/077-- Description:Insert Users8-- =============================================9ALTER PROCEDURE [dbo].[InsertUser]101112@.FirstNamenvarchar(50),13@.LastNamenvarchar(50),14@.MiddleNamenvarchar(50),15@.Activebit16AS17BEGIN18SET NOCOUNT ON;1920INSERT INTO Users21(FirstName, LastName, MiddleName, Active)22VALUES23(@.FirstName, @.LastName, @.MiddleName, 1)24--SET25--Active =126END

I get this message when you try to insert a user:

Procedure or function 'InsertUser' expects parameter '@.Active', which was not supplied.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Procedure or function 'InsertUser' expects parameter '@.Active', which was not supplied.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Procedure or function 'InsertUser' expects parameter '@.Active', which was not supplied.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +921162 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800038 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1932 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +149 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +947 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +149 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135 System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +404 System.Web.UI.WebControls.SqlDataSourceView.ExecuteInsert(IDictionary values) +447 System.Web.UI.DataSourceView.Insert(IDictionary values, DataSourceViewOperationCallback callback) +72 System.Web.UI.WebControls.DetailsView.HandleInsert(String commandArg, Boolean causesValidation) +390 System.Web.UI.WebControls.DetailsView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +602 System.Web.UI.WebControls.DetailsView.OnBubbleEvent(Object source, EventArgs e) +95 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.DetailsViewRow.OnBubbleEvent(Object source, EventArgs e) +109 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +132 System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +177 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746

Am I on the right track?|||

Tharnid:

12 @.FirstNamenvarchar(50),
13 @.LastNamenvarchar(50),
14 @.MiddleNamenvarchar(50),
15 @.Activebit

Tharnid:

Procedure or function 'InsertUser' expects parameter '@.Active', which was not supplied.

You declared an input parameter @.Active, according to your SQL statement this should be 1 by default, so you should not declare it at all!

12 @.FirstNamenvarchar(50),
13 @.LastNamenvarchar(50),
14 @.MiddleNamenvarchar(50)
15

|||

try:

ALTER PROCEDURE [dbo].[InsertUser]
10
11
12@.FirstNamenvarchar(50),
13@.LastNamenvarchar(50),
14@.MiddleNamenvarchar(50),
15@.Activebit = 1
16AS

BEGIN

...

END 

|||

Procedure or function 'InsertUser' expects parameter '@.Active', which was not supplied.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Procedure or function 'InsertUser' expects parameter '@.Active', which was not supplied.

Simply is telling you that you are not supplying the code with the value for the Active Parameter which in your case is an INPUT parameter and not an OUTPUT parameter. If you need it to be OUTPUT, I believe you have to indicate that in your procedure.

|||

Yes, you can give @.Active a default value as Spark is suggesting, but why would you do that? In the SQL statement active is hardcoded to be 1. So if you do supply a value (0 or 1) for it, the procedure will ignore it!

And yes, you can make it an output parameter as Dollarjunkie wrote, but then the return statement should be

SET @.active = 1

But why would you want an output parameter that will always return 1?

In this case, to skip the parameter is the logical solution, although the other 2 approaches will also work...

|||

I appreciate the respone :-) I will try them as soon as I can, indicate who gave the correct answer, and check answered

Insert stored procedure for related tables

I have two sets of related tables: Quote - QuoteDetail and Order - OrderItem

I need to copy Quote - QuoteDetail records to Order - OrderItem tables

I have the stored procedure up to this point: Insert a Quote in the Order table and get the new Order @.@.Identity.

I need to insert the QuoteDetail records into the OrderItem table using the new OrderID

Thank you for your help.Thanks to all who looked!

I figured it out. I was trying to make it harder than it actually was.|||Hope this helps !

CREATE PROCEDURE [InsertTest]
AS
INSERT INTO tblPerson(Login,Password,Email,DateCreated)
VALUES('jaja','aaa','22@.yahoo.com','02-03-04')
INSERT INTO tblSnippet(CategoryID,PersonID,Title,Description,DateCreated)
VALUES(1,@.@.IDENTITY,'HAHA','This is good article',GETDATE());
GO

|||can u post the solution :)|||Here is the code you requested. I was having a mental block over the Select versus the VAULES() to put the @.OrderID into the new Item records. The simple solution is setting the@.OrderID AS OrderID in the SELECT statement.

CREATE PROCEDURE dbo.ECPO_Quote_Convert
(
@.QuoteIDint,
@.OrderIDint output
)
AS
INSERT INTO ECP_Order
(
UserID, ...
(rest of the fields)
 )
SELECT
UserID, ...
(rest of the fields)
FROM
ECP_Quote
WHERE
QuoteID = @.QuoteID
SELECT @.OrderID = @.@.Identity
-- Insert QuoteDetail
INSERT INTO ECP_OrderItem
(
OrderID, ...
(rest of the fields)
 )
SELECT
@.OrderID AS OrderID, ...
(rest of the fields)
FROM
ECP_QuoteDetail
WHERE
QuoteID = @.QuoteID AND Quantity > 0

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

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

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