ado.net sqlserver dataadapter

karunam

Member
Joined
Aug 1, 2005
Messages
11
Programming Experience
Beginner
hi all,


A bit of problem which i am not able to resolv.please help.I am using
dataAdapter.Update(ds, "tablename") to update table.object reference set to instance of object is error .following is sample code sending.

'on form load

Dim dataAdapter As New SqlDataAdapter(cmdString, conn)
Dim ds As New DataSet
dataAdapter.Fill(ds, "testmaster")
dataTable = ds.Tables("Testmaster")

'for initializing commads for updat command initialization

Dim updateCommand As New SqlCommand("UpdateProc", conn)
updateCommand.CommandType = CommandType.StoredProcedure
dataAdapter.UpdateCommand = updateCommand
AddParams(dataAdapter.UpdateCommand, "testdesc", "yearoftest")

'on update button following is the code

Dim row As DataRow = dataTable.Rows(currRec)
row.BeginEdit()
row("testdesc") = txtArticleTitle.Text
row("yearoftest") = txtArticleTopic.Text
row.EndEdit()
dataAdapter.Update(ds, "article")

Thanks in advance

 
Moved to more appropriate forum.

You have declared your "dataAdapter" and "ds" variables as local to the Load event handler, which means that those variables cease to exist once that method completes. If you want to be able to access a variable throughout a class then you need to declare it at the class level, i.e. outside any method definition and usually at the top of the class definition. You can then refer to that variable in any method because any method can see it.
 
Thanku Mr.jmcilhinney.just was waiting for replies .was in dire need of help.but still prob has been solved sending complete code.pl. could u take out some time to help.This i am sending clearly for deletion activity table having three fields testid,testdesc,testyear showing only testdesc and testyear.Following is the code



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


Public Class ADOForm
Inherits System.Windows.Forms.Form

' Private global members to be used in various methods
Public conn As New SqlConnection
Public dataAdapter As SqlDataAdapter
Public ds As DataSet
Public dataTable As dataTable
Public currRec As Integer = 0
Public totalRec As Integer = 0
Public insertSelected As Boolean


Private Sub btnLoadTable_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnLoadTable.Click
Me.Cursor = Cursors.WaitCursor

Dim connectionString As String = "data source=localhost; Integrated Security=SSPI;Initial Catalog=northwind; uid=sa; Pwd="
Dim conn As New SqlConnection(connectionString)
conn.Open()
Dim cmdString As String = "SELECT * FROM testmaster"
Dim dataAdapter As New SqlDataAdapter(cmdString, conn)
Dim ds As New DataSet
dataAdapter.Fill(ds, "testmaster")
dataTable = ds.Tables("testmaster")
currRec = 0

totalRec = dataTable.Rows.Count

FillControls() ' show current record on the form
InitializeCommands() ' prepare commands
ToggleControls(True) ' enable corresponding controls

Me.Cursor = Cursors.Default
End Sub

Private Sub InitializeCommands()

Dim cmdstring As String
Dim dataAdapter As New SqlDataAdapter(cmdstring, conn)

Dim selectCommand As New SqlCommand("SelectProc", conn)
selectCommand.CommandType = CommandType.StoredProcedure
dataAdapter.SelectCommand = selectCommand

' Preparing Delete SQL Command
Dim deleteCommand As New SqlCommand("DeleteProc", conn)
deleteCommand.CommandType = CommandType.StoredProcedure
dataAdapter.DeleteCommand = deleteCommand
AddParams(dataAdapter.DeleteCommand, "testId")
End Sub



Private Sub FillControls()
txtTestmasterId.Text = dataTable.Rows(currRec)("artId").ToString()
txtTestmasterTitle.Text = dataTable.Rows(currRec)("title").ToString()
txtTestmasterTopic.Text = dataTable.Rows(currRec)("topic").ToString()
txtNumOfLines.Text = dataTable.Rows(currRec)("lines").ToString()
txtDateOfPublishing.Text = dataTable.Rows(currRec)("dateOfPublishing").ToString()



End Sub

Private Sub btnNext_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnNext.Click

currRec += 1
If currRec >= totalRec Then
currRec = 0
End If
FillControls()
End Sub

Private Sub btnPrevious_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnPrevious.Click

currRec -= 1
If currRec < totalRec Then
currRec = totalRec - 1
End If
FillControls()
End Sub



Private Sub btnDeleteRecord_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnDeleteRecord.Click

Dim res As DialogResult = MessageBox.Show( _
"Are you sure you want to delete the current record?", _
"Confirm Record Deletion", MessageBoxButtons.YesNo)

If res = DialogResult.Yes Then
Dim row As DataRow = dataTable.Rows(currRec)
row.Delete()
dataAdapter.Update(ds, "testmaster")
ds.AcceptChanges()
lblLabel.Text = "Record Deleted"
totalRec -= 1
currRec = totalRec - 1
FillControls()
End If
End Sub

Private Sub ADOForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

End Sub
End Class
 
Back
Top