Solved

vb.net program + database

Posted on 2004-08-09
16
188 Views
Last Modified: 2010-04-23
I am doing a small project..This is a small project I need to transfer the information from the text file into a database

Here is the question..

Each row in the text file represents the data for one customer..Each piece of data about a customer within a row is separated by the character ‘#’

Here is the example which can be found in the textfile...

William#New York#2342911#abc@aol.com#USA
William#New York#2342911#abc@aol.com#USA
William#New York#2342911#

.

So i now need create customer table in MS SQL Server 2000 and write a VB.NET program that will read all the customer data from the text file and store the data into the customer table.


The CustID is automatically generated by SQL Server 2000 upon the insertion of a row of customer data.

To avoid invalid data, the program has to check that there are 5 data fields for each row in the text file before inserting the row into the database.

There is also records which have only 3data fields...So these records should not be added..This should be displayed in a label to show that these are invalid data..

My program should have a Datagrid to display(the table)
1 label to show the contents in the file
1 label to show invalid data
1 label to display the statistics

The program must also provide the following information after the task is completed:
i)    Total number of customer records read. This number should be the same as the total number of customer records in the text file.
ii)    Total number of customer records created in the SQL Server. (i.e. valid records with 5 data fields).

I am stuck with the coding in reading my file and checking if my data is valid(min 5data fields) and adding it to my data grid..

Thanks alot
0
Comment
Question by:segar123
  • 7
  • 7
16 Comments
 
LVL 8

Expert Comment

by:razo
Comment Utility
this code will help u with ur problem

Dim sqltext As String
        Dim input As TextReader = File.OpenText("filename")
        Dim line As String
        Dim process As Integer
        line = input.ReadLine
        line = input.ReadLine
        Do While Not (line Is Nothing)
            Dim myArray() As String = Split(line, "#")

            process = process + 1
            If myArray.Length = 5 Then
                Try
                    sqltext = "insert into customer(row1,row2,row3,row4,row5)values ('" & myArray(0) & "','" & myArray(1)& "',myArray(2) & "'','" & myArray(3) & "','" & myArray(4)& "')"

                    comm.CommandText = sqltext
                    comm.ExecuteNonQuery()
                Catch ex As Exception
                                End Try
            else
                    label1.text=label1.text&"row"&process&"has invalid data"
            End If
            line = input.ReadLine
        Loop
        input.Close()
0
 

Author Comment

by:segar123
Comment Utility
Thanks a lot. But i need to display my table in a Datagrid first...Then update to the sql server later...
0
 
LVL 8

Expert Comment

by:razo
Comment Utility
ok so instead of inserting to the database add to the datagrid rows
0
 

Author Comment

by:segar123
Comment Utility
      myTable = myDataSet.Tables.Item(0)
        myDataRow = myTable.NewRow
        myDataRow("CustID") = str(0)
        myDataRow("Name") = str(1)
        myDataRow("Address") = str(2)
        myDataRow("Contact") = str(3)
        myDataRow("Email") = str(4)
        myDataRow("Country") = str(5)
        myTable.Rows.Add(myDataRow)

My datagrid seems to be displaying only number and not String...
0
 

Author Comment

by:segar123
Comment Utility
By the way your myArray() is know as str()...
0
 

Author Comment

by:segar123
Comment Utility

Imports System.IO

Public Class Form1
    Inherits System.Windows.Forms.Form
    Dim myDataSet As DataSet = New DataSet
    Dim myTable As DataTable
    Dim myDataRow As DataRow
    Dim OpenFileDialog1 As New OpenFileDialog
    Dim line As String
    Dim i As Integer = 0
    Dim x As Integer = 0
 

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
       
        SqlConnection1.Open()
        SqlDataAdapter1.Fill(myDataSet)
        SqlConnection1.Close()


        If OpenFileDialog1.ShowDialog() = DialogResult.OK Then
            Dim sr As New System.IO.StreamReader("C:\customer.txt")


            line = sr.ReadLine()
            While Not line Is Nothing
                Console.WriteLine(line)


                Dim str() As String = Split(line, "#")
                Dim lenght As Integer = str.Length
                If lenght = 5 Then
                    i = i + 1
                    RichTextBox3.Text = i
                    RichTextBox1.Text = line

                Else
                    x = x + 1
                    RichTextBox2.Text = x

                End If
                line = sr.ReadLine()
            End While

        End If
        SqlConnection1.Open()
        SqlDataAdapter1.Fill(myDataSet)
        SqlConnection1.Close()

        myTable = myDataSet.Tables.Item(0)
        myDataRow = myTable.NewRow
        myDataRow("CustID") = str(0)
        myDataRow("Name") = str(1)
        myDataRow("Address") = str(2)
        myDataRow("Contact") = str(3)
        myDataRow("Email") = str(4)
        myDataRow("Country") = str(5)
        myTable.Rows.Add(myDataRow)
        SqlDataAdapter1.Update(myDataSet)


        DataGrid1.DataSource = myDataSet.Tables("Customer")

    End Sub
0
 
LVL 8

Expert Comment

by:razo
Comment Utility
shouldnt the mydatarow part be added inside the loop when the array has 5 entries?
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 8

Expert Comment

by:razo
Comment Utility
by the way u should use 6 instead of 5 beacuase u added the customer id
and why ur filling the adapter twice?
0
 

Author Comment

by:segar123
Comment Utility
OK..I need to update my table with a customer ID...I do i add a CustID automatically when a new row is added...Or do i have to do it in SQL...

I did the necessary adjustments..And i can add display the text in the datagrid..But i face 1 problem it seems like the 1st 10lines in my datagrid is added with numbers then the next 10 is my records(which are the text)
0
 
LVL 8

Expert Comment

by:razo
Comment Utility
sorry didnt understand the problem
0
 

Author Comment

by:segar123
Comment Utility
The CustID is automatically generated by SQL Server 2000 upon the insertion of a row of customer data.
0
 
LVL 8

Expert Comment

by:razo
Comment Utility
ok i got that the dataadpater will handle this problem
i am asking about the text vs. number problem
0
 

Author Comment

by:segar123
Comment Utility
Well what problem i face is What my datagrid display is 10 Rows. with each colum containing values of 0 - 4 in order...

0     1      2       3        4

And the text 10rows of my Str()... as in       William  New York  2342911  abc@aol.com  USA
0
 
LVL 8

Accepted Solution

by:
razo earned 50 total points
Comment Utility
u should the mydatarow section in the loop
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now