.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan
Post New Web Links

query: last login record without a matching logoff record

Posted By:      Posted Date: October 01, 2010    Points: 0   Category :Sql Server
 

Hello. I am creating a login/logoff audit program to track / restrict users on our network.

I have a table containing log on/off records so : loginid, userid, logindatetime, state, computername (state is either: 1 logon, -1 logoff or 0 attempted logon)

its probably quite simple but i can't quite get my head around it, i need a query to tell me who is currently logged on, ie the most recent logon (state=1) which has no logoff (state=-1) to match. the trouble is that this must cater for users being able to log on to multiple computers so somewhere the computer names must match too when eliminating records.

i currently have the following query for selecting the most recent logon, but can't think how to eliminate the ones which have a matching logoff

with cte as (
	select *, ROW_NUMBER() over (partition by UserID order by a.LoginDateTime desc) as seq from LoginAuditTable a where a.[State] = 1
	)



View Complete Post


More Related Resource Links

Need Help in LINQ query for group By with chunks of record

  

I am assigning and unique id [strShipperIdSequence] on my List on bases of some properties which are grouped together uniquely.
Now what i needed is that my group should be further break down to some maximum amount of chunks.[Let say 10]
that mean's even i am having Same value in 12 records i should get 2 groups[I of 10 items and other of 2 items]

