CODEWITHSUNDEEP
sqldatabasevb.net
Paul
Paul

Reputation: 127

VB.Net SQL issues

I've been receiving errors with the following code below saying that the index is incorrect. I'm assuming this is an error with the SQL statement but I'm unsure what's wrong.

        Private Sub btnStock_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnStock.Click
    frmStock.Visible = True
    Dim SQLCmd As String
    SQLCmd = "SELECT PartID,PartName,PartStockLevel,MakeName,ModelName FROM tblParts WHERE PartStockLevel <= StockWarnLevel;"
    RunSQLCmd("dt", SQLCmd)
    Dim inc As Integer = 0
    Dim NoLowStock As Boolean
    If DataTable IsNot Nothing AndAlso DataTable.Rows.Count > 0 Then
        frmStock.txtPartID.Text = DataTable.Rows(inc).Item(0)
        frmStock.txtName.Text = DataTable.Rows(inc).Item(1)
        frmStock.NUDStockLvl.Value = DataTable.Rows(inc).Item(2)
        frmStock.txtMake.Text = DataTable.Rows(inc).Item(3)
        frmStock.txtModel.Text = DataTable.Rows(inc).Item(4)
    Else
        frmStock.lblLowStock.Visible = True
        frmStock.btnFirstRecord.Visible = False
        frmStock.btnIncDown.Visible = False
        frmStock.btnIncUp.Visible = False
        frmStock.btnLastRecord.Visible = False
        NoLowStock = True
    End If
    If NoLowStock = False Then
        frmStock.Panel1.Visible = False
    End If
End Sub

    Public Sub RunSQLCmd(ByVal DTorDS As String, ByRef SQLCmd As String)
    DataAdapter = New OleDb.OleDbDataAdapter(SQLCmd, con)
    ConnectDB()
    Try
        If DTorDS = "dt" Then
            DataTable = New DataTable
            DataAdapter.Fill(DataTable)
        Else
            DataSet = New DataSet
            DataAdapter.Fill(DataSet, "srcDataSet")
        End If
    Catch ex As Exception
        con.Close()
    End Try
    con.Close()
End Sub

Thanks in advance!

Upvotes: 0

Views: 125

Answers (2)

purplesoft
purplesoft

Reputation: 526

Try this simple way

dim dt as new datatable

using connection as new sqlconnection("server=SERVER;database=DATABASE;uid=USERID;pwd=PASSWORD")
connection.open()
     using cmd as new sqlcommand("SELECT PartID,PartName,PartStockLevel,MakeName,ModelName FROM tblParts WHERE PartStockLevel <= StockWarnLevel", connection)
     dt.load(cmd.executereader)
     end using
end using

Upvotes: 0

Brent Keller
Brent Keller

Reputation: 1435

The error probably comes from you looking at a row index that doesn't exist in the results. You should check that the table has rows before trying to get the data.

Something like this: If DataTable IsNot Nothing AndAlso DataTable.Rows.Count >0 Then ... Code here... End If

Upvotes: 1

Related Questions