-1

I am trying to figure out a way to execute the following stored procedure using C#.

ALTER PROCEDURE spLoadClients
    @NetworkingChannel nvarchar(50)
AS
    SELECT       
        Client_Groups
    FROM
        ClientTable
    WHERE        
        Networking_Channel = (@NetworkingChannel)

    RETURN

The stored procedure is correct. I just need to find the proper method to execute it using C#.

Here is what I tried:

SqlDataReader reader;
string UpdateCommand = "spLoadClients";

using (SqlConnection sqlConnectionCmdString = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Shawn\Documents\Visual Studio 2010\Projects\Server\database\ClientRegit.mdf;Integrated Security=True;User Instance=True"))
using (SqlCommand sqlCommand = new SqlCommand(UpdateCommand, sqlConnectionCmdString))
{
    sqlCommand.CommandType = CommandType.StoredProcedure;
    sqlCommand.Parameters.Add("@NetworkingChannel", SqlDbType.NVarChar).Value = IntializedNetworks[i];

    sqlConnectionCmdString.Open();

    reader = sqlCommand.ExecuteReader();
    // Data is accessible through the DataReader object here.
    IntializedPostNet[i] = reader[i].ToString(); //trying to add data from reader into an array errors here
    sqlConnectionCmdString.Close();
}

I keep getting an error that says

Invalid attempt to read when no data is present.

What am I doing wrong?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
shawn
  • 113
  • 1
  • 1
  • 15
  • possible duplicate of [How to execute a stored procedure within C# program](http://stackoverflow.com/questions/1260952/how-to-execute-a-stored-procedure-within-c-sharp-program) – Abe Miessler Oct 29 '14 at 23:40
  • I gave more in depth of the problem im having with the reader I have no problem casting the stored procedure but something is not right when I am passing the right values and the reader doesn't return any values – shawn Oct 30 '14 at 00:25
  • Perhaps `reader[i]` should read `reader[0]`? As far as I can see there is only one column returned from `spLoadClients`. – Nikola Markovinović Oct 30 '14 at 00:41
  • That's All I need is the column Client_Groups Field in the data reader the reader should have an index that's automatically set to the length of the table number of rows, its not expressing that and its not receiving the values I do not know why this was marker spam or redundant I have nothing similar to this question – shawn Oct 30 '14 at 00:43
  • Shouldn't it be reader.Read().Tostring() to advance through the readers index or something similar? or is it reader["Client_Groups"].Read(); – shawn Oct 30 '14 at 00:48
  • 1
    Yes, I have made an error. It's been a while. See [Retrieving Data Using a DataReader](http://msdn.microsoft.com/en-us/library/haa3afyz%28v=vs.110%29.aspx) to learn to use reader. In short, set up a loop while reader.Read() returns true, and then use Getxxx methods to retrieve column values at given index (reader.GetString(0), I think, will work for you). – Nikola Markovinović Oct 30 '14 at 00:56
  • Ok, I tried everything you said I even tried it the other Convert.ToString(reader["Client_Groups"]); // This still returns as no data presented error and I have the for loop – shawn Oct 30 '14 at 01:04
  • Maybe some code in an sample to better describe the situation cause my code looks right? – shawn Oct 30 '14 at 01:04
  • 1
    There is an example in link I posted earlier. If `IntializedPostNet` is `List` you might do the following: `while (reader.Read()) { IntializedPostNet.Add(reader.GetString(0)); }`. – Nikola Markovinović Oct 30 '14 at 01:17
  • Thank you both of those ways work I just did this while (reader.Read()) { IntializedNetworks[i] = reader.GetString(i); } – shawn Oct 30 '14 at 01:27
  • 1
    I don't think `i` should be used as index in `GetString` because it will work only if stored procedure returns only one row. You should always read column 0 because there is only one column in source. `GetString()` parameter is not index of row but of column. – Nikola Markovinović Oct 30 '14 at 01:31
  • No it actually gets more than one row, It works I got two rows loaded the increments through the progress of the reader, the reader automatically reads the whole tables data rows but only the rows that is looking for the unique identifier that I described in the code also describes more than one row if you look at the parameter being used the WHERE Part the SQL Procedure Call – shawn Oct 30 '14 at 02:03
  • actually you are right it only gets one row, not good I need the whole all rows associated with the @param value my error you was correct. – shawn Oct 30 '14 at 02:10
  • I need to read every row that has the parameter value this is my problem its not showing or GetString() don't work – shawn Oct 30 '14 at 02:18
  • I need to know how to access the readers column index, and string value with a loop of i – shawn Oct 30 '14 at 02:26
  • Look it only adds to the one value from that specified column like that I tried to mystringarray[I] = GetValue(I).tostring(i) this only adds to the index 0 in the array and I have more than one row I need it to add to my index with as many rows as it needs or has – shawn Oct 31 '14 at 21:30

2 Answers2

0

You need to call the .Read() method on your SqlDataReader - at least once, to actually get back the data.

Furthermore, just like SqlConnection and SqlCommand, the SqlDataReader also implements the IDisposable interface and therefore should really be wrapped in a using block.

So add this to your code:

sqlConnectionCmdString.Open();

using (reader = sqlCommand.ExecuteReader())
{
    // iterate over the rows returned by the reader
    while (reader.Read()) 
    {
         // Data is accessible through the DataReader object here.
         IntializedPostNet[i] = reader[i].ToString(); //trying to add data from reader into an array errors here
    }

    reader.Close();
}
sqlConnectionCmdString.Close();
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Ok, I run tested your code, What's happening is the array index element 0 is being replaced with the second Element in the array so, instead of the values being added to the array 0, and 1 it just going into the first index 0 and the same value displays twice and leaves one of the values out. Why is it doing that? Index[0] value just gets keeping placed in here now im using for loop and it should be adding it in Index[myfirstvalue], Index[mysecondvalue] of my intializedPostNet array – shawn Oct 30 '14 at 06:22
  • I got a question in my database the networking channel field has multiple of the same values for instance in my network channel field i have two rows of the value 2 and I want to get all the clients that reside on channel two the reader should be able to get all clients that are on channel 2 in the network channel field. so i want to get clientA and ClientB get all the values in the client field where the networking channel field is equal to 2. that way i can see all the clients that reside on channel two – shawn Oct 30 '14 at 06:34
0

When using the reader with Stored Procedure Versus Oledb you can use this method. 

var list = (from IDataRecord r in reader
                                       select new
                                       {
                                          FieldName1 = (string)r["Network"],
                                          FieldName2 = (string)r["IPAddress"].ToString(),
                                          FieldName3 = (Int32)r["AsPort"],
                                          FieldName4 = (string)r["ThreadLocks"],
                                          FieldName5 = (string)r["GroupBy"]
                                        }).ToList();

When you are using a reader with sql string query as input, or Oledb Inline Query you can write like this to read and add to an object.

 while (objReader.Read())
            {

                //Upon reading Clean Data for fresh search
                cboClientGroup.Items.Clear();
                cboOccupation.Items.Clear();
                cboCompany.Items.Clear();
                cboClientID.Items.Clear();

                //Apply Fresh Search
                cboClientGroup.Items.Add(Convert.ToString(
                    objReader["Client_Groups"]));

                cboOccupation.Items.Add(Convert.ToString(
                    objReader["Occupation"]));

                cboCompany.Items.Add(Convert.ToString(
                    objReader["Company"]));

                cboClientID.Items.Add(Convert.ToString(
                    objReader["ClientID"]));
}
shawn
  • 113
  • 1
  • 1
  • 15