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
--
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.
"Peter W Johnson" <vk****@yahoo.com> schreef in bericht
news:#J*************@tk2msftngp13.phx.gbl...

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.

[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.

"Peter Proost" <pp*****@nospam.hotmail.com> wrote in message
news:ua**************@TK2MSFTNGP14.phx.gbl...

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.
"Peter W Johnson" <vk****@yahoo.com> schreef in bericht
news:#J*************@tk2msftngp13.phx.gbl...

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

--
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.
"Peter W Johnson" <vk****@yahoo.com> schreef in bericht
news:eb**************@TK2MSFTNGP14.phx.gbl...

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