-1

In attempting to do a SQL query (which returns one string and one uniqueidentifier to columns 0 and 1 respectively) I get "Conversion failed when converting from a character string to uniqueidentifier" in my exceptions log. How can I avoid this? I'm assuming the issue is, the datatables columns are not defined, so it's expecting a string and SQL is trying to convert it. The exception is logged. Surprisingly to me the GUID is stored successfully to da[1]. So my program technically works, however I want to clear this exception and to do that I need to understand why it's happening and how to go about fixing it.

da = new DataTable();
da.Clear();
...
string invoiceStatusSQL = @"select status,invoice_id from invoices where acct_id='" + accountid + "'";
command = new SqlCommand(invoiceStatusSQL, cnn);
da.Load(command.ExecuteReader());
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mikael
  • 1,002
  • 1
  • 11
  • 22
  • 5
    **Always use parameterized sql and avoid string concatenation** to add values to sql statements. This mitigates SQL Injection vulnerabilities and ensures values are passed to the statement correctly. See [How can I add user-supplied input to an SQL statement?](https://stackoverflow.com/q/35163361/1260204), and [Exploits of a Mom](https://xkcd.com/327/). – Igor Dec 18 '19 at 18:43
  • 3
    What is `accountid`? Is it `Guid`? You should always parameterise your SQL queries – haldo Dec 18 '19 at 18:45
  • Maybe see [this answer](https://stackoverflow.com/a/1436001/3744182) to [C# guid and SQL uniqueidentifier](https://stackoverflow.com/q/1435908/3744182) and also [Passing uniqueidentifier parameter to Stored Procedure](https://stackoverflow.com/q/11724614/3744182). – dbc Dec 18 '19 at 18:48

1 Answers1

2

You should always parameterise your SQL queries to help prevent SQL injection and avoid problems like you're facing now. See Why do we always prefer using parameters in SQL statements?.

Use SqlParameter to add the parameters to the SqlCommand.

string invoiceStatusSQL = @"select status, invoice_id from invoices where acct_id = @accountId";
command = new SqlCommand(invoiceStatusSQL, cnn);

SqlParameter idParam = new SqlParameter("@accountId", accountid);
command.Parameters.Add(idParam);

da.Load(command.ExecuteReader());

You can also specify the actual database type when creating the parameter, which will reduce any issues you might have with the framework inferring the type incorrectly (although I don't think that would happen in your case for a Guid/UniqueIdentifier). One way to specify the type is shown below. 

var p = new SqlParameter 
            { 
                ParameterName = "@accountId", 
                SqlDbType = SqlDbType.UniqueIdentifier,  
                Value = accountid  
            };
haldo
  • 14,512
  • 5
  • 46
  • 52
  • 1
    Thank you for the answer with examples. This solved the issue. I've read up on it quit a bit in the past hour and you've all taught me a few things. Much appreciated. – Mikael Dec 18 '19 at 20:14
  • 1
    The .Add method has an overload that accepts the `SqlDbType` and another that will accept the type and field size. `command.Parameters.Add("@accountId", SqlDbType.UniqueIdentiifer).Value = accountid` – Mary Dec 19 '19 at 10:12