471,594 Members | 2,068 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,594 software developers and data experts.

C# and SQL-Server Stored Procedures

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
Oct 28 '06 #1
10 2775
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

Oct 28 '06 #2
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


Oct 28 '06 #3
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**********************************@microso ft.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



Oct 28 '06 #4
Did you set Command.CommandType=CommandType.StoredProcedure?
You have 2 paths in Sql Server - the RPC path (stored procs) and the
Language path (e.g. "Text").
Peter

--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com


"J. S. EDV" wrote:
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
Oct 28 '06 #5
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


Oct 28 '06 #6
I have also tried in a query window "execute proc1" - this works.
But
cmd = new SqlCommand("Execute proc1");
and
cmd = new SqlCommand("proc1");
cmd.CommandType = CommandType.StoredProcedure;
won't work.
"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


Oct 28 '06 #7
Hi Peter,

thanks for the answer.

Yes, I set cmd.CommandType = CommandType.StoredProcedure
I also tried with cmd = new SqlCommand("Execute proc1") without
CommandType.StoredProcedure, but this also won't work.

"Peter Bromberg [C# MVP]" wrote:
Did you set Command.CommandType=CommandType.StoredProcedure?
You have 2 paths in Sql Server - the RPC path (stored procs) and the
Language path (e.g. "Text").
Peter

--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com


"J. S. EDV" wrote:
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
Oct 28 '06 #8
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**********************************@microso ft.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**********************************@microso ft.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



Oct 29 '06 #9
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



Oct 29 '06 #10
Ok, now it works.
At the beginning I created a Database-File for my project in Visual Studio
2005.
I took the connection string from the properties window of the Database-File
in the Server Explorer Window. All works, only the procedure with the insert
and update command not. My connection string was" Data
Source=.\SQLEXPRESS;AttachDbFilename=|DataDirector y|\Datenbank.mdf;Integrated
Security=True;User Instance=True"

Now I have registered the database-file in the SQL Express Manager and made
a connection over tcp/ip. This works without any problems!

Is it possible that there is a bug?

Thanks for the help!

"J. S. EDV" wrote:

Jacek
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
Oct 29 '06 #11

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by george lewycky | last post: by
2 posts views Thread by Peter | last post: by
1 post views Thread by --CELKO-- | last post: by
reply views Thread by Medhatithi | last post: by
4 posts views Thread by arial | last post: by
reply views Thread by XIAOLAOHU | last post: by
reply views Thread by leo001 | last post: by
reply views Thread by Anwar ali | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.