locked
Database not updating (Windows Forms App) RRS feed

  • Pergunta

  • Ok....Here is my vb.net code and I'm trying to update a dataset into the Database from which I got the DataAdapter.  My DataAdapter uses a SELECT w/ a JOIN in it and it never created INSERT or UPDATE stmt's using the DataAdapter wizard.  So in my btnInActiveAftNxtJP_Click event I update the Insert Command Text and run the update but it's not updating the SQL Server 2000 DB.....Should I have created a DataTable or is something wrong with the way I'm doing the update?...I can't figure it out....Please help!...Thanks!




    Imports System.Data
    Imports System.Data.SqlClient



    Public Class Form1
        Inherits System.Windows.Forms.Form
       

    #Region " Windows Form Designer generated code "

        Public Sub New()
            MyBase.New()

            InitializeComponent()

        End Sub

      Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
            If disposing Then
                If Not (components Is Nothing) Then
                    components.Dispose()
                End If
            End If
            MyBase.Dispose(disposing)
        End Sub

        Private components As System.ComponentModel.IContainer

        Friend WithEvents Label1 As System.Windows.Forms.Label
        Friend WithEvents cboProgDefs As System.Windows.Forms.ComboBox
        Friend WithEvents btnExit As System.Windows.Forms.Button
        Friend WithEvents DarwinCJWAP As System.Data.SqlClient.SqlConnection
        Friend WithEvents CJWAPDataAdapter As System.Data.SqlClient.SqlDataAdapter
        Friend WithEvents CjWap1 As GuiTest.CJWap
        Friend WithEvents lblInactiveAftNextJack As System.Windows.Forms.Label
        Friend WithEvents MainMenu1 As System.Windows.Forms.MainMenu
        Friend WithEvents txtInActNextJackpt_main As System.Windows.Forms.TextBox
        Friend WithEvents txtActive_main As System.Windows.Forms.TextBox
        Friend WithEvents lblActive_main As System.Windows.Forms.Label
        Friend WithEvents btnActive As System.Windows.Forms.Button
        Friend WithEvents btnInActiveAftNxtJP As System.Windows.Forms.Button
        Friend WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand
        <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
            Me.Label1 = New System.Windows.Forms.Label
            Me.cboProgDefs = New System.Windows.Forms.ComboBox
            Me.CjWap1 = New GuiTest.CJWap
            Me.btnExit = New System.Windows.Forms.Button
            Me.btnInActiveAftNxtJP = New System.Windows.Forms.Button
            Me.btnActive = New System.Windows.Forms.Button
            Me.txtInActNextJackpt_main = New System.Windows.Forms.TextBox
            Me.lblInactiveAftNextJack = New System.Windows.Forms.Label
            Me.txtActive_main = New System.Windows.Forms.TextBox
            Me.lblActive_main = New System.Windows.Forms.Label
            Me.DarwinCJWAP = New System.Data.SqlClient.SqlConnection
            Me.CJWAPDataAdapter = New System.Data.SqlClient.SqlDataAdapter
            Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
            Me.MainMenu1 = New System.Windows.Forms.MainMenu
            CType(Me.CjWap1, System.ComponentModel.ISupportInitialize).BeginInit()
            Me.SuspendLayout()
          
            Me.Label1.Font = New System.Drawing.Font("Microsoft Sans Serif", 14.0!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
            Me.Label1.Location = New System.Drawing.Point(24, 24)
            Me.Label1.Name = "Label1"
            Me.Label1.Size = New System.Drawing.Size(232, 23)
            Me.Label1.TabIndex = 0
            Me.Label1.Text = "Progressive Definitions:"
          
            Me.cboProgDefs.DataSource = Me.CjWap1
            Me.cboProgDefs.DisplayMember = "tblProgressiveConfig.Name"
            Me.cboProgDefs.Location = New System.Drawing.Point(24, 56)
            Me.cboProgDefs.Name = "cboProgDefs"
            Me.cboProgDefs.Size = New System.Drawing.Size(232, 21)
            Me.cboProgDefs.TabIndex = 1
            Me.cboProgDefs.ValueMember = "tblProgressiveConfig.ID"
           
            Me.CjWap1.DataSetName = "CJWap"
            Me.CjWap1.Locale = New System.Globalization.CultureInfo("en-US")
          
            Me.btnExit.Font = New System.Drawing.Font("Microsoft Sans Serif", 10.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
            Me.btnExit.Location = New System.Drawing.Point(376, 432)
            Me.btnExit.Name = "btnExit"
            Me.btnExit.TabIndex = 2
            Me.btnExit.Text = "Exit"
           
            Me.btnInActiveAftNxtJP.Location = New System.Drawing.Point(208, 64)
            Me.btnInActiveAftNxtJP.Name = "btnInActiveAftNxtJP"
            Me.btnInActiveAftNxtJP.TabIndex = 12
            Me.btnInActiveAftNxtJP.Text = "Change"
           
            Me.btnActive.Location = New System.Drawing.Point(208, 24)
            Me.btnActive.Name = "btnActive"
            Me.btnActive.TabIndex = 11
            Me.btnActive.Text = "Change"
           
            Me.txtInActNextJackpt_main.DataBindings.Add(New System.Windows.Forms.Binding("Text", Me.CjWap1, "tblProgressiveConfig.InactiveAfterNextJackpot"))
            Me.txtInActNextJackpt_main.Location = New System.Drawing.Point(120, 64)
            Me.txtInActNextJackpt_main.Name = "txtInActNextJackpt_main"
            Me.txtInActNextJackpt_main.ReadOnly = True
            Me.txtInActNextJackpt_main.Size = New System.Drawing.Size(64, 20)
            Me.txtInActNextJackpt_main.TabIndex = 3
            Me.txtInActNextJackpt_main.Text = ""
            Me.txtInActNextJackpt_main.TextAlign = System.Windows.Forms.HorizontalAlignment.Right
           
            Me.lblInactiveAftNextJack.Font = New System.Drawing.Font("Microsoft Sans Serif", 10.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
            Me.lblInactiveAftNextJack.Location = New System.Drawing.Point(8, 56)
            Me.lblInactiveAftNextJack.Name = "lblInactiveAftNextJack"
            Me.lblInactiveAftNextJack.Size = New System.Drawing.Size(88, 23)
            Me.lblInactiveAftNextJack.TabIndex = 2
            Me.lblInactiveAftNextJack.Text = "InActive After"
           
            Me.txtActive_main.DataBindings.Add(New System.Windows.Forms.Binding("Text", Me.CjWap1, "tblProgressiveConfig.Active"))
            Me.txtActive_main.Location = New System.Drawing.Point(120, 24)
            Me.txtActive_main.Name = "txtActive_main"
            Me.txtActive_main.ReadOnly = True
            Me.txtActive_main.Size = New System.Drawing.Size(64, 20)
            Me.txtActive_main.TabIndex = 1
            Me.txtActive_main.Text = ""
            Me.txtActive_main.TextAlign = System.Windows.Forms.HorizontalAlignment.Right
           
            Me.lblActive_main.Font = New System.Drawing.Font("Microsoft Sans Serif", 10.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
            Me.lblActive_main.Location = New System.Drawing.Point(8, 24)
            Me.lblActive_main.Name = "lblActive_main"
            Me.lblActive_main.Size = New System.Drawing.Size(48, 23)
            Me.lblActive_main.TabIndex = 0
            Me.lblActive_main.Text = "Active:"
           
            Me.DarwinCJWAP.ConnectionString = "workstation id=STEVENAIELLO;packet size=4096;integrated security=SSPI;data source" & _
            "=DARWIN;persist security info=False;initial catalog=CJWAP"
           
            Me.CJWAPDataAdapter.SelectCommand = Me.SqlSelectCommand1
            Me.CJWAPDataAdapter.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "tblProgressiveConfig", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("ID", "ID"), New System.Data.Common.DataColumnMapping("Name", "Name"), New System.Data.Common.DataColumnMapping("Active", "Active"), New System.Data.Common.DataColumnMapping("InactiveAfterNextJackpot", "InactiveAfterNextJackpot"), New System.Data.Common.DataColumnMapping("BaseAmount", "BaseAmount"), New System.Data.Common.DataColumnMapping("Denomination", "Denomination"), New System.Data.Common.DataColumnMapping("SkinDirectory", "SkinDirectory"), New System.Data.Common.DataColumnMapping("EPSName", "EPSName"), New System.Data.Common.DataColumnMapping("CasinoID", "CasinoID")})})
           
            Me.SqlSelectCommand1.CommandText = "SELECT PC.ID, PC.Name, PC.Active, PC.InactiveAfterNextJackpot, PC.BaseAmount, PC." & _
            "Denomination, PC.SkinDirectory, JP.EPSName, JP.CasinoID FROM tblProgressiveConfi" & _
            "g PC INNER JOIN tblJackpot JP ON PC.ID = JP.ProgressiveID"
            Me.SqlSelectCommand1.Connection = Me.DarwinCJWAP
          
            Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
            Me.ClientSize = New System.Drawing.Size(568, 465)
            Me.ContextMenu = Me.ContextMenu1
            Me.Controls.Add(Me.btnNewDef)
            Me.Controls.Add(Me.GroupBox1)
            Me.Controls.Add(Me.btnExit)
            Me.Controls.Add(Me.cboProgDefs)
            Me.Controls.Add(Me.Label1)
            Me.MaximizeBox = False
            Me.Menu = Me.MainMenu1
            Me.MinimizeBox = False
            Me.Name = "Form1"
            Me.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen
            Me.Text = "Wide Area Progressives"
            CType(Me.CjWap1, System.ComponentModel.ISupportInitialize).EndInit()
            Me.ResumeLayout(False)

        End Sub

    #End Region

      


        Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
          
            Me.Close()

        End Sub

        Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
          
            CJWAPDataAdapter.Fill(CjWap1)

        End Sub

        Private Sub Form1_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
         
            CJWAPDataAdapter.Update(CjWap1)

        End Sub

        Private Sub btnActive_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnActive.Click
            If txtActive_main.Text = "True" Then
                txtActive_main.Text = "False"
            ElseIf txtActive_main.Text = "False" Then
                txtActive_main.Text = "True"
            End If
        End Sub

        Private Sub btnInActiveAftNxtJP_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInActiveAftNxtJP.Click
            Dim Active As Integer

            If txtInActNextJackpt_main.Text = "True" Then
                txtInActNextJackpt_main.Text = "False"
                Active = 0

            ElseIf txtInActNextJackpt_main.Text = "False" Then
                txtInActNextJackpt_main.Text = "True"
                Active = 1
            End If
           
           CJWAPDataAdapter.UpdateCommand.CommandText() = "UPDATE tblProgressiveConfig SET InActiveAfterNextJackpot='" & Active & "' WHERE ID='" & cboProgDefs.ValueMember & "'"
           

            CJWAPDataAdapter.Update(CjWap1)
          

        End Sub


    End Class





     



    quarta-feira, 31 de agosto de 2005 14:40

