|
Hi,
I have to generate a report but I am having difficulty getting the data
out of the SQL Server. The report is produced daily, weekly or monthly
but my query doesn't work all the time.
The report lists the facilities that are available to be hired out. A
facility is something like a meeting room, or conference hall and so on.
There is also a reservation table that lists when each facility is
booked from and until. The monthly reports seem to work okay, but
sometimes the weekly reports don't contain all the data and frequently
the daily reports are missing data.
The two tables are (I've listed only the keys and the fields I need):
Facility: FacilityId (the primary key), Description, LocationId
FacilityReservation: FacilityReservationId (the primary key), FacilityId
(the foreign key), StartTime, EndTime
What I need is a list of each facility with the amount of time it is
booked for. My SQL is:
SELECT f.FacilityId, f.LocationId, tu.TimeUtilized
FROM Facility AS f
INNER JOIN (
SELECT Facility.FacilityID, SUM(DATEDIFF(minute, StartTime,
EndTime)) AS TimeUtilized
FROM FacilityReservation
INNER JOIN Facility ON Facility.FacilityId =
FacilityReservation.FacilityId
WHERE StartTime BETWEEN @ReportStartTime AND @ReportEndTime
GROUP BY Facility.FacilityID) AS tu ON f.FacilityID = tu.FacilityID
Does anyone have any ideas why there is missing data?
Cheers,
Andy
|
|
|
|
|
|
Hi,
i have a windows application developed, now i have created a deployment setup project for it. it includes the project output and dependencies and the .rpt files.
Well when i install it on the windows XP it runs smooth. i am using MS Access 2002 file format.
But when i install it on win 2000, I am not able to get the dataset from the database. The aaplication starts and gives me an error when i try to login. It says " Object reference not set to an instance", well i am using odbc DNS connection to MS Access.
I also tried installing odbc_net driver, but still same result, and also i thought it wont work as it is already available with the installation pack as .dll file.
What might be the problem. Do i need any drivers to install on win 9x and win 2000 OS.
DOES setup wizard need any other thing to add to it for win 2000 or does win 2000 need service packs as well.
Thanks
Imran.
|
|
|
|
|
Hi There,
Having a unusual problem. When I execute the following INSERT Command in MSAccess, the row is inserted:
INSERT INTO Counter (ID, ParentID, name, maxValue, minValue, ticksPerBase, isSynchronised, assocTimeBase, OS_Sync) VALUES ('C9', 'example.oil', 'counter1', '65535', '1', '1', 'False', 'ts_counter1', 'True')
However, when I try and execute the exact same command in my C# application using the following:
dbConn.Open();
OleDbDataAdapter dbQuery = new OleDbDataAdapter(query, dbConn);
I get an invalid syntax error. I'm not sure if its the correct way to execute an INSERT statement but it works for inserting data into all other tables in the database!!
All the fields are of type text and the first field is he primary key.
Any ideas,
Cheers,
Donal
|
|
|
|
|
Try using this code:
OleDbCommand cmd = new OleDbCommand(query, dbConn);
cmd.ExecuteNonQuery(); The OleDbDataAdapter is for reading some data into a DataSet. The INSERT query does not return data.
If it still doesn't work, then it's something in the query.
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
I understand that the INSERT query doesn't return any data.
The OleDbDataAdapter is able to execute INSERT queries, it may not be the correct method but I haven't had any problems with it before.
Any ideas whats wrong the the query or the table?
Cheers,
Donal
|
|
|
|
|
Sorted it, it was the name of the table. I had to change it from Counter.
Cheers
|
|
|
|
|
How to add attribute to the xml element, based on column in table in tha datset?
Wrong example code(fragment):
DataTable BusName= new DataTable("BusName");
BusName.Columns.Add("id_subj").ColumnMapping = MappingType.Hidden;
BusName.Columns.Add("NameText");
BusName.Columns.Add("langCode").ColumnMapping = MappingType.Attribute;
lQueryXml.Tables.Add(BusName);
generated xml:
<BusName langCode='cs'>
<NameText>adfga</NameText>
</BusName>
This example assigns attribute 'langCode to the element BusName', that's wrong. My aim is to assign this attribute to the element 'NameText'!!!!
I declare that XSD of this doc is valid, but the dataset.readXSD fails.So I manually create all tables and relations. That's oki, but don't know how to assign attribute to the element, which is as a column in the table in dataset.
Anybody know?
Ufffff...
--------------------------
Xabatcha ... test the best
--------------------------
|
|
|
|
|
Hello,
Does anyone have any ideas on how to add large strings to an access database? I'm guessing the database field type will have to be "OLE Object" and pass in string objects that way.
But how do I read and update the field?
Cheers,
Donal
|
|
|
|
|
Someone please correct me if I'm mistaken, but I believe varchars can be up to 8000 bytes long.
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
I want to raise an event from SQL-Server to C# .NET .
where and how i can do this.
thanks.
|
|
|
|
|
Use RAISERROR. If you set the severity to more that 16 (I think) it causes a SqlException when it gets back to your application.
Remember to use RETURN afterwards, unless you want your stored procedure to continue after raising the error (A stored procedure can return more than one error, see the Errors property of the SqlException to access each error raised)
Does this help?
Do you want to know more?
WDevs.com - Member's Software Directories, Blogs, FTP, Mail and Forums
|
|
|
|
|
I am using ADO com connection to connect to Oracle thru ODBC. This is VC application on Windows NT and the database is Personal Oracle 8i. The application works fine for some time after laucnhing and then it crashes with 'Unspecified error message'. The database connection is inconsistent and is getting lost after some time. After that even if i restart the machine the application does not work. Is there is any known problem in using ADO connection to Oracle database.
|
|
|
|
|
Hi
After Updating my Table the Record is killed. The volumes are displaced.
But this is only when i close the Application and open it again.
public class DataUpdateClass
{
private OleDbConnection conn;
public string ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\\..\\Artikel_db.mdb";
This is the Class..
public void DataUpdate(int rowID, string rubrik, string artikel, string regal, string Fach, string Hersteller, string Beschreibung, string Preis, string Datum, string Bestellungsdatum, string Lieferungsdatum, string Garantie, string Seriennr, string Mitarbeiter, string Verwendung, string Lieferant)
{
conn = new OleDbConnection(ConnectionString);
conn.Open();
string sql = "UPDATE tbl_Artikel SET Rubrik = '"+rubrik+"', Artikel = '"+artikel+"', Hersteller = '"+Hersteller+"', Regal = '"+regal+"', Datum = '"+Datum+"', Bestellungsdatum = '"+Bestellungsdatum+"', Lieferungsdatum = '"+Lieferungsdatum+"', Lieferant = '"+Lieferant+"', Seriennr = '"+Seriennr+"', Verwendung = '"+Verwendung+"', Garantie = '"+Garantie+"', Fach = '"+Fach+"', Preis = '"+Preis+"', Mitarbeiter = '"+Mitarbeiter+"', Beschreibung = '"+Beschreibung+"' WHERE (ID = "+rowID+")";
OleDbCommand icmd = new OleDbCommand(sql,conn);
icmd.ExecuteNonQuery();
conn.Close();
}
And here called...
private void btUpdate_Click(object sender, System.EventArgs e)
{
DataUpdateClass duc = new DataUpdateClass();
duc.DataUpdate(System.Convert.ToInt32(textboxID.Text),textboxArtikel.Text,textboxHersteller.Text,textboxGarantie.Text,textboxPreis.Text,textBoxLieferant.Text,textBoxDatum.Text,textBoxBestellungsdatum.Text,textBoxLieferungsdatum.Text,textBoxMitarbeiter.Text,textBoxSeriennr.Text,textBoxVerwendung.Text,textBoxRubrik.Text,textBoxRegal.Text,textBoxFach.Text,textBoxBeschreibung.Text);
}
Greetings frm Germany
|
|
|
|
|
Got it!
But to me it's a little bit strange... The Variables
were not in the right order. I'd never tought that this
could be the problem. The Variables must be in the same
order like the Textboxes are... Curious...
Greetings from Germany
|
|
|
|
|
Hello,
I'm writing a stored procedure to copy a "quote" (unfinished order) to a finished order (two tables similar to each other, but not identical). I just don't know how to copy the items (each quote or order can have multiple items.)
My QuoteItems table has an ID_quote column, and I want to copy all the QuoteItem records with a specific ID_quote to my Items table (again, similar but not identical, so I'd need to additional fields and change another.)
It should be that difficult, it's just that I'm starting with "advanced" T-SQL. Any ideas?? Thanks in advance,
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
The T-SQL will depend on what you want to insert into the new table exactly. If you can provide more information I may be able to help you create a stored procedure you need.
Depending on your needs, you might need to loop using cursors, or you only need to do this:
INSERT INTO tblX (IDQuote, FieldA, FieldB, ...)
(
SELECT ID_Quote, FieldA, FieldB, ...
FROM QuoteItems [JOIN tableX ON...]
WHERE ID_Quote = @idQuote
)
Edbert P.
Sydney, Australia.
|
|
|
|
|
Thanks for your reply.
I use SELECT to get the quote info (customer information and employee) into local variables and then I insert those into my Orders table.
My QuoteItems table has several fields (ID_item (identity PK), ID_quote, Description) and my Items table has those fields plus others (ID_item (identity PK, different from QuoteItems'), ID_order (newly created order), Description, Produced (must be set to false on record insertion)).
I can create the new Orders record based on my Quotes record. Then I need to select all records from QuoteItems with a specific ID_quote and insert corresponing records into the Items table, copying some fields, and setting others to an arbitrary value (like the ID_order field).
I remember seeing a query like this:
UPDATE Items SET Field=value WHERE id_item IN (SELECT id_item FROM Orders WHERE ID_order=1); I don't know the exact syntax, and whether I could apply it here, but I would believe my answer would somewhere close to that.
I hope I have explained myself better. Thanks for your help,
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Maybe if I could call another stored procedure for each record in QuoteItems that has the ID_quote set to a specific value??
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
I didn't get a clear idea from your previous reply.
Do you actually need to insert new rows to the QuoteItems or do you need to update specific values?
If you can give me an example of what data you need to put in it'll be great 
Edbert P.
Sydney, Australia.
|
|
|
|
|
Ok, let's try. I have two sets of tables. When the salesman is working up an order, he captures it in the Quotes table, and its items in the QuoteItems. When the order is accepted, it is copied to the Orders and OrdersItems tables. Most fields are copied the same, but some change (for example the foreign key in QuoteItems is different than that in the OrderItems.)
For the example, the salesman is working with three potential customers, each buying 3 items. The Quotes table has 3 records (for each potential sale) and the QuotesItems has 9 records, 3 for each customer.) The potential sales are saved in the database because it takes some days, and can be edited later if a new requirement is found. Once sent for production, no changes can be made (well, maybe with a phone call.)
Now customer A decides on buying and gives an advance deposit. A new record must be created in Orders and the corresponging three in OrderItems, representing the new real sale, and effectively sending the order for production. Then the quote is marked as been really sold.
I would like to do this in a stored procedure with only one parameter, the PK (ID_quote), and it automatically create the corresponding records in the two analogous tables.
I can already copy the main record in Quotes to Orders (a SELECT into local variables and then an INSERT, and then a SELECT @@IDENTITY to get the new ID_order.) What I cannot do is copy all the records in QuoteItems with ID_quote = @ID_quote (the stored procedure's parameter) and insert them in OrderItems, changing some values (ID_order for example).
I hope its clearer now. Thanks for your help!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Let's see.
You can already:
1. Insert into Orders.
By this it means you copy record in quotes to orders and give it a new PK (ID_order).
2. Get the ID_order (by using SELECT @@IDENTITY)
According to you,
QuoteItems has :
1. ID_item (PK)
2. ID_quote
3. Description
If you have ID_quote field in the Orders table the sp may look like this:
CREATE PROCEDURE dbo.sp_InsertOrdersItems @ID_quote int AS
INSERT INTO OrdersItems
(ID_order, Description, Produced)
(
SELECT Orders.ID_order, QuoteItems.Description, false
FROM Orders
INNER JOIN QuoteItems ON Orders.ID_quote = QuoteItems.ID_Quote
WHERE Orders.ID_Quote = @ID_quote
)
If you don't have ID_quote field in the Orders table then you need to pass ID_order into the sp's parameters.
Hope that helps!
Edbert P.
Sydney, Australia.
|
|
|
|
|
You've just earned a big Fosters!!!
Thanks! Actually my SELECT query was simpler than that (no JOIN required), but what I wanted was the SELECT inside the INSERT! It now works flawlessly!! thank you very very much!!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Hi
I need help on a SQL statement (I'm on first steps in the world of SQL)
I have 3 tables
LG (id_lg, name)
LineLG (id_linelg, id_lg, job, type_of_hour, monday, tuesday, ..., sunday)
Type_of_hour(type_of_hour)
I need to build an sql statement that retrives:
the id_lg, name, normal, Outdoor, after_hours
where normal, Outdoor, after_hours are the type of hours and I need for each LG the total of normal, outdoor and after_hours hours wich is listed in Table lineLG
Thanks
|
|
|
|
|
Is this what your looking for?
SELECT LG.id_lg,
LG.name,
SUM(LGL.type_of_hour) AS sum_of_hourtypes
FROM LG AS LG
JOIN LineLG AS LGL
ON LGL.id_lg = LG.id_lg
GROUP BY LG.id_lg,
LG.name
ORDER BY LG.name
or this:
SELECT LG.id_lg,
LG.name,
COUNT(LGL.type_of_hour) AS count_of_hourtypes
FROM LG AS LG
JOIN LineLG AS LGL
ON LGL.id_lg = LG.id_lg
GROUP BY LG.id_lg,
LG.name
ORDER BY LG.name
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
