Solved

How do I update a database with a command object?

Posted on 2008-10-14
7
124 Views
Last Modified: 2010-04-23
I've created a Jeopardy style game in VB.NET. I have an Access database with the categories and questions and possible answers. I'm pulling all the categories and putting them in a datatable. The category table has a field "Used" to track whether it has been used('N'). What I need to do is change the field so the used categories don't get loaded in the next game. I know the CategoryID for the ones used, but how do I create a command object or possibly a dataadapter with the update SQL?
Public Function GetCategories() As DataTable

        Dim cn As New OleDb.OleDbConnection(m_sCN) 'new connection

        Dim dt As New DataTable

        Dim sSQL As String = "Select * from tblCategory where Used = 'N'"

        Dim cmd As New OleDb.OleDbCommand(sSQL, cn)

        'get data

        Try

            cn.Open()

            dt.Load(cmd.ExecuteReader)

            GetCategories = dt

            cmd.Dispose()

            cn.Close()

            cn.Dispose()

        Catch ex As Exception

            Throw ex

        End Try

    End Function

 

    Public Function GetQuestion() As DataTable

        Dim cn As New OleDb.OleDbConnection(m_sCN) 'new connection

        Dim dt As New DataTable

        Dim sSQL As String = "Select * from tblQuestion"

        Dim cmd As New OleDb.OleDbCommand(sSQL, cn)

        'get data

        Try

            cn.Open()

            dt.Load(cmd.ExecuteReader)

            GetQuestion = dt

            cmd.Dispose()

            cn.Close()

            cn.Dispose()

        Catch ex As Exception

            Throw ex

        End Try

    End Function

 

Private Sub frmBoard_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim oBoard As New ClsBoard

        Dim dtCategories As New DataTable

       dtCategories = oBoard.GetCategories

        txtCat1.Text = dtCategories.Rows(0).Item(1)

        txtCat2.Text = dtCategories.Rows(1).Item(1)

        txtCat3.Text = dtCategories.Rows(2).Item(1)

        txtCat4.Text = dtCategories.Rows(3).Item(1)

        txtCat5.Text = dtCategories.Rows(4).Item(1)

        txtCat6.Text = dtCategories.Rows(5).Item(1)

 

Private Sub btnCol1_click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn1200.Click, btn1400.Click, btn1600.Click, btn1800.Click, btn11000.Click

 

        m_sender = Strings.Right(sender.tag, 1)

        icolumn = Strings.Left(sender.tag, 1) - 1

        irow = Strings.Right(sender.tag, 1) - 1

 

        'hook up array

        Dim frmQ As New frmQuestion

        vwQuestion.RowFilter = "CategoryID = '" & CStr(m_Categories.Rows(icolumn).Item("CategoryID")).Replace("'", "\'") & "'"

        frmQ.Question = vwQuestion.ToTable.Rows(irow).Item(2)

        frmQ.Answer1 = vwQuestion.ToTable.Rows(irow).Item(3)

        frmQ.Answer2 = vwQuestion.ToTable.Rows(irow).Item(4)

        frmQ.Answer3 = vwQuestion.ToTable.Rows(irow).Item(5)

        frmQ.Answer4 = vwQuestion.ToTable.Rows(irow).Item(6)

        frmQ.CorrectAnswer = vwQuestion.ToTable.Rows(irow).Item(7)

'Sorry for the lengthy code

Open in new window

0
Comment
Question by:98fatboyrider
  • 4
  • 3
7 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22713634
I would use a Command object

Dim cmd as new OleDbCommand(cn)
cmd.CommandText = "Update MyTable Set field1 = " + Value1 + " Where ID = " + ID

cmd.ExecuteNonQuery
0
 

Author Comment

by:98fatboyrider
ID: 22714123
I'm fairly new at this(SQL & command objects), would you mind showing me how to create this and where to place it in my code?
~TIA
0
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 500 total points
ID: 22714220
Without seeing more of the code, it's hard to say where to put the code.  But here is code to get you closer ...

Dim cn As New OleDb.OleDbConnection(m_sCN) 'new connection

cn.open()

dim cmd as new OleDb.OleDbCommand(cn)
 

dim sql as string

sql = "Update Category set Used = 'Y' Where CategoryID = " + CategoryID
 

cmd.CommandText = sql

cmd.ExecuteNonQuery()
 

cn.close()

Open in new window

