Executing an SQL statement in C#?

Question

Hey guys i want to execute my SQL statement but im having synatx trouble, can someone help me understand what i doin wrong please?

Thanks, Ash.

public void AddToDatabase(string[] WordArray, int Good, int Bad, int Remove)
{

    for (int WordCount = 0; WordCount < WordArray.Length; WordCount++)
    {
        string sSQL = "INSERT INTO WordDef (Word, Good, Bad, Remove) VALUES (" + WordArray[WordCount] + ", " + Good + ", " + Bad + ", " + Remove + ")";

        Debug.Print(sSQL);

        //Private m_recordset As ADODB.Recordset
        //Private m_connection As ADODB.Connection
        ADODB.Recordset RS;
        ADODB.Connection CN ;


        CN = new ADODB.Connection();
        RS = new ADODB.Recordset();

        CN.CursorLocation = ADODB.CursorLocationEnum.adUseClient;

        CN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=doom_calc_dict.mdb;jet OLEDB:database";
        CN.Open(CN.ConnectionString,"","",0);

        object dummy = Type.Missing;

        CN.Execute(sSQL,out dummy,0);

        RS.Close(); 
        CN.Close(); 

        //string sSQL = "SELECT Word FROM WordDef WHERE Word='" + WordArray[WordCount] + "'";
        DatabaseTools.LoadDataFromDatabase(sSQL);
        //DatabaseTools.LoadDataFromDatabase(sSQL);

    }
}

Answer 1

Try this (and you should try running the SQL from outside your application):

string sSQL = "INSERT INTO WordDef (Word, Good, Bad, Remove) VALUES ('" + WordArray[WordCount] + "', " + Good + ", " + Bad + ", " + Remove + ");";

Answer 2

You need to add single quotes around the first argument in the SQL Statement.

string sSQL = "INSERT INTO WordDef (Word, Good, Bad, Remove) VALUES ('" + WordArray[WordCount] + "', " + Good + ", " + Bad + ", " + Remove + ")";

Character and Date fields require values to be surrounded with 'single quotes'

Answer 3

What error are you getting when you try to run this?

Answer 4

It will appear at first that I am not being helpful. But in truth, I am trying to help you, so please take it that way. You need to read this and this STAT! Once you've done that, here are some good, clean ADO.NET examples.

Good luck! :)

Answer 5

The most important thing you need to fix is to use query parameters rather than building the string dynamically. This will improve performance, maintenance, and security.

Additionally, you want to use the newer strongly-typed ADO.Net objects. Make sure to add using directives for System.Data.OleDb.

Notice the using statements in this code. They will make sure your connection is closed when you finish with it. This is important because database connections are a limited and unmanaged resource.

Finally, you're not really using an array in your code. All you really care about is the ability to iterate over a collection of words, and so you want to accept an IEnumerable<string> instead of an array. Don't worry: this function will accept an array as an argument if that's what you need to pass it.

public void AddToDatabase(IEnumerable<string> Words, int Good, int Bad, int Remove)
{
    string sql = "INSERT INTO WordDef (Word, Good, Bad, Remove) VALUES (@Word, @Good, @Bad, @Remove)";

    using (OleDbConnection cn = new OleDbConnection("connection string here") )
    using (OleDbCommand cmd = new OleDbCommand(sql, cn))
    {
        cmd.Parameters.Add("@Word", OleDbType.VarChar);
        cmd.Parameters.Add("@Good", OleDbType.Integer).Value = Good;
        cmd.Parameters.Add("@Bad", OleDbType.Integer).Value = Bad;
        cmd.Parameters.Add("@Remove", OleDbType.Integer.Value = Remove;

        cn.Open();

        foreach (string word in Words)
        {
            cmd.Parameters[0].Value = word;
            cmd.ExecuteNonQuery();
        }
    }
}

One more thing: when using query parameters in OleDb it's important to make sure you add them in order.

Update: Fixed to work on VS 2005 / .Net 2.0 (had relied on VS 2008 features).

Answer 6

No you need it like this:

string query = "INSERT INTO Table_PersonInfo(PersonID,Surname,[Family Name],AddsOnName,Street,Number,PostalCode,[City of Birth],[Year of Birth],[Phone Number]) VALUES ('"+@personID+"', '" + @surname + "' ,'"+@familyname+"', '"+@nameExtension+"', '"+@street+"', '"+@houseNumber+"','"+ @postalCode+"', '"+@yearofbirth+"', '"+@placeofbirth+"', '"+@phoneNumber+"')";