SQL Server, SQL Server Express, and SqlDataSource Control

Re: Guids are not very guid! Updating in DetailsView,FormView (Object must implement IConvertible)

pliant — Sun, 15 Nov 2009 12:43:45 GMT

This works for me:

<asp:Parameter Name="id" DbType="Guid" />

I'm using ASP.NET 3.5 though. Don't know if that makes a difference.

Re: Guids are not very guid! Updating in DetailsView,FormView (Object must implement IConvertible)

andybaird — Sun, 04 Jan 2009 23:29:39 GMT

One of these fixes worked for me, and I found another one here http://www.velocityreviews.com/forums/t362268-guid-primary-key-in-sqldatasoure-updatecommand-wont-work.html. This fix uses the conversion of the Guid (SqlServer type uniqueidentifier) to a string and back again. This worked fine for me.

However the easiest fix that worked was the removing of the Type value for Guid fields - for a non-primary field. This made inserts and updates work in my database.

Hope these help someone else,

Andy.

Re: Guids are not very guid! Updating in DetailsView,FormView (Object must implement IConvertible)

subcientifico — Sun, 09 Nov 2008 11:55:26 GMT

My similar problem solved the following way. Note this is good for tables that have a foreign key that is the UserId provided by ASP Membership.

1. I subclassed System.Web.UI.WebControls.Parameter as below in the GuidParameter class.

2. I added a 'Where" filter to the DataSource using a GuidParameter. I am using a LinqDataSource, but you get the idea from the following. The LinqDataSource does connect to a SqlServer 2005 table that has a Guid column that is a foreign key into the asp membership provider user table. NOTE THE FORM OF THE WHERE CLAUSE using the object method, equals, rather than the operator, ==, or something else.

protected void Page_Load(object sender, EventArgs e)

{

dataContext = new DcWorkspaceDataContext();

MembershipUser mu = Membership.GetUser();

if (null != mu)

{

object oUserId = mu.ProviderUserKey;UserId = (Guid)oUserId;

UserName = mu.UserName;

}

LinqDataSource1.Where = "UserId.Equals(@UserId)";GuidParameter parameter = new GuidParameter("UserId", UserId);

LinqDataSource1.WhereParameters.Clear();

LinqDataSource1.WhereParameters.Add(parameter);

}

//==========================================================================================

// source for GuidParameter

using System.Web.UI.WebControls;

/// <remarks>See the documentation of the Parameter class</remarks>

public class GuidParameter : Parameter

