update the db using values in a datagrid

diana_j86

New member
Joined
Oct 7, 2007
Messages
3
Programming Experience
Beginner
I have 3 tables in database, they are:
- DEBT: ACCT, DESK_KEY, SECT_KEY, DESK_DATE, SECT_DATE
- DESK: DESK_NAME, DESK_KEY(primary key)
- SECT : SECT_NAME, SECT_KEY(primary key)

The Application works like the following:
I have a browse button and a corresponding textbox, you should choose an Excel Sheet and it’s path will be typed in the textbox. In all excel sheets that u are to choose there is a column called Acct.
Also, I have two comboboxes, the first one is filled with DESK_NAME as a text, and DESK_KEY as a value. And the second combobox is filled with SECT_NAME as a text and SECK_KEY as a value.
When you select and EXCEL Sheet, a DESK_NAME and a SECT_NAME, DEBT table should be updated, taking all the Acct’s stored in the EXCEL SHEET and updating all the rows containing these Accts in the DEBT table, so the DESK_DATE and SECT date will be set to the current date, while the DESK_KEY and the SECT_KEY will be set to the chosen DESK_KEY and SECT_KEY in the comboboxes.

I started to write the code, but I have many difficulties as I am new to VB.NET...

- A Browse Button containg:

VB.NET:
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        fdlg.Title = "C# Corner Open File Dialog"
        fdlg.InitialDirectory = "c:\"
        fdlg.Filter = "All Excel Sheets (*.xls)|*.xls|All files (*.xls)|*.xls"
        fdlg.FilterIndex = 2
        fdlg.RestoreDirectory = True
        If fdlg.ShowDialog() = DialogResult.OK Then
            TextBox1.Text = fdlg.FileName
        End If

- A Fetch button that displays the values in Acct column from the selected Excel sheet in a datagrid:

VB.NET:
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        x = Me.TextBox1.Text
        Dim connstring2 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
     "Data Source=" & x & ";Extended Properties=""Excel 8.0;HDR=YES;"""
        Dim ds As DataSet
        Dim dr As DataRow
        Dim olecon As OleDbConnection
        Dim olecomm As OleDbCommand
       Dim oleadpt As OleDbDataAdapter
        Try
            olecon = New OleDbConnection
            olecon.ConnectionString = connstring2
            olecomm = New OleDbCommand
            olecomm.CommandText = "Select Acct from [Sheet1$]"
            olecomm.Connection = olecon
            oleadpt = New OleDbDataAdapter(olecomm)
            ds = New DataSet
            olecon.Open()
            oleadpt.Fill(ds, "Sheet1")
            If IsNothing(ds) = False Then
                DataGrid1.SetDataBinding(ds, "Sheet1")
                End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            olecon.Close()
            olecon = Nothing
            olecomm = Nothing
            oleadpt = Nothing
            ds = Nothing
            dr = Nothing
        End Try
    End Sub


- I used the following code to fill the two comboboxes:
VB.NET:
Private Sub fillComboBox1()
        Try
            Dim strSQL As String = "Select DESK_KEY,DESK_NAME from DESK"
            Dim da As New SqlDataAdapter(strSQL, conn)
            Dim ds As New DataSet
            da.Fill(ds, "DESK")
            ComboBox1.DropDownStyle = ComboBoxStyle.DropDownList
            With ComboBox1
                .DataSource = ds.Tables("DESK")
                .DisplayMember = "DESK_NAME"
                .ValueMember = "DESK_KEY"
                .SelectedIndex = 0
            End With
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
    End Sub

- Now the Update Button( and I have problems here)

VB.NET:
    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Try
            If Me.TextBox1.Text <> " " Then
                Dim sqlString As String = _
"update DEBT set DESK_KEY=@DESK_KEY,SECT_KEY,SECT_DATE=getdate(),DESK_DATE=getdate()where Acct=@Acct"
                Dim comd As New SqlCommand(sqlString, conn)
                comd.CommandType = CommandType.Text
[B][I]                ' comd.Parameters.Add("@Acct", Me.DataGrid1.item.Cells(0).Text)[/I][/B]
                comd.Parameters.Add("@DESK_KEY", Me.ComboBox1.SelectedValue)
                comd.Parameters.Add("@SECT_KEY", Me.ComboBox2.SelectedValue)
                conn.Open()
                comd.ExecuteNonQuery()
                conn.Close()
                MsgBox("<script>alert('Update is done successflly')</script>")
                Me.TextBox1.Text = ""
            End If
        Catch ex As ArgumentException
            MsgBox("<script>alert('This Row Can Not Be Updated')</script>")
            Me.TextBox1.Text = ""
        End Try
    End Sub



I would be very thankful if any of u could tell me how can i update the DEBT table using the values in the datagrid, or if there are any other better suggestions to solve this application please reply me.

Thanks alot,
 
Last edited by a moderator:
well, it is not a homework, I have field training, and they asked me if i can do that using vb.net, so I am trying to do it, but it is not necessary.

I am new to vb.net, I can program in Java and C++, but I am trying to learn how to connect to database, and use the datagrid by myself.

I would appriciate if I got more helpful replys.

Thanks,
 
K.. Youre using .NET 1.1 which IMHO isnt amazing for database access, but that's because I get bored easily and find writing database access code very tedious. If you follow the DW1 link in my signature you will find a very useful and comprehensive set of articles about doing database access with your version of .NET
 
Back
Top