var uniqueGroups = objMdbContentInfoList.GroupBy(p => new
{
p.CON_ENTRY_POINT,
p.APPTType,
p.CON_CONTAINER_ISSUE_DATE
}).Select(g => g.First()).ToList();
foreach (var objUnique in uniqueGroups)
{
string strShipperIdSequence = APIGlobalMethods.GetShipperRequestID();
foreach (MdbContentInfo obj in objMdbContentInfoList.FindAll(h => (h.CON_ENTRY_POINT == objUnique.CON_ENTRY_POINT &&
h.APPTType == objUnique.APPTType &&
h.CON_CONTAINER_ISSUE_DATE == objUnique.CON_CONTAINER_ISSUE_DATE)))
{
obj.Shipper

How to query previous and next record?

  

hi there,

  i would like to query out the next record and previous record guid from the current record guid.But i don't want to use the ID + 1 or ID - 1  because the ID is not secure for query like this. I am using MS SQL.

May i know how can i write the sql query?  Please give me a guidance.

 

 

hope can hear from you all soon. Thank You


Sql query to put data in one sngle record

  

i have one parent table and one child table.

so in parent table only one records will stay for one bunch of records and remaing records will pushed into child table.so scenario may be like this in MSATER table for one record there may be 3 reords in child table

2-in MSATER table for one record there may be 5 reords in child table

3-in MSATER table for one record there may be 10 reords in child table and so on..

parent table                                                 Child table

id  Student_name   age   phone                 id  Student_rollno   Student_name  FatherNAme
 
1     manoj        23  42846209862            1       34             manoj           Deepak
						    1       23             ramesh          Kailash
						    1       45             raja              tara	

I want data in this format 	    
id  Student_name   age   phone            FatherNAme1  FatherNAme2 FatherNAme3	
1           manoj    23    42846209862        Deepak     Kailash		tara		

But currently we have 3 records but if I have 10 records then also tha data comes in similar fashion means(	FatherNAme1   coloumns repeat 10 time)
How can I do this.I need to store data in a single row.						

 


Query Timed Out But Record Inserted

  

Hi,

I have a parameterized stored procedure that inserts one record into a table at any one execution. The query timed out but the record was inserted. My question, is it possible for a query to time out and record gets inserted at the same time?

Thanks in advance.

Regards,

Yong Hwee 


Need Help in a query... How to Display Last Record For a Given Month

  

Hi All,

I have a table DBGROWTHTABLE with below structure and data.

USE [ibmdba]

GO



/****** Object: Table [dbo].[DBGROWTHTABLE] Script Date: 10/22/2010 11:54:02 ******/

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO



SET ANSI_PADDING ON

GO



CREATE TABLE [dbo].[DBGROWTHTABLE](

	[TableEntryID] [int] IDENTITY(1,1) NOT NULL,

	[EntryDate] [smalldatetime] NOT NULL,

	[MachineName] [varchar](100) NULL,

	[InstanceName] [varchar](100) NULL,

	[DBNAME] [varchar](100) NULL,

	[DBsizeMB] [bigint] NULL,

	[unallocMB] [bigint] NULL,

	[reservedMB] [bigint] NULL,

	[dataMB] [bigint] NULL,

	[indexMB] [bigint] 

Calculating a time difference between two record and print as time_difference in MS Sql Query

  

Hi to all,

i dont think so its a big task but due to some reason i could not get a proper return value its returning me null which seems to be some thing is wrong in query entries that was not executing in proper way me just writing my query here could you please check where i am wrong.

select
dbo.ProjectA.LineID,
dbo.ProjectA.ProductCod,
dbo.ProjectA.Datestamp as Datetime1,
DateDiff(second, dbo.ProjectA.Datestamp, (select top 1 dbo.ProjectA.Datestamp from dbo.ProjectA where datepart(minute,dbo.ProjectA.Datestamp) > Dateadd(Minute,1, datepart(minute,dbo.ProjectA.Datestamp) ))) as time_difference

from dbo.ProjectA

Where (dbo.ProjectA.Datestamp between CONVERT(Datetime, '2010-09-09 06:00:00', 102)and CONVERT(Datetime, '2010-09-09 18:00:00', 102))

Group by LineID,dbo.ProjectA.Datestamp, dbo.ProjectA.ProductCod

Having (dbo.ProjectA.LineID = '1' )

order by  dbo.ProjectA.Datestamp

LineID    ProductCod             Datestamp                  time_difference
1         prdAitmq4673     2010-09-09 06:05:57.090    NULL
1     &nb

Need Query to find out the latest record insert in database

  

Here is my table contain the following fields

   ID    UID    OID   Date  

   1      20       1    2010-11-03 12:22:44.217

   2      30       1     2010-11-03 12:01:09.090  

   3      20      2     2010-11-03 12:22:44.217

   4      40      2   2010-11-03 12:01:09.090

i need the result like

UID   OID    Date

 30     1        2010-11-03 12:01:09.090  

 40     2         2010-11-03 12:01:09.090  


Please help me

20 2 2010-11-03 12:22:44.217

GridView inline Master/Detail record display

  
This code drop is part of a smash and grab series. If you're in a rush, you can just grab this code and insert it into your application, no understanding is required. When you have some time (ya, right) you may want to review the source code.

Get Top 1 Record from Dataset

  
Many of them in need of getting the Top 1 or Top 10 records from a Dataset without going to SQL query. It is possible to do from Code Behind. I have given the code below,
The below code is to get single record from a Dataset. Also I have given for getting 8 records at Green Color.

Enterprize library 4.1 getting output parameter after adding record in db, plz guide

  

Hi,

I need to get output parameter (flag) from db after saving record in database. I am using Microsoft Enterprise Library 4.1 for DB. I am inserting record using I Data reader.

Please guide

Thanks


Gridview Edit on Searching of Record

  

using Asp.net c#

sql server

i am searching a record from my database and displaying it in gridview now i want the comments should be updated as per the record which is being searched

For E.g if record No.3 is searched then only i can update record no.3 and i don't want rest of data to be shown


now what is happening when i click on edit my whole table is being displayed

this is bcuz i made one method gridfill() which i m calling everytime if i m doing grid row editing,row cancelling and row updating

how i can resolve this thing

my code

 protected void GridView2_RowEditing(object sender, GridViewEditEventArgs e)
    {
        GridView2.EditIndex = e.NewEditIndex;
        gridfill();

    }

 protected void GridView2_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        string id = GridView2.Rows[e.RowIndex].Cells[0].Text;
        TextBox t;
        t = (TextBox)GridView2.Rows[e.RowIndex].Cells[2].Controls[0];
        string query = "update user1 set comments=@comments where id=@id";
        SqlConnection con = new SqlConnection(connstr);
        con.Open();
        SqlCommand cmd = new SqlCommand(query, con);
        cmd.Parameters.AddWithV

Does this look correct for saving a file/ new record to a database

  

So thank you for all your help so far, So I think i got this right,
but would like for you all to look it over to see if there is any
areas I might run into trouble with:

//using asp.net mvc 2
the table (sql server 2005, using entities framework)
"Pattern_Media"
table fields
"Pattern_Media_GUID  uniqueid 
"Pattern_GUID  uniqueid
"Media varbinary(max)  
"height int
"Width int
"Media_Type String (50)


the form:
<h2> Add Media to this pattern</h2>
<% Html.EnableClientValidation(); %>
<% Html.BeginForm("AddMedia", "Pattern", new { id= Model.Pattern_Guid} , FormMethod.Post, new {enctype = "multipart/form-data"}); %>
   
    <%: Html.Label("height") %>
    <%: Html.TextBox("height") %>

    <%: Html.Label("width") %>
    <%: Html.TextBox("width") %>

    <%: Html.Label("Media") %>
    <input type="file" id="Media" name="Media" />
   
    <input type="submit" name=

Why show only one record?

  

Hi,

// Update these variables to values whatever you are storing in your table
        // If you are storing as CARD NUMBER, update this Card Number Attribute Name to CARD NUMBER
        string strCardNumberAttributeName = "Credit card number";
       // string strCardTypeAttributeName = "1";
        string strExpiryDateAttributeName = "Expiry date";
        DataTable dtPaymentDetails = new DataTable();

        string queryString = "SELECT tbl_Payments.PaymentId, tbl_PaymentDetails.AttributeName, tbl_PaymentDetails.Value, tbl_Payments.PaymentType FROM tbl_Payments INNER JOIN tbl_PaymentDetails ON tbl_Payments.ID = tbl_PaymentDetails.PaymentId WHERE ATTRIBUTENAME IN ('" + strCardNumberAttributeName +
                                   "','" + strExpiryDateAttributeName + "') ORDER BY PaymentId";

        string connectionString = "data source=.\\SQLEXPRESS;Integrated Security=SSPI;" +
                                           "AttachDBFilename=|DataDirectory|\\BurnbetDatabase.mdf;User Instance=true";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlCommand command = new SqlCommand(queryString, connection);
            connection.Open();
            SqlDataReader reader = command.ExecuteR

Can this code be setup to run against the whole database instead of just 1 record at a time?

  

We have made some changes to this code to start capturing 1 new field of data and updating it as new records are added. But there is currently about 120,000 records more or less.. those records of course dont have the new field populated with anything..

We would like to run this logic that already in place and run it against the tables to update the fields 1 time. I think to make it easier, if it can be setup to expect the "valueTwo" variable, so that we can run it againt the individual codes instead of doing all the records at one time.. there are codes that only have a few records, so it would be best to test initially against the small code group.

thanks.

  
            strSqual = "insert into trans (trans_type_name, trans_date,sys_id,mod_user_id,show_ind, remoteCode, techName) values('" & valueTwo & "','" & TransDate & "',"&strSystemID&", 1,'T', '" & dbQuote(strUser) & "', '" & strTechName & "')"        
  
            getStaticRecordSet(strSqual)   
            'get the new transaction_id out for just inserted alarm   
            strSqual = "select max(transaction_id) as transaction_id from trans"  
            set rst = getStaticRecordSet(strSqual)

Querying a db for existence of a record.

  

Can someone suggest, what is the best approach using linq to sql to do the following

1. Check if a row exists in a table for a given criteria and...

2. If it exists return a particular field value else return '0'.


delete record from excel sheet

  



Hello,

           I have write the code for deleting the data in excel sheet. but we have got the error" Deleting data in a linked table is not supported by this ISAM."

     below I paste some code snippest.

string strDelete = "Delete  from [" + strSheetName + "$]";

            cmdExcel.Connection = con;
            cmdExcel.CommandType = CommandType.Text;
            cmdExcel.CommandText = strDelete;

            cmdExcel.ExecuteNonQuery();


Please can anybody this answer.It is urgent


Toolbox: The Active Record Pattern, Aspect-Oriented Programming

  

This month we look at implementing the Active Record Pattern in your .NET application and how aspect-oriented programming yields separation of concerns.

Scott Mitchell

MSDN Magazine February 2009


Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend