help writing a multiple table query

[markrawlingson]

Hello, mbatestblrock. I am one of the ASP Experts Mary spoke of above.

I have read through this entire thread, and perhaps I've missed something here because that was quite a bit to read; so if i have missed something please excuse my ignorance. The first question I have for you is:

Why are you transferring the data from the text files to access? You are effectively transferring one data store to another.

If there is no "technical" reason why you're moving the data from the text fles to access, I would use ASP's FileSystemObjec t to simply access the data directly from the text files without using access. Using the FSO you can read the name of the file EX: store123.txt - and output this on the page. This would be very effective in solving the problem of not being able to get the store name - if it is in the filename of the file - asp can grab this for you and display it. You could also use it to read the contents of the text file line by line, checking to see what the status of the store is (backup or what have you) - and output the storename and status of the store.

For instance...

Store1: ALERT! USING BACKUP!
Store2: Nominal
Store3: Nominal
Store4: Nominal
Store5: Nominal
Store6: Nominal

Let me know first if there is anything stopping you from doing this, and if there isn't, i will write a script for you to accomplish this task. Just be sure to send me an example of one of these text files over PM if you can, so I know what I am working with.

Secondly, if you wish to use the function mary created instead, below is the ASP version of it.

[CODE=ASP]
Function appendToTable(t blName)
sSQL = "SELECT * FROM " & tblName
Set rs = Server.CreateOb ject("ADODB.Rec ordSet")
rs.Open sSQL,cnn,3,3 ' cnn is, of course, the connection to your database.
' this will append each record from the passed table name to the tmpTable
Do Until rs.EOF
' Each field name will have to be specified.
' For the purposes of examples I am just going to use 3 and show
' how to pass different value types. The first is a String, the second
' a date and the third a number.
cnn.execute("IN SERT INTO tmpTable (FieldName1, FieldName2, FieldName3) " & _
"VALUES ('" & rs("FieldName1" ) & "', #" & rs("FieldName1" ) & "#, " & rs("FieldName1" ) & ")")
rs.MoveNext
Loop

rs.Close
Set rs = Nothing

End Function
[/CODE]

[mbatestblrock]

Hello, mbatestblrock. I am one of the ASP Experts Mary spoke of above.

I have read through this entire thread, and perhaps I've missed something here because that was quite a bit to read; so if i have missed something please excuse my ignorance. The first question I have for you is:

Why are you transferring the data from the text files to access? You are effectively transferring one data store to another.

If there is no "technical" reason why you're moving the data from the text fles to access, I would use ASP's FileSystemObjec t to simply access the data directly from the text files without using access. Using the FSO you can read the name of the file EX: store123.txt - and output this on the page. This would be very effective in solving the problem of not being able to get the store name - if it is in the filename of the file - asp can grab this for you and display it. You could also use it to read the contents of the text file line by line, checking to see what the status of the store is (backup or what have you) - and output the storename and status of the store.

For instance...

Store1: ALERT! USING BACKUP!
Store2: Nominal
Store3: Nominal
Store4: Nominal
Store5: Nominal
Store6: Nominal

Let me know first if there is anything stopping you from doing this, and if there isn't, i will write a script for you to accomplish this task. Just be sure to send me an example of one of these text files over PM if you can, so I know what I am working with.

Secondly, if you wish to use the function mary created instead, below is the ASP version of it.

[CODE=ASP]
Function appendToTable(t blName)
sSQL = "SELECT * FROM " & tblName
Set rs = Server.CreateOb ject("ADODB.Rec ordSet")
rs.Open sSQL,cnn,3,3 ' cnn is, of course, the connection to your database.
' this will append each record from the passed table name to the tmpTable
Do Until rs.EOF
' Each field name will have to be specified.
' For the purposes of examples I am just going to use 3 and show
' how to pass different value types. The first is a String, the second
' a date and the third a number.
cnn.execute("IN SERT INTO tmpTable (FieldName1, FieldName2, FieldName3) " & _
"VALUES ('" & rs!FieldName1 & "', #" & rs!FieldName2 & "#, " & rs!FieldName3 & ")")
rs.MoveNext
Loop

rs.Close
Set rs = Nothing

End Function
[/CODE]

wow thanks a bunch for all of this, and I apologize you had to read through all that JUNK I posted!

Hopefully MY ignorance did not give you a headache.

To answer your question, there is nothing stopping me from doing the asp way you had suggested. As long as I can pull from the files at the launch of the web page I do not care how it is accomplished. This just the only I knew it could be done, or couldnt be done, hah.

I posted a few of these files here

