Solved

vb.net - sql query populates array

Posted on 2010-08-17
14
457 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
Technology Partners: 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

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

734 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