Hi m Sourabh Dey n m gettng an error
IErrorInfo.GetDescription failed with E_FAIL(0x80004005).The code is as given below:
/*using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;*/
using System;
using System.Collections;
using System.Data;
using System.IO;
using System.Data.OleDb;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Configuration;
namespace Project1
{
class Globals
{
//public const string CONNECTION_STRING = "initial catalog=Library;data source=(local);Connect Timeout=30;user id=sa;password=''";
public static string ConnectionString = "";
public static string UserName = "";
public static string Password = "";
// Users available in system
public static Hashtable UsersList = new Hashtable();
// Categories available in system
public static Hashtable CategoryList = new Hashtable();
// Books available in system
public static Hashtable BookList = new Hashtable();
public static bool isSystemUser = false;
public static int NUMBER_OF_BOOKS_ALLOWED = 3;
public Globals()
{
//
// TODO: Add constructor logic here
//
}
/// <summary>
/// Get all the application data available in system at system start up
/// </summary>
public static bool PopulateAppData()
{
//Globals.ConnectionString = ConfigurationSettings.AppSettings["LbraryConnectionString"];
// Globals.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Library.mdb;";
UsersList.Clear();
CategoryList.Clear();
BookList.Clear();
try
{
DataSet ds = new DataSet();
string OleDbCommand = "select * from Lib_Users ";
OleDbCommand += "select * from Lib_Book_Categories ";
OleDbCommand += "select a.Lib_Book_Title,a.Lib_Book_Author_Name,b.Category_Name,count(*) as Qty from Lib_Book_Details a ";
OleDbCommand += "join Lib_Book_Categories b on b.Category_ID = a.Lib_Book_Category where a.Lib_Book_Issue_Status = 0 and a.Lib_Book_In_Inventory = 1 ";
OleDbCommand += "group by a.Lib_Book_Title,a.Lib_Book_Author_Name,b.Category_Name";
OleDbDataAdapter Adapter = new OleDbDataAdapter(OleDbCommand, Globals.ConnectionString);
Adapter.Fill(ds);
Adapter.SelectCommand.Connection.Close();
if (ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
UsersList.Add(ds.Tables[0].Rows[i][1].ToString(), ds.Tables[0].Rows[i][5].ToString());
}
}
if (ds.Tables[1].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[1].Rows.Count; i++)
{
CategoryList.Add(ds.Tables[1].Rows[i][0].ToString(), ds.Tables[1].Rows[i][1].ToString());
}
}
if (ds.Tables[2].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[2].Rows.Count; i++)
{
BookList.Add(ds.Tables[2].Rows[i][0].ToString() + "~" + ds.Tables[2].Rows[i][1].ToString() + "#" + ds.Tables[2].Rows[i][2].ToString(), ds.Tables[2].Rows[i][3].ToString());
}
}
return true;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Adding category in system
/// </summary>
/// <param name="CategoryName"></param>
/// <returns></returns>
public static bool AddCategory(string CategoryName)
{
bool IsSuccess = false;
if (CategoryList.ContainsValue(CategoryName))
{
return IsSuccess;
}
try
{
//OleDbConnection conn = new OleDbConnection(Globals.ConnectionString);
OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Documents and Settings\\SOURABH DEY\\My Documents\\Visual Studio 2008\\Projects\\Library Management System\\Library Management System\\Library.mdb;");
conn.Open();
OleDbCommand cmd = new OleDbCommand("", conn);
String OleDbCommand = "BEGIN TRANSACTION\r\n";
OleDbCommand += "INSERT INTO Lib_Book_Categories (Category_Name) values ('" + CategoryName + "'\r\n)";
OleDbCommand += "Commit Transaction";
cmd.CommandText = OleDbCommand;
int Count = cmd.ExecuteNonQuery();
if (Count == 1)
{
IsSuccess = true;
}
conn.Close();
}
catch (Exception ex)
{
throw ex;
}
return IsSuccess;
}
/// <summary>
/// Login validation
/// </summary>
/// <param name="UserName"></param>
/// <param name="Password"></param>
/// <returns></returns>
public static bool ValidateId(string UserName, string Password)
{
try
{
DataSet ds = new DataSet();
string OleDbCommand = "select User_Is_System from Lib_Users where User_Name='" + UserName + "' and User_Password ='" + EncryptDecrypt.Encode(Password) + "'";
OleDbDataAdapter Adapter = new OleDbDataAdapter(OleDbCommand, Globals.ConnectionString);
Adapter.Fill(ds);
Adapter.SelectCommand.Connection.Close();
if (ds.Tables[0].Rows.Count > 0)
{
if (Convert.ToBoolean(ds.Tables[0].Rows[0]["User_Is_System"]))
isSystemUser = true;
else
isSystemUser = false;
return true;
}
}
catch (Exception ex)
{
throw ex;
}
return false;
}
/// <summary>
/// Register a new user
/// </summary>
/// <param name="UserName"></param>
/// <param name="Password"></param>
/// <returns></returns>
public static bool RegisterUser(string UserName, string Password)
{
bool IsSuccess = false;
if (UsersList.ContainsKey(UserName))
{
return IsSuccess;
}
try
{
//OleDbConnection conn = new OleDbConnection(Globals.ConnectionString);
OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Documents and Settings\\SOURABH DEY\\My Documents\\Visual Studio 2008\\Projects\\Library Management System\\Library Management System\\Library.mdb;");
conn.Open();
OleDbCommand cmd = new OleDbCommand("", conn);
String OleDbCommand = "BEGIN TRANSACTION\r\n";
OleDbCommand += "INSERT INTO Lib_Users (User_Name,User_Password,User_Is_System) values ('" + UserName + "','" + EncryptDecrypt.Encode(Password) + "'," + "0)\r\n";
OleDbCommand += "Commit Transaction";
cmd.CommandText = OleDbCommand;
int Count = cmd.ExecuteNonQuery();
if (Count == 1)
{
IsSuccess = true;
}
conn.Close();
}
catch (Exception ex)
{
throw ex;
}
return IsSuccess;
}
/// <summary>
/// change user password
/// </summary>
/// <param name="UserName"></param>
/// <param name="Password"></param>
/// <returns></returns>
public static bool ChangePassword(string UserName, string Password)
{
bool IsSuccess = false;
try
{
//OleDbConnection conn = new OleDbConnection(Globals.ConnectionString);
OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Documents and Settings\\SOURABH DEY\\My Documents\\Visual Studio 2008\\Projects\\Library Management System\\Library Management System\\Library.mdb;");
conn.Open();
OleDbCommand cmd = new OleDbCommand("", conn);
String OleDbCommand = "BEGIN TRANSACTION\r\n";
OleDbCommand += "update Lib_Users set User_Password = '" + EncryptDecrypt.Encode(Password) + "' where User_Name = '" + UserName + "'\r\n";
OleDbCommand += "Commit Transaction";
cmd.CommandText = OleDbCommand;
int Count = cmd.ExecuteNonQuery();
if (Count == 1)
{
IsSuccess = true;
}
conn.Close();
}
catch (Exception ex)
{
throw ex;
}
return IsSuccess;
}
/// <summary>
/// Add book in system
/// </summary>
/// <param name="BookTitle"></param>
/// <param name="AuthorName"></param>
/// <param name="catId"></param>
/// <param name="Quantity"></param>
/// <param name="isNewEntry"></param>
/// <returns></returns>
public static bool AddBook(string BookTitle, string AuthorName, int catId, int Quantity, string PubName, string Pubat, int Pubyear, int Edition, int volume, int Page, double Price, ref bool isNewEntry)
{
bool IsSuccess = false;
try
{
DataSet ds = new DataSet();
String OleDbCommand = "BEGIN TRANSACTION\r\n";
OleDbCommand += "select Lib_Book_ID from Lib_Book_Details where Lib_Book_Issue_Status = 0 and Lib_Book_In_Inventory = 0 and Lib_Book_Title='" + BookTitle + "' and Lib_Book_Category = '" + catId + "' and Lib_Book_Author_Name = '" + AuthorName + "'\r\n";
OleDbCommand += "Commit Transaction";
// Check if there is any record for which inventory status is set to false
OleDbDataAdapter Adapter = new OleDbDataAdapter(OleDbCommand, Globals.ConnectionString);
Adapter.Fill(ds);
Adapter.SelectCommand.Connection.Close();
int iCount = 0;
bool InsertBookData = true;
if (ds.Tables[0].Rows.Count > 0)
{
iCount = ds.Tables[0].Rows.Count;
}
//OleDbConnection conn = new OleDbConnection(Globals.ConnectionString);
OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Documents and Settings\\SOURABH DEY\\My Documents\\Visual Studio 2008\\Projects\\Library Management System\\Library Management System\\Library.mdb;");
conn.Open();
OleDbCommand cmd = new OleDbCommand("", conn); ;
if (iCount > 0)
{
isNewEntry = false;
int LoopCount = 0;
if (iCount >= Quantity)
{
LoopCount = Quantity;
Quantity = 0;
iCount = 0;
InsertBookData = false;
}
else
{
LoopCount = iCount;
Quantity = Quantity - iCount;
}
OleDbCommand = "BEGIN TRANSACTION\r\n";
// for records having inventory status as false update status to true
for (int i = 0; i < LoopCount; i++)
OleDbCommand += "Update Lib_Book_Details set Lib_Book_In_Inventory = 1 where Lib_Book_ID ='" + Convert.ToInt32(ds.Tables[0].Rows[i][0]) + "'\r\n";
OleDbCommand += "Commit Transaction";
cmd.CommandText = OleDbCommand;
int Count = cmd.ExecuteNonQuery();
if (Count >= 1)
{
IsSuccess = true;
}
}
// insert new rows if there are no records or no records with inventory status as false
// or there are some row with inventory status false but those are less than quantity of
// books being added
if (InsertBookData)
{
OleDbCommand = "BEGIN TRANSACTION\r\n";
for (int i = 0; i < Quantity; i++)
OleDbCommand += "INSERT INTO Lib_Book_Details (Lib_Book_Title,Lib_Book_Category,Lib_Book_Author_Name,Lib_Book_Publisher_Name,Lib_Book_PublisAt_Name,Lib_Book_YearPubish,Lib_Book_Edition,Lib_Book_Volume,Lib_Book_Page,Lib_Book_Price) values ('" + BookTitle + "'," + catId + ",'" + AuthorName + "','" + PubName + "','" + Pubat + "','" + Pubyear + "','" + Edition + "','" + volume + "','" + Page + "','" + Price + "'\r\n)";
OleDbCommand += "Commit Transaction";
cmd.CommandText = OleDbCommand;
int Count = cmd.ExecuteNonQuery();
if (Count >= 1)
{
IsSuccess = true;
}
}
conn.Close();
}
catch (Exception ex)
{
throw ex;
}
return IsSuccess;
}
/// <summary>
/// Remove book from system
/// </summary>
/// <param name="BookTitle"></param>
/// <param name="Quantity"></param>
/// <param name="AuthorName"></param>
/// <param name="CategoryName"></param>
/// <returns></returns>
public static bool RemoveBook(string BookTitle, int Quantity, string AuthorName, string CategoryName)
{
bool IsSuccess = false;
try
{
//OleDbConnection conn = new OleDbConnection(Globals.ConnectionString);
OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Documents and Settings\\SOURABH DEY\\My Documents\\Visual Studio 2008\\Projects\\Library Management System\\Library Management System\\Library.mdb;");
conn.Open();
OleDbCommand cmd = new OleDbCommand("", conn);
String OleDbCommand = "BEGIN TRANSACTION\r\n";
// rows are not removed from system but inventory status is set as false and such records
// are not at all considered in system for issue etc.
for (int i = 0; i < Quantity; i++)
{
OleDbCommand += "update Lib_Book_Details set Lib_Book_In_Inventory = 0 where Lib_Book_ID = ";
OleDbCommand += "(select top 1 Lib_Book_ID from Lib_Book_Details a join Lib_Book_Categories b on b.Category_ID = a.Lib_Book_Category where a.Lib_Book_Title = '" + BookTitle + "' and a.lib_book_issue_status = 0 and a.Lib_Book_In_Inventory = 1 ";
OleDbCommand += "and a.Lib_Book_Author_Name= '" + AuthorName + "' and b.Category_Name = '" + CategoryName + "')\r\n";
}
OleDbCommand += "Commit Transaction";
cmd.CommandText = OleDbCommand;
int Count = cmd.ExecuteNonQuery();
if (Count >= 1)
{
IsSuccess = true;
}
conn.Close();
}
catch (Exception ex)
{
throw ex;
}
return IsSuccess;
}
/// <summary>
/// Search books
/// </summary>
/// <param name="BookTitle"></param>
/// <param name="Category"></param>
/// <returns></returns>
public static DataSet SearchBook(string BookTitle, string Category)
{
DataSet ds = new DataSet();
string OleDbCommand = "";
// Book and category both criterias are mentioned by user
// records retrieved will be those books which are there in inventory and not issued
if (!BookTitle.Equals("") && !Category.Equals(""))
{
OleDbCommand = "select a.Lib_Book_Title as 'Book',a.Lib_Book_Author_Name as 'Author',count(*) as AvailbleQty,b.Category_Name as 'Category' from Lib_Book_Details a join Lib_Book_Categories b ";
OleDbCommand += "on b.Category_ID = a.Lib_Book_Category where Lib_Book_Title = '" + BookTitle + "' and b.Category_Name = '" + Category + "' and Lib_Book_Issue_Status = 0 and a.Lib_Book_In_Inventory = 1 group by a.lib_book_title,a.Lib_Book_Author_Name,b.Category_Name";
}
// Book criteria is mentioned by user
// records retrieved will be those books which are there in inventory and not issued
if (!BookTitle.Equals("") && Category.Equals(""))
{
OleDbCommand = "select a.Lib_Book_Title as 'Book',a.Lib_Book_Author_Name as 'Author',count(*) as AvailbleQty,b.Category_Name as 'Category' from Lib_Book_Details a join Lib_Book_Categories b ";
OleDbCommand += "on b.Category_ID = a.Lib_Book_Category where Lib_Book_Title = '" + BookTitle + "' and Lib_Book_Issue_Status = 0 and a.Lib_Book_In_Inventory = 1 group by a.Lib_Book_Title,a.Lib_Book_Author_Name,b.Category_Name";
}
// Category criteria is mentioned by user
// records retrieved will be those books which are there in inventory and not issued
if (BookTitle.Equals("") && !Category.Equals(""))
{
OleDbCommand = "select a.Lib_Book_Title as 'Book',a.Lib_Book_Author_Name as 'Author',count(*) as AvailbleQty,b.Category_Name as 'Category' from Lib_Book_Details a join Lib_Book_Categories b ";
OleDbCommand += "on b.Category_ID = a.Lib_Book_Category where b.Category_Name = '" + Category + "' and Lib_Book_Issue_Status = 0 and a.Lib_Book_In_Inventory = 1 group by a.Lib_Book_Title,a.Lib_Book_Author_Name,b.Category_Name";
}
try
{
OleDbDataAdapter Adapter = new OleDbDataAdapter(OleDbCommand, Globals.ConnectionString);
Adapter.Fill(ds);
Adapter.SelectCommand.Connection.Close();
}
catch (Exception ex)
{
throw ex;
}
return ds;
}
/// <summary>
/// Chack if a book is already issued to requesting user
/// </summary>
/// <param name="BookTitle"></param>
/// <param name="Author"></param>
/// <param name="CategoryName"></param>
/// <returns></returns>
public static bool ValidateBook(string BookTitle, string Author, string CategoryName)
{
try
{
DataSet ds = new DataSet();
string OleDbCommand = "select a.Lib_Book_Title,a.Lib_Book_Author_Name as 'Author',b.Category_Name from Lib_Book_Details a join Lib_Book_Categories b on b.Category_ID = a.Lib_Book_Category ";
OleDbCommand += "join Lib_Book_Issue_Details c on c.Lib_Book_ID = a.Lib_Book_ID where c.Lib_Book_Issued_To = '" + Globals.UserName + "'";
OleDbDataAdapter Adapter = new OleDbDataAdapter(OleDbCommand, Globals.ConnectionString);
Adapter.Fill(ds);
Adapter.SelectCommand.Connection.Close();
if (ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (ds.Tables[0].Rows[i][0].ToString().Equals(BookTitle)
&& ds.Tables[0].Rows[i][1].ToString().Equals(Author)
&& ds.Tables[0].Rows[i][2].ToString().Equals(CategoryName))
{
return false;
}
}
}
return true;
}
catch (Exception ex)
{
throw ex;
}
//return false;
}
/// <summary>
/// Issue a book - set issue status as true, insert data in issue details table
/// </summary>
/// <param name="BookTitle"></param>
/// <param name="Author"></param>
/// <param name="Category"></param>
/// <returns></returns>
public static bool IssueBook(string BookTitle, string Author, string Category)
{
bool IsSuccess = false;
try
{
//OleDbConnection conn = new OleDbConnection(Globals.ConnectionString);
OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Documents and Settings\\SOURABH DEY\\My Documents\\Visual Studio 2008\\Projects\\Library Management System\\Library Management System\\Library.mdb;");
conn.Open();
OleDbCommand cmd = new OleDbCommand("", conn);
String OleDbCommand = "BEGIN TRANSACTION\r\n";
OleDbCommand += "declare @bookid int\r\n";
OleDbCommand += "select top 1 @bookid =Lib_Book_ID from Lib_Book_Details a join Lib_Book_Categories b on b.Category_ID = a.Lib_Book_Category ";
OleDbCommand += "where a.Lib_Book_Title = '" + BookTitle + "'and a.Lib_Book_Issue_Status = 0 and a.Lib_Book_In_Inventory = 1 and a.Lib_Book_Author_Name='" + Author + "'";
OleDbCommand += "and b.Category_Name = '" + Category + "'\r\n";
OleDbCommand += "insert into Lib_Book_Issue_Details (Lib_Book_ID,Lib_Book_Issued_On,Lib_Book_Issued_To) values (@bookid,getdate(),'" + Globals.UserName + "')\r\n";
OleDbCommand += "update Lib_Book_Details set Lib_Book_Issue_Status = 1 where Lib_Book_ID = @bookid\r\n";
OleDbCommand += "update Lib_Users set Number_Of_Books_Issued = Number_Of_Books_Issued + 1 where User_Name = '" + Globals.UserName + "'\r\n";
OleDbCommand += "Commit Transaction";
cmd.CommandText = OleDbCommand;
int Count = cmd.ExecuteNonQuery();
if (Count != 1)
{
IsSuccess = true;
}
conn.Close();
}
catch (Exception ex)
{
throw ex;
}
return IsSuccess;
}
/// <summary>
/// Return book. Update issue status to false, delete entry from issue details table
/// </summary>
/// <param name="BookId"></param>
/// <param name="IssueId"></param>
/// <returns></returns>
public static bool ReturnBook(int BookId, int IssueId)
{
bool IsSuccess = false;
try
{
//OleDbConnection conn = new OleDbConnection(Globals.ConnectionString);
OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Documents and Settings\\SOURABH DEY\\My Documents\\Visual Studio 2008\\Projects\\Library Management System\\Library Management System\\Library.mdb;");
conn.Open();
OleDbCommand cmd = new OleDbCommand("", conn);
String OleDbCommand = "BEGIN TRANSACTION\r\n";
OleDbCommand += "update Lib_Book_Details set Lib_Book_Issue_Status=0 where Lib_Book_ID =" + BookId + "\r\n";
OleDbCommand += "delete from Lib_Book_Issue_Details where Lib_Book_Issue_ID =" + IssueId + "\r\n";
OleDbCommand += "update Lib_Users set Number_Of_Books_Issued = Number_Of_Books_Issued - 1 where User_Name = '" + Globals.UserName + "'\r\n";
OleDbCommand += "Commit Transaction";
cmd.CommandText = OleDbCommand;
int Count = cmd.ExecuteNonQuery();
if (Count != 1)
{
IsSuccess = true;
}
conn.Close();
}
catch (Exception ex)
{
throw ex;
}
return IsSuccess;
}
/// <summary>
/// Issued books data to be used in return books screen
/// </summary>
/// <returns></returns>
public static DataSet PopulateReturnBooks()
{
DataSet ds = new DataSet();
string OleDbCommand = "";
OleDbCommand = "select a.Lib_Book_ID,a.Lib_Book_Issue_ID,b.Lib_Book_Title,b.Lib_Book_Author_Name,c.Category_Name from Lib_Book_Issue_Details a ";
OleDbCommand += "join Lib_Book_Details b on a.Lib_Book_ID = b.Lib_Book_ID join Lib_Book_Categories c on b.Lib_Book_Category = c.Category_ID ";
OleDbCommand += "where a.Lib_Book_Issued_To = '" + Globals.UserName + "'";
try
{
OleDbDataAdapter Adapter = new OleDbDataAdapter(OleDbCommand, Globals.ConnectionString);
Adapter.Fill(ds);
//Adapter.SelectCommand.Connection.Close();
}
catch (Exception ex)
{
throw ex;
}
return ds;
}
}
}