Code Enhancement

[dr223]

Hallo,

I am working with a Vb.net 2005 application. On the application I have a Create table button which when clicked creates a table in Sql server with the code shown below;

Private Sub BtnCrtTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnCrtTable.Click

' Open the connection

Dim sql As String
Dim cmd As New SqlCommand
Dim conn As SqlConnection = GetDbConnection()

sql = "CREATE TABLE myTable" + "(myId INTEGER CONSTRAINT PKeyMyId PRIMARY KEY," + "myName CHAR(50), myAddress CHAR(255), myBalance FLOAT)"
cmd = New SqlCommand(sql, conn)
Try
cmd.ExecuteNonQuery()
' Adding records the table
sql = "INSERT INTO myTable(myId, myName, myAddress, myBalance) " + "VALUES (1001, 'Puneet Nehra', 'A 449 Sect 19, DELHI', 23.98 ) "
cmd = New SqlCommand(sql, conn)
cmd.ExecuteNonQuery()

sql = "INSERT INTO myTable(myId, myName, myAddress, myBalance) " + "VALUES (1002, 'Anoop Singh', 'Lodi Road, DELHI', 353.64) "
cmd = New SqlCommand(sql, conn)
cmd.ExecuteNonQuery()

sql = "INSERT INTO myTable(myId, myName, myAddress, myBalance) " + "VALUES (1003, 'Rakesh M', 'Nag Chowk, Jabalpur M.P.', 43.43) "
cmd = New SqlCommand(sql, conn)
cmd.ExecuteNonQuery()

sql = "INSERT INTO myTable(myId, myName, myAddress, myBalance) " + "VALUES (1004, 'Madan Kesh', '4th Street, Lane 3, DELHI', 23.00) "
cmd = New SqlCommand(sql, conn)
cmd.ExecuteNonQuery()

Catch ae As SqlException
MsgBox("Table has been created successfully", MsgBoxStyle.Information, "Load Data Application")
'MessageBox.Show(ae.Message.ToString())
End Try

End Sub


This is really a pre-defined table and the data is already specified to be inserted to the table. However, my primarily task is to read a flat file which has the data and consider the first line as the column to be column names.

Therefore, initially I created an open dialogue to browse for the text file and store the path such as E:\Datafile\test.txt, in a TextBox1 visible at run time.

Now the task is to change the above "create table" to read the txt file (column names are on the first line) and then create the table in the database.

I hope am clear to what I want to achieve, any help will be highly appreciated.

Thank you in advance

[chkmos]

Well, I understood. But, there is a problem concerning the data-file, do you use tab to seperate each value? Could you give me an example.

By they way, don't use + in concatenating string, use & instead. Use + only for the math operation.

[dglienna]

Not a matter in .Net, though + or &

[HanneSThEGreaT]

The best approach would be to make use of Store Procedures, much easier, much quicker, and easier to manipulate

//PS, dr233 please make use of [CODE] and [/CODE] Tags when posting code

[chkmos]

I think he used some tools to create a datafile, may be in CSV or XLS format, and then have a program to read the data file, and inserting the data into the database.