Hi,
I have combo box binded to access.when I click button it will transfer data corresponding to the item selected in the combo box.But I want all data corresponding to all the items in the combo box to be transferred to the data base.Below is my code
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim st As Integer
Dim storenumber As Integer
storenumber = CType(ComboBox1.SelectedValue.ToString(), Integer)
For st = 0 To ComboBox1.Items.Count - 1
Try
Dim cnx As OdbcConnection
Dim dt As New DataTable
Dim dt1 As New DataTable
Dim da As New OdbcDataAdapter
Dim da1 As New OdbcDataAdapter
Dim ccmd As OdbcCommand
Dim ncmd As OdbcCommand
Dim i As Integer = 0
Dim cnxstr As String
Dim CSVFolder As String = "C:\Temp\fpcost_csv"
Dim cmd As New OleDbCommand
Dim scmd As New OleDbCommand
Dim CSVFileName As String = "cshpmxp1_41.csv"
Dim cs As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\test1.mdb;"
cnxstr = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & CSVFolder & ";"
cnx = New OdbcConnection(cnxstr)
Dim datcal As Integer
ncmd = New OdbcCommand("select distinct (COSDAY) from [INVCOSP0_" & storenumber & ".csv]", cnx)
scmd.CommandType = CommandType.Text
da1.SelectCommand = ncmd
cnx.Open()
dt1.Clear()
da1.Fill(dt1)
DataGridView1.DataSource = dt1
DataGridView1.Refresh()
cnx.Close()
datcal = DBConn(storenumber)
If datcal = 0 Then
ccmd = New OdbcCommand("Select COSDAY,COSMIC,COSFOT,COSPOT from [INVCOSP0_" & storenumber & ".csv]", cnx)
Else
ccmd = New OdbcCommand("Select COSDAY,COSMIC,COSFOT,COSPOT from [INVCOSP0_" & storenumber & ".csv] where COSDAY > " & datcal, cnx)
End If
cmd.CommandType = CommandType.Text
da.SelectCommand = ccmd
cnx.Open()
dt.Clear()
da.Fill(dt)
DataGridView2.DataSource = dt
DataGridView2.Refresh()
cnx.Close()
Dim dd As String
Dim con As OleDbConnection = New OleDbConnection(cs)
con.Open()
Do While i < DataGridView2.RowCount - 1
storenumber = CType(ComboBox1.SelectedValue.ToString(), Integer)
dd = Mid(DataGridView2.Item(0, i).Value, 5, 2) & "/" & Mid(DataGridView2.Item(0, i).Value, 7, 2) & "/" & Mid(DataGridView2.Item(0, i).Value, 1, 4)
Sql = "update test set fcost= " & DataGridView2.Item(2, i).Value & ", pcost=" & DataGridView2.Item(3, i).Value & " where bday=#" & CDate(dd) & "# and strno=" & storenumber & " and mcode= " & DataGridView2.Item(1, i).Value & " "
cmd = New OleDbCommand(Sql, con)
cmd.ExecuteNonQuery()
i = i + 1
Loop
cmd.Dispose()
con.Close()
Catch ex As Exception
MessageBox.Show(ex.Message, "Store : " & st)
End Try
Next
MsgBox("The Record is Updated", MsgBoxStyle.OkOnly, "Confirmation")
End Sub