Solved

looping to a folder list

Posted on 2011-03-01
14
275 Views
Last Modified: 2012-05-11
I have a folder that list the name of GarageID for eg.
In c:\Process, the files in it are GarageID's
6666.xls
6667.xls
6668.xls
so it is an array..

I d lke to use a Sql statement
that something like  "Select xxx,sss, vvv from table where GarageId = 'filename' in that foder'
How is that possible?
Wjat code will implement reading that array(loop) adn constructing the statement? Thanks
0
Comment
Question by:zachvaldez
[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
  • 6
  • 3
  • 2
  • +2
14 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 35014509
Are you looking for a t-SQL solution for this? i.e. Reading the file names from a folder on a drive?
0
 

Author Comment

by:zachvaldez
ID: 35014535
vb.net because it has to be done inline code... any ideas if stored proc can provide the logic/
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35014733
I asked you because you mentioned that you want to query using a SQL statement. If you are looking for SQL statement, let me know. will try.
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 37

Expert Comment

by:ValentinoV
ID: 35014861
If you're looking at looping through files while applying a filter in VB.NET, have a look at the EnumerateFiles method in the System.IO.Disrectory class: http://msdn.microsoft.com/en-us/library/dd413233.aspx

It allows you to return a collection of file names while applying a search pattern.

0
 
LVL 18

Expert Comment

by:carlnorrbom
ID: 35016905
Hi,

Don't get it... Are you looking to pick up each file in the array and do something with it or what? How is a sql statement relevant when files are stored in a folder? To iterate over the files in the folder you simply do something like:
        Dim dir As DirectoryInfo = New DirectoryInfo("C:\Process")
        For Each f As FileInfo In dir.GetFiles("*.xls")
            'Here you have a whole bunch of properties to play around with!
        Next

Open in new window


/Carl.
0
 

Author Comment

by:zachvaldez
ID: 35017236
Something like I want to store in a variable the filename as I loop
0
 
LVL 18

Accepted Solution

by:
carlnorrbom earned 175 total points
ID: 35017821
Hi,

Well to put the filename into a sql table you can do something like:
Imports System.IO
Imports System.Data.SqlClient

Partial Class Default14
    Inherits System.Web.UI.Page

    Private Sub DoFileStuff()

        Dim _conn As New SqlConnection(ConfigurationManager.ConnectionStrings("YourConnectionStringGoesHere").ConnectionString)
        Dim strInsertGarageID As String = "INSERT INTO tblGarageID (GarageID) VALUES (@GarageID)"
        Dim sqlInsertGarageID As New SqlCommand(strInsertGarageID, _conn)
        Dim dir As DirectoryInfo = New DirectoryInfo("C:\Process")
        If Not _conn.State = Data.ConnectionState.Open Then
            _conn.Open()
        End If
        Try
            For Each f As FileInfo In dir.GetFiles("*.xls")
                sqlInsertGarageID.Parameters.Clear()
                sqlInsertGarageID.Parameters.AddWithValue("@GarageID", f.Name.ToString()) 'Will put the filename into the sql table
                sqlInsertGarageID.ExecuteNonQuery()
            Next
        Catch ex As Exception
            'TODO: Implement error handling logic
        Finally
            If Not _conn.State = Data.ConnectionState.Closed Then
                _conn.Close()
            End If
        End Try


    End Sub
End Class

Open in new window


And then construct your sql statements something like:
SELECT Table.xxx, Table.sss, Table.vvv FROM Table INNER JOIN tblGarageID ON Table.GarageId=tblGarageID.GarageID ORDER BY tblGarageID.GarageID

Open in new window


/Carl.
0
 
LVL 83

Assisted Solution

by:CodeCruiser
CodeCruiser earned 175 total points
ID: 35021069
Are you looking for something like below?



Dim Root As New DirectoryInfo("C:\")
Dim Files As FileInfo() = Root.GetFiles("*.*")

Dim dbadp As New SqlDataAdapter("", "connectionstring")
For each File in Files
   dbadp.SelectCommand.CommandText = "Select * From Table Where FileName='" & File & "'"
   dbadp.Fill(datatable)
   ...
Next
dbadp.Dispose

Open in new window

0
 

Author Comment

by:zachvaldez
ID: 35023987
In the loop,I am thinking to run a stored proc by passing the file name jn the where clause
Something like";select email from table where garageid is the filename
0
 
LVL 83

Assisted Solution

by:CodeCruiser
CodeCruiser earned 175 total points
ID: 35035362
Then try this



Dim Root As New DirectoryInfo("C:\")
Dim Files As FileInfo() = Root.GetFiles("*.*")

Dim dbadp As New SqlDataAdapter("", "connectionstring")
dbadp.SelectCommand.CommandType = CommandType.StoredProcedure
dbadp.SelectCommand.CommandText = "stored procedure name"
For each File in Files
   dbadp.SelectCommand.Parameters.AddWithValue("@filename", File)
   dbadp.Fill(datatable)
   dbadp.Parameters.Clear()
   ...
Next
dbadp.Dispose

Open in new window

0
 

Author Comment

by:zachvaldez
ID: 35039063
I am getting
'Parameters is not a member of  System.Data.SQLClient.SQLDataAdapter

in line

 dbadp.Parameters.Clear()

Here's what Im trying to do.

Inside the For Each loop
I pass the filename without the extenxion.That will be the ID that I pass in the stored proc.
My SP statement is something like..Select email from xtablle where GarageId = '@fileId'
What ever is returned I stored it in variable
sMsgTo:(email)
I already got the email routine. ...
So every loop I get the email in a variable.

so does this proposition fit in the loop?
0
 
LVL 18

Assisted Solution

by:carlnorrbom
carlnorrbom earned 175 total points
ID: 35039504
Hi,

You need to change it to:
dbadp.SelectParameters.Clear()

Open in new window


/Carl.
0
 

Author Comment

by:zachvaldez
ID: 35040070
For each File in Files
   dbadp.SelectCommand.Parameters.AddWithValue("@filename", File)
   dbadp.Fill(datatable)
  dbadp.SelectParameters.Clear()
Next

where will the return value stored here in the loop
0
 

Author Closing Comment

by:zachvaldez
ID: 35083219
Well  done!
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

717 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