Re: Calling a stored procedure from C# failing - C# Discussion Boards

Le@rner16-Sep-16 19:40

Le@rner

16-Sep-16 19:40

thanks

OriginalGriff16-Sep-16 21:27

16-Sep-16 21:27

You're welcome!

Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...

Le@rner16-Sep-16 0:09

Le@rner

16-Sep-16 0:09

g.DrawString(value, heading_font, C_Brush, str_rect, SF_TEXT);

Pete O'Hanlon15-Sep-16 22:04

Calling a stored procedure from C# failing

15-Sep-16 22:04

I'm sorry but your post doesn't make much sense. There are too many areas that we could be making assumptions about here. Are you saying that you want to take a screenshot of the form and put it in a report? Is it a case that you want to post the values from the text boxes and the image and put them line by line in a report? Are you using report writing software? What exactly do you mean by a report? Are you including headers and footers? Do you expect this to be multipage? Are the number of text boxes and images fixed? Where do the images come from? Are they cropped/resized, or are the images scrollable?

This space for rent

Member 1274201915-Sep-16 5:35

15-Sep-16 5:35

I am stumped as to why the below C# code isn't returning any rows from my database

conn = new System.Data.SqlClient.SqlConnection("Data Source=" + sSessionServer + ";database=" + sSessionDatabase + "; Integrated Security=SSPI");

               String SQL = "[q_CheckOOSWarehouse80ItemsNeedingNotification]";

               cmd = new SqlCommand();

               cmd.CommandText = SQL;
               cmd.CommandType = System.Data.CommandType.StoredProcedure;
               cmd.Connection = conn;

               var myparams = new SqlParameter("@ItemsIDS", DbType.String);
               myparams.Direction = ParameterDirection.Input;
               myparams.Size = 40;
               myparams.Value = ItemsToBeChecked;
               cmd.Parameters.Add(myparams);

               conn.Open();

               myReader = cmd.ExecuteReader();

               // check to see if any rows were returned.
               if (myReader.HasRows)
               {       // if so, loop through all the records
                   while (myReader.Read())

My stored procedure looks like this:

SQL

ALTER PROCEDURE [dbo].[q_CheckOOSWarehouse80ItemsNeedingNotification]@ItemsIDS AS VARCHAR(40)
AS

BEGIN

	SET NOCOUNT ON

	select * from q_Warehouse80_OOS_ItemsNeedingNotification where item_id  = @ItemsIDS

END

The parameters I am using when calling the SP from my C# program are these:
SQL = "[q_CheckOOSWarehouse80ItemsNeedingNotification]"
ItemsToBeChecked = 'B30-244-595-CB-B001'

The SP doesn't return any records when I call it from my C# program, but if I use the above parameters from within a SSMS Query I get the results I expect.

If I were to remove the where clause of my SP I get records returned when the call is done from within my C# program, so it appears the issue with the parameter being passed to the SP.

Does anyone have any suggestions as to how to resolve this issue ?

Thank you in advance for your help.

#realJSOP15-Sep-16 5:43

#realJSOP

15-Sep-16 5:43

First, take the square brackets off your stored proc name in the C# code.

Second, put a try/catch block in your c# method so you can see what exception is being thrown. That will tell you exactly what the problem is.

".45 ACP - because shooting twice is just silly" - JSOP, 2010
-
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013

Member 1274201915-Sep-16 5:45

15-Sep-16 5:45

John,
Thanks for responding to my question.

I'm not getting an error message. The problem is I am not getting any records back.

I previously tried removed the square brackets, but that generated an error that the SP couldn't be found Frown | :(

OriginalGriff15-Sep-16 5:51

15-Sep-16 5:51

Start by checking that you are testing against the same database - it's surprising how often the SSMS check is done against the production DB instead of the development DB which has different data!
Then check that the SP in your Dev DB looks exactly like you think it does.
If all of that looks good, comment out the content of your SP, and replace it with this:

SQL

select '-' + item_id + '-', '-' + @ItemsIDS + '-' from q_Warehouse80_OOS_ItemsNeedingNotification where item_id  = 'B30-244-595-CB-B001'

Then run your C# code again and see exactly what you get back.
If what you say is right, then it should be two identical strings:

-B30-244-595-CB-B001- and -B30-244-595-CB-B001-

But I'm betting it doesn't. Either way, that should isolate roughly where the problem is being caused - and that should give you (and us) at least more to work from.

I'd code it differently:

cmd = new SqlCommand();

cmd.CommandText = SQL;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Connection = conn;

var myparams = new SqlParameter("@ItemsIDS", DbType.String);
myparams.Direction = ParameterDirection.Input;
myparams.Size = 40;
myparams.Value = ItemsToBeChecked;
cmd.Parameters.Add(myparams);

Would become:

cmd = new SqlCommand(SQL, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue(@ItemsIDS, ItemsToBeChecked);

It might be worth checking that as well, in case it's a field size mismatch / padding problem.

Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...

Member 1274201915-Sep-16 6:28

15-Sep-16 6:28

I modified my SP as you suggested:
BEGIN

SET NOCOUNT ON

--select * from q_Warehouse80_OOS_ItemsNeedingNotification where item_id = @ItemsIDS
select '-' + item_id + '-' as R1, '-' + @ItemsIDS + '-' as R2 from q_Warehouse80_OOS_ItemsNeedingNotification where item_id = 'B30-244-595-CB-B001'

END

And this is what I get back when I call the SP from my C#:

R1 = "-B30-244-595-CB-B001-"
R2 = "-'B30-244-595-CB-B001'-"

OriginalGriff15-Sep-16 6:42

15-Sep-16 6:42

And if you use the debugger to look at your itemsToBeChecked variable in the C#, you'll find the quotes are part of the string - which means they are part of the parameter you pass to SQL, and that explains why it doesn't match.

Now all you need to do is look at where that comes from and find out why the quotes are there. They're easy to get rid of - .Trim('''') will remove them - but it's better to find out what adds them and stop it doing it! Laugh | :laugh:

Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...

Member 1274201915-Sep-16 7:01

15-Sep-16 7:01

I'm embarrassed to say that my C# code was adding the quotes around the argument being passed because a long time ago(I think it was Monday) I wanted the SP to take a list of strings and check to see if any of them matched the item_ids in the view the SP was searching.

Here is how my SP was originally looking before I started messing with it.

@ItemsIDS AS VARCHAR(max)
AS

BEGIN

SET NOCOUNT ON

select item_id from q_Warehouse80_OOS_ItemsNeedingNotification where item_id in(@ItemsIDS)

END

As you can see I am using the in qualifier which requires the arguments being passed in the @ItemsIDS string to be separated by single quotes.

That's why the quotes were in the argument that I was passing to the SP.

So here I am back to square one trying to figure out why the SP doesn't work when I pass (in this case) one argument in the @ItemsIDS variable.

OriginalGriff15-Sep-16 8:05

15-Sep-16 8:05

Ah. That's simple: the IN part of the SP is precompiled - you can't pass it a list of items in a string, because it doesn't get replaced by the parameter list value.
See here: Using comma separated value parameter strings in SQL IN clauses[^] - it may give you a solution.

Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...

Member 1274201915-Sep-16 11:07

15-Sep-16 11:07

With a slight modification this function works just the way I need it to.

Finally !

Thank you for all your help ! Blush | :O

OriginalGriff15-Sep-16 11:29

15-Sep-16 11:29

You're welcome!

Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...

Luc Pattyn15-Sep-16 21:12

Luc Pattyn

15-Sep-16 21:12

Excellent thread!

Thumbs Up | :thumbsup:

Luc Pattyn [My Articles] Nil Volentibus Arduum

OriginalGriff15-Sep-16 21:15

How to call store procedure which return multiple result set with EF code first

15-Sep-16 21:15

From you, that is high praise indeed my friend!

Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...

Tridip Bhattacharjee15-Sep-16 5:11

Tridip Bhattacharjee

15-Sep-16 5:11

just show me with sample code that how can i call store procedure which return multiple result set with EF code first.

i search google a bit but found no example. thanks

tbhattacharjee

Re: How to call store procedure which return multiple result set with EF code first

OriginalGriff15-Sep-16 5:42

Re: How to call store procedure which return multiple result set with EF code first

15-Sep-16 5:42

Another Help Vampire.
I suggest that you start reading the answers you have already been given recently, startign with this one: Re: what is difference between LINQ and EF - C# Discussion Boards[^]

Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...

Pete O'Hanlon15-Sep-16 5:43

Re: How to call store procedure which return multiple result set with EF code first

15-Sep-16 5:43

He's the worst example of a Help Vampire I have seen in a long time.

This space for rent

OriginalGriff15-Sep-16 5:59

Re: How to call store procedure which return multiple result set with EF code first

15-Sep-16 5:59

It'll be fun when he gets to the exams and finds he can't ask anyone at all.
All this lack of effort wasted by a fail! :Evil Laugh | :laugh:

Smiley:

Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...

Pete O'Hanlon15-Sep-16 9:05

EF code first and out type parameter in store procedure

15-Sep-16 9:05

Look at his profile. It says he doesn't have time to fill it in. He's obviously not using that time learning the framework.

This space for rent

Tridip Bhattacharjee15-Sep-16 5:11

Tridip Bhattacharjee

15-Sep-16 5:11

suppose i have a store procedure which has one out type parameter and out type parameter value is set inside store proc so when i will call that store proc by EF code first then how could read the out type parameter value by EF code first ?

looking for a small sample. i search google a bit but found no example. thanks

tbhattacharjee

Re: EF code first and out type parameter in store procedure

Dave Kreskowiak16-Sep-16 3:51

Dave Kreskowiak

16-Sep-16 3:51

Might I makes a suggestion: Amazon: Entity Framework[^].

Serisouly, you're not going to learn this stuff by bombarding the forums with questions. You're going to miss a ton of information that ties all this stuff together.

A guide to posting questions on CodeProject

Click this: Asking questions is a skill.
Seriously, do it.

Dave Kreskowiak

How to read single value return by store proc with EF code first

Tridip Bhattacharjee15-Sep-16 5:10

Tridip Bhattacharjee

15-Sep-16 5:10

suppose i am calling store proc by EF code first and store proc return value like this way

select 'SUCCESS'

or

select 1

OR

Return 1

just tell me how to read the single value return by store proc with EF code first. looking for a small sample code. i search google a bit but found no example. thanks

tbhattacharjee

Re: How to read single value return by store proc with EF code first

Pete O'Hanlon15-Sep-16 5:42