Click here to Skip to main content
15,912,507 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi, I have a inline query SELECT statement written in VB. When I try to run it, it prompt me an error message IndexOutOfRangeException. I tried to debug it and found the error prompt after ExecuteReader().
At first I thought it was due to my returned value is NULL but the error still persist after I have added IsDBNull in my codes.
Below are my codes:

VB.NET
Try
    cnSql = New SqlConnection(SQL_CONNECTION_STRING)
    cnSql.Open()

    strSQL = "SELECT ISNULL(MIN(SH.shipping_start_date), GETDATE()) AS StartDate "
    strSQL = strSQL + "FROM Shipping AS SH INNER JOIN Working AS WK ON SH.shipping_id = WK.shipping_id "
    strSQL = strSQL + "WHERE WK.woNo = '" + number + "'"

    cmSql = New SqlCommand(strSQL, cnSql)
    readerSQL = cmSql.ExecuteReader()

    If readerSQL.Read() Then
        If Not IsDBNull(readerSQL.Item("shipping_start_date")) Then
            woStartDate = readerSQL.Item("shipping_start_date")
        End If
    Else
        ShipStartDate = DateTime.Now
    End If


    readerSQL.Close()
    cnSql.Close()
    cmSql.Dispose()
    cnSql.Dispose()

Catch ex As SqlException
    MsgBox(ex.Message, MsgBoxStyle.Critical, "SQL Error!")

Catch ex As Exception
    MsgBox(ex.Message, MsgBoxStyle.Critical, "General Error!")'error happen here


What I have tried:

1. Added IsDBNull in my codes to filter out NULL value returned from DB.
2. Tried to excute the query directly in SQL Server without any error.
Posted
Updated 8-Sep-16 16:39pm
v2
Comments
ChauhanAjay 8-Sep-16 22:43pm    
Change the code from
If Not IsDBNull(readerSQL.Item("shipping_start_date")) Then
woStartDate = readerSQL.Item("shipping_start_date")
End If
to

If Not IsDBNull(readerSQL.Item("StartDate")) Then
woStartDate = readerSQL.Item("StartDate")
End If
Jamie888 8-Sep-16 22:46pm    
Yes sir, thank you for your advice, it is indeed tha "shipping_start_date" that caused the error. Thank you.
ChauhanAjay 8-Sep-16 23:48pm    
You are welcome

1 solution

I am not SQL expert, but it looks like the returned column is named StartDate and you check shipping_start_date in your code.
 
Share this answer
 
Comments
Jamie888 8-Sep-16 22:45pm    
Yes sir! Absolutely! How can I missed that, thank you for your advice, the issue is solved now. Thank you.
Patrice T 8-Sep-16 22:47pm    
You welcome.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900