|
SELECT a.* from Table1
INNER JOIN
(SELECT field1, max(dateentered) AS zDateEntered
FROM table1
GROUP BY field1)
b
ON a.field1 = b.field1 and a.DateEntered = b.zDateEntered
|
|
|
|
|
Very nice… That’s exactly what I was looking for thanks.
Jason W.
|
|
|
|
|
Hi All,
I’m handling events of ADO in an ATL out-of-process server, and for some reasons when I use _ATL_FREE_THREADED declaration event ConnectComplete is not called when I establish an asynchronous connection, thou it is called when I use a synchronous connection. Changing the memory model to _ATL_APARTMENT_THREADED solves the problem, but what is the reason of such strange behaviour anyway, and what’s the
proper solution?
Regards,
Vitaly Tomilov
|
|
|
|
|
There are two SQL Server tables; parent table "Adressen", child table "Details". Details is related with field "adid" to parent table "Adressen". A new Adress Row has multiple DetailsRows.
"adid" is the primary key of "Adressen" and grows 1 by row.
I work with typed Sql Server tables and C#.
simplyfied:
AdressenRow adr_row=new ds1.Adressen.NewAdressenRow();
DetailsRow det_row=new ds1.Details.NewDetialsRow();
//Add the knew Rows
ds1.Adressen.AddAdressenRow(adr_row);
ds1.Details.AddDetailsRow(det_row);
//Update Adressen
xDataAdapter.Update(ds1.Adressen);
//Now I need to have the "adid" of the Inserted Row to set it also in the new DetailsRow but in the DataSet.Adressen Table, adid of the new created AdressenRow is "0".
How I can get this id? Is there the possibility to make a cunning Insert- or Update-Statement?
What's the best way to solve this little problem?
What's the way to solve the problem that DataSource and DataSet is not synchronized?
Thanks
Stefan
|
|
|
|
|
You may have to change your code a bit, but here's the gist:
First, you need a stored procedure like this:
CREATE PROCEDURE [addaddressen]
@param1 varchar(100),
@adid int output
AS
insert into addressen (param1) values (@param1)
select @adid = @@IDENTITY
GO
Notice the @@IDENTITY field. This sql variable contains the ID of the row you just inserted. Then your C# code to retrieve that value would look like this:
SqlConnection SqlCon = new SqlConnection("your_connection_string");
SqlCommand SqlCmd = new SqlCommand("addaddressen", SqlCon);
SqlCmd.CommandType = CommandType.StoredProcedure;
SqlParameter sParam;
sParam = SqlCmd.Parameters.Add(new SqlParameter("@param1",
SqlDbType.VarChar, 50));
sParam.Value = "Test Addressen";
sParam = SqlCmd.Parameters.Add(new SqlParameter("@adid",
SqlDbType.Int));
sParam.Direction = ParameterDirection.Output;
SqlCon.Open();
SqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
string adID = SqlCmd.Parameters["@adid"].Value.ToString();
The string adID now contains your id value of the record you just inserted into addressen. You can now insert into your details table with this ID.
I hope this helps.
-Matt
p.s. You can just do the whole thing in a single stored procedure, like this:
CREATE PROCEDURE [addaddressen]
@param1 varchar(100),
@detailParam1 varchar(100),
@adid int output
AS
insert into addressen (param1) values (@param1)
select @adid = @@IDENTITY
insert into details (adid, detailParam1) values (@adid, @detailParam1)
GO
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
Thank you for your explainations!
Inbetween I've found now exactly what you wrote to me. But I use now SCOPE_IDENTITY() instead of @@IDENTITY as returnvalue. One thing to add: I use now SetParentRow() of the ChildRows to set the correct id of the ChildRows automaticly when updating the Details Table.
Thanks
Stefan
|
|
|
|
|
I have a toolbar in a form. The text of the buttons on the toolbar are like "&New", "&Edit", "&Search".
But the buttons ButtonClick Event is not fired when I use ALT+"n" or ALT+"e". and so not accessible with the keyboard.
What have I to do that I can access the buttons ButtonClick Event with ALT+"X"?
Thanks
Stefan
|
|
|
|
|
Hello,
I have a really generic question (I hope). I have a Query called "Open Job Orders". In it I have a Table called "Job Order" and in that Table there is a field called "Job Order ID". Here is what I want to do (this is all just for me learning and becoming more familiar with DBs and Queries):
I want to create a string and have the Job Order ID returned into that string.
For instance I did the following
Dim blank As String
Dim stDocName As String
stDocName = "Open Job Orders"
DoCmd.OpenQuery stDocName
blank = [Open Job Orders]![Client]![Client Name]
This will open the Query (just so I can see it) but I want blank to be equal to the first Job Order ID.
How can I do this? I know the code above is wrong. Can someone please explain (if it is not too long) the difference between using a '!' or a '.'? Is there a good book on VBA for Access?
Later on what I want to do is be able to press a command button and have all the Job Order IDs that have no value in them yet be replaced with a default value.
If someone could show me how to get to the tables and fields of a query in VBA I woudl love it.
I hope I was descriptive enough!!
Thanks,
NickOne
|
|
|
|
|
I have a Sql table with a primary key id. This field id is set to identety, growing 1.
I add a row to the related DataTable and update it. Then I need the id (which is set from Sql Server)of the updated row. How can I get this id from C# with ADO.NET, Typed DataSet?
Thanks
Stefan
|
|
|
|
|
To get the last identity value you need to execute a
select @@identity
command. I don't know if ADO.NET will do this automatically for you or if you have to do it yourself.
Dave.
|
|
|
|
|
Thank you for the answer.
Is there no way that I get the wrong id? When another table is updated after my update statement and before I can use "select @@identity"?
|
|
|
|
|
You will be OK as long as you don't have any triggers on the table you are updating that also do inserts. If you have triggers, use Chris' option below.
select @@identity returns the last identity value generated in the current session (which is why triggers with inserts can cause issues), so as long as your session doesn't do any other inserts you are OK.
Dave.
|
|
|
|
|
If you're using SQL Server then use
SELECT IDENT_CURRENT('TableName')
This will return the latest Identity value for the Table called 'TableName'.
cheers,
Chris Maunder
|
|
|
|
|
I am writing a database program using ADO and Visual C++.
Let me explain my problem.
m_pConn->Open(L"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=Inventory.mdb;",
L"", L"", adOpenUnspecified );
...
pRs->Open( _variant_t( bstrQuery ),
vNull,
adOpenDynamic,
adLockOptimistic,
adCmdUnknown );
if( m_pRs->Supports( adDelete ) && m_nNoRows > 1 )
{
m_pRs->Delete( adAffectCurrent );
m_pRs->MoveNext();
if( m_pRs->GetADOEOF() )
m_pRs->MovePrevious();
}
CString strFilter = "vField LIKE 'smth%';
m_pRs->Filter = _variant_t( (_bstr_t)strFilter );
//
After I set the filter and I delete a record, if I try to
move to the previous record (even though the record is not
the first) I get an error which suggests that the row where
I am trying to move is either marked for deletion or has
been deleted. Why? The database supports MovePrevious and
I don't use batch mode. This problem occurs only if I
set a filter. The filter works fine too. It selects only
the records that I want it to select.
// Afterall I realized that even my comment lines have bugs
|
|
|
|
|
I have a function within an Oracle database that performs a simple multiplication or division of two data points that the function will query for. The result of the function is then returned to the caller. I have recently become aware that in just about all cases the number being returned is always a five decimal number with correct rounding, but I really require it to return all decimals, or as many as it keeps track off. As a demonstration of the difficulty the following shows an example
select 1.2345 / 1.1111 from dual
1.2345/1.1111
-------------
1.11106111
In this case some kind of truncation occurs, but at least 8 decimals are returned (the 06111 just continues to repeat). Now if I expand the number of decimals for one of the numbers
select 1.2345 / 1.111111 from dual
1.2345/1.111111
---------------
1.11105011
Once again 8 decimals. But now I try
select 1.2345 / 1.11111111 from dual
1.2345/1.11111111
-----------------
1.11105  Why only the five decimals? I need to figure out what are the unique circumstances that are causing the return to limit to five decimals. Any one with some ideas to look at or suggestions, I'll be happy to hear them. Thanks.
Chris Meech
"what makes CP different is the people and sense of community, things people will only discover if they join up and join in." Christian Graus Nov 14, 2002.
"Microsoft hasn't ever enforced its patents. Apparently they keep them for defensive reasons only. Or, they could be waiting 'til they have a critical mass of patents, enforce them all at once and win the game of Risk that they're playing with the world." Chris Sells Feb 18, 2003.
|
|
|
|
|
First of all my assumption is that your precision is set to 8 (8 digits after the decimal point). I don't know what you're doing and to tell you the truth I don't know how to change it because I have never used Oracle. But I can tell you this.
All the calculations that you have shown are correct. You get different results because 1.2345 is being divided by 3 different numbers and that's what the precision shows you.
1.1111 is a different number from 1.111111
The last division
1.2345/1.11111111 = 1.1110500011110500011110500011111 and if you truncate it you get 1.11105000 (the precision is 8). You know that the last zeros are irrelevant so your answer is 1.11105.
Of course it depends on your application how accurate you want to be.
// Afterall I realized that even my comment lines have bugs
|
|
|
|
|
I have written a stored procedure that is called once for each machine in the data table per shift change. Each machine has 200+ records--one for each output. Each output has a code for the material being produced. The stored procedure copies this code from the previous shift data.
Occasionally! the first time the procedure is called after shift change it will copy the codes for about half of the machine and leave NULL in the remaining records. The rest of the machines always appear to have copied successfully.
Any suggestions on how to fix this problem? Either within the SP or the calling code doesn't matter.
Thanx...
>>>-----> MikeO
|
|
|
|
|
You are using terminology in your question that I don't understand. What do you mean by machine? What machine are you talking about? What do you mean by shift change? You are going to have to either explain more about the problem domain (e.g. what a machine/shift change refers to, etc.) or you are going to have to re-phrase your question as a pure database related question (e.g. I am using this sproc (provide code) which gets to a certain point in the process and then dies or inserts nulls when valid fields exist. What's happening?). Send some code and ask your question a bit differently and you'll probably get the answer you need.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
Hi..
I have a table which includes 2 datetime fields. One is Start_DTM, the other is Finish_DTM. It's for employee hours. My typical day would be
Start_DTM Finish_DTM
(date)09.00 (date)13.00
(date)13.00 (date)14.00
(date)14.00 (date)17.00
So, you see it records *everything* including breaks. If I do a SELECT query
where start_DTM or Finish_DTM = (date) then I get the above info.
Of course things get complicated.
Lets say I work a night shift.... like
Start_DTM Finish_DTM
(date)20.00 (date)23.00
(date)23.00 (date+1)01.00
(date+1)01.00 (date+1)04.00
(date+1)04.30 (date+1)05.00
Now... as far as my boss is concerned, I worked from 9pm til 4am on (date).
(Disregard the 4.30 to 5am as it didn't follow on from 4 am)
So when I look for work done on (date) I should also bring back anything
recorded into the next day under certain conditions:
Where there is a record which *starts* on (date) and *finishes* on (date+1),
I have to retrieve that record (the straddle record). Then, I look to see if any other records have a Start_DTM which is identical to the Finish_DTM of
the straddle record. If I do, I take that record's Finish_DTM and try to
find a record which has a Start_DTM identical to that record's Finish_DTM.
Once the continuation stops, the query stops. Hence, I disregard the record
that has Start_DTM (date+1)04.30 because it is later than any previous Finish_DTM that can link it back to (date).
Still with me? congrats!
So.. I am aware of various conditional T-SQL (IF...ELSE, WHILE etc) but
these seem to base their conditions on single items (like an average < 15).
I would like to write a stored procedure to get this data. At the moment, all
I can think of is to set up a temporary table (SQL Server 2000) and add
records to it as I see fit (or add 3 days data and remove as see fit).
I am not aware of how temporary tables affect performance. My goal is
to have a DataAdapter which runs the stored procedure to fill a dataset,
which I will ultimately bind to a WinForms control....
What are my chances? Any tips greatly appreciated!!!
Cheers
John
|
|
|
|
|
Does anyone know where I can find a technical specification for dBASE IV? I looked on www.inprise.com but could not find one. I need to write a program which parses/writes/creates dBASE IV tables (.dbf).
Thanks,
Mark Sanders
sanderssolutions.com
|
|
|
|
|
I used the CodeBase engine for that (don't know a link). On the plus side, it links staticly into your app, it is small, and it is fast. On the down side, the API is unfriendly, but a nice class wrapper solved that problem.
|
|
|
|
|
|
I'm a dBase Specialist. Tell me please what you exactly need to write your programm. Perhaps I can help...
Stefan
|
|
|
|
|
I have an ASP.NET application accessing a SQL Server database. Worked fine last night, this morning I am getting the following error continuously:
System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. at System.Data.SqlClient.SqlConnection.Open() at MyService.DataService.AuthenticateUser(String DomainUsername) in \\Eoctstliciis01\CREQSERVICE\DataService.asmx.vb:line 169 --- End of inner exception stack trace ---
I have checked my code over and over again and I am closing/disposing of all connections as soon as I’ve finished with them. I’m the only person accessing the application at the moment, but I keep getting this error. I’ve checked Process Info / Current Activity in SQL enterprise manager, and there are a normal number of connections open.
· How can I check or set the number of pooled connections I have available?
· Is there a way of increasing the pool size?
Any help would be much appreciated, as I'm currently in a state of panic! 
Thanks,
John.
www.silveronion.com[^]
|
|
|
|
|
Following up on this problem, I think there was an issue with the server I was running this on. I moved the code to my local IIS server and it works fine.
I discovered that PerfMon lets me view the number of connection pools being used. .NET CLR Data / SQLClient:Current # pooled connections.
However – I did some testing by leaving connections open on purpose, the app falls over when it reaches 100 (as expected), but the pooled connection counter never seems to reset back to zero, even when I stop/start IIS. This may be related to the following:
BUG: SqlClient Performance Counters Do Not Reset
ID: Q314429
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314429
I found this on a google search, but the KB Article no longer exists – So I don't know if Q314429 is still an issue
Is there a more reliable way to monitor the number of pooled connections?
Thanks,
John.
www.silveronion.com[^]
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
