Solved

vb.net program + database

Posted on 2004-08-09
16
191 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
ID: 11751573
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
ID: 11751655
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
ID: 11751709
ok so instead of inserting to the database add to the datagrid rows
0
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 

Author Comment

by:segar123
ID: 11751733
      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
ID: 11751759
By the way your myArray() is know as str()...
0
 

Author Comment

by:segar123
ID: 11751789

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
ID: 11751906
shouldnt the mydatarow part be added inside the loop when the array has 5 entries?
0
 
LVL 8

Expert Comment

by:razo
ID: 11751935
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
ID: 11752155
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
ID: 11752212
sorry didnt understand the problem
0
 

Author Comment

by:segar123
ID: 11752232
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
ID: 11752279
ok i got that the dataadpater will handle this problem
i am asking about the text vs. number problem
0
 

Author Comment

by:segar123
ID: 11752446
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
ID: 11759839
u should the mydatarow section in the loop
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

777 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