Solved

SQL go to records VB.NET

Posted on 2014-03-27
6
162 Views
Last Modified: 2014-03-28
I am fairly new to vb.net so please bare with me.  I have recently moved from vb6 and vba so this is a new adventure.  The issue I am having is I have a form that connects to sql and pulls data.  I do not use a datasource connection, I manually put the information in.  I have the sql query filtered.  I have a left and a right arrow.  I want to be able to go to previous or next record based on the sql query.  I am achieving this now by using a populated listview and have the command buttons either go up one or down one.  I know this way works, but its not a good way.  I am looking for suggestions on this.  I posted the code  that I am using below.

Right button click
        Dim sqluname As String
        Dim sqlpass As String
        Dim sqlseverlocation As String
        Dim sqltable As String

        If (IO.File.Exists(txtconfigfile.Text)) Then

            'create a new xmltextreader object
            'this is the object that we will loop and will be used to read the xml file
            Dim document As XmlReader = New XmlTextReader(txtconfigfile.Text)

            'loop through the xml file
            While (document.Read())

                Dim type = document.NodeType

                'if node type was element
                If (type = XmlNodeType.Element) Then

                    'if the loop found a <SQLUserName> tag
                    If (document.Name = "SQLUserName") Then

                        sqluname = Decrypt(document.ReadInnerXml.ToString())

                    End If

                    'if the loop found a <SQLPassword> tag
                    If (document.Name = "SQLPassword") Then

                        sqlpass = Decrypt(document.ReadInnerXml.ToString())

                    End If

                    'if the loop found a <SQLServerLocation> tag
                    If (document.Name = "SQLServerLocation") Then

                        sqlseverlocation = Decrypt(document.ReadInnerXml.ToString())

                    End If

                    'if the loop found a <SQLTableName> tag
                    If (document.Name = "SQLTableName") Then

                        sqltable = Decrypt(document.ReadInnerXml.ToString())

                    End If

                End If

            End While
            document.Dispose()
        Else

            MsgBox("SQL Information is missing. Please put the SQL values in on the next page.  If you do not know this information please contact support!", vbExclamation, System.Reflection.Assembly.GetExecutingAssembly.GetName.Name())
            Exit Sub
        End If

        Dim fdname As String
        fdname = "FIRE AND RESCUE"
        Dim itemsub1 As String
        ListView1.MultiSelect = False
        Dim CurrentRow As Integer
        CurrentRow = ListView1.Items.IndexOf(ListView1.FocusedItem)
        CurrentRow = CurrentRow - 1
        ListView1.Items(CurrentRow).Selected = True
        ListView1.Items(CurrentRow).Focused = True
        itemsub1 = ListView1.SelectedItems.Item(0).Text
        'itemsub1 = txtCasenumber.Text + 1
        Dim connection As SqlConnection = New SqlConnection("Data Source='" + sqlseverlocation + "';Initial Catalog='" + sqltable + "';Persist Security Info=True;User ID='" + sqluname + "';Password='" + sqlpass + "'")
        Dim command As SqlCommand = New SqlCommand("Select TOP 1 Item, AGENCY, Unit, Location, [Activity Code], [Case #], Month, Day, Year, Received, Dispatched, InRoute, Arrived, ToHosp, ArrvHosp, Completed, Remarks, AddNum, SeneMileage, HospMileage, TotalMiles, Operator, [Enter Info], Address FROM COMMUNICATIONS WHERE AGENCY='" + fdname + "' AND Item='" + itemsub1 + "' ORDER BY Item;", connection)

        connection.Open()

        Dim reader As SqlDataReader = command.ExecuteReader()

        If reader.HasRows Then
            Do While reader.Read()
                txtCasenumber.Text = reader.GetInt32(0)
                txtUnit.Text = reader.GetString(2)
                txtCaseNum.Text = reader.GetString(5)
                txtStreet.Text = reader.GetString(3)
                txtMonth.Text = reader.GetString(6)
                txtDay.Text = reader.GetString(7)
                txtYear.Text = reader.GetString(8)
                txtreceived.Text = reader.GetDateTime(9)
                txtDispatched.Text = reader.GetDateTime(10)
                txtArrived.Text = reader.GetDateTime(12)
                txtToHosp.Text = reader.GetDateTime(13)
                txtToHosp.Text = reader.GetDateTime(13)
                txtArrivedHosp.Text = reader.GetDateTime(14)
                txtCompleted.Text = reader.GetDateTime(15)
                txtActivityCode.Text = reader.GetString(4)
                txtSceneMil.Text = reader.GetInt32(18)
                txtHospMil.Text = reader.GetInt32(19)
                txtTotalMil.Text = reader.GetInt32(20)
                txtoperator.Text = reader.GetString(21)
                txtDetails.Text = reader.GetString(16)
                txtAddress.Text = reader.GetString(23)
            Loop
        Else

        End If
        connection.Close()

