Why does setting the InsertCommand proprty of a Data Adapter fail?

Hi;

I am trying to do a simple operation of inserting a row into a database. I
have set a data adapter to get data from the database to check for
duplication. That works fine.
When I try to use this data adapter to INSERT the data into the database, if
there are no duplicate records, I get an exception when I try to set
MyAdapter.InsertCommand.CommandText property. Can Anyone tell me why?

Here is the code:

Private Sub btnOK_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnOK.Click

Dim sConnectionString As String =
"Provider=Microsoft.Jet.OleDB.4.0;" & "Data Source=" _
& System.AppDomain.CurrentDomain.BaseDirectory & "BDCIN.mdb;" _
& "User Id=admin;" & "Password="
Dim conLigacao As New OleDb.OleDbConnection
Dim daAdaptador As New OleDb.OleDbDataAdapter("Produtos", conLigacao)
Dim dsProdutos As New DataSet
Dim dsAlteracoes As New DataSet
Dim sSQLProdutos As String
Dim sSQLInsertProdutos As String = "INSERT INTO Productos (Ref,
Nome) VALUES (1,'TESTE')"


Dim elementos As Integer
Try

If Not IsNumeric(txtRefProd.Text) Then

MessageBox.Show("O conteúdo da referência do Produto de ve
ser númerico.", "Valor errado")
txtRefProd.Focus()

Else
sSQLProdutos = "SELECT * FROM Produtos WHERE Ref =" &
txtRefProd.Text
conLigacao.ConnectionString = sConnectionString
conLigacao.Open()
daAdaptador.SelectCommand.CommandText = sSQLProdutos
daAdaptador.Fill(dsProdutos)
conLigacao.Close()

If dsProdutos.Tables("Table").Rows.Count > 0 Then
MessageBox.Show("A referência " & txtRefProd.Text & " já
existe.", "Referência já existente.")
txtRefProd.Focus()
Else
Dim rowNovoRegisto As DataRow
rowNovoRegisto = dsProdutos.Tables("Table").NewRow
rowNovoRegisto("Ref") = txtRefProd.Text
rowNovoRegisto("Nome") = txtNomProd.Text
dsProdutos.Tables("Table").Rows.Add(rowNovoRegisto)
dsAlteracoes = dsProdutos.GetChanges()
conLigacao.Open()
daAdaptador.InsertCommand.CommandText = sSQLInsertProdutos
daAdaptador.Update(dsAlteracoes, "Produtos")
conLigacao.Close()
Me.Close()

End If


End If
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try


End Sub

I tried this approach from a WROX book, but It does not work. The exception
I get from the line "daAdaptador.InsertCommand.CommandText =
sSQLInsertProdutos" is:

System.NullReferenceException: Object reference not set to an instance of
an object.
at Manutenção_Intermix.IntroProduto.btnOK_Click(Object sender, EventArgs
e) in C:\Documents and Settings\Pedro\My Documents\Visual Studio
Projects\Estudo VB\Manutenção Intermix\IntroProduto.vb:line 162

Thanks for any help.

piyush
Fri May 27 04:49:22 CDT 2005

Hi,

U need to instantiate (Create memory) the insertcommand.......

Add the following lines

dsProdutos.Tables("Table").Rows.Add(rowNovoRegisto)
dsAlteracoes = dsProdutos.GetChanges()
conLigacao.Open()

'New Lines added.........
Dim cmdNew As New OleDb.OleDbCommand
daAdaptador.InsertCommand = cmdNew
'New Lines added - Finished

daAdaptador.InsertCommand.CommandText = sSQLInsertProdutos


This would sureshot remove your error. I have replicated your problem at my
end and have removed like this.


--
Piyush Thakuria
Technical Lead
Microsoft Technologies

"Mallgur" wrote:

> Hi;
>
> I am trying to do a simple operation of inserting a row into a database. I
> have set a data adapter to get data from the database to check for
> duplication. That works fine.
> When I try to use this data adapter to INSERT the data into the database, if
> there are no duplicate records, I get an exception when I try to set
> MyAdapter.InsertCommand.CommandText property. Can Anyone tell me why?
>
> Here is the code:
>
> Private Sub btnOK_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles btnOK.Click
>
> Dim sConnectionString As String =
> "Provider=Microsoft.Jet.OleDB.4.0;" & "Data Source=" _
> & System.AppDomain.CurrentDomain.BaseDirectory & "BDCIN.mdb;" _
> & "User Id=admin;" & "Password="
> Dim conLigacao As New OleDb.OleDbConnection
> Dim daAdaptador As New OleDb.OleDbDataAdapter("Produtos", conLigacao)
> Dim dsProdutos As New DataSet
> Dim dsAlteracoes As New DataSet
> Dim sSQLProdutos As String
> Dim sSQLInsertProdutos As String = "INSERT INTO Productos (Ref,
> Nome) VALUES (1,'TESTE')"
>
>
> Dim elementos As Integer
> Try
>
> If Not IsNumeric(txtRefProd.Text) Then
>
> MessageBox.Show("O conteúdo da referência do Produto de ve
> ser númerico.", "Valor errado")
> txtRefProd.Focus()
>
> Else
> sSQLProdutos = "SELECT * FROM Produtos WHERE Ref =" &
> txtRefProd.Text
> conLigacao.ConnectionString = sConnectionString
> conLigacao.Open()
> daAdaptador.SelectCommand.CommandText = sSQLProdutos
> daAdaptador.Fill(dsProdutos)
> conLigacao.Close()
>
> If dsProdutos.Tables("Table").Rows.Count > 0 Then
> MessageBox.Show("A referência " & txtRefProd.Text & " já
> existe.", "Referência já existente.")
> txtRefProd.Focus()
> Else
> Dim rowNovoRegisto As DataRow
> rowNovoRegisto = dsProdutos.Tables("Table").NewRow
> rowNovoRegisto("Ref") = txtRefProd.Text
> rowNovoRegisto("Nome") = txtNomProd.Text
> dsProdutos.Tables("Table").Rows.Add(rowNovoRegisto)
> dsAlteracoes = dsProdutos.GetChanges()
> conLigacao.Open()
> daAdaptador.InsertCommand.CommandText = sSQLInsertProdutos
> daAdaptador.Update(dsAlteracoes, "Produtos")
> conLigacao.Close()
> Me.Close()
>
> End If
>
>
> End If
> Catch ex As Exception
> MessageBox.Show(ex.ToString)
> End Try
>
>
> End Sub
>
> I tried this approach from a WROX book, but It does not work. The exception
> I get from the line "daAdaptador.InsertCommand.CommandText =
> sSQLInsertProdutos" is:
>
> System.NullReferenceException: Object reference not set to an instance of
> an object.
> at Manutenção_Intermix.IntroProduto.btnOK_Click(Object sender, EventArgs
> e) in C:\Documents and Settings\Pedro\My Documents\Visual Studio
> Projects\Estudo VB\Manutenção Intermix\IntroProduto.vb:line 162
>
> Thanks for any help.
>

Mallgur
Fri May 27 08:57:25 CDT 2005

Thank you very much.

Your instructions did indeed allow me to remove this error. I still have
some other problems, but I will try to overcome them myself before bothering
you again.

Thanks again.

"Piyush Thakuria" wrote:

> Hi,
>
> U need to instantiate (Create memory) the insertcommand.......
>
> Add the following lines
>
> dsProdutos.Tables("Table").Rows.Add(rowNovoRegisto)
> dsAlteracoes = dsProdutos.GetChanges()
> conLigacao.Open()
>
> 'New Lines added.........
> Dim cmdNew As New OleDb.OleDbCommand
> daAdaptador.InsertCommand = cmdNew
> 'New Lines added - Finished
>
> daAdaptador.InsertCommand.CommandText = sSQLInsertProdutos
>
>
> This would sureshot remove your error. I have replicated your problem at my
> end and have removed like this.
>
>
> --
> Piyush Thakuria
> Technical Lead
> Microsoft Technologies
>
> "Mallgur" wrote:
>
> > Hi;
> >
> > I am trying to do a simple operation of inserting a row into a database. I
> > have set a data adapter to get data from the database to check for
> > duplication. That works fine.
> > When I try to use this data adapter to INSERT the data into the database, if
> > there are no duplicate records, I get an exception when I try to set
> > MyAdapter.InsertCommand.CommandText property. Can Anyone tell me why?
> >
> > Here is the code:
> >
> > Private Sub btnOK_Click(ByVal sender As System.Object, ByVal e As
> > System.EventArgs) Handles btnOK.Click
> >
> > Dim sConnectionString As String =
> > "Provider=Microsoft.Jet.OleDB.4.0;" & "Data Source=" _
> > & System.AppDomain.CurrentDomain.BaseDirectory & "BDCIN.mdb;" _
> > & "User Id=admin;" & "Password="
> > Dim conLigacao As New OleDb.OleDbConnection
> > Dim daAdaptador As New OleDb.OleDbDataAdapter("Produtos", conLigacao)
> > Dim dsProdutos As New DataSet
> > Dim dsAlteracoes As New DataSet
> > Dim sSQLProdutos As String
> > Dim sSQLInsertProdutos As String = "INSERT INTO Productos (Ref,
> > Nome) VALUES (1,'TESTE')"
> >
> >
> > Dim elementos As Integer
> > Try
> >
> > If Not IsNumeric(txtRefProd.Text) Then
> >
> > MessageBox.Show("O conteúdo da referência do Produto de ve
> > ser númerico.", "Valor errado")
> > txtRefProd.Focus()
> >
> > Else
> > sSQLProdutos = "SELECT * FROM Produtos WHERE Ref =" &
> > txtRefProd.Text
> > conLigacao.ConnectionString = sConnectionString
> > conLigacao.Open()
> > daAdaptador.SelectCommand.CommandText = sSQLProdutos
> > daAdaptador.Fill(dsProdutos)
> > conLigacao.Close()
> >
> > If dsProdutos.Tables("Table").Rows.Count > 0 Then
> > MessageBox.Show("A referência " & txtRefProd.Text & " já
> > existe.", "Referência já existente.")
> > txtRefProd.Focus()
> > Else
> > Dim rowNovoRegisto As DataRow
> > rowNovoRegisto = dsProdutos.Tables("Table").NewRow
> > rowNovoRegisto("Ref") = txtRefProd.Text
> > rowNovoRegisto("Nome") = txtNomProd.Text
> > dsProdutos.Tables("Table").Rows.Add(rowNovoRegisto)
> > dsAlteracoes = dsProdutos.GetChanges()
> > conLigacao.Open()
> > daAdaptador.InsertCommand.CommandText = sSQLInsertProdutos
> > daAdaptador.Update(dsAlteracoes, "Produtos")
> > conLigacao.Close()
> > Me.Close()
> >
> > End If
> >
> >
> > End If
> > Catch ex As Exception
> > MessageBox.Show(ex.ToString)
> > End Try
> >
> >
> > End Sub
> >
> > I tried this approach from a WROX book, but It does not work. The exception
> > I get from the line "daAdaptador.InsertCommand.CommandText =
> > sSQLInsertProdutos" is:
> >
> > System.NullReferenceException: Object reference not set to an instance of
> > an object.
> > at Manutenção_Intermix.IntroProduto.btnOK_Click(Object sender, EventArgs
> > e) in C:\Documents and Settings\Pedro\My Documents\Visual Studio
> > Projects\Estudo VB\Manutenção Intermix\IntroProduto.vb:line 162
> >
> > Thanks for any help.
> >

Marina
Fri May 27 09:10:10 CDT 2005

The InsertCommand property is not initialized to anything by the data
adapter. It is null. You are trying to set the Text property of
InsertCommand, but InsertCommand is not set to an object.

You need to set it to a new instance of OleDbCommand.

"Mallgur" <Mallgur@discussions.microsoft.com> wrote in message
news:9A617D8E-8FF1-450D-99BB-C905786616A7@microsoft.com...
> Hi;
>
> I am trying to do a simple operation of inserting a row into a database. I
> have set a data adapter to get data from the database to check for
> duplication. That works fine.
> When I try to use this data adapter to INSERT the data into the database,
> if
> there are no duplicate records, I get an exception when I try to set
> MyAdapter.InsertCommand.CommandText property. Can Anyone tell me why?
>
> Here is the code:
>
> Private Sub btnOK_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles btnOK.Click
>
> Dim sConnectionString As String =
> "Provider=Microsoft.Jet.OleDB.4.0;" & "Data Source=" _
> & System.AppDomain.CurrentDomain.BaseDirectory & "BDCIN.mdb;" _
> & "User Id=admin;" & "Password="
> Dim conLigacao As New OleDb.OleDbConnection
> Dim daAdaptador As New OleDb.OleDbDataAdapter("Produtos",
> conLigacao)
> Dim dsProdutos As New DataSet
> Dim dsAlteracoes As New DataSet
> Dim sSQLProdutos As String
> Dim sSQLInsertProdutos As String = "INSERT INTO Productos (Ref,
> Nome) VALUES (1,'TESTE')"
>
>
> Dim elementos As Integer
> Try
>
> If Not IsNumeric(txtRefProd.Text) Then
>
> MessageBox.Show("O conteúdo da referência do Produto de ve
> ser númerico.", "Valor errado")
> txtRefProd.Focus()
>
> Else
> sSQLProdutos = "SELECT * FROM Produtos WHERE Ref =" &
> txtRefProd.Text
> conLigacao.ConnectionString = sConnectionString
> conLigacao.Open()
> daAdaptador.SelectCommand.CommandText = sSQLProdutos
> daAdaptador.Fill(dsProdutos)
> conLigacao.Close()
>
> If dsProdutos.Tables("Table").Rows.Count > 0 Then
> MessageBox.Show("A referência " & txtRefProd.Text & "
> já
> existe.", "Referência já existente.")
> txtRefProd.Focus()
> Else
> Dim rowNovoRegisto As DataRow
> rowNovoRegisto = dsProdutos.Tables("Table").NewRow
> rowNovoRegisto("Ref") = txtRefProd.Text
> rowNovoRegisto("Nome") = txtNomProd.Text
> dsProdutos.Tables("Table").Rows.Add(rowNovoRegisto)
> dsAlteracoes = dsProdutos.GetChanges()
> conLigacao.Open()
> daAdaptador.InsertCommand.CommandText =
> sSQLInsertProdutos
> daAdaptador.Update(dsAlteracoes, "Produtos")
> conLigacao.Close()
> Me.Close()
>
> End If
>
>
> End If
> Catch ex As Exception
> MessageBox.Show(ex.ToString)
> End Try
>
>
> End Sub
>
> I tried this approach from a WROX book, but It does not work. The
> exception
> I get from the line "daAdaptador.InsertCommand.CommandText =
> sSQLInsertProdutos" is:
>
> System.NullReferenceException: Object reference not set to an instance of
> an object.
> at Manutenção_Intermix.IntroProduto.btnOK_Click(Object sender, EventArgs
> e) in C:\Documents and Settings\Pedro\My Documents\Visual Studio
> Projects\Estudo VB\Manutenção Intermix\IntroProduto.vb:line 162
>
> Thanks for any help.
>