|
i need the 2 temporary tables because i'm quering 2 times the same tables and then i'm doing a third query to cross the results
basically, i'm looking for the right bus (it's a bus timetable) to go from some origin to a destination (so i create the temp Table "from", the temp table "TO" and cross them)
it works, but it's horrible and slow
thanks
fuel2run
|
|
|
|
|
So if I've got this straight, you've got a single Buses table with a From and a To field, you're creating a From table with all the buses from some specific location and a To table with all the buses to a specific location, and you want to find the record which is in both those tables?
If that's all you want to do, why not put both conditions in one query and save yourself the trouble of creating the temp tables in the first place?
SELECT * FROM Buses WHERE [From] = ? AND [To] = ?
Or am I missing something here?
|
|
|
|
|
hi ekolis,
well, yes you are missing something, but that's why i haven' been clear enough,
the FROM and TO aren't necessarily the start end stop of the bus route, but they can be intermediate stops.
moreover i'v got a table with the sequence of stops for each bus route (every bus route has the same code and this is what i'm looking for). i'haven't got a column called "STARTS AT" nor a column "ENDS AT", i only have a column "STOPS AT".
so a condition like "WHERE [From] = ? AND [To] = ?" can't exists
thas's why i'm confused...
any advice?
thanks anyway for your suggestions and for interesting.
cheers!
fuel2run
|
|
|
|
|
So I suppose you have 3 fields: BusID, StopTime, and StopLocation, let's call them? And the primary key is BusID and StopTime? Why don't you try creating a query, call it BusSchedule, which looks like this:
SELECT * FROM BusStops WHERE BusID = ? ORDER BY StopTime
Now run this query in a foreach loop on BusID, and it will give you the bus's schedule in the standard timetable format. (Assuming StopTime is stored as a DateTime and not as a string!) Now just grab the first and last record from this query
drFirst = dtQuery.Rows[0];
drLast = dtQuery.Rows[dtQuery.Rows.Count - 1];
and compare drFirst["StopLocation"] to your desired start location and drLast["StopLocation"] to your desired end location. If both equalities hold true, then the BusID you just ran the query on is the bus you're looking for and you can break out of the foreach loop.
Is this the answer you're looking for, or do I still not have your table structure right?
|
|
|
|
|
well,
you figured out the table structure correctly, the primary key is BusID only, StopTime is a DateTime Field, but i can't use a query like this because "Origin" and "Destination" of the travel aren't necessarily the first and last stops of the bus travel.
my query would answer a question like: "i want go from, say, philadelphia to chicago leaving at 10.00am (within a range of a couple of hours: 10.00-12.00am), which bus i have to take?"
so i might take a bus stopping at philadelphia and at chicago but started somewhere else and destinated to somwhere else again.
i'm gettin' lost!
cheers!
fuel2run
|
|
|
|
|
If your primary key is only BusID, how can a single bus have multiple stops? It would need to be listed multiple times (once for each stop), but that wouldn't be allowed by the primary key...
I think I see what you're getting at with the "cross query" you wanted to use - I've never done any of those before, but it seems from what you've said that a cross query returns all records that are present in both tables you're comparing. So what you were originally doing is you had a query which returned all the buses that stopped at the source location and a query which returned all the buses that stopped at the target location? I guess what you could do then is something like this:
TimeSpan tsBest = tsBest.MaxValue;
string strBest = null;
foreach (DataRow drFrom in tblFrom.Rows)
{
// See if the bus is also in the "to" table
DataRow drTo = tblTo.Find(drFrom["BusID"]);
if (drTo != null)
{
// See if the bus stops at the from location *before* stopping
// at the to location (note: will not work if buses run past midnight!)
DateTime dtFromTime = (DateTime)drFrom["StopTime"];
DateTime dtToTime = (DateTime)drTo["StopTime"];
if (dtFromTime < dtToTime)
{
// See if bus leaves with desired range
if (dtFromTime <= dtDesiredMaxFromTime && dtFromTime >= dtDesiredMinFromTime)
{
// found a bus
// note: will not necessarily find the bus CLOSEST to the desired
// departure time, only the FIRST bus listed that is acceptable
strBest = drFrom["BusID"].ToString();
break;
}
}
}
else
{
// no bus goes here to there
strBest = null;
}
}
|
|
|
|
|
hi ekolis,
you're quite right once again, i didn't explain enough, some more informations for you:
i'm trying to convert to asp.net something i've done inside access writing some vb code in a module and, most iportant, i have 3 tables to query from:
- "TabLines"= LineID, LineName
- "TabRoutes"=RouteID, LineID (related to LineID in "TabLines"), .. more data ...
- "TabStops"=LineID, RouteID, StopTime, ...more data...
"TabLines"' key field is LineID, RouteID and LineID are both key fields in "TabRoutes" and they are related to the same fields in "TabStops" where there are no key fields.
the procedures in access can find every bus stopping in the "from" place within a range of 2 hours from the time specified by input and stopping in the "to" place
i create the "ToTable" and write it on disk, create the "FromTable" and write it as well and then query those tables:
..."WHERE ToTable.LineID=FromTable.LineID AND ToTable.RouteID=FromTable.RouteID AND ToTable.StopTime>FromTable.StopTime"
i never thought to past midnight! this is one more problem!
Cheers!
fuel2run
|
|
|
|
|
So the procedures generate the FromTable and the ToTable, and you then query those tables? If that's the case, can I see the procedure code? I've never done anything with Access procedures before, but I imagine they're similar enough to VB.NET that I could at least help translate them... if I'm mistaken again, would it be too much trouble to email me your Access file (with any sensitive data removed) so I can understand what's really going on?
|
|
|
|
|
well, i might send you the access file, but bear in mind this is in italian and almost not commented,
moreover, my problem is finding a different way from the one i used in the access file while simply translating froom vb to vb.net should be easy.
anyway, if you still want to take a look at the file let me know, i might send you the db with empty tables.
cheers!
fuel2run
|
|
|
|
|
Well, sorry that I've been unable to help you thus far. Perhaps we can look at some of your assumptions and try to work around them:
1. Why is it necessary that you not write the temporary tables to disk?
2. Why is it necessary that you do this from scratch (i.e. not use the VBA code or convert it in a simple way?)
3... ???
|
|
|
|
|
weel, i don't want to write temporary tables because it's a slow task, nothing more.
i thank you for trying to help me anyway!
cheers!
fuel2run
|
|
|
|
|
I have an application with a typed dataset. The application works fine if I fill the dataset from a SQL Server connection. However, recently, I've been trying to change the application so that it reads from an XML file. This was supposed to be easy, but its turning out not to be.
I instantiate my dataset subclass, load the data from the database, and then write the XML using the WriteXML (outfilename, System.Data.XmlWriteMode.WriteSchema) to create an XML file with Schema. My plan is to save this file off and not use a database for production hosting because my application isn't really transactional (trying to save some hosting costs). Creating the file is no sweat.
However, when I try to read file into a DataSet using ReadXML and an explicit System.Data.XmlReadMode.ReadSchema, I get an ""Invalid 'key' node inside constraint named: [Constraint Name]"" exception. I've scoured the web and the schema to see if I can identify something wrong, but I haven't found anything of note.
On one post, someone suggested changing the msData:isDataSet flag in the schema from ""True"" to ""False"". I'm not sure I understand the logic, but I tried this. I read the file, but I get different errors (see below for a summary of the scenarios).
On MSDN I saw a note that there is a known bug with row states on XML-based datasets and you should copy the dataset prior to binding it to work around the problem. This seems to do something, but again, I get different errors.
I've tried every scenario I can think of: Reading vs. Ignoring the Schema, Changing ""IsDataSet"" between False and True, and copying vs. not copying prior to binding. Here are my results:
(1) Read Schema; IsDataSet=True -- Results in: Invalid 'key' node inside constraint named: [Constraint Name]"" exception
(2) Read Schema; IsDataSet=False; Copy Prior to Bind -- Results in: The name '[DataSetName]' is invalid. A datatable cannot have the same name of the dataset.
(3) Read Schema; IsDataSet=False; Don't Copy Prior to Bind -- Results in: Application runs, but the result is an empty datalist.
(4) Ignore Schema; IsDataSet=True; Copy Prior to Bind -- Results in: Object reference not set to an Instance of an object when a DataView is bound to the DataList.
(5) Ignore Schema; IsDataSet=True; Don't Copy Prior to Bind -- Results in: Application runs, but the result is an empty datalist.
(6) Ignore Schema; IsDataSet=False; Copy Prior to Bind -- Results in: Object reference not set to an Instance of an object when a DataView is bound to the DataList.
(7) Ignore Schema; IsDataSet=False; Don't Copy Prior to Bind -- Results in: Application runs, but the result is an empty datalist.
Again, the application works fine if I load the DataSet from the database. Its just when I use the ReadXML methods that it doesn't work. Can anyone suggest anything else that I should be trying?
Thanks,
Jordan
|
|
|
|
|
ok.. here my problem:
i have an app that makes an ADO Connection to a xls File to retrieve its data...
so if the xls file is opened in Excel the ADO queries gets very lame.... about 25 seconds/query...
any solutions for this problems?
thanks fpr your answers!
|
|
|
|
|
If you are querying the excel spreadsheet and not just importing or reading it once from top-to-bottom, perhaps you should put it in a database. After putting the data into a database you can add indices to the table. A single index, depending on the amount of data, could make all of difference in the world.
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
I'm trying to open a .dbf file using C#, but I'm having problems with the SELECT statement. I've successfully created a connection to the database, but I don't know what table name I should put in the FROM clause when setting up the OdbcCommand object. I've tried various combinations of the file name, but it always exceptions when I call ExecuteReader(). The exception is as follows:
@"ERROR [42S02] [Microsoft][ODBC dBase Driver] The Microsoft Jet database engine could not find the object 'e:\D6000.DBF'. Make sure the object exists and that you spell its name and the path name correctly.".
Can anyone help?
|
|
|
|
|
Hi,
I have the following problem:
I downloaded Microsoft sql server 2000 MSDE Service pack 3 (sql2ksp3).
Then I installed it with the command line :
setup SECURITYMODE=SQL SAPWD="sa"
Next I tried to open a connection to a database from an ASP.NET using C#.
I tried to use the following to connect:
OleDbConnection conn = new OleDbConnection(@"Provider=SQLOLEDB;User ID=sa;Initial Catalog=master;Data Source=(local);");
conn.Open();
this failed with the error:Login failed for user 'sa'
then I tried this:
OleDbConnection conn = new OleDbConnection(@"Provider=SQLOLEDB;Integrated Security=SSPI;User ID=sa;Initial Catalog=master;Data Source=(local);");
conn.Open();
this failed with the error :Login failed for user 'HOME-35NVKYBV8B'
were 'HOME-35NVKYBV8B' is my local host name
Now the conn.Open(); allways gets the following error:
Please help me if you have an answer.I've read every possible doc I could find but could not find the answer!
Thanks.
avivhal
|
|
|
|
|
In your first example, OleDbConnection conn = new OleDbConnection(@"Provider=SQLOLEDB;User ID=sa;Initial Catalog=master;Data Source=(local);");, you didn't provide a password for the 'sa' account. But in your statement about how you installed MSDE, you said that you set the password to 'sa'. So, for this connection string, you should add Password=sa;. However, it is recommended that you not use a system administrator account to connect to the database from an application.
In your second example, you are using integrated security, which means that the connection is made to the database using the currently active Windows login. I assume from the username in the error message, and your statement that it's the machine name, that you're writing a service, and it's set up to run using the system account. This would work, but you'll have to use a tool (or write an app) to configure the database server to allow this account to login, and give it access to the specific database that it needs to access. Note that since you set Integrated Security=SSPI;, the User Id=sa; part of the connection string is ignored.
Grim (aka Toby) MCDBA, MCSD, MCP+SB
|
|
|
|
|
HI,
I have an access database using workgroup file to allow users to access it.
I want to connect this database in the ASP.net application. I want to know how to write the connection string to use authentication from the workgroup file.
Thanks
Ali Alaradi
Developer
|
|
|
|
|
|
how do I limit the selection in of my "select statement" in orale 8.1
|
|
|
|
|
Praneeth_konda wrote:
how do I limit the selection in of my "select statement" in orale 8.1
if I understand you correctly ..
limit the select using where clause..
Select col1,col2,..... from table1 where col1=XYZ and col2='ABC'
|
|
|
|
|
Can anyone help me ?
I have to change a text fileld to ntext field with very large volume of data. I am using MS-SQL 2000. updating and droping takes too much time and converting syscolumns is leading unwanted results.
|
|
|
|
|
Unfortunately, updating and dropping is the only convenient way to do it.
You COULD take the database offline, export all the data from the table to flat file, drop and recreate the table with the new layout, and then re-import the data previously exported. But This will most likely take just as long or longer than just letting Enterprise Manager do things its own way.
Grim (aka Toby) MCDBA, MCSD, MCP+SB
|
|
|
|
|
Thanks for your answer Grimolfr, I badly need that and if I don't, it is as simple as I cannot do this.
I don't need to detatch the database, what I am currently doing is adding a col, updating the new col from prev col, deleting the actual col and updating the syscolumns to rearrange it's order. Everything works fine but, the updation time.
I wanted to know if it is possible to get EM using COM, so that I can open table in design mode and change the property from within there (possibly using sp_oacreate or sqldmo objects) and may be I Look a bit dummy suggesting all these stuffs but who knows if gurus has some hidden secreats.
|
|
|
|
|
Well, certainly you can use SQLDMO to do this. The problem is that there's really no method faster than what you're already doing. The best you can hope for, I think, is to put the database in single-user mode where you're the only user, and not perform the changes in a transaction.
BTW, be careful modifying the system tables directly. This can often have unexpected results, and cause the table to become pretty much useless.
Grim (aka Toby) MCDBA, MCSD, MCP+SB
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