http://www.mykesdesigns.com/tblrock1/access/terminfo22.txt

http://www.mykesdesigns.com/tblrock1/access/terminfo24.txt

http://www.mykesdesigns.com/tblrock1/access/terminfo25.txt

and there are exactly 58 of the files. all named "terminfo#. txt" Where number is of course the store number.

the only thing I want output to the web page are the ones that are "redflagged " or in the wrong.

and that criteria is if Terminal 2 is NOT set as back up, or if any other Terminal BUT number two is set to backup.
and to make matters a little more difficult.. All of these stores will have terminal 2,3,4,5 what they are named after the number will always be different,

IE
05=#5/DT-OT in one file
and
05=#5/CTR in another file.

Now there will always be, like I said, a 02,03,04,05 but in some cases there could be anywhere up ..20. and anyone of those 20 could be set as backup...

I hope this making sense. for instance

http://www.mykesdesigns.com/tblrock1/access/terminfo171.txt

has a 6 and 7...

And then the output of the page needs to say something like
"store 22 has terminal 3 set as backup"
"store 171 has terminal 4 set as backup"
"store 69 has no backup!"

etc



Let me know if you need to know anything else, ill gladly give you any answers you need.

Thanks a million!

[markrawlingson]

Sounds good, I can provide you with a pretty decent solution, give me a couple of hours

Sincerely,
Mark

[mbatestblrock]

Sounds good, I can provide you with a pretty decent solution, give me a couple of hours

Sincerely,
Mark

I cant even thank you enough!

Looking forward to it!

[markrawlingson]

[CODE=ASP]
<%
'Replace this value with the path to the folder where the files are kept
sFolder = "C:\folder\fold er\folder\"
'Open the File System Object
Set oFSO = Server.CreateOb ject("Scripting .FileSystemObje ct")
'Open the folder to return a list of the files held within it.
Set oFolder = oFSO.GetFolder( Server.MapPath( sFolder ) )

'Loop through each file in the folder
For Each File In oFolder.Files
'Open the text file.
Set oText = oFSO.OpenTextFi le(File)
'Skip the first line because it's just column delimited garbage and contains no information we care about as far as this code goes
oText.SkipLine
'Read each line there-after of the file
Do Until oText.AtEndOfSt ream
'Split the data on the line into an array so we can manipulate the data easier.
aTerminal = Split(oText.Rea dLine, ",")
'If we're on line 2, we want to make sure this terminal is set to backup
If oText.Line = 2 Then
'Make sure line 2, column 2 reads as Backup. If it doesn't, display an error stating that it is NOT set to backup.
If InStr(Trim(LCas e(aTerminal(1)) ), LCase("Backup") ) <= 0 Then
Response.Write "NOTE! " & Replace(File.Na me,".txt","") & " does not have " & aTerminal(0) & " as backup!<br/>"
bError = True
End If
'Else, if we are on any other line
Else
'Check to make sure column 2 of any other line does not say backup, if it doesn't, display an error stating there is a problem.
If InStr(Trim(LCas e(aTerminal(1)) ), LCase("Backup") ) > 0 Then
Response.Write "NOTE! " & Replace(File.Na me,".txt","") & " has " & aTerminal(0) & " as backup!<br/>"
bError = True
End If
End If
Loop
oText.Close
Set oText = Nothing
Next

oFolder.Close
Set oFolder = Nothing
oFSO.Close
Set oFSO = Nothing
%>
<html>
<head>
<title>Error Report for Terminal Servers</title>
</head>
<body>
<% If bError Then %>
<form name="oForm" id="oForm" action="?" Method="POST">
<input type="Submit" value="Generate a Report"/>
<% End If %>
</form>
</body>
</html>
[/CODE]

Note that I've included a button at the bottom of the page to generate a report. If you'd like, this button could be used to slap all of the errors found within the text files into 1 single text file of only errors. Then, you could simply send this text file to whomever to inform them that there are errors. The script can even send an email directly to your superior or whoever so you don't have to send an email yourself.

Let the computer do all the work for you I say, Unless this is your only position at this job.. then you're kind of putting yourself out of a job and well, that isn't a good idea. haha

So, with that said, simply create a new blank text file - name it TerminalErrors. asp (or whatever, just make sure the .asp extension is there) and save it to an asp-capable directory.

Note also that I have NOT tested this code - If by some act of God this works first try, the way it is supposed to with no errors - you are one lucky guy. If there are problems with it let us know, and we can take a look and fix them. I, unfortunately, have just been given a pile of work to do - I will test it later for you to make sure it functions effectively.

Sincerely,
Mark

