VB.Net Exception how to get the row and column of error in query

Question

I have excel files that i want to match into one column of my database

And if the data in excel file is not match in data in database it will popup an exception error it says

"there is no row at position 0"

and i want to change this to specific row and column?

Here's my Whole Code

Imports MySql.Data
Imports MySql.Data.MySqlClient
Module ConnectionV2
    Public connString As String = "Server=localhost;Database=lcs_db;Uid=root;Pwd=@v3p@$$w0rd;"
    Public conn As MySqlConnection = New MySqlConnection(connString)

    Public Function ExecuteSQLQuery(ByVal sql As String) As DataTable
        Dim sqlDT As New DataTable
        Try
            Dim sqlCon As New MySqlConnection(connString)
            Dim sqlDA As New MySqlDataAdapter(sql, connString)
            Dim sqlCB As New MySqlCommandBuilder(sqlDA)
            sqlDA.Fill(sqlDT)
        Catch ex As Exception
            'MsgBox("Error: " & ex.Message)
        End Try
        Return sqlDT
    End Function

    Public Function DT_Foreign_Key(ByVal select_column_name As String, ByVal table_name As String, ByVal where_column_name As String, ByVal where_value As String) As String
        Dim dt_service_provider_id As DataTable = ExecuteSQLQuery("select " & select_column_name & " from " & table_name & " where " & where_column_name & " = '" & where_value & "'")
        Return dt_service_provider_id.Rows(0).Item(0)
    End Function
End Module


 Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Try
            Dim filePath As String = UploadTextBox.Text
            If Not String.IsNullOrEmpty(filePath) Then
                If File.Exists(UploadTextBox.Text) Then
                    If Path.GetExtension(filePath) = ".xls" OrElse Path.GetExtension(filePath) = ".xlsx" Then
                        Try
                            If MessageBox.Show("Batch uploading process will delete all existing data. Are you sure you want to continue?", "Reminder", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then

                                Dim stream = File.OpenRead(filePath)
                                Dim xdr As IExcelDataReader
                                If Path.GetExtension(filePath).EndsWith("xls") Then
                                    xdr = ExcelReaderFactory.CreateBinaryReader(stream)
                                Else
                                    xdr = ExcelReaderFactory.CreateOpenXmlReader(stream)
                                End If
                                stream.Close()
                                stream.Dispose()
                                xdr.IsFirstRowAsColumnNames = True
                                xls = xdr.AsDataSet()
                                xdr.Close()
                                xdr.Dispose()
                                Dim dt_count As Integer = 0
                                For Each dt As DataTable In xls.Tables
                                    If Not dt Is Nothing Then
                                        'MessageBox.Show(dt.TableName)
                                        For Each dr As DataRow In dt.Rows
                                            If (dt.TableName = "Service Provider Types") Then
                                                 Dim site_id As String = DT_Foreign_Key("site_id", "sites", "site_code", dr(2).ToString) 'Actually Im intentionally put error because i want to show the exception that i want

                                            End If
                                        Next
                                    End If
                                Next
                            End If
                        Catch ex As IOException
                            MessageBox.Show(ex.Message & " Please make sure that the file is not curretly open.", "Upload file", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                        End Try
                    End If
                Else
                    MessageBox.Show("File does not exist.", "Upload file", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                End If
            Else
                MessageBox.Show("Browse for the file to upload.", "Upload file", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Upload file", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        End Try
    End Sub

@steve Updated now ahm sir i put a not in "Dim site_id" please read so you can really understand what i mean — Denmark
– Denmark, Commented Mar 14, 2014 at 10:13

WozzeC · Accepted Answer · 2014-03-14 12:06:31Z

1

In function DT_ForeignKey you try to return this:

Return dt_service_provider_id.Rows(0).Item(0)

If your query result brings back nothing, this will fail and tell you that there is no data on row 0. This since you try to select Rows(0) when you return the data.

What I would do is that I would set the DT_ForeignKey function to be boolean. And then return the result using a ByRef parameter.

Public Function DT_Foreign_Key(ByVal select_column_name As String, ByVal table_name As String, ByVal where_column_name As String, ByVal where_value As String, ByRef Foreign_Key As String) As Boolean
    Dim dt_service_provider_id As DataTable = ExecuteSQLQuery("select " & select_column_name & " from " & table_name & " where " & where_column_name & " = '" & where_value & "'")
    If dt_service_provider_id.Rows.Count = 0 Then
        Return False
    Else
        Foreign_Key = dt_service_provider_id.Rows(0).Item(0).ToString()
        Return True
    End If
End Function

This way you will be able to do this, and site_id will hold the expected id for you to use:

Dim site_id As String
If DT_Foreign_Key("site_id", "sites", "site_code", dr(2).ToString, site_id) = False Then
   Throw New Exception("Error was found on row: " & dt_count);
End If

This might be a little bit off at some places. But I hope you get the general idea.

answered Mar 14, 2014 at 12:06

WozzeC

2,6601 gold badge16 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Denmark Over a year ago

it just return the number of sheet in excel but it didn't return the row that the query got error

WozzeC Over a year ago

Dt count you have to increase yourself in the row foreach loop.

Denmark Over a year ago

do you know sir @wozzeC how can i check if the value in excel that pass is null or empty?

WozzeC Over a year ago

I believe "If value Is Nothing Then" is what you are looking for. But that is an entirely different question and this will be my last comment on it.

Collectives™ on Stack Overflow

VB.Net Exception how to get the row and column of error in query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related