//---- public Boolean updateInventoryForApproveStockDetail(gatepassheader gtnh, List <gatepassdetail> getPDet) { Boolean status = true; string utString = ""; string updateSQL = ""; try { foreach (gatepassdetail gtnd in getPDet) { double quant = gtnd.Quantity; int RefNo = gtnd.refNo; updateSQL = "update Stock set " + " PresentStock=" + "( (select PresentStock from Stock where RowID = " + RefNo + ")-" + quant + ")" + " where RowID=" + RefNo; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("update", "Stock", "", updateSQL) + Main.QueryDelimiter; } if (!UpdateTable.UT(utString)) { MessageBox.Show("failed to Update In Reference Number Wise getpassdetail in stock"); status = false; } } catch (Exception ex) { status = false; } return(status); }
public Boolean AcceptGatePass(gatepassheader gtnh) { Boolean status = true; string utString = ""; try { string updateSQL = "update InventoryGatepassHeader set " + "AcceptedUser='******'" + ", AcceptanceDate=convert(date, getdate())" + ", AcceptanceStatus= 1" + ", commentStatus='" + gtnh.CommentStatus + "'" + " where DocumentID='" + gtnh.DocumentID + "'" + " and TemporaryNo=" + gtnh.TemporaryNo + " and TemporaryDate='" + gtnh.TemporaryDate.ToString("yyyy-MM-dd") + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("update", "InventoryGatepassHeader", "", updateSQL) + Main.QueryDelimiter; if (!UpdateTable.UT(utString)) { status = false; } } catch (Exception) { status = false; } return(status); }
public Boolean AcceptReturningQnatGatePass(gatepassheader gtnh, List <gatepassdetail> gplist) { Boolean status = true; string utString = ""; try { string updateSQL = "update InventoryGatepassHeader set ReturnStatus= 0 " + " where DocumentID='" + gtnh.DocumentID + "'" + " and TemporaryNo=" + gtnh.TemporaryNo + " and TemporaryDate='" + gtnh.TemporaryDate.ToString("yyyy-MM-dd") + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("update", "InventoryGatepassHeader", "", updateSQL) + Main.QueryDelimiter; foreach (gatepassdetail gtnd in gplist) { updateSQL = "update InventoryGatepassDetail " + "set QuantityReturned = " + (gtnd.ReturningQuantity + gtnd.ReturnedQuantity) + " , QuantityReturning = 0" + " where RowID = " + gtnd.RowID; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("insert", "InventoryGatepassDetail", "", updateSQL) + Main.QueryDelimiter; double quant = gtnd.ReturningQuantity; int RefNo = gtnd.refNo; updateSQL = "update Stock set " + " PresentStock=" + "( (select isnull(PresentStock,0) from Stock where RowID = " + RefNo + ")+" + quant + ")" + " where RowID=" + RefNo; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("update", "Stock", "", updateSQL) + Main.QueryDelimiter; updateSQL = "update Stock set " + " IssueQuantity=" + "( (select isnull(IssueQuantity,0) from Stock where RowID = " + RefNo + ")-" + quant + ")" + " where RowID=" + RefNo; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("update", "Stock", "", updateSQL) + Main.QueryDelimiter; } if (!UpdateTable.UT(utString)) { status = false; } } catch (Exception) { status = false; } return(status); }
//public Boolean forwardGTN(getpassheader gtnh) //{ // Boolean status = true; // string utString = ""; // try // { // string updateSQL = "update getpassheader set DocumentStatus=" + (gtnh.DocumentStatus + 1) + // ", ReceiveStatus = " + gtnh.ReceiveStatus + // ", forwardUser='******'" + // ", commentStatus='" + gtnh.CommentStatus + "'" + // ", ForwarderList='" + gtnh.ForwarderList + "'" + // " where DocumentID='" + gtnh.DocumentID + "'" + // " and TemporaryNo=" + gtnh.TemporaryNo + // " and TemporaryDate='" + gtnh.TemporaryDate.ToString("yyyy-MM-dd") + "'"; // utString = utString + updateSQL + Main.QueryDelimiter; // utString = utString + // ActivityLogDB.PrepareActivityLogQquerString("update", "getpassheader", "", updateSQL) + // Main.QueryDelimiter; // if (!UpdateTable.UT(utString)) // { // status = false; // } // } // catch (Exception) // { // status = false; // } // return status; //} //public Boolean reverseGTN(getpassheader gtnh) //{ // Boolean status = true; // string utString = ""; // try // { // string updateSQL = "update getpassheader set DocumentStatus=" + gtnh.DocumentStatus + // ", forwardUser='******'" + // ", commentStatus='" + gtnh.CommentStatus + "'" + // ", ForwarderList='" + gtnh.ForwarderList + "'" + // ", ReceiveStatus=" + gtnh.ReceiveStatus + // " where DocumentID='" + gtnh.DocumentID + "'" + // " and TemporaryNo=" + gtnh.TemporaryNo + // " and TemporaryDate='" + gtnh.TemporaryDate.ToString("yyyy-MM-dd") + "'"; // utString = utString + updateSQL + Main.QueryDelimiter; // utString = utString + // ActivityLogDB.PrepareActivityLogQquerString("update", "getpassheader", "", updateSQL) + // Main.QueryDelimiter; // if (!UpdateTable.UT(utString)) // { // status = false; // } // } // catch (Exception) // { // status = false; // } // return status; //} public Boolean ApproveGatePass(gatepassheader gtnh, List <gatepassdetail> gplist) { Boolean status = true; string utString = ""; try { string updateSQL = "update InventoryGatepassHeader set DocumentStatus=99, status=1 " + ", ApproveUser ='******'" + ", ForwarderList ='" + gtnh.ForwarderList + "'" + ", commentStatus='" + gtnh.CommentStatus + "'" + ", GatePassNo =" + gtnh.GatePassNo + ", GatePassDate =convert(date, getdate())" + " where DocumentID='" + gtnh.DocumentID + "'" + " and TemporaryNo=" + gtnh.TemporaryNo + " and TemporaryDate='" + gtnh.TemporaryDate.ToString("yyyy-MM-dd") + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("update", "InventoryGatepassHeader", "", updateSQL) + Main.QueryDelimiter; foreach (gatepassdetail gtnd in gplist) { double quant = gtnd.Quantity; int RefNo = gtnd.refNo; updateSQL = "update Stock set " + " PresentStock=" + "( (select PresentStock from Stock where RowID = " + RefNo + ")-" + quant + ")" + " where RowID=" + RefNo; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("update", "Stock", "", updateSQL) + Main.QueryDelimiter; updateSQL = "update Stock set " + "IssueQuantity=" + "( (select isnull(IssueQuantity,0) from Stock where RowID = " + RefNo + ")+" + quant + ")" + " where RowID=" + RefNo; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("update", "Stock", "", updateSQL) + Main.QueryDelimiter; } if (!UpdateTable.UT(utString)) { status = false; } } catch (Exception) { status = false; } return(status); }
public Boolean validategetpassheader(gatepassheader gtnh) { Boolean status = true; if (gtnh.FromOffice == gtnh.ToOffice) { MessageBox.Show("From Location and To Location Should not be same"); return(false); } if (gtnh.FromOffice == null) { return(false); } if (gtnh.ToOffice == null) { return(false); } return(status); }
public Boolean ReturnGatePass(gatepassheader gtnh, List <gatepassdetail> gplist) { Boolean status = true; string utString = ""; try { string updateSQL = "update InventoryGatepassHeader set ReturnStatus=" + (gtnh.ReturnStatus + 1) + " where DocumentID='" + gtnh.DocumentID + "'" + " and TemporaryNo=" + gtnh.TemporaryNo + " and TemporaryDate='" + gtnh.TemporaryDate.ToString("yyyy-MM-dd") + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("update", "InventoryGatepassHeader", "", updateSQL) + Main.QueryDelimiter; foreach (gatepassdetail gtnd in gplist) { updateSQL = "update InventoryGatepassDetail " + "set QuantityReturning = " + gtnd.ReturningQuantity + " where RowID = " + gtnd.RowID; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("insert", "InventoryGatepassDetail", "", updateSQL) + Main.QueryDelimiter; } if (!UpdateTable.UT(utString)) { status = false; } } catch (Exception) { status = false; } return(status); }
public Boolean insertInvGatePassHEaderAndDetail(gatepassheader gtnh, List <gatepassdetail> getpassdetails) { Boolean status = true; string utString = ""; string updateSQL = ""; try { gtnh.TemporaryNo = DocumentNumberDB.getNumber(gtnh.DocumentID, 1); if (gtnh.TemporaryNo <= 0) { MessageBox.Show("Error in Creating New Number"); return(false); } updateSQL = "update DocumentNumber set TempNo =" + gtnh.TemporaryNo + " where FYID='" + Main.currentFY + "' and DocumentID='" + gtnh.DocumentID + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("update", "DocumentNumber", "", updateSQL) + Main.QueryDelimiter; updateSQL = "insert into InventoryGatepassHeader " + "(DocumentID,TemporaryNo,TemporaryDate,GatePassNo,GatePassDate,FromOffice,ToOffice,CustomerID,Remarks,SpecialNotes," + "Comments,CommentStatus,AcceptanceDate,AcceptedUser,AcceptanceStatus," + "ForwarderList,Status,DocumentStatus,CreateTime,CreateUser)" + " values (" + "'" + gtnh.DocumentID + "'," + gtnh.TemporaryNo + "," + "'" + gtnh.TemporaryDate.ToString("yyyy-MM-dd") + "'," + gtnh.GatePassNo + "," + "'" + gtnh.GatePassDate.ToString("yyyy-MM-dd") + "'," + "'" + gtnh.FromOffice + "'," + "'" + gtnh.ToOffice + "'," + "'" + gtnh.CustomerID + "'," + "'" + gtnh.Remarks + "'," + "'" + gtnh.SpecialNotes + "'," + "'" + gtnh.Comments + "'," + "'" + gtnh.CommentStatus + "'," + "'" + DateTime.Parse("1900-01-01").ToString("yyyy-MM-dd") + "'," + "'" + gtnh.AcceptedUser + "'," + +gtnh.AcceptanceStatus + "," + "'" + gtnh.ForwarderList + "'," + gtnh.Status + "," + gtnh.DocumentStatus + "," + "GETDATE()" + "," + "'" + Login.userLoggedIn + "')"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("insert", "InventoryGatepassHeader", "", updateSQL) + Main.QueryDelimiter; updateSQL = "Delete from InventoryGatepassDetail where DocumentID='" + gtnh.DocumentID + "'" + " and TemporaryNo=" + gtnh.TemporaryNo + " and TemporaryDate='" + gtnh.TemporaryDate.ToString("yyyy-MM-dd") + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("delete", "InventoryGatepassDetail", "", updateSQL) + Main.QueryDelimiter; foreach (gatepassdetail gtnd in getpassdetails) { updateSQL = "insert into InventoryGatepassDetail " + "(DocumentID,TemporaryNo,TemporaryDate,StockItemID,ModelNo,Quantity,Value,QuantityReturning,QuantityReturned,MRNNo,MRNDate,BatchNo,ExpiryDate,SerialNo,PurchaseQuantity, " + "PurchasePrice,PurchaseTax,SupplierID,StockReferenceNo) " + "values ('" + gtnh.DocumentID + "'," + gtnh.TemporaryNo + "," + "'" + gtnh.TemporaryDate.ToString("yyyy-MM-dd") + "'," + "'" + gtnd.StockitemID + "'," + "'" + gtnd.ModelNo + "'," + gtnd.Quantity + "," + gtnd.Value + "," + gtnd.ReturningQuantity + "," + gtnd.ReturnedQuantity + "," + gtnd.MRNNo + "," + "'" + gtnd.MRNDate.ToString("yyyy-MM-dd") + "'," + "'" + gtnd.BatchNo + "'," + "'" + gtnd.ExpiryDate.ToString("yyyy-MM-dd") + "'," + "'" + gtnd.SerialNo + "'," + gtnd.PurchaseQuantity + "," + gtnd.PurchasePrice + "," + gtnd.PurchaseTax + "," + "'" + gtnd.SupplierID + "'," + +gtnd.refNo + ")"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("insert", "InventoryGatepassDetail", "", updateSQL) + Main.QueryDelimiter; } if (!UpdateTable.UT(utString)) { status = false; } } catch (Exception ex) { status = false; MessageBox.Show("Transaction Exception Occured"); } return(status); }
public Boolean updateInvGatePassHEaderAndDetail(gatepassheader gtnh, gatepassheader prevgtnh, List <gatepassdetail> getpassdetails) { Boolean status = true; string utString = ""; try { string updateSQL = "update InventoryGatepassHeader set FromOffice='" + gtnh.FromOffice + "',ToOffice='" + gtnh.ToOffice + "', CustomerID ='" + gtnh.CustomerID + "', Remarks ='" + gtnh.Remarks + "', SpecialNotes ='" + gtnh.SpecialNotes + "', CommentStatus='" + gtnh.CommentStatus + "', Comments='" + gtnh.Comments + "', ForwarderList='" + gtnh.ForwarderList + "' where DocumentID='" + gtnh.DocumentID + "'" + " and TemporaryNo=" + gtnh.TemporaryNo + " and TemporaryDate='" + gtnh.TemporaryDate.ToString("yyyy-MM-dd") + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("update", "InventoryGatepassHeader", "", updateSQL) + Main.QueryDelimiter; updateSQL = "Delete from InventoryGatepassDetail where DocumentID='" + gtnh.DocumentID + "'" + " and TemporaryNo=" + gtnh.TemporaryNo + " and TemporaryDate='" + gtnh.TemporaryDate.ToString("yyyy-MM-dd") + "'"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("delete", "InventoryGatepassDetail", "", updateSQL) + Main.QueryDelimiter; foreach (gatepassdetail gtnd in getpassdetails) { updateSQL = "insert into InventoryGatepassDetail " + "(DocumentID,TemporaryNo,TemporaryDate,StockItemID,ModelNo,Quantity,Value,QuantityReturning,QuantityReturned,MRNNo,MRNDate,BatchNo,ExpiryDate,SerialNo,PurchaseQuantity, " + "PurchasePrice,PurchaseTax,SupplierID,StockReferenceNo) " + "values ('" + gtnd.DocumentID + "'," + gtnd.TemporaryNo + "," + "'" + gtnd.TemporaryDate.ToString("yyyy-MM-dd") + "'," + "'" + gtnd.StockitemID + "'," + "'" + gtnd.ModelNo + "'," + gtnd.Quantity + "," + gtnd.Value + "," + gtnd.ReturningQuantity + "," + gtnd.ReturnedQuantity + "," + gtnd.MRNNo + "," + "'" + gtnd.MRNDate.ToString("yyyy-MM-dd") + "'," + "'" + gtnd.BatchNo + "'," + "'" + gtnd.ExpiryDate.ToString("yyyy-MM-dd") + "'," + "'" + gtnd.SerialNo + "'," + gtnd.PurchaseQuantity + "," + gtnd.PurchasePrice + "," + gtnd.PurchaseTax + "," + "'" + gtnd.SupplierID + "'," + +gtnd.refNo + ")"; utString = utString + updateSQL + Main.QueryDelimiter; utString = utString + ActivityLogDB.PrepareActivityLogQquerString("insert", "InventoryGatepassDetail", "", updateSQL) + Main.QueryDelimiter; } if (!UpdateTable.UT(utString)) { status = false; MessageBox.Show("Transaction Exception Occured"); } } catch (Exception ex) { status = false; } return(status); }
//public Boolean updateGTNInStock(List<getpassdetail> gtndList, string store) //{ // Boolean status = true; // try // { // foreach (getpassdetail gtnd in gtndList) // { // if (!CheckStockAvailability(gtnd.refNo, gtnd.Quantity)) // { // status = false; // break; // } // } // if (status) // { // //stock available. select rows from stock table. update stcok table. insert GTN records in stock table // } // } // catch (Exception ex) // { // status = false; // } // return status; //} //public Boolean CheckStockAvailability(int stockRefNo, double Qunt) //{ // Boolean status = false; // try // { // SqlConnection conn = new SqlConnection(Login.connString); // string query = "select PresentStock" + // " from Stock where RowID =" + stockRefNo; // SqlCommand cmd = new SqlCommand(query, conn); // conn.Open(); // SqlDataReader reader = cmd.ExecuteReader(); // if (reader.Read()) // { // Double aqty = reader.GetDouble(0); // if (aqty >= Qunt) // status = true; // } // conn.Close(); // } // catch (Exception) // { // MessageBox.Show("Error querying stock quantity"); // status = false; // } // return status; //} public static List <gatepassdetail> getGatePassdetail(gatepassheader gtnh) { gatepassdetail gtnd; List <gatepassdetail> getpassdetail = new List <gatepassdetail>(); try { string query = ""; SqlConnection conn = new SqlConnection(Login.connString); query = "select RowID,DocumentID,TemporaryNo, TemporaryDate,StockItemID,StockItemName,ModelNo, ModelName,Quantity,QuantityReturning, " + "QuantityReturned,MRNNo,MRNDate,BatchNo,SerialNo,ExpiryDate,PurchaseQuantity,PurchasePrice,PurchaseTax,SupplierID,SupplierName,StockReferenceNo,Value " + "from ViewInventoryGatepassDetail " + "where DocumentID='" + gtnh.DocumentID + "'" + " and TemporaryNo=" + gtnh.TemporaryNo + " and TemporaryDate='" + gtnh.TemporaryDate.ToString("yyyy-MM-dd") + "'" + " order by StockItemID"; SqlCommand cmd = new SqlCommand(query, conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { gtnd = new gatepassdetail(); gtnd.RowID = reader.GetInt32(0); gtnd.DocumentID = reader.GetString(1); gtnd.TemporaryNo = reader.GetInt32(2); gtnd.TemporaryDate = reader.GetDateTime(3).Date; gtnd.StockitemID = reader.GetString(4); gtnd.StockItemName = reader.GetString(5); gtnd.ModelNo = reader.IsDBNull(6)?"NA":reader.GetString(6); gtnd.ModelName = reader.IsDBNull(7) ? "NA":reader.GetString(7); gtnd.Quantity = reader.GetDouble(8); gtnd.ReturningQuantity = reader.GetDouble(9); gtnd.ReturnedQuantity = reader.GetDouble(10); gtnd.MRNNo = reader.IsDBNull(11)? 0:reader.GetInt32(11); if (reader.IsDBNull(12)) { gtnd.MRNDate = DateTime.Parse("1900-01-01"); } else { gtnd.MRNDate = reader.GetDateTime(12).Date; } gtnd.BatchNo = reader.IsDBNull(13) ? "NA" : reader.GetString(13); gtnd.SerialNo = reader.IsDBNull(14) ? "NA" : reader.GetString(14); if (reader.IsDBNull(15)) { gtnd.ExpiryDate = DateTime.Parse("1900-01-01"); } else { gtnd.ExpiryDate = reader.GetDateTime(15); } gtnd.PurchaseQuantity = reader.GetDouble(16); gtnd.PurchasePrice = reader.GetDouble(17); gtnd.PurchaseTax = reader.GetDouble(18); gtnd.SupplierID = reader.IsDBNull(19)? "NA" : reader.GetString(19); gtnd.SupplierName = reader.IsDBNull(20)?"NA" : reader.GetString(20); if (reader.IsDBNull(21)) { gtnd.refNo = 0; } else { gtnd.refNo = reader.GetInt32(21); } gtnd.Value = reader.IsDBNull(22) ? 0 : reader.GetDouble(22); getpassdetail.Add(gtnd); } conn.Close(); } catch (Exception ex) { MessageBox.Show("Error querying Gate Pass Details"); } return(getpassdetail); }
public List <gatepassheader> getFilteredGPHeader(string userList, int opt, string userCommentStatusString) { //approved user comment status string string acStr = ""; try { acStr = userCommentStatusString.Substring(0, userCommentStatusString.Length - 2) + "1" + Main.delimiter2; } catch (Exception ex) { acStr = ""; } //----- gatepassheader gheader; List <gatepassheader> gheaderlist = new List <gatepassheader>(); try { string query1 = "select RowID, DocumentID, DocumentName,TemporaryNo,TemporaryDate," + " GatePassNO,GatePassDate,FromOffice,FromOfficeName,ToOffice,ToOfficeName,CustomerID,CustomerName,Comments,Commentstatus,AcceptanceDate,AcceptedUser,AcceptanceStatus," + "CreateUser,CreateTime,ForwardUser,ApproveUser,ForwarderList,Status,DocumentStatus,ReturnStatus,Remarks,SpecialNotes" + " from ViewInventoryGatepassHeader" + " where ((forwardUser='******' and DocumentStatus between 2 and 98) " + " or (createuser='******' and DocumentStatus=1)" + " or (commentStatus like '%" + userCommentStatusString + "%' and DocumentStatus between 1 and 98)) and Status not in (7,98) order by TemporaryDate desc,DocumentID asc,TemporaryNo desc"; //string query2 = "select RowID, DocumentID, DocumentName,TemporaryNo,TemporaryDate," + // " GetPassNO,GetPassDate,FromOffice,FromOfficeName,ToOffice,ToOfficeName,CustomerID,CustomerName,Comments,Commentstatus,AcceptanceDate,AcceptedUser,AcceptanceStatus," + // "CreateUser,CreateTime,ForwardUser,ApproveUser,ForwarderList,Status,DocumentStatus" + // " from ViewInventoryGatepassHeader" + // " where ((createuser='******' and DocumentStatus between 2 and 98 ) " + // " or (ForwarderList like '%" + userList + "%' and DocumentStatus between 2 and 98 and ForwardUser <> '" + Login.userLoggedIn + "')" + // " or (commentStatus like '%" + acStr + "%' and DocumentStatus between 1 and 98)) and Status not in (7,98) order by TemporaryDate desc,DocumentID asc,TemporaryNo desc"; string query6 = "select RowID, DocumentID, DocumentName,TemporaryNo,TemporaryDate," + " GatePassNO,GatePassDate,FromOffice,FromOfficeName,ToOffice,ToOfficeName,CustomerID,CustomerName,Comments,Commentstatus,AcceptanceDate,AcceptedUser,AcceptanceStatus," + "CreateUser,CreateTime,ForwardUser,ApproveUser,ForwarderList,Status,DocumentStatus,ReturnStatus,Remarks,SpecialNotes" + " from ViewInventoryGatepassHeader" + " where DocumentStatus = 99 and Status = 1 order by GatePassDate desc,DocumentID asc,GatePassNO desc"; string query4 = "select RowID, DocumentID, DocumentName,TemporaryNo,TemporaryDate," + " GatePassNO,GatePassDate,FromOffice,FromOfficeName,ToOffice,ToOfficeName,CustomerID,CustomerName,Comments,Commentstatus,AcceptanceDate,AcceptedUser,AcceptanceStatus," + "CreateUser,CreateTime,ForwardUser,ApproveUser,ForwarderList,Status,DocumentStatus,ReturnStatus,Remarks,SpecialNotes" + " from ViewInventoryGatepassHeader" + " where DocumentStatus = 99 and Status = 1 and ForwarderList like '%" + userList + "%' order by GatePassDate desc,DocumentID asc,GatePassNO desc"; SqlConnection conn = new SqlConnection(Login.connString); string query = ""; switch (opt) { case 1: query = query1; break; case 6: query = query6; break; case 4: query = query4; break; default: query = ""; break; } SqlCommand cmd = new SqlCommand(query, conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { gheader = new gatepassheader(); gheader.RowID = reader.GetInt32(0); gheader.DocumentID = reader.GetString(1); gheader.DocumentName = reader.GetString(2); gheader.TemporaryNo = reader.GetInt32(3); gheader.TemporaryDate = reader.GetDateTime(4); gheader.GatePassNo = reader.GetInt32(5); gheader.GatePassDate = reader.GetDateTime(6); gheader.FromOffice = reader.GetString(7); gheader.FromOfficeName = reader.GetString(8); gheader.ToOffice = reader.GetString(9); gheader.ToOfficeName = reader.GetString(10); gheader.CustomerID = reader.IsDBNull(11)? "" : reader.GetString(11); gheader.CustomerName = reader.IsDBNull(12) ? "" : reader.GetString(12); gheader.Comments = reader.GetString(13); gheader.CommentStatus = reader.GetString(14); gheader.AcceptanceDate = reader.GetDateTime(15); gheader.AcceptedUser = reader.IsDBNull(16)?"":reader.GetString(16); gheader.AcceptanceStatus = reader.GetInt32(17); gheader.CreateUser = reader.GetString(18); gheader.CreateTime = reader.GetDateTime(19); gheader.ForwardUser = reader.IsDBNull(20) ? "" : reader.GetString(20); gheader.ApproveUser = reader.IsDBNull(21) ? "" : reader.GetString(21); gheader.ForwarderList = reader.GetString(22); gheader.Status = reader.GetInt32(23); gheader.DocumentStatus = reader.GetInt32(24); gheader.ReturnStatus = reader.IsDBNull(25) ? 0 : reader.GetInt32(25); gheader.Remarks = reader.IsDBNull(26) ? "" : reader.GetString(26); gheader.SpecialNotes = reader.IsDBNull(27) ? "" : reader.GetString(27); gheaderlist.Add(gheader); } conn.Close(); } catch (Exception ex) { MessageBox.Show("Error querying GTN Header Data"); } return(gheaderlist); }