Post by Peter W Jo » Thu, 05 May 2005 17:56:34

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

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

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


Dim custDS As DataSet = New DataSet



End Sub

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

Any ideas as to what is wrong?




Post by Peter Proo » Thu, 05 May 2005 18:18:25

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



Post by Cor Ligthe » Thu, 05 May 2005 18:37:51


In addition to Peter,

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

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,



Post by Peter W Jo » Thu, 05 May 2005 18:40:04


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?




Post by Peter W Jo » Thu, 05 May 2005 20:48:35

Thanks Guys,

I managed it by using this:-

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

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


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

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


All now working well.

Many thanks



Post by Peter Proo » Thu, 05 May 2005 23:14:00

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


Post by wandi » Sun, 14 May 2006 16:36:44

I am trying to update the customer table by using the updatecommd,
please see
below, however, when it runs it does not fire the update statement. I
ran the sql profiler
and the only statement the profiler shows is the 'Select * from
Customers...' and zero
rows updated.

Does any knwo why the updatecommand does not fire.

Thanks in advance


... code sample



Post by Cor Ligthe » Sun, 14 May 2006 17:56:56



We have a problem at the moment with showing links on our website.

Therefore is here the text.

This sample is to show the following items for an SQLServer Database
Creating by hand the select, insert, update, delete commands including the
Filling that table
Showing that table with negative seeded identnumbers
Update that table (you can edit the table)

Not implemented is any error handling beside showing that there is an error
(not at the places where that in fact cannot happen in this sample).

You need for this a new project, drag in a DataGridView and a Button on the
form and paste this code in the class. (Although it is 2005 is it as well to
use with 2003 where you than have to change the DataGridView for a DataGrid
and some of the code.

If you want to use another DataBaseName change that name at DBName, be aware
that it in advance of the sample every time will be Droped (Deleted).

Imports System.Data.sqlclient
Imports System.Data
Public Class Form1
Private DBName As String = "TestDataBaseAutoNumber"
Private ConnString As String
Friend da As New SqlDataAdapter
Friend Conn As New SqlConnection
Dim dt As New DataTable

Private Sub Form1_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
'To have a test is a very very small database created
'In this parts are the commands
'This part is to test the sample
da.FillSchema(dt, SchemaType.Mapped)
Dim col As DataColumn = dt.Columns(0)
col.AutoIncrement = True
col.AutoIncrementSeed = -1
col.AutoIncrementStep = -1
For i As Integer = 0 To 3
dt.Rows(i)(1) = ChrW(i + 65)
dt.DefaultView.Sort = "WhatEver"
'the situation is only showed in the Datagrid
'the click on the button does the update
DataGridView1.DataSource = dt
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
End Sub

Public Sub CreateCommands()
Dim nb As Byte = 0
Dim cmdSelect As New SqlCommand
Dim cmdInsert As New SqlCommand
Dim cmdUpdate As New SqlCommand
Dim cmdDelete As New SqlCommand
da.DeleteCommand = cmdDelete
da.InsertCommand = cmdInsert
da.SelectCommand = cmdSelect
da.UpdateCommand = cmdUpdate
da.TableMappings.AddRange(New Common.DataTableMapping() _
{New Common.DataTableMapping("Table", _
"Sample", New Common.DataColumnMapping() {New
Common.DataColumnMapping("AutoId", "AutoId"), _
New Common.DataColumnMapping("WhatEver", "WhatEver")})})
cmdSelect.CommandText = "SELECT AutoId, WhatEver FROM Sample"
cmdSelect.Connection = Conn
cmdInsert.CommandText = "INSERT INTO Sample(WhatEver) " & _
"VALUES (@WhatEver); SELECT AutoId, " & _
"WhatEver FROM Sample WHERE (AutoId = Scope_Identity())"


Post by wandi » Mon, 15 May 2006 14:35:51

Thanks Cor for the quick respone. Actually I was hoping if someone
could pinpoint the
problem with my update codes. Why updatecommand would not fire? If I
use the
ExecuteNonQuery() instead of update(datatable1) then update works fine,
but why?



Post by Cor Ligthe » Mon, 15 May 2006 16:27:37


In my idea is your update command not equal to the one in the sample. I miss
at least the reading of the current item from the database to check to the
old one


"wandii" < XXXX@XXXXX.COM > schreef in bericht


Post by wandi » Wed, 17 May 2006 02:13:52

I tried another simple block of codes, but still not updating the
database and doesn't throw an exception. Again if I
use the ExecuteNonQuery() it updates the database. I can see reading
the table in the Sql Profiler, however, no update. Any idea?

Thanks in advace.

' select statement
cmd = New SqlClient.SqlCommand("Select CaseID, UserID from
dbo.CaseToClose", consql)
adapter.SelectCommand = cmd

' update statment
cmd = New SqlClient.SqlCommand(" UPDATE dbo.CaseToClose set
DateCompleted = Null, StatusID = 3 " +_
" WHERE CaseID = @CaseID" + _
" AND ProblemTypeID =
@ProblemType ", consql)

cmd.Parameters.Add("@CaseID", SqlDbType.VarChar, 10).Value =
cmd.Parameters.Add("@ProblemType", SqlDbType.VarChar, 10).Value
= ProblemType

adapter.UpdateCommand = cmd


Post by Cor Ligthe » Wed, 17 May 2006 03:13:17


A dataadapter does check if the original datarow is not changed.
(Concurrency checking) Therefore he needs a select as in the sample I gave

A profiler or an executenonquery don't need that select, they just update
it, even if somebody else has changed the data. (Real nice if it is a for a
bank where the money was just taken and now put back or even raised. Don't
try it, this is the most know sample and protected).

I hope I make it clear with this. Have a look at the sample I gave you there
the commands, they are NOT equal to your code.


"wandii" < XXXX@XXXXX.COM > schreef in bericht