Update Query C# MySQL Forms

Question

Before you comment please note that I understand that my code is vulnerable to SQL injection, please disregard any comments about it being vulnerable for purposes of simplicity

I've checked around the website for answers but none seem to fit my situation, many are PHP.

I am trying to update information on a MySQL database from C# Forms Application on Visual Studio 2012, so I've allowed the user to input data but I want them to be able to update their data.

I've tried all sorts of different methods many give me errors, I feel like I'm very close with this method.

string Connection = "server = xxxx; " + "database = xxxxx; " + "uid = xxxx;"+ "pwd = xxxxx;";
        MySqlConnection Conn = new MySqlConnection(Connection);
        try
        {
            MySqlDataAdapter dAdapter = new MySqlDataAdapter("SELECT * FROM example", Conn);

            DataTable dTable = new DataTable(); 
            dAdapter.Fill(dTable);
            DataRow dr = dTable.NewRow();
            dr["TestData1"] = Convert.ToInt32(cboTestData1.Text);
            dr["TestData2"] = txtTestData2.Text;
            dr["TestData3"] = Convert.ToInt32(txtTestData3.Text);
            dTable.Rows.Add(dr);
            string Query = "Update example(field 1, field 2, field 3) VALUES ("TestData1", "TestData2", "TestData3")";
            dTable.Rows.Add(Query);
            MySqlCommandBuilder commandBuilder = new MySqlCommandBuilder(dAdapter);
            int iRowsAffected = dAdapter.Update(dTable);
            if (iRowsAffected == 1)
            {
                MessageBox.Show("Record Added", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            else
            {
                MessageBox.Show("Error adding record", "Record Added", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
        catch (MySqlException ex)
        {
            MessageBox.Show(ex.Message);
        }

The issue is that it doesn't like the 'Query' code due to it being bad. It gives me this error message

Additional information: Input string was not in a correct format.Couldn't store in ID Column. Expected type is Int32.

I've looked around the internet for solutions but all either do not offer the same situation as mine or are related to PHP code.

DRapp · Accepted Answer · 2015-03-11 02:26:40Z

1

The update query should be in a syntax of...

update SomeTable
   set  SomeField = NewValue,
        AnotherField = AnotherValue
   where 
      SomeKey = KeyIDTheUserWasWorkingWith

Also, for future, I know this is sample mach-up data/columns, but you should really use real table / column names. The sample data, we know could be made up to prevent confidentiality, but real structures are more practical to get answers accurate.

The INSERT statement is closer to what you have and is ...

insert into SomeTable
   ( fld1, fld2, fld3 )
   values 
   ( someFld1, anotherFld2, lastField )

Finally, with your column names, if you DO (but I never do), have columns with embedded spaces, be sure to

`wrap in tic marks`

, so the engine recognizes the whole string as the column name.

answered Mar 11, 2015 at 2:26

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Steve Padmore · Accepted Answer · 2015-03-11 03:14:54Z

I think there is some confusion in your code.

The SELECT statement may be bringing back 4 fields such as: ID, TestData1, TestData2, TestData3.

You then fill a DataTable with the records retrieved from the database.

Next, you create a new DataRow in the DataTable (that will have the four columns that match the SELECT statement). You place values into the editable fields (not the ID field).

Then you add the DataRow to the DataTable.

Here its where it gets mixed up...

You create a SQL Update Query String - then add that string as a DataRow to the DataTable.

When updating the DataTable via the MySqlDataAdapter, the last DataRow is not a valid record to be parsed by the Adapter.

Try removing the two lines:

string Query = "Update example(field 1, field 2, field 3) VALUES ("TestData1", "TestData2", "TestData3")";
dTable.Rows.Add(Query);

Collectives™ on Stack Overflow

Update Query C# MySQL Forms

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related