SQL parameters and C# - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL parameters and C#

Author

Topic

matti-82
Starting Member

2 Posts

Posted - 2011-08-03 : 04:40:59

Hi,
I have a problem with SQL parameters.

INSERT INTO Material (MaterialNr,Bezeichnung,Mandant) VALUES (@MaterialNr, @Bezeichnung, @Mandant)

Executing this statement results in the error Must declare the scalar variable "@MaterialNr".

If I declare the variable before...

DECLARE @MaterialNr varchar(4000), @Bezeichnung varchar(4000), @Mandant varchar(4000)
INSERT INTO Material (MaterialNr,Bezeichnung,Mandant) VALUES (@MaterialNr, @Bezeichnung, @Mandant)

...I get the error Cannot insert the value NULL into column 'MaterialNr', although OdbcCommand.Parameters is filled.

If I use ? as parameter, it works. But I want to avoid that, because the final statement will look like this:

UPDATE Material SET Bezeichnung=@Bezeichnung WHERE MaterialNr=@MaterialNr AND Mandant=@Mandant
IF (@@ROWCOUNT = 0) BEGIN INSERT INTO Material (MaterialNr,Bezeichnung,Mandant) VALUES (@MaterialNr, @Bezeichnung, @Mandant) END

If using ?, I would have to pass the same parameter multiple times.

BTW: I use the Odbc classes, because the program should not require MS SQL Server.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-08-03 : 05:36:16

http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson06.aspx

No, you're never too old to Yak'n'Roll if you're too young to die.

matti-82
Starting Member

2 Posts

Posted - 2011-08-03 : 10:28:58

I found a solution:

DECLARE @MaterialNr varchar(4000) = ?, @Bezeichnung varchar(4000) = ?, @Mandant varchar(4000) = ?
UPDATE Material SET Bezeichnung=@Bezeichnung WHERE MaterialNr=@MaterialNr AND Mandant=@Mandant
IF (@@ROWCOUNT = 0) BEGIN INSERT INTO Material (MaterialNr,Bezeichnung,Mandant) VALUES (@MaterialNr, @Bezeichnung, @Mandant) END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources