mbatestblrock

Alrighty, I have been trying to fix my problem for quite some time. I am faily new to .NET and I could really use a hand here.

I have an access database that has nearly 70 linked tables to text files. I have one large union query joining together 70 queries.. In access it works flawlessly for me. It returns just a few fields of text that I, and few others need to check on a regular basis.

I wanted to put this on our web server so we could check it at any given moment. I have gotten this to work before and it works beautifully.

But now, When I connect to the database, and ( I am using VS 2008 Beta 2 right now, and it does the same thing in VS 2005, and Microsft Expression Web ) it asks me "how would you like to retrieve data from your database

I choose the Specify a Custom SQL Statement or Sotred Procedure.

If I chosse the stored procedure query I get the error:

Schema Could Not be retrieved for this stored procedure. Please make sure the connection settings are correct and that the databse is online.

NOW in the one that I mentioned earlier I got to work, I got that same error for the stored procedure, But if I did my own SQL statement (copy and paste from the access query) it worked fine, but when I try and do that now, I get this error.

I took a screen cap:

http://www.mykesdesigns.com/tblrock1/vserror.jpg


I have tried searching around, but I am seriously out of google grease, and am prarying someone can help me here!

thanks in advance!



Re: .NET Framework Data Access and Storage Configuring access datasource HELP!!!

Caddre

(I have an access database that has nearly 70 linked tables to text files. I have one large union query joining together 70 queries..)


This is not what you want to hear but 70 tables should not be linked in a UNION, you could create a UNION ALL view which moves only the columns you need to a view. This means you spend time to design the data and how you want the pages and create UNION ALL views for what you want and your code will run. The Asp.net team is right code like what you have should not be running.






Re: .NET Framework Data Access and Storage Configuring access datasource HELP!!!

mbatestblrock

Sorry for the confusion. they are linked with UNION ALL. the final query looks something like this,



Select * FROM qry22
UNION ALL
SELECT * FROM qry24
UNION ALL
SELECT * FROM qry25
UNION ALL
SELECT * FROM qry26
UNION ALL
SELECT * FROM qry27
UNION ALL
SELECT * FROM qry31
UNION ALL
SELECT * FROM qry32
UNION ALL
SELECT * FROM qry33
UNION ALL
SELECT * FROM qry34
UNION ALL
SELECT * FROM qry35
UNION ALL SELECT * FROM qry36;


but with a whole lot more.. like I said it works fine in access.

I guess I didnt understadn everything you said, but maybe this inforamtion will help




Re: .NET Framework Data Access and Storage Configuring access datasource HELP!!!

jgalley

If you want to just get some data from a buch of text files, maybe a better approach might be to go after the text directly rather than via access.

Are all the text files in a particular directory or set of directories

Can you provide the layout of one of the files, and the information you are after

It should be faily easy to get a quick example running.





Re: .NET Framework Data Access and Storage Configuring access datasource HELP!!!

mbatestblrock

no problem. I have provided a few samples 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
http://www.mykesdesigns.com/tblrock1/access/terminfo171.txt

they are all in one directory. F:\IMPORT\manpoll\FTP\misc




now. here where the hard part comes (for me) and this is why i used access.. As you can see these files are pretty much all the same. I have 58 of them, they update to the given location nearly 5 times a day. They conatin information on the configuration of our stores terminals.

they are either configured as "Backup" or "Workstation"

the only that can be set as backup is "02" and it NEEDS to be set as backup
ALL other terminals MUST be set as "Workstation"

I have to sift through these files everyday and try to catch the ones that do not meet those requirements, then I have to email the store and tell them whats what.

So i need this page to return to me all the "REDFLAGS" if you will..

EXAMPLE:

Store 22 has no BACKUP

or

STORE 22 Terminal 05 is wrongly set as backup

I think you get the idea...

also the the text itself in these files do not contain the store number, only the NAME of the file does...

This might be an easy thing to do without access, I am not sure... But I would give anything for some help on this!!!

-looking forward to your response!

-Michael






Re: .NET Framework Data Access and Storage Configuring access datasource HELP!!!

Caddre

