Solved

vb.net - sql query populates array

Posted on 2010-08-17
14
465 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
[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
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

689 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