473,394 Members | 1,554 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,394 software developers and data experts.

Problems inserting rows using OLEDB and views

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?

Lastly: I have several silly looking restrictions using views in
SQLServer. As I'm not familiar with other databases I'm curious if these
are universal restrictions, or just lazy programming at Microsoft? For
instance, combining tables to create a view has many restrictions, and
inserting or updating through these views is quite tricky.

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

--
Marko Poutiainen | These are my principles.
me*@paju.oulu.fi | If you don't like them, I have others.
http://www.toffeeweb.com | -Groucho Marx
Jul 20 '05 #1
0 2411

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Marko Poutiainen | last post by:
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,...
3
by: Joachim Klassen | last post by:
Hi all, first apologies if this question looks the same as another one I recently posted - its a different thing but for the same szenario:-). We are having performance problems when...
4
by: Deepankar | last post by:
Hi, I was trying to change an example for SQL Server to work with Access db to insert image data. I have everything working except getting the OleDbParameter type for the image column. The...
9
by: Curt Emich | last post by:
I've got some code that I'm trying to enclose in a try/catch block. This code is in the Page_Load() method of the code-behind for an .aspx page. On the line where the "try" keyword is, I'm...
2
by: Mattyw | last post by:
Hi I have a sqlcommand that returns all the rows in a column and then pass that to a datareader. I am new to VS.Net and so far I can only return the first row in the first column using ...
6
by: Pushpendra Vats | last post by:
Hi , I am trying to insert records into database. I am trying to write the following code. On button1 click event i am inserting five records and after that i am calling the update method of...
0
by: Michael Manuel via .NET 247 | last post by:
I seem to be having a lot of trouble using the CheckBoxList Control. I have created two pages to try and find out what I have been doing wrong. Both pages are causing me problems but different...
1
by: Ray Nimmo | last post by:
This is driving me nuts, been at it now for the last week, anyone any ideas on wher im going wrong? using a list box pipeLB and a date/time picker pDate, im trying to populate the list box with...
0
by: tom c | last post by:
I am going through "Walkthrough: Editing and Inserting Data in Web Pages with the DetailsView Web Server Control" found at http://msdn2.microsoft.com/en-us/library/sdba1d59.aspx I am using...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.