[mbatestblrock]

<%
'Replace this value with the path to the folder where the files are kept
sFolder = "C:\folder\fold er\folder\"
'Open the File System Object
Set oFSO = Server.CreateOb ject("Scripting .FileSystemObje ct")
'Open the folder to return a list of the files held within it.
Set oFolder = oFSO.GetFolder( Server.MapPath( sFolder ) )

'Loop through each file in the folder
For Each File In oFolder.Files
'Open the text file.
Set oText = oFSO.OpenTextFi le(File)
'Skip the first line because it's just column delimited garbage and contains no information we care about as far as this code goes
oText.SkipLine
'Read each line there-after of the file
Do Until oText.AtEndOfSt ream
'Split the data on the line into an array so we can manipulate the data easier.
aTerminal = Split(oText.Rea dLine, ",")
'If we're on line 2, we want to make sure this terminal is set to backup
If oText.Line = 2 Then
'Make sure line 2, column 2 reads as Backup. If it doesn't, display an error stating that it is NOT set to backup.
If InStr(Trim(LCas e(aTerminal(1)) ), LCase("Backup") ) <= 0 Then
Response.Write "NOTE! " & Replace(File.Na me,".txt","") & " does not have " & aTerminal(0) & " as backup!<br/>"
bError = True
End If
'Else, if we are on any other line
Else
'Check to make sure column 2 of any other line does not say backup, if it doesn't, display an error stating there is a problem.
If InStr(Trim(LCas e(aTerminal(1)) ), LCase("Backup") ) > 0 Then
Response.Write "NOTE! " & Replace(File.Na me,".txt","") & " does not have " & aTerminal(0) & " as workstation!<br/>"
bError = True
End If
End If
Loop
oText.Close
Set oText = Nothing
Next

oFolder.Close
Set oFolder = Nothing
oFSO.Close
Set oFSO = Nothing
%>
<html>
<head>
<title>Error Report for Terminal Servers</title>
</head>
<body>
<% If bError Then %>
<form name="oForm" id="oForm" action="?" Method="POST">
<input type="Submit" value="Generate a Report"/>
<% End If %>
</form>
</body>
</html>


Note that I've included a button at the bottom of the page to generate a report. If you'd like, this button could be used to slap all of the errors found within the text files into 1 single text file of only errors. Then, you could simply send this text file to whomever to inform them that there are errors. The script can even send an email directly to your superior or whoever so you don't have to send an email yourself.

Let the computer do all the work for you I say, Unless this is your only position at this job.. then you're kind of putting yourself out of a job and well, that isn't a good idea. haha

So, with that said, simply create a new blank text file - name it TerminalErrors. asp (or whatever, just make sure the .asp extension is there) and save it to an asp-capable directory.

Note also that I have NOT tested this code - If by some act of God this works first try, the way it is supposed to with no errors - you are one lucky guy. If there are problems with it let us know, and we can take a look and fix them. I, unfortunately, have just been given a pile of work to do - I will test it later for you to make sure it functions effectively.

Sincerely,
Mark

I am trying this right now...

[mbatestblrock]

I am trying this right now...

okay, so I tried this two ways. One by copying exactlly what you gave me.

And when I pasted it into notepad it gave me #'s everywhere. and it didnt work, I got this.

Microsoft VBScript compilation error '800a0400'

Expected statement

/terminalerrors. asp, line 3

#
^



----
i thought maybe all the #'s were throwing it off. so I pasted it with no #'s

and get this...

Server.MapPath( ) error 'ASP 0172 : 80004005'

Invalid Path

/terminalerrors. asp, line 7

The Path parameter for the MapPath method must be a virtual path. A physical path was used.


------
I am not sure if I should copy and paste the ASP code in your post or the straight text in the reply.

thank you for helping with your busy schedule!

[markrawlingson]

No problem, I figured there would be errors - Like i said if it worked first try you're one lucky man. This means you're not lucky, today is a day to avoid the casino haha.

I just have to wrap up my workload here and then I will test this out for you. The other ASP Experts have been notified of this thread and asked if they wouldn;t mind giving a hand as well, so if I don't get around to it for several hours perhaps they can about getting the code I posted to work.

Sincerely,
Mark

[mbatestblrock]

No problem, I figured there would be errors - Like i said if it worked first try you're one lucky man. This means you're not lucky, today is a day to avoid the casino haha.

I just have to wrap up my workload here and then I will test this out for you. The other ASP Experts have been notified of this thread and asked if they wouldn;t mind giving a hand as well, so if I don't get around to it for several hours perhaps they can about getting the code I posted to work.

