asked on

vb.net program + database

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

razo

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()

segar123

ASKER

Thanks a lot. But i need to display my table in a Datagrid first...Then update to the sql server later...

razo

ok so instead of inserting to the database add to the datagrid rows

segar123

ASKER

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...

segar123

ASKER

By the way your myArray() is know as str()...

segar123

ASKER

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

razo

shouldnt the mydatarow part be added inside the loop when the array has 5 entries?

razo

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

segar123

ASKER

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)

razo

sorry didnt understand the problem

segar123

ASKER

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

razo

ok i got that the dataadpater will handle this problem
i am asking about the text vs. number problem

segar123

ASKER

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

ASKER CERTIFIED SOLUTION

razo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial