How do I pass a GUID value into an SqlCommand object SQL INSERT statement?

Question

I have an SQL Server database table created by deploying the following description in a .dbproj project:

CREATE TABLE [dbo].[Tasks] ( TaskId uniqueidentifier primary key, State int not null, )

and I want to insert a row into that table with the following code:

using( SqlTransaction transaction = connection.BeginTransaction() ) { using( SqlCommand command = connection.CreateCommand() ) { command.CommandText = "INSERT INTO Tasks VALUES( \"" + Guid.NewGuid().ToString() + "\", 0)"; command.Transaction = transaction; command.ExecuteNonQuery(); transaction.Commit(); } }

when ExecuteNonQuery() runs an exeption is thrown saying

The name [the string representation of the GUID I passed] is not permitted in this context.

What's up? I did the same to insert data into an SQLite table previously and it worked. How do I pass a GUID into an SQL INSERT statement?

Don't use single quotes - use a parametrized query instead! Much safer.... can you spell SQL injection attack? — marc_s
– marc_s, Commented Mar 17, 2011 at 11:03

Marnix van Valen · Accepted Answer · 2011-03-17 11:11:09Z

Use a parameterized query, like so:

command.CommandText = "INSERT INTO Tasks VALUES( @id, 0)"; command.Parameters.Add( "@id", SqlDbType.UniqueIdentifier, 16 ).Value = value;

This way, the database driver formats the value for you. This is a good practice that will also help protect your database from SQL Injection attacks.

Alternatively, you could let the database generate the guid for you:

command.CommandText = "INSERT INTO Tasks VALUES( NEWID(), 0)";

Collectives™ on Stack Overflow

How do I pass a GUID value into an SqlCommand object SQL INSERT statement?

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related