|
Try executing this from SqlAdvantage to see what it returns. What is @@IDENTITY ? Is it an int or some other data type?
|
|
|
|
|
Does anyone have an idea of how you can retrieve/return the value of a primary key (IDENTITY/Autoincrement COL) value after an INSERT statement using ODBC or ADO (without a stored proc). I am not using a stored proc, and so cannot use the @@IDENTITY function. I am using Sybase SQL Anywhere and MS SQL Server 7 as my databases.
Any suggestions or code samples would be appreciated.
|
|
|
|
|
Just after the statement that execute the INSERT.
eg:
SQL = "your insert statement"
set rs=mycon.execute(SQL)
'assuming that your identity field name is Key1
response.write("New key: " & rs("Key1"))
hope this works for ya
|
|
|
|
|
select @@identity as ID should do the trick.
Then ID = rs("ID")
cheers,
Chris Maunder
|
|
|
|
|
idCMD = New OleDb.OleDbCommand("SELECT @@IDENTITY", Me.conn)
Should work with any database that supports an indentity column.
Best Regards,
Bob Zagars
|
|
|
|
|
I'm trying to show the headers of all the records in a listbox :
1. is there a easiest/better fitted control for showing headers of records ?
2. whats my next step for loading a dialog with all Record Data after a row had been selected ? How do i get the index of the Record (is there a return value in ListBox ??) or, How to retrive the Record from the ListBox ?
Thanks for your help,
Ariel.
|
|
|
|
|
Hi there,
Well i'm trying to figure out a way to lock a record when i'm editting it and it seems i can't find the way.
First, I'm using ODBC and my DB is both in Access and FoxPro - i'm not sure wether the lock of the record is handled by ODBC Drivers or the DB itself - so if somone can help me with that i'll be more than happy.
Second, I'm trying to change the CRecordset::SetLockingMode(CRecordset::pessimistic) and i get an exception which tells me i'm actualy back to default CRecordset::Optimistic (Locking Mode) - those this have to do with my ODBC Driver or my Access DB.
Third, is there a flag in CRecordset which tells me whether the Record is edited by another user or only the ODBC/DB (???) handles and know it ?
Actually what i'm asking in a one sentence is : Can i check if a record is edited by another user in another process or i have to put my own Semaphore for this ???
Thank you alot for reading all this and much more if help given,
Ariel.
|
|
|
|
|
Record locking is handled by the database engine itself...
- Anders
|
|
|
|
|
First, Thanks for your answer !
Second, Since the DataBase is Locking the Records is there a way i can check wether the record is locked in a specific moment ? Is there a way i can know if the record is editted right now ?
Ariel
|
|
|
|
|
I am trying to create a MS Access DB with ADO.I can't find any ADO method to do that !
I tried with ADOX .But using Catalog object I could create a database in the specified drive ,but can't open it!
Could anyone help!
Y.Yamini Devi
|
|
|
|
|
I am trying to create a MS Access DB with ADO.I can't find any ADO method to do that !
I tried with ADOX .But using Catalog object I could create a database in the specified drive ,but can't open it!
Could anyone help!
Y.Yamini Devi
|
|
|
|
|
I'm using ASP/ADO and Access2000
I've performed an insert and i'm trying to return the primary key of the last modified record.
Can anyone please help.
Thanks
Marc
|
|
|
|
|
I have a table with a field defined as the primary key and as auto incremented.
Does anyone have an idea on how to insert data into the table overriding the auto increment property, so that I can insert my vaalues for this field as well.
I want to know if there is a way to do this using ADO and the Open and AddNew methods.
Kostas Stefanou
|
|
|
|
|
You can use something like
SET IDENTITY_INSERT T1 ON
INSERT INTO T1 (column_1,column_2)
VALUES (-99,'Explicit identity value')
- Anders
|
|
|
|
|
At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, Microsoft® SQL Server™ returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.
If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.
The setting of SET IDENTITY_INSERT is set at execute or run time and not at parse time.
|
|
|
|
|
Is it possible to some how pass IN a recordset to a stored procedure? I want to be able to do something like the following:
Alter Procedure uspBillClient
@RS Recordset
As
Begin
Update Bills
Set BillID = (Select MAX(BillID) From Bills) + 1
Where HoursID In RS
End
Thanks in advance for any help.
Jamie Nordmeyer
Portland, Oregon, USA
|
|
|
|
|
Nope, you can not do that...
Why don't you just "build" the recordset inside the stored procedure, passing the needed parameters?
Maybe, if you supplied some more details about what you would like to do, we might be able to help you. 
- Anders
|
|
|
|
|
My application tracks the amount of time that one of my company's agents spends on a particular project. Everything is fine and dandy until it comes time to compile a bill. The application query's the SQLServer database for all records whose 'BillID' is 0, meaning that it hasn't been billed yet (when a record is billed, it'll get a new BillID). The application uses this disconnected recordset to build an Excel spreadsheet. The third and final step of the billing process is to go back and update the 'BillID' fields of the billed records.
This should work 99.9% of the time. Where this plan fails is if another agent adds a new record to the database in the middle of the billing cycle. For example, a record might be added during the Excel phase (if you've ever done anything with the Excel Object Library, you know that it takes a few seconds to build a substantial report). When the application goes back to update the database, the new record will have a 'BillID' of 0, since it's a new record and hasn't been billed, and will thus be updated with a new 'BillID'. This is bad, because the new record hasn't really been billed (it wasn't added to the report). The whole process works like this in summary:
1. Get disconnected recordset containing all records whose 'BillID' is 0
2. Compile an Excel Spreadsheet based on this recordset
3. Set all records whose 'BillID' is 0 to a new non-zero number (here is where a record might be mis-billed)
My solution for the time being was to add a couple of methods to my 'Update' COM Object (I have two objects, Query and Update) called BeginBilling and EndBilling that look like this (in VB):
Public Sub BeginBilling()
GetObjectContext.DisableCommit
End Sub
Public Sub EndBilling()
GetObjectContext.EnableCommit
End Sub
I call BeginBilling at the start of the billing process to prevent MTS from closing the transaction (some what wrapping the Excel procedure in the transaction), and then calling EndBilling when everythings has completed. This seems somewhat hoky to me, though, and thus, the posted question.
Jamie Nordmeyer
Portland, Oregon, USA
|
|
|
|
|
Just a suggestion here.
Instead of retreiving all the rows with BillID = 0, initially update all rows with BillID = 0 but set the BillID to something like -1. Then retreive all rows with -1, produce the report and the update the -1 BillID to the new non zero positive value.
Alternatively, if you can add a new column to the table to indicate processing status or state, that would be the most desirable solution.
Chris
|
|
|
|
|
Good idea, Chris. Thanks! 
Jamie Nordmeyer
Portland, Oregon, USA
|
|
|
|
|
How the heck did I get here? There seem to be eddies in the Space-Time continuum.
Do you want to know more?
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
|
|
|
|
|
|
Sorry I can't help with specific SQL Server stuff, but in an Oracle database I would do something like the following;
create or replace procedure FindBillingID
as
cursor BillingID /* define your Recordset here */
begin
for BillRec in BillingID cursor loop
uspBillClient(BillRec.BillingID);
end loop;
end FindBillingID;
The uspBillClient is then pretty much as you wrote except that it's IN parameter is of a different type and it get's repetitively called.
Hope this is kinda what you are looking for.
Chris
|
|
|
|
|
http://www.csharpindex.com/articles/2001/01/MiddleTier.htm
|
|
|
|
|
I am writing a VB app (called FISWDC) with a data environment (called DataEnv). I
believe I should be able to do:
DataEnv.Connection.connectionString = "File Name=" & App.Path & "\FISWDC.UDL;"
or, at least
DataEnv.Connection.connectionString = "File Name=FISWDC.UDL;"
or maybe
DataEnv.Connection.Open "File Name=" & App.Path & "\FISWDC.UDL;"
or something like that, but whatever I do I just keep getting the error
"The parameter is incorrect"
or
"Connection was denied. New connection you requested has different characteristics than the one already in use."
Can anyone help?
Dave
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
