8 Replies - 1145 Views - Last Post: 28 July 2011 - 03:07 PM Rate Topic: -----

#1 sthomas   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 28-July 11

Lost with a Select statement vb.net

Posted 28 July 2011 - 08:42 AM

Hello,

I am new to this forum, and to programming so if there is an easy solution I apologize. I am working on a front-end application program and cannot figure out what is giving me this error. The program is accessing an Access Database and filling a text box on the form. The error i keep receiving is "Index out of range", "No row at position 0". I have information in the data table, and when I type the select statement into a query in Access, it returns the information in the data table.
This is the code that I have written:

Public Class frmInterchangeComments
    'Interchange Comments Report program for Inventory Interface
    'Connection to Access Database
    'Displays comments for loaded interchanges
    '
    '
    Dim tmpFullIntNumber As String
    Dim daIntComments As New OleDb.OleDbDataAdapter("SELECT [Int_Comments] FROM(MhkInterchanges)WHERE [Int_Part_Num]='" & tmpFullIntNumber & "';", mhkconnection)
    Dim dtIntComments As New DataTable
    Dim rpIntComments As Integer = 0

    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click

        Me.Close()

    End Sub

    Private Sub frmInterchangeComments_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        txtInterchangeCode.Text = tmpInterchangeCode
        txtInterchangeNumber.Text = tmpInterchangeNumber
        tmpFullIntNumber = tmpInterchangeCode & ":" & tmpInterchangeNumber

        LoadComments()

    End Sub

    Private Sub LoadComments()

        ConnectionOpen()
        daIntComments.Fill(dtIntComments)
        ConnectionClose()

        If IsDBNull(dtIntComments.Rows(rpIntComments)("Int_Comments")) Then
            txtInterchangeComments.Text = "No comments loaded"

        Else : txtInterchangeComments.Text = dtIntComments.Rows(rpIntComments)("Int_Comments")

        End If

    End Sub

End Class




The line "If IsDBNull(dtIntComments.Rows(rpIntComments)("Int_Comments")) Then" is what triggers the error. I am sure I am overlooking something but can't figure it out. Just looking for any advice on what I did wrong.

Thank you.

Is This A Good Question/Topic? 0
  • +

Replies To: Lost with a Select statement vb.net

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14408
  • View blog
  • Posts: 57,765
  • Joined: 12-June 08

Re: Lost with a Select statement vb.net

Posted 28 July 2011 - 08:59 AM

Are you sure rows are being returned?

Here's a great opportunity to learn about Debugging!

It's the process of putting a breakpoint in the execution of your code (in your case at the start of LoadComments), and stepping through each line to see the values of the variables and how your code is interacting versus assuming how it is!

"SELECT [Int_Comments] FROM dbo.MhkInterchanges WHERE [Int_Part_Num]='" & tmpFullIntNumber & "' "
Was This Post Helpful? 0
  • +
  • -

#3 CharlieMay   User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1729
  • View blog
  • Posts: 5,708
  • Joined: 25-September 09

Re: Lost with a Select statement vb.net

Posted 28 July 2011 - 09:18 AM

Where are you assigning anything to tmpFullintNumber?
You have
Dim tmpFullIntNumber As String
Dim daIntComments As New OleDb.OleDbDataAdapter("SELECT [Int_Comments] FROM(MhkInterchanges)WHERE [Int_Part_Num]='" & tmpFullIntNumber & "';", mhkconnection)


This would make tmpFullIntNumber = ""

So your query would be
SELECT [Int_Comments] From (MhkInterchanges) WHERE [Int_Part_Num]='';
Was This Post Helpful? 0
  • +
  • -

#4 sthomas   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 28-July 11

Re: Lost with a Select statement vb.net

Posted 28 July 2011 - 09:56 AM

Hello,
Thank you for the quick responses. I have inserted a break point, and followed the execution of the code; and the variables are filling with information. But I canít tell if the data table is being filled by the data adapter. The variable tmpFullIntNumber is filled by a sub from the main form. The code for that is below:

    Private Sub lstInterchanges_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstInterchanges.SelectedIndexChanged

        Dim intcount As Integer


        intcount = Len(lstInterchanges.SelectedItem)

        If intcount < 8 Then
            Exit Sub

        Else
            tmpInterchangeCode = Mid(lstInterchanges.SelectedItem, 1, 3)
            tmpInterchangeNumber = Mid(lstInterchanges.SelectedItem, 7, intcount)

        End If

        frmInterchangeComments.ShowDialog()

    End Sub



