|
Go back to the Test team and ask them to explain in detail why they believe that your query is 'weak'.
|
|
|
|
|
Hi,
Can someone please advise me on what to do with this issue.
I have a table that has a DATE, GROSSHOURS, GROSSMINUTES and TOTAL. I have a select query that takes the GROSSHOURS and GROSSMINUTES and add them together in seconds e.g: 1H 30M = 90M
My issue is this:
I have 2 lines on the same day.
2010/01/05 1HOUR 30MINUTES 90MINUTES
2010/01/05 2HOURS 20MINUTES 140MINUTES
How will I write a select statement where I can only display 1 line for 2010/01/05 but with both totals?
2010/01/05 230MINUTES
My statement looks like this at the moment:
Select Id, CONVERT(VARCHAR,EntryDate,111) AS EntryDate, GrossHours, GrossMinutes, DATEDIFF(minute, StartJob, EndJob) / 60 * 60 + GrossMinutes AS Total FROM [Timesheet] GROUP BY Id, EntryDate, GrossHours, GrossMinutes, StartJob, EndJob
Thank you!
Illegal Operation
|
|
|
|
|
You need to remove GrossHours and GrossMinutes from your select statement and group by.
|
|
|
|
|
Hello,
I'm attempting to query a list of tables from an SQLite3 database and have come across the query PRAGMA table_info("table_name") in the SQLite docs.
Here is my code:
OdbcConnection DbConnection;
OdbcDataReader DbReader;
DbConnection = new OdbcConnection("DSN=" + odbcConnName);
DbConnection.Open();
OdbcCommand DbCommand = DbConnection.CreateCommand();
DbCommand.CommandText = ("PRAGMA table_info(bandwidth_records)");
DbReader = DbCommand.ExecuteReader();
while (DbReader.Read())
{
Trace.WriteLine(DbReader["type"].ToString());
}
DbReader.Close();
DbConnection.Close();
However, DbReader.Read() is not returning true. Very weird.
Any ideas?
Thanks,
Matt
|
|
|
|
|
I am not sure what was wrong with your case, but a test on my system returned the correct info.
Are you sure you want to use the PRAGMA commands in your programs? Based on the documentation future versions of SQLite will not guarantee the backward compatability for these PRAGMA commands.
|
|
|
|
|
Hm.. loyal ginger, which SQLite-ODBC driver have you used?
I want it too
|
|
|
|
|
This will not work in SQLite via ODBC (if you use SQLite.Data.Dll everything will be ok)
However, using ODBC you can get table fields too. Here's a sample:
DbConnection.Open();
DataTable dt = DbConnection.GetSchema("Columns");
foreach (var col in dt.Columns) {
Console.Write(col + "\t");
}
foreach (DataRow row in dt.Rows) {
foreach (object field in row.ItemArray) {
Console.Write(field + "\t");
}
Console.WriteLine();
}
|
|
|
|
|
|
DataTable class itself isn't exactly what you need - it just contains the data.
You should look at GetSchema method of DbConnection class. This method returns the metaschema of data for different databases.
|
|
|
|
|
|
Is there a given interface to the ODBC items:
TABLE_CAT
TABLE_SCHEM
TABLE_NAME
COLUMN_NAME
DATA_TYPE
TYPE_NAME
COLUMN_SIZE
BUFFER_LENGTH
DECIMAL_DIGITS
NUM_PREC_RADIX
NULLABLE
REMARKS
COLUMN_DEF
SQL_DATA_TYPE
SQL_DATETIME_SUB
CHAR_OCTET_LENGTH
ORDINAL_POSITION
IS_NULLABLE
to only parse certain "fields?"
Thanks for your time,
Matt
|
|
|
|
|
What do you mean as interface to the ODBC items?
I don't completely understand your question. Please be more concrete, maybe I'll be able to help you.
|
|
|
|
|
I have a view that contains a list transactions, each of which are associated with a single routing number. Multiple transactions can exist for each routing number. Each transaction can be one of two types (a and b). I need to get the routing numbers, the count of transactions for each routing number, and then the count of those that are of type b, and the result set can only include transactions of type b (along with the requisite counts, of course). Can I get some guidance?
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
One way is to have a nested query
select a.[transaction], b.tran_count
from transactiontable A
inner join (select [transaction], count(*) as tran_count from transactiontable group by [transaction]) as b
on a.[trasaction] = b.[transaction];
This is an off-the-cuff answer so it needs some looking into, but should start you down the right path.
|
|
|
|
|
I'll give it a try tomorrow. We came up with a three-select query, but it's evident that we know not what we do. 
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
select RoutingNUmber, count(TransactionNumber) as TransCount
from TransTable
where TransType = 'B'
group by RoutingNUmber
Does that help?
Tim
|
|
|
|
|
Nah, but that's okay - we have a solution (to use the term loosely).
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
Hi,
I need to run a dynamic query baseed on a parameter.
Actualy I have a comboBox in my report filled with TableNames of my DataSource,
now I want to select all records of the selected table.
I've filled the combo but I'm confused how to run a different and runtime query
Best wishes
|
|
|
|
|
If I understand your problem, you can build a string like:
mySQL = "select * from " + me.Combobox1.selectedValue
Then execute the statement.
|
|
|
|
|
Either run a parameterised query or a stored proc, passing in the value form your combo box. Try googling either of those terms and you will find many examples.
Bob
Ashfield Consultants Ltd
Proud to be a Code Project MVP
|
|
|
|
|
If you are using ADO.Net, you can create a command object. By assigning different values to its "CommandText" property, you can conduct queries on different tables. Use ExecuteReader() method to get the result.
Please give more details on what API you are trying to use to get access to the database. That way people here can better answer your question.
|
|
|
|
|
Hello everyone,
I query the db and I get a number of rows returned.
They are displayed in a form with fields to be updated.
I know how to do the update on a one by one basis.
What I want to do is be able to update multiple rows at once.
Let say for example 5 fields per row and 10 rows.
There is something about arrays and loop thru it that I cannot get to work.
Any help would be greatly appreciated.
Thanks
Murali
|
|
|
|
|
I generally loop through a datatable and update each row individually, there are some articles around where an xml string is used to pass a collection of rows into the database for updating but I have never actually needed to use it.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
If the new values can be expressed by expressions, then you can use an update query to update multiple records and multiple fields.
For example,
Update MyTable Set Price=Price*1.05, ItemVolume=ItemVolume+100 Where Category='Electronics';
Hope this helps. Happy programming!
|
|
|
|
|
What language are you using for your front end? Some languages will support this.
For example, Java supports batch updates so you can loop through all the entries setting the update parameters for each in turn, then do an executeBatch to do the lot in one go. (OK, before anyone points this out, not all JDBC drivers support this.)
C# has something similar, although I've never used it myself. And if you can do it in C#, you can probably do it in VB.NET as well.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
