test file to sql

[swathys]

hi,

If the condition = true
How do i select all records stored in .txt (temporary text file) to be updated in sql table
How do i do that. Please help

Thank You

[samuel_1991]

Well, I would use

Dim AllContent As String() = File.ReadAllLines(path to your file)
For i As integer = 0 To AllContent.Length - 1
dbCommand.CommandText="insert into Table(ColumnName) Values (" & AllContent.GetValue(i) & ")"
dbConn.Open()
dbCommand.ExecuteNonQuery()
dbConn.Close()
'I assume you have the sql connection ready.
Next

[swathys]

Hi,

Thank You for the answer, i did exactly as your code but i receive an error say " object referance not set to an instance of an object".
is this because of the ";" which i use to separate the data in text file. Please guide

Thank You

Dim AllContent As String() = File.ReadAllLines("C:\Documents and Settings\user\Desktop\MY_Application\Bill Payment\BillPaymentRecords.txt")
For i As Integer = 0 To AllContent.Length - 1

cmdPayment.CommandText = "INSERT INTO PaymentDetails " _
& "(KioskID, KioskLocation, TransactionNo, SerialNo, TransactionDateTime, TransactionDate, TransactionTime, TransactionCode, AgencyCode, AgencyName, " _
& "AccountNo, BillNo, BillAmount, PaidAmount) Values (" & AllContent.GetValue(i) & ")"

conn.Open()
cmdPayment.ExecuteNonQuery()
conn.Close()
'I assume you have the sql connection ready.
Next

[samuel_1991]

Hi,

Thank You for the answer, i did exactly as your code but i receive an error say " object referance not set to an instance of an object".
is this because of the ";" which i use to separate the data in text file. Please guide

Thank You

Dim AllContent As String() = File.ReadAllLines("C:\Documents and Settings\user\Desktop\MY_Application\Bill Payment\BillPaymentRecords.txt")
For i As Integer = 0 To AllContent.Length - 1

cmdPayment.CommandText = "INSERT INTO PaymentDetails " _
& "(KioskID, KioskLocation, TransactionNo, SerialNo, TransactionDateTime, TransactionDate, TransactionTime, TransactionCode, AgencyCode, AgencyName, " _
& "AccountNo, BillNo, BillAmount, PaidAmount) Values (" & AllContent.GetValue(i) & ")"

conn.Open()
cmdPayment.ExecuteNonQuery()
conn.Close()
'I assume you have the sql connection ready.
Next

From your code:

cmdPayment.CommandText = "INSERT INTO PaymentDetails " _
                & "(KioskID, KioskLocation, TransactionNo, SerialNo, TransactionDateTime, TransactionDate, TransactionTime, TransactionCode, AgencyCode, AgencyName, " _
                & "AccountNo, BillNo, BillAmount, PaidAmount) Values (" & AllContent.GetValue(i) & ")"

What happen is that you want to insert values for KioskID, KioskLocation, TransactionNo, SerialNo, TransactionDateTime, TransactionDate, TransactionTime, TransactionCode, AgencyCode, AgencyName,AccountNo, BillNo, BillAmount, PaidAmount but your

Values (" & AllContent.GetValue(i) & ")"

means to insert value for KioskID, which happens to be the string value for all of them highlighted in bold.

In summary, my code was presumed that you have one column to update, and not to be separated by commas etc.

So to do this, modify the code to:

Dim AllContent As String() = File.ReadAllLines("C:\Documents and Settings\user\Desktop\MY_Application\Bill Payment\BillPaymentRecords.txt")

