ASP.Net-C#: "Conversion failed... character string to uniqueidentifier"

[blazeboy]

Hello,

I am using a SQL data source to insert records into a table. The table uses a GUID as the primary key (GoalEventID), and a GUID for a second field (GoalID) as a foreign key.

When I call SqlDataSource1.Insert() I get the exception : "Conversion failed when converting from a character string to uniqueidentifier."

I'd sure appreciate any help in resolving the issue. The code is below. Thank you.

---------------

The table definition is as follows:
GoalEventID : uniqueidentifier : pk : default=newid() : RowGuid=Yes
GoalID : uniqueidentifier : fk
GoalValue : float
GoalDateTime : datetime

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConflictDetection="CompareAllValues" 
        ConnectionString="<%$ ConnectionStrings:goals_goalsConnectionString1 %>" 
        DeleteCommand="DELETE FROM [tblGoalEvents] WHERE [GoalEventID] = @original_GoalEventID AND [GoalID] = @original_GoalID AND [GoalValue] = @original_GoalValue AND [GoalDateTime] = @original_GoalDateTime" 
        InsertCommand="INSERT INTO tblGoalEvents(GoalEventID, GoalID, GoalValue, GoalDateTime) VALUES (@GoalEventID, @GoalID, @GoalValue, @GoalDateTime)" 
        OldValuesParameterFormatString="original_{0}" 
        SelectCommand="SELECT tblGoals.GoalID FROM tblGoals INNER JOIN vwUsers ON tblGoals.UserID = vwUsers.UserID WHERE (tblGoals.GoalName = @GoalName) AND (vwUsers.Email = @Email)" 
        UpdateCommand="UPDATE [tblGoalEvents] SET [GoalID] = @GoalID, [GoalValue] = @GoalValue, [GoalDateTime] = @GoalDateTime WHERE [GoalEventID] = @original_GoalEventID AND [GoalID] = @original_GoalID AND [GoalValue] = @original_GoalValue AND [GoalDateTime] = @original_GoalDateTime">
 
      <!-- **Select, Update, and Delete commands ommitted for brevity**-->
 
        <InsertParameters>
            <asp:Parameter Name="GoalEventID" Type="Empty" DefaultValue="newid()" />
            <asp:Parameter Name="GoalID" Type="Empty" />
            <asp:Parameter Name="GoalValue" Type="Double" />
            <asp:Parameter Name="GoalDateTime" Type="DateTime" />
        </InsertParameters>
    </asp:SqlDataSource>

In the page load event I have:

  //...
  goalID = new Guid(reader[0].ToString());
  //...
  SqlDataSource1.InsertParameters["GoalID"].DefaultValue = goalID.ToString();
  SqlDataSource1.InsertParameters["GoalValue"].DefaultValue = goalEventValue.ToString();
  SqlDataSource1.InsertParameters["GoalDateTime"].DefaultValue = goalDateTime.ToString();
  SqlDataSource1.Insert();

[Frinavale]

Hello,

I am using a SQL data source to insert records into a table. The table uses a GUID as the primary key (GoalEventID), and a GUID for a second field (GoalID) as a foreign key.

When I call SqlDataSource1.Insert() I get the exception : "Conversion failed when converting from a character string to uniqueidentifier."

I'd sure appreciate any help in resolving the issue. The code is below. Thank you.

---------------

The table definition is as follows:
GoalEventID : uniqueidentifier : pk : default=newid() : RowGuid=Yes
GoalID : uniqueidentifier : fk
GoalValue : float
GoalDateTime : datetime

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConflictDetection="CompareAllValues" 
        ConnectionString="<%$ ConnectionStrings:goals_goalsConnectionString1 %>" 
        DeleteCommand="DELETE FROM [tblGoalEvents] WHERE [GoalEventID] = @original_GoalEventID AND [GoalID] = @original_GoalID AND [GoalValue] = @original_GoalValue AND [GoalDateTime] = @original_GoalDateTime" 
        InsertCommand="INSERT INTO tblGoalEvents(GoalEventID, GoalID, GoalValue, GoalDateTime) VALUES (@GoalEventID, @GoalID, @GoalValue, @GoalDateTime)" 
        OldValuesParameterFormatString="original_{0}" 
        SelectCommand="SELECT tblGoals.GoalID FROM tblGoals INNER JOIN vwUsers ON tblGoals.UserID = vwUsers.UserID WHERE (tblGoals.GoalName = @GoalName) AND (vwUsers.Email = @Email)" 
        UpdateCommand="UPDATE [tblGoalEvents] SET [GoalID] = @GoalID, [GoalValue] = @GoalValue, [GoalDateTime] = @GoalDateTime WHERE [GoalEventID] = @original_GoalEventID AND [GoalID] = @original_GoalID AND [GoalValue] = @original_GoalValue AND [GoalDateTime] = @original_GoalDateTime">
 
      <!-- **Select, Update, and Delete commands ommitted for brevity**-->
 
        <InsertParameters>
            <asp:Parameter Name="GoalEventID" Type="Empty" DefaultValue="newid()" />
            <asp:Parameter Name="GoalID" Type="Empty" />
            <asp:Parameter Name="GoalValue" Type="Double" />
            <asp:Parameter Name="GoalDateTime" Type="DateTime" />
        </InsertParameters>
    </asp:SqlDataSource>

In the page load event I have:

  //...
  goalID = new Guid(reader[0].ToString());
  //...
  SqlDataSource1.InsertParameters["GoalID"].DefaultValue = goalID.ToString();
  SqlDataSource1.InsertParameters["GoalValue"].DefaultValue = goalEventValue.ToString();
  SqlDataSource1.InsertParameters["GoalDateTime"].DefaultValue = goalDateTime.ToString();
  SqlDataSource1.Insert();

Are you sure that the GoalID and GoalEventID are Strings in your DataBase?
Make sure that your data types match....

[blazeboy]

Are you sure that the GoalID and GoalEventID are Strings in your DataBase?
Make sure that your data types match....

I know that they are NOT strings in the database. They are uniqueidentifiers. However, I thought I'd read somewhere that it would perform implicit conversion. Regardless, knowing the database fields are uniqueidentifiers and not strings, how would I pass the parameters as GUIDs? There is no 'Type="Empty"'

I have also tried casting them in the SQL statement (as follows), but I get the same "conversion" error message.

InsertCommand="INSERT INTO tblGoalEvents(GoalEventID, GoalID, GoalValue, GoalDateTime) VALUES (CAST(@GoalEventID AS UNIQUEIDENTIFIER), CAST(@GoalID AS UNIQUEIDENTIFIER), @GoalValue, @GoalDateTime)" 

Suggestions? Thanks.

[blazeboy]

I've found the solution. This post had the answer: http://www.devnewsgroups.net/group/m...opic10857.aspx

i figuried it out. you have to do ..

"'{" & (new guid(str)).toString("D") & "}'"

That worked for me!