Razvan Balaci Razvan Balaci - 6 months ago 26
Vb.net Question

When I update database, information doesn't get inserted, when I insert ..... vb.net

I GOT a big problem.

When I update my database, the information doesn't get inserted into the database. But when I insert the information into the database, it doesn't update.

This is the code that I use when I update the database trying to insert but it doesn't work, only updates:

provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\311026\Documents\PAP3\Biblio.accdb"
connString = provider
myConnection.ConnectionString = connString
myConnection.Open()
Dim str As String

str = "Insert into Livrosrequisitar([NomeLivro], [AutorLivro], [EditoraLivro], [AnoLivro], [Nome_Aluno], [Ano_Aluno], [Turma_Aluno], [Contacto_Aluno], [Email_Aluno], [DataRequicicao], [RetornoRequicicao]) Values (?,?,?,?,?,?,?,?,?,?,?)"
Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)

cmd.Parameters.Add(New OleDbParameter("NomeLivro", CType(TextBox11.Text, String)))
cmd.Parameters.Add(New OleDbParameter("AutorLivro", CType(TextBox4.Text, String)))
cmd.Parameters.Add(New OleDbParameter("EditoraLivro", CType(TextBox5.Text, String)))
cmd.Parameters.Add(New OleDbParameter("AnoLivro", CType(TextBox6.Text, String)))
cmd.Parameters.Add(New OleDbParameter("Nome_Aluno", CType(TextBox10.Text, String)))
cmd.Parameters.Add(New OleDbParameter("Ano_Aluno", CType(TextBox9.Text, String)))
cmd.Parameters.Add(New OleDbParameter("Turma_Aluno", CType(TextBox8.Text, String)))
cmd.Parameters.Add(New OleDbParameter("Contacto_Aluno", CType(TextBox13.Text, String)))
cmd.Parameters.Add(New OleDbParameter("Email_Aluno", CType(TextBox12.Text, String)))
cmd.Parameters.Add(New OleDbParameter("DataRequicicao", CType(DateTimePicker1.Text, String)))
cmd.Parameters.Add(New OleDbParameter("RetornoRequicicao", CType(DateTimePicker2.Text, String)))

cmd = New OleDb.OleDbCommand("UPDATE Livrosescola SET Quantidade = Quantidade - 1 Where NomeLivro like'%" & ListBox1.SelectedValue & "%'", myConnection)
ListBox1.Refresh()

Try
cmd.ExecuteNonQuery()
cmd.Dispose()
myConnection.Close()
TextBox11.Clear()
TextBox4.Clear()
TextBox5.Clear()
TextBox6.Clear()
TextBox10.Clear()
TextBox9.Clear()
TextBox8.Clear()
TextBox13.Clear()
TextBox12.Clear()
TextBox9.Clear()

MessageBox.Show("Registo inserido!", "Registo inserido", MessageBoxButtons.OK, MessageBoxIcon.Information)

Catch ex As Exception
MessageBox.Show("Erro!", "Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

listbox()


That is the code that I use when I insert the information, trying to update, but only inserts the code.

provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\311026\Documents\PAP3\Biblio.accdb"
connString = provider
myConnection.ConnectionString = connString
myConnection.Open()
Dim str As String

str = "Insert into Livrosrequisitar([NomeLivro], [AutorLivro], [EditoraLivro], [AnoLivro], [Nome_Aluno], [Ano_Aluno], [Turma_Aluno], [Contacto_Aluno], [Email_Aluno], [DataRequicicao], [RetornoRequicicao]) Values (?,?,?,?,?,?,?,?,?,?,?)"
Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)


cmd.Parameters.Add(New OleDbParameter("NomeLivro", CType(TextBox11.Text, String)))
cmd.Parameters.Add(New OleDbParameter("AutorLivro", CType(TextBox4.Text, String)))
cmd.Parameters.Add(New OleDbParameter("EditoraLivro", CType(TextBox5.Text, String)))
cmd.Parameters.Add(New OleDbParameter("AnoLivro", CType(TextBox6.Text, String)))
cmd.Parameters.Add(New OleDbParameter("Nome_Aluno", CType(TextBox10.Text, String)))
cmd.Parameters.Add(New OleDbParameter("Ano_Aluno", CType(TextBox9.Text, String)))
cmd.Parameters.Add(New OleDbParameter("Turma_Aluno", CType(TextBox8.Text, String)))
cmd.Parameters.Add(New OleDbParameter("Contacto_Aluno", CType(TextBox13.Text, String)))
cmd.Parameters.Add(New OleDbParameter("Email_Aluno", CType(TextBox12.Text, String)))
cmd.Parameters.Add(New OleDbParameter("DataRequicicao", CType(DateTimePicker1.Text, String)))
cmd.Parameters.Add(New OleDbParameter("RetornoRequicicao", CType(DateTimePicker2.Text, String)))



Try
cmd.ExecuteNonQuery()
cmd.Dispose()
myConnection.Close()
TextBox11.Clear()
TextBox4.Clear()
TextBox5.Clear()
TextBox6.Clear()
TextBox10.Clear()
TextBox9.Clear()
TextBox8.Clear()
TextBox13.Clear()
TextBox12.Clear()
TextBox9.Clear()

MessageBox.Show("Registo inserido!", "Registo inserido", MessageBoxButtons.OK, MessageBoxIcon.Information)

Catch ex As Exception
MessageBox.Show("Erro!", "Erro", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

cmd = New OleDb.OleDbCommand("UPDATE Livrosescola SET Quantidade = Quantidade - 1 Where NomeLivro like'%" & ListBox1.SelectedValue & "%'", myConnection)
ListBox1.Refresh()

listbox()


I want to do the same thing at the same time, but it doesn't work...

If you could help me...

Thank you!

Answer

A command need to be executed to do anything.

In both examples of your code you call ExecuteNonQuery only for one of the command text and not for the other.

In the first example just add this line:

......
Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)

Try
    ' Add this (and move the start of the Try catch before this line'
     cmd.ExecuteNonQuery()

    cmd.Parameters.Add(New OleDbParameter("NomeLivro", CType(TextBox11.Text, String)))
    ....
Catch ex as Exception
    MessageBox.Show("Error:" & ex.Message)
End Try

In the second example you have the same problem, but the ExecuteNonQuery is missing after the last OleDbCommand creation

Comments