what is more confusing is that if a change the select statement to "Select * from mhkIntComments" and perform a sequential search it will return the information. the problem is that the database has 30,000 plus records.
Was This Post Helpful? 0
  • +
  • -

#5 CharlieMay   User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1729
  • View blog
  • Posts: 5,708
  • Joined: 25-September 09

Re: Lost with a Select statement vb.net

Posted 28 July 2011 - 10:09 AM

I'm still not sure it would get that information before populating the adapter right below it. Maybe move the adapter line into the form_load.

I'm going to try something on my end and I'll let you know what I find.

Another thing you can do is in your form_load put something like:
MessageBox.Show(dtIntComments.Rows.Count.Tostring)


and see if it comes up with >0 rows.

This post has been edited by CharlieMay: 28 July 2011 - 10:10 AM

Was This Post Helpful? 0
  • +
  • -

#6 CharlieMay   User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1729
  • View blog
  • Posts: 5,708
  • Joined: 25-September 09

Re: Lost with a Select statement vb.net

Posted 28 July 2011 - 10:15 AM

OK:

Move this
Dim daIntComments As New OleDb.OleDbDataAdapter("SELECT [Int_Comments] FROM(MhkInterchanges)WHERE [Int_Part_Num]='" & tmpFullIntNumber & "';", mhkconnection)


To just inside your LoadComments sub.
Was This Post Helpful? 1
  • +
  • -

#7 sthomas   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 28-July 11

Re: Lost with a Select statement vb.net

Posted 28 July 2011 - 12:15 PM

View PostCharlieMay, on 28 July 2011 - 10:15 AM, said:

OK:

Move this
Dim daIntComments As New OleDb.OleDbDataAdapter("SELECT [Int_Comments] FROM(MhkInterchanges)WHERE [Int_Part_Num]='" & tmpFullIntNumber & "';", mhkconnection)


To just inside your LoadComments sub.


I moved the statment to the load sub and it worked. Thank you for your help on this, i couldn't figure out what the problem was.
Was This Post Helpful? 0
  • +
  • -

#8 CharlieMay   User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1729
  • View blog
  • Posts: 5,708
  • Joined: 25-September 09

Re: Lost with a Select statement vb.net

Posted 28 July 2011 - 12:54 PM

Do you understand why that works?

Also, if you need to use daIntComments anywhere else then you will want to declare it at the class level so that it is available throughout BUT... in the form_load assign it the statement.

Example
Public Class Form1

Dim daIntComments as Oledb.OleDbDataAdapter


Private Sub Form_Load (........)
daIntComments = New Oledb.OleDbDataAdapter("SELECT [Int_Comments] FROM(MhkInterchanges)WHERE [Int_Part_Num]='" & tmpFullIntNumber & "';", mhkconnection)
...



This way you can still call the adapter throughout the form.

This post has been edited by CharlieMay: 28 July 2011 - 12:55 PM

Was This Post Helpful? 1
  • +
  • -

#9 sthomas   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 28-July 11

Re: Lost with a Select statement vb.net

Posted 28 July 2011 - 03:07 PM

View PostCharlieMay, on 28 July 2011 - 12:54 PM, said:

Do you understand why that works?

Also, if you need to use daIntComments anywhere else then you will want to declare it at the class level so that it is available throughout BUT... in the form_load assign it the statement.

Example
Public Class Form1

Dim daIntComments as Oledb.OleDbDataAdapter


Private Sub Form_Load (........)
daIntComments = New Oledb.OleDbDataAdapter("SELECT [Int_Comments] FROM(MhkInterchanges)WHERE [Int_Part_Num]='" & tmpFullIntNumber & "';", mhkconnection)
...



This way you can still call the adapter throughout the form.


I did make that change in the decloration ssection. I was under the impression that when a form loads, it processes the declorations before the load sub. I had another form where the SQL statement was in the decloration section and that was working fine but i moved it to the sub that is calling to avoid any potential errors. I am still a little confused why it wouldn't work but I do know why it works now. This is just alot to take in when self learning a language in a short time frame.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1