CODEWITHSUNDEEP
vb.netms-access
user2178307
user2178307

Reputation:

Update and Insert Statement in one Control

My problem is after inserting a data in database the no. of copies in tblBooklist isn't subtrated by one. Here's my sample codes:

      Dim sqlinsert As String
      sqlinsert = "UPDATE tblBookList SET Copy = Copy - 1 WHERE 
      [Accession Number] = @Accession_Number"
      sqlinsert = "INSERT INTO tblBookIssue([Accession Number],[Book Title],
      [Student Name],[Years],[Section],[Date Issue],[Time],[Am or Pm],[Date to be Return], 
      [Status])  VALUES (@Accession_Number,@Book_Title,@Student_Name,@Years,
      @Section,@Date_Issue,@Time,@Am_or_Pm,@Date_to_be_Return,@Status)"

      Dim cmd As New OleDbCommand(sqlinsert, con)
      cmd.Parameters.Add(New OleDbParameter("@Accession_Number", txtBookAccessNumber.Text))
      cmd.Parameters.Add(New OleDbParameter("@Book_Title", txtBookBookTitle.Text))
      cmd.Parameters.Add(New OleDbParameter("@Student_Name", txtBookStudentName.Text))
      cmd.Parameters.Add(New OleDbParameter("@Years", txtBookYears.Text))
      cmd.Parameters.Add(New OleDbParameter("@Section", txtBookSection.Text))
      cmd.Parameters.Add(New OleDbParameter("@Date_Issue", Label88.Text))
      cmd.Parameters.Add(New OleDbParameter("@Time", lblTime.Text))
      cmd.Parameters.Add(New OleDbParameter("@Am_or_Pm", lblAm.Text))
      cmd.Parameters.Add(New OleDbParameter("@Date_to_be_Return", 
      txtBookDatetobeReturn.Text))
      cmd.Parameters.Add(New OleDbParameter("@Status", lblStatus.Text))
      con.Open()
      cmd.ExecuteNonQuery()

      MessageBox.Show("Book has been borrowed", "Issue", MessageBoxButtons.OK, 
      MessageBoxIcon.Information)

Thank you for your help in advance

Upvotes: 0

Views: 44

Answers (2)

Fabian Bigler
Fabian Bigler

Reputation: 10905

As already pointed out by OTTA, here is the corrected code:

  Dim sqlinsert As String
  sqlinsert = "UPDATE tblBookList SET Copy = Copy - 1 WHERE 
  [Accession Number] = @Accession_Number"
  Dim cmd As New OleDbCommand(sqlinsert, con)
  cmd.Parameters.Add(New OleDbParameter("@Accession_Number", txtBookAccessNumber.Text))
  cmd.ExecuteNonQuery()

  sqlinsert = "INSERT INTO tblBookIssue([Accession Number],[Book Title],
  [Student Name],[Years],[Section],[Date Issue],[Time],[Am or Pm],[Date to be Return], 
  [Status])  VALUES (@Accession_Number,@Book_Title,@Student_Name,@Years,
  @Section,@Date_Issue,@Time,@Am_or_Pm,@Date_to_be_Return,@Status)"
  cmd.CommandText = sqlinsert
  cmd.Parameters.Add(New OleDbParameter("@Book_Title", txtBookBookTitle.Text))
  cmd.Parameters.Add(New OleDbParameter("@Student_Name", txtBookStudentName.Text))
  cmd.Parameters.Add(New OleDbParameter("@Years", txtBookYears.Text))
  cmd.Parameters.Add(New OleDbParameter("@Section", txtBookSection.Text))
  cmd.Parameters.Add(New OleDbParameter("@Date_Issue", Label88.Text))
  cmd.Parameters.Add(New OleDbParameter("@Time", lblTime.Text))
  cmd.Parameters.Add(New OleDbParameter("@Am_or_Pm", lblAm.Text))
  cmd.Parameters.Add(New OleDbParameter("@Date_to_be_Return", 
  txtBookDatetobeReturn.Text))
  cmd.Parameters.Add(New OleDbParameter("@Status", lblStatus.Text))
  cmd.ExecuteNonQuery()

  MessageBox.Show("Book has been borrowed", "Issue", MessageBoxButtons.OK, 
  MessageBoxIcon.Information)

Upvotes: 1

OTTA
OTTA

Reputation: 1081

Your Update statement is never executed, first thing you do is set sqlinsert to your Update statement, next thing you do is overwrite it with your Insert statement which you then execute.

Upvotes: 0

Related Questions