Prev: MS Small Basic - teach youngins to program
Next: How to stop a service and wait until service stopped?
From: GADOI on 2 Feb 2010 17:05
Hello:
Using VS2008 .net 3.5

I have a data table (MyDataTable) that has records i want to insert into an
access accdb table (AllAgents).
I am using the following function

Public Function OverWriteAgents(ByVal MydataTable As DataTable) As
String
Dim AccessConnection As New
System.Data.OleDb.OleDbConnection("PROVIDER=Microsoft.Ace.OLEDB.12.0;Persist
Security Info = False; Data Source=" + MdbPath)
Try
AccessConnection.Open()
Catch ex As Exception
MsgBox(ex.Message)
End Try
Try
Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT
INTO AllAgents(LICNUM,NAME,EXPDATE) SELECT LICNUM,NAME,EXPDATE from
[MydataTable]", AccessConnection)
AccessCommand.ExecuteNonQuery()
AccessConnection.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
AccessConnection.Close()
End Try
End Function

but no records are updated because the inset statement contains the name of
the table but does not reference it.
I just dont know how to do this.
Help
Thanks
T

From: Armin Zingler on 2 Feb 2010 18:20
GADOI schrieb:
> Hello:
> Using VS2008 .net 3.5
>
> I have a data table (MyDataTable) that has records i want to insert into an
> access accdb table (AllAgents).
> I am using the following function
>
> Public Function OverWriteAgents(ByVal MydataTable As DataTable) As
> String
> Dim AccessConnection As New
> System.Data.OleDb.OleDbConnection("PROVIDER=Microsoft.Ace.OLEDB.12.0;Persist
> Security Info = False; Data Source=" + MdbPath)
> Try
> AccessConnection.Open()
> Catch ex As Exception
> MsgBox(ex.Message)
> End Try
> Try
> Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT
> INTO AllAgents(LICNUM,NAME,EXPDATE) SELECT LICNUM,NAME,EXPDATE from
> [MydataTable]", AccessConnection)
> AccessCommand.ExecuteNonQuery()
> AccessConnection.Close()
> Catch ex As Exception
> MessageBox.Show(ex.Message)
> Finally
> AccessConnection.Close()
> End Try
> End Function
>
> but no records are updated because the inset statement contains the name of
> the table but does not reference it.
> I just dont know how to do this.
> Help
> Thanks
> T

You are mixing two different worlds. One is your application, the other
one is the database engine. The database engine has no knowledge of your
application. It doesn't know what a DataTable is, and it has no knowledge about
a variable that you've once used to refer to an object a while back when
writing the application. All queries executed by the database engine are
based on tables and other queries.

In order to insert records, you must build the appropriate Insert SQL statement
for each record in a loop. You must use a different version of the Insert statement:

INSERT INTO table([list of fields]) VALUES ([list of values])

For this purpose, use the OleDbCommand's Parameters property. Within the loop
processing the DataRows in the DataTable, set each parameter's value to
the value from the corresponding field from the DataRow being processed.

You can also use an OleDBDataAdapter to synchronize the database with the
DataTable. With an OleDbCommandbuilder, the required Insert-, Delete- and
Update-SQL commands are created automatically, derived from the Select
command.

You'll find sufficient information in the ADO.net documentation:
http://msdn.microsoft.com/en-us/library/e80y5yhx.aspx

--
Armin



 | 
Pages: 1
Prev: MS Small Basic - teach youngins to program
Next: How to stop a service and wait until service stopped?