Error MySql You have an error in your SQL syntax. when adding a parameter with a variable

Question

Syntax error when adding a parameter with textBox1.Text.I want to add a column to the database. How do I do it right. Write the correct code please

private async void button1_Click(object sender, EventArgs e)
{      
    MySqlCommand command = new MySqlCommand("ALTER TABLE Students ADD COLUMN ? TEXT", sqlConnection);

    command.Parameters.AddWithValue("?", textBox1.Text); 

    try
    {
        await command.ExecuteNonQueryAsync();
        Close();

    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }

}

Screenshot https://i.paste.pics/77ce28a6fd93598b0939bc43a1d15b9a.png

Screenshot 2 result answer https://i.paste.pics/a9e8c0739bcbaf6cf7a23ca62a9c9812.png

This is not C at all. Did you mean C++?

klutt
– klutt

2019-05-08 08:08:30 +00:00
Commented May 8, 2019 at 8:08 — klutt
– klutt, Commented May 8, 2019 at 8:08
you can't use parameter for object name (table and columns)

ScaisEdge
– ScaisEdge

2019-05-08 08:09:07 +00:00
Commented May 8, 2019 at 8:09 — ScaisEdge
– ScaisEdge, Commented May 8, 2019 at 8:09
Is '?' a legal name for a column?

David C. Rankin
– David C. Rankin

2019-05-08 08:09:18 +00:00
Commented May 8, 2019 at 8:09 — David C. Rankin
– David C. Rankin, Commented May 8, 2019 at 8:09
@Broman C# I just could have made a mistake somewhere

Серега Wend
– Серега Wend

2019-05-08 09:55:14 +00:00
Commented May 8, 2019 at 9:55 — Серега Wend
– Серега Wend, Commented May 8, 2019 at 9:55

kara · Accepted Answer · 2019-05-08 11:29:33Z

1

Parameters can't be used to modify column- or table-names. You have to build your query manually. But don't forget to escape the user-input, to avoid sql-injections:

string escapedStringColumnName = MySql.Data.MySqlClient.MySqlHelper.EscapeString(textBox1.Text);

// Do some more validations, what text you got before building you column..
if (!new Regex("[a-zA-z ]+").IsMatch(escapedStringColumnName))
    throw new Exception();

MySqlCommand command = new MySqlCommand("ALTER TABLE Students ADD COLUMN `" + escapedStringColumnName + "` TEXT", sqlConnection);

edited May 8, 2019 at 11:29

answered May 8, 2019 at 8:15

kara

3,4854 gold badges23 silver badges39 bronze badges

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

4 Comments

Серега Wend Over a year ago

Screenshot 2 result answer i.paste.pics/a9e8c0739bcbaf6cf7a23ca62a9c9812.png

kara Over a year ago

You're not allowed to select a columnname with spaces. Try "asdasd" instead of "asd asd".

Серега Wend Over a year ago

I need with spaces, create a column. For example, the column name will be "Next day"

kara Over a year ago

You can quote columnnames with `. I modified the example to make it work with spaces. But be aware: Spaces can bring up problems. You have to quote every time ..

Collectives™ on Stack Overflow

Error MySql You have an error in your SQL syntax. when adding a parameter with a variable

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related