locked
Inserting data to SQL Server RRS feed

  • Question

  • I typed the following statement after making a connection to the sql server. but I get an error saying "string or binary data would be truncated. The statement has been terminated." I think sqldatareader is not the one to use here. Is that so?? then what am I suppose to do here???

    SqlCommand command2  = new Sql Command("INSERT INTO Login(UserID, Password, UserType) VALUES('"+txtUsername.Text+"', 'Wrd', '"txtPassword.Text"')", myconnection);

    SqlDataReader insert = command2.ExecuteReader();

    Tuesday, March 19, 2013 5:30 PM

Answers

  • You should use ExecuteNonQuery method to inserts and updates and all other commands that are not ones querying data.

    Another thing you should do is check the text box values, otherwise you might be inserting invalid data.

    • Marked as answer by WindyD Wednesday, March 20, 2013 5:29 AM
    Tuesday, March 19, 2013 5:38 PM
  • "String or binary data would be truncated" is a SQL error indicating that you have a character column that is defined to hold X number or characters...and the number of characters in the value you are trying to insert into that column exceeds that number of characters.

    • Marked as answer by WindyD Wednesday, March 20, 2013 5:29 AM
    Tuesday, March 19, 2013 6:00 PM

All replies

  • You should use ExecuteNonQuery method to inserts and updates and all other commands that are not ones querying data.

    Another thing you should do is check the text box values, otherwise you might be inserting invalid data.

    • Marked as answer by WindyD Wednesday, March 20, 2013 5:29 AM
    Tuesday, March 19, 2013 5:38 PM
  • First of all, don't use such constructs at all. You do not want to embed parameters into the string the way you did. Use the parameters.

    When you use parameters, the beauty of it that you can specify the type and size and therefore you may avoid that problem at all.

    So, your query above can be changed to

    using (SqlCommand command2  = new Sql Command("INSERT INTO Login(UserID, Password, UserType) VALUES(@UserId, @Password, @UserType)", myconnection))
    {
     command2.Parameters.Add("@UserId", SqlDbType.VarChar,10).Value = txtUsername.Text.Substring(0,10); //use the correct type and size
    
    // 'Wrd', '"txtPassword.Text"' - same thing for your two other parameters
    
    Int3 rowsAffected = 0;  
    rowsAffected = command2.ExecuteNonQuery();//you're inserting, not running a select statement
    
    }


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Proposed as answer by Kalman Toth Monday, April 8, 2013 6:15 PM
    Tuesday, March 19, 2013 5:40 PM
  • "String or binary data would be truncated" is a SQL error indicating that you have a character column that is defined to hold X number or characters...and the number of characters in the value you are trying to insert into that column exceeds that number of characters.

    • Marked as answer by WindyD Wednesday, March 20, 2013 5:29 AM
    Tuesday, March 19, 2013 6:00 PM
  • Thanks all!!!
    Wednesday, March 20, 2013 5:30 AM
  • I've found this to be a great resource:

    http://csharp.net-informations.com/dataadapter/insertcommand-sqlserver.htm

    You can find all kinds of great C# sample code here:

    http://csharp.net-informations.com/


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Sunday, March 24, 2013 4:46 PM
  • Thanks alot for your kind support.. :)
    Monday, April 8, 2013 1:08 PM