0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:98fatboyrider
ID: 22715115
Thanks. Below is additional code if it would help.
Public Class frmBoard

    Inherits System.Windows.Forms.Form

 

    Private m_Categories As DataTable

    Private m_Question As String

 

    Dim dtQuestions As DataTable

    Dim arrCol1(4) As Button

    Dim arrCol2(4) As Button

    Dim arrCol3(4) As Button

    Dim arrCol4(4) As Button

    Dim arrCol5(4) As Button

    Dim arrCol6(4) As Button

 

    Private Sub frmGame_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim oBoard As New ClsBoard

        Dim dtCategories As New DataTable

 

        'hook up array

        arrCol1(0) = btn1200

        arrCol1(1) = btn1400

        arrCol1(2) = btn1600

        arrCol1(3) = btn1800

        arrCol1(4) = btn11000

 

        dtCategories = oBoard.GetCategories

        txtCat1.Text = dtCategories.Rows(0).Item(1)

        txtCat2.Text = dtCategories.Rows(1).Item(1)

        txtCat3.Text = dtCategories.Rows(2).Item(1)

        txtCat4.Text = dtCategories.Rows(3).Item(1)

        txtCat5.Text = dtCategories.Rows(4).Item(1)

        txtCat6.Text = dtCategories.Rows(5).Item(1)

        For x = 0 To 4

            arrCol1(x).Text = (x + 1) * 200

            arrCol1(x).Tag = x

        Next

        Me.Show()

    End Sub

 

    Private Sub btnCol1_click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn1200.Click, btn1400.Click, btn1600.Click, btn1800.Click, btn11000.Click

        Dim frmQ As New frmQuestion

        Dim vwQuestion As New DataView(dtQuestions)

 

        'hook up array

        For x = 0 To m_Categories.Rows.Count - 1

            vwQuestion.RowFilter = "CategoryID = " & m_Categories.Rows(x).Item(1)

            frmQ.Question = vwQuestion.ToTable.Rows(sender.tag).Item(2)

            frmQ.Answer1 = vwQuestion.ToTable.Rows(sender.tag).Item(3)

            frmQ.Answer2 = vwQuestion.ToTable.Rows(sender.tag).Item(4)

            frmQ.Answer3 = vwQuestion.ToTable.Rows(sender.tag).Item(5)

            frmQ.Answer4 = vwQuestion.ToTable.Rows(sender.tag).Item(6)

            frmQ.CorrectAnswer = vwQuestion.ToTable.Rows(sender.tag).Item(7)

        Next

    End Sub

 

Public Class frmQuestion

    Inherits System.Windows.Forms.Form

    'Private m_Categories As DataTable

    Private m_Question As String

    Private m_Answer1 As String

    Private m_Answer2 As String

    Private m_Answer3 As String

    Private m_Answer4 As String

    Private m_CorrectAnswer As String

 

    Public Property Question() As String

        Get

            Return m_Question

        End Get

        Set(ByVal value As String)

            m_Question = value

        End Set

    End Property

 

    Public Property Answer1() As String

        Get

            Return m_Answer1

        End Get

        Set(ByVal value As String)

            m_Answer1 = value

        End Set

    End Property

 

    Public Property Answer2() As String

        Get

            Return m_Answer2

        End Get

        Set(ByVal value As String)

            m_Answer2 = value

        End Set

    End Property

 

    Public Property Answer3() As String

        Get

            Return m_Answer3

        End Get

        Set(ByVal value As String)

            m_Answer3 = value

        End Set

    End Property

 

    Public Property Answer4() As String

        Get

            Return m_Answer4

        End Get

        Set(ByVal value As String)

            m_Answer4 = value

        End Set

    End Property

 

    Public Property CorrectAnswer() As String

        Get

            Return m_CorrectAnswer

        End Get

        Set(ByVal value As String)

            m_CorrectAnswer = value

        End Set

    End Property

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

        lblTimer.Text = 15

        Timer1.Interval = 1000

        Timer1.Enabled = True

 

 

        Dim oBoard As New ClsBoard

        Dim dtQuestions As DataTable

        dtQuestions = oBoard.GetQuestion

 

        lblQuestion.Text = Me.Question

        lblOpt1.Text = Me.Answer1

        lblOpt2.Text = Me.Answer2

        lblOpt3.Text = Me.Answer3

        lblOpt4.Text = Me.Answer4

 

        Me.Show()

    End Sub

 

Imports System.Data.OleDb

Public Class ClsBoard

    Dim oBoard As ClsBoard

    Dim m_sCN As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\Jeopardy07.accdb;User Id=admin;Password=;"

 

    Public Function GetCategories() As DataTable

 

        Dim cn As New OleDb.OleDbConnection(m_sCN) 'new connection

        Dim dt As New DataTable

        Dim sSQL As String = "Select * from tblCategory where Used = 'N'"

        Dim cmd As New OleDb.OleDbCommand(sSQL, cn)

        'get data

        Try

            cn.Open()

            dt.Load(cmd.ExecuteReader)

            GetCategories = dt

            cmd.Dispose()

            cn.Close()

            cn.Dispose()

 

        Catch ex As Exception

            Throw ex

        End Try

    End Function

 

    Public Function GetQuestion() As DataTable

 

        Dim cn As New OleDb.OleDbConnection(m_sCN) 'new connection

        Dim dt As New DataTable

        Dim sSQL As String = "Select * from tblQuestion"

        Dim cmd As New OleDb.OleDbCommand(sSQL, cn)

        'get data

        Try

            cn.Open()

            dt.Load(cmd.ExecuteReader)

            GetQuestion = dt

            cmd.Dispose()

            cn.Close()

            cn.Dispose()

 

        Catch ex As Exception

            Throw ex

        End Try

    End Function

End Class

Open in new window

0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22757091
do you have a button_click event in which you want to do this update?  Is it the btnCol1_Click?
0
 

Author Comment

by:98fatboyrider
ID: 22773080
Friend Function Used(ByVal iCatID As Integer) As Integer
      Dim cn As New OleDb.OleDbConnection(m_sCN) 'new connection
      Dim sSQL As String
      sSQL = "Update tblCategory SET Used = 'Y' Where CategoryID = " & iCatID.ToString
      Dim cmd As OleDbCommand = New OleDbCommand(sSQL, cn)
      Try
         cn.Open()
         Used = cmd.ExecuteNonQuery
         cmd.Dispose()
         cn.Close()
         cn.Dispose()
      Catch ex As Exception
         Throw ex
      End Try
   End Function
End Class
0
 

Author Closing Comment

by:98fatboyrider
ID: 31505998
Thanks for the help.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now