CODEWITHSUNDEEP
c#sql-serversqlcommand
Ranvoxrat
Ranvoxrat

Reputation: 21

How to fix input string was not in a correct format in sql on C#?

public static String AccountNumber;
public static double oldBalance, newBalance;

SqlConnection connection = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\CSharp\ATM\DB\LoginDB.mdf;Integrated Security=True;Connect Timeout=30");

private void iconButton1_Click(object sender, EventArgs e)
{
        newBalance = oldBalance + Convert.ToInt32(txtAmmount.Text);
        try
        {  
            connection.Open();
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;              
            adapter.SelectCommand = new SqlCommand("UPDATE Account SET AccBalance ='" + Convert.ToDouble(newBalance) + "'WHERE AccountNum =" + txtAccount.Text.Trim() + "", connection);
            DataTable data = new DataTable();
            adapter.Fill(data);
            if (data.Rows.Count >0)
            {
                MessageBox.Show("Deposit Success", "Deposit", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            else
            {
                MessageBox.Show("Deposit Faild", "Deposit", MessageBoxButtons.OK, MessageBoxIcon.Warning);

            }
            

            connection.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show(" " + ex, "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
}      

private void GetBalance()
{
        try
        {
            connection.Open();
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            AccountNumber =Form1.AccountNumber;
            int accounts=Convert.ToInt32(AccountNumber);
            adapter.SelectCommand = new SqlCommand("SELECT AccBalance FROM Account WHERE AccountNum='" + accounts + "'", connection); 
            DataTable data = new DataTable();
            adapter.Fill(data);
            oldBalance = Convert.ToInt32(data.Rows[0][0].ToString());
            connection.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Warning Message!", MessageBoxButtons.OK, MessageBoxIcon.Warning);
        }
}

private void Form3_Load(object sender, EventArgs e)
{
    GetBalance();
}

When I load the Form, I get an error

Input string was not in a correct format

Upvotes: 1

Views: 5037

Answers (1)

Võ Quang Hòa
Võ Quang Hòa

Reputation: 3024

I think the error is here

UPDATE Account SET AccBalance ='" + Convert.ToDouble(newBalance) + "'WHERE AccountNum =" + txtAccount.Text.Trim() + ""

In the runtime, for example, newBalance = 1 and then txtAccount.Text = "My name" the result will be

UPDATE Account SET AccBalance ='1'WHERE AccountNum =My Name

As you can see:

  • No space between 1' and WHERE
  • No ' before and after "My Name"

That's why the code can't work and now you know how to fix it.

As some comment already above, you should use SQLCommand Params instead of building raw SQL string https://csharp-station.com/Tutorial/AdoDotNet/Lesson06

Upvotes: 0

Related Questions