Solved

How do I update a database with a command object?

Posted on 2008-10-14
7
123 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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.
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…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 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

24 Experts available now in Live!

Get 1:1 Help Now