I am starting to use guid's as primary keys instead of incrementing int's. I have a page that needs to fill a gridview which has a select parameter of a guid. Guid is not an object type you can choose while defining your parameter so I first tried passing it as an Object type, thinking that the conversion would happen behind the scenes. It didn't. I did some research on how to make this work and found a couple of articles on how to create custom parameter types. The articles were a little confusing and I ended up not being able to figure it out. Instead I did it my way - the easy way! I passed my parameter as a string and then converted it to a uniqueidentifier in my stored procedure. In my procedure I declared the parameter as a varchar with a size of 36. You can
read more about the t-sql convert function if you'd like, or just copy my example. Here is my procedure:
ALTER PROCEDURE [dbo].[Version_Fill]
@Application varchar(36)
AS
DECLARE @guid uniqueidentifier
SELECT @guid = CONVERT(uniqueidentifier, @Application)
SELECT * FROM [Version] WHERE [Application]=@guid ORDER BY ReleaseDate DESC
Here is my data source object:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:rushapps %>" SelectCommand="Version_Fill" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:QueryStringParameter DefaultValue="0" Name="Application" QueryStringField="ID" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
This is the easiest way, in my opinion.
7 comments:
You can also simply define the guid field in your table as uniqueidentifier instead of string.
Mhasan
I have the same problem but my field in the table is a uniqueidentifier so that isn't the source of the problem. I'll try solution above and keep my fingers crossed but would like to understand why this is happening as casting to an object in .NET code doesn't stop the problem when it should.
What you should do is cast it to a GUID in your .NET code. To do this you use the GUID type, for example:
Guid myGuid = new Guid(userId.ToString();
Now you simply apply the myGuid instance as a parameter to your command object. Of course you could just do the cast whilst applying it as a parameter to the command object so it is all done in one step. In any case now there is no need to cast in your stored procedure.
Whoops, missed out the closing parentheses above, it should be:
Guid myGuid = new Guid(userId.ToString());
I just removed the "Type" of the querystringparamater. That worked for me.
I just made sure the parameter in the stored procedure was a uniqueIdentifier and that the value in the .net code was a string.
--- Stored procedure parameter ----
@LocationID uniqueidentifier
------------- C# Code ------------ SqlConnection conn = new SqlConnection(connStr);
conn.Open();
SqlCommand dCmd = new SqlCommand("Select_yourstoredprocedure", conn);
SqlDataAdapter dAd = new SqlDataAdapter();
dCmd.CommandType = CommandType.StoredProcedure;
dCmd.Parameters.AddWithValue("@AccountID", dealerManufacturerBrand.Id);
// create a new data adapter based on the specified query.
//set the SelectCommand of the adapter
dAd.SelectCommand = dCmd;
// create a new DataTable
DataTable dtGet = new DataTable();
//fill the DataTable
dAd.Fill(dtGet);
//return the DataTable
return dtGet;
perfect.. thank you
Post a Comment