SQL Server 2000, uniqueidentifier (guid) PK columns, and ADO.NET 2.0
-
Hello gurus. I am hoping that one of you will know the answer to this question, as I am at a complete and total loss (even google, ultimate mensa of the net, seems to have a hole in its memory). So, without further ado, here is the problem: I have a simple web app. Its currently 2 pages, written in ASP.NET 2.0 using C# and ADO.NET 2.0. The data store is SQL Server 2000, with a meager 10 tables. All of the tables except one have integer identity PK columns, and the one unique one has a uniqueidentifier (guid) PK column. If I query any table by ID directly from SQL Server (enterprise manager or query analyzer) I can query all of the tables without problems. HOWEVER, when I query the tables from ADO.NET 2.0 (or directly from Visual Studio 2005's server explorer, which I beleive uses ADO.NET 2.0), I am getting odd behavior from the table with the uniqueidentifier PK. Oh, and BTW, all of the queries are in stored procedures, which are called from C# code. When I query the uniqueidentifier table with a simple stored proc containing one parameter (for the guid ID of the row I want to retrieve) and a SELECT statement, ADO.NET 2.0 can't seem to handle this properly. Instead of getting a single row for the record that has a matching ID, I get a result set that looks valid, except it has no data. When I try to use a SqlDataReader to read the fields of the row thats returned, everything seems to work right, but I ultimately get an InvalidCastException. An example (or rather, actual) stored procedure is:
CREATE PROCEDURE dbo.spDSB_GetSuperbillHeader
(
@AppointmentID uniqueidentifier
)
AS
BEGIN
SELECT
a.AppointmentID,
a.ApptDate,
a.PatientID,
a.DoctorID,
a.FacilityID,
a.ApptReason,
a.ApptComments,
a.RefDocName,
a.RespPartyName,
a.FeeSchedule,
a.PriInsName,
a.SecInsName,
a.Copay,
a.PtBalance,
a.AcctComments,
d.DoctorName,
f.FacilityName,
f.Address AS FacilityAddress,
f.CityStateZip AS FacilityCSZ,
f.PhoneNo AS FacilityPhone,
f.TaxID,
p.PatientName,
p.Address AS PatientAddress,
p.CityStateZip AS PatientCSZ,
p.HomePhone,
p.BusinessPhone,
p.MobilePhone,
p.BirthDate,
p.SSN
FROM
tblAppointments a
INNER JOIN tblDoctors d ON a.DoctorID = d.DoctorID
INNER JOIN tblFacilities f ON a.FacilityID = f.FacilityID
INNER JOIN tblPatients p ON a.PatientID = p.PatientID
WHERE
a.AppointmentID = @AppointmentID
ENDThe resulting error details are:
Description: An un
-
Hello gurus. I am hoping that one of you will know the answer to this question, as I am at a complete and total loss (even google, ultimate mensa of the net, seems to have a hole in its memory). So, without further ado, here is the problem: I have a simple web app. Its currently 2 pages, written in ASP.NET 2.0 using C# and ADO.NET 2.0. The data store is SQL Server 2000, with a meager 10 tables. All of the tables except one have integer identity PK columns, and the one unique one has a uniqueidentifier (guid) PK column. If I query any table by ID directly from SQL Server (enterprise manager or query analyzer) I can query all of the tables without problems. HOWEVER, when I query the tables from ADO.NET 2.0 (or directly from Visual Studio 2005's server explorer, which I beleive uses ADO.NET 2.0), I am getting odd behavior from the table with the uniqueidentifier PK. Oh, and BTW, all of the queries are in stored procedures, which are called from C# code. When I query the uniqueidentifier table with a simple stored proc containing one parameter (for the guid ID of the row I want to retrieve) and a SELECT statement, ADO.NET 2.0 can't seem to handle this properly. Instead of getting a single row for the record that has a matching ID, I get a result set that looks valid, except it has no data. When I try to use a SqlDataReader to read the fields of the row thats returned, everything seems to work right, but I ultimately get an InvalidCastException. An example (or rather, actual) stored procedure is:
CREATE PROCEDURE dbo.spDSB_GetSuperbillHeader
(
@AppointmentID uniqueidentifier
)
AS
BEGIN
SELECT
a.AppointmentID,
a.ApptDate,
a.PatientID,
a.DoctorID,
a.FacilityID,
a.ApptReason,
a.ApptComments,
a.RefDocName,
a.RespPartyName,
a.FeeSchedule,
a.PriInsName,
a.SecInsName,
a.Copay,
a.PtBalance,
a.AcctComments,
d.DoctorName,
f.FacilityName,
f.Address AS FacilityAddress,
f.CityStateZip AS FacilityCSZ,
f.PhoneNo AS FacilityPhone,
f.TaxID,
p.PatientName,
p.Address AS PatientAddress,
p.CityStateZip AS PatientCSZ,
p.HomePhone,
p.BusinessPhone,
p.MobilePhone,
p.BirthDate,
p.SSN
FROM
tblAppointments a
INNER JOIN tblDoctors d ON a.DoctorID = d.DoctorID
INNER JOIN tblFacilities f ON a.FacilityID = f.FacilityID
INNER JOIN tblPatients p ON a.PatientID = p.PatientID
WHERE
a.AppointmentID = @AppointmentID
ENDThe resulting error details are:
Description: An un
Well, for anyone who runs into the same problem, the solution is simple, if odd. Seems you can't directly pass a Guid to a stored proc using an ADO.NET parameter. To get the stored procedure to work, I had to do the following: cmd.Parameters.Add("@param", SqlDbType.VarChar, 40); cmd.Parameters["@param"].Value = myGuid.ToString("D"); This sends the guid as a string value in the form {xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}. You must be sure to explicityly set the varchar length to 40, as setting it to 38 (which I did when I originally tried this, since I thought the bounding { and } would be invalid) will still cause the call to fail. You can keep the type of the parameter in your stored procedure as uniqueidentifier. You do not have to change it to varchar, and in fact, if you do, the stored procedure will fail since varchar(40) won't match the uniqueidentifier type of the column.