Problem with DataSets

[shanebrennan1968]

I'm hitting a stone wall and I hope someone can help me please. Please forgive me for any terminology I will undoubtly get wrong.

I have created a DataSet to a SQL Express server and have managed to Bind this dataset to a FormView in EditMode to allow me to edit the date. For this screen I don't want to use the built in update functions, as I will have a number of Multiselect Listboxes on the form view that I need to set and unset during the page load and update tasks.

Anyways My problem is, when I come to update the DataSet I'm hitting a "NullReferenceException was unhandled by user code" error. on the following line in the cmdUpdate_Click event:

MyDataSet.Tables("tblClients").Rows(0)("Name") = MyTextBox.Text

The MyTEstBox.text does indeed contain text from the TextBox control "Name" on the FormView.
The problems is the DataSet "MyDataSet" is completely and utterly empty! even though I used it on the PageLoad.

I know I am missing something simple but I can't figger it out - please help.

Thank you in advance of any help given.

Shane Brennan

My Code
/---------



Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Data


Partial Class Clients_ClientDetails4
Inherits System.Web.UI.Page

Dim MySqlDataAdapter As SqlDataAdapter
Dim MyDataSet As DataSet = New DataSet()



Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Me.Load
Dim CustomerID As Integer

CustomerID = Request("ID")
If IsPostBack Then
' do nothing
Else
Dim ConnectionString As String = ConfigurationManager.ConnectionStrings("FRAConnectionString").ToString
Dim SQLStr As String = "Select * from tblClients where ID=" & CustomerID & ";"
Dim MysqlConnection As SqlConnection
MysqlConnection = New SqlConnection(ConnectionString)

MySqlDataAdapter = New SqlDataAdapter(SQLStr, MysqlConnection)
Dim MyDataRowsCommandBuilder As SqlCommandBuilder = New SqlCommandBuilder(MySqlDataAdapter)
MySqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
MySqlDataAdapter.Fill(MyDataSet, "tblClients")

FormView1.DataSource = MyDataSet
FormView1.DataBind()
End If

End Sub

Protected Sub cmdUpdate_Click(ByVal sender As Object, ByVal e As System.EventArgs)

Dim MyTextBox As TextBox

MyTextBox = FormView1.FindControl("Name")
MyDataSet.Tables("tblClients").Rows(0)("Name") = MyTextBox.Text

MySqlDataAdapter.Update(MyDataSet, "tblClients")

End Sub

Protected Sub cmdCancel_Click(ByVal sender As Object, ByVal e As System.EventArgs)

End Sub
End Class

 

[KDavie]

The reason your getting the null reference is because you are trying to access a member of a row that doesn't exist:

MyDataSet.Tables("tblClients").[red]Rows(0)[/red]("Name") = MyTextBox.Text

Since there is no row at zero you cannot read the Name column.

As far as your dataset goes, please post your data-access code. There's no way of giving you meaningful feedback without being able to see what you're doing.

 

[shanebrennan1968]

Thanks for the Reply KDavie

Can you please explain what you mean by the "Data-access Code"?

WHen I pause the code in the "Page_load" event and highlight look at the watch for MyDateSet - I can interegate the watch and see the tables, etc. and it does bind to the FormView On my ClientDetails4.aspx form/page.

The code above is from my ClientDetails4.aspx.vb file.

When I pause the code in the sub cmdUpdate_Click - the watch for MyDataSet is completely empty.

Thanks in advance for any help.

 

[shanebrennan1968]

Just a thought....The MyDateSet....Is this losted when the page re-loads?

Do I need to use the Viewstate function to store MyDataSet after I first retrieve it, then load it from the view state on subsequent post-backs.

If the Page_Load ALWAYS run for every post pack? even button clicks?

 

[jbenson001]

Yes, you will have to persist the data set in session or viewstate.

 

[KDavie]

I apologize, I must have read your original post too fast. By data-access code I meant the code you are using to retrieve the data from the db. After reviewing your post again I see that you did, in fact, include it.

Your last thought was correct, the DataSet is losing scope when the response is sent back to client. You can store the DataSet in Session to persist it across post-backs.

On another note, you may want to consider removing your data-access code from your code-behind. Your presentation layer should have no direct knowledge of your database. There are several articles available that cover this topic in detail. I recommend reading about n-tier design. Let me know if you have any questions.

 

[shanebrennan1968]

MY HEAD HURTS!

I'm getting there thank you very much KDavie for your help - I feel like I am getting some where. After a couple of hours bashing my head on the table I finally did a jig around the room - much to my wifes amazement - SHANE DOES NOT DANCE

Ok I've finally got my head around(ish) the "Session State" thanks to Tek-tips and those lovely people over at Microsoft's MSDN (and I didn't need an interpreter this time).

A couple of things.

1) KDavies mentioned "removing your data-access code from your code-behind." - is this using the DAL and BLL? If so - I have done a little with asp 3.5's ObjectDataSources - is this just tapping into the functions/routines I would have created there?

2) I need to implement Optimistic Concurrency. I'm using an MSSQL Express database backend - would using the timestamp field be enough to check that there has been no changes?

Thank you very much to everyone for your help over the last few weeks.

Shane Brennan

My Code

/--------------------------------------------------------

Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Data


Partial Class Clients_ClientDetails5
Inherits System.Web.UI.Page

Dim MySqlDataAdapter As SqlDataAdapter
Dim MyDataSet As DataSet = New DataSet()



Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Me.Load
Dim CustomerID As Integer

CustomerID = Request("ID")
If IsPostBack Then
' do nothing
Else
If IsDBNull(Session("MyDataSet")) Or Session("MyDataSet") = Nothing Then

Dim ConnectionString As String = ConfigurationManager.ConnectionStrings("FRAConnectionString").ToString
Dim SQLStr As String = "Select * from tblClients where ID=" & CustomerID & ";"
Dim MysqlConnection As SqlConnection
MysqlConnection = New SqlConnection(ConnectionString)

MySqlDataAdapter = New SqlDataAdapter(SQLStr, MysqlConnection)
Dim MyDataRowsCommandBuilder As SqlCommandBuilder = New SqlCommandBuilder(MySqlDataAdapter)
MySqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
MySqlDataAdapter.Fill(MyDataSet, "tblClients")
Session("MyDataSet") = MyDataSet
Session("MySQLDataAdapter") = MySqlDataAdapter

Else
MyDataSet = Session("MyDataSet")
End If

FormView1.DataSource = MyDataSet
FormView1.DataBind()
End If

End Sub

Protected Sub cmdUpdate_Click(ByVal sender As Object, ByVal e As System.EventArgs)

Dim MyTextBox As TextBox

MyDataSet = Session("MyDataSet")
MySqlDataAdapter = Session("MySQLDataAdapter")

MyTextBox = FormView1.FindControl("Name")
MyDataSet.Tables("tblClients").Rows(0)("Name") = MyTextBox.Text

MySqlDataAdapter.Update(MyDataSet, "tblClients")

End Sub

Protected Sub cmdCancel_Click(ByVal sender As Object, ByVal e As System.EventArgs)

End Sub
End Class