asp.net mvc c# - problem with reading from sql server database

Question

I cant read any data from database, could you please look at my code and find a problem?

 cmd = new SqlCommand();
                cmd.Connection = connection;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = @"SELECT ImageData, " 
                                 + " ContentType, " 
                                 + " ImageName " 
                                 + " FROM UsersImage "
                                 + " WHERE UserName = @UserName ";

                cmd.Parameters.Add(new SqlParameter("@UserName", ThreadUserName));

                reader = cmd.ExecuteReader();
            if (reader["ContentType"] != DBNull.Value)
            {
                ContentType = Convert.ToString(reader["@ContentType"]);
            }

            if (reader["ImageName"] != DBNull.Value)
            {
                ImageName = Convert.ToString(reader["@ImageName"]);
            }

            if (reader["ImageData"] != DBNull.Value)
            {
                ImageData = Convert.ToByte(reader["@ImageData"]);
            }

            int affectedRows = cmd.ExecuteNonQuery();
            if (affectedRows != 1)
            {

            }

            reader.Close();

My table name is: UsersImage.

My columns, ImageData(image), ContentType(nvarchar50), ImageName(nvarchar50), UserName (varchar20)

Do you get an exception? Does it work at all? It's not possible for us to debug your code by looking at it. — Dave Swersky
– Dave Swersky, Commented Aug 25, 2010 at 16:26
you definitely have rows in the db? you might also think about using an ORM - Castle ActiveRecord is super easy to get moving with - beats all this messing about with inline sql :) — user156888
– user156888, Commented Aug 25, 2010 at 16:26
Amen on ORM, have used Active Record and easy enough once you get going. I would say this type of coding should be retired ASAP. Is very inefficient and subject to pain (like you are feeling now) — ArtificialGold
– ArtificialGold, Commented Aug 25, 2010 at 16:28
exaption says that i have no entries in database, but i have it for username which is in parameter — r.r
– r.r, Commented Aug 25, 2010 at 16:38

Martin · Accepted Answer · 2010-08-26 12:05:56Z

3

Add in reader.Read().

 cmd = new SqlCommand();
                cmd.Connection = connection;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = @"SELECT ImageData, " 
                                 + " ContentType, " 
                                 + " ImageName " 
                                 + " FROM UsersImage "
                                 + " WHERE UserName = @UserName ";

                cmd.Parameters.Add(new SqlParameter("@UserName", ThreadUserName));

using (IDataReader reader = cmd.ExecuteReader())
{

    if (reader.Read())
    {
        if (reader["ContentType"] != DBNull.Value)
        {
            ContentType = Convert.ToString(reader["ContentType"]);
        }

        if (reader["ImageName"] != DBNull.Value)
        {
            ImageName = Convert.ToString(reader["ImageName"]);
        }

        if (reader["ImageData"] != DBNull.Value)
        {
            ImageData = Convert.ToByte(reader["ImageData"]);
        }

        int affectedRows = cmd.ExecuteNonQuery();
        if (affectedRows != 1)
        {

        }
    }
}

EDIT: I added in a using as well to replace the reader.Close().

edited Aug 26, 2010 at 12:05

answered Aug 25, 2010 at 16:40

Martin

11.1k8 gold badges53 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

r.r Over a year ago

i find out that my only problem was that i read with @. it schould be without @ like (reader["ImageName"]) instead (reader["@ImageName"]). that it.

Malevolence · Accepted Answer · 2010-08-25 17:11:44Z

    string sql = @"SELECT ImageData, ContentType, ImageName FROM UsersImage WHERE UserName = @UserName";

    using (var cn = new SqlConnection("[YOUR CONNECTION STRING]"))
    using (var cmd = new SqlCommand(sql, cn))
    {
        // Set some properties on the cmd object
        cmd.CommandType = CommandType.Text;
        cmd.Parameters.AddWithValue("@UserName", ThreadUserName);

        // Open the connection
        cn.Open();

        // Execute your command and get back a data reader
        using (var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
        {
            if (reader.HasRows)
            {
                reader.Read();

                if (reader["ContentType"] != DBNull.Value)
                {
                    ContentType = reader["ContentType"].ToString();
                }
                if (reader["ImageName"] != DBNull.Value)
                {
                    ImageName = reader["ImageName"].ToString();
                }
                if (reader["ImageData"] != DBNull.Value)
                {
                    ImageData = (byte[])reader["ImageData"];
                }
            }
        }
    }

Collectives™ on Stack Overflow

asp.net mvc c# - problem with reading from sql server database

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related