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
User contributions licensed under CC BY-SA 3.0