• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • Last Modified:

HELP !!!! I have a table that consists of 6 fields....how can I create a string that will......

I have a table called: ENG_DB
It has fields called:  quote_id
                             quote_rev
                             quote_yr
                             quote_mon
                             qdock_num
                             qdock_rev

I'm trying to keep track of a quote number.

The quote number works like this:
QXXX.Z-yy.mm-xx.z

QXXX = the quote number which is ascending, changes with every different quote (in order) (ex. Q001 through Q020,etc.)
Z = is the quote revision (which is always associated with its quote number)  (ex. Q001.1,Q001.2, does not equal Q002.1)
yy = the year the project is quoted
mm = the month the project is quotes
xx = the number of the project (there could be 4 projects for 1 quote)(ex. Q001.0-05.04-01.0, Q001.0-05.04-02.0) <---this is two projects
z = the revision of the project (same rules as the other instances)
...The dashes and decimals are just for saving the file or creating the folders representation purposes only (need to be included but not in database)...I think  :)

I need a string that would combine all this into a usable representation of the file or folder and have the ability to create the folder when needed.  

I'm using a SQL database with VB6 or VB.NET

Thanks
B
0
brian_leighty
Asked:
brian_leighty
  • 4
  • 3
  • 3
2 Solutions
 
EasyWriterCommented:
Here is a string that will put the fields into a usuable format.  Is that what you are asking?

MyString = "Q" & Format(quote_id, "000") & "." & quote_rev & "-" & Format(quote_yr, "00") & "." & Format(quote_mon, "00") & "-" & Format(qdock_num, "00") & "." & qdock_rev
0
 
brian_leightyAuthor Commented:
ya but how would I write that string to a folder
b
0
 
brian_leightyAuthor Commented:
to reword that I mean create a folder with that strings info
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!

 
Julian HansenCommented:
You could use the CreateDirectory API - very simple

Public Declare Function CreateDirectory Lib "kernel32" Alias "CreateDirectoryA" (ByVal lpPathName As String, lpSecurityAttributes As SECURITY_ATTRIBUTES) As Long

CreateDirectory ( pathname )


BUT

There is a catch ....

For the path

c:\folder\subfolder1\subfolder2

You would need to test to see that the path c:\folder\subfolder1 exists before trying to create

c:\folder\subfolder1\subfolder2

This means you would have to loop through the string looking for the first '\' and then calling CreateDirectory (Left(path, posofbackslash ) ).
Then finding the location of the next '\' and calling CreateDirectory again
Repeat until the path is created

That should do it - if you need explicit code post back here
0
 
Julian HansenCommented:
You could also use the CreateFolder method of the FileSystemObject

set fso = CreateObject ("Scripting.FileSystemObject" )
fso.CreateFolder ( "C:\folder\subfolder1" )

But as with the CreateDirectory API call you would need to create the path progressively to ensure all parents of the resulting folder exist in the file system.
0
 
brian_leightyAuthor Commented:
how do I add the string to the createdirectory API :

Quote_num = "Q" & Format(quote_id, "000") & "." & quote_rev & "-" & Format(quote_yr, "00") & "." & Format(quote_mon, "00") & "-" & Format(qdock_num, "00") & "." & qdock_rev

Is this code using SQL commands

0
 
EasyWriterCommented:
What database are you writing SQL against?
0
 
EasyWriterCommented:
No this code is VB code.  It should work in both VB.NET as well as VB6.  

Do you want to create a directory for every entry in your database?   Or do you want to be able to run some SQL code to find, say, a particular qote, and create a directory for just that single quote?
0
 
EasyWriterCommented:
Here is what I suggest.  In Visual Basic, create a form and put a ListBox on the form that lists all of the quotes in your database.  This SQL query should will list them in the format you want:

SELECT 'Q' & Format(quote_id,'000') & '.' & quote_rev & '-' & Format(quote_yr,'00') & '.' & Format(quote_mon,'00') & '-' & Format(qdock_num,'00') & '.' & qdock_rev AS Expr1
FROM ENG_DB;

Then place a button on your form called Create Directory.  You will select the quote you want in the list, and then click the button to create the directory.

Do you need code to create the directory, and link the listbox to the database?
0
 
Julian HansenCommented:
>>  how do I add the string to the createdirectory API :

I am not sure what you are asking here ? Can you elaborate ?

The CreateDirectory API is a simple call

CreateDirectory ( pathvariable)

That's it

pathvariable needs to be a full path otherwise directory will be created in whatever the current working directory is.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now