hi,
i have inserted the image present in mydocuments using alter command
create table aa(a int, d image)
insert into aa values (1,'F:\prudhvi\baba 002.jpg')
when i do
select * from aa
i am getting the result in the column d as
0x463A5C707275646876695C70727564687669203030322E6A 7067
how i can i view the image?
pls clarify my doubt
satish
Hi,
Sql is used for storing data... and image data is stored as varibale length
binary data...
image datatype...
Variable-length binary data from 0 through 231-1 (2,147,483,647) bytes.
Thats why you got that value...
For seeing it... just follow the below link... it uses ASP.NET and the "LOAD
FILE FROM DATABASE" property of text.
http://support.microsoft.com/default...b;en-us;326502
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"satish" wrote:
> hi,
> i have inserted the image present in mydocuments using alter command
> create table aa(a int, d image)
> insert into aa values (1,'F:\prudhvi\baba 002.jpg')
> when i do
> select * from aa
> i am getting the result in the column d as
> 0x463A5C707275646876695C70727564687669203030322E6A 7067
>
> how i can i view the image?
> pls clarify my doubt
> satish
>
Showing posts with label view. Show all posts
Showing posts with label view. Show all posts
Friday, March 30, 2012
inserted the image in a column-how can i view the image
hi,
i have inserted the image present in mydocuments using alter command
create table aa(a int, d image)
insert into aa values (1,'F:\prudhvi\baba 002.jpg')
when i do
select * from aa
i am getting the result in the column d as
0x463A5C707275646876695C70727564687669203030322E6A7067
how i can i view the image?
pls clarify my doubt
satishHi,
Sql is used for storing data... and image data is stored as varibale length
binary data...
image datatype...
Variable-length binary data from 0 through 231-1 (2,147,483,647) bytes.
Thats why you got that value...
For seeing it... just follow the below link... it uses ASP.NET and the "LOAD
FILE FROM DATABASE" property of text.
http://support.microsoft.com/default.aspx?scid=kb;en-us;326502
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"satish" wrote:
> hi,
> i have inserted the image present in mydocuments using alter command
> create table aa(a int, d image)
> insert into aa values (1,'F:\prudhvi\baba 002.jpg')
> when i do
> select * from aa
> i am getting the result in the column d as
> 0x463A5C707275646876695C70727564687669203030322E6A7067
>
> how i can i view the image?
> pls clarify my doubt
> satish
>
i have inserted the image present in mydocuments using alter command
create table aa(a int, d image)
insert into aa values (1,'F:\prudhvi\baba 002.jpg')
when i do
select * from aa
i am getting the result in the column d as
0x463A5C707275646876695C70727564687669203030322E6A7067
how i can i view the image?
pls clarify my doubt
satishHi,
Sql is used for storing data... and image data is stored as varibale length
binary data...
image datatype...
Variable-length binary data from 0 through 231-1 (2,147,483,647) bytes.
Thats why you got that value...
For seeing it... just follow the below link... it uses ASP.NET and the "LOAD
FILE FROM DATABASE" property of text.
http://support.microsoft.com/default.aspx?scid=kb;en-us;326502
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"satish" wrote:
> hi,
> i have inserted the image present in mydocuments using alter command
> create table aa(a int, d image)
> insert into aa values (1,'F:\prudhvi\baba 002.jpg')
> when i do
> select * from aa
> i am getting the result in the column d as
> 0x463A5C707275646876695C70727564687669203030322E6A7067
>
> how i can i view the image?
> pls clarify my doubt
> satish
>
inserted the image in a column-how can i view the image
hi,
i have inserted the image present in mydocuments using alter command
i have inserted the image present in mydocuments using alter command
create table aa(a int, d image)
insert into aa values (1,'F:\prudhvi\baba 002.jpg')
when i do
select * from aa
i am getting the result in the column d as
0x463A5C707275646876695C70727564687669203030322E6A 7067
how i can i view the image?
pls clarify my doubt
satishUse WRITETEXT/READTEXT instead of INSERT/SELECT to store / retrieve
There is quite a detailed explanation in Books Online
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"satish" <satishkumar.gourabathina@.gmail.com> wrote in message
news:1141732028.000941.183610@.e56g2000cwe.googlegr oups.com...
> hi,
> i have inserted the image present in mydocuments using alter command
> create table aa(a int, d image)
> insert into aa values (1,'F:\prudhvi\baba 002.jpg')
>
> when i do
> select * from aa
> i am getting the result in the column d as
> 0x463A5C707275646876695C70727564687669203030322E6A 7067
>
> how i can i view the image?
>
> pls clarify my doubt
>
> satish|||satish (satishkumar.gourabathina@.gmail.com) writes:
> i have inserted the image present in mydocuments using alter command
> create table aa(a int, d image)
> insert into aa values (1,'F:\prudhvi\baba 002.jpg')
> when i do
> select * from aa
> i am getting the result in the column d as
> 0x463A5C707275646876695C70727564687669203030322E6A 7067
>
> how i can i view the image?
You have not inserted the the image into the table. You have inserted the
disk location of the image into the table. Run
SELECT convert(varchar(80), d) FROM aa
to see.
There is no way to insert data into a column directly from a file. The
normal way of loading image is write a program that reads the file,
and the passes the binary stream through a parameterised INSERT statement
in a client API. You can also convert the contents to a hexstring and
build an INSERT statement from that.
Conversly, to display the image you also need an application. If you
have stored an image in a table, a SELECT on that table in Query Analyzer
or Mgmt Studio will only display a long hex string.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
inserted the image in a column-how can i view the image
hi,
i have inserted the image present in mydocuments using alter command
create table aa(a int, d image)
insert into aa values (1,'F:\prudhvi\baba 002.jpg')
when i do
select * from aa
i am getting the result in the column d as
0x463A5C707275646876695C7072756468766920
3030322E6A7067
how i can i view the image?
pls clarify my doubt
satishHi,
Sql is used for storing data... and image data is stored as varibale length
binary data...
image datatype...
Variable-length binary data from 0 through 231-1 (2,147,483,647) bytes.
Thats why you got that value...
For seeing it... just follow the below link... it uses ASP.NET and the "LOAD
FILE FROM DATABASE" property of text.
http://support.microsoft.com/defaul...kb;en-us;326502
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and
time
asking back if its 2000 or 2005]
"satish" wrote:
> hi,
> i have inserted the image present in mydocuments using alter command
> create table aa(a int, d image)
> insert into aa values (1,'F:\prudhvi\baba 002.jpg')
> when i do
> select * from aa
> i am getting the result in the column d as
> 0x463A5C707275646876695C7072756468766920
3030322E6A7067
>
> how i can i view the image?
> pls clarify my doubt
> satish
>sql
i have inserted the image present in mydocuments using alter command
create table aa(a int, d image)
insert into aa values (1,'F:\prudhvi\baba 002.jpg')
when i do
select * from aa
i am getting the result in the column d as
0x463A5C707275646876695C7072756468766920
3030322E6A7067
how i can i view the image?
pls clarify my doubt
satishHi,
Sql is used for storing data... and image data is stored as varibale length
binary data...
image datatype...
Variable-length binary data from 0 through 231-1 (2,147,483,647) bytes.
Thats why you got that value...
For seeing it... just follow the below link... it uses ASP.NET and the "LOAD
FILE FROM DATABASE" property of text.
http://support.microsoft.com/defaul...kb;en-us;326502
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and
time
asking back if its 2000 or 2005]
"satish" wrote:
> hi,
> i have inserted the image present in mydocuments using alter command
> create table aa(a int, d image)
> insert into aa values (1,'F:\prudhvi\baba 002.jpg')
> when i do
> select * from aa
> i am getting the result in the column d as
> 0x463A5C707275646876695C7072756468766920
3030322E6A7067
>
> how i can i view the image?
> pls clarify my doubt
> satish
>sql
Wednesday, March 21, 2012
Insert Trigger and Updating a view
I am just getting back to SqlServer and TSQL after a 4 year hiatus.
I want to write a trigger to update a view with the same record that is
being inserted into a table. I have a trigger bound to the table to be
inserted and since it is a simple process, I will probably forgoing using a
stored proc.
In my trigger I want to essentially do:
On Insert....
Update MyView
Set Col A = NewCol A Value,
Col B = NewCol B Value,
Col C = NewCol C Value
The NewCol x Value values are the insert values of the record being posted
to the table being inserted.
Interbase has New property. Can anyone provide the syntac to accomplish my
task?
TIA
LarryLarry,
There are two special tables accessible within a trigger,
inserted and deleted. They hold the new rows (for inserts
and updates) and the old rows (for deletes and updates)
of the target table with respect to the statement that fired
the trigger. Note that a trigger fires only once, whether the
triggering statement affects multiple rows or not, and so the
inserted and deleted tables can have more than one row.
It sounds like your triggering statement will be affecting
only one row, but it is still a good idea to consider making
sure of that by checking @.@.rowcount at the very beginning
of the trigger.
Your trigger will probably look something like this:
create trigger... as
if @.@.rowcount <> 1 begin
raiserror (as appropriate)
rollback transaction -- or return, or whatever you need
update MyView set
ColA = i.ColA,
ColB = i.ColB,
. and so on
where MyView.viewKey = i.ColumnIdentifyingViewRowToUpdate
If you want, post CREATE TABLE statement and sample data for an
example and we can try to help more specifically to your case. You
can also find out more about the special tables inserted and deleted
in Books Online.
Steve Kass
Drew University
DelphiGuy wrote:
>I am just getting back to SqlServer and TSQL after a 4 year hiatus.
>I want to write a trigger to update a view with the same record that is
>being inserted into a table. I have a trigger bound to the table to be
>inserted and since it is a simple process, I will probably forgoing using a
>stored proc.
>In my trigger I want to essentially do:
>On Insert....
>Update MyView
>Set Col A = NewCol A Value,
> Col B = NewCol B Value,
> Col C = NewCol C Value
>
>The NewCol x Value values are the insert values of the record being posted
>to the table being inserted.
>Interbase has New property. Can anyone provide the syntac to accomplish my
>task?
>TIA
>Larry
>
>
I want to write a trigger to update a view with the same record that is
being inserted into a table. I have a trigger bound to the table to be
inserted and since it is a simple process, I will probably forgoing using a
stored proc.
In my trigger I want to essentially do:
On Insert....
Update MyView
Set Col A = NewCol A Value,
Col B = NewCol B Value,
Col C = NewCol C Value
The NewCol x Value values are the insert values of the record being posted
to the table being inserted.
Interbase has New property. Can anyone provide the syntac to accomplish my
task?
TIA
LarryLarry,
There are two special tables accessible within a trigger,
inserted and deleted. They hold the new rows (for inserts
and updates) and the old rows (for deletes and updates)
of the target table with respect to the statement that fired
the trigger. Note that a trigger fires only once, whether the
triggering statement affects multiple rows or not, and so the
inserted and deleted tables can have more than one row.
It sounds like your triggering statement will be affecting
only one row, but it is still a good idea to consider making
sure of that by checking @.@.rowcount at the very beginning
of the trigger.
Your trigger will probably look something like this:
create trigger... as
if @.@.rowcount <> 1 begin
raiserror (as appropriate)
rollback transaction -- or return, or whatever you need
update MyView set
ColA = i.ColA,
ColB = i.ColB,
. and so on
where MyView.viewKey = i.ColumnIdentifyingViewRowToUpdate
If you want, post CREATE TABLE statement and sample data for an
example and we can try to help more specifically to your case. You
can also find out more about the special tables inserted and deleted
in Books Online.
Steve Kass
Drew University
DelphiGuy wrote:
>I am just getting back to SqlServer and TSQL after a 4 year hiatus.
>I want to write a trigger to update a view with the same record that is
>being inserted into a table. I have a trigger bound to the table to be
>inserted and since it is a simple process, I will probably forgoing using a
>stored proc.
>In my trigger I want to essentially do:
>On Insert....
>Update MyView
>Set Col A = NewCol A Value,
> Col B = NewCol B Value,
> Col C = NewCol C Value
>
>The NewCol x Value values are the insert values of the record being posted
>to the table being inserted.
>Interbase has New property. Can anyone provide the syntac to accomplish my
>task?
>TIA
>Larry
>
>
insert to view vs. insert directly to table
Will I see performance degrading if I use commands to insert / delete and
select from a view as supposed to doing it directly from a table
The view is a simple select * from TableName
INdirected one level as you have, no.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Avi" <rememberoti@.yahoo.com> wrote in message
news:uHkW5FOlEHA.3520@.tk2msftngp13.phx.gbl...
> Will I see performance degrading if I use commands to insert / delete and
> select from a view as supposed to doing it directly from a table
>
> The view is a simple select * from TableName
>
>
|||Avi,
Firstly, your view should not be select * from table, it should be
select <columnlist> from table.
You can only insert into a view if the view only references a base
table. Why have you got a view that does a select * anyway? Why not just
insert into the table?
You could try benchmarking this yourself to see if there's any
difference, should be pretty easy to set up. I don't think you'll notice
much of a difference, though the direct insert into the table probably
will win it by nanoseconds.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Avi wrote:
> Will I see performance degrading if I use commands to insert / delete and
> select from a view as supposed to doing it directly from a table
>
> The view is a simple select * from TableName
>
>
sql
select from a view as supposed to doing it directly from a table
The view is a simple select * from TableName
INdirected one level as you have, no.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Avi" <rememberoti@.yahoo.com> wrote in message
news:uHkW5FOlEHA.3520@.tk2msftngp13.phx.gbl...
> Will I see performance degrading if I use commands to insert / delete and
> select from a view as supposed to doing it directly from a table
>
> The view is a simple select * from TableName
>
>
|||Avi,
Firstly, your view should not be select * from table, it should be
select <columnlist> from table.
You can only insert into a view if the view only references a base
table. Why have you got a view that does a select * anyway? Why not just
insert into the table?
You could try benchmarking this yourself to see if there's any
difference, should be pretty easy to set up. I don't think you'll notice
much of a difference, though the direct insert into the table probably
will win it by nanoseconds.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Avi wrote:
> Will I see performance degrading if I use commands to insert / delete and
> select from a view as supposed to doing it directly from a table
>
> The view is a simple select * from TableName
>
>
sql
insert to view vs. insert directly to table
Will I see performance degrading if I use commands to insert / delete and
select from a view as supposed to doing it directly from a table
The view is a simple select * from TableNameINdirected one level as you have, no.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Avi" <rememberoti@.yahoo.com> wrote in message
news:uHkW5FOlEHA.3520@.tk2msftngp13.phx.gbl...
> Will I see performance degrading if I use commands to insert / delete and
> select from a view as supposed to doing it directly from a table
>
> The view is a simple select * from TableName
>
>|||Avi,
Firstly, your view should not be select * from table, it should be
select <columnlist> from table.
You can only insert into a view if the view only references a base
table. Why have you got a view that does a select * anyway? Why not just
insert into the table?
You could try benchmarking this yourself to see if there's any
difference, should be pretty easy to set up. I don't think you'll notice
much of a difference, though the direct insert into the table probably
will win it by nanoseconds.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Avi wrote:
> Will I see performance degrading if I use commands to insert / delete and
> select from a view as supposed to doing it directly from a table
>
> The view is a simple select * from TableName
>
>
select from a view as supposed to doing it directly from a table
The view is a simple select * from TableNameINdirected one level as you have, no.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Avi" <rememberoti@.yahoo.com> wrote in message
news:uHkW5FOlEHA.3520@.tk2msftngp13.phx.gbl...
> Will I see performance degrading if I use commands to insert / delete and
> select from a view as supposed to doing it directly from a table
>
> The view is a simple select * from TableName
>
>|||Avi,
Firstly, your view should not be select * from table, it should be
select <columnlist> from table.
You can only insert into a view if the view only references a base
table. Why have you got a view that does a select * anyway? Why not just
insert into the table?
You could try benchmarking this yourself to see if there's any
difference, should be pretty easy to set up. I don't think you'll notice
much of a difference, though the direct insert into the table probably
will win it by nanoseconds.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Avi wrote:
> Will I see performance degrading if I use commands to insert / delete and
> select from a view as supposed to doing it directly from a table
>
> The view is a simple select * from TableName
>
>
Insert to a sql database from a details view!?!?!?!?
I have tried just about everything i could find on the internet to fix this.
Anyway....I have a sql databasewith 3 colums: Client ID, Client Name, and Client Address
I have no trouble reading the database through the gridview, but when i try to add a script that allows me to add to that database off of my site, I get error after error.
Anyone who can figure this out must be a sql genius (or I am a complete idoit).
Here is the code I have so far:
<%
@.PageLanguage="VB"MasterPageFile="~/MasterPage.master" %><
asp:ContentID="Content1"runat="server"ContentPlaceHolderID="ContentPlaceHolder1"><scriptrunat="server">Dim strClient_NameAsStringDim strClient_AddressAsString</script><asp:DetailsViewID="DetailsView1"runat="server"AutoGenerateRows="False"DataKeyNames="Client ID"DataSourceID="SqlDataSource1"Height="50px"Width="125px"><Fields><asp:BoundFieldDataField="Client Name"HeaderText="Client Name"SortExpression="Client Name"/><asp:BoundFieldDataField="Client Address"HeaderText="Client Address"SortExpression="Client Address"/><asp:BoundFieldDataField="Client ID"HeaderText="Client ID"InsertVisible="False"ReadOnly="True"SortExpression="Client ID"/><asp:CommandFieldShowInsertButton="True"/></Fields></asp:DetailsView><asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:clientInfoConnectionString %>"InsertCommand="INSERT INTO [Clients] ([Client Name], [Client Address]) VALUES (strClient_Name, strClient_Address)"SelectCommand="SELECT [Client Name], [Client Address], [Client ID] FROM Clients"></asp:SqlDataSource></
asp:Content>
Best Regards and Thank You,
The King
I thisnk that this is not correct
InsertCommand="INSERT INTO [Clients] ([Client Name], [Client Address]) VALUES (strClient_Name, strClient_Address)"
These
Dim strClient_NameAsString
Dim strClient_AddressAsString
Are variables not available for SQL INSERT Command, you have to modify your code see example on msdn:
http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.insertcommand.aspx
The following code example demonstrates how to insert data into a database using theSqlDataSource control and a simple Web Forms page. The current data in the Data table is displayed in theDropDownList control. You can add new records by entering values into theTextBox controls, and then clicking theInsert button. When theInsert button is clicked, the specified values are inserted into the database, and theDropDownList is refreshed.
<%@.Page Language="VB" %><!DOCTYPE htmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server">PrivateSub InsertShipper (ByVal SourceAsObject,ByVal eAs EventArgs) SqlDataSource1.Insert()EndSub' InsertShipper</script><html > <head runat="server"> <title>ASP.NET Example</title></head><body> <formid="form1" runat="server"> <asp:dropdownlistid="DropDownList1" runat="server" datasourceid="SqlDataSource1" datatextfield="CompanyName" datavaluefield="ShipperID" /><!-- Security Note: The SqlDataSource uses a FormParameter, Security Note: which doesnot perform validationof input from the client. Security Note:To validate the valueof the FormParameter, handle the Insertingevent. --><asp:sqldatasourceid="SqlDataSource1" runat="server" connectionstring="<%$ ConnectionStrings:MyNorthwind %>" selectcommand="SELECT CompanyName,ShipperID FROM Shippers" insertcommand="INSERT INTO Shippers (CompanyName,Phone) VALUES (@.CoName,@.Phone)"> <insertparameters> <asp:formparameter name="CoName" formfield="CompanyNameBox" /> <asp:formparameter name="Phone" formfield="PhoneBox" /> </insertparameters> </asp:sqldatasource> <br /><asp:textboxid="CompanyNameBox" runat="server" /> <asp:RequiredFieldValidatorid="RequiredFieldValidator1" runat="server" ControlToValidate="CompanyNameBox" Display="Static" ErrorMessage="Please enter a company name." /> <br /><asp:textboxid="PhoneBox" runat="server" /> <asp:RequiredFieldValidatorid="RequiredFieldValidator2" runat="server" ControlToValidate="PhoneBox" Display="Static" ErrorMessage="Please enter a phone number." /> <br /><asp:buttonid="Button1" runat="server" text="Insert New Shipper" onclick="InsertShipper" /> </form> </body></html>
|||
I tried to define the variable using:
PublicProperty InsertCommandAsString
like the article said to, and i got the error:
Incorrect syntax near 'nvarchar'.
The name "strClient_Name" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
any further help wold be appreciated.
|||well, i looked a little deeper into the example code and saw some reason.
based off of this I rewrote my page, but i am using the exact code in the <script> tag.
it looks like it should work but every tie it brings back a syntax error when tested.
Is this something I did wrong?
Code Below:
//begin problem section
<
scriptrunat="server">Private
Sub InsertShipper (ByVal SourceAsObject,ByVal eAs EventArgs)SqlDataSource1.Insert()
End
Sub' InsertShipper</
script>//past here works perfectly
<asp:ContentID="Content1"runat="server"ContentPlaceHolderID="ContentPlaceHolder1">
<asp:sqldatasourceid="SqlDataSource1"runat="server"connectionstring="<%$ ConnectionStrings:clientInfoConnectionString %>"selectcommand="SELECT Client Name,Client Address FROM Clients"insertcommand="INSERT INTO Clients (Client Name,Client Address) VALUES (@.CName,@.CAddress)"><insertparameters><asp:formparametername="CName"formfield="Namebox"/><asp:formparametername="CAddress"formfield="Addressbox"/></insertparameters></asp:sqldatasource> Name:<br/><asp:TextBoxID="Namebox"runat="server"/><br/><asp:RequiredFieldValidatorID="RequiredFieldValidator1"runat="server"ControlToValidate="Namebox"ErrorMessage="Please Enter A Name"></asp:RequiredFieldValidator>
<br/>Address:<br/><asp:TextBoxID="Addressbox"runat="server"/><br/>
<asp:RequiredFieldValidatorID="RequiredFieldValidator2"runat="server"ControlToValidate="Addressbox"ErrorMessage="Please Enter An Address"></asp:RequiredFieldValidator>
<br/><asp:ButtonID="Submitbtn"runat="server"Text="Submit"OnClick="InsertShipper"/><br/>
</
asp:Content>
Thanks for any help in advance,
The King
|||well, i looked a little deeper into the example code and saw some reason.
based off of this I rewrote my page, but i am using the exact code in the <script> tag.
it looks like it should work but every tie it brings back a syntax error when tested.
Is this something I did wrong?
Code Below:
//begin problem section
<
scriptrunat="server">Private
Sub InsertShipper (ByVal SourceAsObject,ByVal eAs EventArgs)SqlDataSource1.Insert()
End
Sub' InsertShipper</
script>//past here works perfectly
<asp:ContentID="Content1"runat="server"ContentPlaceHolderID="ContentPlaceHolder1">
<asp:sqldatasourceid="SqlDataSource1"runat="server"connectionstring="<%$ ConnectionStrings:clientInfoConnectionString %>"selectcommand="SELECT Client Name,Client Address FROM Clients"insertcommand="INSERT INTO Clients (Client Name,Client Address) VALUES (@.CName,@.CAddress)"><insertparameters><asp:formparametername="CName"formfield="Namebox"/><asp:formparametername="CAddress"formfield="Addressbox"/></insertparameters></asp:sqldatasource> Name:<br/><asp:TextBoxID="Namebox"runat="server"/><br/><asp:RequiredFieldValidatorID="RequiredFieldValidator1"runat="server"ControlToValidate="Namebox"ErrorMessage="Please Enter A Name"></asp:RequiredFieldValidator>
<br/>Address:<br/><asp:TextBoxID="Addressbox"runat="server"/><br/>
<asp:RequiredFieldValidatorID="RequiredFieldValidator2"runat="server"ControlToValidate="Addressbox"ErrorMessage="Please Enter An Address"></asp:RequiredFieldValidator>
<br/><asp:ButtonID="Submitbtn"runat="server"Text="Submit"OnClick="InsertShipper"/><br/>
</
asp:Content>
Thanks for any help in advance,
The King
|||well, i looked a little deeper into the example code and saw some reason.
based off of this I rewrote my page, but i am using the exact code in the <script> tag.
it looks like it should work but every tie it brings back a syntax error when tested.
Is this something I did wrong?
Code Below:
//begin problem section
<
scriptrunat="server">Private
Sub InsertShipper (ByVal SourceAsObject,ByVal eAs EventArgs)SqlDataSource1.Insert()
End
Sub' InsertShipper</
script>//past here works perfectly
<asp:ContentID="Content1"runat="server"ContentPlaceHolderID="ContentPlaceHolder1">
<asp:sqldatasourceid="SqlDataSource1"runat="server"connectionstring="<%$ ConnectionStrings:clientInfoConnectionString %>"selectcommand="SELECT Client Name,Client Address FROM Clients"insertcommand="INSERT INTO Clients (Client Name,Client Address) VALUES (@.CName,@.CAddress)"><insertparameters><asp:formparametername="CName"formfield="Namebox"/><asp:formparametername="CAddress"formfield="Addressbox"/></insertparameters></asp:sqldatasource> Name:<br/><asp:TextBoxID="Namebox"runat="server"/><br/><asp:RequiredFieldValidatorID="RequiredFieldValidator1"runat="server"ControlToValidate="Namebox"ErrorMessage="Please Enter A Name"></asp:RequiredFieldValidator>
<br/>Address:<br/><asp:TextBoxID="Addressbox"runat="server"/><br/>
<asp:RequiredFieldValidatorID="RequiredFieldValidator2"runat="server"ControlToValidate="Addressbox"ErrorMessage="Please Enter An Address"></asp:RequiredFieldValidator>
<br/><asp:ButtonID="Submitbtn"runat="server"Text="Submit"OnClick="InsertShipper"/><br/>
</
asp:Content>
Thanks for any help in advance,
The King
|||Hi,
When your object names contain spaces, you have to quote them with []. So, your insert command should look like
INSERT INTO Clients ([Client Name],[Client Address]) VALUES (@.CName,@.CAddress)">
HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!
Monday, March 19, 2012
Insert thru a view to a table with an IDENTITY property
Why doesn't my identity property function normally when I try to insert
through a view?
--I create base table with identity property
CREATE TABLE _t
(id int identity
,num int)
--then insert a value
INSERT _t(num) VALUES (1)
--create view on base table
CREATE VIEW t
AS
SELECT * FROM _t
--create trigger to insert from view into the base table
CREATE TRIGGER trg
ON t
INSTEAD OF INSERT, UPDATE
AS
INSERT _t(num)
SELECT num
FROM inserted
--now try to insert into view (w/o specifying an ident value)
INSERT t(num) VALUES (3)
--and get this error
-- Server: Msg 233, Level 16, State 2, Line 1
-- The column 'id' in table 't' cannot be null.
--now try to insert into view (w specifying an ident value)
INSERT t(id, num) VALUES (7,3)
SELECT * FROM t
--and this is the result
-- id num
-- -- --
-- 1 1
-- 2 3
Does anyone have any idea why the IDENTITY property is not functioning
properly?
IOW why it asking me to supply a value for the IDENTITY column in order to
do the insert and then when I supply it, it is ignored.
What am I missing?This is interesting...
As a workaround, omit the IDENTITY column from the view's query:
CREATE VIEW t
AS
SELECT num FROM _t
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W
in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:05338885-9863-42AE-A2FE-3BC68F94AD30@.microsoft.com...
> Why doesn't my identity property function normally when I try to insert
> through a view?
> --I create base table with identity property
> CREATE TABLE _t
> (id int identity
> ,num int)
> --then insert a value
> INSERT _t(num) VALUES (1)
> --create view on base table
> CREATE VIEW t
> AS
> SELECT * FROM _t
>
> --create trigger to insert from view into the base table
> CREATE TRIGGER trg
> ON t
> INSTEAD OF INSERT, UPDATE
> AS
> INSERT _t(num)
> SELECT num
> FROM inserted
>
> --now try to insert into view (w/o specifying an ident value)
> INSERT t(num) VALUES (3)
> --and get this error
> -- Server: Msg 233, Level 16, State 2, Line 1
> -- The column 'id' in table 't' cannot be null.
> --now try to insert into view (w specifying an ident value)
> INSERT t(id, num) VALUES (7,3)
> SELECT * FROM t
> --and this is the result
> -- id num
> -- -- --
> -- 1 1
> -- 2 3
>
> Does anyone have any idea why the IDENTITY property is not functioning
> properly?
> IOW why it asking me to supply a value for the IDENTITY column in order to
> do the insert and then when I supply it, it is ignored.
> What am I missing?|||Dave
Yes, there is an issue with instead of trigger on view. scop_identity
function returns NULL
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:05338885-9863-42AE-A2FE-3BC68F94AD30@.microsoft.com...
> Why doesn't my identity property function normally when I try to insert
> through a view?
> --I create base table with identity property
> CREATE TABLE _t
> (id int identity
> ,num int)
> --then insert a value
> INSERT _t(num) VALUES (1)
> --create view on base table
> CREATE VIEW t
> AS
> SELECT * FROM _t
>
> --create trigger to insert from view into the base table
> CREATE TRIGGER trg
> ON t
> INSTEAD OF INSERT, UPDATE
> AS
> INSERT _t(num)
> SELECT num
> FROM inserted
>
> --now try to insert into view (w/o specifying an ident value)
> INSERT t(num) VALUES (3)
> --and get this error
> -- Server: Msg 233, Level 16, State 2, Line 1
> -- The column 'id' in table 't' cannot be null.
> --now try to insert into view (w specifying an ident value)
> INSERT t(id, num) VALUES (7,3)
> SELECT * FROM t
> --and this is the result
> -- id num
> -- -- --
> -- 1 1
> -- 2 3
>
> Does anyone have any idea why the IDENTITY property is not functioning
> properly?
> IOW why it asking me to supply a value for the IDENTITY column in order to
> do the insert and then when I supply it, it is ignored.
> What am I missing?|||On Wed, 26 Oct 2005 17:48:02 -0700, Dave wrote:
>Why doesn't my identity property function normally when I try to insert
>through a view?
Hi Dave,
That's because SQL Server checks if the NOT NULL constraint is violated
BEFORE the INSTEAD OF trigger is fired.
<speculation>
I *think* that this has an architectural reason. The new row(s) have to
be present in the "inserted" pseudo-table. This table has the same
structure as the table or view that the INSTEAD OF trigger is defined
for - up to and including nullability. That meanst that if a column
can't be NULL in the table (or view), there will be no space in the data
structure to represent whether a real value or a NULL was inserted.
And since SQL Server can't faithfully represent a NULL in the inserted
table that is passed to the INSTEAD OF trigger, it takes the safe route
and generates an error message.
</speculation>
>Does anyone have any idea why the IDENTITY property is not functioning
>properly?
It has nothing to do with the IDENTITY property, as explained above. If
you check Books Online, you'll find an example where a bogus value has
to be passed for a computed value in the view.
>IOW why it asking me to supply a value for the IDENTITY column in order to
>do the insert and then when I supply it, it is ignored.
>What am I missing?
You missed the discussion of a similar situation in Books Online, under
the heading "INSTEAD OF INSERT Triggers".
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
through a view?
--I create base table with identity property
CREATE TABLE _t
(id int identity
,num int)
--then insert a value
INSERT _t(num) VALUES (1)
--create view on base table
CREATE VIEW t
AS
SELECT * FROM _t
--create trigger to insert from view into the base table
CREATE TRIGGER trg
ON t
INSTEAD OF INSERT, UPDATE
AS
INSERT _t(num)
SELECT num
FROM inserted
--now try to insert into view (w/o specifying an ident value)
INSERT t(num) VALUES (3)
--and get this error
-- Server: Msg 233, Level 16, State 2, Line 1
-- The column 'id' in table 't' cannot be null.
--now try to insert into view (w specifying an ident value)
INSERT t(id, num) VALUES (7,3)
SELECT * FROM t
--and this is the result
-- id num
-- -- --
-- 1 1
-- 2 3
Does anyone have any idea why the IDENTITY property is not functioning
properly?
IOW why it asking me to supply a value for the IDENTITY column in order to
do the insert and then when I supply it, it is ignored.
What am I missing?This is interesting...
As a workaround, omit the IDENTITY column from the view's query:
CREATE VIEW t
AS
SELECT num FROM _t
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W
in Israel![url]http://www.microsoft.com/israel/sql/sqlw
/default.mspx[/url]"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:05338885-9863-42AE-A2FE-3BC68F94AD30@.microsoft.com...
> Why doesn't my identity property function normally when I try to insert
> through a view?
> --I create base table with identity property
> CREATE TABLE _t
> (id int identity
> ,num int)
> --then insert a value
> INSERT _t(num) VALUES (1)
> --create view on base table
> CREATE VIEW t
> AS
> SELECT * FROM _t
>
> --create trigger to insert from view into the base table
> CREATE TRIGGER trg
> ON t
> INSTEAD OF INSERT, UPDATE
> AS
> INSERT _t(num)
> SELECT num
> FROM inserted
>
> --now try to insert into view (w/o specifying an ident value)
> INSERT t(num) VALUES (3)
> --and get this error
> -- Server: Msg 233, Level 16, State 2, Line 1
> -- The column 'id' in table 't' cannot be null.
> --now try to insert into view (w specifying an ident value)
> INSERT t(id, num) VALUES (7,3)
> SELECT * FROM t
> --and this is the result
> -- id num
> -- -- --
> -- 1 1
> -- 2 3
>
> Does anyone have any idea why the IDENTITY property is not functioning
> properly?
> IOW why it asking me to supply a value for the IDENTITY column in order to
> do the insert and then when I supply it, it is ignored.
> What am I missing?|||Dave
Yes, there is an issue with instead of trigger on view. scop_identity
function returns NULL
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:05338885-9863-42AE-A2FE-3BC68F94AD30@.microsoft.com...
> Why doesn't my identity property function normally when I try to insert
> through a view?
> --I create base table with identity property
> CREATE TABLE _t
> (id int identity
> ,num int)
> --then insert a value
> INSERT _t(num) VALUES (1)
> --create view on base table
> CREATE VIEW t
> AS
> SELECT * FROM _t
>
> --create trigger to insert from view into the base table
> CREATE TRIGGER trg
> ON t
> INSTEAD OF INSERT, UPDATE
> AS
> INSERT _t(num)
> SELECT num
> FROM inserted
>
> --now try to insert into view (w/o specifying an ident value)
> INSERT t(num) VALUES (3)
> --and get this error
> -- Server: Msg 233, Level 16, State 2, Line 1
> -- The column 'id' in table 't' cannot be null.
> --now try to insert into view (w specifying an ident value)
> INSERT t(id, num) VALUES (7,3)
> SELECT * FROM t
> --and this is the result
> -- id num
> -- -- --
> -- 1 1
> -- 2 3
>
> Does anyone have any idea why the IDENTITY property is not functioning
> properly?
> IOW why it asking me to supply a value for the IDENTITY column in order to
> do the insert and then when I supply it, it is ignored.
> What am I missing?|||On Wed, 26 Oct 2005 17:48:02 -0700, Dave wrote:
>Why doesn't my identity property function normally when I try to insert
>through a view?
Hi Dave,
That's because SQL Server checks if the NOT NULL constraint is violated
BEFORE the INSTEAD OF trigger is fired.
<speculation>
I *think* that this has an architectural reason. The new row(s) have to
be present in the "inserted" pseudo-table. This table has the same
structure as the table or view that the INSTEAD OF trigger is defined
for - up to and including nullability. That meanst that if a column
can't be NULL in the table (or view), there will be no space in the data
structure to represent whether a real value or a NULL was inserted.
And since SQL Server can't faithfully represent a NULL in the inserted
table that is passed to the INSTEAD OF trigger, it takes the safe route
and generates an error message.
</speculation>
>Does anyone have any idea why the IDENTITY property is not functioning
>properly?
It has nothing to do with the IDENTITY property, as explained above. If
you check Books Online, you'll find an example where a bogus value has
to be passed for a computed value in the view.
>IOW why it asking me to supply a value for the IDENTITY column in order to
>do the insert and then when I supply it, it is ignored.
>What am I missing?
You missed the discussion of a similar situation in Books Online, under
the heading "INSTEAD OF INSERT Triggers".
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Wednesday, March 7, 2012
INSERT Records in multiple tables
I need to update two tables. I have created a view and am using the code in the attached file to insert into the two tables.
The page loads without errors, but I get this message that the view is not updatable because the modification affects multiple base tables.
I thought this was the purpose of views?
Does anyone have any suggestions? I am using Dreamweaver MX and SQL Server.
Thanks!
NNo, that is not the purpose of views. Views are frequently not updateable, and I don't think it is ever possible to update different columns from different tables in the same view. Even a direct SQL Update statement will only update one table at a time, so you will need to issues separate update statements or handle the problem through triggers or cascading updates.
Truth is, views don't serve much purpose any more.
Good database application design principles dictate making all your updates through stored procedures. Your application should rarely if ever have direct access to the database tables, even for retrieving data.|||No, you can update the columns of each of the base table independantly (one or more UPDATE statements per base table), but you can't update multiple base tables in a single pass.
Thinking outside of the SQL box, a table represents a relational algebra entity. An entity has no inherant order for either columns or rows, they behave something like a hash in that respect.
Views represent a relational algebra result. A result can have order, there can be a first, middle, and last for both rows and columns in a view.
-PatP|||Views (with multiple base tables) can be updated at one shot by using INSTEAD OF trigger
Here is some supporting article from MSDN
Cheers
Benny
-----------------------
Modifying Data Through a View
You can modify data through a view in these ways:
Use INSTEAD OF triggers with logic to support INSERT, UPDATE and DELETE statements.
Use updatable partitioned views that modify one or more member tables.
If a view does not use an INSTEAD OF trigger or is not an updatable partitioned view, it can still be updatable provided that:
The view contains at least one table in the FROM clause of the view definition; the view cannot be based solely on an expression.
No aggregate functions (AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, VARP) or GROUP BY, UNION, DISTINCT, or TOP clauses are used in the select list. However, aggregate functions can be used within a subquery defined in the FROM clause provided that the derived values generated by the aggregate functions are not modified.
Note Partitioned views using the UNION ALL operator can be updatable.
No derived columns are used in the select list. Derived columns are result set columns formed by anything other than a simple column reference.
Guidelines for Modifying Data Through a View
Before you modify data through a view without using an INSTEAD OF trigger or an updatable partitioned view, consider these guidelines:
All data modification statements executed against the view must adhere to the criteria set within the SELECT statement defining the view if the WITH CHECK OPTION clause is used in the definition of the view. If the WITH CHECK OPTION clause is used, rows cannot be modified in a way that causes them to disappear from the view. Any modification that would cause this to happen is canceled and an error is displayed.
SQL Server must be able to resolve unambiguously the modification operation to specific rows in one of the base tables referenced by the view. You cannot use data modification statements on more than one underlying table in a single statement. Therefore, the columns listed in the UPDATE or INSERT statement must belong to a single base table within the view definition.
All the columns in the underlying table that are being updated and do not allow null values have values specified in either the INSERT statement or DEFAULT definitions. This ensures that all the columns in the underlying table that require values have them.
The data modified in the columns in the underlying table must adhere to the restrictions on those columns, such as nullability, constraints, DEFAULT definitions and so on. For example, if a row is deleted, all the underlying FOREIGN KEY constraints in related tables must still be satisfied for the delete to succeed.
A distributed partition view (remote view) cannot be updated using a keyset-driven cursor. This restriction can be resolved by declaring the cursor on the underlying tables and not on the view itself.
Additionally, to delete data in a view:
Only one table can be listed in the FROM clause of the view definition.|||Originally posted by blindman
Truth is, views don't serve much purpose any more.
what??!!
maybe not for use by the DBA, but for use by end users in a reporting environment, views are invaluable
"much purpose any more"?
what do you suppose the purpose of a view used to be then, before it got to where this purpose was diluted?
okay, here's an example
say a table is called Accounts and say it contains a column called LedgerCode and say the column values range from A to E, and now you have to change the table so that instead of values A to E, the LedgerCode becomes a numeric tinyint foreign key to a Ledger table with values A through Z
the mere fact that you can have a view with a join in it eliminates the need for the end user to figure out how to write a join
rename the table, change the table, declare a view called Accounts, build the join into the view, and voila, all existing code that used to select from the Accounts table still works
a long time ago i used to work in a shop where end users never got to use base tables, they were always given just views, and i can definitely see the logic behind that
it's called program-data independence|||Let me rephrase that...
Truth is, views don't serve much purpose any more, "IMHO".
I used to use views a lot too, specifically for program-data independence. Now, at least for application development, I always use Sprocs or UDFs.|||cool :cool:|||I believe you mean...
"Cool, IMHO." ;)|||indeed
burying application code inside sporcs and fuds is not cool to everybody, i admit -- especially those folks who would like to see a clear separation of application logic from proprietary database languages
usually i insist on declarative relational integrity but otherwise force application logic outside the database
you know, like so that your app is not dependent on any particular dbms
but sporcs and fuds are reasonably coolish, in my opinion, yeah|||Okey-dokey. I usually take the exact opposite approach, putting as much application logic into the RDBMS as possible, so that the application is not dependent on any particular interface. The reasoning is that these days people frequently want to access their data through different channels, such as a VB application, a Crystal Report, Access ADP project, Dot-Net, etc. By putting the application logic in the database you ensure consistent input and output and you avoid duplicating code. Let the the interface do what it does best: display the data and guide the user through it.
I guess the decision depends upon whether it is more likely that your application will need to be ported to a different RDBMS, or that users will come up with new requirements for accessing it. Perhaps I prefer the latter because the former results in boring "file cabinet" databases which frequently lack any sort of referential integrity. They just aren't as much fun or rewarding to work with as a database which is practically an application in itself.
The page loads without errors, but I get this message that the view is not updatable because the modification affects multiple base tables.
I thought this was the purpose of views?
Does anyone have any suggestions? I am using Dreamweaver MX and SQL Server.
Thanks!
NNo, that is not the purpose of views. Views are frequently not updateable, and I don't think it is ever possible to update different columns from different tables in the same view. Even a direct SQL Update statement will only update one table at a time, so you will need to issues separate update statements or handle the problem through triggers or cascading updates.
Truth is, views don't serve much purpose any more.
Good database application design principles dictate making all your updates through stored procedures. Your application should rarely if ever have direct access to the database tables, even for retrieving data.|||No, you can update the columns of each of the base table independantly (one or more UPDATE statements per base table), but you can't update multiple base tables in a single pass.
Thinking outside of the SQL box, a table represents a relational algebra entity. An entity has no inherant order for either columns or rows, they behave something like a hash in that respect.
Views represent a relational algebra result. A result can have order, there can be a first, middle, and last for both rows and columns in a view.
-PatP|||Views (with multiple base tables) can be updated at one shot by using INSTEAD OF trigger
Here is some supporting article from MSDN
Cheers
Benny
-----------------------
Modifying Data Through a View
You can modify data through a view in these ways:
Use INSTEAD OF triggers with logic to support INSERT, UPDATE and DELETE statements.
Use updatable partitioned views that modify one or more member tables.
If a view does not use an INSTEAD OF trigger or is not an updatable partitioned view, it can still be updatable provided that:
The view contains at least one table in the FROM clause of the view definition; the view cannot be based solely on an expression.
No aggregate functions (AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, VARP) or GROUP BY, UNION, DISTINCT, or TOP clauses are used in the select list. However, aggregate functions can be used within a subquery defined in the FROM clause provided that the derived values generated by the aggregate functions are not modified.
Note Partitioned views using the UNION ALL operator can be updatable.
No derived columns are used in the select list. Derived columns are result set columns formed by anything other than a simple column reference.
Guidelines for Modifying Data Through a View
Before you modify data through a view without using an INSTEAD OF trigger or an updatable partitioned view, consider these guidelines:
All data modification statements executed against the view must adhere to the criteria set within the SELECT statement defining the view if the WITH CHECK OPTION clause is used in the definition of the view. If the WITH CHECK OPTION clause is used, rows cannot be modified in a way that causes them to disappear from the view. Any modification that would cause this to happen is canceled and an error is displayed.
SQL Server must be able to resolve unambiguously the modification operation to specific rows in one of the base tables referenced by the view. You cannot use data modification statements on more than one underlying table in a single statement. Therefore, the columns listed in the UPDATE or INSERT statement must belong to a single base table within the view definition.
All the columns in the underlying table that are being updated and do not allow null values have values specified in either the INSERT statement or DEFAULT definitions. This ensures that all the columns in the underlying table that require values have them.
The data modified in the columns in the underlying table must adhere to the restrictions on those columns, such as nullability, constraints, DEFAULT definitions and so on. For example, if a row is deleted, all the underlying FOREIGN KEY constraints in related tables must still be satisfied for the delete to succeed.
A distributed partition view (remote view) cannot be updated using a keyset-driven cursor. This restriction can be resolved by declaring the cursor on the underlying tables and not on the view itself.
Additionally, to delete data in a view:
Only one table can be listed in the FROM clause of the view definition.|||Originally posted by blindman
Truth is, views don't serve much purpose any more.
what??!!
maybe not for use by the DBA, but for use by end users in a reporting environment, views are invaluable
"much purpose any more"?
what do you suppose the purpose of a view used to be then, before it got to where this purpose was diluted?
okay, here's an example
say a table is called Accounts and say it contains a column called LedgerCode and say the column values range from A to E, and now you have to change the table so that instead of values A to E, the LedgerCode becomes a numeric tinyint foreign key to a Ledger table with values A through Z
the mere fact that you can have a view with a join in it eliminates the need for the end user to figure out how to write a join
rename the table, change the table, declare a view called Accounts, build the join into the view, and voila, all existing code that used to select from the Accounts table still works
a long time ago i used to work in a shop where end users never got to use base tables, they were always given just views, and i can definitely see the logic behind that
it's called program-data independence|||Let me rephrase that...
Truth is, views don't serve much purpose any more, "IMHO".
I used to use views a lot too, specifically for program-data independence. Now, at least for application development, I always use Sprocs or UDFs.|||cool :cool:|||I believe you mean...
"Cool, IMHO." ;)|||indeed
burying application code inside sporcs and fuds is not cool to everybody, i admit -- especially those folks who would like to see a clear separation of application logic from proprietary database languages
usually i insist on declarative relational integrity but otherwise force application logic outside the database
you know, like so that your app is not dependent on any particular dbms
but sporcs and fuds are reasonably coolish, in my opinion, yeah|||Okey-dokey. I usually take the exact opposite approach, putting as much application logic into the RDBMS as possible, so that the application is not dependent on any particular interface. The reasoning is that these days people frequently want to access their data through different channels, such as a VB application, a Crystal Report, Access ADP project, Dot-Net, etc. By putting the application logic in the database you ensure consistent input and output and you avoid duplicating code. Let the the interface do what it does best: display the data and guide the user through it.
I guess the decision depends upon whether it is more likely that your application will need to be ported to a different RDBMS, or that users will come up with new requirements for accessing it. Perhaps I prefer the latter because the former results in boring "file cabinet" databases which frequently lack any sort of referential integrity. They just aren't as much fun or rewarding to work with as a database which is practically an application in itself.
insert records dependent on values from other table - with a l
thats great, all looks fine when the input data are from a table or view.
My data comes from a select statement from table1
for example:
SELECT [id] , InvoiceId + SoldTo AS f1, pieces as nr FROM table 1 order
by InvoiceId
how can i use this as a input.
best regards
Xavier
"R.D" wrote:
> Xavier
> Try this, If its ok for you to use cursors
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> ALTER proc Myproc
> as
> DECLARE @.ID int,
> @.F1 varchar(20),@.f2 varchar(10),
> @.NR INT,
> @.COUNTER INT
> DECLARE Mycursor CURSOR
> READ_ONLY
> FOR SELECT [ID],f1,nr FROM TABLE1
> OPEN Mycursor
> FETCH NEXT FROM Mycursor INTO @.ID,@.F1,@.NR
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> select @.COUNTER = 1
> WHILE (@.NR > (@.COUNTER - 1))
> BEGIN
> select @.f2 = @.F1 + CAST(@.COUNTER AS VARCHAR(10))
> INSERT INTO TABLE1([ID],F2) VALUES(@.ID,@.f2)
> SELECT @.COUNTER = @.COUNTER + 1
> END
> END
> FETCH NEXT FROM Mycursor INTO @.ID,@.F1,@.NR
> END
> CLOSE Mycursor
> DEALLOCATE Mycursor
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> Regards
> R.D
> --Post back if you want something less of cursors
>
> "Xavier" wrote:
>Just change the FOR SELECT statement in the cursor
That should work
Regards
R.D
"Xavier" wrote:
> thats great, all looks fine when the input data are from a table or view.
> My data comes from a select statement from table1
> for example:
> SELECT [id] , InvoiceId + SoldTo AS f1, pieces as nr FROM table 1 order
> by InvoiceId
> how can i use this as a input.
> best regards
> Xavier
> "R.D" wrote:
>
My data comes from a select statement from table1
for example:
SELECT [id] , InvoiceId + SoldTo AS f1, pieces as nr FROM table 1 order
by InvoiceId
how can i use this as a input.
best regards
Xavier
"R.D" wrote:
> Xavier
> Try this, If its ok for you to use cursors
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> ALTER proc Myproc
> as
> DECLARE @.ID int,
> @.F1 varchar(20),@.f2 varchar(10),
> @.NR INT,
> @.COUNTER INT
> DECLARE Mycursor CURSOR
> READ_ONLY
> FOR SELECT [ID],f1,nr FROM TABLE1
> OPEN Mycursor
> FETCH NEXT FROM Mycursor INTO @.ID,@.F1,@.NR
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> select @.COUNTER = 1
> WHILE (@.NR > (@.COUNTER - 1))
> BEGIN
> select @.f2 = @.F1 + CAST(@.COUNTER AS VARCHAR(10))
> INSERT INTO TABLE1([ID],F2) VALUES(@.ID,@.f2)
> SELECT @.COUNTER = @.COUNTER + 1
> END
> END
> FETCH NEXT FROM Mycursor INTO @.ID,@.F1,@.NR
> END
> CLOSE Mycursor
> DEALLOCATE Mycursor
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> Regards
> R.D
> --Post back if you want something less of cursors
>
> "Xavier" wrote:
>Just change the FOR SELECT statement in the cursor
That should work
Regards
R.D
"Xavier" wrote:
> thats great, all looks fine when the input data are from a table or view.
> My data comes from a select statement from table1
> for example:
> SELECT [id] , InvoiceId + SoldTo AS f1, pieces as nr FROM table 1 order
> by InvoiceId
> how can i use this as a input.
> best regards
> Xavier
> "R.D" wrote:
>
INSERT Record through a view
Is is possible to insert a record through a view. If so, how?
USE Northwind
GO
CREATE TABLE tbForms (
FormID INT IDENTITY (1,1) NOT NULL,
Form varchar (100) NOT NULL
)
GO
ALTER TABLE tbForms
ADD CONSTRAINT tbForms_pk PRIMARY KEY (FormID)
GO
CREATE TABLE tbDoubleTeeForms (
fkFormID INT NOT NULL,
Form varchar(100) NOT NULL,
Width FLOAT,
Height FLOAT,
Flange FLOAT,
Leg FLOAT,
LegCount INT
)
GO
ALTER TABLE tbDoubleTeeForms
ADD CONSTRAINT tbDoubleTeeForms_pk PRIMARY KEY (fkFormID)
GO
ALTER TABLE tbDoubleTeeForms
ADD CONSTRAINT tbDoubleTeeForms_fk FOREIGN KEY (fkFormID)
REFERENCES tbForms (FormID)
GO
CREATE TABLE tbFlatPanelForms (
fkFormID INT NOT NULL,
Form varchar(100) NOT NULL,
Width FLOAT,
HEIGHT FLOAT
)
GO
ALTER TABLE tbFlatPanelForms
ADD CONSTRAINT tbFlatPanelForms_pk PRIMARY KEY (fkFormID)
GO
ALTER TABLE tbFlatPanelForms
ADD CONSTRAINT tbFlatPanelForms_fk FOREIGN KEY (fkFormID)
REFERENCES tbForms (FormID)
GO
CREATE VIEW MyProducts AS
SELECT fkFormID, Form FROM tbDoubleTeeForms UNION ALL
SELECT fkFormID, FOrm FROM tbFlatPanelForms
GO
-- How can I insert a new record, the pk of the forms table is identity.
-- Can this be done?
INSERT INTO MyProducts (Form)
VALUES ('My First Entry')
GO
SELECT * FROM MyProducts
GO
DROP VIEW MyProducts
GO
DROP TABLE tbFlatPanelForms
GO
DROP TABLE tbDoubleTeeForms
GO
DROP TABLE tbForms
GO
Mike Bithink it's imposible to do with view with union
in your case you woudl like to insert data into 3 tables
maybe tray insert data into 2 tables and triger to put data into 3-th table|||you can insert into a view but you can only affect one table.
so in the case of unions this is not possible
BUT......
you can however use an instead of trigger to check for which table the insert is going to and then instead of inserting through the view, you insert directly to the correct table.|||BOL:
Updatable Partitioned Views
If a local or distributed partitioned view is not updatable, it can serve only as a read-only copy of the original table. An updatable partitioned view can exhibit all the capabilities of the original table.
A view is considered an updatable partitioned view if:
The view is a set of SELECT statements whose individual result sets are combined into one using the UNION ALL statement. Each individual SELECT statement references one SQL Server base table. The table can be either a local table or a linked table referenced using a four-part name, the OPENROWSET function, or the OPENDATASOURCE function (you cannot use an OPENDATASOURCE or OPENROWSET function that specifies a pass-through query).
The view will not be updatable if a trigger or cascading update or delete is defined on one or more member tables.|||rdjabarov
no partition mentioned. so went with the conservative option
hey
how about some liquor this friday?|||Scott,
I am accompanying my daughter's class for the trip to NASA in Houston this evening. We're coming back on Friday night. But I hope it's gonna be shortly after noon, not at night. Will let you know.|||Originally posted by rdjabarov
Scott,
I am accompanying my daughter's class for the trip to NASA in Houston this evening. We're coming back on Friday night. But I hope it's gonna be shortly after noon, not at night. Will let you know. Now THAT's a road-trip! Funny, driving across Texas takes a lot longer than driving across Illinois, doesn't it?
-PatP|||Hell...he'll be still backing out of his driveway by the time I get across NJ
:D
and a shamless 2500th post...
And you should look into partitioned views...the contraints have to be very specific...
but updating the base table is the best performing method...
for the view, the optimizer will still look at a tables in the view...|||Originally posted by Brett Kaiser
and a shamless 2500th post... Uff da! That's a lot of postings! Congratulations.
I still think you should have posted #2500 into the Yak Corral!
-PatP|||Originally posted by Pat Phelan
Now THAT's a road-trip! Funny, driving across Texas takes a lot longer than driving across Illinois, doesn't it?
-PatP
Talking about long road trips .. Three years ago I drove all the way from the east coast to the west coast .. (NC to CA) .. and man! .. I thought it took me an eternity to drive across Texas!|||Yeah, I had a car like that once...
;)
-PatP|||northern florida absolutely sucks
normally when you drive you will guage how long you have to go by the # of exits
for example when you get on a highway and your directions say to get off the highway at exit 120 and you are at exit 100.
normally that should take no time
but in northern FL the exits are 20 to 30 miles apart.
eternity ensues|||Originally posted by GDMI
Talking about long road trips .. Three years ago I drove all the way from the east coast to the west coast .. (NC to CA) .. and man! .. I thought it took me an eternity to drive across Texas!
Over the christmas holidays I drove a plymoth breeze from Windsor Ontario Canada -> South Padre Island, Texas -> Orlando Florida -> Back to Windsor
Just under 7000 kilometers if I remember correctly.
Great time though!
Mike B|||Originally posted by Ruprect
northern florida absolutely sucks
normally when you drive you will guage how long you have to go by the # of exits
for example when you get on a highway and your directions say to get off the highway at exit 120 and you are at exit 100.
normally that should take no time
but in northern FL the exits are 20 to 30 miles apart.
eternity ensues The best I can come up with is "Well duh!"
Why put the exits closer together? Who would want to get off? If you think that the highway inhales vigorously, you should have tried getting off the highway somewhere in northern Florida!
-PatP
USE Northwind
GO
CREATE TABLE tbForms (
FormID INT IDENTITY (1,1) NOT NULL,
Form varchar (100) NOT NULL
)
GO
ALTER TABLE tbForms
ADD CONSTRAINT tbForms_pk PRIMARY KEY (FormID)
GO
CREATE TABLE tbDoubleTeeForms (
fkFormID INT NOT NULL,
Form varchar(100) NOT NULL,
Width FLOAT,
Height FLOAT,
Flange FLOAT,
Leg FLOAT,
LegCount INT
)
GO
ALTER TABLE tbDoubleTeeForms
ADD CONSTRAINT tbDoubleTeeForms_pk PRIMARY KEY (fkFormID)
GO
ALTER TABLE tbDoubleTeeForms
ADD CONSTRAINT tbDoubleTeeForms_fk FOREIGN KEY (fkFormID)
REFERENCES tbForms (FormID)
GO
CREATE TABLE tbFlatPanelForms (
fkFormID INT NOT NULL,
Form varchar(100) NOT NULL,
Width FLOAT,
HEIGHT FLOAT
)
GO
ALTER TABLE tbFlatPanelForms
ADD CONSTRAINT tbFlatPanelForms_pk PRIMARY KEY (fkFormID)
GO
ALTER TABLE tbFlatPanelForms
ADD CONSTRAINT tbFlatPanelForms_fk FOREIGN KEY (fkFormID)
REFERENCES tbForms (FormID)
GO
CREATE VIEW MyProducts AS
SELECT fkFormID, Form FROM tbDoubleTeeForms UNION ALL
SELECT fkFormID, FOrm FROM tbFlatPanelForms
GO
-- How can I insert a new record, the pk of the forms table is identity.
-- Can this be done?
INSERT INTO MyProducts (Form)
VALUES ('My First Entry')
GO
SELECT * FROM MyProducts
GO
DROP VIEW MyProducts
GO
DROP TABLE tbFlatPanelForms
GO
DROP TABLE tbDoubleTeeForms
GO
DROP TABLE tbForms
GO
Mike Bithink it's imposible to do with view with union
in your case you woudl like to insert data into 3 tables
maybe tray insert data into 2 tables and triger to put data into 3-th table|||you can insert into a view but you can only affect one table.
so in the case of unions this is not possible
BUT......
you can however use an instead of trigger to check for which table the insert is going to and then instead of inserting through the view, you insert directly to the correct table.|||BOL:
Updatable Partitioned Views
If a local or distributed partitioned view is not updatable, it can serve only as a read-only copy of the original table. An updatable partitioned view can exhibit all the capabilities of the original table.
A view is considered an updatable partitioned view if:
The view is a set of SELECT statements whose individual result sets are combined into one using the UNION ALL statement. Each individual SELECT statement references one SQL Server base table. The table can be either a local table or a linked table referenced using a four-part name, the OPENROWSET function, or the OPENDATASOURCE function (you cannot use an OPENDATASOURCE or OPENROWSET function that specifies a pass-through query).
The view will not be updatable if a trigger or cascading update or delete is defined on one or more member tables.|||rdjabarov
no partition mentioned. so went with the conservative option
hey
how about some liquor this friday?|||Scott,
I am accompanying my daughter's class for the trip to NASA in Houston this evening. We're coming back on Friday night. But I hope it's gonna be shortly after noon, not at night. Will let you know.|||Originally posted by rdjabarov
Scott,
I am accompanying my daughter's class for the trip to NASA in Houston this evening. We're coming back on Friday night. But I hope it's gonna be shortly after noon, not at night. Will let you know. Now THAT's a road-trip! Funny, driving across Texas takes a lot longer than driving across Illinois, doesn't it?
-PatP|||Hell...he'll be still backing out of his driveway by the time I get across NJ
:D
and a shamless 2500th post...
And you should look into partitioned views...the contraints have to be very specific...
but updating the base table is the best performing method...
for the view, the optimizer will still look at a tables in the view...|||Originally posted by Brett Kaiser
and a shamless 2500th post... Uff da! That's a lot of postings! Congratulations.
I still think you should have posted #2500 into the Yak Corral!
-PatP|||Originally posted by Pat Phelan
Now THAT's a road-trip! Funny, driving across Texas takes a lot longer than driving across Illinois, doesn't it?
-PatP
Talking about long road trips .. Three years ago I drove all the way from the east coast to the west coast .. (NC to CA) .. and man! .. I thought it took me an eternity to drive across Texas!|||Yeah, I had a car like that once...
;)
-PatP|||northern florida absolutely sucks
normally when you drive you will guage how long you have to go by the # of exits
for example when you get on a highway and your directions say to get off the highway at exit 120 and you are at exit 100.
normally that should take no time
but in northern FL the exits are 20 to 30 miles apart.
eternity ensues|||Originally posted by GDMI
Talking about long road trips .. Three years ago I drove all the way from the east coast to the west coast .. (NC to CA) .. and man! .. I thought it took me an eternity to drive across Texas!
Over the christmas holidays I drove a plymoth breeze from Windsor Ontario Canada -> South Padre Island, Texas -> Orlando Florida -> Back to Windsor
Just under 7000 kilometers if I remember correctly.
Great time though!
Mike B|||Originally posted by Ruprect
northern florida absolutely sucks
normally when you drive you will guage how long you have to go by the # of exits
for example when you get on a highway and your directions say to get off the highway at exit 120 and you are at exit 100.
normally that should take no time
but in northern FL the exits are 20 to 30 miles apart.
eternity ensues The best I can come up with is "Well duh!"
Why put the exits closer together? Who would want to get off? If you think that the highway inhales vigorously, you should have tried getting off the highway somewhere in northern Florida!
-PatP
Subscribe to:
Posts (Atom)