update the db using values in a datagrid

Please support our VB.NET advertiser: Intel Parallel Studio Home
Reply

Join Date: Oct 2007
Posts: 2
Reputation: diana_j86 is an unknown quantity at this point 
Solved Threads: 0
diana_j86 diana_j86 is offline Offline
Newbie Poster

update the db using values in a datagrid

 
0
  #1
Oct 9th, 2007
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,
Reply With Quote Quick reply to this message  
Join Date: Jun 2005
Posts: 85
Reputation: williamrojas78 is an unknown quantity at this point 
Solved Threads: 4
williamrojas78's Avatar
williamrojas78 williamrojas78 is offline Offline
Junior Poster in Training

Re: update the db using values in a datagrid

 
0
  #2
Oct 9th, 2007
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.

  1. 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.
Reply With Quote Quick reply to this message  
Reply

This thread is more than three months old.
Perhaps start a new thread instead?
Message:


Thread Tools Search this Thread



About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC