Solved

vb.net - sql query populates array

Posted on 2010-08-17
14
421 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
 
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

760 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

20 Experts available now in Live!

Get 1:1 Help Now