Error Retrieving Single Data Using ExecuteScalar Method

[wonder_gal]

Hi, I received this error "No default member found for type 'Guid'." when I execute my program.

db = New SqlConnection(GetConnectionString())
db.Open()

Dim strSQL As String
strSQL = "SELECT GUID FROM MyLog WHERE MyID = '" & strID & "'"

strGUIDTracker = GetSingleData(strSQL)

..................


Function GetSingleData(ByVal sqlText As String) As String
        Dim sd As String
        Dim oConn As SqlConnection
        oConn = New SqlConnection(GetConnectionString())
        Dim strSQLCmd As SqlCommand = New SqlCommand(sqlText, oConn)
        strSQLCmd.Connection.Open()
        [B]sd = strSQLCmd.ExecuteScalar(System.Data.CommandBehavior.CloseConnection)[/B]
        Return sd
End Function
[FONT=verdana,geneva,lucida,'lucida grande',arial,helvetica,sans-serif][/FONT]
[COLOR=Black]

[/COLOR]

The highlighted line is the statement where the error occured at. I've
tested my SQL statements, it really contains a single record.

Can someone please kindly explain to me why am I getting this error here? Thanks alot.

 

[cjard]

try this instead:

db = New SqlConnection(GetConnectionString())
db.Open()

Dim strSQL As String
strSQL = "SELECT GUID FROM MyLog WHERE MyID = '" & strID & "'"

strGUIDTracker = GetSingleData(strSQL)

..................


Function GetSingleData(ByVal sqlText As String) As Object
        Dim o As Object
        Dim oConn As SqlConnection
        oConn = New SqlConnection(GetConnectionString())
        Dim strSQLCmd As SqlCommand = New SqlCommand(sqlText, oConn)
        strSQLCmd.Connection.Open()
        o[B] = strSQLCmd.ExecuteScalar(System.Data.CommandBehavior.CloseConnection)[/B]
        Return o
End Function

I suspect there isnt a way of converting the returned object to a string. What I'd like you to do is put a breakpoint on the return line, then investigate exactly what it is you get back.. i.e. what o actually is.. (It's something boxed up as an object)

 

[wonder_gal]

Hi cjard, thanks for your reply. I tried ur suggestion, but i still receive the same error, "No default member found for type 'Guid".

Actually the GetSingleData function works pretty fine in my other applications(I used this function before, no such problem).

Function GetSingleData(ByVal sqlText As String) As String
        Dim sd As String
        Dim oConn As SqlConnection
        oConn = New SqlConnection(GetConnectionString())
        Dim strSQLCmd As SqlCommand = New SqlCommand(sqlText, oConn)
        strSQLCmd.Connection.Open()
        sd = strSQLCmd.ExecuteScalar(System.Data.CommandBehavior.CloseConnection)
        Return sd
End Function

But I'm wondering why this time I'm getting this error. I doubt this is due to I'm retrieving a unique identifier value this time. Any idea? Thanks.

 

[cjard]

what is the column type, in the datbase, of GUID?

 

[wonder_gal]

GUID is my column name, its data type is unique identifier.

 

[cjard]

in what rdbms? SQLS?

 

[wonder_gal]

I'm using MS SQL.

 

[cjard]

That you've used the function before doesnt count for much in this case because the return value from the scalar query is an object of type System.Guid

System.Guid has no default member, as the error states, so you cannot just refer to the instance name and expect vb to know what you want.

In cases where system.Guid is the object in use, you are dealing with a complex multipart type. If you want a string representation of it you must call the ToString() method


I really really really do not recommend that you write functions like this, that run a scalar query and return a string result. In selling everything out to be a string, you lose the whole point of an object oriented language in the first place...

If anything you should write several functions:

GetScalarString(sqlText)
GetScalarGuid(sqlText)
GetScalarInteger(sqlText)
...
GetScalarBoolean(sqlText)


but then you realise that its kinda silly to write your data access layers in this way; we dont store arrays full of SQLs and run them like this. We store arrays full of properly created parameterized queries, change the values and run them..

For more info on param queries, read the PQ link in my signature.. For a better idea of how a data access layer should be done, have a look at what VB2005 generates as dataset back end code (tableadapters) - imitating this is no bad thing..