-1

I used the following code to insert data into a database

public partial class Products : Form
{
    private OleDbConnection connection = new OleDbConnection();
    public Products()
    {
        InitializeComponent();
        connection.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Folder\Database.mdb;Persist Security Info=False;";
    }
private void cmdAdd_Click(object sender, EventArgs e)
    {
        try
        {
            connection.Open();
            OleDbCommand command = new OleDbCommand();
            command.Connection = connection;
            command.CommandText = "INSERT INTO Products (ProductName, Quantity, Weight(g)) VALUES ('" + txtName.Text + "', '" + txtQuantity.Text + "', '" + txtWeight.Text + "')";
            
            command.ExecuteNonQuery();
            MessageBox.Show("Data saved");
            connection.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error" + ex);
        }
        

    }

And the last two variables don't seem to work. I tried entering a string for the first field and it works perfectly, but when I go to insert a number on the last two variables it throws an exception. I've already tried doing int.Parse(txtQuantity.Text) but it doesn't work either.

Loathing
  • 5,109
  • 3
  • 24
  • 35
Gianella
  • 3
  • 4
  • What is the exception? Edit question to show exact message. Don't use apostrophe delimiters for number field parameter (use # for date/time). Or use declared parameters instead of concatenation https://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work. – June7 Mar 06 '21 at 19:17
  • 1
    Use Command [Parameters](https://learn.microsoft.com/en-us/dotnet/api/system.data.oledb.oledbparametercollection.add?view=dotnet-plat-ext-5.0#System_Data_OleDb_OleDbParameterCollection_Add_System_String_System_Data_OleDb_OleDbType_System_Int32) to build the query, don't (try to) concatenate strings. It will also solve data Type mismatches. -- Write your Fields in square brackets. – Jimi Mar 06 '21 at 19:21
  • Square brackets are only necessary if object name uses spaces or punctuation/special characters (underscore only exception) or are reserved words. However, using can't hurt. – June7 Mar 06 '21 at 19:23
  • Sorry the exception says "ErrorSystem.FormatException: Input string was not in a correct format. at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NUmberFormatInfo info, Boolean parseDecimal) at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info) at System.Int32.Parse(String s) at Database.Products.cmdAdd_Click(Object sender, EventArgs e) in C:Folder.cs:line 58 – Gianella Mar 06 '21 at 19:27
  • 1
    Square brackets are obviously necessary here, `Weight(g)` contains parentheses and that's not allowed so needs to be bracketed. However, there are a ton of other red flags here, such as the risk of SQL injection and use of Access + ASP.Net which is explicitly not supported by Microsoft. – Erik A Mar 06 '21 at 22:04

1 Answers1

1

Change your string by removing ' from the last 2 items:


command.CommandText = "INSERT INTO Products (ProductName, Quantity, Weight(g)) 
VALUES ('" + txtName.Text + "', " + txtQuantity.Text + ", " + txtWeight.Text + ")";
Serge
  • 40,935
  • 4
  • 18
  • 45