Open in new window


Left button click
        'Get SQL Information from xml file
        Dim sqluname As String
        Dim sqlpass As String
        Dim sqlseverlocation As String
        Dim sqltable As String

        If (IO.File.Exists(txtconfigfile.Text)) Then

            'create a new xmltextreader object
            'this is the object that we will loop and will be used to read the xml file
            Dim document As XmlReader = New XmlTextReader(txtconfigfile.Text)

            'loop through the xml file
            While (document.Read())

                Dim type = document.NodeType

                'if node type was element
                If (type = XmlNodeType.Element) Then

                    'if the loop found a <SQLUserName> tag
                    If (document.Name = "SQLUserName") Then

                        sqluname = Decrypt(document.ReadInnerXml.ToString())

                    End If

                    'if the loop found a <SQLPassword> tag
                    If (document.Name = "SQLPassword") Then

                        sqlpass = Decrypt(document.ReadInnerXml.ToString())

                    End If

                    'if the loop found a <SQLServerLocation> tag
                    If (document.Name = "SQLServerLocation") Then

                        sqlseverlocation = Decrypt(document.ReadInnerXml.ToString())

                    End If

                    'if the loop found a <SQLTableName> tag
                    If (document.Name = "SQLTableName") Then

                        sqltable = Decrypt(document.ReadInnerXml.ToString())

                    End If

                End If

            End While
            document.Dispose()
        Else

            MsgBox("SQL Information is missing. Please put the SQL values in on the next page.  If you do not know this information please contact support!", vbExclamation, System.Reflection.Assembly.GetExecutingAssembly.GetName.Name())
            Exit Sub
        End If

        Dim fdname As String
        fdname = "FIRE AND RESCUE"
        Dim itemsub1 As String
        ListView1.MultiSelect = False
        Dim CurrentRow As Integer
        CurrentRow = ListView1.Items.IndexOf(ListView1.FocusedItem)
        CurrentRow = CurrentRow + 1
        ListView1.Items(CurrentRow).Selected = True
        ListView1.Items(CurrentRow).Focused = True
        itemsub1 = ListView1.SelectedItems.Item(0).Text
        'itemsub1 = txtCasenumber.Text - 1
        Dim connection As SqlConnection = New SqlConnection("Data Source='" + sqlseverlocation + "';Initial Catalog='" + sqltable + "';Persist Security Info=True;User ID='" + sqluname + "';Password='" + sqlpass + "'")
        Dim command As SqlCommand = New SqlCommand("Select Item, AGENCY, Unit, Location, [Activity Code], [Case #], Month, Day, Year, Received, Dispatched, InRoute, Arrived, ToHosp, ArrvHosp, Completed, Remarks, AddNum, SeneMileage, HospMileage, TotalMiles, Operator, [Enter Info], Address FROM COMMUNICATIONS WHERE AGENCY='" + fdname + "' AND Item='" + itemsub1 + "';", connection)

        connection.Open()

        Dim reader As SqlDataReader = command.ExecuteReader()

        If reader.HasRows Then
            Do While reader.Read()
                txtCasenumber.Text = reader.GetInt32(0)
                txtUnit.Text = reader.GetString(2)
                txtCaseNum.Text = reader.GetString(5)
                txtStreet.Text = reader.GetString(3)
                txtMonth.Text = reader.GetString(6)
                txtDay.Text = reader.GetString(7)
                txtYear.Text = reader.GetString(8)
                txtreceived.Text = reader.GetDateTime(9)
                txtDispatched.Text = reader.GetDateTime(10)
                txtArrived.Text = reader.GetDateTime(12)
                txtToHosp.Text = reader.GetDateTime(13)
                txtToHosp.Text = reader.GetDateTime(13)
                txtArrivedHosp.Text = reader.GetDateTime(14)
                txtCompleted.Text = reader.GetDateTime(15)
                txtActivityCode.Text = reader.GetString(4)
                txtSceneMil.Text = reader.GetInt32(18)
                txtHospMil.Text = reader.GetInt32(19)
                txtTotalMil.Text = reader.GetInt32(20)
                txtoperator.Text = reader.GetString(21)
                txtDetails.Text = reader.GetString(16)
                txtAddress.Text = reader.GetString(23)
            Loop
        Else

        End If
        connection.Close()

Open in new window


Thanks for any suggestions.  I want to note, I do not want to use datasource connection. And also it is written in VB.NET not C+.
0
Comment
Question by:russell12
  • 3
  • 3
