Solved

vb.net - sql query populates array

Posted on 2010-08-17
14
441 Views
Last Modified: 2012-08-14
got this from someother EE link and modifying to fit my code...yet it throws errors..
' all my imports....
Imports System.Net
Imports System.Diagnostics
Imports System.HttpStyleUriParser
Imports System
Imports System.IO
Imports System.Windows.Forms
Imports System.Data.SqlClient
Imports System.Data.DataSet
Imports System.Data.SqlClient.SqlDataAdapter
'
' Grab existing filenames and trhough them into an array
                Dim SQLData As DataSet
                Dim sqlcommand As SqlDataAdapter
                Dim x As Integer = 0 'count of results
                Dim existingFileName As New List(Of String)
                Dim SQLServer As New SqlConnection
                Dim SQLString As String
                SQLServer.Open()
                SQLString = "SELECT FileNamePath FROM Files"
                SqlCommand = New SqlCommand(SQLString, SQLServer)
                SQLData = SqlCommand.ExecuteReader()
                Do While SQLData.Read()
                    existingFileName.Add(SQLData.GetValue(0).ToString())
                    x = x + 1
                Loop

                SQLData.Close()
                SQLServer.Close()


---

'Read' is not a memeber of System.data.dataset'

New SqlCommand(SQLString, SQLServer) --> issue here...
0
Comment
Question by:GlobaLevel
  • 9
  • 4
14 Comments
 
LVL 8

Expert Comment

by:PagodNaUtak
ID: 33461651
Dim SQLData As DataSet
This should be change to
Dim SQLDATA as SQLDataReader


0
 
LVL 8

Expert Comment

by:PagodNaUtak
ID: 33461655
Here is the complete code...
Dim SQLData As SQLDataReader
                Dim sqlcommand As SqlDataAdapter
                Dim x As Integer = 0 'count of results
                Dim existingFileName As New List(Of String)
                Dim SQLServer As New SqlConnection
                Dim SQLString As String
                SQLServer.Open()
                SQLString = "SELECT FileNamePath FROM Files"
                SqlCommand = New SqlCommand(SQLString, SQLServer)
                SQLData = SqlCommand.ExecuteReader()
                Do While SQLData.Read()
                    existingFileName.Add(SQLData.GetValue(0).ToString())
                    x = x + 1
                Loop

                SQLData.Close()
                SQLServer.Close()

Open in new window

0
 
LVL 8

Accepted Solution

by:
PagodNaUtak earned 250 total points
ID: 33461672
You must also change
Dim sqlcommand As SqlCommand
Here is the latest revised code...

Dim SQLData As SQLDataReader
                Dim sqlcommand As SqlCommand
                Dim x As Integer = 0 'count of results
                Dim existingFileName As New List(Of String)
                Dim SQLServer As New SqlConnection
                Dim SQLString As String
                SQLServer.Open()
                SQLString = "SELECT FileNamePath FROM Files"
                SqlCommand1 = New SqlCommand(SQLString, SQLServer)
                SQLData = SqlCommand1.ExecuteReader()
                Do While SQLData.Read()
                    existingFileName.Add(SQLData.GetValue(0).ToString())
                    x = x + 1
                Loop

                SQLData.Close()
                SQLServer.Close()

Open in new window

0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 8

Expert Comment

by:PagodNaUtak
ID: 33461735
Here is the code that I revised, please use this instead...
 

 Dim x As Integer = 0
        Dim listOfExistingFileName As New List(Of String)
        Using sqlConn As New SqlConnection
            Dim SQLString As String = "SELECT FileNamePath FROM Files"
            Dim dataReader As SqlDataReader
            sqlConn.Open()
            Using command As New SqlCommand(SQLString, sqlConn)
                dataReader = command.ExecuteReader(CommandBehavior.CloseConnection)
                Do While dataReader.Read()
                    listOfExistingFileName.Add(dataReader.GetValue(0).ToString())
                Loop
                dataReader.Close()
                sqlConn.Close()
            End Using
        End Using

        'Retrieve the number of record.
        x = listOfExistingFileName.Count

Open in new window

0
 
LVL 53

Assisted Solution

