ASP.NET

Stored Procedure trouble RSS

2 replies

Last post Apr 09, 2012 02:30 PM by Chen Yu - MSFT

‹ Previous Thread|Next Thread ›

kahlshira

0 Points

1 Post

Stored Procedure trouble

Apr 03, 2012 11:55 PM|LINK

Hi everyone,
I have an existing website that uses a stored procedure that I am trying to add additional fields to each record. This is as far as I've gotten before getting a "Procedure or function 'sp_D21ToDoInsert' expects parameter '@Priority', which was not supplied." Thank you for your help in advance.

Page code:

<%@ Page Language="VB"  Debug="true"%>
<script runat="server">

    Dim BaseID as Integer
    Dim Usersaveable as Boolean
    
    Sub Page_Load(Sender as Object,e as EventArgs)
        ButtonRollovers(btnAdd)
        ButtonRollovers(btnSave)
    
        ' user rights check
            If Global.ASP.Global.RoleCheck(User.Identity.Name,1) OR Global.ASP.Global.RoleCheck(User.Identity.Name,2)
                UserSaveable = True
    
            Else
                UserSaveable = False
    
            End If
    
            If Not page.IsPostBack
    
                RefreshGrid()
                Hidedetails()
                If Usersaveable
                    btnAdd.visible = true
                Else
                    btnAdd.Visible = false
                End If
            End If
    
    End Sub
    
    Sub HideDetails()
        tbNote.Visible = False
        tbPriority.Visible = False
        tbDateAdded.Visible = False
        tbDateCompleted.Visible = False
        lbNote.Visible = False
        lbPriority.Visible = False
        lbDateAdded.Visible = False
        lbDateCompleted.Visible = False
        btnsave.visible = false
        If UserSaveable
            btnAdd.Visible = True
        End If
    End Sub
    
    Sub RefreshGrid()
        dgResults.Datasource = Global.ASP.Global.SQLSelect("SELECT * FROM D21ToDoNotes")
        dgResults.Databind()
    End Sub
    
    Sub dgResults_ItemCreated(sender As Object, e As DataGridItemEventArgs)
        Select Case e.Item.ItemType
                Case ListItemType.Item, ListItemType.AlternatingItem, ListItemType.EditItem
                    e.Item.Attributes.Add("onmouseover", "this.style.backgroundColor = 'sandybrown'")
                    e.Item.Attributes.Add("onmouseout", "this.style.backgroundColor = 'peachpuff'")
                Dim myButton As Button = CType(e.Item.Cells(5).Controls(0), Button)
                    myButton.CssClass = "button_Grid"
                    buttonrollovers(MyButton)
        End Select
    End Sub
    
    
    Sub ButtonRollovers(buttonname as object)
        Buttonname.Attributes.Add("onmouseover", "this.style.backgroundColor = '#33CCFF'")
        Buttonname.Attributes.Add("onmouseout", "this.style.backgroundColor = '#5777AD'")
    End Sub
    
    Sub btnAdd_Click(sender As Object, e As EventArgs)
        btnAdd.visible = false
        btnsave.Visible = True
        tbNote.Visible = True
        tbPriority.Visible = True
        tbDateAdded.Visible = True
        tbDateCompleted.Visible = True
        lbNote.Visible = True
        lbNote.Text = "To Do Note:"
        lbPriority.Visible = True
        lbPriority.Text = "Priority (1 Hi to 5 Low): "
        lbDateAdded.Visible = True
        lbDateAdded.Text = "Date Added: "
        lbDateCompleted.Visible = True
        lbDateCompleted.Text = "Date Completed: "
    End Sub
    
    Sub btnSave_Click(ByVal sender As Object, ByVal e As EventArgs)
        Global.ASP.Global.SQLSingle("sp_D21ToDoInsert '" & tbNote.Text & "," & tbPriority.Text & "," & tbDateAdded.Text & "," & tbDateCompleted.Text & "'")
        RefreshGrid()
        HideDetails()
    End Sub
    
    Sub dgResults_ItemCommand(sender As Object, e As DataGridCommandEventArgs)
        If e.commandname = "Delete"
                Dim DeleteSQL as String
                DeleteSQL = "DELETE FROM D21ToDoNotes WHERE D21ToDoNoteID =" & e.Item.Cells(0).Text
                Global.ASP.Global.SQLInsert(DeleteSQL)
                RefreshGrid()
                HideDetails()
    
        End If
    End Sub

</script>
<html>
<head>
    <title>MINA Base Information — Monaco Information Network Application</title>
    <!--#include file="include4aspx.htm"-->
    <link href="favicon.ico" rel="shortcut icon" />
