Big problems with data binding

[Lloyd Sheen]

I am having trouble with an app that does the following:

1. Query SQL Server and return one row
2. Bind the columns to text boxes
3. User updates info
4. User clicks update button

Questions:

1. Is data binding one direction.
2. If the user changes a value in a text box how does that value get back
to the dataset or does it?

Code is as follows:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
If Not Page.IsPostBack Then
Me.SqlConnection1.Open()
Me.SqlDataAdapter1.Fill(Me.TerrDataSet)
Dim dv As DataView = Me.TerrDataSet.Tables(0).DefaultView
Me.DataBind()
Me.SqlConnection1.Close()
Me.Session.Add("DataSet", Me.TerrDataSet)
Else
Me.TerrDataSet = CType(Me.Session.Item("DataSet"), TerrDataSet)
End If
End Sub


Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim picnt As Integer
Me.SqlConnection1.Open()
Try
picnt = Me.SqlDataAdapter1.Update(Me.TerrDataSet)
Catch ex As Exception
Dim s As String = ex.Message
End Try
Me.SqlConnection1.Close()
End Sub

Lloyd Sheen

 

[Guest]

Lloyd,
Note that the internet is a stateless environment. Once the request is processed on the server and the response is sent to the client it's all over as far as the server is concerned. Apart form the session id the server doesn't remember(maintain state) anything. Also note the response being sent back is nothing but html file in text format. There's no concept of DataGrids or DataSets in HTML.

To answer your questions:

1. Is data binding one direction.

Yes it is on the internet.

2. If the user changes a value in a text box how does that value get back to the dataset or does it?

No it will not. You have to process the form that gets posted back to the server and fill up your dataset.

HTH,
Suresh.

----- Lloyd Sheen wrote: -----

I am having trouble with an app that does the following:

1. Query SQL Server and return one row
2. Bind the columns to text boxes
3. User updates info
4. User clicks update button

Questions:

1. Is data binding one direction.
2. If the user changes a value in a text box how does that value get back
to the dataset or does it?

Code is as follows:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
If Not Page.IsPostBack Then
Me.SqlConnection1.Open()
Me.SqlDataAdapter1.Fill(Me.TerrDataSet)
Dim dv As DataView = Me.TerrDataSet.Tables(0).DefaultView
Me.DataBind()
Me.SqlConnection1.Close()
Me.Session.Add("DataSet", Me.TerrDataSet)
Else
Me.TerrDataSet = CType(Me.Session.Item("DataSet"), TerrDataSet)
End If
End Sub


Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim picnt As Integer
Me.SqlConnection1.Open()
Try
picnt = Me.SqlDataAdapter1.Update(Me.TerrDataSet)
Catch ex As Exception
Dim s As String = ex.Message
End Try
Me.SqlConnection1.Close()
End Sub

Lloyd Sheen

 

[Lloyd Sheen]

Ok, things are getting really strange. First I edit the
UpdateCommand.CommandText of the SqlDataAdapter. Of course as soon as I do
this it renews the CommandText with incorrect information. I love this IDE.

So then to get around this I move the correct SQL to the UpdateCommand and
can see that it is correct while debugging. I am just attempting a proof of
concept using the Territories table in Northwind. I have input the values
to the parameters as in the following code:

SqlDataAdapter1.UpdateCommand.CommandText = "UPDATE Territories SET
TerritoryDescription = @TerritoryDescription, RegionID = @RegionID WHERE
(TerritoryID = @TerritoryID)"
SqlDataAdapter1.UpdateCommand.Parameters("@RegionID").Value =
Me.TextBox1.Text
SqlDataAdapter1.UpdateCommand.Parameters("@TerritoryDescription").Value =
Me.TextBox2.Text
SqlDataAdapter1.UpdateCommand.Parameters("@TerritoryID").Value =
Me.TextBox3.Text
picnt = Me.SqlDataAdapter1.Update(Me.TerrDataSet)

This command give me the following error:

Violation of PRIMARY KEY constraint 'PK_Territories'. Cannot insert
duplicate key in object 'Territories'.

I am updating not inserting and the update command does not update the
primary key so I have no idea what is going on. The one thing I know is
that the SqlDataAdapter is pretty useless in this case since it has no idea
how to create good SQL for updates for tables that have a primary key. And
changing the SQL is a useless operation in the designer since it seems that
the data adapter will use whatever it wants not what is entered for the
UpdateCommand.

Lloyd Sheen

Lloyd,
Note that the internet is a stateless environment. Once the request is

processed on the server and the response is sent to the client it's all over
as far as the server is concerned. Apart form the session id the server
doesn't remember(maintain state) anything. Also note the response being sen
t back is nothing but html file in text format. There's no concept of
DataGrids or DataSets in HTML.

To answer your questions:


Yes it is on the internet.

back to the dataset or does it?

No it will not. You have to process the form that gets posted back to the

server and fill up your dataset.

 

[Lloyd Sheen]

And the fun continues.

I change to use a SqlCommand. Used the IDE to generate the statement which
is copied from the designer as :

UPDATE Territories SET TerritoryDescription = @TerritoryDescription,
RegionID = @RegionID WHERE (TerritoryID = @TerritoryID)

Now when I am debugging as soon as I get to the first line using the
Sq1Command the text is now:
UPDATE Territories SET TerritoryID =, TerritoryDescription =
@TerritoryDescription, RegionID = @RegionID WHERE (TerritoryID =
@TerritoryID)

So what is going on?????

It seems to have some idea that the PK for the table is around but that text
at the beginning with the TerritoryID=, comes from some magic place.

I have no reinstalled VS2003 4 times. If this is the best it can do, please
MS give us a fix.

Lloyd Sheen