Inserting via a view

Hello All, I have an strange issue regarding inserts via a view. Below are
the environemnts and the code. Both environments are identical however,
service packs a different.
Environment 1: This environmnet the insert works
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Windows 2k sp4
Environment 2: This envirnoment the insert fails
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
Here is the insert statment;
INSERT INTO vMoxyStandardRestriction ( R.RestrictionID,
R.RestrictionType,
R.SecAssocType,
R.RestrictionName,
R.CanOverride,
D.TranCodeBits,
D.MinCompRestPercent,
D.MaxCompRestPercent,
D.IsAggregate,
D.RoundOption,
D.RoundFactor,
D.MinShares,
D.MinValue,
D.CompType)
VALUES (11, 1, 16, 'Unreconciled', 1, 12, 0.000000000000000e+000,
0.000000000000000e+000, 0, 0, 0.000000000000000e+000, 0.000000000000000e+000
,
0.000000000000000e+000, 0)
Here is the code to the view
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER VIEW vMoxyStandardRestriction AS
SELECT R.RestrictionID, R.RestrictionType, R.SecAssocType,
R.RestrictionName, R.CanOverride,
D.TranCodeBits, D.MinCompRestPercent, D.MaxCompRestPercent, D.IsAggregate,
D.RoundOption, D.RoundFactor, D.MinShares, D.MinValue, D.CompType
FROM MoxyRestriction R, MoxyRestDef D
WHERE R.RestrictionID = D.RestrictionID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOThis is the error message in the environment which does not work.
Server: Msg 4405, Level 16, State 2, Line 1
View or function 'vMoxyStandardRestriction' is not updatable because the
modification affects multiple base tables.
"FredG" wrote:

> Hello All, I have an strange issue regarding inserts via a view. Below are
> the environemnts and the code. Both environments are identical however,
> service packs a different.
> Environment 1: This environmnet the insert works
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Windows 2k sp4
> Environment 2: This envirnoment the insert fails
> Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> Here is the insert statment;
> INSERT INTO vMoxyStandardRestriction ( R.RestrictionID,
> R.RestrictionType,
> R.SecAssocType,
> R.RestrictionName,
> R.CanOverride,
> D.TranCodeBits,
> D.MinCompRestPercent,
> D.MaxCompRestPercent,
> D.IsAggregate,
> D.RoundOption,
> D.RoundFactor,
> D.MinShares,
> D.MinValue,
> D.CompType)
> VALUES (11, 1, 16, 'Unreconciled', 1, 12, 0.000000000000000e+000,
> 0.000000000000000e+000, 0, 0, 0.000000000000000e+000, 0.000000000000000e+0
00,
> 0.000000000000000e+000, 0)
> Here is the code to the view
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
> ALTER VIEW vMoxyStandardRestriction AS
> SELECT R.RestrictionID, R.RestrictionType, R.SecAssocType,
> R.RestrictionName, R.CanOverride,
> D.TranCodeBits, D.MinCompRestPercent, D.MaxCompRestPercent, D.IsAggreg
ate,
> D.RoundOption, D.RoundFactor, D.MinShares, D.MinValue, D.CompType
> FROM MoxyRestriction R, MoxyRestDef D
> WHERE R.RestrictionID = D.RestrictionID
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO|||Hi Fred
Your error messages seems pretty explicit. You cannot insert into a view
that is based on a join. If you could, your single insert would have to put
rows into both the underlying tables, and according to BOL:
[If you insert into a view] .. the modifications made by the INSERT
statement cannot affect more than one of the base tables referenced in the
FROM clause of the view. For example, an INSERT into a multitable view must
use a column_list that references only columns from one base table.
If you are not getting the message for one of your servers, that is the
weirdness. Perhaps you have an INSTEAD OF TRIGGER on the view on that
server.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"FredG" <FredG@.discussions.microsoft.com> wrote in message
news:B1BE7DA5-30D1-41F7-8C63-E213DA147DFA@.microsoft.com...
> Hello All, I have an strange issue regarding inserts via a view. Below are
> the environemnts and the code. Both environments are identical however,
> service packs a different.
> Environment 1: This environmnet the insert works
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Windows 2k sp4
> Environment 2: This envirnoment the insert fails
> Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> Here is the insert statment;
> INSERT INTO vMoxyStandardRestriction ( R.RestrictionID,
> R.RestrictionType,
> R.SecAssocType,
> R.RestrictionName,
> R.CanOverride,
> D.TranCodeBits,
> D.MinCompRestPercent,
> D.MaxCompRestPercent,
> D.IsAggregate,
> D.RoundOption,
> D.RoundFactor,
> D.MinShares,
> D.MinValue,
> D.CompType)
> VALUES (11, 1, 16, 'Unreconciled', 1, 12, 0.000000000000000e+000,
> 0.000000000000000e+000, 0, 0, 0.000000000000000e+000,
> 0.000000000000000e+000,
> 0.000000000000000e+000, 0)
> Here is the code to the view
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
> ALTER VIEW vMoxyStandardRestriction AS
> SELECT R.RestrictionID, R.RestrictionType, R.SecAssocType,
> R.RestrictionName, R.CanOverride,
> D.TranCodeBits, D.MinCompRestPercent, D.MaxCompRestPercent,
> D.IsAggregate,
> D.RoundOption, D.RoundFactor, D.MinShares, D.MinValue, D.CompType
> FROM MoxyRestriction R, MoxyRestDef D
> WHERE R.RestrictionID = D.RestrictionID
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>

Inserting TextBox values to database using SqlDataSource.Insert method

Hi, it is few days I posted here my question, but received no answer. Maybe the problem is just my problem, maybe I put my question some strange way. OK, I try to put it again, more simply.

I have few textboxes, their values I need to transport to database. I set SqlDataSource, parameters... and used SqlDataSource.Insert() method. I got NULL values in the database's record. So I tried find problem by using Microsoft's sample code from addresshttp://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.insert.aspx. After some changes I tried that code and everything went well, data were put into database. Next step was to separate code beside and structure of page to two separate files followed by new test. Good again, data were delivered to database. Next step: to use MasterPage, very simple, just with one ContentPlaceHolder. After this step the program stoped to deliver data to database and delivers only NULLs to new record. It is exactly the same problem which I have found in my application. The functionless code is here:

http://forums.asp.net/thread/1437716.aspx

I cannot find any answer this problem on forums worldwide. I cannot believe it is only my problem. I compared html code of two generated pages - with maserPage and without. There are differentions in code in ids' of input fields generated by NET.Framework:

Without masterpage:

<input name="NazevBox" type="text" id="NazevBox" />
<span id="RequiredFieldValidator1" style='color:Red;visibility:hidden;'>Please enter a company name.</span>
<p>
<input name="CodeBox" type="text" id="CodeBox" />
<span id="RequiredFieldValidator2" style='color:Red;visibility:hidden;'>Please enter a phone number.</span>
<p>
<input type="submit" name="Button1" value="Insert New Shipper" onclick="javascript:WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions("Button1", "", true, "", "", false, false))" id="Button1" />

With masterpage:

<input name="ctl00$Obsah$NazevBox" type="text" id="ctl00_Obsah_NazevBox" />
<span id="ctl00_Obsah_RequiredFieldValidator1" style='color:Red;visibility:hidden;'>Please enter a company name.</span>
<p>
<input name="ctl00$Obsah$CodeBox" type="text" id="ctl00_Obsah_CodeBox" />
<span id="ctl00_Obsah_RequiredFieldValidator2" style='color:Red;visibility:hidden;'>Please enter a phone number.</span>
<p>
<input type="submit" name="ctl00$Obsah$Button1" value="Insert New Shipper" onclick="javascript:WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions("ctl00$Obsah$Button1", "", true, "", "", false, false))" id="ctl00_Obsah_Button1" />

In second case ids' of input fields have different names, but I hope it is inner business of NET.Framework.

There must be something I haven't noticed, maybe NET's bug, maybe my own. Thanks for any suggestion.

Had a look at both of your posts, mostly the first.

By using the Inserting event for the SqlDataSource control, you can programmatically supply the parameters and their values.

http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.inserting.aspx

|||Thank you for your reply. Your solution works fine (and resolves this bug of NET.Framework 2.0 - I think it is little bug.)