New Topic/Question
Reply
Here is the whole function
which searches books by ID, by book title, by year, by place and in addition by category
so I don't have problems with all except searching by book title. Since book titles are long it is not advisable to make the search with the whole title. So I wanted to be any book that contains the search text. For Instance if I search like this "tif" then Artificial Intelligence, Tiffany Days,...should come out.
public static DataTable GetBooks(int ID,string bt, int yr, string place,string cat)
{
try
{
DBConnector.Connect();
string query,query1;
SqlCommand cmd = new SqlCommand();
cmd.Connection = DBConnector.conn;
query = "";
if (ID != 0)
{
query = "SELECT * FROM Book WHERE Book_ID='"+ID+"'";
}
else if (bt != "")
{
query = "SELECT * FROM Book WHERE Book_Title LIKE '" + bt + "'";
}
else if (place != "")
{
query = "SELECT * FROM Book WHERE Place LIKE '" + place + "'";
}
else if (yr != 0)
{
query = "SELECT * FROM Book WHERE Year_Published='" + yr + "'";
}
query1 = query + "AND Category_ID='" + Category.GetID(cat) + "'"; //inculdes category search
if (cat == "All")
{
cmd.CommandText = query;
}
else
{
cmd.CommandText = query1;
}
SqlDataAdapter adp = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adp.Fill(dt);
return dt;
}
catch
{
return null;
}
}
As you can see in the above code I used LIKE operator first, I tried the following too...
query = "SELECT * FROM Book WHERE Book_Title LIKE '" + "*"+bt+"*" + "'";
It didn't work....
So my question is how should the query should look like.....?????????????????
If needed here is code for the function that is activated when search button is clicked on the search form, it calls GetBooks(...)....
lbl a prefix for the name of labels
cmb a prefix for the name of combo boxes
gv a prefix for the data grid view controls
txt a prefix for the name of textbox controls
private void btnSearch_Click(object sender, EventArgs e)
{
try
{
lblStatus.Text = "";
if (gvResults.Rows.Count == 0)
{
gvResults.DataSource = null;
}
switch (cmbSearchBy.Text)
{
case "ID":
int result;
int.TryParse(txtSearchText.Text,out result);
if (result == 0)
{
lblStatus.Text = "Please Enter a Number on the Search Text!";
gvResults.DataSource = null;
}
else
{
gvResults.DataSource = Book.GetBooks(int.Parse(txtSearchText.Text), "", 0, "", "All");
txtSearchText.Text = "";
}
break;
case "BookTitle":
if (txtSearchText.Text.Length < 3)
{
lblStatus.Text = "Search Text should contain atleast 3 characters";
}
else
{
gvResults.DataSource = Book.GetBooks(0, txtSearchText.Text, 0, "", cmbSearchFrom.Text);
txtSearchText.Text = "";
}
break;
case "Year":
gvResults.DataSource = Book.GetBooks(0,"",int.Parse(txtSearchText.Text),"", cmbSearchFrom.Text);
txtSearchText.Text = "";
break;
case "Place":
gvResults.DataSource = Book.GetBooks(0,"",0,txtSearchText.Text, cmbSearchFrom.Text);
txtSearchText.Text = "";
break;
}
lblStatus.Text = lblStatus.Text+"\n"+gvResults.Rows.Count.ToString() + " Result/s Found";
lblStatus.ForeColor = Color.Blue;
}
catch
{
lblStatus.Text="Some Error Occurred";
}
}
Again if needed here is the script for the table Book, note this not the full database script.
USE [BookCirculation] GO /****** Object: Table [dbo].[Book] Script Date: 01/04/2011 15:37:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Book]( [Book_ID] [int] NOT NULL, [Book_Title] [varchar](50) NOT NULL, [Category_ID] [int] NOT NULL, [Author_Name] [varchar](50) NOT NULL, [Quantity_Available] [int] NOT NULL, [Total_Quantity] [int] NOT NULL, [Price] [float] NOT NULL, [Year_Published] [bigint] NOT NULL, [Publisher_Name] [varchar](50) NOT NULL, [Place] [varchar](50) NOT NULL, CONSTRAINT [PK_Book] PRIMARY KEY CLUSTERED ( [Book_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[Book] WITH CHECK ADD CONSTRAINT [FK_Book_Category] FOREIGN KEY([Category_ID]) REFERENCES [dbo].[Category] ([Category_ID]) GO ALTER TABLE [dbo].[Book] CHECK CONSTRAINT [FK_Book_Category] GO
MultiQuote
It's been a while since I wrote raw tsql commands.
| 