0

I am new to C#. I tried to write a case-sensitive sql query for Microsoft Access in C#. Here is part of the table named "EmployeeList". Notice the second record has a lower case for "s":

| ID   | Name      | InternalID |
|:----:|:---------:| :---------:|
| 8    | John Smith| 52455      |
| 9    | John smith| 49         |
| ...  | ...       | ...        |

Here is the query. I tested in Access, and it worked well.

SELECT InternalID FROM EmployeeList WHERE StrComp(Name, 'John Smith', 0) = 0;

What is the correct syntax to write it in C#? I have looked up many sources but my code is still wrong:

public DataTable findInternalID(string name)
        {
            Connection.Open();
            DataTable output = new DataTable();
            OleDbCommand bdCommand = RecordConnection.CreateCommand() as OleDbCommand;
            
            StringBuilder x = new StringBuilder();
            x.AppendLine("SELECT InternalID ");
            x.AppendLine("FROM EmployeeList ");
            x.AppendLine($"WHERE StrComp(Name, @name, 0) = 0");
          
            bdCommand.CommandText = x.ToString();
            bdCommand.Parameters.AddWithValue("name", $"%{name}%");

            _dataAdapter = new OleDbDataAdapter(bdCommand);
            _dataAdapter.Fill(output);

            RecordConnection.Close();
            return output;

        }

and here is the code under main to test my function:

string name = "John Smith";
DataTable output = _accessManager.findInternalID(name);
String message;
message = output.Rows[0][0].ToString();
MessageBox.Show(message);

and this code throws me an error:

System.IndexOutOfRangeException: 'There is no row at position 0.'

Why there's no record written into result?

Emory Lu
  • 77
  • 1
  • 9
  • Don't use string comparison in the where statement. Just send WHERE Name = @name – GH DevOps Feb 15 '22 at 20:56
  • @GHDevOps Hi, thanks for the feedback. Since sql query in MS Access is case insensitive, if I simply use NAME=@name, then it will recognize "John Smith" and "John smith" as the same thing – Emory Lu Feb 15 '22 at 20:59

1 Answers1

0

StrComp is an Access function, not a SQL Server function.

So this will get rid of the error:

commandTextBuilder.AppendLine($"WHERE Name = @name");

However, that's not case-sensitive.

You can use StrComp if you use newer drivers, as per here:

How to write Case Sensitive Query for MS Access?

If this were SQL Server, you'd want to change the column to be case-sensitive. The command to do so looks something like

ALTER TABLE <mytable>
ALTER COLUMN <mycolumn> VARCHAR(<mylength>) 
COLLATE <myencoding>

Just fill in the blanks. myencoding will be whatever the current encoding is, with CI replaced with CS. If you're running defaults, that means going from SQL_Latin1_General_CP1_CI_AS to SQL_Latin1_General_CP1_CS_AS.

Stu
  • 15,675
  • 4
  • 43
  • 74
  • I really appreciate your help. Unfortunately, this ALTER TABLE feature can't be used for MS Access. – Emory Lu Feb 16 '22 at 16:03
  • Amended answer. This question might actually be a duplicate. – Stu Feb 16 '22 at 19:53
  • Thank you very much. I actually figured out how to use `StrComp` in my function, simply change ```bdCommand.Parameters.AddWithValue("name", $"%{name}%");``` to ```bdCommand.Parameters.AddWithValue("name", $"{name}");```. Lol, so the `%` is actually the problem when I tried to do parameterized sql query. – Emory Lu Feb 16 '22 at 22:17