Solved

How do I update a database with a command object?

Posted on 2008-10-14
7
126 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

821 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