Using INSERT sql command with Access database in C#

Question

I have everything else setup but now i simply need to add an entry to my table in access.It would be simple to just show my data in a data grid view and use a binding source with a binding navigator, but i here is the scenario : I have to tables, One with Song Name , Artist and Genre and another table with only genre, the two tables have a relationship(with referential integrity) with relation to genre. I would like to call a inputbox in a C# program to simply add a new genre to the genre table. Now since there is no data grid view or any other control to easily add a entry, what is the simple procedure to add a entry into your table?

Here is my code thus far for the appropriate event handler :

private void newGenreToolStripMenuItem_Click(object sender, EventArgs e)
{
    //(cnSongs)Connection delacred and instantiated elsewhere
    cnSongs.Open();

    string input = Interaction.InputBox("Genre ", "New Genre", "Type genre here", -1, -1);

    string InsertSql = "INSERT INTO tblGenres (Genre) VALUES (" + input + ")";

    OleDbCommand cmdInsert = new OleDbCommand(InsertSql, cnSongs);

    //(daSongs)Data Adapter delacred and instantiated elsewhere
    daSongs = new OleDbDataAdapter(InsertSql, cnSongs);

    daSongs.InsertCommand = cmdInsert;

    cmdInsert.ExecuteNonQuery();
    cnSongs.Close();
}

I did research and only got the sql statement needed, which was useful but i need to know how to exacute it in code.

Thank you for your time.

This looks right apart from two things. Firstly, the two lines with daSongs are irrelevant (the comment says daSongs is instantiated elsewhere, clearly it is being instantiated right there). Secondly, you should learn how to use OleDbParameter as what you have is vulnerable to SQL injection. What problem are you seeing? Is there an error message? — Laurence
– Laurence, Commented Sep 22, 2013 at 14:29
Sorry i misspoke, both the connection and adapter is declared(but not instantiated) Globally, but they are only instantiated where needed(the connection in the form load event handler and the adapter in the above event handler). I do not get a error, but when i run and the input box comes up, i enter a value, quit the program and go the the access file but the value is not added to the table. So my code is syntactically sound but i have a logic error somewhere. Can you explain a simple OleDbParameter to me? Thanx — Mordacai1000
– Mordacai1000, Commented Sep 22, 2013 at 15:45

Laurence · Accepted Answer · 2013-09-22 15:57:28Z

1

This is an example of how to use OleDbParameter. However, I can't see why your code wouldn't produce new genres.

private void newGenreToolStripMenuItem_Click(object sender, EventArgs e)
{
    //(cnSongs)Connection delacred and instantiated elsewhere
    cnSongs.Open();
    try {

        string input = Interaction.InputBox("Genre ", "New Genre", "Type genre here", -1, -1);

        Console.WriteLine("User input " + input);
        string InsertSql = "Insert Into tblGenres (Genre) Values (?)";

        OleDbCommand cmdInsert = new OleDbCommand(InsertSql, cnSongs);

        cmdInsert.Parameters.Add(new OleDbParameter("genre", input));

        int i = cmdInsert.ExecuteNonQuery();
        Console.WriteLine("Inseted " + i.toString() + " row(s)");
    } finally {
        cnSongs.Close();
    }
}

answered Sep 22, 2013 at 15:57

Laurence

11k1 gold badge28 silver badges36 bronze badges

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

2 Comments

Mordacai1000 Over a year ago

It Works! i understand it now. We started using databases only recently, so i'm still a bit new to the field. Thank you very much for your input

Laurence Over a year ago

No problem. You probably don't want the Console.WriteLine statements in the finished product.

Collectives™ on Stack Overflow

Using INSERT sql command with Access database in C#

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related