Searching for a file name

[vmaruv]

hi,

how is it possible to parse a file name. Ok..to be more specific abt my requirement :

In my VB code, I am connecting to a SQL Server and then setting a database name to a variable. I have hardcoded for now but the name of the SQL Server and the Database will be a part of the file name at a particular path.

For eg., at the location c:\data\bin I have a file like
"pom_sqlserver_sql_database" where sqlserver is the name of the SQL server and sql_database is the name of the database. the word pom is standard and will be followed by the name of the server and the database. These can change and I will have to get them by searching for the word pom at the location.

I am really not sure how to proceed with this.

Please help !

Vidya.

 

[HarleyQuinn]

Hi,

Try something like:

Private Sub Command2_Click()
Dim strNames As String
Dim Position As Integer
Dim MyServer As String
Dim MyDatabase As String

strNames = Right(Text3.Text, Len(Text3.Text) - 4)

Position = InStr(strNames, "_")
MyServer = Left(strNames, Position - 1)
MyDatabase = Right(strNames, Len(strNames) - Position)
MsgBox MyServer & " ," & MyDatabase

End Sub

Hope this helps

Harleyquinn

---------------------------------
For tsunami relief donations

 

[vmaruv]

hi Harleyquinn
I'm not very clear with the above code.

I just know the path where the file is (C:\data\bin). I also know that the file name starts with the word "pom". I will have to retrieve the entire file name based on this and then search for the database and server names in it.

How is that possible ? I'm lost..Please suggest..

Thanks in Advance.
Vidya.

 

[HarleyQuinn]

Hi,

Add a reference to Microsoft Scripting Runtime and a command button to a form and try this:

Private Sub Command1_Click()
Dim strNames As String
Dim Position As Integer
Dim MyServer As String
Dim MyDatabase As String
Dim fso As New FileSystemObject
Dim f As File

For Each f In fso.GetFolder("C:\data\bin\").Files
    If Left(f.Name, 4) = "pom_" Then
            strNames = Right(f.Name, Len(f.Name) - 4)
            strNames = Left(strNames, Len(strNames) - 4) [green]'use this if the file has an extention i.e .txt[/green]
            Exit For
    End If
Next f
    
Set fso = Nothing
Set f = Nothing

Position = InStr(strNames, "_")
MyServer = Left(strNames, Position - 1)
MyDatabase = Right(strNames, Len(strNames) - Position)
MsgBox MyServer & " ," & MyDatabase
End Sub

That should do what you want. If you put a breakpoint at the start and then one at the first 'strNames = ' and step through the code it will allow you to look at the variables values and help you understand the code. If you need any help just post your question back.

Hope this helps

Harleyquinn

---------------------------------
For tsunami relief donations

 

[vmaruv]

Hi Harleyquinn

THANKS a Ton for your code.
Actually, I found out another way in which I can get the server and the database name in my application. We have a batch file called profilevar.bat that defines these fields.

set SQL_SERVER=hyeri30
set SQL_DATABASE=db1_sql

I discovered that the file which I had mentioned with the name pom_... is picking up the data from this file.

Can you please help me to get hold of these fileds ? I will have to read this file and locate the server and database name.

Hope my questions are not irritating you. THis is the first time I'm doing something like this.

Thanks for your help in advance.
Vidya.

 

[HarleyQuinn]

Just out of interest, why are you wanting to look at the batch file when you can get the information you require from the above code?

Harleyquinn

---------------------------------
For tsunami relief donations

 

[vmaruv]

Hi,

I was just worried that the server name could have a '_' in it and my code would fail as I am considering '_' as a delimiter.
I came across databases names with a '_' (eg., sql_eng) and so thought if proper convensions are not being followed for naming the servers or databases then my code will be in trouble. So Thought the best thing is to read it as it is from the batch file.

Please suggest the best thing to do.

Thanks,
Vidya.

 

[HarleyQuinn]

Try:

Dim strNames As String
Dim MyServer As String
Dim MyDatabase As String
Dim fso As New FileSystemObject
Dim f As File
Dim strPath As String
Dim strInput As String

strPath = "C:\data\bin\"

For Each f In fso.GetFolder(strPath).Files
    If Left(f.Name, 4) = "pom_" Then
             strNames = f.Name
             Exit For
    End If
Next f
    
Set fso = Nothing
Set f = Nothing

Open strPath & strNames For Input As #1
Do Until MyServer <> "" And MyDatabase <> ""
Line Input #1, strInput
If Left(strInput, 15) = "set SQL_SERVER=" Then MyServer = Split(strInput, "=")(1)
If Left(strInput, 17) = "set SQL_DATABASE=" Then MyDatabase = Split(strInput, "=")(1)
Loop
Close #1

MsgBox MyServer & " ," & MyDatabase

I think that will work for you.

Harleyquinn

---------------------------------
For tsunami relief donations

 

[HarleyQuinn]

Oops, my bad. You can cut the code down to this:

Dim MyServer As String
Dim MyDatabase As String
Dim strPath As String
Dim strInput As String

strPath = "C:\data\bin\profilevar.bat"

Open strPath For Input As #1
Do Until MyServer <> "" And MyDatabase <> ""
Line Input #1, strInput
If Left(strInput, 15) = "set SQL_SERVER=" Then MyServer = Split(strInput, "=")(1)
If Left(strInput, 17) = "set SQL_DATABASE=" Then MyDatabase = Split(strInput, "=")(1)
Loop
Close #1

MsgBox MyServer & " ," & MyDatabase

Had not noticed that the name of the .bat file could be hardcoded in as it is always the same.

Harleyquinn

---------------------------------
For tsunami relief donations

 

[vmaruv]

Thank you very much Harleyquinn. The code is working perfectly.

I have 2 more hurdles resolving which this module will be complete.

1)how can i get the word before a particular letter using the split function ? for example I have a string like