6 Comments
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 500 total points
ID: 39959035
If you just want to move backwards and forwards through a single table then it is a lot more efficient to hold the data in a DataTable, rather than going back to the database each time you want to move to the next record.

This sample show pulling the data into a DataTable, attaching it to a BindingSource, binding a couple of textboxes to it and then using the BindingSources navigation controls to move between records:
Imports System.Data
Imports System.Data.SqlClient

Public Class Form1

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

        '// create database connection
        Dim cn As New SqlConnection("Server=(local);Initial Catalog=CarlDemo;Integrated Security=SSPI;")

        '// define query and populate datatable
        Dim adap As New SqlDataAdapter("SELECT * FROM TestTable", cn)
        adap.Fill(dt)

        '// configure binding source
        bindingSource.DataSource = dt
        bindingSource.RaiseListChangedEvents = True

        '// bind textboxes to columns in the datatable
        TextBox1.DataBindings.Add(New Binding("Text", bindingSource, "ItemID"))
        TextBox2.DataBindings.Add(New Binding("Text", bindingSource, "Description"))

    End Sub

    Private Sub LeftButton_Click(sender As Object, e As EventArgs) Handles LeftButton.Click
        '// move to previous record
        bindingSource.MovePrevious()
    End Sub

    Private Sub RightButton_Click(sender As Object, e As EventArgs) Handles RightButton.Click
        '// move to next record
        bindingSource.MoveNext()
    End Sub

    Dim bindingSource As New BindingSource()
    Dim dt As New DataTable

End Class

Open in new window

0
 
LVL 2

Author Comment

by:russell12
ID: 39961089
just to clarify i do not have to put the data binding on the form correct?  I am sorry I am new to vb.net, I just moved from vb6 and vba.  And also if the textbox field is updated, it will not auto update to the table right?
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39961186
Correct. The BindingSource in my sample just binds controls on the form to columns in the DataTable, and allows easy navigation through the rows. If you change the value in the textbox it will update the DataTable that the BindingSource is bound to, but it won't have any effect on the underlying database. Although you could flush the changes through to the database if you wanted to....it just won't happen automatically.

The data retrieval / database elements are completely separate.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 2

Author Comment

by:russell12
ID: 39961305
I am having an issue with this code.  I want to make sure I did it properly.  I added
        Dim dt As New DataSet
and I am going to post the code.  The error I am getting is on the first line no matter what I put on the first line.  The error is:
 An unhandled exception of type 'System.ArgumentException' occurred in System.Windows.Forms.dll

Additional information: Cannot bind to the property or column Unit on the DataSource.

