Conversion failed when converting from string to uniqueidentifier - loading DataTable with ExecuteReader

Question

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());

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?, and Exploits of a Mom. — Igor
– Igor, Commented Dec 18, 2019 at 18:43
What is accountid? Is it Guid? You should always parameterise your SQL queries — haldo
– haldo, Commented Dec 18, 2019 at 18:45
Maybe see this answer to C# guid and SQL uniqueidentifier and also Passing uniqueidentifier parameter to Stored Procedure. — dbc
– dbc, Commented Dec 18, 2019 at 18:48

haldo · Accepted Answer · 2019-12-18 19:21:22Z

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  
            };

edited Dec 18, 2019 at 19:21

answered Dec 18, 2019 at 18:59

haldo

17k8 gold badges57 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mikael Over a year ago

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.

Mary Over a year ago

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

Collectives™ on Stack Overflow

Conversion failed when converting from string to uniqueidentifier - loading DataTable with ExecuteReader

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related