September 24, 2013 at 8:11 am
I have a simple stored procedure that takes one nvarchar parameter as input. The parm is compared to a column defined as uniqueidentifier (column SubjectId, below). When I run the sproc in Management Studio it works. When I call it from c# it does not fail but it returns 0 rows.
I have checked the spelling of the sproc and of the parm (and their case). All aok. I have converted the sproc call to Command.Text. No change. I have hardcoded the parameter value. No change. I have put a breakpoint in just before filling the dataset and grabbed the query and executed it in Mangement Studio and it works, then I let C# continue execution but no rows are returned. I have changed the dataadapter to a datareader, no change.
Here's my code:
[dbo].[GetBySubjectId]
(@SubjectIdString nvarchar(550) = null)
as
begin
set nocount on
Select GroupId, GroupName
from dbo.MyTable
where filetypename = 'Enrollment' and
SubjectId = @SubjectIdString
Here's an example of the sproc call with the parm hardcoded in as an experiment:
sqlCommand.Parameters.Add("@SubjectIdString", SqlDbType.NVarChar).Value = "FBBE20B1-55F1-E111-BF73-00155D062F00";
Anybody see the problem?
September 24, 2013 at 8:19 am
What you are showing is only the Code to add the variable data to the stored procedure call. Not the Code to actually open the connection, call the sproc name, assign the variable to the sproc, close the connection. If you include all that, maybe we can see something. I use C# for all my development, and never have an issue calling a sproc and getting a value returned.
I would suggest that you use varchar instead of nvarchar. You are not using any international characters, and that is only wasting memory and space in SQL Server. Keep the data types as small as possible.
What happens if you hard code that Id into the sproc and call it form a test page from the front-end?
Andrew SQLDBA
September 24, 2013 at 8:25 am
I just saw this:
But in the sproc that you sent, you have a "begin" with no "end"
There is really no reason to have the "begin". Remove that. Unless you are not showing all the code in the sproc.
Why are you allowing the input param to be NULL if nothing is passed in?
Andrew SQLDBA
September 24, 2013 at 8:26 am
The rest of the code is:
DataSet ds = new DataSet();
try
{
using (SqlConnection Con = new SqlConnection(connectionString))
{
SqlCommand sqlCommand = new SqlCommand(sSprocName, Con);
sqlCommand.CommandType = CommandType.Text;
SqlDataAdapter Da = new SqlDataAdapter(sqlCommand);
//sqlCommand.Parameters.AddWithValue("@SubjectIdString", sSubjectId);
sqlCommand.Parameters.Add("@SubjectIdString", SqlDbType.NVarChar).Value = "FBBE20B1-55F1-E111-BF73-00155D062F00";
Da.Fill(ds);
}
}
catch (Exception ex)
{
Console.WriteLine("Unable to retrieve Subjects. Error was: " + ex.Message);
}
return ds;
To answer your question, no rows are returned.
September 24, 2013 at 8:28 am
Thanks for responding, Andrew. I just forgot to copy the "End".
No, I am not allowing null input and the null reference was not originally there. I added it during my debugging process. You are correct: it can be removed. But before it was there the results I was getting were identical to now.
September 24, 2013 at 8:47 am
Hi LadyReader
Did you try
command.CommandType = CommandType.StoredProcedure;
instead of
sqlCommand.CommandType = CommandType.Text;
Br.
Mike
September 24, 2013 at 8:53 am
Thanks for responding, Mike. Yes, the original method call passes in the name of the stored procedure and the value of the parameter.
private DataSet dsList(String connectionString, String sSprocName, String sSubjectId)
{
DataSet ds = new DataSet();
try
{
using (SqlConnection Con = new SqlConnection(connectionString))
{
SqlCommand sqlCommand = new SqlCommand(sSprocName, Con);
sqlCommand.CommandType = StoredProcedure;
SqlDataAdapter Da = new SqlDataAdapter(sqlCommand);
sqlCommand.Parameters.AddWithValue("@SubjectIdString", sSubjectId);
Da.Fill(ds);
September 24, 2013 at 9:21 am
I am not seeing anywhere that you are opening and closing your connection.
Why would are you passing in the connectionstring, and the sproc name? I believe in code re-use, but I also keep things really simple. What little bit of duplicate code, is more than ok with me, when it comes to troubleshooting a problem.
I keep my connection string in a method that gets called from each and every method needed. I do not pass that value in as a long string. Have you tried to set code in place to see if the connection is even open before you try to execute the sproc? I would make certain that your connection is Open before your sproc tries to fire.
This is a small sample of one of my methods where I insert data. The methods that I have that select data and fill a DropDownList are almost the exact same.
protected void InsertContactUsData(int titleID, string firstName)
{
SqlCommand SQLInsertCommand;
SqlConnection SQLDBConn = new SqlConnection(SQLDBConnectionString());
Int32 rowsAffected;
try
{
SQLDBConn.Open();
SQLInsertCommand = new SqlCommand("INS_ContactUs", SQLDBConn);
SQLInsertCommand.CommandType = CommandType.StoredProcedure;
SqlParameter TitleID = SQLInsertCommand.Parameters.Add("@TitleID", SqlDbType.Int);
InsertSuccess.Direction = ParameterDirection.Input;
SQLInsertCommand.Parameters["@TitleID"].Value = titleID;
SqlParameter FirstName = SQLInsertCommand.Parameters.Add("@FirstName", SqlDbType.VarChar, 30);
FirstName.Direction = ParameterDirection.Input;
SQLInsertCommand.Parameters["@FirstName"].Value = firstName;
rowsAffected = SQLInsertCommand.ExecuteNonQuery();
SQLDBConn.Close();
}
catch (System.Data.SqlClient.SqlException ex)
{
string msg = "Fetch Error:";
msg += ex.Message;
throw new Exception(msg);
}
finally
{
if (SQLDBConn != null)
{
SQLDBConn.Close();
}
}
}
Andrew SQLDBA
September 24, 2013 at 9:50 am
Andrew, the method was originally written to handle two stored procedures. The method works for the other sproc. The connectionstring is fine.
You do not need to Open a connection when you use a dataadapter; it happens automatically.
Once again, this call does not error (e.g. "your connection is not open"), it simply returns 0 rows.
September 24, 2013 at 11:04 am
Have you tried to setup a test page with all the items hard coded? Do you get a resultset using only SSMS and that same string? Have you tried CASTing the string to a UID once in the sproc to see what you get?
I have found that it is never a good idea to use a UID in things. Merely messes thing up, where an integer always work just fine.
Andrew SQLDBA
September 24, 2013 at 11:49 am
Yes, to all questions. No rows returned.
Here is what the sproc looks like now:
ALTER procedure [dbo].[ce_GetBySubjectId]
(@SubjectIdString nvarchar(550))
as
begin
set nocount on
Select GroupId, GroupName from dbo.MyTable
where filetypename = 'Enrollment' and
SubjectId = Convert (uniqueidentifier, @SubjectIdString)
end
In SSMS it returns one row (which is correct). From C#, no rows. This suggests there's something wrong with the parameter definition but I am not seeing it. The parm is defined as (@SubjectIdString nvarchar(550) in SSMS, and it was nvarchar in C#.
sqlCommand.Parameters.Add("@SubjectIdString", SqlDbType.NVarChar).Value = sSubjectId;
I've tried changing the parm's datatype to uniqueidentifier but that doesn't work either.
September 24, 2013 at 11:49 am
Have you tried to just run a query:
SELECT
GroupId
, GroupName
, FileTypeName
, SubjectID
FROM
dbo.MyTable
WHERE
SubjectId = 'FBBE20B1-55F1-E111-BF73-00155D062F00'
ORDER BY
FileTypeName ASC
To see if there is a row with the FileTypeName of 'Enrollment' and a SubjectID of 'FBBE20B1-55F1-E111-BF73-00155D062F00' ?
Have you verified that your SQL Connection String C# code is using the database that you think it is using?
Just suggesting things now. I have setup the exact same table and an ASP.NET page using C#, and executing a sproc coded the same as you show, and I get a resultset.
Andrew SQLDBA
September 24, 2013 at 11:55 am
You should use the SQL Profiler to see what's really happening on your SQL server when it's called from C#. In all likelihood, there's some unexpected difference at that point.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 24, 2013 at 12:00 pm
Lady
Try removing the ""SET NOCOUNT ON" statement for one time. I just read something about that. I am using it in my sproc, but it may be worth a shot here.
I read this:
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.aspx
It is a little off on what you are using, but it may just do the trick for you
Andrew SQLDBA
September 24, 2013 at 3:10 pm
Yes, to all questions.
The field I am comparing the parameter to is defined as uniqueidentifier and contains a Guid.
The sproc now reads, in part:
where filetypename = 'Enrollment' and
SubjectId = Convert (uniqueidentifier, rtrim(ltrim(@SubjectIdString)))
Works in SSMS (returns one row), fails in C# (returns 0 rows, but no error).
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply