public static CatalogPieceSize GetPieceSizeByNo(int pPieceNo, int pSizeNo) { CatalogPieceSize size = new CatalogPieceSize(); string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\\PineSpringsPottery.accdb"; OleDbConnection connection = new OleDbConnection(connectionString); //Open connection if not already if (connection.State == ConnectionState.Closed) { connection.Open(); } string selectStatement = "SELECT PIECE_SIZE.SizeNo, PIECE_SIZE.Dimensions, PIECE_SIZE.BasePrice, PIECE_SIZE.SizeDescription, PIECE_SIZE.TotalPounds " + "FROM PIECE_SIZE WHERE PIECE_SIZE.PieceNo = " + pPieceNo + " AND PIECE_SIZE.SizeNo = " + pSizeNo; //Create Command for select statement OleDbCommand selectCmd = new OleDbCommand(selectStatement, connection); //execute reader and close reader OleDbDataReader reader = selectCmd.ExecuteReader(); while (reader.Read()) { size.sizeNo = Convert.ToInt32(reader["SizeNo"]); size.totalPounds = Convert.ToDecimal(reader["TotalPounds"]); size.sizeDescription = Convert.ToString(reader["SizeDescription"]); size.dimensions = Convert.ToString(reader["Dimensions"]); size.basePrice = Convert.ToDecimal(reader["BasePrice"]); } connection.Close(); return(size); }
//Add a new row to size options private void AddRow(int rowCount, CatalogPieceSize pSize) { //new row and formatting for table RowStyle style = new RowStyle(); style.SizeType = SizeType.AutoSize; style.Height = 30; tableAddSizes.RowStyles.Add(style); tableAddSizes.RowCount++; //Pounds NUD NumericUpDown tempnud = new NumericUpDown(); tempnud.Width = 75; tempnud.DecimalPlaces = 2; tempnud.Maximum = 100; tempnud.Focus(); tempnud.Value = pSize.totalPounds; nudPounds.Insert(rowCount - 1, tempnud); tableAddSizes.Controls.Add(nudPounds[rowCount - 1], 0, rowCount); //Dimensions Text TextBox temp = new TextBox(); temp.Width = 155; temp.MaxLength = 255; temp.Text = pSize.dimensions; txtDimensions.Insert(rowCount - 1, temp); tableAddSizes.Controls.Add(txtDimensions[rowCount - 1], 1, rowCount); //Descriptions Text temp = new TextBox(); temp.Width = 150; temp.MaxLength = 255; temp.Text = pSize.sizeDescription; txtDescriptions.Insert(rowCount - 1, temp); tableAddSizes.Controls.Add(txtDescriptions[rowCount - 1], 2, rowCount); //Prices NUD tempnud = new NumericUpDown(); tempnud.Width = 85; tempnud.DecimalPlaces = 2; tempnud.Maximum = 100000; tempnud.Value = pSize.basePrice; nudPrices.Insert(rowCount - 1, tempnud); tableAddSizes.Controls.Add(nudPrices[rowCount - 1], 3, rowCount); //Delete Button Label delete = new Label(); delete.ForeColor = Color.DarkRed; delete.Text = "X"; delete.Click += new EventHandler(this.DeleteRow_Click); if (rowCount == 1) { delete.Visible = false; } lblDeletes.Insert(rowCount - 1, delete); //add row to table tableAddSizes.Controls.Add(lblDeletes[rowCount - 1], 4, rowCount); }
public static List <CatalogPiece> GetPieces() { List <CatalogPiece> pieces = new List <CatalogPiece>(); string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\\PineSpringsPottery.accdb"; OleDbConnection connection = new OleDbConnection(connectionString); //Open connection if not already if (connection.State == ConnectionState.Closed) { connection.Open(); } string selectStatement = "SELECT PIECE_CATALOG.PieceNo, PIECE_CATALOG.PieceName, PIECE_CATALOG.Description, PIECE_CATALOG.Picture.FileName, PIECE_CATALOG.Picture.FileData " + "FROM PIECE_CATALOG ORDER BY PieceName;"; //Create Command for select statement OleDbCommand selectCmd = new OleDbCommand(selectStatement, connection); //execute reader and close reader OleDbDataReader reader = selectCmd.ExecuteReader(); while (reader.Read()) { CatalogPiece temp = new CatalogPiece(); temp.pieceNo = Convert.ToInt32(reader["PieceNo"]); temp.pieceName = Convert.ToString(reader["PieceName"]); temp.description = Convert.ToString(reader["Description"]); string filename = Convert.ToString(reader["PIECE_CATALOG.Picture.FileName"]); if (filename != "") { byte[] image = GetImageBytesFromOLEField((byte[])reader["PIECE_CATALOG.Picture.FileData"]); try { MemoryStream memStream = new MemoryStream(image); temp.picture = new Bitmap(memStream); } catch (Exception ex) { MessageBox.Show(ex.Message); } } else { temp.picture = new Bitmap((System.Drawing.Image)(Resources.noimage)); } temp.sizeOptions = new List <CatalogPieceSize>(); string subSelectStatement = "SELECT PIECE_SIZE.SizeNo, PIECE_SIZE.Dimensions, PIECE_SIZE.BasePrice, PIECE_SIZE.SizeDescription, PIECE_SIZE.TotalPounds " + "FROM PIECE_SIZE WHERE PIECE_SIZE.PieceNo = " + temp.pieceNo + " ORDER BY SizeNo;"; OleDbCommand subSelectCmd = new OleDbCommand(subSelectStatement, connection); //execute reader and close reader OleDbDataReader subReader = subSelectCmd.ExecuteReader(); while (subReader.Read()) { CatalogPieceSize subTemp = new CatalogPieceSize(); subTemp.sizeNo = Convert.ToInt32(subReader["SizeNo"]); subTemp.totalPounds = Convert.ToDecimal(subReader["TotalPounds"]); subTemp.sizeDescription = Convert.ToString(subReader["SizeDescription"]); subTemp.dimensions = Convert.ToString(subReader["Dimensions"]); subTemp.basePrice = Convert.ToDecimal(subReader["BasePrice"]); temp.sizeOptions.Add(subTemp); } pieces.Add(temp); } connection.Close(); return(pieces); }