Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL go to records VB.NET

Posted on 2014-03-27
6
Medium Priority
?
197 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 2000 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

609 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