CODEWITHSUNDEEP
c#asp.netsql-servermessage
armze3
armze3

Reputation: 55

C# connection to SQL database error

I am complete newbie to programming. I have a problem with my program, what I am trying to do is create a register page so that users can register and it will save into the SQL database but I got an error and I dont know what it means, I will paste the whole error message sorry if its not specific. Please explain in simple terms to me if possible thanks.

Here is my code:

protected void registerBtn_Click(object sender, EventArgs e)
{
    String conString = @"Data Source=sql-server;Initial Catalog=wa310;Integrated Security=True";
    SqlConnection myConnection = new SqlConnection(conString);

    string cmd = "INSERT INTO Student( First_Name, Surname, User_ID, Password) VALUES ( '" + fNameTxt.Text + "' , '" + sNameTxt.Text + "','" + userIdTxt.Text + "' ,'" + passwordTxt.Text + "')";
    SqlCommand myCommand = new SqlCommand(cmd, myConnection);

    try
    {
      myCommand.Connection = myConnection;
        myConnection.Open();
        myCommand.ExecuteNonQuery();
        Label1.Text = "You have successfully registered";
    }
    catch (Exception ex)
    {
        Label1.Text = "Exception in DBHandler" + ex;
    }
    finally
    {
        myConnection.Close();
    }

}

This is the error I get, again sorry for the long error message:

Exception in DBHandlerSystem.Data.SqlClient.SqlException (0x80131904): String or binary data would be truncated. The statement has been terminated. 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.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at OnlineExamCW.Register.registerBtn_Click(Object sender, EventArgs e) in f:\COMP1551 - Application and Web Development\OnlineExamCW\OnlineExamCW\Register.aspx.cs:line 31 ClientConnectionId:e08ebbe3-d4be-4a76-a64d-14aadb6e9d4c

Please tell me what this error means. Many thanks.

Upvotes: 0

Views: 35604

Answers (2)

Steve
Steve

Reputation: 216243

The error message tells you that something in your input values is too long to be stored in the designated column. Without knowing the size of your columns is difficult to propose a correct solution so, assuming that you have reasonable limits in the database fields like in this hypotethical schema:

First_Name nvarchar(50)
Surname nvarchar(50)
User_ID int
Password nvarchar(16)

then you should limit the max length of your input fields to the same sizes allowed by the datatable fields.

<asp:TextBox ID="fNameTxt" MaxLength="50" Text="" runat="server"/>
.....

and then create your insert command using this approach

protected void registerBtn_Click(object sender, EventArgs e)
{
    String conString = "....";
    string cmd = @"INSERT INTO Student( First_Name, Surname, User_ID, Password) 
                   VALUES (@first, @surname, @id, @password);"
    using(SqlConnection myConnection = new SqlConnection(conString))
    using(SqlCommand myCommand = new SqlCommand(cmd, myConnection))
    {
        myCommand.Parameters.Add(new SqlParameter() 
        {
           ParameterName = "@first",
           Value= fNameTxt.Text, 
           SqlDbType = SqlDbType.NVarChar,
           Size = 50
        });

        myCommand.Parameters.Add(new SqlParameter
        {
             ParameterName = "@surname",
             Value= sNameTxt.Text, 
             SqlDbType = SqlDbType.NVarChar,
             Size = 50
        });
        myCommand.Parameters.Add(new SqlParameter() 
        {
             ParameterName = "@id",
             Value= Convert.ToInt32(userIdTxt.Text), 
             SqlDbType = SqlDbType.Int
        });
        myCommand.Parameters.Add(new SqlParameter() 
        {
             ParameterName = "@password",
             Value= passwordTxt.Text, 
             SqlDbType = SqlDbType.NVarChar,
             Size = 16
        });

        myCommand.Connection = myConnection;
        myConnection.Open();
        if(myCommand.ExecuteNonQuery() > 0)
            Label1.Text = "You have successfully registered";
    }
}

Notice that in this code I have totally removed the string concatenation approach. This is a very dangerous mode to write sql statements because you could be easily hacked using the Sql Injection technique and your code could fail if someone enters a single quote in the input values (Try for example for a surname like O'Reilly)

There is another point that need a deep redesign. Storing passwords in clear text is considered a very bad practice because a simple look at your database table could reveal the passwords of all your students. But this is a more complex matter. If you are interested search about Password Hashing

Upvotes: 6

amit_g
amit_g

Reputation: 31250

It means the data in one or more fields that you are inserting in Student table has more characters than the field allows. Check the length of all fields -First_Name, Surname, User_ID, Password. Either increase the field length in the database or put a limit on data entry page so that the user isn't allowed to enter more than allowed characters in each field.

Upvotes: 1

Related Questions