set TNAM_DB_CONNECT=irfodba:tbwiabd@KcEng9122

In the above, irfodba is the user name and tbwiabd is the password. How can I seperate out tbwiabd from the string ?

I have done something like
If Left(strInput, 28) = "set IMAN_DB_CONNECT=infodba:" Then MyPassword = Split(strInput, ":")(1)

but it is returning KcEng9122.

2) My second hurdle is the password which is "tbaiabd" is encrypted in the batch file. how can i decrypt it and check with the user input to see if he has entered the same password or not ?

Please suggest.

Thanks again.
Vidya

 

[HarleyQuinn]

Hi kgarf,

OK, lets try and wrap this up for you:

1)

If Left(strInput, 28) = "set TNAM_DB_CONNECT=infodba:" Then MyPassword = Split(Split(strInput, ":")(1), "@")(0)

The first Split() (Split(strInput, ":")(1) returns everything to the right of the ":". The second Split() around it returns everything to the left (signified by the 0 at the end rather than the 1) of the "@" in the value returned by the first Split() function.

2) You will need to know how the file is encrypted. Then encrypt your user input and match it to the encrypted password.

Hope this helps

Harleyquinn

---------------------------------
For tsunami relief donations

 

[vmaruv]

I tried your code and Its returning a null for me.

I used the line with slight changes as below:

If Left(strInput, 20) = "set TNAM_DB_CONNECT=" Then MyPassword = Split(Split(strInput, ":")(1), "@")(0)

The line in the batch file is as below :

set TNAM_DB_CONNECT=irfodba:tbaiabd@BdEng9987

The word after : and before @ is MyPassword and I need to get it.

Thanks again

 

[HarleyQuinn]

Try putting

strInput = "set TNAM_DB_CONNECT=infodba:tbwiabd@KcEng9122"

and then running the If statement you now have. Tell me if it returns what you want.

Harleyquinn

---------------------------------
For tsunami relief donations

 

[HarleyQuinn]

Oh, and sorry for calling you kgarf in one of my previous posts. I have been working alot with him in another thread and gotten a bit confused as to who I was replying to...

Have you found what was the problem with your code that was getting it mto return NULL???

Harleyquinn

---------------------------------
For tsunami relief donations

 

[vmaruv]

Its ok for calling me kgarf. I understand.

It is still returning a null for me.

Not sure why that is happening.

 

[HarleyQuinn]

Put a command button on the form and paste in this exact code in the click event.

Dim strInput2  As String
Dim MyString As String

strInput2 = "set TNAM_DB_CONNECT=irfodba:tbaiabd@BdEng9987"

If Left(strInput2, 20) = "set TNAM_DB_CONNECT=" Then MyString = Split(Split(strInput2, ":")(1), "@")(0)

MsgBox MyString

The messagebox will/should return the password you are looking for in that string.

Harleyquinn

---------------------------------
For tsunami relief donations

 

[vmaruv]

Yeah..its returning the password. THanks a lot Harleyquinn ! But how do I read strInput2 from the batch file ?

 

[HarleyQuinn]

If it's the same batch file as before use the code supplied:

Dim MyServer As String
Dim MyDatabase As String
Di MyPassword as String
Dim strPath As String
Dim strInput As String

strPath = "C:\data\bin\profilevar.bat"

Open strPath For Input As #1
Do Until MyServer <> "" And MyDatabase <> "" And MyPassword <> ""
Line Input #1, strInput
If Left(strInput, 15) = "set SQL_SERVER=" Then MyServer = Split(strInput, "=")(1)
If Left(strInput, 17) = "set SQL_DATABASE=" Then MyDatabase = Split(strInput, "=")(1)
[red]If Left(strInput, 20) = "set TNAM_DB_CONNECT=" Then MyPassword = Split(Split(strInput, ":")(1), "@")(0)[/red]
Loop
Close #1

MsgBox MyServer & " ," & MyDatabase & " ," & MyPassword

If that doesn't/isn't working are you sure that "set TNAM_DB_CONNECT=" is the first 20 chars that preceed the password as the code supplied works for me in testing?
Hope this helps

Harleyquinn

---------------------------------
For tsunami relief donations

 

[vmaruv]

I'm still getting a null. The thing is as I have mentioned earlier

The line in the batch file is :

set TNAM_DB_CONNECT=irfodba:tbaiabd@BdEng9987

The word after : and before @ is MyPassword and I need to get it.


thanks again...

 

[HarleyQuinn]

I know what you are trying to do, and as far as I can see (and have tested) the code supplied really does work. I have created a batch file and put the same line into it as you have specified and I return MyPassword as 'tbaiabd', as expected. I did notice earlier you posted the line "set IMAN_DB_CONNECT=". Could that be where it is going wrong?

Harleyquinn

---------------------------------
For tsunami relief donations