Solved

vb.net program + database

Posted on 2004-08-09
16
194 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

688 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