SQL Error caused by .NET Framework incompatibility

[Edward]

Our app was built on VS using .NET Framework version 1.0.3705 It
worked fine until last week when the SysAdmin installed W2k SP4 which,
it appears, installs the .NET Framework version 1.1 This has broken
our code. It was failing with the error :

"Syntax error converting from a character string to uniqueidentifier."

The SQL in question was, in retrospect, probably not very well formed:

"SELECT tblContacts.*, tblCompanies.fldCompanyName FROM tblContacts
INNER JOIN tblCompanies ON tblContacts.fldCompanyID =
tblCompanies.fldCompanyId WHERE (fldContactID = '" & CType(pRecordID,
String) & "')"

If the value of pRecordID was empty, the actual SQL was

"SELECT tblContacts.*, tblCompanies.fldCompanyName FROM tblContacts
INNER JOIN tblCompanies ON tblContacts.fldCompanyID =
tblCompanies.fldCompanyId WHERE (fldContactID = '')"


The function that used this SQL was:

Public Overloads Shared Function ExecuteReader(ByVal connectionString
As String, ByVal commandType As CommandType, ByVal commandText As
String) As SqlDataReader

In the previous (working) version this simply returned Nothing which
was fine - we would test for Nothing, and deduce that this meant a new
record.

Under the new version of .NET Framework, this throws the above error.

Just a heads up if you are interested. We were under the impression
that Visual Studio targetted particular Frameworks, and that versions
1.0.3705 and 1.1 were designed to sit together side by side. Seems
not.

Best

Edward

 

[Cowboy \(Gregory A. Beamer\)]

My suggestion is this. Alter the statement to something like:

Dim sql As String = "SELECT tblContacts.*, tblCompanies.fldCompanyName FROM
tblContacts
INNER JOIN tblCompanies ON tblContacts.fldCompanyID =
tblCompanies.fldCompanyId WHERE (fldContactID = @fldContactID)"

Then, set up a parameter for @fldContactID using a SQLGuid (assuming this is
a uniqueIdentifier from the error message):

Imports System.Data.SqlTypes

....

Dim sg As SqlGuid

Try
sg = new SqlGuid(pRecordId)
Catch
If pRecordId.Trim() = "" Then
sg = SqlGuid.Null
Else
'You figure how to handle garbage here
End If
End Try

'Set up param
cmd.Parameters.Add("@fldContactId", sg)

You now can properly handle a NULL rather than have the Framework cast an
empty as a NULL.

Hope this helps.