format of the initialization string does not conform to specification at index 33

Question

I have a simple query to update the users information however i get the error stating 'format of the initialization string does not conform to specification at index 33' and it seems to highlight this specific code Connection.Close(); however im not sure why, here is the complete code:

public void AddNewUser()
{
    string filePath;
    try
    {
        filePath = (Application.StartupPath + ("\\" + DBFile));
        connection = new System.Data.OleDb.OleDbConnection((ConnectionString + filePath));
        connection.Open();
        System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand();
        command.Connection = connection;
         // ---set the user's particulars in the table---
        string sql = ("UPDATE enroll SET SSN=\'"
                        + (txtSSN.Text + ("\', " + ("FirstName=\'"
                        + (txtFirstName.Text + ("\', " + ("LastName=\'"
                        + (txtLastName.Text + ("\' "
                        + (" WHERE ID=" + _UserID))))))))));
        command.CommandText = sql;
        command.ExecuteNonQuery();
        MessageBox.Show("Student added successfully!", "Registered");

    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString(), "Error");
    }
    finally
    {
       connection.Close();
    }
}

EDIT:

Here are the file paths:

const string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"C:\\Users\\Zack\\My Documents\\Test\\Database.mdb";

const string DBFile = "C:\\Users\\Zack\\My Documents\\Test\\Database.mdb";

Answer 1

Your command text is wrong and you should use parametirized queries, here is correct version:

command.CommandText = "UPDATE enroll SET SSN= @ssn, FirstName = @fname, LastName = @lastName WHERE ID = @id";
command.Parameters.AddWithValue("@ssn", txtSSN.Text);
command.Parameters.AddWithValue("@fname", txtFirstName.Text);
command.Parameters.AddWithValue("@lastName", txtLastName.Text);
command.Parameters.AddWithValue("@id", _UserID);

And connection string:

string conString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\Users\Zack\My Documents\Test\Database.mdb'";

Answer 2

Zack,

There are quite a number of issues with this code. Primarly if you were to run this (as SLacks states) you are open to sql injection attacks. (Read up on it).

First off.. Your connection string (based on your code) when run will be.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"C:\\Users\\Zack\\My Documents\\Test\\Database.mdb\\C:\\Users\\Zack\\My Documents\\Test\\bin\Debug\\C:\\Users\\Zack\\My Documents\\Test\\Database.mdb

Well that is a guess. You should be using the following (note your path is hard coded).

const string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"{0}\"";

const string DBFile = "Database.mdb";
//...
var connection = new System.Data.OleDb.OleDbConnection(ConnectionString)

If you wanted to make your connection string dynamic to the path try this.

string conString = string.Format(ConnectionString, Path.Combine(Application.StartupPath, DBFile));
var connection = new System.Data.OleDb.OleDbConnection(conString);

This should set your connection string properly to you application startup. Now you may find it more useful to work of the executing assembly path as opposed to the application startup (your call).

Next your queries are a mess. I have cleaned it up to use parameterized queries instead with the resulting code somthing like. (note this has not been tested).

const string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"{0}\"";

const string DBFile = "Database.mdb";
public void AddNewUser()
{
    string conString = string.Format(ConnectionString, Path.Combine(Application.StartupPath, DBFile));
    using (var connection = new System.Data.OleDb.OleDbConnection(conString))
    {
        try
        {
            string sql = "UPDATE enroll SET SSN=@ssn, FirstName=@firstName, LastName=@lastName WHERE ID=@userID";
            System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand(sql, connection);
            command.Parameters.AddWithValue("@ssn", txtSSN.Text);
            command.Parameters.AddWithValue("@firstName", txtFirstName.Text);
            command.Parameters.AddWithValue("@lastName", txtLastName.Text);
            command.Parameters.AddWithValue("@userID", _UserID);

            connection.Open();
            command.ExecuteNonQuery();
            MessageBox.Show("Student added successfully!", "Registered");

        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString(), "Error");
        }
        finally
        {
            connection.Close();
        }
    }
}

EDIT:

I created a test lab for the code above and all ran correctly. Let me know if you have any questions.

Cheers.