The code is:
        Dim sqluname As String
        Dim sqlpass As String
        Dim sqlseverlocation As String
        Dim sqltable As String
        If (IO.File.Exists(txtconfigfile.Text)) Then

            'create a new xmltextreader object
            'this is the object that we will loop and will be used to read the xml file
            Dim document As XmlReader = New XmlTextReader(txtconfigfile.Text)

            'loop through the xml file
            While (document.Read())

                Dim type = document.NodeType

                'if node type was element
                If (type = XmlNodeType.Element) Then

                    'if the loop found a <SQLUserName> tag
                    If (document.Name = "SQLUserName") Then

                        sqluname = Decrypt(document.ReadInnerXml.ToString())

                    End If

                    'if the loop found a <SQLPassword> tag
                    If (document.Name = "SQLPassword") Then

                        sqlpass = Decrypt(document.ReadInnerXml.ToString())

                    End If

                    'if the loop found a <SQLServerLocation> tag
                    If (document.Name = "SQLServerLocation") Then

                        sqlseverlocation = Decrypt(document.ReadInnerXml.ToString())

                    End If

                    'if the loop found a <SQLTableName> tag
                    If (document.Name = "SQLTableName") Then

                        sqltable = Decrypt(document.ReadInnerXml.ToString())

                    End If

                End If

            End While
            document.Dispose()
        Else

        End If

        Dim fdname As String
        fdname = "FIRE AND RESCUE"

        '// create database connection
        Dim cn As New SqlConnection("Server='" + sqlseverlocation + "';Initial Catalog='" + sqltable + "';Persist Security Info=True;User ID='" + sqluname + "';Password='" + sqlpass + "'")

        '// define query and populate datatable
        Dim adap As New SqlDataAdapter("Select TOP 100 Item, AGENCY, Unit, Location, [Activity Code], [Case #], Month, Day, Year, Received, Dispatched, InRoute, Arrived, ToHosp, ArrvHosp, Completed, Remarks, AddNum, SeneMileage, HospMileage, TotalMiles, Operator, [Enter Info], Address FROM COMMUNICATIONS WHERE AGENCY='" + fdname + "' ORDER BY Item DESC;", cn)
        Dim dt As New DataSet

        adap.Fill(dt)

        '// configure binding source
        BindingSource.DataSource = dt
        BindingSource.RaiseListChangedEvents = True

        '// bind textboxes to columns in the datatable

        txtCasenumber.DataBindings.Add(New Binding("Text", BindingSource, "Item"))
        txtUnit.DataBindings.Add(New Binding("Text", BindingSource, "Unit"))
        txtCaseNum.DataBindings.Add(New Binding("Text", BindingSource, "[Case #]"))
        txtStreet.DataBindings.Add(New Binding("Text", BindingSource, "Location"))
        txtMonth.DataBindings.Add(New Binding("Text", BindingSource, "Month"))
        txtDay.DataBindings.Add(New Binding("Text", BindingSource, "Day"))
        txtYear.DataBindings.Add(New Binding("Text", BindingSource, "Year"))
        txtreceived.DataBindings.Add(New Binding("Text", BindingSource, "Received"))
        txtDispatched.DataBindings.Add(New Binding("Text", BindingSource, "Dispatched"))
        txtArrived.DataBindings.Add(New Binding("Text", BindingSource, "Arrived"))
        txtToHosp.DataBindings.Add(New Binding("Text", BindingSource, "ToHosp"))
        txtArrivedHosp.DataBindings.Add(New Binding("Text", BindingSource, "ArrvHosp"))
        txtCompleted.DataBindings.Add(New Binding("Text", BindingSource, "Completed"))
        txtActivityCode.DataBindings.Add(New Binding("Text", BindingSource, "[Activity Code]"))
        txtSceneMil.DataBindings.Add(New Binding("Text", BindingSource, "SeneMileage"))
        txtHospMil.DataBindings.Add(New Binding("Text", BindingSource, "HospMileage"))
        txtTotalMil.DataBindings.Add(New Binding("Text", BindingSource, "TotalMiles"))
        txtoperator.DataBindings.Add(New Binding("Text", BindingSource, "Operator"))
        txtDetails.DataBindings.Add(New Binding("Text", BindingSource, "Remarks"))
        txtAddress.DataBindings.Add(New Binding("Text", BindingSource, "Address"))

Open in new window

Now doing research on databindings, I seen where you needed to create the dataset and set the rows programically.  One example I found was:
Private Sub InitDataLayer()
    'Create table
    DataTable = New DataTable

    DataTable.Columns.Add("ID")
    DataTable.Columns("ID").DataType = GetType(Integer)
    DataTable.Columns("ID").AllowDBNull = False        

    DataTable.Columns.Add("Name")
    DataTable.Columns("Name").DataType = GetType(String)
    DataTable.Columns("Name").AllowDBNull = False

    'Create new rows
    Dim NewRow As DataRow = DataTable.NewRow
    NewRow.Item("ID") = 1
    NewRow.Item("Name") = "John"
    DataTable.Rows.Add(NewRow)

    NewRow = DataTable.NewRow
    NewRow.Item("ID") = 2
    NewRow.Item("Name") = "Steve"
    DataTable.Rows.Add(NewRow)

    'Bind controls
    Dim BS As New BindingSource
    BS.DataSource = DataTable

    TextBoxID.DataBindings.Add(New Binding("text", BS, "ID"))
    TextBoxName.DataBindings.Add(New Binding("text", BS, "Name"))

    BindingNavigator1.BindingSource = BS

Open in new window

I do not want you to think I am questioning you answer, I am just new to datasets and not to familiar with them.  I decided though that I would listen to experts and go the best route.  I am going to be going out of town this weekend so I will be back to test code on Sunday.  Thanks for you help in guiding me.
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39961328
You need to change:

    Dim dt As New DataSet

To:

    Dim dt As New DataTable

A DataSet is a container for one or more DataTable objects. The DataSet itself doesn't have columns, it only has tables. The exception you are getting is because you are trying to bind to a column of the DataSet, which isn't valid.

With regards to the second sample you posted. All they are doing is creating and populating an in-memory DataTable. You are doing the same thing, only you are pulling data from a database (via the SqlDataAdapter objects Fill() method).
0
 
LVL 2

Author Closing Comment

by:russell12
ID: 39962841
I wish I could grant you more points.  You were a great help and thank you for working with me on this!  I cant thank you enough!!  I actually learned something from this as you explained it to me instead of just giving me an answer.  Thanks again!!
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

The object model of .Net can be overwhelming at times – so overwhelming that quite trivial tasks often take hours of research. In this case, the task at hand was to populate the datagrid from SQL Server database in Visual Studio 2008 Windows applica…
In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now