30 Extra exercises for Murach\'s Visnal Basic 2015 Extra 16-1 Write the code for
ID: 3741014 • Letter: 3
Question
30 Extra exercises for Murach's Visnal Basic 2015 Extra 16-1 Write the code for a Product Maintenance application In this exercise, you'll use ADO.NET to write the data access code for an application that lets the user add, modify, and delete products. Desiption Price Murach's ASP NET 46 Wieb Pogranming with Cat 201 Modity Product Code AC Descrption Price57.5000 Open the project and add a class that gets a connection to the database 1. Open the ProductMaintenance project in the Extra Exercises Chapter 16 ProductMaintenance directory. This project contains the two forms for the application, the Product and Validator classes, and the MMABooks.mdf database file. 2. Add a public class named MMABooksDB to the project. Then, add a shared method named GetConnection that creates an SqlConnection object for the MMABooks database and then returns that connection. For this to work, you'll need to add an Imports statement for the System.Data.SqlClient namespace at the beginning of the class. Write the code to retrieve a product 3. Add another public class named ProductDB to the project, and add Imports statements for the System.Data and System.Data.SqlClient namespaces to this class. 4. Add a shared method named GetProduct to the ProductDB class. This method should receive the product code of the product to be retrieved, and it should return a Product object for that product. If a product with the product code isn't found, this method should return Nothing. Place the code that works with the database in the Try block of a Try...Catch statement, include a Catch block that catches and then throws any SqlException that occurs, and include a Finally block that closes the connection.Explanation / Answer
2. The below code lets you connect to the database. Change the details of username and password accordingly:
public class MMABooksDB
{
SqlConnection conn;
SqlCommand comm;
SqlDataReader dreader;
string connstring = "server=localhost;database=MMABOOKS;user=user_name;password=pswd";
conn = new SqlConnection(connstring);
conn.Open();
try
{
comm.ExecuteNonQuery();
MessageBox.Show("Connected");
}
catch (Exception)
{
MessageBox.Show("Not Connected");
}
finally
{
conn.Close();
}
}
To inset, update and search data, we have following 3 functions:
void GetProduct (Object sender, EventArgs e)
void btnInsert_Click(object sender, EventArgs e)
void btnUpdate_Click(object sender, EventArgs e)
These methods will be called by Calling the functions inside the class:
ButtonSearch.Click += new EventHandler(this.GetProduct );
ButtonInsert.Click += new EventHandler(this.btnInsert_Click);
ButtonUpdate.Click += new EventHandler(this.btnUpdate_Click);
The code for the above said methods are:
Q.no 3-6
void GetProduct (Object sender, EventArgs e)
{
String searchText;
SearchText=prodid;
conn = new SqlConnection(connectstring); // already declared in the main class
conn.Open();
comm = new SqlCommand("select * from product_detail where product_code= SearchText, conn); // product_detail is the name of the table
try
{
dreader = comm.ExecuteReader();
if (dreader.Read())
{
txtname.Text = dreader[1].ToString();
txtage.Text = dreader[2].ToString();
txtcourse.Text = dreader[3].ToString();
}
else
{
MessageBox.Show(" No Record Available");
}
dreader.Close();
}
catch (Exception)
{
MessageBox.Show(" No Record Available");
}
finally
{
conn.Close();
}
}
Q.no 10-12 Inserting to table:
void btnInsert_Click(object sender, EventArgs e)
{
conn = new SqlConnection(connstring);
conn.Open();
comm = new SqlCommand("insert into product_detail values(" + pidTxt.Text + ",'" + pnameTxt.Text + "','" + prodPriceTxt.Text + "'," + QuantityTxt.Text + ")", conn);
// I assume that there are 4 columns in the table. pidTxt, pnameTxt, prodPriceTxt, QuantityTxt are the names of the text field where the user enters the details to be entered
try
{
comm.ExecuteNonQuery();
MessageBox.Show("Data Entered Successfully");
}
catch (Exception)
{
MessageBox.Show("Unable to Enter Data");
}
finally
{
conn.Close();
}
}
Q 7-9: Updating Contents in table:
void btnUpdate_Click(object sender, EventArgs e)
{
conn = new SqlConnection(connstring);
conn.Open();
comm = new SqlCommand("update product_detail set prod_name= '"+prodname.Text+" where prodid="+pid.Text+" '", conn);
//prodname is the name of textfield where the user inputs the new data to be updated pid is id of the product which will be searched in the database
try
{
comm.ExecuteNonQuery();
MessageBox.Show("Data Updated");
}
catch (Exception)
{
MessageBox.Show(" Not Updated");
}
finally
{
conn.Close();
}
}
Q. No 13
private void btndelete_Click(object sender, EventArgs e)
{
conn = new SqlConnection(connstring);
conn.Open();
comm = new SqlCommand("delete from product_detail where prodid= " + txtProdId.Text + " ", conn);
try
{
comm.ExecuteNonQuery();
MessageBox.Show("Data Deleted"); }
catch (Exception ex)
{
MessageBox.Show(" Not Deleted" + ex.Message );
}
finally
{
conn.Close();
}
}
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.