Hello All.

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:

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
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:
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)
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(),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)
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,

Hi,

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.


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