Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How do I update a database with a command object?

Posted on 2008-10-14
7
Medium Priority
?
133 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

636 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