Hey all, I am currently fiddling around with SQL/Access statements in C# and created a very simple dictionary program that will search for the words depending on the length of each text entered in one text box. So when the user presses the search button it the program will search for the word according to letter or by words itself. I use this data access class to search my database (which is an access database). Here is the class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace DictionarySystemLibrary
{
    public class OLEDBDataAccess : IDataAccess
    {
        private static string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
                                                 @"Data Source=C:\Documents and Settings\Dictionary.accdb;" + 
                                                 @"Persist Security Info=False;";

        private string word_InsertStatement = @"INSERT INTO Word (Word, Definition, PartOfSpeech) " + 
                                              @"VALUES (?, ?, ?)";

        private string dictionary_SelectAll = @"SELECT * " + 
                                              @"FROM Word";

        private string dictionary_SelectByWord = @"SELECT [WordID], [Word], [Definition], [PartOfSpeech] " +
                                                 @"FROM [Word] " +
                                                 @"WHERE [Word] LIKE '" + @"?" + "*'";

        private string dictionary_SelectByOneLetter = @"SELECT [Word], [Definition], [PartOfSpeech] " +
                                                      @"FROM Word " +
                                                      @"WHERE [Word] LIKE '" + @"?" + @"*'";

        private string dictionary_SelectByWordID =    @"SELECT [Word], [Definition], [PartOfSpeech] " +
                                                      @"FROM [Word] " +
                                                      @"WHERE [WordID] = ?";

        public List<Word> GetListOfWords()
        {
            List<Word> listOfWords = new List<Word>();

            OleDbConnection connectionGate = new OleDbConnection(connectionString);
            connectionGate.Open();
            
            OleDbDataAdapter dataBridge = new OleDbDataAdapter(dictionary_SelectAll, connectionGate);
            DataSet currentDataSet = new DataSet(dictionary_SelectAll);

            dataBridge.Fill(currentDataSet);

            foreach (DataRow currentDataRow in currentDataSet.Tables[0].Rows)
            {
                Word currentWord = new Word();

                currentWord.WordID = Convert.ToInt64(currentDataRow["WordID"].ToString());
                currentWord.WordName = currentDataRow["Word"].ToString();
                currentWord.WordDefinition = currentDataRow["Definition"].ToString();
                currentWord.WordPartOfSpeech = (PartOfSpeech)Enum.Parse(typeof(PartOfSpeech), currentDataRow["PartOfSpeech"].ToString());

                listOfWords.Add(currentWord);
            }
            connectionGate.Close();

            return listOfWords;
        }

        public Word GetWordByWholeWord(string currentWordName)
        {
            Word returningWord = new Word();
            
            OleDbConnection connectionGate = new OleDbConnection(connectionString);
           
            OleDbCommand commandLine = new OleDbCommand(dictionary_SelectByWord, connectionGate);
            commandLine.Parameters.Add("Word", OleDbType.VarWChar, 255).Value = currentWordName;

            OleDbDataReader dataReader = null;
            
            connectionGate.Open();
            dataReader = commandLine.ExecuteReader();

            dataReader.Read();
            if (dataReader.HasRows)
            {
                returningWord.WordID = Convert.ToInt64(dataReader["WordID"].ToString());
                returningWord.WordName = dataReader["Word"].ToString();
                returningWord.WordDefinition = dataReader["Definition"].ToString();
                returningWord.WordPartOfSpeech = (PartOfSpeech)Enum.Parse(typeof(PartOfSpeech), dataReader["PartOfSpeech"].ToString());
            }

            else
            {
                returningWord = null;
            }
            connectionGate.Close();

            return returningWord;
        }

        public Word GetWordByLetters(string currentWordLetters)
        {
            Word returningWord = new Word();
            
            OleDbConnection connectionGate = new OleDbConnection(connectionString);
            
            OleDbCommand commandLine = new OleDbCommand(dictionary_SelectByOneLetter, connectionGate);
            commandLine.Parameters.Add("Word", OleDbType.VarWChar, 255).Value = currentWordLetters;

            OleDbDataReader dataReader = null;
            connectionGate.Open();
            dataReader = commandLine.ExecuteReader();
            dataReader.Read();
            if (dataReader.HasRows)
            {
                returningWord.WordID = Convert.ToInt64(dataReader["WordID"].ToString());
                returningWord.WordName = dataReader["Word"].ToString();
                returningWord.WordDefinition = dataReader["Definition"].ToString();
                returningWord.WordPartOfSpeech = (PartOfSpeech)Enum.Parse(typeof(PartOfSpeech), dataReader["PartOfSpeech"].ToString());
            }

            else
            {
                returningWord = null;
            }

            connectionGate.Close();


            return returningWord;
        }

        public Word GetWordByWordID(long wordID)
        {
            Word returningWord = new Word();

            OleDbConnection connectionGate = new OleDbConnection(connectionString);

            OleDbCommand commandLine = new OleDbCommand(dictionary_SelectByWordID, connectionGate);
            commandLine.Parameters.Add("WordID", OleDbType.VarWChar, 255).Value = wordID;

            OleDbDataReader dataReader = null;
            connectionGate.Open();
            dataReader = commandLine.ExecuteReader();
            dataReader.Read();
            if (dataReader.HasRows)
            {
                returningWord.WordID = wordID;
                returningWord.WordName = dataReader["Word"].ToString();
                returningWord.WordDefinition = dataReader["Definition"].ToString();
                returningWord.WordPartOfSpeech = (PartOfSpeech)Enum.Parse(typeof(PartOfSpeech), dataReader["PartOfSpeech"].ToString());
            }

            else
            {
                returningWord = null;
            }

            connectionGate.Close();


            return returningWord;
        } 

        public void AddWord(Word currentWord)
        {
            OleDbConnection connectionGate = new OleDbConnection(connectionString);
            
            OleDbCommand commandLine = new OleDbCommand(word_InsertStatement, connectionGate);
            commandLine.Parameters.Add("Word", OleDbType.VarWChar, 255).Value = currentWord.WordName;
            commandLine.Parameters.Add("Definition", OleDbType.VarWChar, 255).Value = currentWord.WordDefinition;
            commandLine.Parameters.Add("PartOfSpeech", OleDbType.VarWChar, 255).Value = currentWord.WordPartOfSpeech.ToString();

            connectionGate.Open();
            commandLine.ExecuteNonQuery();
            connectionGate.Close();
        }

        

    }
}