{

public GuidParameter() { } //NECESSARY

public GuidParameter(string name, Guid value): base(name)

{

DataValue = value;

}

// The GuidParameter copy constructor is provided to ensure that

// the state contained in the DataValue property is copied to new

// instances of the class.

protected GuidParameter(GuidParameter original): base(original)

{

DataValue = original.DataValue;

}

// The Clone method is overridden to call the

// GuidParameter copy constructor, so that the data in

// the DataValue property is correctly transferred to the

// new instance of the GuidParameter.

protected override Parameter Clone()

{

return new GuidParameter(this);

}

// The DataValue is a Guid.

private Guid _DataValue = Guid.Empty;public Guid DataValue

{

get

{

return _DataValue;

}

set

{

_DataValue = value;

}

// The Value property is a type safe convenience property

public Guid Value

{

get

{

return _DataValue;

}

set

{

DataValue = value;

OnParameterChanged();

}

// The Evaluate method is overridden to return the

// DataValue property instead of the DefaultValue.

protected override object Evaluate(HttpContext context, Control control)

{

if (context.Request == null)

return null;

return DataValue;

}

Re: Guids are not very guid! Updating in DetailsView,FormView (Object must implement IConvertible)

shtroumf — Sun, 31 Aug 2008 14:04:43 GMT

For the records, i had the same problem.

Here is my situation : VS 2008, SP1

My databse use GUID as primary key

The key is passed to the sqlDatasource with a querystring

The type "selected" by the GUI wizard for the key was "object".

I just deleted the "Type" section ans everything worked for me... I think I was lucky on this one :)

Here is my code for the datasource :

<asp:SqlDataSource ID="DSdetailsOrdinateur_Inventaire" runat="server"

ConnectionString="SQLServerEXpress"

DeleteCommand="DELETE FROM [TBL_COMPUTERS] WHERE [clef] = @clef"

ProviderName="System.Data.SqlClient"

SelectCommand="SELECT [FullName], [SerialNo], [STQNumber], [Locale], [Lieu], [STQEcran], [SerialEcran], [DateAchat], [DureeGarantie], [ExpireLe], [clef], [Nom], [DateDebutGarantie], [Status], [CheckGarantie], [AccuseRecep] FROM [TBL_COMPUTERS] WHERE ([clef] = @clef)"

UpdateCommand="UPDATE [TBL_COMPUTERS] SET [FullName] = @FullName, [SerialNo] = @SerialNo, [STQNumber] = @STQNumber, [Locale] = @Locale, [Lieu] = @Lieu, [STQEcran] = @STQEcran, [SerialEcran] = @SerialEcran, [DateAchat] = @DateAchat, [DureeGarantie] = @DureeGarantie, [ExpireLe] = @ExpireLe, [Nom] = @Nom, [DateDebutGarantie] = @DateDebutGarantie, [Status] = @Status, [CheckGarantie] = @CheckGarantie, [AccuseRecep] = @AccuseRecep WHERE [clef] = @clef">

<asp:QueryStringParameter Name="clef" QueryStringField="ordinateur" />

</SelectParameters>

<asp:Parameter Name="clef"/>

</DeleteParameters>

<asp:Parameter Name="FullName" Type="String" />

<asp:Parameter Name="SerialNo" Type="String" />

<asp:Parameter Name="STQNumber" Type="String" />

<asp:Parameter Name="Locale" Type="String" />

<asp:Parameter Name="Lieu" Type="String" />

<asp:Parameter Name="STQEcran" Type="String" />

<asp:Parameter Name="SerialEcran" Type="String" />

<asp:Parameter Name="DateAchat" Type="DateTime" />

<asp:Parameter Name="DureeGarantie" Type="Int32" />

<asp:Parameter Name="ExpireLe" Type="DateTime" />

<asp:Parameter Name="Nom" Type="String" />

<asp:Parameter Name="DateDebutGarantie" Type="DateTime" />

<asp:Parameter Name="Status" Type="String" />

<asp:Parameter Name="CheckGarantie" Type="Boolean" />

<asp:Parameter Name="AccuseRecep" Type="String" />

<asp:Parameter Name="clef" />

</UpdateParameters>

</asp:SqlDataSource>

Re: Guids are not very guid! Updating in DetailsView,FormView (Object must implement IConvertible)

Dimeji — Sun, 17 Feb 2008 21:06:48 GMT

Hey I'm running into the same problem. In my stored procedure the ID is declared as a uniqueidentifer. But when i add it into the updateparameters as previously instructed so far in this forum I;m getting the ever famour annoying "Object not set to instance....". Its sad that one cannot depend on datasources this days i resorted to using Objects in System.Data.SqlClient programmatically. Any suggestions would be greatly appreciated though.

Re: Guids are not very guid! Updating in DetailsView,FormView (Object must implement IConvertible)

groker — Wed, 28 Nov 2007 20:44:41 GMT

Thankyou for all the information... hope they fix it. The following is a more permanent solution... note: this may cause problems if you are storing a guid in a different column type (like varchar).

namespace HrServe.WebControls{ #region using using System; using System.Data; using System.Data.Common; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using HrServe; #endregion using /// <summary> /// Summary description for MySqlDataSource /// </summary> public class MySqlDataSource : SqlDataSource { public MySqlDataSource() { // // TODO: Add constructor logic here // } protected override void OnInit(EventArgs e) { base.OnInit(e); this.Inserting += new SqlDataSourceCommandEventHandler(MySqlDataSource_Query); this.Updating += new SqlDataSourceCommandEventHandler(MySqlDataSource_Query); this.Deleting += new SqlDataSourceCommandEventHandler(MySqlDataSource_Query); } void MySqlDataSource_Query(object sender, SqlDataSourceCommandEventArgs e) { Guid test; for (int i = 0; i < e.Command.Parameters.Count; i++) { DbParameter parameter = e.Command.Parameters[i]; if (Tools.GuidTryParse(parameter.Value.ToString(), out test)) parameter.DbType = DbType.Guid; } } }

}

#region usingusing System;using System.Data;using System.Configuration;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.Collections;using System.Text.RegularExpressions;using System.Text;using System.IO;using System.Net.Sockets;using System.Net;using System.Web.Mail;#endregion //using /// <summary>/// Summary description for Tools/// </summary>namespace HrServe{ public class Tools { public Tools() { // // TODO: Add constructor logic here // } public static bool GuidTryParse(string s, out Guid result) { if (s == null) throw new ArgumentNullException("s"); Regex format = new Regex( "^[A-Fa-f0-9]{32}$|" + "^({|\$)?[A-Fa-f0-9]{8}-([A-Fa-f0-9]{4}-){3}[A-Fa-f0-9]{12}(}|\$)?$|" + "^({)?[0xA-Fa-f0-9]{3,10}(, {0,1}[0xA-Fa-f0-9]{3,6}){2}, {0,1}({)([0xA-Fa-f0-9]{3,4}, {0,1}){7}[0xA-Fa-f0-9]{3,4}(}})$"); Match match = format.Match(s); if (match.Success) { result = new Guid(s); return true; } else { result = Guid.Empty; return false; } } }

}

Re: Guids are not very guid! Updating in DetailsView,FormView (Object must implement IConvertible)

sydbarrett74 — Tue, 24 Apr 2007 19:52:34 GMT

I got it to work, but still specifying the ID as an Object. I created a single function that handles all three scenarios (inserting, updating, deleting):

Protected Sub MySqlDataSource_Query(ByVal sender As Object, ByVal e As SqlDataSourceCommandEventArgs)

Dim sparam As SqlParameter

'ID being the name of the Guid (uniqueidentifier) field

If (e.Command.Parameters.Contains("@unqEvaluatorID")) Then

sparam = e.Command.Parameters(

"@unqEvaluatorID")

sparam.SqlDbType = SqlDbType.UniqueIdentifier

End If

End Sub

Notice the lack of a Handles clause. The secret is to go to <SqlDataSource> and set three attributes equal to the function above: OnInserting, OnUpdating, OnDeleting. It then works like a charm. Also, I handle guid generation within my Insert stored procedure. Since stored procs are precompiled, this is probably a better way of doing it.

Re: Guids are not very guid! Updating in DetailsView,FormView (Object must implement IConvertible)

sydbarrett74 — Tue, 24 Apr 2007 19:52:34 GMT

I got it to work, but still specifying the ID as an Object. I created a single function that handles all three scenarios (inserting, updating, deleting):

Protected Sub MySqlDataSource_Query(ByVal sender As Object, ByVal e As SqlDataSourceCommandEventArgs)

Dim sparam As SqlParameter

'ID being the name of the Guid (uniqueidentifier) field

If (e.Command.Parameters.Contains("@unqEvaluatorID")) Then

sparam = e.Command.Parameters(

"@unqEvaluatorID")

sparam.SqlDbType = SqlDbType.UniqueIdentifier

End If

End Sub

Re: Guids are not very guid! Updating in DetailsView,FormView (Object must implement IConvertible)

winterstide — Wed, 21 Feb 2007 15:05:48 GMT

hachiihcah:
After monkeying with this same problem quite a bit, I came up with the following resolution (thanks to some suggestions here in this forum)

After working for hours on this problem I finally got a solution that works. I have used ee's solution, but modified it slightly.

My parameters look like this

Note the lack of Type.

My code looks like this

protected void SqlDataSource1_ItemInserting(object sender, SqlDataSourceCommandEventArgs e)
    {
        if (e.Command.Parameters.Contains("@staffid")) {
            SqlParameter sparam = e.Command.Parameters["@staffid"] as SqlParameter;
            DropDownList d1 = (DropDownList)FormView1.FindControl("DropDownList1");
            sparam.Value = new Guid(d1.SelectedValue.ToString());
        }

        if (e.Command.Parameters.Contains("@planid"))
        {
            SqlParameter pparam = e.Command.Parameters["@planid"] as SqlParameter;
            DropDownList d1 = (DropDownList)FormView1.FindControl("DropDownListName");
            pparam.Value = new Guid(d1.SelectedValue.ToString());
        }
   }

Remember to add the onInserted="SqlDataSource1_ItemInserting" to the SqlDataSource code.

Cheers,

Luke

Re: Guids are not very guid! Updating in DetailsView,FormView (Object must implement IConvertible)

barney1 — Tue, 31 Oct 2006 12:09:57 GMT

This problem has been driving me nuts, too. The only way I found to fix it was, when setting up my database I used int instead of unique identifier as my primary key. You have to manually enter ID's that way, but it was the only thing that worked.

Re: Guids are not very guid! Updating in DetailsView,FormView (Object must implement IConvertible)

CM3Solutions — Tue, 17 Oct 2006 17:30:03 GMT

Did you try this:

<asp:Parameter Name="UserId" Type="Object" />

take out Type="Object" in your UpdateParameters.

<asp:Parameter Name="UserId" />

Also works for InsertParameters

Re: Guids are not very guid! Updating in DetailsView,FormView (Object must implement IConvertible)

Rivelyn — Wed, 04 Oct 2006 18:36:31 GMT

Well its now October and I am in the same spot as you where. I am using stored proceedures in a update command, and can not find any solutions...

Re: Guids are not very guid! Updating in DetailsView,FormView (Object must implement IConvertible)

hachiihcah — Fri, 09 Jun 2006 21:20:58 GMT

After monkeying with this same problem quite a bit, I came up with the following resolution (thanks to some suggestions here in this forum)

Works (for me) on VS2005 and the released version of .Net 2.0

1. So for a SqlDataSource that defines an InsertCommand and the autogenerated parameters that look like: (Where ID is a Guid field in db/storedproc)

