Problems inserting rows using OLEDB and views

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problems inserting rows using OLEDB and views

Author

Topic

Makis
Starting Member

1 Post

Posted - 2004-04-14 : 10:25:06

Situation:

We had to make our SQLServer 2000 database multi-lingual. That is, certain things (such as product names) in the database should be shown in the language the user is using (Finnish, Swedish or English). There are about a dozen tables with columns that need localization.

Doing this in the application level was a no-goer. It would have taken far too much time (there is a *lot* of code and unfortunately most of the multi-lingual tables are very central to the system).

Solution:

The easiest solution we could think of was to create views with similar names to the tables that included columns that need to be multi-lingual and create tables holding the different multi-lingual columns. See the examples on how we did this (notice that we also have UPDATE and DELETE triggers not included here).

We did run into some problems, namely SQLServer's notation for outer joins (*= and =*) wouldn't work with the views. So we had to remove those from queries that used one of the multi-lingual tables. Thankfully there weren't too many places where this problem occured so I fixed them in about two days.

Additional testing in Query Analyzer looked good: we could insert, update and select rows through the views.

Then came the bummer. Running one of our applications and inserting values using ADODB.Recordset gave the very helpful "Multi-step OLE DB operation caused an error" message. I have commented the code below to show where the error occurs. I included line numbers for clarity.

Fixing every place where we use this notation is not really an option either.

How to reproduce the error:

1. Create a database user and a login name, both named "england".
2. Run the SQL scripts in Query Analyzer.
3. Create a VB exe project.
4. Add a command button to to the form.
5. Paste the code to the form's code window. Fix the parameter for ADODB.Connection() function (line 20).
6. Add a breakpoint to the start of the DoStuff() function.
7. Walk through the code with F8 and observe where the error happens (line 130).

My analysis:

As far as I can tell, the problem within the Microsoft Cursor Engine. I think the problem is that MCE is too smart, and therefore can't figure out which table it should use in the insertion. The error I get is "The value violated the data source schema constraint for the field", and originator was MCE.

So, is there a way around this?

Or is there some other way of doing what we are trying to do?

SQL Queries:


CREATE TABLE dbo.TestTable
(
	ID Int Identity(1,1) NOT NULL,
	Name Varchar(10) NOT NULL,
	Misc Varchar(10),
	CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
	(
		[ID]
	) WITH FILLFACTOR = 90 ON [PRIMARY]
)

GO

CREATE TABLE england.TestTable_LT
(
	ID Int NOT NULL,
	Name Varchar(10),
	CONSTRAINT [KF_LT_TestTable] FOREIGN KEY
	(
		[ID]
	) REFERENCES dbo.TestTable (
		[ID]
	)
)

GO


CREATE VIEW england.TestTable
AS
SELECT  ID = CASE TBL.ID 
		WHEN -8 THEN NULL
		ELSE TBL.ID
	END,
	CASE WHEN LT.Name IS NOT NULL
	THEN	LT.Name
	ELSE    TBL.Name
	END As Name,
	TBL.Misc
	FROM dbo.TestTable TBL
	LEFT JOIN england.TestTable_LT LT ON LT.ID = TBL.ID
GO

CREATE TRIGGER [england].trg_TestTable_LT_insert 
ON [england].[TestTable] INSTEAD OF INSERT AS

BEGIN
SET NOCOUNT ON
INSERT INTO dbo.TestTable(Name, Misc) SELECT Name, Misc FROM inserted

INSERT INTO england.TestTable_LT (ID, Name) SELECT SCOPE_IDENTITY(), Name FROM inserted

END

GO

VB Code:


Option Explicit

Private Sub Command1_Click()
    DoStuff
End Sub


Private Sub DoStuff()
          Dim conn As ADODB.Connection
          Dim rst As ADODB.Recordset
          Dim cmd As ADODB.Command
          
10        Set conn = New ADODB.Connection
          
          ' Fix following line:
20        conn.Open "PROVIDER=SQLOLEDB;SERVER=w2kts1;DATABASE=Rahti16;UID=sverige;PWD=sverige"
          
          ' METHOD 1
          
30        Set cmd = New ADODB.Command
40        cmd.ActiveConnection = conn
          ' The following insert works as imagined
50        cmd.CommandText = "INSERT INTO TestTable(Name, Misc) VALUES('Foo', 'Bar')"
60        cmd.Execute
          
          
          ' METHOD 2 - Does not work
          
70        Set rst = New ADODB.Recordset
80        rst.CursorLocation = adUseServer
90        rst.Open "SELECT * FROM TestTable WHERE ID = 0", conn, adOpenDynamic, adLockOptimistic
100       With rst
              
110           .AddNew
              
120           !Misc = "Foo"  ' This works
130           !Name = "Bar"  ' But this bombs

140           .Update
              
150       End With
          
End Sub

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources