?
Solved

How do I update a database with a command object?

Posted on 2008-10-14
7
Medium Priority
?
130 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
Not sure which OpenStack Certification to get?

So you’ve realized you might want to get certified in OpenStack, but you’re not sure what the benefits might be or even which one you should take. You know there are several certification courses you can choose from, but how do you know which one is right for you?

 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses
Course of the Month12 days, 19 hours left to enroll

777 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