ADO.NET error adding records

[Mark Smith via .NET 247]

Hello,

I am adding records to a table using ADO.NET

(I cannot use sql statements e.g. INSERT)

The program through an error on the final update.

The table consists of 3 field:
"A", "B" and "C X"

I am using
- mdac 2.8
- MSDE2000a

Any idea?



Dim DestAdapter As OdbcDataAdapter
Dim DestDS As DataSet
Dim DestDataAdapter As OdbcDataAdapter
Dim DestRow As DataRow
Dim DestTable As DataTable
Dim DestCommBuilder As OdbcCommandBuilder
Dim DestCommand As OdbcCommand

Try
DestDataAdapter = New OdbcDataAdapter("SELECT * FROM [tbltest]", "DSN=ODBC_tbltest;UID=sa;PWD=sa")
DestCommBuilder = New OdbcCommandBuilder(DestDataAdapter)

DestDS = New DataSet

DestDataAdapter.Fill(DestDS, "tbltest")

DestTable = DestDS.Tables("tbltest")
DestRow = DestTable.NewRow()

DestRow("A") = "A,test: " + Now.ToString
DestRow("B") = Now.ToString

DestTable.Rows.Add(DestRow)
DestDataAdapter.Update(DestTable)

MsgBox("Done")

Catch ex As Exception
MsgBox(ex.Message + vbCrLf + ex.Source & vbCrLf & vbCrLf & ex.StackTrace, MsgBoxStyle.Critical, "Error")
End Try

Thanks!

 

[William Ryan eMVP]

Have you confirmed that it's on the call to .Update();. I'm assuming it is
but it's better to be clera. I'd also change it from System.Exception and
wrap a try catch around it, trapping OdbcException and looking at its
ex.ToString(). What is the specific exception you are getting, rather
exception message? Looks like you haven't specified a connection for your
command object, that tends to be problematic.

If C X is an actual field name, you are probably having problems with
that... probably need to wrap the field name in [] ie [C X] , also make
sure you set the QuotePrefix and QuoteSuffix properties are set.

HTH,

Bill







--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/
http://www.devbuzz.com/content/zinc_personal_media_center_pg1.asp

Hello,

I am adding records to a table using ADO.NET

(I cannot use sql statements e.g. INSERT)

The program through an error on the final update.

The table consists of 3 field:
"A", "B" and "C X"

I am using
- mdac 2.8
- MSDE2000a

Any idea?



Dim DestAdapter As OdbcDataAdapter
Dim DestDS As DataSet
Dim DestDataAdapter As OdbcDataAdapter
Dim DestRow As DataRow
Dim DestTable As DataTable
Dim DestCommBuilder As OdbcCommandBuilder
Dim DestCommand As OdbcCommand

Try
DestDataAdapter = New OdbcDataAdapter("SELECT * FROM

[tbltest]", "DSN=ODBC_tbltest;UID=sa;PWD=sa")

DestCommBuilder = New OdbcCommandBuilder(DestDataAdapter)

DestDS = New DataSet

DestDataAdapter.Fill(DestDS, "tbltest")

DestTable = DestDS.Tables("tbltest")
DestRow = DestTable.NewRow()

DestRow("A") = "A,test: " + Now.ToString
DestRow("B") = Now.ToString

DestTable.Rows.Add(DestRow)
DestDataAdapter.Update(DestTable)

MsgBox("Done")

Catch ex As Exception
MsgBox(ex.Message + vbCrLf + ex.Source & vbCrLf & vbCrLf &

ex.StackTrace, MsgBoxStyle.Critical, "Error")

 

[Marina]

I think you might need to set the QuotePrefix and QuoteSuffix properties on
the command builder to [ and ], respectively. I think the problem may be in
the space of the 'C X' field name.

Hello,

I am adding records to a table using ADO.NET

(I cannot use sql statements e.g. INSERT)

The program through an error on the final update.

The table consists of 3 field:
"A", "B" and "C X"

I am using
- mdac 2.8
- MSDE2000a

Any idea?



Dim DestAdapter As OdbcDataAdapter
Dim DestDS As DataSet
Dim DestDataAdapter As OdbcDataAdapter
Dim DestRow As DataRow
Dim DestTable As DataTable
Dim DestCommBuilder As OdbcCommandBuilder
Dim DestCommand As OdbcCommand

Try
DestDataAdapter = New OdbcDataAdapter("SELECT * FROM

[tbltest]", "DSN=ODBC_tbltest;UID=sa;PWD=sa")

DestCommBuilder = New OdbcCommandBuilder(DestDataAdapter)

DestDS = New DataSet

DestDataAdapter.Fill(DestDS, "tbltest")

DestTable = DestDS.Tables("tbltest")
DestRow = DestTable.NewRow()

DestRow("A") = "A,test: " + Now.ToString
DestRow("B") = Now.ToString

DestTable.Rows.Add(DestRow)
DestDataAdapter.Update(DestTable)

MsgBox("Done")

Catch ex As Exception
MsgBox(ex.Message + vbCrLf + ex.Source & vbCrLf & vbCrLf &

ex.StackTrace, MsgBoxStyle.Critical, "Error")