4

I get a "Conversion failed when converting from a character string to uniqueidentifier."

I am using a String on the VB side and a GUID on the database side.....

Is there an equivalent field that I can use on the VB side that can work well with a "uniqueidentifier" data type in the Sql Server

Improve this question
3
  • Possible duplicate: stackoverflow.com/q/13428960/261997 Commented Oct 31, 2013 at 14:52
  • 1
    A uniqueidentifier is a GUID. Apart from that, don't concatenate your query but use sql-parameters to prevent conversion or localization errors and -more important- to prevent sql-injection. Commented Oct 31, 2013 at 14:52
  • How should I handle the empty string parmList.Add(String.Empty) when the table column is a GUID? Commented Oct 31, 2013 at 15:43

1 Answer 1

Reset to default
1

If you already have your value as a string and since you are writing out your SQL by hand, you can CONVERT it like this:

strSql.Append("INSERT INTO tableName ")
strSql.Append("(GUID, ParentObsSetGUID, ChildObsSetGUID, ChildObsItemGUID) ")
strSql.Append(String.Format("VALUES (CONVERT(uniqueidentifier, '{0}'), CONVERT(uniqueidentifier, '{1}'), CONVERT(uniqueidentifier, '{2}'), CONVERT(uniqueidentifier, '{3}'))", parmList.ToArray))

EDIT: If you have an empty string and you need a new Guid, then do this:

parmList.Add(Guid.NewGuid().ToString())

instead of

parmList.Add(String.Empty)

If you would rather insert a NULL into the GUID column, then you need to change the last bit of your code to be like this instead:

parmList.Add(dtNewGUID.Rows(0).Item(0).ToString)
parmList.Add(dtResultParentGUID.Rows(0).Item(0).ToString)
parmList.Add(dtResultChildGUID.Rows(0).Item(0).ToString)
// remove the line with the empty string parameter
strSql.Append("INSERT INTO tableName ")
strSql.Append("(GUID, ParentObsSetGUID, ChildObsSetGUID, ChildObsItemGUID) ")
strSql.Append(String.Format("VALUES (CONVERT(uniqueidentifier, '{0}'), CONVERT(uniqueidentifier, '{1}'),
CONVERT(uniqueidentifier, '{2}'), NULL)", parmList.ToArray))
// Note the change to the last line. '{3}' becomes NULL.
// Make sure you remove the single quotes

NOTE: Your code as it stands (and this answer) is/are vulnerable to a SQL Injection attack, but that's another matter. At least with this answer you know how to convert the string to a uniqueidentifier.

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

1 Comment

The following still gives me the same error: INSERT INTO aic_obs_set_obs_set_obs_item_xref (GUID, ParentObsSetGUID, ChildObsSetGUID, ChildObsItemGUID) VALUES (CONVERT(uniqueidentifier, 'a1771622-6cd6-49f7-8b26-6befafaf556e'), CONVERT(uniqueidentifier, '4746da06-e830-42a0-9761-ce60a62ea4d1'), CONVERT(uniqueidentifier, '633ce901-1d4d-479c-b295-232fe7b53295'), CONVERT(uniqueidentifier, ''))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.