by:Dhaest
Dhaest earned 250 total points
ID: 33461934
You also need to set the connectionstring to your database
Imports System.Net
Imports System.Diagnostics
Imports System.HttpStyleUriParser
Imports System
Imports System.IO
Imports System.Windows.Forms
Imports System.Data.SqlClient
Imports System.Data.DataSet
Imports System.Data.SqlClient.SqlDataAdapter
'
' Grab existing filenames and trhough them into an array
                Dim SQLData As DataSet
                Dim sqlcommand As SqlDataAdapter
                Dim x As Integer = 0 'count of results
                Dim existingFileName As New List(Of String)
                Dim SQLServer As New SqlConnection
                Dim SQLString As String
dim sqlConnection as string = "Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;"
SQLServer.ConnectionString = connectionString;

                SQLServer.Open()
                SQLString = "SELECT FileNamePath FROM Files"
                SqlCommand = New SqlCommand(SQLString, SQLServer)
                SQLData = SqlCommand.ExecuteReader()
                Do While SQLData.Read()
                    existingFileName.Add(SQLData.GetValue(0).ToString())
                    x = x + 1
                Loop

                SQLData.Close()
                SQLServer.Close()

Open in new window

0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 33471605
Once the sql is connected to  and the data is read and the array is populated..how would "listOfExistingFileName." be passed to another function as a paramter??

Dim x As Integer = 0
        Dim listOfExistingFileName As New List(Of String)
        Using sqlConn As New SqlConnection
            Dim SQLString As String = "SELECT FileNamePath FROM Files"
            Dim dataReader As SqlDataReader
            sqlConn.Open()
            Using command As New SqlCommand(SQLString, sqlConn)
                dataReader = command.ExecuteReader(CommandBehavior.CloseConnection)
                Do While dataReader.Read()
                    listOfExistingFileName.Add(dataReader.GetValue(0).ToString())
                Loop
                dataReader.Close()
                sqlConn.Close()
            End Using
        End Using

        'Retrieve the number of record.
        x = listOfExistingFileName.Count
0
 
LVL 8

Expert Comment

by:PagodNaUtak
ID: 33472038
Create a function like the one below:
then use it something like this:
Dim count as integer = 0
count = CountExistingFilenames(listOfExistingFileName)

Public Function CountExistingFilenames(ByVal listOfFilename As List(Of String)) As Integer
        Return listOfFilename.Count
    End Function

Open in new window

0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 33478618
How would I then go through and in the new function go thorugh each array?


Public Function CountExistingFilenames(ByVal listOfFilename As List(Of String)) As Integer
Dim count as integer = 1

for count = 1 to UBOUNDS(???)
 ShellCMD.Open + listOfFilename(count)

Next


        Return listOfFilename.Count
End Function

..correct??
0
 
LVL 8

Expert Comment

by:PagodNaUtak
ID: 33481253
Try the below code...

Public Sub OpenFiles(ByVal listOfFilename As List(Of String)) 



For each FilesToOpen In listOfFilename
ShellCMD.Open + FilesToOpen
Next

End Function

Open in new window

0
 
LVL 8

Expert Comment

by:PagodNaUtak
ID: 33481257
Try this one instead...

Public Sub OpenFiles(ByVal listOfFilename As List(Of String))   
  
For each FilesToOpen In listOfFilename  
ShellCMD.Open + FilesToOpen  
Next  
  
End Sub

Open in new window

0
 
LVL 8

Expert Comment

by:PagodNaUtak
ID: 33481263
Sorry here is the correct one...

Public Sub OpenFiles(ByVal listOfFilename As List(Of String))   
For each FilesToOpen as string In listOfFilename  
ShellCMD.Open + FilesToOpen  
Next  
  
End Function

Open in new window

0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 33503092
PagodNaUtak,

I've been gone all weekend, I will try...shortly...
0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 33503205
so this?


Public Sub OpenFiles(ByVal listOfFilename As List(Of String))  
  dim gatherShellResults(25) as string
  dim i as integer

   For each FilesToOpen as string In listOfFilename  
        ' open the shell and open the program..run each filename against...
        ShellCMD.Open + FilesToOpen  
        '  read os install results from DOS
        gatherShellResults(i) = Shell.Copy+Paste                                <<<<<<RIGHT???
        i = i + 1
   Next  
 
End Function
0
 
LVL 8

Expert Comment

by:PagodNaUtak
ID: 33507906
What do you want to achieve in your last code?
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

777 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