[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

vb.net program + database

Posted on 2004-08-09
16
Medium Priority
?
198 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 200 total points
ID: 11759839
u should the mydatarow section in the loop
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

656 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