CODEWITHSUNDEEP
sqldatabasevb.net
Silver
Silver

Reputation: 29

Using an update statement for a Access database (Vb 2008)

I'm trying to create an update statement for my program that will update a database using SQL based on the data the user inputs, unfortunately I have the problem that I can only update one at a time and sometimes no of them work. If any help could be given it will be much appreciated.

Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click

    Dim con As New OleDb.OleDbConnection

    Dim d1 As New OleDb.OleDbDataAdapter
    Dim d2 As New OleDb.OleDbDataAdapter
    Dim d3 As New OleDb.OleDbDataAdapter
    Dim d4 As New OleDb.OleDbDataAdapter
    Dim d5 As New OleDb.OleDbDataAdapter
    Dim d6 As New OleDb.OleDbDataAdapter
    Dim d7 As New OleDb.OleDbDataAdapter
    Dim d8 As New OleDb.OleDbDataAdapter
    Dim d9 As New OleDb.OleDbDataAdapter
    Dim d10 As New OleDb.OleDbDataAdapter

    Dim dt As New DataTable("Animals")

    'uses the 2010 compatible connection string
    con.ConnectionString = "PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source = h:\Animals.accdb"
    con.Open()

    MsgBox("UPDATE Animals SET LatinName = '" & latintxt.Text & "'" & " WHERE AnimalName = " & "'" & Form1.txtname.Text & "'")
    d1 = New OleDb.OleDbDataAdapter("UPDATE Animals SET LatinName = '" & latintxt.Text & "'" & " WHERE AnimalName = " & "'" & Form1.txtname.Text & "'", con)
    d2 = New OleDb.OleDbDataAdapter("UPDATE Animals SET LocationFound = '" & locationtxt.Text & "'" & " WHERE AnimalName = " & "'" & Form1.txtname.Text & "'", con)
    d3 = New OleDb.OleDbDataAdapter("UPDATE Animals SET AverageHeight = '" & heighttxt.Text & "'" & " WHERE AnimalName = " & "'" & Form1.txtname.Text & "'", con)
    d4 = New OleDb.OleDbDataAdapter("UPDATE Animals SET AverageWeight = '" & weighttxt.Text & "'" & " WHERE AnimalName = " & "'" & Form1.txtname.Text & "'", con)
    d5 = New OleDb.OleDbDataAdapter("UPDATE Animals SET DietaryNeeds = '" & diettxt.Text & "'" & " WHERE AnimalName = " & "'" & Form1.txtname.Text & "'", con)
    d6 = New OleDb.OleDbDataAdapter("UPDATE Animals SET ConservationStatus = '" & statustxt.Text & "'" & " WHERE AnimalName = " & "'" & Form1.txtname.Text & "'", con)
    d7 = New OleDb.OleDbDataAdapter("UPDATE Animals SET AverageLifeSpan = '" & lifetxt.Text & "'" & " WHERE AnimalName = " & "'" & Form1.txtname.Text & "'", con)
    d8 = New OleDb.OleDbDataAdapter("UPDATE Animals SET BreedingSeason = '" & breedtxt.Text & "'" & " WHERE AnimalName = " & "'" & Form1.txtname.Text & "'", con)
    d9 = New OleDb.OleDbDataAdapter("UPDATE Animals SET AverageLength = '" & lengthtxt.Text & "'" & " WHERE AnimalName = " & "'" & Form1.txtname.Text & "'", con)
    d10 = New OleDb.OleDbDataAdapter("UPDATE Animals SET AnimalName = '" & nametxt.Text & "'" & " WHERE AnimalName = " & "'" & Form1.txtname.Text & "'", con)

    d1.Fill(dt)
    d2.Fill(dt)
    d3.Fill(dt)
    d4.Fill(dt)
    d5.Fill(dt)
    d6.Fill(dt)
    d7.Fill(dt)
    d8.Fill(dt)
    d9.Fill(dt)
    d10.Fill(dt)

    con.Close()

End Sub

Upvotes: 0

Views: 722

Answers (2)

Mike_OBrien
Mike_OBrien

Reputation: 1423

Your function is very inefficient. You should be using OleDB.OleDBCommand's instead of the dataadapters. Dataadapters are mainly supposed to be used for getting data from your database not updating your database. You can use them to update data but not the way you are doing it.

Try changing your function to look like this:

Using cn As New OleDbConnection(YOURCONNECTIONSTRING)
    Dim cSQL As String = "THIS WILL BE YOUR SQL"
    Dim cmd As New OleDbCommand(cSQL, cn)
    Try
        If cn.State <> ConnectionState.Open Then cn.Open()
        cmd.ExecuteNonQuery()

        'Now reset cSQL to your second SQL string and recreate your OleDbCommand with the new string.'
        cSQL = "NEW SQL STRING"
        cmd = New OleDbCommand(cSQL, cn)
        cmd.ExecuteNonQuery()

        'Now repeat your process as many times as you like.'
    Catch ex As Exception
        'Handle any errors here.'
    End Try
End Using

Having said that, you should be using Command Parameters for all your input like others have mentioned. This is a little bit more advanced and you should google around on your own time to teach yourself how to do it. There are a very large number of tutorials out there that will walk you through the whole process. Once you learn how to use the parameters you will be well on your way to protecting your future projects from hackers and malicious users.

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20804

The actual sql can resemble this:

update yourtable
set field1 = something
, field2 = something else
etc

All you have to do with your .net code is to create a string resembling that. Also, use query parameters.

Upvotes: 0

Related Questions