Hello All.

I have 3 tables in database, they are:
- 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:

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:

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
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
oleadpt.Fill(ds, "Sheet1")
If IsNothing(ds) = False Then
DataGrid1.SetDataBinding(ds, "Sheet1")
End If
Catch ex As Exception
olecon = Nothing
olecomm = Nothing
oleadpt = Nothing
ds = Nothing
dr = Nothing
End Try
End Sub

- I used the following code to fill the two comboboxes:
Private Sub fillComboBox1()
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
End Try
End Sub

- Now the Update Button( and I have problems here)
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
If Me.TextBox1.Text <> " " Then
Dim sqlString As String = "update DEBT set DESK_KEY=@DESK_KEY,SECT_KEY,SECT_DATE=getdate(),DE SK_DATE=getdate()where Acct=@Acct"
Dim comd As New SqlCommand(sqlString, conn)
comd.CommandType = CommandType.Text
' comd.Parameters.Add("@Acct", Me.DataGrid1.item.Cells(0).Text)
comd.Parameters.Add("@DESK_KEY", Me.ComboBox1.SelectedValue)
comd.Parameters.Add("@SECT_KEY", Me.ComboBox2.SelectedValue)
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,

10 Years
Discussion Span
Last Post by williamrojas78


I noticed you are using the oledb classes, so why don't you use a oleDbCommandBuilder to update your database? The command builder will create the Insert, update, delete commands automatically for you.

Dim cmdbuilder As OleDBCommandBuilder = New OleDBCommandBuilder(oleadpt)

For this I think you need to make the data adapter global (or private to the form) and possibly the OleDbConnection and OleDbCommand.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.