 <InsertParameters>
      <asp:Parameter Name="ID" Type="Object"/>
      <asp:Parameter Name="FirstName" Type="String" />
      <asp:Parameter Name="LastName" Type="String" />
      <asp:Parameter Name="EMail" Type="String" />
      <asp:Parameter Name="Phone" Type="String" />
      <asp:Parameter Name="Company" Type="String" />
</InsertParameters>

In the Inserting Event of the SqlDataSource object, do something like this:

        protected void MySqlDataSource_Inserting(object sender, SqlDataSourceCommandEventArgs e)
        {

             //ID being the name of the Guid (uniqueidentifier) field
            if (e.Command.Parameters.Contains("@ID"))
            {
                SqlParameter sparam = e.Command.Parameters["@ID"] as SqlParameter;
                sparam.SqlDbType = SqlDbType.UniqueIdentifier;
            }
        }

The key being that the datasource has a list of Parameters that are internally SqlParameters which have a SqlDbType property that supports SqlDbType.UniqueIdentifier

I also Handled the DetailView's ItemInserting Event so I could specify a default Guid Value (DefaultValue="bleah" method didn't work too well) that was hidden in my details view (clients don't like visible guids ;):

        protected void MyDetailsView_Using_MySqlDataSource_ItemInserting(object sender, DetailsViewInsertEventArgs e)
        {
            e.Values["ID"] = Guid.NewGuid();
        }

Summary:
1. Go ahead and use the automagic generation for guid-using stored procs and sql queries
2. Just override the SqlDbType before Inserting/Updating
3. May need to specify the value of the parameter as well as the type in callbacks.

Caveats:
1. Tested for Insertion only (sofar). you will need to use appropriate Event overloads for Delete/Update/etc
2. Im Using stored procs for my InsertCommand
3. Theres a bunch of different ways to do things, and something else may prevent this for working for you.

Good Luck,
-ee-

Re: Guids are not very guid! Updating in DetailsView,FormView (Object must implement IConvertible)

stswordman — Sat, 06 May 2006 04:36:09 GMT

hi, Hojez,

it also throw the "Object must implement IConvertible" exception when i change the type to "String".

.....

So, who can solve?

thanks~

Re: Guids are not very guid! Updating in DetailsView,FormView (Object must implement IConvertible)

Hojez — Tue, 11 Apr 2006 07:26:04 GMT

i got the result.

that is uniqueidentifier is a STRING Type.

when you try to change that parameter like <Parameter Name=***, Type= String >

it must work.

But one problem i comes that is , in sql 2000, it shows that there is only a typ called bit

But in VS2005,there is a type called boolean.,

THey cannot change to each other. and i faced the same caution that Object must implement IConvertible

。。。。。。

So, who can solve?