Stored procedure's parameter was not supplied in aspx.vb

I want to display data in a gridview using a SQL Server stored procedure. The interface should include two textboxes for two parameters, one button, and a gridview. The data will display after all parameters are filled out and the button is clicked. But I got an error saying

‘Procedure or function ‘uspGetBillOfMaterials’ expects parameter ‘@StartProductID’, which was not supplied.’

Edit 1: I found out that I called parameter StartProducID instead of StartProductID. Thank you guys for helping me with this. I fixed the problem.

Now I get a new error:

Both DataSource and DataSourceID are defined on ‘GridView1’. Remove one definition.

Hope you can help me figure out where is my problem and the way to fix it.

Thanks.

Edit 2: I figured out my problems and posted the answer below. Basically, I just deleted the last two rows in my code-behind and it worked.

Here is my code in ASP.NET:

Please enter StartedProduct <asp:TextBox ID="StartProductID" runat="server"></asp:TextBox>
<br />
Please enter Date <asp:TextBox ID="CheckDate" runat="server"></asp:TextBox>
<br />
<asp:Button ID="Button1" runat="server" Text="Search BOM" />
<br />
<asp:GridView ID="GridView1" runat="server" DataSourceID="TestProc" AutoGenerateColumns="True">
    <Columns>
        <asp:BoundField DataField="ProductAssemblyID" HeaderText="ProductAssemblyID" ReadOnly="True" SortExpression="ProductAssemblyID" />
        <asp:BoundField DataField="ComponentID" HeaderText="ComponentID" ReadOnly="True" SortExpression="ComponentID" />
        <asp:BoundField DataField="ComponentDesc" HeaderText="ComponentDesc" ReadOnly="True" SortExpression="ComponentDesc" />
        <asp:BoundField DataField="TotalQuantity" HeaderText="TotalQuantity" ReadOnly="True" SortExpression="TotalQuantity" />
        <asp:BoundField DataField="StandardCost" HeaderText="StandardCost" ReadOnly="True" SortExpression="StandardCost" />
        <asp:BoundField DataField="ListPrice" HeaderText="ListPrice" ReadOnly="True" SortExpression="ListPrice" />
        <asp:BoundField DataField="BOMLevel" HeaderText="BOMLevel" ReadOnly="True" SortExpression="BOMLevel" />
        <asp:BoundField DataField="RecursionLevel" HeaderText="RecursionLevel" ReadOnly="True" SortExpression="RecursionLevel" />
    </Columns>
</asp:GridView>
<asp:SqlDataSource ID="TestProc" runat="server" 
    ConnectionString="<%$ ConnectionStrings:AdventureWorks2014ConnectionString %>" 
    SelectCommand="uspGetBillOfMaterials" 
    SelectCommandType="StoredProcedure">

    <SelectParameters>
        <asp:ControlParameter ControlID="StartProductID" Name="StartProductID" PropertyName="Text" Type="Int32" />
        <asp:ControlParameter ControlID="CheckDate" Name="CheckDate" PropertyName="Text" Type="DateTime" />
    </SelectParameters>
</asp:SqlDataSource>

Here is my code-behind:

Public Class TestStoredProcedure

Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

End Sub

Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    Dim command As New SqlCommand()
    Dim adapter As New SqlDataAdapter()
    Dim ds As New DataSet()
    Dim i As Integer = 0
    Dim sql As String = Nothing
    Dim connetionString As String = "Data Source=(local);Initial Catalog=AdventureWorks2014;Integrated Security=True"
    Dim connection As New SqlConnection(connetionString)
    connection.Open()
    command.Connection = connection
    command.CommandType = CommandType.StoredProcedure
    command.CommandText = "uspGetBillOfMaterials"
    command.Parameters.AddWithValue("StartProductID", StartProductID.Text)
    command.Parameters.AddWithValue("@CheckDate", CheckDate.Text)
    adapter = New SqlDataAdapter(command)
    adapter.Fill(ds)
    connection.Close()
    GridView1.DataSource = ds.Tables(0)
    GridView1.DataBind()

End Sub
End Class

Here is Solutions:

We have many solutions to this problem, But we recommend you to use the first solution because it is tested & true solution that will 100% work for you.

Solution 1

I figured out my problems:

The first error was because of a wrong parameter’s name. It should be "StartProductID" instead of "StartProducID".

The second error was because I defined the gridview in both C# code and Vb code. I deleted the last two rows in my code-behind and it worked. Here is the link that helped me solve my issue.

Thank you all.

Note: Use and implement solution 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply