|
|
You should try looking at 'Factory' class which gives you an idea how to write database agnostic [generic] code.
|
|
|
|
|
I want to select categories with biggest id from the table below. For example, Diet should return 228 and excercise should return 225... I want distinct category with biggest id.
id Category
9 Diet
218 Diet
228 Diet
8 Exercise
215 Exercise
225 Exercise
1 Investing
224 Investing
234 Investing
5 Movie
219 Movie
229 Movie
Anybody can help me??
thanks in advance
-- modified at 22:36 Thursday 13th July, 2006
|
|
|
|
|
SELECT Category, MAX(id) AS MaximumID
FROM dbo.TableName
GROUP BY category
should do the trick. that is of course assuming you are using SQL Server, best in future to specify the actual database (and version) it removes any doubt and ensures the answer you get is accurate
|
|
|
|
|
thank you very much. This is exactly what i wanted.
|
|
|
|
|
No worries, MAX is one of SQL Server's "aggregation" functions. There are lots - SUM, MIN, AVG etc etc and Grouping is a very powerful feature - definitly worth the time to look into and understand.
|
|
|
|
|
//String* con = S"Provider=Microsoft.Jet.OLEDB.4.0;" + S" DataSource=C:\\AEM\\Database\\ProTunerDatabase.mdb";
OleDbConnection* ole = new OleDbConnection( S"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\AEM\\Database\\ProTunerDatabase.mdb" );
//OleDbDataAdapter* dadapter = new OleDbDataAdapter(S"select * from Item_List", ole);
OleDbDataAdapter* dadapter = new OleDbDataAdapter();
DataSet* dset = new DataSet();
ole->Open();
OleDbParameter* par;
dadapter->SelectCommand = new OleDbCommand(S"select * from test", ole);
dadapter->Fill( dset, S"test" );
dadapter->UpdateCommand = new OleDbCommand(S"update test set Item_name=@str where Item_id=@prev");
par = dadapter->UpdateCommand->Parameters->Add(new OleDbParameter ("@str", OleDbType::VarChar));
par->SourceColumn = "Item_name";
par->SourceVersion = DataRowVersion::Current;
par = dadapter->UpdateCommand->Parameters->Add(new OleDbParameter ("@prev", OleDbType::Integer));
par->SourceColumn = "Item_id";
par->SourceVersion = DataRowVersion::Original;
dadapter->UpdateCommand->Connection = ole;
dadapter->Update(dset,"test");
HOW DO I SPECIFY VALUES FOR @str and @prev ????
so that i can update my database.
I am using Visual C++.NET in Visual Studio 2005
Thanks,
Kedar Dave
|
|
|
|
|
dadapter->UpdateCommand->Parameters("@str")->Value = "test".
|
|
|
|
|
dadapter->UpdateCommand->Parameters("@str")->Value = "test";
I tried it....but it gave me the following errors:-
error C2064: term does not evaluate to a function taking 1 arguments
error C2227: left of '->Value' must point to class/struct/union/generic type
why do i get this?
setting par->Value = "test"; -- doesnt giv any errors but doesnt work either
Thanks,
Kedar Dave
|
|
|
|
|
My mistake. Parameres["@str"] - square brackets, not parentheses
|
|
|
|
|
i did Parameters["@str"]->Value = S"test";
but got the following when i compiled :
error C2107: illegal index, indirection not allowed
hw do i get rid of this error?
thanks a lot,
Kedar Dave
|
|
|
|
|
Hey,
we have an application written in ASP 3.0.in that we used Crystal Reports to generate reports(directly communicating with ASP).Now we want to use Sql REporting Servicesinstead of crystal Reports.I want to know whether we can use SQl Reporting services with ASp 3.0 or not.. If we can, plz send a sample or explain me how to do that. my id..
grminds@yahoo.com
Thanks in advance..
|
|
|
|
|
|
To best of my knowledge you can build web and smart client using reporting services from VS 2005 development platform. Since you are using ASP3.0 which I assume not asp.net, then I guess try exploring options of web services interface provided by ms reporting services.
Hope this helps.
Difficult - > Challenging, this simple replacement made me take my life little easy;)
|
|
|
|
|
Hi, i'm having a bit of trouble with this query:
"select SUM(t1.value1 - SUM(t2.value2)) from table1 as t1, table2 as t2 where t1.prop=0 and t1.payed=0 and t1.date='2006-3-21' and t2.parent=t1.id";
I can't retrieve the SUM(t1.value1 - SUM(t2.value2)) because i don't know where to insert the group by and what should i group for. I was able to retrieve (t1.value1 - SUM(t2.value2)) if i group by t1.id (wich is the column index) but adding the SUM it doesn't work. Ignore the conditional values, i'm having troubles with the SUM(t1.value1 - SUM(t2.value2)) part.
Thanx if someone can helpe me with this one.
-- modified at 16:57 Thursday 13th July, 2006
|
|
|
|
|
Start small
SELECT
SUM(t1.Value1),
SUM(t2.Value2)
FROM
table1 t1,
table2 t2
GROUP BY
t1.Value1, t2.Value2
I don't know if the above query works but it is a smaller stepping point. After the above works gradually move towards your more complicated query. I tried in Oracle and it seems to be an expesive query. Maybe breaking it up using T-SQL would help.
A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."
-- Stephen Crane
|
|
|
|
|
Your query surely works but asume i modified it a step foward as you suggested and i get the following:
SELECT
(t1.Value1 - SUM(t2.Value2))
FROM
table1 t1,
table2 t2
GROUP BY
t1.id
That returns a list of integers that is correct. But now i want the sum of that list.
Thanks anyway
|
|
|
|
|
If you can't get it with one select (with a lot of tweaking you usually can) you can always sutff the results into a table variable and do another select.
DECLARE @table TABLE (int a)
INSERT INTO @table
SELECT
(t1.Value1 - SUM(t2.Value2))
FROM
table1 t1,
table2 t2
GROUP BY
t1.id
SELECT SUM(a) FROM @table
A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."
-- Stephen Crane
|
|
|
|
|
I'd use a subquery:
SELECT SUM(t1.value1 - t2sum)
FROM table1 t1
INNER JOIN
( SELECT parent, SUM( value2 ) as t2sum
FROM t2
GROUP BY parent ) t2
ON
t2.parent = t1.id
WHERE
t1.prop = 0 AND
t1.payed = 0 AND
t1.date = '20060321'
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Thanks man! it worked just fine! your're bright man. The only type error you had was "From table2" instead of "from t2". It worked at first run. I'll study the structure of this query to lear how it works.
Thanks man again, good luck.
|
|
|
|
|
I have two SQL server databases on the same server.
The table structure is same except for few.
I want to move some records from different tables one database to another.
What is the best way to do it?
create a DTS package, but I don't know how it supports 'Begin Tran' and the 'Execute SQL Task' in the DTS does not take any parameter (i.e. global variables) in the update and delete statements.
If I create a Stored procedure and use the 'Begin Tran' and 'Commit' statement in the procedure. It also does not work for me since the stored procedure keeps on executing the statements that are followed even if any error occurs (i.e. primary key violation etc.).
How do I make sure that the statements in the stored procedure are rolled back or procedure exits when any error occurs in it.
Thank you for helping me.
|
|
|
|
|
set @errornum = @@error after every sql statement then check for the error number. @@error is reset after every succesful statement so if(@@error = 15) will set @@error = 0.
http://www.sommarskog.se/error-handling-I.html
A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."
-- Stephen Crane
|
|
|
|
|
Thank you Ennis. It really works for me.
and thank you for the reference URL too.
|
|
|
|
|
Hello all,
I have a MS Access database. There are several employees who uses that database everyday. But when someone opens it, it locks for other user to open the database.
How can I share the database between all the users at the same time? Can someone let me know ASAP.
THANKS
Bashar Amin
Sr. Software Engineer
|
|
|
|
|
Access is not designed to be a multi-user database. If it is really required create an application front-end that uses jet or odbc and keeps its fingers crossed. Make daily back-ups because Access likes to get corrupted when it is used with multiple users.
A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."
-- Stephen Crane
|
|
|
|