Well you did not understand me the difference between my UNION ALL and yours is you are doing a SELECT ALL from 70 tables and combining it in a massive UNION ALL which is only valid in Access not RDBMS(relational database management systems). So you need to create a design of what you want and create smaller views for different data view of your pages. I hope this makes it more clear, post again if you still need help.






Re: .NET Framework Data Access and Storage Configuring access datasource HELP!!!

jgalley

Would you like an answer in C# or VB





Re: .NET Framework Data Access and Storage Configuring access datasource HELP!!!

mbatestblrock

Which ever would be easiest for you! I can deal with either.




Re: .NET Framework Data Access and Storage Configuring access datasource HELP!!!

mbatestblrock

I dont get though, I did this jus to test the same situation in a smaller way.

This is what I did in my test db

I made a linked table to a text file.
I created a table with one text field,

i made a query that had the both of those table in it.

I the created another query that pulled the two fields from that query in.


I know it sounds crazy, but that is how my real databse is working.

it has 58 linked table to text files.
58 tables that have just one text field in it each table is a store number.
Then a query that pulls the linked text file and the store number in
then a master query to pull all of those queries.


The test woorked just fine in Visual Studio, and it connected to the query just fine. It is all working the same way, i dont get it




Re: .NET Framework Data Access and Storage Configuring access datasource HELP!!!

jgalley

Super Quick and Dirty

Code Block

string myDirPath = "..\\..\\Files\\";
DirectoryInfo myDirInfo = new DirectoryInfo(myDirPath);

StringBuilder myErrorText = new StringBuilder();

foreach(FileInfo myFileInfo in myDirInfo.GetFiles("*.txt"))
{
bool isAnyBackup = false;
bool isT2Backup = false;
bool isOtherBackup = false;
List<string> myOtherBackupList = new List<string>();

string myStoreId = string.Empty;
myStoreId = myFileInfo.Name.Replace("terminfo",
string.Empty);
myStoreId = myStoreId.Replace(".txt", string.Empty);

string myNextLine = string.Empty;
using (StreamReader myReader =
new StreamReader(myFileInfo.FullName))
{
myNextLine = myReader.ReadLine();
do
{
if (!myNextLine.StartsWith("[") &&
!myNextLine.StartsWith("T") &&
!myNextLine.StartsWith(" "))
{
/// ****************************
/// We should have a terminal record.
/// Rather than working with an array of
/// strings it would be better to load up
/// an instance of a terminal object
/// ****************************
string[] myFields = myNextLine.Split(',');
/// ****************************

if (myFields[1].StartsWith("Backup"))
{
isAnyBackup = true;
if (myFields[0].StartsWith("02"))
{
isT2Backup = true;
}
else
{
isOtherBackup = true;
myOtherBackupList.Add(
myFields[0].Trim());
}
}
}
myNextLine = myReader.ReadLine();
} while (!myReader.EndOfStream);
}

/// There is a problem
if (!isAnyBackup || !isT2Backup || isOtherBackup)
{
myErrorText.AppendLine("*************************");
myErrorText.AppendLine("Store: " +
myStoreId +
" problems found.");
myErrorText.AppendLine("*************************");

if (!isAnyBackup)
{
myErrorText.AppendLine("** no backup set");
}
if (!isT2Backup)
{
myErrorText.AppendLine("** 02 not set to Backup");
}
if (isOtherBackup)
{
myErrorText.AppendLine("** Wrong Backup(s).");
foreach(string myBadTerminal in myOtherBackupList)
{
myErrorText.AppendLine("** " + myBadTerminal);
}
}
myErrorText.AppendLine("*************************");
myErrorText.AppendLine();
}
}

if (!myErrorText.Length.Equals(0))
{
textBox1.Text = myErrorText.ToString();
}
else
{
textBox1.Text = "No Errors Found";
}





Re: .NET Framework Data Access and Storage Configuring access datasource HELP!!!

mbatestblrock

I am sorry to be so ignorant, but I JUST dont understand what exactly all this is.


I pasted it into my source code in VS, and my page looks like this.




