在一个控件中更新和插入语句(Update and Insert Statement in one Control)

在一个控件中更新和插入语句(Update and Insert Statement in one Control)

我的问题是在数据库中插入数据后没有。 tblBooklist中的副本不会被一个减去。 这是我的示例代码：

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)

提前谢谢你的帮助

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

最满意答案

您的Update语句永远不会被执行，您首先要做的是将sqlinsert设置为您的Update语句，接下来您要使用随后执行的Insert语句覆盖它。

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.