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:
<%
@Page Language="C#" %>
<%
@Import Namespace="System.Data" %>
<%
@Import Namespace="System.Data.Common" %>
<%
@Import Namespace="System.Data.SqlClient" %>
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="<%$ ConnectionStrings:MyNorthwind %>"
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" />LastName:
<asp:TextBox ID="LastNameTextBox" runat="server" Text='<%# Bind("LastName") %>'>
asp:TextBox><br />
FirstName:
<asp:TextBox ID="FirstNameTextBox" runat="server" Text='<%# Bind("FirstName") %>'>
asp:TextBox><br />
Title:
<asp:TextBox ID="TitleTextBox" runat="server" Text='<%# Bind("Title") %>'>
asp:TextBox><br />
Notes:
<asp:TextBox ID="NotesTextBox" runat="server" Text='<%# Bind("Notes") %>'>
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.