title: After inserting data into one table, I also want that data to auto appear in another table
body: <p>I have two buttons, one when clicked will go to medication form, another when clicked will go to prescription form. What I want to achieve is, if I entered one record in the medication form, the prescription form will auto generate a prescriptionID and pop up the medicationName based on what medication name that I typed on the medication form.</p>
  
  <p>For the field medicationName in prescription form, it was orginally a medicationID, but I used left outer join to display as medicationName which exist in medication table. </p>
  
  <p>For the attached first image, the reason the prescription form have a panadol record is because I type a number 3 medicationID in the prescription database. I don;t want to manually type it in the database, I want it to auto pop up whenever i insert a record in medication form. See, when I insert record with a strong panadol, it didn;t appear in the prescription form!!!!!</p>
  
  <p>PRESCRIPTION AND MEDICATION FORM
  <img src="https://i.stack.imgur.com/35e8Q.jpg" alt="output"></p>
  
  <p>PRESCRIPTION TABLE
  <img src="https://i.stack.imgur.com/lchxJ.jpg" alt="prescriptiontable"></p>
  
  <p>MEDICATION TABLE
  <img src="https://i.stack.imgur.com/GIUVw.jpg" alt="medicationtable"></p>
  
  <p>Error after keying value and clicking submit button in medication form.
  Conversion failed when converting the nvarchar value 'panadol3' to data type int.
  <img src="https://i.stack.imgur.com/vYVwQ.jpg" alt="Error"></p>
  
  <p>//PRESCRIPTION FORM CODES</p>
  
  <pre><code>using System;
  using System.Collections.Generic;
  using System.ComponentModel;
  using System.Data;
  using System.Drawing;
  using System.Linq;
  using System.Text;
  using System.Windows.Forms;
  using System.Data.SqlClient;
  using System.Configuration;
  
  namespace GRP_02_03_SACP
  {
      public partial class prescription : Form
      {
  
          // Data Table to store employee data
          DataTable Prescription = new DataTable();
  
          // Keeps track of which row in Gridview
          // is selected
          DataGridViewRow currentRow = null;
  
          SqlDataAdapter PrescriptionAdapter;
  
          public prescription()
          {
              InitializeComponent();
          }
  
          private void prescription_Load(object sender, EventArgs e)
          {
              LoadPrescriptionRecords();
          }
  
          private void LoadPrescriptionRecords()
          {
  
              //retrieve connection information info from App.config
              string strConnectionString = ConfigurationManager.ConnectionStrings["sacpConnection"].ConnectionString;
              //STEP 1: Create connection
              SqlConnection myConnect = new SqlConnection(strConnectionString);
              //STEP 2: Create command
              string strCommandText = "SELECT prescriptionID, med.medicationName FROM PRESCRIPTION AS pres";
              strCommandText += " LEFT OUTER JOIN medication as med on pres.medicationid = med.medicationid";
  
              PrescriptionAdapter = new SqlDataAdapter(strCommandText, myConnect);
  
              //command builder generates Select, update, delete and insert SQL
              // statements for MedicalCentreAdapter
              SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(PrescriptionAdapter);
              // Empty Employee Table first
              Prescription.Clear();
              // Fill Employee Table with data retrieved by data adapter
              // using SELECT statement
              PrescriptionAdapter.Fill(Prescription);
  
              // if there are records, bind to Grid view &amp; display
              if (Prescription.Rows.Count &gt; 0)
                  grdPrescription.DataSource = Prescription;
          }
  
          private void btnPrint_Click(object sender, EventArgs e)
          {
              if (printDialog1.ShowDialog() == DialogResult.OK) // this displays the dialog box and performs actions dependant on which option chosen.
              {
                  printDocument1.Print();
              }
          }
  
          private void printDocument1_PrintPage(object sender, System.Drawing.Printing.PrintPageEventArgs e)
          {
              int columnPosition = 0;
              int rowPosition = 25;
  
              // run function to draw headers
              DrawHeader(new Font(this.Font, FontStyle.Bold), e.Graphics, ref columnPosition, ref rowPosition); // runs the DrawHeader function
  
              rowPosition += 35; // sets the distance below the header text and the next black line (ruler)
  
              // run function to draw each row
              DrawGridBody(e.Graphics, ref columnPosition, ref rowPosition);
          }
  
          // DrawHeader will draw the column title, move over, draw the next column title, move over, and continue.
          private int DrawHeader(Font boldFont, Graphics g, ref int columnPosition, ref int rowPosition)
          {
              foreach (DataGridViewColumn dc in grdPrescription.Columns)
              {
  
                  //MessageBox.Show("dc = " + dc);
  
                  g.DrawString(dc.HeaderText, boldFont, Brushes.Black, (float)columnPosition, (float)rowPosition);
                  columnPosition += dc.Width + 5; // adds to colPos. value the width value of the column + 5. 
              }
  
              return columnPosition;
          }
  
          /* DrawGridBody will loop though each row and draw it on the screen. It starts by drawing a solid line on the screen, 
           * then it moves down a row and draws the data from the first grid column, then it moves over, then draws the data from the next column,
           * moves over, draws the data from the next column, and continus this pattern. When the entire row is drawn it starts over and draws
           * a solid line then the row data, then the next solid line and then row data, etc.
          */
          private void DrawGridBody(Graphics g, ref int columnPosition, ref int rowPosition)
          {
              // loop through each row and draw the data to the graphics surface.
              foreach (DataRow dr in ((DataTable)grdPrescription.DataSource).Rows)
              {
                  columnPosition = 0;
  
                  // draw a line to separate the rows 
                  g.DrawLine(Pens.Black, new Point(0, rowPosition), new Point(this.Width, rowPosition));
  
                  // loop through each column in the row, and draw the individual data item
                  foreach (DataGridViewColumn dc in grdPrescription.Columns)
                  {
                      // draw string in the column
                      string text = dr[dc.DataPropertyName].ToString();
                      g.DrawString(text, this.Font, Brushes.Black, (float)columnPosition, (float)rowPosition + 10f); // the last number (10f) sets the space between the black line (ruler) and the text below it.
  
                      // go to the next column position
                      columnPosition += dc.Width + 5;
                  }
  
                  // go to the next row position
                  rowPosition = rowPosition + 60; // this sets the space between the row text and the black line below it (ruler).
              }
          }
  
          private void btnPrintPreview_Click(object sender, EventArgs e)
          {
              try
              {
                  // PrintPreviewDialog printPreviewDialog1 = new PrintPreviewDialog(); // instantiate new print preview dialog
                  printPreviewDialog1.Document = this.printDocument1;
                  if (printPreviewDialog1.ShowDialog() == DialogResult.OK) // Show the print preview dialog, uses printPage event to draw preview screen
                  {
                      printDocument1.Print();
                  }
              }
              catch (Exception exp)
              {
                  System.Console.WriteLine(exp.Message.ToString());
              }
          }
      }
  }
  </code></pre>
  
  <p>MEDICATION FORM codes. This is my code in the insertprescription trigger. </p>
  
  <p>ALTER TRIGGER insertPrescriptions ON dbo.MEDICATION AFTER INSERT AS INSERT INTO prescription(prescriptionID, medicationID) select MedicationID, MedicationName from inserted GO</p>
  
  <pre><code>    using System;
  using System.Collections.Generic;
  using System.ComponentModel;
  using System.Data;
  using System.Drawing;
  using System.Linq;
  using System.Text;
  using System.Windows.Forms;
  using System.Data.SqlClient;
  using System.Configuration;
  
  namespace GRP_02_03_SACP
  {
      public partial class medication : Form
      {
          // Data Table to store employee data
          DataTable Medication = new DataTable();
  
          // Keeps track of which row in Gridview
          // is selected
          DataGridViewRow currentRow = null;
  
          SqlDataAdapter MedicationAdapter;
  
          public medication()
          {
              InitializeComponent();
          }
  
          private void btnSubmit_Click(object sender, EventArgs e)
          {
              if (btnSubmit.Text == "Clear")
              {
                  btnSubmit.Text = "Submit";
                  ClearTextBoxes();
                  txtmedicationType.Focus();
              }
              else
              {
                  btnSubmit.Text = "Clear";
                  int result = AddMedicationRecord();
                  if (result &gt; 0)
                      MessageBox.Show("Insert Successful");
                  else
                      MessageBox.Show("Insert Fail");
  
              }
          }
          private void ClearTextBoxes()
          {
              txtmedicationType.Clear();
              txtmedicationName.Clear();
              txtexpiryDate.Clear();
              txtmedicationPrice.Clear();
          }
  
          private int AddMedicationRecord()
          {
              int result = 0;
              // TO DO: Codes to insert customer record
              //retrieve connection information info from App.config
              string strConnectionString = ConfigurationManager.ConnectionStrings["sacpConnection"].ConnectionString;
              //STEP 1: Create connection
              SqlConnection myConnect = new SqlConnection(strConnectionString);
                  //STEP 2: Create command
   String strCommandText = "INSERT MEDICATION(medicationType, medicationName, expiryDate, medicationPrice) "
                  + " VALUES (@NewmedicationType, @NewmedicationName,@NewexpiryDate, @NewmedicationPrice)";
  
              SqlCommand updateCmd = new SqlCommand(strCommandText, myConnect);
  
  
              updateCmd.Parameters.AddWithValue("@NewmedicationName", txtmedicationName.Text);
              //updateCmd.Parameters["@clientid"].Direction = ParameterDirection.Output; 
              // STEP 3 open connection and retrieve data by calling ExecuteReader
              myConnect.Open();
              // STEP 4: execute command
              // indicates number of record updated.
              result = updateCmd.ExecuteNonQuery();
  
              // STEP 5: Close
              myConnect.Close();
              return result;
  
          }
  
          private void medication_Load(object sender, EventArgs e)
          {
              LoadMedicationRecords();
          }
  
          private void LoadMedicationRecords()
          {
  
              //retrieve connection information info from App.config
              string strConnectionString = ConfigurationManager.ConnectionStrings["sacpConnection"].ConnectionString;
              //STEP 1: Create connection
              SqlConnection myConnect = new SqlConnection(strConnectionString);
              //STEP 2: Create command
              string strCommandText = "SELECT medicationID, medicationType, medicationName, expiryDate, medicationPrice FROM MEDICATION";
  
              MedicationAdapter = new SqlDataAdapter(strCommandText, myConnect);
  
              //command builder generates Select, update, delete and insert SQL
              // statements for MedicalCentreAdapter
              SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(MedicationAdapter);
              // Empty Employee Table first
              Medication.Clear();
              // Fill Employee Table with data retrieved by data adapter
              // using SELECT statement
              MedicationAdapter.Fill(Medication);
  
              // if there are records, bind to Grid view &amp; display
              if (Medication.Rows.Count &gt; 0)
                  grdMedication.DataSource = Medication;
          }
  
          private void btnUpdate_Click(object sender, EventArgs e)
          {
              int modifiedRows = 0;
              // Get changes
              DataTable UpdatedTable = Medication.GetChanges();
              if (UpdatedTable != null)
              {
                  // there are changes
                  // Write modified data to database 
                  modifiedRows = MedicationAdapter.Update(UpdatedTable);
                  // accept changes
                  Medication.AcceptChanges();
              }
              else
                  MessageBox.Show("there are no changes to update");
  
              if (modifiedRows &gt; 0)
              {
                  MessageBox.Show("There are " + modifiedRows + " records updated");
                  LoadMedicationRecords();
              }
          }
  
          private void grdMedication_CellValueChanged(object sender, DataGridViewCellEventArgs e)
          {
              MedicationAdapter.Update(Medication);
          }
  
      }
  }
  </code></pre>