For i As integer = 0 To AllContent.Length - 1
Dim Details As String = AllContent.GetValue(i).ToString().Split(New Char() {";"c}, StringSplitOptions.RemoveEmptyEntries) 'To split each separated content on a line. I presume again, your text file is one record one line, and dont wrap it.
Dim KioskID As Integer = CInt(Details.GetValue(0)) 'For KioskID row. Adjust it to suit your placement of the line (I again presume your text file has the same order as the way you insert the records)
Dim KioskLocation As String = Details.GetValue(1)
Dim TransactionNo As Integer = CInt(Details.GetValue(2))
Dim SerialNo As Integer = CInt(Details.GetValue(3))
Dim TransactionDateTime As DateTime = CDate(Details.GetValue(4))
Dim TransactionDate As DateTime = CDate(Details.GetValue(5))
Dim TransactionTime As DateTime = CDate(Details.GetValue(6)) 'Dont you think that TransactionDate & TransactionTime is repeating with / part of TransactionDateTime??
Dim TransactionCode As Integer = CInt(Details.GetValue(7))
Dim AgencyCode As Integer = CInt(Details.GetValue(8))
Dim AgencyName As String = Details.GetValue(9)
Dim BillNo As Integer = CInt(Details.GetValue(10))
Dim BillAmount As Double = CDbl(Details.GetValue(11))
Dim PaidAmount As Double = CDbl(Details.GetValue(12))
cmdPayment.CommandText = "INSERT INTO PaymentDetails " _
                & "(KioskID, KioskLocation, TransactionNo, SerialNo, TransactionDateTime, TransactionDate, TransactionTime, TransactionCode, AgencyCode, AgencyName, " _
                & "AccountNo, BillNo, BillAmount, PaidAmount) Values (" & KioskID & ",'" & KioskLocation & "'," & TransactionNo & "," & SerialNo & "," & TransactionDateTime & ","  TransactionDate & ","  TransactionTime & ","  TransactionCode & ","  AgencyCode & ",'"  AgencyName & "'," & AccountNo & "," & BillNo & "," & BillAmount & "," & PaidAmount &")"
conn.Open()
            cmdPayment.ExecuteNonQuery()
            conn.Close()
Next

[swathys]

hi samuel,

thank you so much for the reply. But i still get the error

1. Dim Details As String = AllContent.GetValue(i).ToString().Split(New Char() {";"c}, StringSplitOptions.RemoveEmptyEntries)

error msg > Value of type '1-dimensional array of String' cannot be converted to 'String'.

2. SESSION_AGENCY_NAME = Details.GetValue(0)
error msg > 'GetValue' is not a member of 'String'.

Please guide

[samuel_1991]

My apologies, change

Dim Details As String

to

Dim Details As String[B]()[/B]

And

Details.GetValue(0)

to

Details.GetValue(0).ToString()

By the way, please check your first parameter is a string or integer etc, and adjust them accordingly. Without a sample on your text file, nobody could get the exact code for it.

[swathys]

hi samuel,

1 last question. I still got an error on (";"c)
Bounds can be specified only for the top-level array when initializing an array of arrays.

(";"c) - wat is c for

please help
THANK YOU

[samuel_1991]

hi samuel,

1 last question. I still got an error on (";"c)
Bounds can be specified only for the top-level array when initializing an array of arrays.

(";"c) - wat is c for

please help
THANK YOU

I can only say that, please check your text file again. Check that the number of ";" is the same as in the line.

Example: I had did a test on this code on a file with things like empty space with the records of :

1: 1;East Coast;12
2: 2;West Coast;13
3: 3;North Point;14
for KioskID, KioskLocation, TransactionNo respectively (I know that there are many details as well, but for simplicity sake, I have omitted them.)

The file content is as of follows:
1;East Coast;12
2;West Coast;13

3;North Point;14

, it will generate an error of array out of bounds issue.

No, I did not ask you to create a multi-dimensional array / arraylist as I am not familiar with that as well.

[swathys]

hi samuel,

Your post is really helpfull and THANK YOU VERY MUCH for the guidance.

swathys

[samuel_1991]

Before I forget, the code

(";"c)

, is actually says the char ";" without the quotes only. Thats all. I pick it from somewhere else as well.

[swathys]

thank you so much samuel.