string myDirPath = "..\\..\\Files\\"; DirectoryInfo myDirInfo = new DirectoryInfo(myDirPath); StringBuilder myErrorText = new StringBuilder(); foreach(FileInfo myFileInfo in myDirInfo.GetFiles("*.txt")) { bool isAnyBackup = false; bool isT2Backup = false; bool isOtherBackup = false; List myOtherBackupList = new List(); string myStoreId = string.Empty; myStoreId = myFileInfo.Name.Replace("terminfo", string.Empty); myStoreId = myStoreId.Replace(".txt", string.Empty); string myNextLine = string.Empty; using (StreamReader myReader = new StreamReader(myFileInfo.FullName)) { myNextLine = myReader.ReadLine(); do { if (!myNextLine.StartsWith("[") && !myNextLine.StartsWith("T") && !myNextLine.StartsWith(" ")) { /// **************************** /// We should have a terminal record. /// Rather than working with an array of /// strings it would be better to load up /// an instance of a terminal object /// **************************** string[] myFields = myNextLine.Split(','); /// **************************** if (myFields[1].StartsWith("Backup")) { isAnyBackup = true; if (myFields[0].StartsWith("02")) { isT2Backup = true; } else { isOtherBackup = true; myOtherBackupList.Add( myFields[0].Trim()); } } } myNextLine = myReader.ReadLine(); } while (!myReader.EndOfStream); } /// There is a problem if (!isAnyBackup || !isT2Backup || isOtherBackup) { myErrorText.AppendLine("*************************"); myErrorText.AppendLine("Store: " + myStoreId + " problems found."); myErrorText.AppendLine("*************************"); if (!isAnyBackup) { myErrorText.AppendLine("** no backup set"); } if (!isT2Backup) { myErrorText.AppendLine("** 02 not set to Backup"); } if (isOtherBackup) { myErrorText.AppendLine("** Wrong Backup(s)."); foreach(string myBadTerminal in myOtherBackupList) { myErrorText.AppendLine("** " + myBadTerminal); } } myErrorText.AppendLine("*************************"); myErrorText.AppendLine(); } } if (!myErrorText.Length.Equals(0)) { textBox1.Text = myErrorText.ToString(); } else { textBox1.Text = "No Errors Found"; }




I changed the myDirPath to my directory where the text files were, and I pretty much get the exact thing....

Any advice




Re: .NET Framework Data Access and Storage Configuring access datasource HELP!!!

jgalley

First off this example is in C# and assumes that you are creating a winforms application.

You are correct in setting your directory in place of the one I have (my testing dir).

You will also need a classic TextBox control on your form to display the results (textBox1)

After adding the textbox, add a button (button1) and double click it in the designer to generate an empty event handler for the button:

private void button1_Click(object sender, EventArgs e)
{

}

at that point you can cut and paste the code above into the event handler

Code Block

private void button1_Click(object sender, EventArgs e)
{
string myDirPath = "..\\..\\Files\\";
DirectoryInfo myDirInfo = new DirectoryInfo(myDirPath);

StringBuilder myErrorText = new StringBuilder();

foreach(FileInfo myFileInfo in myDirInfo.GetFiles("*.txt"))
{
bool isAnyBackup = false;
bool isT2Backup = false;
bool isOtherBackup = false;
List<string> myOtherBackupList = new List<string>();

string myStoreId = string.Empty;
myStoreId = myFileInfo.Name.Replace("terminfo",
string.Empty);
myStoreId = myStoreId.Replace(".txt", string.Empty);

string myNextLine = string.Empty;
using (StreamReader myReader =
new StreamReader(myFileInfo.FullName))
{
myNextLine = myReader.ReadLine();
do
{
if (!myNextLine.StartsWith("[") &&
!myNextLine.StartsWith("T") &&
!myNextLine.StartsWith(" "))
{
/// ****************************
/// We should have a terminal record.
/// Rather than working with an array of
/// strings it would be better to load up
/// an instance of a terminal object
/// ****************************
string[] myFields = myNextLine.Split(',');
/// ****************************

if (myFields[1].StartsWith("Backup"))
{
isAnyBackup = true;
if (myFields[0].StartsWith("02"))
{
isT2Backup = true;
}
else
{
isOtherBackup = true;
myOtherBackupList.Add(
myFields[0].Trim());
}
}
}
myNextLine = myReader.ReadLine();
} while (!myReader.EndOfStream);
}

/// There is a problem
if (!isAnyBackup || !isT2Backup || isOtherBackup)
{
myErrorText.AppendLine("*************************");
myErrorText.AppendLine("Store: " +
myStoreId +
" problems found.");
myErrorText.AppendLine("*************************");

if (!isAnyBackup)
{
myErrorText.AppendLine("** no backup set");
}
if (!isT2Backup)
{
myErrorText.AppendLine("** 02 not set to Backup");
}
if (isOtherBackup)
{
myErrorText.AppendLine("** Wrong Backup(s).");
foreach(string myBadTerminal in myOtherBackupList)
{
myErrorText.AppendLine("** " + myBadTerminal);
}
}
myErrorText.AppendLine("*************************");
myErrorText.AppendLine();
}
}

if (!myErrorText.Length.Equals(0))
{
textBox1.Text = myErrorText.ToString();
}
else
{
textBox1.Text = "No Errors Found";
}
}





Re: .NET Framework Data Access and Storage Configuring access datasource HELP!!!

Caddre

( wanted to put this on our web server so we could check it at any given moment. I have gotten this to work before and it works beautifully. )

The above means the user is in a Webform and I also noticed the error page shows sqldatasource while the user is using Access. So the user just needs a UNION ALL View with 58 columns using Accessdatasource.






Re: .NET Framework Data Access and Storage Configuring access datasource HELP!!!

jgalley

Opps, sorry I missed the webserver part when I did my code... one sec and I will give you a web based version....

Place a label on your webform:

Code Block
<asp:Label ID="lblReport" runat="server" />

Then in the code behind:

Code Block

protected void Page_Load(object sender, EventArgs e)
{
string myDirPath = "E:\\Temp\\msdnQuestion\\Files\\";
DirectoryInfo myDirInfo = new DirectoryInfo(myDirPath);

StringBuilder myErrorText = new StringBuilder();

foreach (FileInfo myFileInfo in myDirInfo.GetFiles("*.txt"))
{
bool isAnyBackup = false;
bool isT2Backup = false;
bool isOtherBackup = false;
List myOtherBackupList = new List();

string myStoreId = string.Empty;
myStoreId = myFileInfo.Name.Replace("terminfo",
string.Empty);
myStoreId = myStoreId.Replace(".txt", string.Empty);

string myNextLine = string.Empty;
using (StreamReader myReader =
new StreamReader(myFileInfo.FullName))
{
myNextLine = myReader.ReadLine();
do
{
if (!myNextLine.StartsWith("[") &&
!myNextLine.StartsWith("T") &&
!myNextLine.StartsWith(" "))
{
/// ****************************
/// We should have a terminal record.
/// Rather than working with an array of
/// strings it would be better to load up
/// an instance of a terminal object
/// ****************************
string[] myFields = myNextLine.Split(',');
/// ****************************

if (myFields[1].StartsWith("Backup"))
{
isAnyBackup = true;
if (myFields[0].StartsWith("02"))
{
isT2Backup = true;
}
else
{
isOtherBackup = true;
myOtherBackupList.Add(
myFields[0].Trim());
}
}
}
myNextLine = myReader.ReadLine();
} while (!myReader.EndOfStream);
}

/// There is a problem
if (!isAnyBackup || !isT2Backup || isOtherBackup)
{
myErrorText.Append("*************************"

myErrorText.Append("<br>");
myErrorText.Append("Store: " + myStoreId);
myErrorText.Append(" problems found.");
myErrorText.Append("<br>");
myErrorText.Append("*************************");

myErrorText.Append("<br>");
if (!isAnyBackup)
{
myErrorText.Append("** no backup set");
myErrorText.Append("<br>");
}
if (!isT2Backup)
{
myErrorText.Append("** 02 not set to Backup");
myErrorText.Append("<br>");
}
if (isOtherBackup)
{
myErrorText.Append("** Wrong Backup(s).");
myErrorText.Append("<br>");
foreach (string myBadTerminal in myOtherBackupList)
{
myErrorText.Append("** " + myBadTerminal);
myErrorText.Append("<br>");
}
}
myErrorText.Append("*************************");
myErrorText.Append("<br>");
}
}

if (!myErrorText.Length.Equals(0))
{
lblReport.Text = myErrorText.ToString();
}
else
{
lblReport.Text = "No Errors Found";
}
}