June 16, 2009 at 10:01 am
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= 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 Sub
I 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 Sub
Using 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
June 19, 2009 at 5:49 pm
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
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply