[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

vb.net program + database

Posted on 2004-08-09
16
Medium Priority
?
200 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
14 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
Industry Leaders: 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 200 total points
ID: 11759839
u should the mydatarow section in the loop
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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 …
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

873 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