Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Importing txt file in sql database appending records

Posted on 2011-05-13
5
Medium Priority
?
176 Views
Last Modified: 2013-05-23
I am new to visual studio (2010) and vb.net (very green). I know how to accomplish this in access but have no clue in vs. I will try to be brief. I have created a db in sql 2000 called YamahaLabelDB. I have created two tables one called yam_con_label and one called archive. I imported test data via a text file using the wizard in sql svr to populate the yam_con_label table. I created a form with a data grid (see attched code) with help from ee which views all records stored in the yam_con_label table. (for test purposes to verify code works). What I need to have happen is create two buttons. One button will launch code that will delete all previous records in the yam_con_label table and pull records in from a comma delimited text file (header included in first record of text file) called yam_con_label located in a folder C:\label. I would then like the second a button which will launch code and append records from the yam_con_label table into the table called Archive. I have no idea how to accomplish this in VS which is why I have maxed points. code.txt
0
Comment
Question by:tmaususer
  • 3
  • 2
5 Comments
 
LVL 18

Accepted Solution

by:
dj_alik earned 1500 total points
ID: 35755302
Example here:
Bulk Insert into SQL Server using SqlBulkCopy
http://www.dotnetcurry.com/ShowArticle.aspx?ID=323
0
 
LVL 18

Assisted Solution

by:dj_alik
dj_alik earned 1500 total points
ID: 35755389
0
 

Author Comment

by:tmaususer
ID: 35755837
Sorry but I still cannot seem to grasp it. I am assuming I do not need to do anything with a connection string in any of the examples since that has been defined when the form loads (per my attached code) correct?
0
 

Author Comment

by:tmaususer
ID: 35756513
error after error after error. I'm lost....
0
 

Author Comment

by:tmaususer
ID: 35757169
Here is the code I have created. I do not receive any errors but pressing the button does not pull the data from my text file into my table called archive. What am I missing?

  Dim table As New DataTable()

        table.Columns.Add("Shipment_ID")
        table.Columns.Add("Supplier_Name")
        table.Columns.Add("Supplier_ID")
        table.Columns.Add("Supplier_Address")
        table.Columns.Add("Supplier_City")
        table.Columns.Add("Supplier_State")
        table.Columns.Add("Supplier_Zip")
        table.Columns.Add("To_Name")
        table.Columns.Add("To_Address")
        table.Columns.Add("To_City")
        table.Columns.Add("To_State")
        table.Columns.Add("To_Zip")
        table.Columns.Add("Part_No")
        table.Columns.Add("PO_No")
        table.Columns.Add("Part_Desc")
        table.Columns.Add("Qty")
        table.Columns.Add("Revision_No")
        table.Columns.Add("Size")
        table.Columns.Add("Stocklocation")
        table.Columns.Add("Sirlocation")
        table.Columns.Add("ContainerNum")
        table.Columns.Add("TotalContainers")
        table.Columns.Add("LabelCode")
        table.Columns.Add("DueDate")
        table.Columns.Add("DeliveryLocation")

        '--TextField Parser is used to read the files
        Dim parser As New FileIO.TextFieldParser("C:\yamaha\files\yam_con_label.txt")

        parser.Delimiters = New String() {","} ' fields are separated by comma
        parser.HasFieldsEnclosedInQuotes = True ' each of the values is enclosed with double quotes
        parser.TrimWhiteSpace = True

        '--First line is skipped , its the header
        parser.ReadLine()

        '-- Add all the rows to datatable
        Do Until parser.EndOfData = True
            table.Rows.Add(parser.ReadFields())
        Loop

        '--Create SQL query
        Dim strSql As String = "INSERT INTO Archive (shipment_id,supplier_name,supplier_id,supplier_address,supplier_city,supplier_state,supplier_zip,to_name,to_address,to_city,to_state,to_zip,part_no,po_no,part_desc,qty,revision_no,size,stocklocation,sirlocation,containernum,totalcontainers,labelcode,duedate,deliverylocation) VALUES (@shipment_id,@supplier_name,@supplier_id,@supplier_address,@supplier_city,@supplier_state,@supplier_zip,@to_name,@to_address,@to_city,@to_state,@to_zip,@part_no,@po_no,@part_desc,@qty,@revision_no,@size,@stocklocation,@sirlocation,@containernum,@totalcontainers,@labelcode,@duedate,@deliverylocation)"




        Global_sqlProdCN.Open()
        Global_sqlcmd = New SqlCommand(Global_strsql, Global_sqlProdCN)
        Global_sqlcmd.ExecuteNonQuery()
        Global_sqlProdCN.Close()
    End Sub
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
Screencast - Getting to Know the Pipeline
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses
Course of the Month10 days, 20 hours left to enroll

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question