Hi Dave,
the procedure I posted was noly an example. Here is the real procedure,
which I would like to use in my application. The sense is to update a
productdatabase with a new price. If an
productgroup/productfamily/productcategorie is existing, so the ID is taken.
If not it will be added to the table....
Whats realy funny - select commands works, only update and insert not.
ALTER PROCEDURE dbo.SpeichereArtikel
@ID int OUTPUT,
@Artikelnummer numeric(18,0),
@Herstellerartikelnummer varchar(50),
@Hersteller varchar(50),
@EAN varchar(50),
@Bezeichnung varchar(150),
@Bestand int,
@Preis money,
@Listenpreis money,
@Produktfamilie varchar(50),
@Produktkategorie varchar(50),
@Produktgruppe varchar(50),
@EOL bit,
@Gewicht decimal(18,4)
AS
declare @ProduktfamilieID as numeric(18,0)
declare @ProduktkategorieID as numeric(18,0)
declare @ProduktgruppeID as numeric(18,0)
declare @HerstellerID as numeric(18,0)
/* Produktfamilie einfügen */
if (select count(*) from UserProduktfamilie where
Bezeichnung=@Produktfamilie) = 0
begin
insert into UserProduktfamilie (Bezeichnung) values (@Produktfamilie)
end
SET @ProduktfamilieID = (select ID from UserProduktfamilie where
Bezeichnung=@Produktfamilie)
/* Produktkategorie einfügen */
if (select count(*) from UserProduktkategorie where
Bezeichnung=@Produktkategorie) = 0
begin
insert into UserProduktkategorie (Bezeichnung) values (@Produktkategorie)
end
SET @ProduktkategorieID = (select ID from UserProduktkategorie where
Bezeichnung=@Produktkategorie)
/* Produktgruppe einfügen */
if (select count(*) from UserProduktgruppe where
Bezeichnung=@Produktgruppe) = 0
begin
insert into UserProduktgruppe (Bezeichnung) values (@Produktgruppe)
end
SET @ProduktgruppeID = (select ID from UserProduktgruppe where
Bezeichnung=@Produktgruppe)
/* Hersteller einfügen */
if (select count(*) from UserHersteller where Bezeichnung=@Hersteller) = 0
begin
insert into UserHersteller (Bezeichnung) values (@Hersteller)
end
SET @HerstellerID = (select ID from UserHersteller where
Bezeichnung=@Hersteller)
/* prüfen, ob Artikel bereits eingetragen */
if (select count(*) from UserProdukte where Artikelnummer = @Artikelnummer)
0
BEGIN
/* Artikel vorhanden -update */
update UserProdukte set
Herstellerartikelnummer = @Herstellerartikelnummer,
Hersteller = @HerstellerID,
EAN = @EAN,
Bezeichnung = @Bezeichnung,
Bestand = @Bestand,
Preis = @Preis,
Listenpreis = @Listenpreis,
Produktkategorie = @ProduktkategorieID,
Produktfamilie = @ProduktfamilieID,
Produktgruppe = @ProduktgruppeID,
EOL = @EOL,
Gewicht = @Gewicht
where Artikelnumer=@Artikelnummer
END
ELSE BEGIN
/* Artikel nicht vorhanden -insert */
insert into UserProdukte (Artikelnummer, Herstellerartikelnummer,
Hersteller, EAN, Bezeichnung,
Bestand, Preis, Listenpreis, Produktkategorie, Produktfamilie,
Produktgruppe, EOL, Gewicht)
values
(@Artikelnummer, @Herstellerartikelnummer, @HerstellerID, @EAN,
@Bezeichnung,
@Bestand, @Preis, @Listenpreis, @ProduktkategorieID, @ProduktfamilieID,
@ProduktgruppeID, @EOL, @Gewicht)
end
SET @ID = @@IDENTITY
RETURN @@ERROR
"Dave Sexton" wrote:
Hi Jacek,
Verify that the connection string used in the Data Connections window is the
exact same connection string that you are using in code.
The stored procedure code for proc1 that you posted doesn't use @@IDENTITY at
all. Are you retrieving the value of @@IDENTITY in a separate query or
connection? You might want to post the complete proc1 SQL if you haven't
already.
(You should really use the SCOPE_IDENTITY() function instead, unless you
understand the difference and require the value of @@IDENTITY. I doubt that
using SCOPE_IDENTITY() instead will help your current issue, however.)
--
Dave Sexton
"J. S. EDV" <JS***@discussions.microsoft.comwrote in message
news:CA**********************************@microsof t.com...
Hi Dave,
I have closed the view and opened it again and there is no entry. But when I
execute the prcedure from the Data Connections tree it works.
I can't extract the values programmatically, because they don't exists.
Every time I execute the procedure I get the same @@identity value. I am a
little bit confused now (-;
"Dave Sexton" wrote:
Hi Jacek,
It sounds like it's working but your not looking at the right place to
verify.
Make sure that you refresh the view if you have the table opened in Visual
Studio.
Can you programmatically extract the new values from the database?
--
Dave Sexton
"J. S. EDV" <JS***@discussions.microsoft.comwrote in message
news:DC**********************************@microsof t.com...
Hi Dave,
thanks for the very fast answer. I've forgotten to say, that I use the
Express Edition - I think there is no Profiler. But when I call a bigger
procedure with some more insert and update commands I get the message
that
XX
rows has been affekted. So I think the procedure has been executed,
because
the count of rows that has been affekted is similar to the count of
update
and insert-commands I use in the peocedure, but nothing happens.
As output parameter I have defined @@identity and the number I get is
also
the next one which normaly would been uses for the new row.
"Dave Sexton" wrote:
Hi Jacek,
Run Sql Profiler and see if the procedure is being called at all. Sql
Profile
can be launched from Enterprise Manager (Sql Server 2000) or Sql Server
Management Studio (Sql Server 2005), both from the Tools menu, IIRC.
--
Dave Sexton
"J. S. EDV" <JS***@discussions.microsoft.comwrote in message
news:86**********************************@microsof t.com...
Hello,
I have got a little problem with stored procedures and C#.
I have got a stored procedure which should only insert something in a
table.
For example:
ALTER PROCEDURE DBO.PROC1
AS
insert into dbo.Test values ('test')
when I execute this procedure in my Visual Studio Envirment with right
click
on it. It makes what it should The procedure inserts a row in my
table.
But if I trie to execute it from C#, it doesn't.
SqlCommand cmd = new SqlCommand("proc1",conn);
cmd.ExecuteNonQuery();
I get the message that 1 row has been affekted - but there is no entry
in
the table.
Does anyone has some ideas what I have done wrong?
Thanks!
Jacek
