[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 312
  • Last Modified:

Why isn't this Query actually updating my Access Table?

I have this code.  What I want to do is have the user press on a Save Data button and then automatically write all the values that are in the table onscreen to overwrite the ones in the database.  Here's my code for the update...

Dim RecordCount, RecordIndex As Integer
        Dim sSQL As String
        Dim con As OleDbConnection
        Dim da As OleDbDataAdapter

        Dim connectionString As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename
        con = New OleDbConnection(connectionString)

        RecordCount = CrimeData.Tables(0).Rows.Count

        RecordIndex = 0

        While (RecordCount > RecordIndex)
            sSQL = "UPDATE Virginia SET Count = '" + CrimeData.Tables(0).Rows(RecordIndex).Item(1).ToString() + "'                  
                         WHERE Jurisdiction = '" +  
                         dbClass.GetJurisdictionList().Tables(0).Rows(JurisdictionComboBox.SelectedIndex).Item(0).ToString() + "'
                         AND Type = '" + dbClass.GetTypeList(dbClass.GetJurisdictionList().Tables(0).Rows(JurisdictionComboBox.SelectedIndex).Item(0).ToString()).Tables(0).Rows(CrimeTypeComboBox.SelectedIndex).Item(0).ToString() + "' AND Month = #" + CrimeData.Tables(0).Rows(RecordIndex).Item(0) + "#"


            MessageBox.Show(sSQL)

            da = New OleDbDataAdapter(sSQL, con)

            RecordIndex += 1
        End While

        con.Close()
 
Those long attempts at getting a variable out of a ComboBox work, don't worry about those.  When the messagebox pops up it displays a query that looks something like this...

        Update Virginia SET Count = '10' WHERE Jurisdiction = '00101' AND Type = 'MUR' AND Month = #02/01/1960#

....which when I just throw it into an open Query window using SQL in Access it will update the table just fine.  Is there some reason this function is not saving the changes I make or is there something else I'm missing?
0
SysCapstone
Asked:
SysCapstone
  • 3
  • 2
1 Solution
 
Arthur_WoodCommented:
If you want to UPDATE the database, do not use a DataAdapter, rather, use the ExecuteNonQuery method of a command object, like this:

Dim sSQL As String
        Dim con As OleDbConnection
        Dim cmd As OleDbCommand

        Dim connectionString As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename
        con = New OleDbConnection(connectionString)

        RecordCount = CrimeData.Tables(0).Rows.Count

        RecordIndex = 0

        While (RecordCount > RecordIndex)
            sSQL = "UPDATE Virginia SET Count = '" + CrimeData.Tables(0).Rows(RecordIndex).Item(1).ToString() + "'                  
                         WHERE Jurisdiction = '" +  
                         dbClass.GetJurisdictionList().Tables(0).Rows(JurisdictionComboBox.SelectedIndex).Item(0).ToString() + "'
                         AND Type = '" + dbClass.GetTypeList(dbClass.GetJurisdictionList().Tables(0).Rows(JurisdictionComboBox.SelectedIndex).Item(0).ToString()).Tables(0).Rows(CrimeTypeComboBox.SelectedIndex).Item(0).ToString() + "' AND Month = #" + CrimeData.Tables(0).Rows(RecordIndex).Item(0) + "#"


            MessageBox.Show(sSQL)

            cmd = New OleDbCommand(sSQL, con)
            cmd.ExecuteNonQuery()

            RecordIndex += 1
        End While

        con.Close()

AW
0
 
SysCapstoneAuthor Commented:
Thanks I'll give that a shot tonight.
0
 
SysCapstoneAuthor Commented:
Wouldn't happen to know if this query is wrong somehow?  The program shoots back an error saying there's a syntax error in my UPDATE statement when the statement looks like this...

UPDATE Virginia SET Count=9 WHERE Month=#1/1/1960# AND Type='MUR' AND Jurisdiction='00101'

Those are the right column and table name(s).  See anything else that I'm missing?
0
 
SysCapstoneAuthor Commented:
Nevermind, that last question was solved by finding out "Count" is a reserved word in MS Access.  Thanks much for the help Arthur_Wood!
0
 
Arthur_WoodCommented:
glad to be of assistance.  You need to be very careful about using RESERVED words, in Access, or any other Database - they all have them.

AW
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now