That's the solution, thank you very much.
Now it works!
Tino
Search found 39 matches
- Thu 11 Oct 2012 14:07
- Forum: SQL Server Data Access Components
- Topic: CRBatchMove and Identity Columns
- Replies: 3
- Views: 1255
- Thu 11 Oct 2012 09:29
- Forum: SQL Server Data Access Components
- Topic: CRBatchMove and Identity Columns
- Replies: 3
- Views: 1255
CRBatchMove and Identity Columns
Hello,
i want to update an program written with SDAC 3.xxx to actual SDAC version.
In this program i use CRBatchMove for copying table data.
Some of the tables have an identity - column.
So i execute an "set identity_insert [tablename] on" bevore executing crbatchmove.
In the old version it works.
In the new version (6.2. it fails because the crbatchmove automaticly does not include the identity column in the insert - code.
How can i tell crbatchmove, that i want ALL columns, even the identity column too?
Within the mappings - property i do assign the identity column to a source column.
But if i look the final code with SQL profiler i see, the identity column is missed.
So the server can't insert the data.
SQL Server 2008 R2 / Delphi XE2
Thanks for Help
Tino
i want to update an program written with SDAC 3.xxx to actual SDAC version.
In this program i use CRBatchMove for copying table data.
Some of the tables have an identity - column.
So i execute an "set identity_insert [tablename] on" bevore executing crbatchmove.
In the old version it works.
In the new version (6.2. it fails because the crbatchmove automaticly does not include the identity column in the insert - code.
How can i tell crbatchmove, that i want ALL columns, even the identity column too?
Within the mappings - property i do assign the identity column to a source column.
But if i look the final code with SQL profiler i see, the identity column is missed.
So the server can't insert the data.
SQL Server 2008 R2 / Delphi XE2
Thanks for Help
Tino
- Fri 10 Feb 2012 12:32
- Forum: SQL Server Data Access Components
- Topic: Getting ID value after insert
- Replies: 13
- Views: 33846
* SOLVED *
we have to set
MSQuery.Options.ReturnParams:=true!
complete:
1. place an TMSQuery, generate the Scripts
2. set Options -> ReturnParams = true
3. edit TMSQuery.BeforeUpdateExecute:
Code: Select all
procedure Tdm_Projekt.queryBeforeUpdateExecute(
Sender: TCustomMSDataSet; StatementTypes: TStatementTypes; Params: TMSParams);
begin
If stInsert IN statementTypes then // otherwise there is no param with this name
Params.ParamByName('query_ID').ParamType := ptInputOutput;
end;
Regards
Tino
- Fri 10 Feb 2012 10:38
- Forum: SQL Server Data Access Components
- Topic: Getting ID value after insert
- Replies: 13
- Views: 33846
Hi,
i have the same problem with SDAC 6.1.6 / Delphi 2009 / MSSQL 2005.
The sample connects with the northwind - database.
If i set the query.sql property to
and leave all properties at default i get the categoryID after an insert as expected.
If i now use the SQL - Query Builder from SDAC i get the i.e. the update statement i get
But i never get an CategoryID - value after insert / append now.
- Tfield.autogeneratevalue = arDefault / arNone /arAutoInc --> no affect
- TMSQuery.Options.QueryIdentity = true/false --> no affect
- TMSQuery.BeforeUpdateExecute :
Params.ParamByName('CategoryID').ParamType:=ptInputOutput;
--> no affect
If i delete the Update SQL Statement and leave the "SELECT * FROM .." - it will work.
If i set debug=on i see, that Params.ParamByName('Category_ID').ParamType:=ptInputOutput; influences the final code. But i dont get an ID back.
What's wrong with my code?
Thanks für any Help!
Tino
i have the same problem with SDAC 6.1.6 / Delphi 2009 / MSSQL 2005.
The sample connects with the northwind - database.
If i set the query.sql property to
Code: Select all
"SELECT * FROM categories"
If i now use the SQL - Query Builder from SDAC i get the i.e. the update statement i get
Code: Select all
INSERT INTO categories
(CategoryName, Description, Picture)
VALUES
(:CategoryName, :Description, :Picture)
SET :CategoryID = SCOPE_IDENTITY()
- Tfield.autogeneratevalue = arDefault / arNone /arAutoInc --> no affect
- TMSQuery.Options.QueryIdentity = true/false --> no affect
- TMSQuery.BeforeUpdateExecute :
Params.ParamByName('CategoryID').ParamType:=ptInputOutput;
--> no affect
If i delete the Update SQL Statement and leave the "SELECT * FROM .." - it will work.
If i set debug=on i see, that Params.ParamByName('Category_ID').ParamType:=ptInputOutput; influences the final code. But i dont get an ID back.
What's wrong with my code?
Thanks für any Help!
Tino
- Tue 20 Apr 2010 16:49
- Forum: SQL Server Data Access Components
- Topic: I cannot connect to the server
- Replies: 6
- Views: 1749
Perhaps it is tooo simple -
But do you have checked, if sql server authentication is enabled at this server (Management Studio -> select Server -> Settings -> Safety)?
Can you connect with any other tool (i.e. Management Studio) using server auth?
Just one idea (i have had the same problem just a couple of days before )
tino
But do you have checked, if sql server authentication is enabled at this server (Management Studio -> select Server -> Settings -> Safety)?
Can you connect with any other tool (i.e. Management Studio) using server auth?
Just one idea (i have had the same problem just a couple of days before )
tino
- Wed 31 Mar 2010 10:58
- Forum: SQL Server Data Access Components
- Topic: TField.Origin - Problem
- Replies: 7
- Views: 3389
- Wed 31 Mar 2010 06:11
- Forum: SQL Server Data Access Components
- Topic: TField.Origin - Problem
- Replies: 7
- Views: 3389
Hi,
sorry, but i'm here again.
I have updated from SDAC 4.70.0.48 to
SDAC 4.80.0.56
in Version 4.70.xxx TField.Origin returns the origin field name,
in 4.80.xxx it does not.
I have'nt made any other changes within my application, only the SDAC - update.
Is there a difference between theese 2 versions handling Tfield.Origin?
How can i fix it (the dataset is NOT read only)?
thank you, regards
Tino
sorry, but i'm here again.
I have updated from SDAC 4.70.0.48 to
SDAC 4.80.0.56
in Version 4.70.xxx TField.Origin returns the origin field name,
in 4.80.xxx it does not.
I have'nt made any other changes within my application, only the SDAC - update.
Is there a difference between theese 2 versions handling Tfield.Origin?
How can i fix it (the dataset is NOT read only)?
thank you, regards
Tino
- Fri 21 Aug 2009 08:51
- Forum: SQL Server Data Access Components
- Topic: TField.Origin
- Replies: 2
- Views: 1548
- Thu 20 Aug 2009 07:06
- Forum: SQL Server Data Access Components
- Topic: TField.Origin
- Replies: 2
- Views: 1548
TField.Origin
Hi,
i have a problem with Tfield.Origin - Property in a MSQuery-Komponent:
e.g.
MSQuery1.FieldByName('ID1').origin returns "Table1.ID1" - as i expected.
If i set MSQuery1.readonly = true
MSQuery1.FieldByName('ID1').origin returns only "."
Why does SDAC so ?
Thank's for help
Tino
edit: SDAC 4.70.0.48, Delphi 2009
i have a problem with Tfield.Origin - Property in a MSQuery-Komponent:
e.g.
Code: Select all
CREATE TABLE Table1(
ID1 Integer NOT NULL,
Data nVarchar(50)
)
SELECT Table1.ID1, Table1.Data FROM Table1
If i set MSQuery1.readonly = true
MSQuery1.FieldByName('ID1').origin returns only "."
Why does SDAC so ?
Thank's for help
Tino
edit: SDAC 4.70.0.48, Delphi 2009
- Thu 08 Jan 2009 10:36
- Forum: SQL Server Data Access Components
- Topic: FetchAll=False and Locks - Problem
- Replies: 2
- Views: 2032
FetchAll=False and Locks - Problem
Hi,
i have this problem, maybe i did'nt understand the FetchAll - Idea as well.
Situation:
A table i.g. Customers with up to 100.000 Records, additional up to 20 related tables in master-detail relation to this maintable.
Form[1]:
Based on the query SELECT * FROM Customers WHERE ID = [one id] it shows one record with all relations. First i open the query on base table, after that i open the details tables.
It works fine and quick.
All tables are FetchAll = True, editing data works well.
Form[2]:
Only a crDBGrid + Datasource + MSQuery with SELECT [some fields] FROM Customers.
MSQuery.Fetchall=false, MSQuery.Readonly=true,
MSQuery.Cursortype=ctDefaultResultSet (because of enabling local sorting)
My Idea is: first search with form[2] one record, than show detail data by calling form[1]. If i single user, it works fine.
But in Multi -User enviorment my Problem is:
The Form[2] (with the fetchall=false - query) locks records. Depending on sorting in the crdbgrid there are sometimes many, many locks at the DB - Tables. These locks prevent other users from changing data.
But i dont want any locking while searching with my Form[2].
How can i configure SDAC, that there are no locks when fetchall=false ?
Thanks
Tino
Edit:
Latest SDAC,
MSSQL Server 2000
Delphi 2006
i have this problem, maybe i did'nt understand the FetchAll - Idea as well.
Situation:
A table i.g. Customers with up to 100.000 Records, additional up to 20 related tables in master-detail relation to this maintable.
Form[1]:
Based on the query SELECT * FROM Customers WHERE ID = [one id] it shows one record with all relations. First i open the query on base table, after that i open the details tables.
It works fine and quick.
All tables are FetchAll = True, editing data works well.
Form[2]:
Only a crDBGrid + Datasource + MSQuery with SELECT [some fields] FROM Customers.
MSQuery.Fetchall=false, MSQuery.Readonly=true,
MSQuery.Cursortype=ctDefaultResultSet (because of enabling local sorting)
My Idea is: first search with form[2] one record, than show detail data by calling form[1]. If i single user, it works fine.
But in Multi -User enviorment my Problem is:
The Form[2] (with the fetchall=false - query) locks records. Depending on sorting in the crdbgrid there are sometimes many, many locks at the DB - Tables. These locks prevent other users from changing data.
But i dont want any locking while searching with my Form[2].
How can i configure SDAC, that there are no locks when fetchall=false ?
Thanks
Tino
Edit:
Latest SDAC,
MSSQL Server 2000
Delphi 2006
- Wed 08 Oct 2008 05:01
- Forum: SQL Server Data Access Components
- Topic: non-trial beta required
- Replies: 5
- Views: 3024
- Wed 24 Sep 2008 05:30
- Forum: SQL Server Data Access Components
- Topic: D2009
- Replies: 5
- Views: 3435
- Wed 09 Jul 2008 14:57
- Forum: SQL Server Data Access Components
- Topic: SDAC 4.50 Stored Procedure Parameters
- Replies: 8
- Views: 3535
Hi,
i am not sure, but please try to specify the parameter types:
i am not sure, but please try to specify the parameter types:
Code: Select all
...
Query.Prepared := True;
Query.Params.ParamByName('FirstParam').datatype := ftInteger;
Query.Params.ParamByName('FirstParam').AsInteger := 1;
Query.Params.ParamByName('SecondParam').datatype := ftBoolean;
Query.Params.ParamByName('SecondParam').AsBoolean := False;
Query.Open; // !!! HERE IS AN EXCEPTION (EMSError)
...
- Tue 08 Jul 2008 08:23
- Forum: SQL Server Data Access Components
- Topic: LocateEx and segmented keys
- Replies: 3
- Views: 4049
Many thank's for testing.
My Demo - App did work now, my 'really' app unfortunally not. But I've found the solution for that :
I call locateEx on a table like this:
SDAC does not recognize the user column - type "ID".
If i change the column data type to numeric(18,0) instead of 'ID' (what means the same) it works !
Well, i'ts not neccesary for SDAC to 'parse' user defined types, i have to change my database.
But, sorry, now i've a second problem:
See my Example
If i locate [4,31.12.1996] i get [4, 03.01.1997] - perfect.
If i locate [3 , 01.05.1998] i expect [4 , 08.07.1996] because this is the next record 'greater than the specified values'. But the record pointer does'nt moves, there is no effect if i 'search' such keys.
Is this a bug or a feature ?
Thanks again for help
Tino
My Demo - App did work now, my 'really' app unfortunally not. But I've found the solution for that :
I call locateEx on a table like this:
Code: Select all
EXEC sp_addtype N'ID', N'numeric(18,0)', N'null'
go
CREATE TABLE MyTable
(
Variant_ID ID,
Date SmallDateTime,
...
)
If i change the column data type to numeric(18,0) instead of 'ID' (what means the same) it works !
Well, i'ts not neccesary for SDAC to 'parse' user defined types, i have to change my database.
But, sorry, now i've a second problem:
See my Example
Code: Select all
empID, Orderdate
...
3 , 30.04.1998
4 , 08.07.1996
....
4 , 20.12.1996
4 , 03.01.1997
...
If i locate [3 , 01.05.1998] i expect [4 , 08.07.1996] because this is the next record 'greater than the specified values'. But the record pointer does'nt moves, there is no effect if i 'search' such keys.
Is this a bug or a feature ?
Thanks again for help
Tino
- Fri 04 Jul 2008 04:39
- Forum: SQL Server Data Access Components
- Topic: LocateEx and segmented keys
- Replies: 3
- Views: 4049
LocateEx and segmented keys
Hi,
i try to use TMSQuery.LocateEx in this way:
(SQL Server DAC 4.50.0.35, BDS (Delphi) 2007, connect to northwind - DB SQL-Server 2000)
In documentation i read:
It seems, the second field is unused for finding the record.
Is it a problem especially with datetime - columns or do i sth. wrong ?
Thanks
Tino
P.S. Demo - App is available if needed.
i try to use TMSQuery.LocateEx in this way:
(SQL Server DAC 4.50.0.35, BDS (Delphi) 2007, connect to northwind - DB SQL-Server 2000)
Code: Select all
Var empID:Integer;
date:TDateTime;
MSQuery1.SQL.Text:="SELECT * FROM Orders ORDER BY EmloyeeID,OrderDate";
MSQuery1.IndexFieldNames:="EmployeeID;OrderDate";
MSQuery1.Open;
empID:=4;
date:=StrToDate('01.01.1997');
MSQuery1.locateEx('EmployeeID;Orderdate',VarArrayOf([empID,date]),[lxNearest])
Code: Select all
Sample - Data in Orders:
empID, Orderdate
...
3 , 30.04.1998
4 , 08.07.1996
....
4 , 20.12.1996
4 , 03.01.1997
...
I expect, that my locateEX() - statement positions at [4, 03.01.1997], but it sets the pointer to [4,08.07.1996] - the first record with employeeID = 4lxNearest
LocateEx moves the cursor to a specific record in a dataset or to the
first record in the dataset that is greater than the values specified in
the KeyValues parameter. For this option to work correctly the dataset should be sorted by the fields the search is performed in. If the dataset is not sorted, the function may return a line that is not connected with the search condition
It seems, the second field is unused for finding the record.
Is it a problem especially with datetime - columns or do i sth. wrong ?
Thanks
Tino
P.S. Demo - App is available if needed.