ExecuteScalar() Limitations?

Question

I have a GUID (varchar(50,notnull) in sql) That I am returning from SQL via ExecuteScalar() in VB I am storing that value in a string (max of 2gb) in VB I then run a select where GUID = Stored GUID

When i run the program it looks like it trucates the GUID Sample {3F2504E0-4F89-11D3-9A0C-0305E82C3301}

Sampele Error The floating point value '3F2504E0' is out of the range of the computer representation (8bytes)

Is my executeScalar truncating this info?

Code:

 Dim sqlquery As String
        Dim ConnectionString As String
        If cmboxDatabaseName.Text <> "" Then
            ConnectionString = "Server=" + ServerName + "\" + InstanceName + "; Database=" + Control + "; User Id=" + UserId + ";Password=" + Password + ";"
            sqlquery = "Select top 1 GUID from dbo.Databases with(Nolock) where dbName = '" + cmboxDatabaseName.Text + "'"

            'Connect
            Using conn As SqlConnection = New SqlConnection(ConnectionString)
                conn.Open()
                Using comm As SqlCommand = New SqlCommand(sqlquery, conn)
                    Hosted_GUID = comm.ExecuteScalar()
                    conn.Close()
                End Using 'comm
                conn.Close()
            End Using 'conn

Your sample isn't a GUID - A GUID in this format should be hex only, and this has a `P` and `R` in it. — Joe Enos, Jul 08 '13 at 21:17
Correct, I am not as familiar with guids and just typed an arbitrary value. — UPGRAYEDD, Jul 08 '13 at 21:19

Fabian Bigler · Accepted Answer · 2013-07-09T15:29:23.297

4

There are a few things not quite right:

Use UniqueIdentifier for GUID. There is a reason why SQL got this data type. http://en.wikipedia.org/wiki/Globally_unique_identifier
If Hosted_GUID is declared as GUID, then you obviously can't implicitly convert like that!

Hosted_GUID = comm.ExecuteScalar()

=> If you define your GUID-Column as UniqueIdentifier, your problems will suddenly disappear. Though make sure to check if comm.ExecuteScalar is Nothing.

MSDN about ExecuteScalar:

The first column of the first row in the result set, or a null reference.

EDIT: If you can not alter your current Column to UniqueIdentifier, last option would be to convert the string in code to GUID:

Hosted_GUID = new Guid(comm.ExecuteScalar())

Sidenote: Also make sure to use parameterized queries.

edited Jul 09 '13 at 15:29

answered Jul 08 '13 at 21:02

Fabian Bigler

10,403
6
47
70

Is there a conversion step i can go through first while keeping the varchar(50) in sql? – UPGRAYEDD Jul 08 '13 at 21:22
Well yes. You could create a new nullable column and then convert the existing VARCHAR(50) column like answered here: http://stackoverflow.com/questions/1390109/convert-varchar-to-uniqueidentifier-in-sql-server – Fabian Bigler Jul 08 '13 at 21:39
sorry, I need to do the conversion in vb, we would have to release patches if I start changing the sql code. and that is a HUGE ordeal. – UPGRAYEDD Jul 09 '13 at 13:13
@UPGRAYEDD OK. Updated my answer accordingly to show how to convert string to guid in code – Fabian Bigler Jul 09 '13 at 15:30
Long Story short. When I retrieve the guid it starts and Ends with {}. My sql select I was using it in the next step was missing the quotes. *Facepalm. Thank you for your help! – UPGRAYEDD Jul 09 '13 at 19:47

ExecuteScalar() Limitations?

1 Answers1