I have an aspx page with a drop down list(ddlCategories), and a datalist(dlLinks). The drop down lists data property is a uniqueidentifier from a table.
When an item in the list is selected it fires the following:
SqlLinks.SelectParameters("CategoryID").DefaultValue = ddlCategories.SelectedValue
dlLinks.DataBind()
The sqldatasource for the datalist runs a stored procedure (below)
sp_GetLinks (@.CategoryID ?) AS
select * from links where category = @.category
My question is, what should @.Category be declared as if the category column in the table is a uniqueidentifier? And what conversion do I need to do I just can't work it out, as I keep getting the following error:
Implicit conversion from data type sql_variant to uniqueidentifier is not allowed. Use the CONVERT function to run this query.
Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.Exception Details:System.Data.SqlClient.SqlException: Implicit conversion from data type sql_variant to uniqueidentifier is not allowed. Use the CONVERT function to run this query.
Source Error:
Line 5: Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlCategories.SelectedIndexChanged
Line 6: SqlLinks.SelectParameters("CategoryID").DefaultValue = ddlCategories.SelectedValue
Line 7: dlLinks.DataBind()
Line 8: End Sub
Line 9: End Class
Source File:C:\Documents and Settings\Karl Walls\My Documents\My Webs\AFRA\links.aspx.vb Line:7
Stack Trace:
[SqlException (0x80131904): Implicit conversion from data type sql_variant to uniqueidentifier is not allowed. Use the CONVERT function to run this query.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +177
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +68
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +199
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2305
System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +31
System.Data.SqlClient.SqlDataReader.get_MetaData() +62
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +294
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1021
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +314
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +20
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +107
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +10
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +7
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +139
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +139
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1659
System.Web.UI.WebControls.BaseDataList.GetData() +53
System.Web.UI.WebControls.DataList.CreateControlHierarchy(Boolean useDataSource) +267
System.Web.UI.WebControls.BaseDataList.OnDataBinding(EventArgs e) +56
System.Web.UI.WebControls.BaseDataList.DataBind() +62
links.DropDownList1_SelectedIndexChanged(Object sender, EventArgs e) in C:\Documents and Settings\Karl Walls\My Documents\My Webs\AFRA\links.aspx.vb:7
System.Web.UI.WebControls.ListControl.OnSelectedIndexChanged(EventArgs e) +75
System.Web.UI.WebControls.DropDownList.RaisePostDataChangedEvent() +124
System.Web.UI.WebControls.DropDownList.System.Web.UI.IPostBackDataHandler.RaisePostDataChangedEvent() +7
System.Web.UI.Page.RaiseChangedEvents() +138
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4507
Try to modify the stored procedure as following:
alter proc sp_GetLinks @.Category sql_variant AS
select * from links where category =convert(uniqueidentifier,@.category)
go
Thanks for the reply, before I saw it i removed this line
SqlLinks.SelectParameters("CategoryID").DefaultValue = ddlCategories.SelectedValue
and it worked fine
