Major issues with Update Command

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Major issues with Update Command

Author

Topic

Dudemaster
Starting Member

5 Posts

Posted - 2009-04-08 : 13:09:03

I'm not a SQL Guru by far,but this should be relatively easy for a good DBA. I'm involved in an easy project where I'm learning how to "Update" using SQL Query.

My Database: CITC_Data
My Table: MaintByPo (only 1 Table, this should be easy right?)

Objective: Make a SP that can be called by a webpage which will update the table with paramters specified from an easy webpage. (This is rudementary Database updating from a webpage).

Here is the Query to create the SP (below). The query does create the SP, but I get the following errors (at the very bottom) when I execute the SP.

Please don't tell me to use VS -- I only have SQL Server and SP Designer (don't assume anything more than what I've written above).

Thanks for ANY assistance!

---------query to create sp------------
USE [CITC_Data]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--DROP PROCEDURE [dbo].[SP_UpdateMaintByPO]
--GO
CREATE PROCEDURE [dbo].[SP_UpdateMaintByPO]
(
@ID int,
@Current bit,
@Renew nvarchar(255),
@RenewalComments nvarchar(255),
@Reviewers nvarchar(255),
@PO__ nvarchar(255),
@PO_Date datetime,
@Acct__ nvarchar(255),
@Div nvarchar(255),
@UNT_Custodian nvarchar(255),
@Contract_Start datetime,
@Contract_End datetime,
@Vendor nvarchar(255),
@Item nvarchar(255),
@Item_ float(53),
@Manufacturer nvarchar(255),
@ModelNameNumber nvarchar(255),
@Decal__ nvarchar(255),
@Serial__ nvarchar(255),
@ServiceName nvarchar(255),
@YearsofMaint nvarchar(255),
@NumYrSuptInclPurch float(53),
@FYend float(53),
@SupportVendor nvarchar(255),
@SupportContactName nvarchar(255),
@SupportContactPhone nvarchar(255),
@EstFY07MaintAmt money,
@FY07MaintAmt money,
@FY07PurchPrice money,
@EstFY08MaintAmt money,
@FY08MaintAmt money,
@FY08PurchPrice nvarchar(255),
@EstFY09MaintAmt money,
@FY09MaintAmt money,
@FY09PurchPrice money,
@EstFY10MaintAmt money,
@FY10MaintAmt money,
@FY10PurchPrice money,
@Type__hw_sw_ nvarchar(255),
@Notes nvarchar(255),
@Contract_Type__maintenance__rental__warranty_ nvarchar(255),
@Heaf_Proj__ nvarchar(255),
@DR_Item_ float(53),
@Location nvarchar(255),
@SER_sContact nvarchar(255),
@NewAcct_ float(53),
@Encoded_Absolute_URL nvarchar(255),
@Item_Type nvarchar(255),
@Path nvarchar(255),
@URL_Path nvarchar(255),
@Workflow_Instance_ID nvarchar(255)
)
AS
BEGIN
UPDATE [MaintByPo]
SET [Current] = @Current,
[Renew] = @Renew,
[RenewalComments] = @RenewalComments,
[Reviewers] = @Reviewers,
[PO #] = @PO__,
[PO Date] = @PO_Date,
[Acct #] = @Acct__,
[Div] = @Div,
[UNT Custodian] = @UNT_Custodian,
[Contract Start] = @Contract_Start,
[Contract End] = @Contract_End,
[Vendor] = @Vendor,
[Item] = @Item,
[Item#] = @Item_,
[Manufacturer] = @Manufacturer,
[ModelNameNumber] = @ModelNameNumber,
[Decal #] = @Decal__,
[Serial #] = @Serial__,
[ServiceName] = @ServiceName,
[YearsofMaint] = @YearsofMaint,
[NumYrSuptInclPurch] = @NumYrSuptInclPurch,
[FYend] = @FYend,
[SupportVendor] = @SupportVendor,
[SupportContactName] = @SupportContactName,
[SupportContactPhone] = @SupportContactPhone,
[EstFY07MaintAmt] = @EstFY07MaintAmt,
[FY07MaintAmt] = @FY07MaintAmt,
[FY07PurchPrice] = @FY07PurchPrice,
[EstFY08MaintAmt] = @EstFY08MaintAmt,
[FY08MaintAmt] = @FY08MaintAmt,
[FY08PurchPrice] = @FY08PurchPrice,
[EstFY09MaintAmt] = @EstFY09MaintAmt,
[FY09MaintAmt] = @FY09MaintAmt,
[FY09PurchPrice] = @FY09PurchPrice,
[EstFY10MaintAmt] = @EstFY10MaintAmt,
[FY10MaintAmt] = @FY10MaintAmt,
[FY10PurchPrice] = @FY10PurchPrice,
[Type (hw/sw)] = @Type__hw_sw_,
[Notes] = @Notes,
[Contract Type (maintenance, rental, warranty)] = @Contract_Type__maintenance__rental__warranty_,
[Heaf Proj #] = @Heaf_Proj__,
[DR Item?] = @DR_Item_,
[Location] = @Location,
[SER'sContact] = @SER_sContact,
[NewAcct#] = @NewAcct_,
[Encoded Absolute URL] = @Encoded_Absolute_URL,
[Item Type] = @Item_Type,
[Path] = @Path,
[URL Path] = @URL_Path,
[Workflow Instance ID] = @Workflow_Instance_ID

WHERE [Current] = @Current AND
[Renew] = @Renew AND
[RenewalComments] = @RenewalComments AND
[Reviewers] = @Reviewers AND
[PO #] = @PO__ AND
[PO Date] = @PO_Date AND
[Acct #] = @Acct__ AND
[Div] = @Div AND
[UNT Custodian] = @UNT_Custodian AND
[Contract Start] = @Contract_Start AND
[Contract End] = @Contract_End AND
[Vendor] = @Vendor AND
[Item] = @Item AND
[Item#] = @Item_ AND
[Manufacturer] = @Manufacturer AND
[ModelNameNumber] = @ModelNameNumber AND
[Decal #] = @Decal__ AND
[Serial #] = @Serial__ AND
[ServiceName] = @ServiceName AND
[YearsofMaint] = @YearsofMaint AND
[NumYrSuptInclPurch] = @NumYrSuptInclPurch AND
[FYend] = @FYend AND
[SupportVendor] = @SupportVendor AND
[SupportContactName] = @SupportContactName AND
[SupportContactPhone] = @SupportContactPhone AND
[EstFY07MaintAmt] = @EstFY07MaintAmt AND
[FY07MaintAmt] = @FY07MaintAmt AND
[FY07PurchPrice] = @FY07PurchPrice AND
[EstFY08MaintAmt] = @EstFY08MaintAmt AND
[FY08MaintAmt] = @FY08MaintAmt AND
[FY08PurchPrice] = @FY08PurchPrice AND
[EstFY09MaintAmt] = @EstFY09MaintAmt AND
[FY09MaintAmt] = @FY09MaintAmt AND
[FY09PurchPrice] = @FY09PurchPrice AND
[EstFY10MaintAmt] = @EstFY10MaintAmt AND
[FY10MaintAmt] = @FY10MaintAmt AND
[FY10PurchPrice] = @FY10PurchPrice AND
[Type (hw/sw)] = @Type__hw_sw_ AND
[Notes] = @Notes AND
[Contract Type (maintenance, rental, warranty)] = @Contract_Type__maintenance__rental__warranty_ AND
[Heaf Proj #] = @Heaf_Proj__ AND
[DR Item?] = @DR_Item_ AND
[Location] = @Location AND
[SER'sContact] = @SER_sContact AND
[NewAcct#] = @NewAcct_ AND
[Encoded Absolute URL] = @Encoded_Absolute_URL AND
[Item Type] = @Item_Type AND
[Path] = @Path AND
[URL Path] = @URL_Path AND
[Workflow Instance ID] = @Workflow_Instance_ID
END

--------------------------------------
Error
Procedure or function SP_UpdateMaintByPO has too many arguments specified. SqlException (0x80131904): Procedure or function SP_UpdateMaintByPO has too many arguments specified.]

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-08 : 13:38:02

can you show us the code that calls this procedure with the parms.

SP_UpdateMaintByPO

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Dudemaster
Starting Member

5 Posts

Posted - 2009-04-08 : 13:52:01

quote:
Originally posted by yosiasz

can you show us the code that calls this procedure with the parms.

SP_UpdateMaintByPO
<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Yes, it's in a webpage and here is the code (top).
Update: The SP was changed as a recommendation by a DBA to the following (much easier) (bottom).

BUT -- it still doesn't work. When I call the SP from the webpage, I receive the classic error:

Procedure or function SP_UpdateMaintByPO has too many arguments specified.

-----Begin Code that Calls SP--------

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<%@ Page Language="C#" %>
<html dir="ltr" xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252" />
<title>Test</title>
</head>

<body>

<form id="form1" runat="server">
<asp:GridView runat="server" id="GridView1" AutoGenerateColumns="False" DataSourceID="Test1" DataKeyNames="ID" AllowPaging="True" AllowSorting="True">
<Columns>
<asp:commandfield ShowDeleteButton="True" ShowEditButton="True">
</asp:commandfield>
<asp:boundfield DataField="ID" HeaderText="ID" ReadOnly="True" SortExpression="ID">
</asp:boundfield>
<asp:checkboxfield DataField="Current" HeaderText="Current" SortExpression="Current">
</asp:checkboxfield>
<asp:boundfield DataField="Renew" HeaderText="Renew" SortExpression="Renew">
</asp:boundfield>
<asp:boundfield DataField="RenewalComments" HeaderText="RenewalComments" SortExpression="RenewalComments">
</asp:boundfield>
<asp:boundfield DataField="Reviewers" HeaderText="Reviewers" SortExpression="Reviewers">
</asp:boundfield>
<asp:boundfield DataField="PO #" HeaderText="PO #" SortExpression="PO #">
</asp:boundfield>
<asp:boundfield DataField="PO Date" HeaderText="PO Date" SortExpression="PO Date">
</asp:boundfield>
<asp:boundfield DataField="Acct #" HeaderText="Acct #" SortExpression="Acct #">
</asp:boundfield>
<asp:boundfield DataField="Div" HeaderText="Div" SortExpression="Div">
</asp:boundfield>
<asp:boundfield DataField="UNT Custodian" HeaderText="UNT Custodian" SortExpression="UNT Custodian">
</asp:boundfield>
<asp:boundfield DataField="Contract Start" HeaderText="Contract Start" SortExpression="Contract Start">
</asp:boundfield>
<asp:boundfield DataField="Contract End" HeaderText="Contract End" SortExpression="Contract End">
</asp:boundfield>
<asp:boundfield DataField="Vendor" HeaderText="Vendor" SortExpression="Vendor">
</asp:boundfield>
<asp:boundfield DataField="Item" HeaderText="Item" SortExpression="Item">
</asp:boundfield>
<asp:boundfield DataField="Item#" HeaderText="Item#" SortExpression="Item#">
</asp:boundfield>
<asp:boundfield DataField="Manufacturer" HeaderText="Manufacturer" SortExpression="Manufacturer">
</asp:boundfield>
<asp:boundfield DataField="ModelNameNumber" HeaderText="ModelNameNumber" SortExpression="ModelNameNumber">
</asp:boundfield>
<asp:boundfield DataField="Decal #" HeaderText="Decal #" SortExpression="Decal #">
</asp:boundfield>
<asp:boundfield DataField="Serial #" HeaderText="Serial #" SortExpression="Serial #">
</asp:boundfield>
<asp:boundfield DataField="ServiceName" HeaderText="ServiceName" SortExpression="ServiceName">
</asp:boundfield>
<asp:boundfield DataField="YearsofMaint" HeaderText="YearsofMaint" SortExpression="YearsofMaint">
</asp:boundfield>
<asp:boundfield DataField="NumYrSuptInclPurch" HeaderText="NumYrSuptInclPurch" SortExpression="NumYrSuptInclPurch">
</asp:boundfield>
<asp:boundfield DataField="FYend" HeaderText="FYend" SortExpression="FYend">
</asp:boundfield>
<asp:boundfield DataField="SupportVendor" HeaderText="SupportVendor" SortExpression="SupportVendor">
</asp:boundfield>
<asp:boundfield DataField="SupportContactName" HeaderText="SupportContactName" SortExpression="SupportContactName">
</asp:boundfield>
<asp:boundfield DataField="SupportContactPhone" HeaderText="SupportContactPhone" SortExpression="SupportContactPhone">
</asp:boundfield>
<asp:boundfield DataField="EstFY07MaintAmt" HeaderText="EstFY07MaintAmt" SortExpression="EstFY07MaintAmt">
</asp:boundfield>
<asp:boundfield DataField="FY07MaintAmt" HeaderText="FY07MaintAmt" SortExpression="FY07MaintAmt">
</asp:boundfield>
<asp:boundfield DataField="FY07PurchPrice" HeaderText="FY07PurchPrice" SortExpression="FY07PurchPrice">
</asp:boundfield>
<asp:boundfield DataField="EstFY08MaintAmt" HeaderText="EstFY08MaintAmt" SortExpression="EstFY08MaintAmt">
</asp:boundfield>
<asp:boundfield DataField="FY08MaintAmt" HeaderText="FY08MaintAmt" SortExpression="FY08MaintAmt">
</asp:boundfield>
<asp:boundfield DataField="FY08PurchPrice" HeaderText="FY08PurchPrice" SortExpression="FY08PurchPrice">
</asp:boundfield>
<asp:boundfield DataField="EstFY09MaintAmt" HeaderText="EstFY09MaintAmt" SortExpression="EstFY09MaintAmt">
</asp:boundfield>
<asp:boundfield DataField="FY09MaintAmt" HeaderText="FY09MaintAmt" SortExpression="FY09MaintAmt">
</asp:boundfield>
<asp:boundfield DataField="FY09PurchPrice" HeaderText="FY09PurchPrice" SortExpression="FY09PurchPrice">
</asp:boundfield>
<asp:boundfield DataField="EstFY10MaintAmt" HeaderText="EstFY10MaintAmt" SortExpression="EstFY10MaintAmt">
</asp:boundfield>
<asp:boundfield DataField="FY10MaintAmt" HeaderText="FY10MaintAmt" SortExpression="FY10MaintAmt">
</asp:boundfield>
<asp:boundfield DataField="FY10PurchPrice" HeaderText="FY10PurchPrice" SortExpression="FY10PurchPrice">
</asp:boundfield>
<asp:boundfield DataField="Type (hw/sw)" HeaderText="Type (hw/sw)" SortExpression="Type (hw/sw)">
</asp:boundfield>
<asp:boundfield DataField="Notes" HeaderText="Notes" SortExpression="Notes">
</asp:boundfield>
<asp:boundfield DataField="Contract Type (maintenance, rental, warranty)" HeaderText="Contract Type (maintenance, rental, warranty)" SortExpression="Contract Type (maintenance, rental, warranty)">
</asp:boundfield>
<asp:boundfield DataField="Heaf Proj #" HeaderText="Heaf Proj #" SortExpression="Heaf Proj #">
</asp:boundfield>
<asp:boundfield DataField="DR Item?" HeaderText="DR Item?" SortExpression="DR Item?">
</asp:boundfield>
<asp:boundfield DataField="Location" HeaderText="Location" SortExpression="Location">
</asp:boundfield>
<asp:boundfield DataField="SER'sContact" HeaderText="SER'sContact" SortExpression="SER'sContact">
</asp:boundfield>
<asp:boundfield DataField="NewAcct#" HeaderText="NewAcct#" SortExpression="NewAcct#">
</asp:boundfield>
<asp:boundfield DataField="Encoded Absolute URL" HeaderText="Encoded Absolute URL" SortExpression="Encoded Absolute URL">
</asp:boundfield>
<asp:boundfield DataField="Item Type" HeaderText="Item Type" SortExpression="Item Type">
</asp:boundfield>
<asp:boundfield DataField="Path" HeaderText="Path" SortExpression="Path">
</asp:boundfield>
<asp:boundfield DataField="URL Path" HeaderText="URL Path" SortExpression="URL Path">
</asp:boundfield>
<asp:boundfield DataField="Workflow Instance ID" HeaderText="Workflow Instance ID" SortExpression="Workflow Instance ID">
</asp:boundfield>
</Columns>
</asp:GridView>
<br />
<asp:SqlDataSource runat="server" ID="Test1" ConnectionString="<%$ ConnectionStrings:CITC_DataConnectionString %>" SelectCommand="SELECT * FROM [MaintByPo]
Where [Current] = 1 AND [Contract End] <= DateAdd(day,+90,GetDate())AND [Contract End] >= DateAdd(day,+1,GetDate())" DeleteCommand="DELETE FROM [MaintByPo] WHERE [ID] = @ID" InsertCommand="INSERT INTO [MaintByPo] ([Current], [Renew], [RenewalComments], [Reviewers], [PO #], [PO Date], [Acct #], [Div], [UNT Custodian], [Contract Start], [Contract End], [Vendor], [Item], [Item#], [Manufacturer], [ModelNameNumber], [Decal #], [Serial #], [ServiceName], [YearsofMaint], [NumYrSuptInclPurch], [FYend], [SupportVendor], [SupportContactName], [SupportContactPhone], [EstFY07MaintAmt], [FY07MaintAmt], [FY07PurchPrice], [EstFY08MaintAmt], [FY08MaintAmt], [FY08PurchPrice], [EstFY09MaintAmt], [FY09MaintAmt], [FY09PurchPrice], [EstFY10MaintAmt], [FY10MaintAmt], [FY10PurchPrice], [Type (hw/sw)], [Notes], [Contract Type (maintenance, rental, warranty)], [Heaf Proj #], [DR Item?], [Location], [SER'sContact], [NewAcct#], [Encoded Absolute URL], [Item Type], [Path], [URL Path], [Workflow Instance ID]) VALUES (@Current, @Renew, @RenewalComments, @Reviewers, @column1, @PO_Date, @column2, @Div, @UNT_Custodian, @Contract_Start, @Contract_End, @Vendor, @Item, @column3, @Manufacturer, @ModelNameNumber, @column4, @column5, @ServiceName, @YearsofMaint, @NumYrSuptInclPurch, @FYend, @SupportVendor, @SupportContactName, @SupportContactPhone, @EstFY07MaintAmt, @FY07MaintAmt, @FY07PurchPrice, @EstFY08MaintAmt, @FY08MaintAmt, @FY08PurchPrice, @EstFY09MaintAmt, @FY09MaintAmt, @FY09PurchPrice, @EstFY10MaintAmt, @FY10MaintAmt, @FY10PurchPrice, @column6, @Notes, @column7, @column8, @column9, @Location, @column10, @column11, @Encoded_Absolute_URL, @Item_Type, @Path, @URL_Path, @Workflow_Instance_ID)" UpdateCommand="SP_UpdateMaintByPO" UpdateCommandType="StoredProcedure">
<DeleteParameters>
<asp:parameter Name="ID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:parameter Name="Acct__" Type="String" />
<asp:parameter Name="Contract_End" Type="DateTime" />
<asp:parameter Name="Contract_Start" Type="DateTime" />
<asp:parameter Name="Contract_Type__maintenance__rental__warranty_" Type="String" />
<asp:parameter Name="Current" Type="Boolean" />
<asp:parameter Name="Decal__" Type="String" />
<asp:parameter Name="Div" Type="String" />
<asp:parameter Name="DR_Item_" Type="Double" />
<asp:parameter Name="Encoded_Absolute_URL" Type="String" />
<asp:parameter Name="EstFY07MaintAmt" Type="Decimal" />
<asp:parameter Name="EstFY08MaintAmt" Type="Decimal" />
<asp:parameter Name="EstFY09MaintAmt" Type="Decimal" />
<asp:parameter Name="EstFY10MaintAmt" Type="Decimal" />
<asp:parameter Name="FY07MaintAmt" Type="Decimal" />
<asp:parameter Name="FY07PurchPrice" Type="Decimal" />
<asp:parameter Name="FY08MaintAmt" Type="Decimal" />
<asp:parameter Name="FY08PurchPrice" Type="String" />
<asp:parameter Name="FY09MaintAmt" Type="Decimal" />
<asp:parameter Name="FY09PurchPrice" Type="Decimal" />
<asp:parameter Name="FY10MaintAmt" Type="Decimal" />
<asp:parameter Name="FY10PurchPrice" Type="Decimal" />
<asp:parameter Name="FYend" Type="Double" />
<asp:parameter Name="Heaf_Proj__" Type="String" />
<asp:parameter Name="ID" Type="Int32" />
<asp:parameter Name="Item" Type="String" />
<asp:parameter Name="Item_" Type="Double" />
<asp:parameter Name="Item_Type" Type="String" />
<asp:parameter Name="Location" Type="String" />
<asp:parameter Name="Manufacturer" Type="String" />
<asp:parameter Name="ModelNameNumber" Type="String" />
<asp:parameter Name="NewAcct_" Type="Double" />
<asp:parameter Name="Notes" Type="String" />
<asp:parameter Name="NumYrSuptInclPurch" Type="Double" />
<asp:parameter Name="Path" Type="String" />
<asp:parameter Name="PO__" Type="String" />
<asp:parameter Name="PO_Date" Type="DateTime" />
<asp:parameter Name="Renew" Type="String" />
<asp:parameter Name="RenewalComments" Type="String" />
<asp:parameter Name="Reviewers" Type="String" />
<asp:parameter Name="SER_sContact" Type="String" />
<asp:parameter Name="Serial__" Type="String" />
<asp:parameter Name="ServiceName" Type="String" />
<asp:parameter Name="SupportContactName" Type="String" />
<asp:parameter Name="SupportContactPhone" Type="String" />
<asp:parameter Name="SupportVendor" Type="String" />
<asp:parameter Name="Type__hw_sw_" Type="String" />
<asp:parameter Name="UNT_Custodian" Type="String" />
<asp:parameter Name="URL_Path" Type="String" />
<asp:parameter Name="Vendor" Type="String" />
<asp:parameter Name="Workflow_Instance_ID" Type="String" />
<asp:parameter Name="YearsofMaint" Type="String" />
</UpdateParameters>
<InsertParameters>
<asp:parameter Name="Current" Type="Boolean" />
<asp:parameter Name="Renew" Type="String" />
<asp:parameter Name="RenewalComments" Type="String" />
<asp:parameter Name="Reviewers" Type="String" />
<asp:parameter Name="column1" Type="String" />
<asp:parameter Name="PO_Date" Type="DateTime" />
<asp:parameter Name="column2" Type="String" />
<asp:parameter Name="Div" Type="String" />
<asp:parameter Name="UNT_Custodian" Type="String" />
<asp:parameter Name="Contract_Start" Type="DateTime" />
<asp:parameter Name="Contract_End" Type="DateTime" />
<asp:parameter Name="Vendor" Type="String" />
<asp:parameter Name="Item" Type="String" />
<asp:parameter Name="column3" Type="Double" />
<asp:parameter Name="Manufacturer" Type="String" />
<asp:parameter Name="ModelNameNumber" Type="String" />
<asp:parameter Name="column4" Type="String" />
<asp:parameter Name="column5" Type="String" />
<asp:parameter Name="ServiceName" Type="String" />
<asp:parameter Name="YearsofMaint" Type="String" />
<asp:parameter Name="NumYrSuptInclPurch" Type="Double" />
<asp:parameter Name="FYend" Type="Double" />
<asp:parameter Name="SupportVendor" Type="String" />
<asp:parameter Name="SupportContactName" Type="String" />
<asp:parameter Name="SupportContactPhone" Type="String" />
<asp:parameter Name="EstFY07MaintAmt" Type="Decimal" />
<asp:parameter Name="FY07MaintAmt" Type="Decimal" />
<asp:parameter Name="FY07PurchPrice" Type="Decimal" />
<asp:parameter Name="EstFY08MaintAmt" Type="Decimal" />
<asp:parameter Name="FY08MaintAmt" Type="Decimal" />
<asp:parameter Name="FY08PurchPrice" Type="String" />
<asp:parameter Name="EstFY09MaintAmt" Type="Decimal" />
<asp:parameter Name="FY09MaintAmt" Type="Decimal" />
<asp:parameter Name="FY09PurchPrice" Type="Decimal" />
<asp:parameter Name="EstFY10MaintAmt" Type="Decimal" />
<asp:parameter Name="FY10MaintAmt" Type="Decimal" />
<asp:parameter Name="FY10PurchPrice" Type="Decimal" />
<asp:parameter Name="column6" Type="String" />
<asp:parameter Name="Notes" Type="String" />
<asp:parameter Name="column7" Type="String" />
<asp:parameter Name="column8" Type="String" />
<asp:parameter Name="column9" Type="Double" />
<asp:parameter Name="Location" Type="String" />
<asp:parameter Name="column10" Type="String" />
<asp:parameter Name="column11" Type="Double" />
<asp:parameter Name="Encoded_Absolute_URL" Type="String" />
<asp:parameter Name="Item_Type" Type="String" />
<asp:parameter Name="Path" Type="String" />
<asp:parameter Name="URL_Path" Type="String" />
<asp:parameter Name="Workflow_Instance_ID" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
</form>

</body>

</html>
-----End Code that Calls SP--------

-----Begin Query that creates SP--------

New SP Code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SP_UpdateMaintByPO]
(
@ID int,
@Current bit,
@Renew nvarchar(255),
@RenewalComments nvarchar(255),
@Reviewers nvarchar(255),
@PO__ nvarchar(255),
@PO_Date datetime,
@Acct__ nvarchar(255),
@Div nvarchar(255),
@UNT_Custodian nvarchar(255),
@Contract_Start datetime,
@Contract_End datetime,
@Vendor nvarchar(255),
@Item nvarchar(255),
@Item_ float(53),
@Manufacturer nvarchar(255),
@ModelNameNumber nvarchar(255),
@Decal__ nvarchar(255),
@Serial__ nvarchar(255),
@ServiceName nvarchar(255),
@YearsofMaint nvarchar(255),
@NumYrSuptInclPurch float(53),
@FYend float(53),
@SupportVendor nvarchar(255),
@SupportContactName nvarchar(255),
@SupportContactPhone nvarchar(255),
@EstFY07MaintAmt money,
@FY07MaintAmt money,
@FY07PurchPrice money,
@EstFY08MaintAmt money,
@FY08MaintAmt money,
@FY08PurchPrice nvarchar(255),
@EstFY09MaintAmt money,
@FY09MaintAmt money,
@FY09PurchPrice money,
@EstFY10MaintAmt money,
@FY10MaintAmt money,
@FY10PurchPrice money,
@Type__hw_sw_ nvarchar(255),
@Notes nvarchar(255),
@Contract_Type__maintenance__rental__warranty_ nvarchar(255),
@Heaf_Proj__ nvarchar(255),
@DR_Item_ float(53),
@Location nvarchar(255),
@SER_sContact nvarchar(255),
@NewAcct_ float(53),
@Encoded_Absolute_URL nvarchar(255),
@Item_Type nvarchar(255),
@Path nvarchar(255),
@URL_Path nvarchar(255),
@Workflow_Instance_ID nvarchar(255)
)
AS
BEGIN
UPDATE [MaintByPo]
SET [Current] = @Current,
[Renew] = @Renew,
[RenewalComments] = @RenewalComments,
[Reviewers] = @Reviewers,
[PO #] = @PO__,
[PO Date] = @PO_Date,
[Acct #] = @Acct__,
[Div] = @Div,
[UNT Custodian] = @UNT_Custodian,
[Contract Start] = @Contract_Start,
[Contract End] = @Contract_End,
[Vendor] = @Vendor,
[Item] = @Item,
[Item#] = @Item_,
[Manufacturer] = @Manufacturer,
[ModelNameNumber] = @ModelNameNumber,
[Decal #] = @Decal__,
[Serial #] = @Serial__,
[ServiceName] = @ServiceName,
[YearsofMaint] = @YearsofMaint,
[NumYrSuptInclPurch] = @NumYrSuptInclPurch,
[FYend] = @FYend,
[SupportVendor] = @SupportVendor,
[SupportContactName] = @SupportContactName,
[SupportContactPhone] = @SupportContactPhone,
[EstFY07MaintAmt] = @EstFY07MaintAmt,
[FY07MaintAmt] = @FY07MaintAmt,
[FY07PurchPrice] = @FY07PurchPrice,
[EstFY08MaintAmt] = @EstFY08MaintAmt,
[FY08MaintAmt] = @FY08MaintAmt,
[FY08PurchPrice] = @FY08PurchPrice,
[EstFY09MaintAmt] = @EstFY09MaintAmt,
[FY09MaintAmt] = @FY09MaintAmt,
[FY09PurchPrice] = @FY09PurchPrice,
[EstFY10MaintAmt] = @EstFY10MaintAmt,
[FY10MaintAmt] = @FY10MaintAmt,
[FY10PurchPrice] = @FY10PurchPrice,
[Type (hw/sw)] = @Type__hw_sw_,
[Notes] = @Notes,
[Contract Type (maintenance, rental, warranty)] = @Contract_Type__maintenance__rental__warranty_,
[Heaf Proj #] = @Heaf_Proj__,
[DR Item?] = @DR_Item_,
[Location] = @Location,
[SER'sContact] = @SER_sContact,
[NewAcct#] = @NewAcct_,
[Encoded Absolute URL] = @Encoded_Absolute_URL,
[Item Type] = @Item_Type,
[Path] = @Path,
[URL Path] = @URL_Path,
[Workflow Instance ID] = @Workflow_Instance_ID

WHERE
id = @id
end

-----End Query that creates SP--------

Thanks!

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-08 : 14:10:19

But does it work with InserParamerts?
Maybe the number of Updateparameters you are passing from Sqldatasource & the ones defined in the stored proc are not matching.

Dudemaster
Starting Member

5 Posts

Posted - 2009-04-08 : 14:17:49

quote:
Originally posted by ayamas

But does it work with InserParamerts?
Maybe the number of Updateparameters you are passing from Sqldatasource & the ones defined in the stored proc are not matching.

I don't know what you mean by InserParamerts; I couldn't find any reference to this command in any of my quick search books.

I thought there might be a problem with the Update Parameters also, but according to the data that Posted in this last message, they all match up. Don't they?

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-08 : 14:26:29

I meant the "InsertParameters" of the SqlDataSource.Does the Insert Stored proc work?Sorry for the spelling mistake.

Dudemaster
Starting Member

5 Posts

Posted - 2009-04-08 : 14:35:51

quote:
Originally posted by ayamas

I meant the "InsertParameters" of the SqlDataSource.Does the Insert Stored proc work?Sorry for the spelling mistake.

I should have understood what you meant (was doing copy/paste functions) -- sorry.

The Insert and Delete commands work very well, but just not the "update" command.

I posted "everything" in the last post including ALL of the code from the webpage. Rather than post the same data again, can you tell me what else do you need to make a recommendation ? (please spell it out in a manner of speaking).

Your help is very much appreciated :)

Thank you :)

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-08 : 14:41:48

Dude, in the web page you have 50 paramaters in the stored procedure you have 51. you got to fix that up.

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Dudemaster
Starting Member

5 Posts

Posted - 2009-04-08 : 14:58:23

quote:
Originally posted by yosiasz

Dude, in the web page you have 50 paramaters in the stored procedure you have 51. you got to fix that up.

I did a comparative analysis side by side in Excel for display purposes, and there are the exact same number of Paramaters as stored procedures (51). Perhaps you are confusing the bottom of the UPdate command where the 'key' is passed as in "Where" (ID = @ID).

I am new at this, and I absolutely admit I know little, so if I'm mistaken about where things go, please correct me :)

I added the line ID = @ID, to the Update Section in SQL and it made no difference; the SP compiled but the error continues :(

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources