CODEWITHSUNDEEP
c#sqlms-access
Ajith Rengaraj
Ajith Rengaraj

Reputation: 23

Access Database with C#

I'm very new to C#, and I just learn coding as one of my hobbies.

I'm trying to develop a stand-alone Windows application using Visual Studio and an Access database. I have some difficulties how to access the database. Project is my table in the Access database and projectname and path are my columns in the project table.

I'm trying to read the folders Name in a certain path and writing the name and path of a folder into my table.

How do I compare and insert only the new folder created?

This is showing an error in my insert syntax!

string[] files = Directory.GetDirectories(@"C:\\SomePath\\Project_1\\Project_1\\Resources");
foreach (string file in files)
{

    string name = new DirectoryInfo(file).Name;
    String Root = Path.GetFullPath(file);
    connection_2.Open();
    OleDbCommand ListWrite = new OleDbCommand();
    ListWrite.Connection = connection_2;
    ListWrite.CommandText= "insert into Project (projectname,path) values ('" + name + "','" +Root+ "') where'"
        + name + "' != projectname  ";
    ListWrite.ExecuteNonQuery();
    connection_2.Close();
}

Upvotes: 2

Views: 190

Answers (3)

Derrick Moeller
Derrick Moeller

Reputation: 4950

A WHERE clause is not valid in an insert statement. I assume what you were attempting to do was prevent duplicate project names. One way you can do this in SQL Server is as shown, I've made the assumption it's valid in Access as well but this isn't always the case.

SQL

INSERT INTO Project (projectname, path)
SELECT DISTINCT 'yourpath', 'yourroot'
FROM Project
WHERE NOT EXISTS (SELECT projectname FROM Project WHERE projectname = 'yourpath')

C#

ListWrite.CommandText= "insert into Project (projectname,path) select distinct '" + name + "','" + Root + "') from Project where not exists (select projectname from Project where projectname = '" + name + "'";

As pointed out by LosWochos and apomene, you should also look into parameterized SQL.

Upvotes: 0

apomene
apomene

Reputation: 14389

You just need to leave a space after where:

..where '...

However, the best solution for not having such issues as well as SQL injection is to use parameters:

ListWrite.CommandText= "insert into Project (projectname,path) values (@name, @path)";
ListWrite.Parameters.Add("@name",SqlDbType.NVarChar).Value = name;
ListWrite.Parameters.Add("@path",SqlDbType.NVarChar).Value = Root;

Also notice that the where clause does not have any sense in an insert statement. You have to handle it from code or putting a unique constraint on the projectname column.

Upvotes: 0

LosWochos
LosWochos

Reputation: 342

An insert command does not allow a where statement the way you use it.

You will need to query the table for each of the directory names. If the result of the query is not empty, the specific directory name it is already present in the table. Otherwise, you can insert the new name with an insert.

I would suggest to write a new method for that check:

public bool DoesFolderAlreadyExistInTable(string folder_name, string path, OleDbConnection connection)
{
    using (var ListWrite = new OleDbCommand("select count(*) as c from Project where name=@name and path=@path", connection)) {
        ListWrite.Parameters.AddWithValue("@name", folder_name);
        ListWrite.Parameters.AddWithValue("@path", path);
        var result = ListWrite.ExecuteReader();
        return result.Read() && result.GetInt32(0) > 0;
    }
}

Upvotes: 2

Related Questions