sqldatasource

lnwuyaowei 2005-11-20 01:00:36
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.

...全文
149 5 打赏 收藏 转发到动态 举报
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
lnwuyaowei 2005-11-20
  • 打赏
  • 举报
 回复
3、出错提示：
================================================================
Server Error in '/Webchina' Application.
--------------------------------------------------------------------------------

Input string was not in a correct format.
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.FormatException: Input string was not in a correct format.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
============================================================================
请高人指点，谢谢。
lnwuyaowei 2005-11-20
  • 打赏
  • 举报
 回复
2、datalist
===============================================================
<asp:DataList
ID="SoftwareShowDataList"
runat="server" DataKeyField="SoftID" DataSourceID="SoftwareShowSqlDataSource" Width="100%">
<ItemTemplate>
<table style="width: 610px" cellpadding="4" cellspacing="1" class="tablethinline" >
<tbody>
<tr>
<td align="left" class="thinlinetd" colspan="2">
<asp:Label ID="SoftNameLabel" runat="server" Text='<%# Eval("SoftName") %>'></asp:Label></td>
<td align="left" class="thinlinetd" colspan="2">
演示地址：<asp:Label ID="SoftDemoLabel" runat="server" Text='<%# Eval("SoftDemo") %>'></asp:Label></td>
</tr>
<tr>
<td align="left" style="width: 54px">
加入时间</td>
<td align="left" class="thinlinetd" style="width: 100px">
<asp:Label ID="SoftJoinDateLabel" runat="server" Text='<%# Eval("SoftJoinDate") %>'></asp:Label></td>
<td align="left" class="thinlinetd" style="width: 100px">
程序类别</td>
<td align="left" class="thinlinetd" style="width: 100px">
<asp:Label ID="SoftFirstClassIDLabel" runat="server" Text='<%# Eval("SoftFirstClassID") %>'></asp:Label>
<span style="color: #ff0033">|</span><asp:Label ID="SoftSecondClassIDLabel" runat="server"
Text='<%# Eval("SoftSecondClassID") %>'></asp:Label></td>
</tr>
<tr>
<td align="left" style="width: 54px; height: 17px">
文件大小</td>
<td align="left" style="width: 100px; height: 17px">
<asp:Label ID="SoftSizeLabel" runat="server" Text='<%# Eval("SoftSize") %>'></asp:Label></td>
<td align="left" style="width: 100px; height: 17px">
授权方式</td>
<td align="left" style="width: 100px; height: 17px">
<asp:Label ID="SoftModeLabel" runat="server" Text='<%# Eval("SoftMode") %>'></asp:Label></td>
</tr>
<tr>
<td align="left" style="width: 54px; height: 17px">
应用平台</td>
<td align="left" style="width: 100px; height: 17px">
<asp:Label ID="SoftRoofLabel" runat="server" Text='<%# Eval("SoftRoof") %>'></asp:Label></td>
<td align="left" style="width: 100px; height: 17px">
推荐程度</td>
<td align="left" style="width: 100px; height: 17px">
</td>
</tr>
<tr>
<td align="left" style="width: 54px; height: 17px">
程序主页</td>
<td align="left" style="width: 100px; height: 17px">
<asp:Label ID="SoftHomeLabel" runat="server" Text='<%# Eval("SoftHome") %>'></asp:Label></td>
<td align="left" style="width: 100px; height: 17px">
下载/浏览</td>
<td align="left" style="width: 100px; height: 17px">
<asp:Label ID="SoftDownCountLabel" runat="server" Text='<%# Eval("SoftDownCount") %>'></asp:Label>
<span style="color: #ff3333">/</span><asp:Label ID="SoftReadCountLabel" runat="server"
Text='<%# Eval("SoftReadCount") %>'></asp:Label></td>
</tr>
<tr>
<td align="left" style="width: 54px; height: 17px">
下载地址1</td>
<td align="left" colspan="3" style="height: 17px">
<asp:Label ID="SoftUrlOneLabel" runat="server" Text='<%# Eval("SoftUrlOne") %>'></asp:Label> 
</td>
</tr>
<tr>
<td align="left" style="width: 54px; height: 17px">
下载地址2</td>
<td align="left" colspan="3" style="height: 17px">
<asp:Label ID="SoftUrlTwoLabel" runat="server" Text='<%# Eval("SoftUrlTwo") %>'></asp:Label></td>
</tr>
<tr>
<td align="left" style="width: 54px; height: 17px">
下载地址3</td>
<td align="left" colspan="3" style="height: 17px">
<asp:Label ID="SoftUrlThreeLabel" runat="server" Text='<%# Eval("SoftUrlThree") %>'></asp:Label></td>
</tr>
<tr>
<td align="left" style="width: 54px; height: 17px">
下载地址4</td>
<td align="left" colspan="3" style="height: 17px">
<asp:Label ID="SoftUrlFourLabel" runat="server" Text='<%# Eval("SoftUrlFour") %>'></asp:Label><asp:HyperLink
ID="HyperLink1" runat="server" NavigateUrl='<%# Eval("SoftUrlFour") %>' Text='<%# Eval("SoftUrlFour") %>'></asp:HyperLink></td>
</tr>
<tr>
<td align="left" colspan="4">
简价</td>
</tr>
<tr>
<td align="left" colspan="4" style="height: 22px">
<asp:Label ID="SoftDescLabel" runat="server" Text='<%# Eval("SoftDesc") %>'></asp:Label></td>
</tr>
<tr>
<td align="left" style="width: 54px; height: 17px">
</td>
<td align="left" style="width: 100px; height: 17px">
</td>
<td align="left" style="width: 100px; height: 17px">
</td>
<td align="left" style="width: 100px; height: 17px">
</td>
</tr>
</tbody>
</table>
</ItemTemplate>
</asp:DataList>
================================================================
lnwuyaowei 2005-11-20
  • 打赏
  • 举报
 回复
这个倒是行了，可是以下问题还没有解决：
1、数据源：
====================================================================
<asp:SqlDataSource
ID="SoftwareShowSqlDataSource"
runat="server"
ConnectionString="<%$ ConnectionStrings:Personal %>"
SelectCommand="SELECT [SoftID], [SoftName], [SoftJoinDate], [SoftSize], [SoftMode], [SoftRoof], [SoftHome], [SoftDemo], [SoftFirstClassID], [SoftSecondClassID], [SoftDesc], [SoftReadCount], [SoftDownCount], [SoftUrlOne], [SoftUrlTwo], [SoftUrlThree], [SoftUrlFour] FROM [SoftWare] WHERE ([SoftID] = @SoftID)">
<SelectParameters>
<asp:QueryStringParameter Name="SoftID" QueryStringField="SoftID" Type="Empty" />
</SelectParameters>
</asp:SqlDataSource>
===============================================================
clxxj 2005-11-20
  • 打赏
  • 举报
 回复
niu..................
lnwuyaowei 2005-11-20
  • 打赏
  • 举报
 回复
Inserting with a SqlDataSource Using uniqueidentifier Parameters
Scenario: Creating a page with an editable GridView or insertable DetailsView (or FormView) that uses a SqlDataSource whose contens are generated through the Configure Data Source wizard. Specifically, the SqlDataSouce's SELECT statement returns a column of type uniqueidentifier (a GUID), and the wizard is configured to automatically generate the INSERT, UPDATE, and DELETE statements as well.

Problem: When updating or inserting into the GridView or DetailsView (or FormsView) I get an “Implicit conversion from data type sql_variant to uniqueidentifier is not allowed. Use the CONVERT function to run this query” exception.

Solution: According to FormView Control, SqlDataSource, and 'Procedure or Argument has too Many Arguments Specified', It appears that this particular problem (or something similar) was a bug back in Beta 2 that (sadly) made it to RTM.

The crux of the problem, it appears, is that the <asp:Parameter> value for the uniqueidentifier field is, by default, set to Type=”Object”. To fix this, simply remove the Type property altogether. That is, change the SqlDataSource parameter setting from something like:

<asp:SqlDataSource ...>
<InsertParameters>
<asp:Parameter Name=”UserId” Type=”Object” />
...
</InsertParameters>
</asp:SqlDataSource>

to:

<asp:SqlDataSource ...>
<InsertParameters>
<asp:Parameter Name=”UserId” />
...
</InsertParameters>
</asp:SqlDataSource>

This change worked for me; once the Type was removed the exception ceased and the updates/inserts worked as expected.

Hope this helps some folks...

posted on Wednesday, November 16, 2005 3:24 PM

SqlDataSource
使用SqlDataSource控件操作数据库实验报告
ASP.NET SqlDataSource To Access
ASP.NET SqlDataSource To AccessASP.NET SqlDataSource To Access
GridView_+_FormView_+_SqlDataSource
GridView_+_FormView_+_SqlDataSource
SQLdataSource和gridView操作数据
SQLdataSource连接数据库、操纵数据、GridView显示数据并结合SQLdataSource管理数据
SqlDataSource、GridView、DetailsView显示、编辑、删除数据
SqlDataSource、GridView、DetailsView显示、编辑、删除数据
.NET社区

62,074

社区成员

669,028

社区内容

发帖
与我相关
我的任务
社区描述
.NET技术交流专区
javascript云原生 企业社区
社区管理员
  • ASP.NET
  • .Net开发者社区
  • R小R
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告

.NET 社区是一个围绕开源 .NET 的开放、热情、创新、包容的技术社区。社区致力于为广大 .NET 爱好者提供一个良好的知识共享、协同互助的 .NET 技术交流环境。我们尊重不同意见，支持健康理性的辩论和互动，反对歧视和攻击。

希望和大家一起共同营造一个活跃、友好的社区氛围。

试试用AI创作助手写篇文章吧

+ 用AI写文章