</head>
<body>
    <form runat="server">
        <div align="center">
        </div>
        <div align="center">
            <asp:DataGrid id="dgResults" runat="server" BackColor="#FFE0C0" Font-Names="Arial" Font-Size="X-Small" Width="80%" AutoGenerateColumns="False" OnItemCreated="dgResults_ItemCreated" OnItemCommand="dgResults_ItemCommand">
                <HeaderStyle backcolor="#FF8000"></HeaderStyle>
                <Columns>
                    <asp:BoundColumn Visible="False" DataField="D21ToDoNoteID" ReadOnly="True" HeaderText="ToDoID"></asp:BoundColumn>
                    <asp:BoundColumn DataField="ToDoNote" HeaderText="Notes"><HeaderStyle width="60%"></HeaderStyle></asp:BoundColumn>
                    <asp:BoundColumn DataField="Priority" HeaderText="Priority (1 Hi to 5 Low)"><HeaderStyle Width="10%" /></asp:BoundColumn>
                    <asp:BoundColumn DataField="DateAdded" HeaderText="Date Added"><HeaderStyle Width="10%" /></asp:BoundColumn>
                    <asp:BoundColumn DataField="DateCompleted" HeaderText="Date Completed"><HeaderStyle Width="10%" /></asp:BoundColumn>
                    <asp:ButtonColumn Text="Delete" ButtonType="PushButton" HeaderText="Delete Note" CommandName="Delete"><HeaderStyle Width="10%" /></asp:ButtonColumn>
                </Columns>
            </asp:DataGrid>
        </div>
        <p align="center">
            <asp:Button class="button_long" id="btnAdd" onclick="btnAdd_Click" runat="server" Text="Add Note"></asp:Button>
        </p>
        <!-- Insert content here -->
        <p align="center">
            &nbsp;<asp:Label ID="lbNote" runat="server"></asp:Label></br><asp:TextBox id="tbNote" runat="server" Width="619px" Height="99px" TextMode="MultiLine"></asp:TextBox></br>
            &nbsp;<asp:Label ID="lbPriority" runat="server"></asp:Label><asp:TextBox ID="tbPriority" runat="server" TextMode="SingleLine"></asp:TextBox>
            &nbsp;<asp:Label ID="lbDateAdded" runat="server"></asp:Label><asp:TextBox ID="tbDateAdded" runat="server" TextMode="SingleLine"></asp:TextBox>
            &nbsp;<asp:Label ID="lbDateCompleted" runat="server"></asp:Label><asp:TextBox ID="tbDateCompleted" runat="server" TextMode="SingleLine"></asp:TextBox>
        </p>
        <p align="center">
            <asp:Button class="button_long" id="btnSave" onclick="btnSave_Click" runat="server" Text="Save"></asp:Button>
        </p>
        <p>
        </p>
    </form>
</body>
</html>

Stored Procedure:

USE [CALL]
GO
/****** Object:  StoredProcedure [dbo].[sp_D21ToDoInsert]    Script Date: 04/03/2012 16:53:20 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_D21ToDoInsert]
(
@ToDoNote as Varchar(5000),
@Priority as Varchar(2),
@DateAdded as Date,
@DateCompleted as Date
)
AS
IF NOT EXISTS (
    SELECT ToDoNote
    FROM    D21ToDoNotes
    WHERE ToDoNote = @ToDoNote AND Priority = @Priority AND DateAdded = @DateAdded AND DateCompleted = @DateCompleted
   )
BEGIN
    INSERT INTO D21ToDoNotes (ToDoNote, Priority, DateAdded, DateCompleted) 
    VALUES (@ToDoNote, @Priority, @DateAdded, @DateCompleted)  
END

bugwee

Member

354 Points

72 Posts

Re: Stored Procedure trouble

Apr 04, 2012 12:12 AM|LINK

i think, the tbPriority.text is empty so change the

Global.ASP.Global.SQLSingle("sp_D21ToDoInsert '" & tbNote.Text & "," & tbPriority.Text & "," & tbDateAdded.Text & "," & tbDateCompleted.Text & "'")

adding single qoute

Global.ASP.Global.SQLSingle("sp_D21ToDoInsert '" & tbNote.Text & "','" & tbPiority.Text & "','" & tbDateAdded.Text & "','" & tbDateCompleted.Text & "'")

Mark as Answer the posts that helped you.

Marked as answer by Chen Yu - MSFT on Apr 17, 2012 02:02 AM

- Reply
Chen Yu - MS...

All-Star

21581 Points

2493 Posts

Microsoft

Re: Stored Procedure trouble

Apr 09, 2012 02:30 PM|LINK

Hi kahlshira,

You could check these textboxes whther it has value or not before you use them. For example, Use IsNull() in your code.

Thanks.

Please mark the replies as answers if they help or unmark if not.
Feedback to us

Develop and promote your apps in Windows Store
- Marked as answer by Chen Yu - MSFT on Apr 17, 2012 02:02 AM

‹ Previous Thread|Next Thread ›

Stored Procedure trouble RSS

2 replies

kahlshira

Stored Procedure trouble

bugwee

Re: Stored Procedure trouble

Chen Yu - MS...

Re: Stored Procedure trouble

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft