CODEWITHSUNDEEP
sql
tanya
tanya

Reputation: 21

inserting values into database

private void BtnSave_Click(object sender, EventArgs e)
{
   using (SqlConnection sqlConn = new SqlConnection("Data Source=TANYA-PC;Initial Catalog=biore1;Integrated Security=True")) //or variable to it
   {
      string sqlQuery = @"INSERT INTO cottonpurchase VALUES(@slipno, @purchasedate, @farmercode, @farmername, @villagename, @basicprice, @weight, @totalamountbasic, @premium, @totalamountpremium, @totalamountpaid, @yeildestimates)";

      //NOTE: RENAME "MyTable" to your database table!!!!

      using (SqlCommand cmd = new SqlCommand(sqlQuery, sqlConn))
      {
         cmd.Parameters.Add("@slipno", SqlDbType.Int).Value = (!String.IsNullOrEmpty(TxtSlipNo.Text)) ? int.Parse(TxtSlipNo.Text) : (object)DBNull.Value;
        cmd.Parameters.Add("@purchasedate", SqlDbType.DateTime).Value = monthCalendar1.SelectionStart; //date of selection!
      cmd.Parameters.Add("@farmercode", SqlDbType.Int).Value = Convert.ToInt32(TxtFarmerCode.Text);
      cmd.Parameters.Add("@farmername", SqlDbType.VarChar, 100).Value = TxtFarmerName.Text;
      cmd.Parameters.Add("@villagename", SqlDbType.VarChar, 100).Value = TxtVillageName.Text;
      cmd.Parameters.Add("@basicprice", SqlDbType.Int).Value = (!String.IsNullOrEmpty(TxtBasicPrice.Text)) ? int.Parse(TxtBasicPrice.Text) : (object)DBNull.Value;
      cmd.Parameters.Add("@weight", SqlDbType.Int).Value = (!String.IsNullOrEmpty(TxtWeight.Text)) ? int.Parse(TxtWeight.Text) : (object)DBNull.Value;
      cmd.Parameters.Add("@totalamountbasic", SqlDbType.Int).Value = (!String.IsNullOrEmpty(TxtTotalAmountBasic.Text)) ? int.Parse(TxtTotalAmountBasic.Text) : (object)DBNull.Value;
      cmd.Parameters.Add("@premium", SqlDbType.Int).Value = (!String.IsNullOrEmpty(TxtPremium.Text)) ? int.Parse(TxtPremium.Text) : (object)DBNull.Value;
      cmd.Parameters.Add("@totalamountpremium", SqlDbType.Int).Value = (!String.IsNullOrEmpty(TxtTotalAmountPremium.Text)) ? int.Parse(TxtTotalAmountPremium.Text) : (object)DBNull.Value;
      cmd.Parameters.Add("@totalamountpaid", SqlDbType.Int).Value = (!String.IsNullOrEmpty(TxtTotalAmountPaid.Text)) ? int.Parse(TxtTotalAmountPaid.Text) : (object)DBNull.Value;
      cmd.Parameters.Add("@yeildestimates", SqlDbType.Int).Value = (!String.IsNullOrEmpty(TxtYeildEstimates.Text)) ? int.Parse(TxtYeildEstimates.Text) : (object)DBNull.Value;

      sqlConn.Open();
      try
      {
        cmd.ExecuteNonQuery();
      }
      catch (Exception ex)
      {
        MessageBox.Show(ex.Message);
      }
    }
  }
}

When I execute this I get an error that says :

Input string was not in a correct format.

cmd.Parameters.Add("@farmercode", SqlDbType.Int).Value = int.Parse(TxtFarmerCode.Text);

Upvotes: 1

Views: 815

Answers (4)

Johan
Johan

Reputation: 76693

Your error does not match your code:

Code

cmd.Parameters.Add("@farmercode", SqlDbType.Int).Value 
  = Convert.ToInt32(TxtFarmerCode.Text);

Error 

cmd.Parameters.Add("@farmercode", SqlDbType.Int).Value 
   = int.Parse(TxtFarmerCode.Text);

Other than that it seems obvious that TxtFarmerCode.Text does not translate into a valid int.
Make sure you test your values for validity before inserting them into the database, this goes for all your vars that have a limited range.
Obviously not all ints are valid for farmercode, so let the user have a list of valid farmercodes and make him choose one from the list. That way it's impossible to pick an incorrect farmercode.

Suspected error inserting primary key
You are trying to insert a new record, if slipno is your primary key, you should not allow a value other than null here, if you try to insert an existing key into the table you'll get an error, that should never happen.
It's best to let the database assign the primary key because that's foolproof.

Normalization of database
Why do you have farmercode and farmername in your table? Isn't farmercode the id of a farmer?
If so storing farmername as well is duplication of information.
You should just store the farmerid in the table. The farmer id then points to the primary key of table farmers which stores the name and other relevant info about farmers.

Links:
http://en.wikipedia.org/wiki/Database_normalization
http://databases.about.com/od/specificproducts/a/normalization.htm

Upvotes: 0

David
David

Reputation: 218960

What value is present in

TxtFarmerCode.Text

According to the SQL parameter, it needs to be an integer. Sounds like you need to do some input validation to ensure that it is an integer before passing it to the database.

Upvotes: 0

marc_s
marc_s

Reputation: 754983

The error says it quite clearly - obviously, the text you're trying to convert to an INT is not really an INT number...

Maybe you need to do something more like this:

int farmerCode = -1;

if(int.TryParse(TxtFarmerCode.Text.Trim(), out farmerCode)
{
    cmd.Parameters.Add("@farmercode", SqlDbType.Int).Value = farmerCode;
}
else
{
    cmd.Parameters.Add("@farmercode", SqlDbType.Int).Value = DBNull.Value;  // or whatever makes sense
}

It's a basic principle of defensive programming - don't just assume the conversion will work - it might not! Test it, use int.TryParse, and if your input is not a number, you need to deal with that (passing a NULL or some other mechanism - up to you)

Upvotes: 2

Oleg Grishko
Oleg Grishko

Reputation: 4281

TxtFarmerCode.Text is not a valid int value

Good: int.Parse("123")

Bad: int.Parse("Not an int")

Upvotes: 0

Related Questions