CODEWITHSUNDEEP
c#asp.net
Hendrick Wijaya
Hendrick Wijaya

Reputation: 47

datareader error in asp.net c#

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

This is my table design

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.

Upvotes: 0

Views: 1388

Answers (1)

Mark Fitzpatrick
Mark Fitzpatrick

Reputation: 1624

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.

Upvotes: 1

Related Questions