Sincerely,
Mark

Ill be sure to stay away from the casinos tonight!

You all are good people for helping out with this. I hope we can get this licked!

[MMcCarthy]

Ill be sure to stay away from the casinos tonight!

You all are good people for helping out with this. I hope we can get this licked!

Paste the straight text from the reply rather than the ASP code from the post.

[mbatestblrock]

Paste the straight text from the reply rather than the ASP code from the post.

okie dokie, i get the error stated above

Server.MapPath( ) error 'ASP 0172 : 80004005'

Invalid Path

/terminalerrors. asp, line 7

The Path parameter for the MapPath method must be a virtual path. A physical path was used.


ill use the straight text from now on

[markrawlingson]

Ok, I'm back - sorry that took so long. I had to make a billion changes to a customer's site.

Can you send me, by way of email, about 5 of those text files? Doesn't matter which ones, I just want to run this script on my test box and see if I can work all the kinks out of it for you. I'll send you a PM with my address.

Sincerely,
Mark

[NeoPa]

You're impressing Mark.
Great attitude :)

[markrawlingson]

Thank you Neo :)

mbatestblrock,

Here you go! All the errors are worked out. I've tested it quite thoroughly and it works great.

So just to recap - if line #2, column #2 (ASP is reading this as line 4 though, for some reason...) is NOT "Backup", it will write an error to the page. If any other line, column #2 in the file IS "Backup", it will write an error to the page.

[CODE=ASP]
<%
'Replace this value with the path to the folder where the files are kept
sFolder = "D:\Inetpub\www root\netTest\Te st\Reports"
bError = False
'Open the File System Object
Set oFSO = Server.CreateOb ject("Scripting .FileSystemObje ct")
'Open the folder to return a list of the files held within it.
Set oFolder = oFSO.GetFolder( sFolder )
Function Val( sValuePrivate )
Set oRegExp = New RegExp
oRegExp.Pattern = "[^0123456789\.]"
oRegExp.IgnoreC ase = True
oRegExp.Global = True
Val = Abs( oRegExp.Replace ( "0" & sValuePrivate, "" ) )
Set oRegExp = Nothing
End Function

'Loop through each file in the folder
For Each File In oFolder.Files
'Open the text file.
Set oText = oFSO.OpenTextFi le(File)
'Skip the first 2 lines because it's just column delimited garbage and contains no information we care about as far as this code goes
oText.SkipLine
oText.SkipLine
'Read each line there-after of the file
Do Until oText.AtEndOfSt ream
'Split the data on the line into an array so we can manipulate the data easier.
aTerminal = Split(oText.Rea dLine, ",")
'If we're on line 2, we want to make sure this terminal is set to backup
If Val(oText.Line) = 4 Then
'Make sure line 4, column 2 reads as Backup. If it doesn't, display an error stating that it is NOT set to backup.
If Trim(aTerminal( 1)) <> "Backup" Then
Response.Write "<font face='Verdana' size='1' color='red'>NOT E! " & Replace(File.Na me,".txt","") & " does not have " & aTerminal(0) & " as backup!</font><br/>"
bError = True
End If
'Else, if we are on any other line
Else
'Check to make sure column 2 of any other line does not say backup, if it doesn't, display an error stating there is a problem.
If Trim(aTerminal( 1)) = "Backup" Then
Response.Write "NOTE! " & Replace(File.Na me,".txt","") & " has " & aTerminal(0) & " as Backup!<br/>"
bError = True
End If
End If
Loop
oText.Close
Set oText = Nothing
Next

Set oFolder = Nothing
Set oFSO = Nothing
%>
<html>
<head>
<title>Error Report for Terminal Servers</title>
</head>
<body>
<% If bError = True Then %>
<form name="oForm" id="oForm" action="?" Method="POST">
<input type="Submit" value="Generate a Report"/>
<% Else %>
<font face="arial" size="1" color="green">T he system is nominal. No errors to report!</font>
<% End If %>
</form>
</body>
</html>
[/CODE]

It is important to note that the "Generate a Report" button is merely cosmetic at this point. I was going to add in some code to have it generate a report and email it to you but it dawned on me that I don't know what email component you have installed on your system, probably CDOSYS would work fine but i didn't want to take the chance of the page throwing more errors.

All you have to do is replace the value of the sFolder variable with the physical FOLDER path to where the text files are kept on your server. There are no other files in this folder I hope...

Let me know if there are any problems with it.

Sincerely,
Mark

[mbatestblrock]

I am super stoked to give this its run tomorrow.

I will do it first thing in the morning, and let you all know how it turned out!!