Devart Forums

Discussion forums for open issues and questions concerning database tools, data access components and developer tools from Devart
https://forums.devart.com/

Views + autorefresh

https://forums.devart.com/viewtopic.php?f=6&t=31946

Page 1 of 1

Views + autorefresh

Posted: Fri 12 Jun 2015 21:21
by ccmcbride
Using Delphi 2010, sDac 6.11.23, MSSQL 2008 R2.
If I have a table component attached to a view,
and I call quickrefresh on the table,
then I get an error: (listed below)
(and yes, both RecUID and edittimestamp are fields in the view)

bService is the base table, I am selecting from the view table (service))
In the error message, it's my 'real' code. I have test code to recreate the issue, where all I do is call quickrefresh on the table component.

so - 'real' table is bservice, and viewname is service

component.tablename = 'Service'
component.quickrefresh(true) = error.


---------------------------6/12/2015 12:34:05 PM---------------------------
Exception 'EMSError' in module sme5.exe at 00289F5E
The multi-part identifier "bService.RecUID" could not be bound.
The multi-part identifier "bService.RecUID" could not be bound.

Module: OLEDBAccess, Source: UNKNOWN, Line UNKNOWN
Procedure: __linkproc__ TOLEDBConnection::OLEDBError

Call stack:
:0068AF5E [sme5.exe] __linkproc__ TOLEDBConnection::OLEDBError
:76CFC42D [KERNELBASE.dll]
Recursive call (3 times):
:0068AF5E [sme5.exe] __linkproc__ TOLEDBConnection::OLEDBError
:0068A7F5 [sme5.exe] __linkproc__ TOLEDBConnection::Check
:0068E470 [sme5.exe] __linkproc__ TOLEDBCommand::Check
:00694011 [sme5.exe] __linkproc__ TOLEDBCommand::GetNextResult
:0069248C [sme5.exe] DoExecute
:006925E7 [sme5.exe] __linkproc__ TOLEDBCommand::Execute
:00676CFB [sme5.exe] __linkproc__ TCRRecordSet::ExecCommand
:0069994A [sme5.exe] __linkproc__ TOLEDBRecordSet::ExecCommand
:0064C4C9 [sme5.exe] __linkproc__ TCustomDADataSet::InternalExecute
:006D3C5A [sme5.exe] __linkproc__ TCustomMSDataSet::InternalExecute
:0064C85C [sme5.exe] __linkproc__ TCustomDADataSet::Execute
:00567C73 [sme5.exe] __linkproc__ GetIsClass
:00662A61 [sme5.exe] __linkproc__ TDBAccessUtils::Execute
:006560E4 [sme5.exe] __linkproc__ TDADataSetUpdater::UpdateExecute
:006577D2 [sme5.exe] __linkproc__ TDADataSetUpdater::PerformSQL
:00658E11 [sme5.exe] __linkproc__ TDADataSetUpdater::PerformRefreshQuick
:0064D8B7 [sme5.exe] __linkproc__ TCustomDADataSet::InternalRefreshQuick
:006D3C1E [sme5.exe] __linkproc__ TCustomMSDataSet::InternalRefreshQuick
:006D4BA1 [sme5.exe] __linkproc__ TCustomMSDataSet::RefreshQuick
:006D35B9 [sme5.exe] __linkproc__ TCustomMSDataSet::AutoRefreshTimer
:00590662 [sme5.exe] __linkproc__ TWin32Timer::Timer
:00590407 [sme5.exe] __linkproc__ TimerWndProc
:753C62FA [USER32.dll]
:753C6D3A [USER32.dll]
:753C6CE9 [USER32.dll]
:753C77C4 [USER32.dll]
:753C788A [USER32.dll]
:0055A1DA [sme5.exe] __linkproc__ TApplication::ProcessMessage
:0055A21F [sme5.exe] __linkproc__ TApplication::HandleMessage
:0055A54A [sme5.exe] __linkproc__ TApplication::Run
:0055A58D [sme5.exe] __linkproc__ TApplication::Run
:024B5DE9 [sme5.exe]
:75FA338A [kernel32.dll]
:77779F72 [ntdll.dll]
:77779F45 [ntdll.dll]

Modules:
$00400000 (00400000,00001000,020B4A00) C:\DevCode2010\SME9.0\bin\sme5.exe
$76CF0000 (76CF0000,00001000,0003F800) C:\Windows\syswow64\KERNELBASE.dll
$753B0000 (753B0000,00010000,0006CC00) C:\Windows\syswow64\USER32.dll
$75F90000 (75F90000,00010000,000D0000) C:\Windows\syswow64\kernel32.dll
$77740000 (77740000,00010000,000D5C00) C:\Windows\SysWOW64\ntdll.dll

Main Thread ID = 00000B10, Current Thread ID = 00000B10
Registers:
EAX = 0022F93C CS = 0023 EIP = 76CFC42D Flags = 00200206
EBX = 00680BE4 SS = 002B ESP = 0022F93C EBP = 0022F98C
ECX = 00000007 DS = 002B ESI = 80040E14 FS = 0053
EDX = 00000000 ES = 002B EDI = 00000000 GS = 002B
Code at CS:EIP
C9 C2 10 00 CC CC CC CC CC 8B FF 55 8B EC 56 8B
Stack:
0EEDFADE 00000001 00000000 76CFC42D 00000007
00000000 77779F45 00406005 11B70FAC 00465111
10CD746C 0932F711 00000000 10CD7410 77779F45
FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF 00000001

Additional info:
EMSError
Exe Version : 8.9.9.99 User :ADMIN

---------------------------6/12/2015 12:48:46 PM---------------------------
Exception 'EMSError' in module sme5.exe at 00289F5E
The multi-part identifier "bService.EditTimeStamp" could not be bound.

Module: OLEDBAccess, Source: UNKNOWN, Line UNKNOWN
Procedure: __linkproc__ TOLEDBConnection::OLEDBError

Call stack:
:0068AF5E [sme5.exe] __linkproc__ TOLEDBConnection::OLEDBError
:76CFC42D [KERNELBASE.dll]
Recursive call (3 times):
:0068AF5E [sme5.exe] __linkproc__ TOLEDBConnection::OLEDBError
:0068A7F5 [sme5.exe] __linkproc__ TOLEDBConnection::Check
:0068E470 [sme5.exe] __linkproc__ TOLEDBCommand::Check
:00694011 [sme5.exe] __linkproc__ TOLEDBCommand::GetNextResult
:0069248C [sme5.exe] DoExecute
:006925E7 [sme5.exe] __linkproc__ TOLEDBCommand::Execute
:00676CFB [sme5.exe] __linkproc__ TCRRecordSet::ExecCommand
:0069994A [sme5.exe] __linkproc__ TOLEDBRecordSet::ExecCommand
:0064C4C9 [sme5.exe] __linkproc__ TCustomDADataSet::InternalExecute
:006D3C5A [sme5.exe] __linkproc__ TCustomMSDataSet::InternalExecute
:0064C85C [sme5.exe] __linkproc__ TCustomDADataSet::Execute
:00567C73 [sme5.exe] __linkproc__ GetIsClass
:00662A61 [sme5.exe] __linkproc__ TDBAccessUtils::Execute
:006560E4 [sme5.exe] __linkproc__ TDADataSetUpdater::UpdateExecute
:006577D2 [sme5.exe] __linkproc__ TDADataSetUpdater::PerformSQL
:00658E25 [sme5.exe] __linkproc__ TDADataSetUpdater::PerformRefreshQuick
:0064D8B7 [sme5.exe] __linkproc__ TCustomDADataSet::InternalRefreshQuick
:006D3C1E [sme5.exe] __linkproc__ TCustomMSDataSet::InternalRefreshQuick
:006D4BA1 [sme5.exe] __linkproc__ TCustomMSDataSet::RefreshQuick
:020971AC [sme5.exe] __linkproc__ TfrmOrders::DeleteMatItem (fOrders.pas, line 3991)
:020A41AC [sme5.exe] __linkproc__ TfrmOrders::btnOrderDeleteClick (fOrders.pas, line 6832)
:00529E29 [sme5.exe] __linkproc__ TControl::Click
:00D00E1B [sme5.exe] __linkproc__ TAdvCustomGlowButton::Click (advglowbutton.pas, line 3252)
:0052A2CE [sme5.exe] __linkproc__ TControl::WMLButtonUp
:00D018CE [sme5.exe] __linkproc__ TAdvCustomGlowButton::WMLButtonUp (advglowbutton.pas, line 3736)
:005298BB [sme5.exe] __linkproc__ TControl::WndProc
:007F6258 [sme5.exe] __linkproc__ TdxMessagesController::IsMessageLocked
:007F638A [sme5.exe] __linkproc__ TdxMessagesController::BlockLockedMessage
:753D62D5 [USER32.dll]
:00779B4E [sme5.exe] __linkproc__ TdxSystemHook::ProcessHookProcs
:0052DDB4 [sme5.exe] __linkproc__ TWinControl::WndProc
:7775FA6D [ntdll.dll]
:75FA3016 [kernel32.dll]
:75FA302B [kernel32.dll]
:753D0DE3 [USER32.dll]
:753D2AC1 [USER32.dll]
:0052DA18 [sme5.exe] __linkproc__ TWinControl::IsControlMouseMsg
:0052450B [sme5.exe] __linkproc__ FindControl
:0052E185 [sme5.exe] __linkproc__ TWinControl::WndProc
:00779B4E [sme5.exe] __linkproc__ TdxSystemHook::ProcessHookProcs
:0052D823 [sme5.exe] __linkproc__ TWinControl::MainWndProc
:0045B04E [sme5.exe] __linkproc__ StdWndProc
:753C62FA [USER32.dll]
:753C6D3A [USER32.dll]
:753C6CE9 [USER32.dll]
:753C77C4 [USER32.dll]
:753D0751 [USER32.dll]
:0055B386 [sme5.exe] __linkproc__ TApplication::StopHintTimer
:753C788A [USER32.dll]
:0055A1DA [sme5.exe] __linkproc__ TApplication::ProcessMessage
:0055A21F [sme5.exe] __linkproc__ TApplication::HandleMessage
:0055A54A [sme5.exe] __linkproc__ TApplication::Run
:0055A58D [sme5.exe] __linkproc__ TApplication::Run
:024B5DE9 [sme5.exe]
:75FA338A [kernel32.dll]
:77779F72 [ntdll.dll]
:77779F45 [ntdll.dll]

Modules:
$00400000 (00400000,00001000,020B4A00) C:\DevCode2010\SME9.0\bin\sme5.exe
$76CF0000 (76CF0000,00001000,0003F800) C:\Windows\syswow64\KERNELBASE.dll
$753B0000 (753B0000,00010000,0006CC00) C:\Windows\syswow64\USER32.dll
$75F90000 (75F90000,00010000,000D0000) C:\Windows\syswow64\kernel32.dll
$77740000 (77740000,00010000,000D5C00) C:\Windows\SysWOW64\ntdll.dll

Main Thread ID = 00000B10, Current Thread ID = 00000B10
Registers:
EAX = 0022F698 CS = 0023 EIP = 76CFC42D Flags = 00200212
EBX = 00680BE4 SS = 002B ESP = 0022F698 EBP = 0022F6E8
ECX = 00000007 DS = 002B ESI = 80040E14 FS = 0053
EDX = 00000000 ES = 002B EDI = 00000000 GS = 002B
Code at CS:EIP
C9 C2 10 00 CC CC CC CC CC 8B FF 55 8B EC 56 8B
Stack:
0EEDFADE 00000001 00000000 76CFC42D 00000007
00000000 77779F45 00406005 11B6E39C 00465111
1190BDBC 0932F711 00000000 10CD7410 77779F45
FFFFFFFF FFFFFFFF FFFFFFFF FFFFFFFF 00000001

Re: Views + autorefresh

Posted: Mon 15 Jun 2015 08:39
by azyk
To generate a SQL query for the RefreshQuick method, we are using meta-information about the name of the database object, that owns each field. SQL Server returns only the name of the table, that owns the field, and doesn't return the name of the view. To solve the problem, use the VIEW_METADATA option when creating the view. For example:

Code: Select all

CREATE VIEW dbo.Service
WITH VIEW_METADATA
AS
SELECT *
FROM dbo.bservice

Re: Views + autorefresh

Posted: Thu 25 Jun 2015 22:17
by ccmcbride
okay, that fixed that issue, but it caused another one.
with the view_metadata, my program can add and edit records, using the view, but can't delete records.
with it added,
DELETE FROM PropTask
WHERE
RecUID = ?
View or function 'PropTask' is not updatable because the modification affects multiple base tables.

without, it works.

is there a way I can have both?
(this is from a datatable.delete)

Re: Views + autorefresh

Posted: Fri 26 Jun 2015 09:12
by azyk
The specified error message on record deleting is returned by SQL Server. To avoid the error message, you should create an INSTEAD OF DELETE trigger for this view. See more details about how to do this in SQL Server documentation (the INSTEAD OF Triggers section): https://technet.microsoft.com/en-us/lib ... l.80).aspx
All times are UTC
Page 1 of 1
Powered by phpBB® Forum Software © phpBB Limited
https://www.phpbb.com/