UpdateCommand

[Peter W Johnson]

Hi Guys,

I am having problems getting the following code to update an Access table. I
get the following error:-

An unhandled exception of type 'System.NullReferenceException' occurred in
MemberBase.exe

Additional information: Object reference not set to an instance of an
object.


This is the code:-

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnSave.Click

Dim myconnection As Odbc.OdbcConnection
myconnection = New Odbc.OdbcConnection("DSN=memberbase")
Dim mysql As String
Dim myda As Odbc.OdbcDataAdapter

mysql = "UPDATE tblMembers SET LastName = 'Johnson' WHERE MemberID
= 6666"

Dim dsOutCmdBuild As New Odbc.OdbcCommandBuilder(myda)
dsOutCmdBuild.QuotePrefix = "["
dsOutCmdBuild.QuoteSuffix = "]"

Dim UpdateCommand = New Odbc.OdbcCommand(mysql, myconnection)
myda.UpdateCommand = UpdateCommand

myconnection.Open()

Dim custDS As DataSet = New DataSet
myda.Fill(custDS)

myda.Update(custDS)

myconnection.Close()

End Sub

The LastName and MemberID fields exist and I have a MemberID of 6666.

Any ideas as to what is wrong?

Thanks

Peter.

 

[Peter Proost]

First turn option strict on at the top of your page,
next i think this is the problem:

Dim myda As Odbc.OdbcDataAdapter
should be
Dim myda As New Odbc.OdbcDataAdapter

this is also wrong:
Dim UpdateCommand = New Odbc.OdbcCommand(mysql, myconnection)
should be
Dim UpdateCommand As New Odbc.OdbcCommand(mysql, myconnection)

hth Greetz Peter

 

[Cor Ligthert]

Peter,

In addition to Peter,

A commandbuilder builds from a select command an update, insert and a delete
command.

I don't see a select command however an Update command that you use in that.

Also do you not have to use a dataadapter (or a dataset) to use this update
command however only a command.

I hope this helps,

Cor

 

[Peter W Johnson]

Peter,

Thanks. I amended the commands and now get a new error:-

An unhandled exception of type 'System.InvalidOperationException' occurred
in system.data.dll

Additional information: The SelectCommand property has not been initialized
before calling 'Fill'.

The error occurs at the "myda.Fill(custDS)" statement.

Any more ideas?

Cheers

Peter.

First turn option strict on at the top of your page,
next i think this is the problem:

Dim myda As Odbc.OdbcDataAdapter
should be
Dim myda As New Odbc.OdbcDataAdapter

this is also wrong:
Dim UpdateCommand = New Odbc.OdbcCommand(mysql, myconnection)
should be
Dim UpdateCommand As New Odbc.OdbcCommand(mysql, myconnection)

hth Greetz Peter
--
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.

Hi Guys,

I am having problems getting the following code to update an Access
table. I
get the following error:-

An unhandled exception of type 'System.NullReferenceException' occurred
in
MemberBase.exe

Additional information: Object reference not set to an instance of an
object.


This is the code:-

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnSave.Click

Dim myconnection As Odbc.OdbcConnection
myconnection = New Odbc.OdbcConnection("DSN=memberbase")
Dim mysql As String
Dim myda As Odbc.OdbcDataAdapter

mysql = "UPDATE tblMembers SET LastName = 'Johnson' WHERE MemberID
= 6666"

Dim dsOutCmdBuild As New Odbc.OdbcCommandBuilder(myda)
dsOutCmdBuild.QuotePrefix = "["
dsOutCmdBuild.QuoteSuffix = "]"

Dim UpdateCommand = New Odbc.OdbcCommand(mysql, myconnection)
myda.UpdateCommand = UpdateCommand

myconnection.Open()

Dim custDS As DataSet = New DataSet
myda.Fill(custDS)

myda.Update(custDS)

myconnection.Close()

End Sub

The LastName and MemberID fields exist and I have a MemberID of 6666.

Any ideas as to what is wrong?

Thanks

Peter.

 

[Peter W Johnson]

Thanks Guys,

I managed it by using this:-

Dim myconnection As New Odbc.OdbcConnection("DSN=memberbase")
myconnection.Open()

Dim mysql As String = "SELECT * from Members WHERE MemberID = 6666"
Dim dsUpdate As New DataSet

Dim daUpdate As Odbc.OdbcDataAdapter = New
Odbc.OdbcDataAdapter(mysql, myconnection)
Dim cmdBuilder As Odbc.OdbcCommandBuilder = New
Odbc.OdbcCommandBuilder(daUpdate)

daUpdate.Fill(dsUpdate)

dsUpdate.Tables(0).Rows(0)("LastName") = "Johnson"

' Update database with modified data
daUpdate.UpdateCommand = cmdBuilder.GetUpdateCommand()
daUpdate.Update(dsUpdate.Tables(0))

myconnection.Close()

All now working well.

Many thanks

Peter

 

[Peter Proost]

Glad to see you got it working, I wasn't at work for a couple of hours
that's why I didn't respond.

Greetz Peter