datareader error in asp.net c#

Question

I had a problem in selecting some data from a database and saving the data into a textbox. I have the following profile table:

if (Session["User"] != null)
            {
                try
                {
                    string user = Session["User"].ToString();
                    SqlConnection conn = new SqlConnection(@"Data Source=localhost;Initial Catalog=SKRIPSI;User ID=sa;Password=sa");
                    conn.Open();
                    string biodata = "select * from mahasiswa where id='"+user+"'";
                    SqlCommand comm = new SqlCommand(biodata, conn);
                    SqlDataReader reader = comm.ExecuteReader();
                    while (reader.Read())
                    {
                        txtid.Text = reader["id"].ToString();
                        txtnama.Text = reader["nama"].ToString();
                        txtemail.Text = reader["email"].ToString();
                        txtkontak.Text = reader["kontak"].ToString();
                        txtalamat.Text = reader["password"].ToString();
                    }
                    reader.Close();
                    conn.Close();
                }
                catch (Exception ex)
                {
                    Response.Write(ex.ToString());
                }
            }
            else
            {
                Response.Redirect("Login_Form.aspx");
            }

According to my experience of coding this is correct. but I get an error when calling while(reader.Read()). This the error Exception.

System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting the varchar value 'System.Web.UI.WebControls.TextBox' to data type int. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows) at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more) at System.Data.SqlClient.SqlDataReader.Read() at Skripsi.Mahasiswa.Page_Load(Object sender, EventArgs e) in C:\Users\Henz\documents\visual studio 2015\Projects\Skripsi\Skripsi\Mahasiswa.aspx.cs:line 26 ClientConnectionId:aebdd470-263a-43a9-a7f3-12885668d946 Error Number:245,State:1,Class:16

Please help me solve this problem. I'm still a beginner. Thank You.

while(reader.Read()) isn't error, can you add thrown Exception message — Fabio
– Fabio, Commented Nov 19, 2016 at 12:55
It seems to me you're comparing an int id to a string user... — Thejaka Maldeniya
– Thejaka Maldeniya, Commented Nov 19, 2016 at 12:56
The fifth column in your table is "alamat"; the code you posted is expecting "password". — to StackOverflow
– to StackOverflow, Commented Nov 19, 2016 at 13:04

Mark Fitzpatrick · Accepted Answer · 2016-11-19 13:06:58Z

If you're getting a single row, use if(reader.read()). while(reader.read()) could loop, and it often gets people into trouble when the query goes wrong.

You have nullable types in your database. You are not checking for null values though. Use the IsDBNull() method before you set the field. You have to go through an extra step as this only accepts an int.

if(!reader.IsDBNull(reader.GetOrdinal("nama"))
{
    txtid.Text = reader["id"].ToString();
}

Also, in your sql statement you have single quotes around your id concatenation. The id column is defined as an int, adding it in single quotes will have it treated as a string.

 string biodata = "select * from mahasiswa where id="+user+"";

Keep in mind, this string concatenation approach ensures that your website is open to sql injection attacks, and the way it is written someone could easily craft sql that would give them the contents of the entire table, or even delete the contents of the database.

Collectives™ on Stack Overflow

datareader error in asp.net c#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related