Move from one record to another in table using Vb.net 2008

[Dili1234]

I used this code to retrive from one record to another first tried to retrive the first record.................

varible decalaration

Imports System.Data
Imports System.Data.SqlClient

Public Class Form3
    '
    Dim connectionstring As String = Module1.connectionString
    Dim sqlconn As New SqlConnection(connectionstring)
    Public sqlDataset As New DataSet
    Dim da1 As OleDb.OleDbDataAdapter
    Dim myCommand1 As SqlCommand
    Public SQLdr As SqlDataReader
    Dim myConnection As SqlConnection
    Dim nxtmajor As String
    Dim sqlcmd As New SqlCommand
    'Dim dep As String
    Dim tbStudent As DataTable
    Public n As Integer
    Public i As Integer
    'Dim sname As String
    Public majorname As String
    Dim myCommand As SqlCommand


Private Sub Form3_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        myConnection = New SqlConnection(connectionstring)
        myConnection.Open()
        myCommand = New SqlCommand("SELECT * FROM Major ", myConnection)
        Dim da As SqlDataAdapter
        SQLdr = myCommand.ExecuteReader()

        da = New SqlDataAdapter(myCommand)
        da.Fill(sqlDataset, "Vb_table")


        myConnection.Close()

    End Sub



 Private Sub nav()
        TextBox1.Text = sqlDataset.Tables("Vb_table").Rows(i).Item(1)

        TextBox2.Text = sqlDataset.Tables("Vb_table").Rows(i).Item(2)
        TextBox3.Text = sqlDataset.Tables("Vb_table").Rows(i).Item(3)

    End Sub

Private Sub cmdFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdFirst.Click
        i = 0
        nav()

    End Sub

    I got the error pointing :TextBox1.Text = sqlDataset.Tables("Vb_table").Rows(i).Item(1)
    error:Object reference not set to an instance of an object.

[Reverend Jim]

The obvious guess would be either TextBox1 isn't defined or sqlDataset isn't defined. Put a breakpoint at the line where you get the error and try to examine both objects.

[Dili1234]

I tried but nothing happen..............
I got the same error

[Reverend Jim]

What do you mean by "nothing happened"? Were both objects defined or not?

[Dili1234]

both objects have defined as according to my knowledge I'm not fluent in vb.net 2008, if you don't mind could you check on the codings I have provided.................
It will be great help to me................
thank you

[Reverend Jim]

Try this instead

Imports System.Data
Imports System.Data.SqlClient

Public Class Form1

    Public connectionstring As String = "Server=.\SQLEXPRESS;Database=PUBS;Trusted_Connection=yes;"
    Public sqlDataset As New DataSet
    Public recnum As Integer

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

        Dim conn As New SqlConnection(connectionstring)
        Dim da As New SqlDataAdapter("SELECT * FROM Authors ", conn)

        conn.Open()
        da.Fill(sqlDataset, "Vb_table")
        conn.Close()

        recnum = 0
        nav()

    End Sub

    Private Sub nav()

        If recnum < sqlDataset.Tables("Vb_table").Rows.Count Then
            TextBox1.Text = sqlDataset.Tables("Vb_table").Rows(recnum).Item(1)
            TextBox2.Text = sqlDataset.Tables("Vb_table").Rows(recnum).Item(2)
            TextBox3.Text = sqlDataset.Tables("Vb_table").Rows(recnum).Item(3)
        Else
            MsgBox("end of table")
        End If

    End Sub

    Private Sub btnNext_Click(sender As System.Object, e As System.EventArgs) Handles btnNext.Click
        recnum += 1
        nav()
    End Sub

End Class

[Dili1234]

I got the same error............................

[Reverend Jim]

I tried the code that I posted against the PUBS database (one of the sample databases you can download from Microsoft for SQL Server). It worked just fine. If you zip your entire project and post it here I'll load it up and give it a look.

[Dili1234]

Okay I'll attach the project which I'm doing all my trial work...........

Thank you :In my computer is not allowed to attach I have attach all the tables

[Reverend Jim]

I can't get your application to run. I don't have the time to go through it line by line to figure out what it is supposed to do. You haven't commented the code and you haven't named any of your controls to indicate what their function is.

[Dili1234]

Sorry for the Inconvenience,I did new project hope this can be easy understand.......
Vb_Exam is the database name I did this using sql 2005

[Reverend Jim]

That's much more manageable. I can have a look at it this afternoon. A couple of comments quickly

1. It's never a good idea to declare variables like "n" at the class level. Variable names like n, i, j, etc are typically used for short duration such as for loop indices and should not have scope beyond that. Things should have the smallest scope possible. Never declare at the class level purely for convenience.

2. You declare myConnection at the class level and instantiate it in the form load event. But then you create another myConnection in the Save sub. This is not necessary. The old one is still hanging around. Use it. In this case it makes sense to declare your connection at the class level. However, when using database connections the general rule is to open the connection at the last possible moment and close it as quickly as possible.

3. You declare both myConnection and sqlconn at the Class level as SQLConnection. Why do you need two connection objects?

I'll look in more detail after I walk the dog and have some lunch.

[Dili1234]

thank you for your advice.......

[Reverend Jim]

Still looking when I can. It's been a busy day. A few more quick comments. Check your naming. You have a database field named Suject_Name instead of Subject_Name. You also have two controls names txttname instead of txtName, and txtdepartement instead of txtDepartment. So for now, I've changed part of your code to

Public SQLdr As SqlDataReader
Public SQLds As New DataSet
Public SQLda As SqlDataAdapter

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

    myConnection = New SqlConnection(connectionstring)
    SQLda = New SqlDataAdapter("SELECT * FROM Major ", myConnection)

    myConnection.Open()
    SQLda.Fill(SQLds, "VB_Table")
    myConnection.Close()

    recnum = 0
    nav()

End Sub

Private Sub nav()

    If recnum < SQLds.Tables("Major").Rows.Count Then
        txtmajor.Text = SQLds.Tables("Major").Rows(recnum).Item(1)
        txtdepartment.Text = SQLds.Tables("Major").Rows(recnum).Item(2)
        txtname.Text = SQLds.Tables("Major").Rows(recnum).Item(3)
    Else
        MsgBox("end of table")
    End If

End Sub

I recreated your table structure in my local SQL Server but I have no data to work with so make the changes at your end and try it out.

[Dili1234]

This the error I got....................

 Private Sub nav()
        myConnection = New SqlConnection(connectionstring)

        myConnection.Open()
        Dim da As New SqlDataAdapter("SELECT * FROM Major ", myConnection)
        da.Fill(sqlDataset, "Vb_Exam")
        myConnection.Close()
        If recnum < sqlDataset.Tables("Major").Rows.Count Then
            txtmajor.Text = sqlDataset.Tables("Major").Rows(recnum).Item(1)
            txtdepartement.Text = sqlDataset.Tables("Major").Rows(recnum).Item(2)
            txttname.Text = sqlDataset.Tables("Major").Rows(recnum).Item(3)
        Else
            MsgBox("end of table")
        End If
    End Sub

I got to change the coding if I include this part into form load:


 Dim da As New SqlDataAdapter("SELECT * FROM Major ", myConnection)
            da.Fill(sqlDataset, "Vb_Exam")

the add button won't work
it is pointing 8th line........

[Reverend Jim]

My computer just went into the shop and I won't have it back for 8-10 days so I can't try anything at my end until then. All I have now is an old Thinkpad with a browser nd no dev tools. Also a fulty "A" key.

[Dili1234]

Solve my problems when you get your computer.................