Query to get most recent event for each item - Database Discussion Boards

Now i get success by adding [] in Level field.
Now my success code is

"UPDATE tblSave SET [Level]='" & level & "',Exercise='" & exercise & "',CWords=" & CW & ",FWords='" & FWds & "',WPM=' & RCW & ",SpanTime='" & SpanTime & "' WHERE tblSave.UserName='" & CurrentUser & "'"

Plese tell me how do u think for above metter?

sounds like "Level" is a keyword and has to be put into [] if used as column.

how to get row values as columns.

Ismail
Software Engineer

I believe that's called a 'pivot table'. If you google that, you should find the help you need.

Christian Graus - Microsoft MVP - C++

"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )

Add the following code in your stored Procedure

DECLARE @V1 VARCHAR(1000) --Increase the size depending on your row count
DECLARE @V2 VARCHAR(500)
DECLARE @V3 VARCHAR(500)

SET @V1 = ''
DECLARE C1 CURSOR FOR
SELECT country_name FROM t_master_country
OPEN C1
FETCH C1 INTO @V3
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH C1 INTO @V2
IF @@FETCH_STATUS <> 0
PRINT ' '
ELSE
SET @V1 = @V1 + ' ' + @V2
END
SET @V1 = @V3 + ' ' + @V1
CLOSE C1
PRINT @V1 -- Return @v1

This i know but I want to take row as coulum and next coulum values as row values

Ismail
Software Engineer

i dint get you...can u give an example

I a C# app that queries against an accessDB having the following tables:

tblItems<br />
	int ItemID	string ItemName	...<br />
	1		Item1		...<br />
	2		Item2		...<br />
	3		Item3		...<br />
	...<br />
<br />
tblEvents<br />
	int EventID	string EventName<br />
	1		Event1<br />
	2		Event2<br />
	3		Event3<br />
	4		Event4<br />
	....<br />
   <br />
<br />
lnkItemEvents<br />
	int ItemID	int EventID	DateTime TimeStamp	...<br />
	1		1		Jan 1			...<br />
	1		2		Jan 3			...<br />
	1		4		Jan 4			...<br />
	1		1		Jan 8			...<br />
	1		2		Jan 13			...<br />
	2		1		Jan 2			...<br />
	2		4		Jan 3			...<br />
	2		3		Jan 8			...<br />
	...<br />
	<br />
<br />

What I need is a query to return something that looks like this.

ItemName	EventName	TimeStamp	... about 10 more fields from a half dozen tables.<br />
Item1		Event2		Jan 13		...<br />
Item2		Event3		Jan 8		...<br />
...<br />

Based on a bit of googling I believe that to make it work I'll need to use sub queries. One ran on lnkItemEvents to get the most recent time stamp for each item. And a second using the first as a sub query and adding the ItemName, EventName, etc fields to create the final result. I have the first working:

SELECT        MAX(TimeStamp) AS Expr1, ItemID<br />
FROM            lnkItemEvents<br />
GROUP BY ItemID

But when I try and add it to the WHERE clause of the second I get a syntax parsing error at the WHERE and a second error "You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field." If I remove ITemID from the sub query, I only get the single most recent event. Not the most recent event for each item.

To keep it simple while trying to debug, in my truncated main query I haven't added the SQL to use the ID fields to get the corresponding Names, or the other fields of data I need.

<br />
SELECT        ItemID, EventID, TimeStamp<br />
FROM            lnkItemEvents<br />
WHERE        (SELECT        MAX(TimeStamp) AS Expr1, ItemID<br />
                          FROM            lnkItemEvents<br />
                          GROUP BY ItemID)<br />
GROUP BY ItemID, EventID, TimeStamp

If i remove the WHERE clause it does return the selected fields of every row in the table. I do have a otherwise complete, working, version of the main query that gets everything except for the data linked via lnkItemEvents.

Otherwise [Microsoft is] toast in the long term no matter how much money they've got. They would be already if the Linux community didn't have it's head so firmly up it's own command line buffer that it looks like taking 15 years to find the desktop.
-- Matthew Faithfull

Try this query

SELECT ItemID, EventID, TimeStamp
FROM lnkItemEvents
INNER JOIN tblItems ON tblItems.ItemID = lnkItemEvents.ItemID
INNER JOIN tblEvents ON tblEvents .EventID = lnkItemEvents.EventID
WHERE TimeStamp = (SELECT MAX(TimeStamp)FROM lnkItemEvents)

I guess this will work

With a few formatting tweaks (below) to make the DB happy it ran, but it only returns a single row with the single most recent event, not the most recent event for each item.

SELECT tblItems.ItemID, tblEvents.EventID, TimeStamp
FROM ((lnkItemEvents
INNER JOIN tblItems ON tblItems.ItemID = lnkItemEvents.ItemID)
INNER JOIN tblEvents ON tblEvents .EventID = lnkItemEvents.EventID)
WHERE TimeStamp = (SELECT MAX(TimeStamp)FROM lnkItemEvents)

Otherwise [Microsoft is] toast in the long term no matter how much money they've got. They would be already if the Linux community didn't have it's head so firmly up it's own command line buffer that it looks like taking 15 years to find the desktop.
-- Matthew Faithfull

Putting the subquery as it's own entity so that I could access it like a table in the main one let me get what I needed working.

Otherwise [Microsoft is] toast in the long term no matter how much money they've got. They would be already if the Linux community didn't have it's head so firmly up it's own command line buffer that it looks like taking 15 years to find the desktop.
-- Matthew Faithfull

I am new to C# and I am trying to convert some old VB6 code to the new platform.

I am importing a text file ".txt" and want to put the data into a database.

In VB6 you could create a recordset for the database in ram and store it there while you are using it and then delete it.

I would like to accomplish the same task in C# but I am having some problems.
The code I am using is as follows:

private ADODB.Connection cn = new ADODB.Connection();
private ADODB.Recordset rs = new ADODB.Recordset();

public frmMainAlarm()
{
InitializeComponent();
rs.Fields.Append ("ID", ADODB.DataTypeEnum.adInteger, 4, ADODB.FieldAttributeEnum.adFldUpdatable, -1);
rs.Fields.Append("Alarm", ADODB.DataTypeEnum.adChar, 255, ADODB.FieldAttributeEnum.adFldUpdatable, -1);
rs.Fields.Append("Message", ADODB.DataTypeEnum.adChar, 255, ADODB.FieldAttributeEnum.adFldUpdatable, -1);
//rs.CursorType = ADODB.CursorTypeEnum.adOpenStatic;
//rs.LockType = ADODB.LockTypeEnum.adLockOptimistic;
rs.Open(null, null, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, -1);
}

I get an error at the "rs.Fields.Append" statement saying it has the wrong variables or data types.
Any help would be greatly appreciated.

Keith Mabey Smile | :)

If you are using the same code in C# then it will not compile you have to write C# equivalent code to this vb6 code

1 - write code to read from file and then
2 - make a SqlConnection object
3 - make a SqlCommand object
4 - through SalCommand object call the ExecuteNonQuery method

I am tring to create a virtual database in RAM (Memory).
Fill the database table with data from a text file.
Then manipulate the data and rewrite it back out as an XML file.

[Message Deleted]

write code

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

[Message Deleted]

vinodh.s wrote:
can you plz give me an example.so tat i can get a claer view.

An example of what? You haven't explained what you want to do.

I suggest you read the forum guidelines on message etiquette. Asking vague questions isn't likely to appeal you to the users of this forum. State clearly what your problem is and what you want to achieve and perhaps someone will help you.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

need an example of creating a table with a column name declared of datetime datatype.......

Don't delete your messages on a thread. It's rude.

Have you tried to solve the problem yourself? What code have you written?

Documentation for the CREATE TABLE[^] statement is easily available on the web. I suggest you read it and attempt to solve the problem yourself before asking for help again.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

thank you.......

Most frustrating. The format of the website cannot be changed:

I have 5 web pages each with a form. These page forms have content the user can edit & redit.On the top of each page is a menu bar affording the user to toggle back and forth to any of the 5 pages viewing the respective content. Now say the user toggles to page 2, makes changes to the content, via a RadEditor, toggles to page 5, then retoggles to page 2 to see the changes he just made.

Here's the delema: During these edits/redits, the revised content CANNOT be posted as an update to the database. Only AFTER the user is satisfied with all the edits made, does he click button 6 for "Update All My Changes".

I cannot have one page with all the form elements listed via visible/not visible panels. I cannot use session objects, or cookies - there is just too much data. I heard about the cache object / application object but I do not know if it plays a part here. Also note there could be thousands of concurrent users taking this initially identical data content then editing it to each's own unique wants.

I know this may sound more of a .Net question but I thought as DB ppl here, you must have more experience on this need then others.

So in short, how can I have several editable web pages of form elements that allow the user to see the changes they made, even after clicking off and then back to that form without using a database update or items mentioned above. Remember, They will not let me hit the database until aff the edits on the 5 web page forms are complete. Thanks.

Update:

I have reevaluated the entire process.

I've decided to utilize the file stream object and have them edit their entire page.

Now, I have to figure out how to reiterate thru these modified forms to place the data into the db.

modified on Tuesday, February 26, 2008 4:39 PM

If you're writing to the database through a DataSet, you can update the dataset, store it in cache and finally update the db using the dataset when the user is satisfied. Your dataset should have all tables and relations and the relevant update and insert commands defined. It all depends on how you've designed your data layer. I presume that this should be easy if you're using Enterprise Library or something similar. Also, the server will put the cache to the pagefile if it grows too big. The flip side is that this will make your webpage slower. You can keep the user interested using ajax style progress bars/indicators etc while the page loads.

SG
Cause is effect concealed. Effect is cause revealed.

That all sounds great dude, do you know of an actual working VB.net demo I can view that details how that all works?

If it helps, I have a radeditor. Initial it loads with generic info, that everyone sees. Each user is allowed to edit and tailer the content in that rad editor, including uploading/inserting images, to their heart's content. They then click to view another page they may want to edit. I need all the changes/image inserts from that prior page to hang somewhere, but not the database, until the very end.

So, do you know of any VB.net demo online illustrating this?
TYVM

New to ADO .NET (& C#), please help..

I have multiple questions on connecting to SQLServer 2005. I'm doing the following in my code:
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();

builder["Server"] = cmboBoxServerName.Text;
builder["Data Source"] = cmboBoxServerName.Text;
builder["Initial Catalog"] = cmboBoxDBName.Text;
builder["integrated Security"] = true;
builder["Trusted_Connection"] = true;

strConnectStr = builder.ConnectionString;

IDbConnection connection = New SqlConnection();
connection.ConnectionString = strConnectStr;
connection.Open();

This is working fine if I have SQL Native Client installed on my machine.
When I deploy this on a machine witout SQLNativeClient I get the error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'my domain\ my username'.

My questions:
1. Is 'SQL Native Client' required to connect (through my C# App using ADO.NET) to a remote SQL Server ?
2. When is SQL Native client required and when is not? What is the purpose of having a SQL Client for ADO.NET Apps?
3. What is the difference between builder["Server"] and builder["Data Source"]?
4. What is the difference between builder["integrated Security"] and builder["Trusted_Connection"]?
5. What is the minimum number of SQL Client licenses per Server?
6. What is SQL Express and can I use it with ADO .NET? Can I use it as an alternative if don't have enough licenses? Again, should I install it on every machine as well even if I ought to connect to a centralized remote database?

Looks like I have multiple issues and I'm getting confused with my limited knowledge.. I just want to clear out one by one..

Please direct me to any tutorial that handles case by case set up scenarios..
from local server to remote server to cross platform..

Thanks a lot!
-ap