Respostas

  • No...The sqlCommand Object will work just fine with an SQL Db.

    assuming that cboProgDefs is a combo box try cboProgDefs.SelectedItem to return what is selected

    You also need to set the minimum command properties:

    Dim MyC As SqlCommand

    MyC.CommandType = CommandType.Text

    MyC.CommandText = MyText

    MyC.Connection = MyConn

    Then:

    MyConn.Open
    Dim MyReturn as Integer = MyCommand.ExecuteNonQuery
    MyConn.Close

    In Order to test this command change the variables in your command text to known literals and execute...

    Note:  Anytime you work with the Db you should wrap your code in a try/catch block...this will enable you to trap any errors and help debugging your code

    quarta-feira, 31 de agosto de 2005 18:07

Todas as Respostas

  • <quote> 


    CJWAPDataAdapter.UpdateCommand.CommandText() = "UPDATE tblProgressiveConfig SET InActiveAfterNextJackpot='" & Active & "' WHERE ID='" & cboProgDefs.ValueMember & "'"  
    CJWAPDataAdapter.Update(CjWap1)



     
    </quote>

    SInce you are setting the values in the command string progmatically you may want to consider manually executing your command vs using the update from the data adapter



    Dim MyCommand as New OleDbCommand
    MyCommand.CommandText ="UPDATE tblProgressiveConfig SET InActiveAfterNextJackpot='" & Active & "' WHERE ID='" & cboProgDefs.ValueMember & "'"  
    Dim MyReturn as Integer = MyCommand.ExecuteNonQuery

     

    MyReturn will contain an integer indicating how many records were effected...
    You must also make sure that the variables ACtive and cboProgDefs.Valuemember actually contain valid values.
    quarta-feira, 31 de agosto de 2005 15:58

  • Dim MyCommand as New SqlCommand MyCommand.CommandText ="UPDATE tblProgressiveConfig SET InActiveAfterNextJackpot='" & Active & "' WHERE ID='" & cboProgDefs.ValueMember & "'" Dim MyReturn as Integer = MyCommand.ExecuteNonQuery
     


    I used a SqlCommand instead of a OleDbCommand because I'm using a SQL Server DB.  Should I Import the OLEDB Namespace to use that instead?  I am getting a system error at: 
    Dim MyReturn as Integer = MyCommand.ExecuteNonQuery.  Also, the cboProgDefs.ValueMember is not showing up correctly in my Command String.  It is showing up as the actual words "tblProgressiveConfig.ID" instead of the actual ID # that is in the DB.

    quarta-feira, 31 de agosto de 2005 16:22
  • No...The sqlCommand Object will work just fine with an SQL Db.

    assuming that cboProgDefs is a combo box try cboProgDefs.SelectedItem to return what is selected

    You also need to set the minimum command properties:

    Dim MyC As SqlCommand

    MyC.CommandType = CommandType.Text

    MyC.CommandText = MyText

    MyC.Connection = MyConn

    Then:

    MyConn.Open
    Dim MyReturn as Integer = MyCommand.ExecuteNonQuery
    MyConn.Close

    In Order to test this command change the variables in your command text to known literals and execute...

    Note:  Anytime you work with the Db you should wrap your code in a try/catch block...this will enable you to trap any errors and help debugging your code

    quarta-feira, 31 de agosto de 2005 18:07
  • Thanks a lot!.....I had to use the cboProgDefs.SelectedValue to get it to work and used the code you provided.....I'm not sure how to use the try/catch blocks yet as I'm really new to VB....thanks for your help though!Big Smile
    quarta-feira, 31 de agosto de 2005 18:32