473,837 Members | 1,531 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Access Table Load

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_LoadImport Table"
objData.Initial izeCommand()

Line = objReader.ReadL ine

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

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

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

Private Sub LoadTable()
objData.OpenCon nection()
intRowsAffected = objData.Command .ExecuteNonQuer y()
' Error Handling
objData.CloseCo nnection()
End Sub

The Access SQL command "usb_LoadImport Table" 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.

Nov 23 '05 #1
3 2011
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*****@discus sions.microsoft .com> schreef in bericht
news:C9******** *************** ***********@mic rosoft.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_LoadImport Table"
objData.Initial izeCommand()

Line = objReader.ReadL ine

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

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

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

Private Sub LoadTable()
objData.OpenCon nection()
intRowsAffected = objData.Command .ExecuteNonQuer y()
' Error Handling
objData.CloseCo nnection()
End Sub

The Access SQL command "usb_LoadImport Table" 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.

Nov 23 '05 #2
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*****@discus sions.microsoft .com> schreef in bericht
news:C9******** *************** ***********@mic rosoft.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_LoadImport Table"
objData.Initial izeCommand()

Line = objReader.ReadL ine

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

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

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

Private Sub LoadTable()
objData.OpenCon nection()
intRowsAffected = objData.Command .ExecuteNonQuer y()
' Error Handling
objData.CloseCo nnection()
End Sub

The Access SQL command "usb_LoadImport Table" 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.


Nov 23 '05 #3
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.publi c.dotnet.framew ork.adonet
which is dedicated to data access.

Ron Allen
"roy_ware" <ro*****@discus sions.microsoft .com> wrote in message
news:C9******** *************** ***********@mic rosoft.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_LoadImport Table"
objData.Initial izeCommand()

Line = objReader.ReadL ine

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

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

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

Private Sub LoadTable()
objData.OpenCon nection()
intRowsAffected = objData.Command .ExecuteNonQuer y()
' Error Handling
objData.CloseCo nnection()
End Sub

The Access SQL command "usb_LoadImport Table" 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.

Nov 23 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
15200
by: Bruce A. Julseth | last post by:
Newbie Question: Is there a way to import MS Access into MySQL? Maybe create a CSV or something. If so, what would be the SQL to do this? Thanks... Bruce
11
4207
by: Mike MacSween | last post by:
My client has an MS Access database application on her local machine. I have full access to that in terms of changing the design. I've got a simple PHP/MySql application on shared hosting, so no direct access to the db server. I'd like to give her the facility to export the information in her local Access application to the shared PHP/MySql site. From one command button (or similar) in the Access application.
3
4312
by: Prince Kumar | last post by:
When running LOAD with "ALLOW READ ACCESS", I get the following error if select is running againt the table (isolation UR). load.sql --------- db2 load from /u02/data/dly_d040817_test.dat of asc \ modified by implieddecimal nullindchar=Y \ fastparse anyorder \ METHOD L \(01 10,11 12,13 15,16 24,25 28,29 48,49 64\) \ MESSAGES /u02/load/msg/auth_detail.msg \
5
4011
by: Scott | last post by:
I have a customer that had developed an Access97 application to track their business information. The application grew significantly and they used the Upsizing Wizard to move the tables to SQL 2000. Of course there were no modifications made to the queries and they noticed significant performance issues. They recently upgraded the application to Access XP expecting the newer version to provide performance benefits and now queries take...
6
2811
by: fumanchu | last post by:
I've got to let end users (really just one person) load billing batch files into a third party app table. They need to specify the billing cycle name, the batch name, and the input file name and then I can use these values to execute a SQL Server stored procedure to load them into the batch table from the input file (the stored procedure uses Dynamic SQL to Bulk Insert the file to a temp table then processes it into the final table.) My...
6
2207
by: StepUP | last post by:
I'm a long time developer in Access. Noticing the "Why do IT guys hate MS Access?" thread made me think of my recent experiences in experimenting with VB .Net, and specifically, the Datagrid control. I have an app that has a very large table, about 700,000 records. Access handles this table with ease. I can load the entire table in a datasheet in seconds, with little additional impact on system memory. I can lookup, sort and filter on...
24
2796
by: Bob Alston | last post by:
Most of my Access database implementations have been fairly small in terms of data volume and number of concurrent users. So far I haven't had performance issues to worry about. <knock on wood> But I am curious about what techniques those of you who have done higher volume access implementations use to ensure high performance of the database in a multi-user 100mbps LAN implementation??? Thanks
4
2747
by: CPD | last post by:
From PHP, doing a DROP TABLE and a CREATE TABLE is successful, but doing a LOAD FILE keeps failing, with the error "Access denied for user 'db_user'@'localhost' (using password: YES)". The DROP/CREATE code and the LOAD FILE code are in separate .php files which I am running from my browser, but both commands are executed with mysql_query(). Don't know what I'm doing wrong, so I would appreciate any help with this. Both files run the...
2
4246
by: egoldthwait | last post by:
I need to convert a 17mb access 2000 db to Oracle and house it in a Citrix farm. The issue: we have never converted an Access Db to Oracle but can probably use Oracle's Workbench to assist with this. Also - the citrix folks do not want us to keep the FE in Access as the queries and other activities consume a lot of power. The users will be in 3 different offices across the globe all accessing the 1 Oracle DB in Citrix. Does anyone have...
6
6270
by: onnodb | last post by:
Hi all, While working on an Access UI to a MySQL database (which should be a reasonable, low-cost, flexible interface to the DB, better than web-based, much less costly than a full-fledged .NET app or so.... is it?), I tried measuring the bandwith consumed by the Access/MyODBC/MySQL link, which came out to be, er, quite high. I fancied it would be interesting to look at the queries Access throws at MySQL through the ODBC link, so I...
0
9837
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
9682
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10621
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10267
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
6998
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5845
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4474
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4038
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3123
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.