sqldatasource

This is about VisualStudio beta 2 June.
I was trying to create a template step for my registeruserwizard with a Formview control. It causes me a whole weekend to figure this out .
Here is the Sp:

CREATE PROCEDURE sp_insertEp

@FirstName nvarchar(10),
@LastName nvarchar(20) ,
@Title nvarchar(30),
@Notes nvarchar(200),
@CreatedBy uniqueidentifier =NULL,
@PK_New uniqueidentifier =NULL OUTPUT
AS
begin

SET @PK_New = newid()
INSERT INTO Ep(FirstName,LastName,Title,Notes,CreatedBy,EmployeeID)VALUES (@FirstName,@LastName,@Title,@Notes,@CreatedBy,@PK_New)
RETURN (1)

end
GO

And here is the page that generated the 'Procedure or function has too many arguments specified' error:





DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

<script runat="server">

protected void On_Inserting(object sender, SqlDataSourceCommandEventArgs e)

{

SqlParameter createdBy = new SqlParameter("@CreatedBy", SqlDbType.UniqueIdentifier);

createdBy.Direction = ParameterDirection.Input;


createdBy.Value = new Guid("8CD648F7-ECF1-4A3A-BBCD-C464D427C5EB");

e.Command.Parameters.Add(createdBy);


SqlParameter insertedKey = new SqlParameter("@PK_New", SqlDbType.UniqueIdentifier);

insertedKey.Direction = ParameterDirection.InputOutput;

e.Command.Parameters.Add(insertedKey);




SqlParameter retVal = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);

retVal.Direction = ParameterDirection.ReturnValue;

e.Command.Parameters.Add(retVal);

}

protected void On_Inserted(object sender, SqlDataSourceStatusEventArgs e)

{

DbCommand command = e.Command;

// The label displays the primary key of the recently inserted row.

Label1.Text = command.Parameters["@PK_New"].Value.ToString();


}

script>

<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">

<title>Untitled Pagetitle>

head>

<body>

<form id="form1" runat="server">

<div>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString=""

InsertCommand="sp_insertEp" InsertCommandType="StoredProcedure" SelectCommand="SELECT EmployeeID, LastName, FirstName, Title, Notes, CreatedBy FROM Ep" OnInserted="On_Inserted" OnInserting="On_Inserting">

<InsertParameters>

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

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

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

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


<asp:Parameter Name="CreatedBy" >

<asp:Parameter Name="PK_New" Direction="InputOutput" />




InsertParameters>

asp:SqlDataSource>


div>

<asp:FormView ID="FormView1" runat="server" DataKeyNames="EmployeeID" DataSourceID="SqlDataSource1"

DefaultMode="Insert">

<InsertItemTemplate>

LastName:

<asp:TextBox ID="LastNameTextBox" runat="server" Text=''>

asp:TextBox><br />

FirstName:

<asp:TextBox ID="FirstNameTextBox" runat="server" Text=''>

asp:TextBox><br />

Title:

<asp:TextBox ID="TitleTextBox" runat="server" Text=''>

asp:TextBox><br />

Notes:

<asp:TextBox ID="NotesTextBox" runat="server" Text=''>

asp:TextBox><br />

<asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert"

Text="Insert">

asp:LinkButton>

<asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"

Text="Cancel">

asp:LinkButton>

InsertItemTemplate>



asp:FormView>

<asp:Label ID="Label1" runat="server">asp:Label>

form>

body>

html>

Notice the two lines in the gray box generated by SqlDataSource control? I also have two in my On_Inserting event handler to “Add“ these two parameters. I should either remove this two lines or change the “Add“ parameters in On_Inserting event handler to simply assign value like this:



SqlParameter insertedKey = e.Command.Parameters["@PK_New"]; //new SqlParameter("@PK_New", SqlDbType.UniqueIdentifier);

insertedKey.Direction = ParameterDirection.InputOutput;

//e.Command.Parameters.Add(insertedKey);

insertedKey.SqlDbType = SqlDbType.UniqueIdentifier;

That's the bad boy. Apparently, SqlDataSource control doesn't like “uniqueidentifier” type. You can work around this by just removing that two lines from <InsertParameters> tag, and handlling them in the ON_Inserting event by yourself.