Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 207
  • Last Modified:

SQL go to records VB.NET

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
russell12
Asked:
russell12
  • 3
  • 3
1 Solution
 
Carl TawnSystems and Integration DeveloperCommented:
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
 
russell12Author Commented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
russell12Author Commented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
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
 
russell12Author Commented:
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

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now