Retrieving VarChar(MAX) from SQL Server using C# and a stored procedure

Question

I have a WebMethod which is called to retrieve a varchar(max) column from SQL Server. I create my neccessary stored procedure, which works fine in Management Studio, but when I run the below code I get an error:

Invalid attempt to read when no data is present

Code:

[WebMethod]
public static void PopulatePopUp(string arg)
{
    var str = GlobalStatic.ExceptBlanks(arg);

    SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["Conn"].ConnectionString);

    SqlDataReader rdr = null;

    using (conn)
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            conn.Open();

            cmd.CommandText = "GetMessage_Sel";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@messageId", SqlDbType.VarChar, -1).Value = str;
            cmd.Parameters.Add("@RowCount", SqlDbType.Int).Direction = ParameterDirection.Output;

            cmd.Connection = conn;

            try
            {
                rdr = cmd.ExecuteReader();

                if (rdr.HasRows)
                {
                    string fieldValue = rdr.GetString(0);
                }
                else
                {
                    Console.WriteLine("No rows found.");
                }

                rdr.Close();
            }
            catch (Exception err)
            {
                // handle the error
                //messageInsert = false;
            }
            finally
            { 
                conn.Close(); 
            }
        }
    }
}

Can you post the procedure? – DoctorMick Jul 10 '13 at 08:57 — DoctorMick, Jul 10 '13 at 08:57
Well you need a `rdr.Read()` call somewhere there – V4Vendetta Jul 10 '13 at 09:02 — V4Vendetta, Jul 10 '13 at 09:02

V4Vendetta · Answer 1 · 2013-07-10T09:09:32.093

2

Depending on the rows you expect you would need the Read method called which will actually fetch you the results, so this condition should be good as it would only move in if there is a row to read out

while(rdr.Read())
{...}

OR

if(rdr.Read())
{ ...}

edited Jul 10 '13 at 09:09

answered Jul 10 '13 at 09:03

V4Vendetta

37,194
9
78
82

score 2 · Accepted Answer · answered Jul 10 '13 at 09:04

2

try this

                    if (rdr.HasRows)
                    {
                        while(rdr.Read())
                        {
                            string fieldValue = rdr[0].ToString();
                        }

                    }
                    else
                    {
                        Console.WriteLine("No rows found.");
                    }

answered Jul 10 '13 at 09:04

sangram parmar

8,462
2
23
47

School boy error. thanks guys. Changing my code to accept a read (idiot i am) i now get a cast issue. Int32 to a string. I was under the impression that -1 in the declaration is for a Varchar(max) – CSharpNewBee Jul 10 '13 at 09:11

score 1 · Answer 3 · answered Jul 10 '13 at 09:04

1

That happened because you have not read next record.

Change

if (rdr.HasRows)
{
    string fieldValue = rdr.GetString(0);
}

to

if (rdr.Read())
{
    string fieldValue = rdr.GetString(0);
}

answered Jul 10 '13 at 09:04

gzaxx

17,312
2
36
54

Retrieving VarChar(MAX) from SQL Server using C# and a stored procedure

3 Answers3