Paul Edward Pagente Paul Edward Pagente - 2 months ago 6
Vb.net Question

Proper use and Transition of Subs

I have an Image here that looks like this.

enter image description here

This code together is inside two Subs namely

Public Sub Import_Data
and
Public Sub Insert_Data


To make it clearer I will put the codes in Transition and here it is.

I will click the button import

Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
Import_Data
Insert_Data
End Sub

Sub import_data
'This is the code when I put the excel data in datagridview
Dim conn As OleDbConnection
Dim dta As OleDbDataAdapter
Dim dts As DataSet
Dim excel As String
Dim OpenFileDialog As New OpenFileDialog
OpenFileDialog.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.MyDocuments
OpenFileDialog.Filter = "All Files (*.*)|*.*|Excel files (*.xlsx)|*.xlsx|CSV Files (*.csv)|*.csv|XLS Files (*.xls)|*xls"
If (OpenFileDialog.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK) Then
Dim fi As New FileInfo(OpenFileDialog.FileName)
Dim FileName As String = OpenFileDialog.FileName
excel = fi.FullName
conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excel + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1';")
dta = New OleDbDataAdapter("Select F3 as ItemCode,F4 as Description,F6 as OrderedQty,F9 as Remarks,F10 as Tag From [Official Transmittal Form$]", conn)
dts = New DataSet
dta.Fill(dts, 13, 768, "MyTable")
DataGridView1.DataSource = dts
DataGridView1.DataMember = "MyTable"
conn.Close()
End if
End Sub

Sub Insert_Data
'This is the code that will save all data in the datagridview into MYSQL
Try
Dim myconnect As MySqlConnection = New MySqlConnection("datasource=192.168.2.254;database=inventory;userid=root;password=admin1950")
Dim queryInsert As String = "insert into stockrequisition (SRNumber,ItemCode,Description,RequestedQty,Remarks) values (@SRNumber,@ItemCode,@Description,@RequestedQty,@Remarks)"
Dim cmd = New MySqlCommand(queryInsert, myconnect)
cmd.Parameters.Add("@SRNumber", MySqlDbType.VarChar)
cmd.Parameters.Add("@ItemCode", MySqlDbType.VarChar)
cmd.Parameters.Add("@Description", MySqlDbType.VarChar)
cmd.Parameters.Add("@RequestedQty", MySqlDbType.VarChar)
cmd.Parameters.Add("@Remarks", MySqlDbType.VarChar)

For x As Integer = 0 To DataGridView1.Rows.Count - 1
cmd.Parameters("@SRNumber").Value = TextBox1.Text
cmd.Parameters("@ItemCode").Value = (CStr(DataGridView1.Rows(x).Cells("ItemCode").Value.ToString))
cmd.Parameters("@Description").Value = (CStr(DataGridView1.Rows(x).Cells("Description").Value.ToString))
Try
cmd.Parameters("@RequestedQty").Value = (FormatNumber(CStr(DataGridView1.Rows(x).Cells("OrderedQty").Value.ToString), TriState.True, TriState.True))

Catch
cmd.Parameters("@RequestedQty").Value = "0.00"
End Try
cmd.Parameters("@Remarks").Value = (CStr(DataGridView1.Rows(x).Cells("Remarks").Value.ToString))
myconnect.Open()
cmd.ExecuteNonQuery()
myconnect.Close()

Next

Catch

End Try

End Sub


Now to remove the rows that has a
Exclude
data I put this code inside the
DGV1_Cellformating


Private Sub DGV1_Cellformating
Dim resultSet = From drow As DataGridViewRow In DataGridView1.Rows _
Where drow.Cells(4).Value.ToString.ToUpper = "EXCLUDE" _
Select drow.Index
For Each rowIndex In resultSet
DataGridView1.Rows.RemoveAt(rowIndex)
Next
End Sub


Now here is my final Goal/Question
How can make remove all rows with
Exclude
data before saving it to database?

For example on the first image above that all row with
Include
data will be the only datas will be saved in database.

What happens in my code is all the data has been saved in database the
Include
and
Exclude


TYSM for future help

Answer

it's probable that you are removing rows at the wrong index.

Try this way:

For R As Integer = DataGridView1.RowCount - 1 To 0 Step -1
    If DataGridView1.Item(4, R).Value.ToString = "Exclude" Then
        DataGridView1.Rows.RemoveAt(R)
    End If
Next