Save Movements To Access 2007 Database

[shawn130c]

Hello,

I need help saving data into an access 2007 database from a program created in vb 2008.

I am making a basic "game" that allows you to move a "robot" up,down,left, right and will save the movements/positions into an access 2007 database. I already have the basic movement created with buttons that will change the robot location (the movements are done though a separate class).

The main issue I have is creating the connection and saving the movements.

the access database is located here, no password and I have already added it as a datasource in vb 2008:

c:\hw3robot.accdb

and has Fields: direction (char(1), x (decimal), y (decimal), TimeStamp (datetime)

I have tried some examples for connectionstring.com and other places and I get a variety of different errors.

If anyone has a good place to start, i would appreciate it.

The code is pretty long already but here is a snippet for the up button (btnup):

If I can get it to save data into the above fields, I should just be able to edit it for the rest of the buttons.

Form 1 code:

Imports System.Data
Imports System.Data.OleDb

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        'centers robots
        Dim centerx As Integer
        centerx = (Panel1.Width - picRobotRed.Width) / 2
        Dim centery As Integer
        centery = (Panel1.Height - picRobotRed.Height) / 2

        picRobotRed.Location = New Point(centerx, centery)
        picRobotBlue.Location = New Point(centerx, centery + 20)

        'Helps with focus/arrow keys
        radGo1.Checked = True
        radRobotRed.Checked = True
        btnDown.Focus()
    End Sub

    Dim Robot As New Robot
    Dim currentx As Integer
    Dim currenty As Integer
    Dim amountmove As Integer

    Private Sub btnup_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnup.Click

        If radRobotRed.Checked = True Then
            picRobotRed.Text = "5"
            If radGo1.Checked = True Then
                picRobotRed.Location = Robot.redup
            ElseIf radGo10.Checked = True Then
                picRobotRed.Location = Robot.redup10
            End If
        ElseIf radRobotBlue.Checked = True Then
            picRobotBlue.Text = "5"
            If radGo1.Checked = True Then
                picRobotBlue.Location = Robot.blueup
            ElseIf radGo10.Checked = True Then
                picRobotBlue.Location = Robot.blueup10
            End If
        End If

        currentx = picRobotRed.Location.X
        currenty = picRobotRed.Location.Y

        lblRedLocation.Text = currentx & "," & currenty

    End Sub

Robot Class (for movement)

Public Function redup() As Point

        Dim loc As Point

        If Form1.picRobotRed.Location.Y - 1 < 0 Then
            RaiseEvent RangeLimit()
            loc = New Point(Form1.picRobotRed.Location.X, Form1.picRobotRed.Location.Y)
        Else
            loc = New Point(Form1.picRobotRed.Location.X, Form1.picRobotRed.Location.Y - 1)
            Form1.picRobotRed.Location = loc
        End If

        Return loc

    End Function
#End Region

Thanks,
Shawn

[shawn130c]

I have been trying a few different examples and I seem to be getting stuck on the connection string part.

Editing an example I have that was orgianlly used for a sql database I insert this:

Dim conn As New Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\hw3robot.accdb;Persist Security Info=False;

into form1_load.

But no matter what I try I keep getting end of statement expected for =Microsoft.ACE.OLEDB.12.0;Data Source=C and syntax error for :\

Anyone have a better example for a connection string or ideas?

Shawn

[shawn130c]

I found a tutorial that seemed to help greatly. I am now able to save data into my access database. The only issue is that I can not figure out how to clear the database on each start/run of the program. I basically want a fresh database each time.

I am aware of this command

ds.clear() but depending on where I place it, it will either not do anything or cause an error:
System.IndexOutOfRangeException was unhandled
Message="There is no row at position 0."

I also was thinking of trying this command

ds.Tables(0).Rows.RemoveAt(put all the row numbers except for zero), but not sure exactly how to do it.

Here is a snippet of the code:

Imports System.Data
Imports System.Data.OleDb

Public Class Form1

    Dim ds As New DataSet()
    Dim intCurrentIndex As Integer = 0
    Dim da As New OleDbDataAdapter()
    Dim conn As New OleDbConnection()

    Dim robotcolor As String
    Dim currentdirection As String

    'Orginal
    Dim Robot As New Robot
    Dim currentx As Integer
    Dim currenty As Integer
    Dim amountmove As Integer


    Private Sub FillFields()

        currentdirection = ds.Tables(0).Rows(intCurrentIndex).Item("Direction").ToString()
        robotcolor = ds.Tables(0).Rows(intCurrentIndex).Item("Robot").ToString()
        currentx = ds.Tables(0).Rows(intCurrentIndex).Item("X").ToString()
        currenty = ds.Tables(0).Rows(intCurrentIndex).Item("Y").ToString()

    End Sub


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\hw3robot.accdb"

        da.SelectCommand = New OleDbCommand("SELECT ID, Direction, Robot, X, Y FROM RobotMovement")
        da.SelectCommand.Connection = conn


        da.UpdateCommand = New OleDbCommand("UPDATE RobotMovement SET Direction = @Direction, Robot= @Robot, X = @X, Y = @Y WHERE ID = @ID")
        da.UpdateCommand.Connection = conn
        da.UpdateCommand.Parameters.Add("@Direction", OleDbType.VarChar, 40, "Direction")
        da.UpdateCommand.Parameters.Add("@Robot", OleDbType.VarChar, 40, "Robot")
        da.UpdateCommand.Parameters.Add("@X", OleDbType.VarChar, 40, "X")
        da.UpdateCommand.Parameters.Add("@Y", OleDbType.VarChar, 40, "Y")
        da.UpdateCommand.Parameters.Add("@ID", OleDbType.Integer, 5, "ID") '.SourceVersion = DataRowVersion.Original


        da.InsertCommand = New OleDbCommand("INSERT INTO RobotMovement(Direction, Robot, X, Y) VALUES(@Direction,@Robot,@X,@Y)")
        da.InsertCommand.Connection = conn
        da.InsertCommand.Parameters.Add("@Direction", OleDbType.VarChar, 40, "Direction")
        da.InsertCommand.Parameters.Add("@Robot", OleDbType.VarChar, 40, "Robot")
        da.InsertCommand.Parameters.Add("@X", OleDbType.VarChar, 40, "X")
        da.InsertCommand.Parameters.Add("@Y", OleDbType.VarChar, 40, "Y")


        da.DeleteCommand = New OleDbCommand("DELETE FROM RobotMovement WHERE ID = @ID")
        da.DeleteCommand.Connection = conn
        da.DeleteCommand.Parameters.Add("@ID", OleDbType.Integer, 5, "ID")


        da.Fill(ds)

        If ds.Tables(0).Rows.Count > 0 Then 'Check to see if the table is empty
            FillFields()
        End If

Shawn