procedure or function expects parameter which was not supplied1

-2

We have a search page linked to the following page. When we search for something specific i.e. the Book Title we receive an error saying the inputted parameter wasn't provided. We attempted to change the sproc and the below code to search for an ID number in our table but receive the same error. Any help would be greatly appreciated.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data;

///This class uses the ado.net sql classes to provide a connection to an 
sql server database.
///it is free for use by anybody so long as you give credit to the 
original author i.e me
///Matthew Dean mjdean@dmu.ac.uk De Montfort University 2013

public class clsDataConnection
{
//connection object used to connect to the database
SqlConnection connectionToDB = new SqlConnection();
//data adapter used to transfer data to and from the database
SqlDataAdapter dataChannel = new SqlDataAdapter();
//ado.net class for building the sql commands
SqlCommandBuilder commandBuilder = new SqlCommandBuilder();
//stores a list of all of the sql parameters
List<SqlParameter> SQLParams = new List<SqlParameter>();
//data table used to store the results of the stored procedure
DataTable dataTable = new DataTable();
//string variable used to store the connection string
private string connectionString;

public clsDataConnection()
{
    GetConString(GetDBName());
}

public clsDataConnection(string DBLocation)
{
    GetConString(DBLocation);
}


private string GetConString(string SomePath)
{
    //build up the connection string for the sql server database Visual 
Studio 2010
    //connectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=" + 
GetDBName() + ";Integrated Security=True;User Instance=True";
    //build up the connection string for the sql server database Visual 
Studio 2012
    //connectionString = "Data Source=(LocalDB)\\v11.0;AttachDbFilename=" 
+ GetDBName() + ";Integrated Security=True;Connect Timeout=30";
    //connectionString = "Data Source= 
(LocalDB)\\MSSQLLocalDB;AttachDbFilename=\"" + GetDBName() + 
"\";Integrated Security=True;Connect Timeout=30";
    connectionString = "Data Source= 
(LocalDB)\\MSSQLLocalDB;AttachDbFilename=\"###\";Integrated 
Security=True;Connect Timeout=30";
    connectionString = connectionString.Replace("###", SomePath);
    return connectionString;
}

public string GetDBName()
{

    //array to store folder names
    string[] filePaths;
    //var to store index for path array
    Int32 PathArrayIndex = 0;
    string[] dirConts;
    //flag for found
    Boolean Found = false;
    //var for counter
    Int32 Counter;
    List<string> DBNames = new List<string>();
    //get the folder for the project
    string BaseDir = 
TrimPath(System.AppDomain.CurrentDomain.BaseDirectory);
    do
    {
        //get the list of files in the folder
        filePaths = System.IO.Directory.GetDirectories(BaseDir);
        PathArrayIndex = 0;
        while (PathArrayIndex < filePaths.Length & Found == false)
        {
            //make path lowercase
            filePaths[PathArrayIndex] = 
filePaths[PathArrayIndex].ToLower();
            //if the file is not a system database file
            if (filePaths[PathArrayIndex].Contains("app_data") == true)
            {
                //get the list of files in the folder
                dirConts = 
System.IO.Directory.GetFiles(filePaths[PathArrayIndex], "*.mdf", 
System.IO.SearchOption.AllDirectories);
                Counter = 0;
                //while there are files to process
                while (Counter < dirConts.Length)
                {
                    //if the file is not a system database file
                    if (dirConts[Counter].Contains("ASPNETDB.MDF") == 
false)
                    {
                        //add the file to the list of db names
                        DBNames.Add(dirConts[Counter]);
                    }
                    //inc the counter
                    Counter++;
                }
                if (DBNames.Count == 1)
                {
                    //flag found
                    Found = true;
                }
                else
                {
                    //inc the counter
                    PathArrayIndex++;
                }
            }
            else
            {
                //inc the counter
                PathArrayIndex++;
            }
        }
        if (Found == false)
        {
            //move up a path and try again
            BaseDir = TrimPath(BaseDir);
        }
    }
    while (BaseDir != "" & Found == false);
    //if one database name is found use that
    if (DBNames.Count == 1)
    {
        //return the full path of the db
        return DBNames[0];
    }
    //if no database is founjd throw an exception
    else if (DBNames.Count == 0)
    {
        throw new System.Exception("There is no database in your App_Data 
folder");
    }
    //if multiple database have been found throw an error
    else
    {
        throw new System.Exception("You have too many database files in 
your App_Data folder");
    }
}

private string TrimPath(string OldPath)
{
    Int32 Posn = 0;
    //find the right most instance of \\
    Posn = OldPath.LastIndexOf("\\");
    if (Posn != -1)
    {
        //split the path at that point
        OldPath = OldPath.Substring(0, Posn);
    }
    else
    {
        OldPath = "";
    }
    return OldPath;
}

public void AddParameter(string ParamName, object ParamValue)
///public method allowing the addition of an sql parameter to the list of 
parameters
///it accepts two parameters the name of the parameter and its value
{
    //create a new instance of the sql parameter object
    SqlParameter AParam = new SqlParameter(ParamName, ParamValue);
    //add the parameter to the list
    SQLParams.Add(AParam);
}

public Int32 Execute(string SProcName)
{
    ///public method used to execute the named stored procedure
    ///accepts one parameter which is the name of the stored procedure to 
use
    //open the stored procedure
    //initialise the connection to the database
    connectionToDB = new SqlConnection(connectionString);
    //open the database
    connectionToDB.Open();
    //initialise the command builder for this connection
    SqlCommand dataCommand = new SqlCommand(SProcName, connectionToDB);
    //add the parameters to the command builder
    //loop through each parameter
    for (int Counter = 0; Counter < SQLParams.Count; Counter += 1)
    {
        //add it to the command builder
        dataCommand.Parameters.Add(SQLParams[Counter]);
    }
    //create an instance of the SqlParameter class
    SqlParameter returnValue = new SqlParameter();
    //set the direction as the return value
    returnValue.Direction = ParameterDirection.ReturnValue;
    //add this parameter to the Command builder
    dataCommand.Parameters.Add(returnValue);
    //set the command type as stored procedure
    dataCommand.CommandType = CommandType.StoredProcedure;
    //initialise the data adapter
    dataChannel = new SqlDataAdapter(SProcName, connectionToDB);
    //set the select command property for the data adapter
    dataChannel.SelectCommand = dataCommand;
    //use the copmmand builder to generate the sql insert delete etc
    commandBuilder = new SqlCommandBuilder(dataChannel);
    //fill the data adapter
    dataChannel.Fill(dataTable);
    //close the connection
    connectionToDB.Close();
    //return the result of the stored procedure
    return Convert.ToInt32(returnValue.Value);
}

public Int32 Count
//property that returns the count of records in the query results
{
    get
    {
        //return the count of the query results
        return dataTable.Rows.Count;
    }
}

public DataTable DataTable
//public property that provides access to the query results
{
    get
    {
        //return the query results
        return dataTable;
    }
    set
    {
        //set the query results
        dataTable = value;
    }
}
}

 > System.Data.SqlClient.SqlException
 HResult=0x80131904
 Message=Procedure or function 'sproc_tblBook_FilterByBookTitle' expects 
parameter '@bk_title', which was not supplied.
Source=.Net SqlClient Data Provider
StackTrace:
<Cannot evaluate the exception stack trace>


<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<title>Really Simple Form</title>
</head>
<body>
<h1>Really Simple Form</h1>
<br/>
<form method="post"
      action="RetrieveBook.aspx">
    <input name="txtCaptured" type="text" />
    <br/>
    <input name="Sumbit Query" type="submit" />
    <br/>
    <input name="Reset" type="reset" />
</form>
</body>
</html>



        <%
            char isbn;
            char bk_author;
            char bk_title;
            char bk_shelf_no;
            Int32 Index = 0;
            //create database connection and execute SQL Query
            clsDataConnection Book = new clsDataConnection();
            Book.Execute("sproc_tblBook_FilterByBookTitle");
            //start of table which displays results
            Response.Write("<table border=1 cellpadding=4><tr>");
            //Fill in column headers by looping for each field in the table
            //Within each cycle of the loop, write a table cell with the field anem in it

            while (Index < Book.Count)
            {
                //start the row of output table
                Response.Write("<tr>");
                //get the isbn 
                isbn = Convert.ToChar(Book.DataTable.Rows[Index]["isbn"]);
                //get author
                bk_author = Convert.ToChar(Book.DataTable.Rows[Index]["bk_author"]);
                //get title
                bk_title = Convert.ToChar(Book.DataTable.Rows[Index]["bk_title"]);
                //get shelf code
                bk_shelf_no = Convert.ToChar(Book.DataTable.Rows[Index]["bk_shelf_no"]);
                //write the isbn column
                Response.Write("<td><b>" + isbn + "</b></td");
                //author column
                Response.Write("<td><b>" + bk_author + "</b></td");
                //title column
                Response.Write("<td><b>" + bk_title + "</b></td");
                //shelf no column
                Response.Write("<td><b>" + bk_shelf_no + "</b></td");
                //point at the next record
                Index++;
                //end the row of output table
                Response.Write("</tr>");
            }
            //end the table
            Response.Write("</table>");

        %>

    </div>
</form>
CREATE PROCEDURE [dbo].[sproc_tblBook_FilterByBookTitle]
    @bk_title char(40)
AS
    SELECT *
    FROM tblBook
    WHERE bk_title = @bk_title;
RETURN 0

Searching Errormsg

c#
stored-procedures
asked on Stack Overflow Apr 15, 2021 by Connor Davis • edited Apr 15, 2021 by Connor Davis

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0