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.
| Author |
Topic |
|
User45633
Starting Member
2 Posts |
 Posted - 2009-06-16 : 13:33:01
|
| I am trying to write data to a View that I set up in SQL Server 2005 Std in a web form I created in Visual Studio 2008 Pro (using VB behind the scenes), and I am in need of some help. I am able to write to a single table using the following code:Public strConn As String = "data source=[Server]; Initial Catalog=Contact_Management; User ID=[user] Password=[password]; Persist Security Info=True;packet size=4096"Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click Dim cmd As New SqlCommand("INSERT INTO Customers (CustomerID, FirstName, LastName)VALUES('" & txtCustomerID.Text & "','" & txtFirstName.Text & "','" & txtLastName.Text & "')", New SqlConnection(strConn)) cmd.Connection.Open() cmd.ExecuteNonQuery() cmd.Connection.Close() LoadData()End SubI followed an online tutorial to get to this point, so you may recognize some of those variables. In the above example, I am accepting input into textboxes called txtCustomerID, txtFirstName and txtLastName and writing them to their respective columns CustomerID, FirstName and LastName in the Customers table. I'm doing a few more things below the SQL command, but I don't think they are relevant to this issue.The problem is that I have created a view called Customer_Views, which includes approximately 10 tables and I want to write input directly to the view I created using one big SQL command instead of writing 10 separate SQL commands, and I assume this can be done with a View.For another section of the same website, I created a LINQ to SQL Class that connects to the Customer_Views and I am hoping to use a similar connection to write data to my View as I have to display data from the View. The code I am using to display data from the view is as follows:Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click Dim db As New Contact_ManagementDBDataContext() Dim q = From b In db.Customer_Views _ Where b.FirstName.Contains(txtSearch.Text.Trim()) Or _ b.LastName.Contains(txtSearch.Text.Trim()) Or _ b.AddressLine1.Contains(txtSearch.Text.Trim()) Or _ b.CustomerID.Contains(txtSearch.Text.Trim()) Or _ b.Email.Contains(txtSearch.Text.Trim()) Or _ b.Email2.Contains(txtSearch.Text.Trim()) Or _ b.Email3.Contains(txtSearch.Text.Trim()) Or _ b.Fax.Contains(txtSearch.Text.Trim()) Or _ b.LocID.Contains(txtSearch.Text.Trim()) Or _ b.Phone.Contains(txtSearch.Text.Trim()) Or _ b.Phone2.Contains(txtSearch.Text.Trim()) Or _ b.Phone3.Contains(txtSearch.Text.Trim()) _ Select b lv.DataSource = q lv.DataBind()End SubUsing LINQ to SQL is such a clean way to manage data, I would much prefer to be able to manage data to my View using this method than by manually establishing a connection to the SQL server as in the top example. Can someone help me write data to my custom View using my LINQ to SQL connection (Contact_ManagementDBDataContect())? It would be much appreciated.Thanks in advance,Tyler |
|
|
User45633
Starting Member
2 Posts |
Posted - 2009-06-19 : 19:51:43
|
| After further research, I discovered that the INSTEAD OF trigger is what I needed to use in order to enable me to insert data into the underlying tables of a view. Now that I have that working, the problem I am facing is that even though the underlying tables are receiving the new data, the view does not reflect the newly inserted data. Does anyone know what setting I need to change in my view to reflect the data that I just inserted?Tyler |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-06-19 : 22:59:24
|
quote:
Originally posted by User45633
After further research, I discovered that the INSTEAD OF trigger is what I needed to use in order to enable me to insert data into the underlying tables of a view. Now that I have that working, the problem I am facing is that even though the underlying tables are receiving the new data, the view does not reflect the newly inserted data. Does anyone know what setting I need to change in my view to reflect the data that I just inserted?Tyler
Check the view's query. Maybe it's the join or where condition that did not include those records
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
|
|
|
|
|
Cannot write data to custom View