Access Table Load

[roy_ware]

I'm using a VB.Net interface to load an Access table. The relationship is 1
input record to many table rows. The problem is that the first name of the
first row is populated for every row on the table (which curiously has the
correct total number of rows for the input file!). The reader does read the
data correctly.

If I put all the input record data into one row, it works perfectly.

Here's the partial code:

Dim objData as New DALBase
objData.SQL = "usp_LoadImportTable"
objData.InitializeCommand()

Line = objReader.ReadLine

' Code to read the input file and split the data into the desired fields -
works great.

objData.AddParameter("@ID", OleDb.OleDbType.VarChar, 10, strID)
objData.AddParameter("@Name", OleDb.OleDbType.VarChar, 50, strName1)
Call LoadTable()

objData.AddParameter("@ID", OleDb.OleDbType.VarChar, 10, strID)
objData.AddParameter("@Name", OleDb.OleDbType.VarChar, 50, strName2)

Private Sub LoadTable()
objData.OpenConnection()
intRowsAffected = objData.Command.ExecuteNonQuery()
' Error Handling
objData.CloseConnection()
End Sub

The Access SQL command "usb_LoadImportTable" is very simple:
INSERT INTO TempTable ID, Name FROM @ID, @NAME;

I figure that I need to clear the objData settings between the various
iterations of the input record, but everything I have tried has failed.

If the code extract isn't clear, I can email the actual Notepad file.

[Cor Ligthert [MVP]]

Roy,

You are using complex code for a simple problem. Why don't you first try it
with direct code.
Not Stored Procedures
No parameters
etc.

If it works than you can piece by piece change it in the code it has to be
and than in the way as you did, because the principle is right.

This in the context of. It is better to learn somebody how to fish than give
him fish.

I hope this helps,

Cor
"roy_ware" <ro*****@discussions.microsoft.com> schreef in bericht
news:C9**********************************@microsof t.com...

I'm using a VB.Net interface to load an Access table. The relationship is
1
input record to many table rows. The problem is that the first name of
the
first row is populated for every row on the table (which curiously has the
correct total number of rows for the input file!). The reader does read
the
data correctly.

If I put all the input record data into one row, it works perfectly.

Here's the partial code:

Dim objData as New DALBase
objData.SQL = "usp_LoadImportTable"
objData.InitializeCommand()

Line = objReader.ReadLine

' Code to read the input file and split the data into the desired
fields -
works great.

objData.AddParameter("@ID", OleDb.OleDbType.VarChar, 10, strID)
objData.AddParameter("@Name", OleDb.OleDbType.VarChar, 50, strName1)
Call LoadTable()

objData.AddParameter("@ID", OleDb.OleDbType.VarChar, 10, strID)
objData.AddParameter("@Name", OleDb.OleDbType.VarChar, 50, strName2)

Private Sub LoadTable()
objData.OpenConnection()
intRowsAffected = objData.Command.ExecuteNonQuery()
' Error Handling
objData.CloseConnection()
End Sub

The Access SQL command "usb_LoadImportTable" is very simple:
INSERT INTO TempTable ID, Name FROM @ID, @NAME;

I figure that I need to clear the objData settings between the various
iterations of the input record, but everything I have tried has failed.

If the code extract isn't clear, I can email the actual Notepad file.

[roy_ware]

I hate to admit it, but I don't know how to do it inline.......
"Cor Ligthert [MVP]" wrote:

Roy,

You are using complex code for a simple problem. Why don't you first try it
with direct code.
Not Stored Procedures
No parameters
etc.

If it works than you can piece by piece change it in the code it has to be
and than in the way as you did, because the principle is right.

This in the context of. It is better to learn somebody how to fish than give
him fish.

I hope this helps,

Cor
"roy_ware" <ro*****@discussions.microsoft.com> schreef in bericht
news:C9**********************************@microsof t.com...

I'm using a VB.Net interface to load an Access table. The relationship is
1
input record to many table rows. The problem is that the first name of
the
first row is populated for every row on the table (which curiously has the
correct total number of rows for the input file!). The reader does read
the
data correctly.

If I put all the input record data into one row, it works perfectly.

Here's the partial code:

Dim objData as New DALBase
objData.SQL = "usp_LoadImportTable"
objData.InitializeCommand()

Line = objReader.ReadLine

' Code to read the input file and split the data into the desired
fields -
works great.

objData.AddParameter("@ID", OleDb.OleDbType.VarChar, 10, strID)
objData.AddParameter("@Name", OleDb.OleDbType.VarChar, 50, strName1)
Call LoadTable()

objData.AddParameter("@ID", OleDb.OleDbType.VarChar, 10, strID)
objData.AddParameter("@Name", OleDb.OleDbType.VarChar, 50, strName2)

Private Sub LoadTable()
objData.OpenConnection()
intRowsAffected = objData.Command.ExecuteNonQuery()
' Error Handling
objData.CloseConnection()
End Sub

The Access SQL command "usb_LoadImportTable" is very simple:
INSERT INTO TempTable ID, Name FROM @ID, @NAME;

I figure that I need to clear the objData settings between the various
iterations of the input record, but everything I have tried has failed.

If the code extract isn't clear, I can email the actual Notepad file.

[Ron Allen]

Roy,
OleDb parameters aren't named the way SQL parameters are. They are just
? characters and the parameters must be added to the command in the order
they appear in the SQL text.
Also the insert command would be
INSERT INTO TempTable (ID, Name) Values (?, ?);
using standard SQL syntax.
If you are using an Access Query your parameters probably have different
names than you are expecting. I think that you need to include the
enclosing square brackets in the parameter names.
You might get better answers in microsoft.public.dotnet.framework.adonet
which is dedicated to data access.

Ron Allen
"roy_ware" <ro*****@discussions.microsoft.com> wrote in message
news:C9**********************************@microsof t.com...

I'm using a VB.Net interface to load an Access table. The relationship is
1
input record to many table rows. The problem is that the first name of
the
first row is populated for every row on the table (which curiously has the
correct total number of rows for the input file!). The reader does read
the
data correctly.

If I put all the input record data into one row, it works perfectly.

Here's the partial code:

Dim objData as New DALBase
objData.SQL = "usp_LoadImportTable"
objData.InitializeCommand()

Line = objReader.ReadLine

' Code to read the input file and split the data into the desired
fields -
works great.

objData.AddParameter("@ID", OleDb.OleDbType.VarChar, 10, strID)
objData.AddParameter("@Name", OleDb.OleDbType.VarChar, 50, strName1)
Call LoadTable()

objData.AddParameter("@ID", OleDb.OleDbType.VarChar, 10, strID)
objData.AddParameter("@Name", OleDb.OleDbType.VarChar, 50, strName2)

Private Sub LoadTable()
objData.OpenConnection()
intRowsAffected = objData.Command.ExecuteNonQuery()
' Error Handling
objData.CloseConnection()
End Sub

The Access SQL command "usb_LoadImportTable" is very simple:
INSERT INTO TempTable ID, Name FROM @ID, @NAME;

I figure that I need to clear the objData settings between the various
iterations of the input record, but everything I have tried has failed.

If the code extract isn't clear, I can email the actual Notepad file.