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.EventArg s) Handles MyBase.Load
'Put user code to initialize the page here
If Not Page.IsPostBack Then
Me.SqlConnectio n1.Open()
Me.SqlDataAdapt er1.Fill(Me.Ter rDataSet)
Dim dv As DataView = Me.TerrDataSet. Tables(0).Defau ltView
Me.DataBind()
Me.SqlConnectio n1.Close()
Me.Session.Add( "DataSet", Me.TerrDataSet)
Else
Me.TerrDataSet = CType(Me.Sessio n.Item("DataSet "), TerrDataSet)
End If
End Sub
Private Sub Button1_Click(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles Button1.Click
Dim picnt As Integer
Me.SqlConnectio n1.Open()
Try
picnt = Me.SqlDataAdapt er1.Update(Me.T errDataSet)
Catch ex As Exception
Dim s As String = ex.Message
End Try
Me.SqlConnectio n1.Close()
End Sub

Lloyd Sheen

[Suresh]

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(mainta in 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.EventArg s) Handles MyBase.Load
'Put user code to initialize the page here
If Not Page.IsPostBack Then
Me.SqlConnectio n1.Open()
Me.SqlDataAdapt er1.Fill(Me.Ter rDataSet)
Dim dv As DataView = Me.TerrDataSet. Tables(0).Defau ltView
Me.DataBind()
Me.SqlConnectio n1.Close()
Me.Session.Add( "DataSet", Me.TerrDataSet)
Else
Me.TerrDataSet = CType(Me.Sessio n.Item("DataSet "), TerrDataSet)
End If
End Sub
Private Sub Button1_Click(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles Button1.Click
Dim picnt As Integer
Me.SqlConnectio n1.Open()
Try
picnt = Me.SqlDataAdapt er1.Update(Me.T errDataSet)
Catch ex As Exception
Dim s As String = ex.Message
End Try
Me.SqlConnectio n1.Close()
End Sub

Lloyd Sheen

[Lloyd Sheen]

Ok, things are getting really strange. First I edit the
UpdateCommand.C ommandText 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
TerritoryDescri ption = @TerritoryDescr iption, RegionID = @RegionID WHERE
(TerritoryID = @TerritoryID)"
SqlDataAdapter1 .UpdateCommand. Parameters("@Re gionID").Value =
Me.TextBox1.Tex t
SqlDataAdapter1 .UpdateCommand. Parameters("@Te rritoryDescript ion").Value =
Me.TextBox2.Tex t
SqlDataAdapter1 .UpdateCommand. Parameters("@Te rritoryID").Val ue =
Me.TextBox3.Tex t
picnt = Me.SqlDataAdapt er1.Update(Me.T errDataSet)

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

"Suresh" <an*******@disc ussions.microso ft.com> wrote in message
news:C3******** *************** ***********@mic rosoft.com...

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(mainta in 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:

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.EventArg s) Handles MyBase.Load
'Put user code to initialize the page here
If Not Page.IsPostBack Then
Me.SqlConnectio n1.Open()
Me.SqlDataAdapt er1.Fill(Me.Ter rDataSet)
Dim dv As DataView = Me.TerrDataSet. Tables(0).Defau ltView
Me.DataBind()
Me.SqlConnectio n1.Close()
Me.Session.Add( "DataSet", Me.TerrDataSet)
Else
Me.TerrDataSet = CType(Me.Sessio n.Item("DataSet "), TerrDataSet)
End If
End Sub
Private Sub Button1_Click(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles Button1.Click
Dim picnt As Integer
Me.SqlConnectio n1.Open()
Try
picnt = Me.SqlDataAdapt er1.Update(Me.T errDataSet)
Catch ex As Exception
Dim s As String = ex.Message
End Try
Me.SqlConnectio n1.Close()
End Sub

Lloyd Sheen

[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 TerritoryDescri ption = @TerritoryDescr iption,
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 =, TerritoryDescri ption =
@TerritoryDescr iption, 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

"Lloyd Sheen" <sq************ *******@tostops pamhotmail.com> wrote in message
news:SG******** *************@n ews04.bloor.is. net.cable.roger s.com...

Ok, things are getting really strange. First I edit the
UpdateCommand.C ommandText 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
TerritoryDescri ption = @TerritoryDescr iption, RegionID = @RegionID WHERE
(TerritoryID = @TerritoryID)"
SqlDataAdapter1 .UpdateCommand. Parameters("@Re gionID").Value =
Me.TextBox1.Tex t
SqlDataAdapter1 .UpdateCommand. Parameters("@Te rritoryDescript ion").Value = Me.TextBox2.Tex t
SqlDataAdapter1 .UpdateCommand. Parameters("@Te rritoryID").Val ue =
Me.TextBox3.Tex t
picnt = Me.SqlDataAdapt er1.Update(Me.T errDataSet)

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

"Suresh" <an*******@disc ussions.microso ft.com> wrote in message
news:C3******** *************** ***********@mic rosoft.com...

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(mainta in 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:

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.EventArg s) Handles MyBase.Load
'Put user code to initialize the page here
If Not Page.IsPostBack Then
Me.SqlConnectio n1.Open()
Me.SqlDataAdapt er1.Fill(Me.Ter rDataSet)
Dim dv As DataView = Me.TerrDataSet. Tables(0).Defau ltView
Me.DataBind()
Me.SqlConnectio n1.Close()
Me.Session.Add( "DataSet", Me.TerrDataSet)
Else
Me.TerrDataSet = CType(Me.Sessio n.Item("DataSet "), TerrDataSet)
End If
End Sub
Private Sub Button1_Click(B yVal sender As System.Object, ByVal e

As System.EventArg s) Handles Button1.Click
Dim picnt As Integer
Me.SqlConnectio n1.Open()
Try
picnt = Me.SqlDataAdapt er1.Update(Me.T errDataSet)
Catch ex As Exception
Dim s As String = ex.Message
End Try
Me.SqlConnectio n1.Close()
End Sub

Lloyd Sheen