I know that my search is not that correct because it only retrieves one word per search, I will be editing the code to make it retrieve a list of words later instead. I do not know still why my data access can't retrieve even just a single word though. I mean if the data reader does extract a single row from the database it must retrieve at least one word right? I used the '*' sign for my like statements because when I use '%' it doesn't retrieve anything. I created a query in Access while using the '*' symbol and it works, I then copy pasted my query in C# and surprisingly it does not work.

Input, suggestions, and solutions will surely be appreciated. Thank you.

Edited 6 Years Ago by ticktock: n/a

I edited my code to return multiple words if ever there were multiple results in the query but I still could not return any rows from the query.. Here is my new data access class:

public class OLEDBDataAccess : IDataAccess
    {
        private static string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
                                                 @"Data Source=G:\My Documents\Dictionary\Dictionary.accdb;" + 
                                                 @"Persist Security Info=False;";

        private string word_InsertStatement = @"INSERT INTO WordBank (Word, Definition, PartOfSpeech) " + 
                                              @"VALUES (?, ?, ?)";

        private string dictionary_SelectAll = @"SELECT * " +
                                              @"FROM [WordBank]";

        private string dictionary_SelectByWord = @"SELECT [Word], [Definition], [PartOfSpeech] " + 
                                                 @"FROM [WordBank] " + 
                                                 @"WHERE [Word] LIKE """ + @"?" + @"*""";

        private string dictionary_SelectByOneLetter = @"SELECT [Word], [Definition], [PartOfSpeech] " +
                                                      @"FROM [WordBank] " +
                                                      @"WHERE [Word] LIKE """ + @"?" + @"*""";

        private string dictionary_SelectByWordID =    @"SELECT [Word], [Definition], [PartOfSpeech] " +
                                                      @"FROM [WordBank] " +
                                                      @"WHERE [WordID] = ?";

        public List<Word> GetListOfWords()
        {
            List<Word> listOfWords = new List<Word>();

            OleDbConnection connectionGate = new OleDbConnection(connectionString);
            connectionGate.Open();
            
            OleDbDataAdapter dataBridge = new OleDbDataAdapter(dictionary_SelectAll, connectionGate);
            DataSet currentDataSet = new DataSet(dictionary_SelectAll);

            dataBridge.Fill(currentDataSet);

            foreach (DataRow currentDataRow in currentDataSet.Tables[0].Rows)
            {
                Word currentWord = new Word();

                currentWord.WordID = Convert.ToInt64(currentDataRow["WordID"].ToString());
                currentWord.WordName = currentDataRow["Word"].ToString();
                currentWord.WordDefinition = currentDataRow["Definition"].ToString();
                currentWord.WordPartOfSpeech = (PartOfSpeech)Enum.Parse(typeof(PartOfSpeech), currentDataRow["PartOfSpeech"].ToString());

                listOfWords.Add(currentWord);
            }

            connectionGate.Close();

            return listOfWords;
        }

        public List<Word> GetWordByWholeWord(string currentWordName)
        {
            List<Word> returningListOfWords = new List<Word>();
            
            OleDbConnection connectionGate = new OleDbConnection(connectionString);
           
            OleDbCommand commandLine = new OleDbCommand(dictionary_SelectByWord, connectionGate);
            commandLine.Parameters.Add("Word", OleDbType.VarWChar, 255).Value = currentWordName;

            OleDbDataReader dataReader = null;
            
            connectionGate.Open();
            dataReader = commandLine.ExecuteReader();
            dataReader.Read();

            if (dataReader.HasRows)
            {
                while (dataReader.Read())
                {
                    Word currentWord = new Word();
                    currentWord.WordID = Convert.ToInt64(dataReader["WordID"].ToString());
                    currentWord.WordName = dataReader["Word"].ToString();
                    currentWord.WordDefinition = dataReader["Definition"].ToString();
                    currentWord.WordPartOfSpeech = (PartOfSpeech)Enum.Parse(typeof(PartOfSpeech), dataReader["PartOfSpeech"].ToString());

                    returningListOfWords.Add(currentWord);
                }
            }

            else
            {
                returningListOfWords = null;
            }
            dataReader.Close();
            connectionGate.Close();

            return returningListOfWords;
        }

        public List<Word> GetWordByLetters(string currentWordLetters)
        {
            List<Word> returningListOfWords = new List<Word>();
            
            OleDbConnection connectionGate = new OleDbConnection(connectionString);
            
            OleDbCommand commandLine = new OleDbCommand(dictionary_SelectByOneLetter, connectionGate);
            commandLine.Parameters.Add("Word", OleDbType.VarWChar, 255).Value = currentWordLetters;

            OleDbDataReader dataReader = null;
            connectionGate.Open();
            dataReader = commandLine.ExecuteReader();
            dataReader.Read();

            if (dataReader.HasRows)
            {
                while (dataReader.Read())
                {
                    Word currentWord = new Word();
                    currentWord.WordID = Convert.ToInt64(dataReader["WordID"].ToString());
                    currentWord.WordName = dataReader["Word"].ToString();
                    currentWord.WordDefinition = dataReader["Definition"].ToString();
                    currentWord.WordPartOfSpeech = (PartOfSpeech)Enum.Parse(typeof(PartOfSpeech), dataReader["PartOfSpeech"].ToString());

                    returningListOfWords.Add(currentWord);
                }
            }

            else
            {
                returningListOfWords = null;
            }
            dataReader.Close();
            connectionGate.Close();


            return returningListOfWords;
        }

        public Word GetWordByWordID(long wordID)
        {
            Word returningWord = new Word();

            OleDbConnection connectionGate = new OleDbConnection(connectionString);

            OleDbCommand commandLine = new OleDbCommand(dictionary_SelectByWordID, connectionGate);
            commandLine.Parameters.Add("WordID", OleDbType.VarWChar, 255).Value = wordID;

            OleDbDataReader dataReader = null;
            connectionGate.Open();
            dataReader = commandLine.ExecuteReader();
            dataReader.Read();

            if (dataReader.HasRows)
            {
                returningWord.WordID = Convert.ToInt64(dataReader["WordID"].ToString());
                returningWord.WordName = dataReader["Word"].ToString();
                returningWord.WordDefinition = dataReader["Definition"].ToString();
                returningWord.WordPartOfSpeech = (PartOfSpeech)Enum.Parse(typeof(PartOfSpeech), dataReader["PartOfSpeech"].ToString());
            }

            else
            {
                returningWord = null;
            }
            dataReader.Close();
            connectionGate.Close();

            return returningWord;
        } 

        public void AddWord(Word currentWord)
        {
            OleDbConnection connectionGate = new OleDbConnection(connectionString);
            
            OleDbCommand commandLine = new OleDbCommand(word_InsertStatement, connectionGate);
            commandLine.Parameters.Add("Word", OleDbType.VarWChar, 255).Value = currentWord.WordName;
            commandLine.Parameters.Add("Definition", OleDbType.VarWChar, 255).Value = currentWord.WordDefinition;
            commandLine.Parameters.Add("PartOfSpeech", OleDbType.VarWChar, 255).Value = currentWord.WordPartOfSpeech.ToString();

            connectionGate.Open();
            commandLine.ExecuteNonQuery();
            connectionGate.Close();
        }
}

Help and input will surely be appreciated

Edited 6 Years Ago by ticktock: n/a

Hello

Did you work out a solution to this issue as I am having the same problem passing string parameters to an Access query that uses the parameters with 'like' statements.

This article has been dead for over six months. Start a new discussion instead.