Solved

vb.net - sql query populates array

Posted on 2010-08-17
14
454 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
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

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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Write to a printer using vb.net 9 57
Help with preventing selection from a combobox 11 40
System.net.Sockets Error 5 38
VB.Net - BackgroundWorker running multiple 5 46
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…
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

679 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