VB.net and Sql server 2000 problem

[regwood79]

Hi everyone:
I am a complete noob to VB.net and I have a major problem. I am writing a program that will take data from a text file and send it to a sql server db. For the most part the program works. However, It seems that when the program runs it takes the first ten lines from the text file store them to the DB and then skips the next 14 lines then take the 10 after those. It repeats this patter throughout the entire text file. I am getting this error when the program runs:

System.Data.SqlClient.SqlException: String or binary data would be truncated.
The statement has been terminated.
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at DataImport.Module1.Main() in C:\Programming\VB\DataImport\Module1.vb:line 63

Any help would certainly be appreciated. I am providing the source code for what I am doing and the text file. Thanks in advance.


source code:

[size=2][color=#0000ff]Module[/color][/size][size=2] Module1
 
[/size][size=2][color=#0000ff]Sub[/color][/size][size=2] Main()
 
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] test_conn [/size][size=2][color=#0000ff]As[/color][/size][size=2][color=#0000ff]Boolean
 
[/color][/size][size=2][color=#0000ff]Dim[/color][/size][size=2] my_Conn [/size][size=2][color=#0000ff]As[/color][/size][size=2] SqlConnection
 
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] sql_cmd [/size][size=2][color=#0000ff]As[/color][/size][size=2] SqlCommand
 
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] my_cmd [/size][size=2][color=#0000ff]As[/color][/size][size=2][color=#0000ff]String
 
[/color][/size][size=2][color=#0000ff]Dim[/color][/size][size=2] filename, f2 [/size][size=2][color=#0000ff]As[/color][/size][size=2][color=#0000ff]String
 
[/color][/size][size=2][color=#0000ff]Dim[/color][/size][size=2] i [/size][size=2][color=#0000ff]As[/color][/size][size=2][color=#0000ff]Integer
 
 
 
[/color][/size][size=2][color=#0000ff]Try
 
[/color][/size][size=2]my_Conn = [/size][size=2][color=#0000ff]New[/color][/size][size=2] SqlConnection("server=130.20.64.157;trusted_connection=yes;database=Sensor_202")
 
my_Conn.Open()
 
Console.WriteLine("YOU ARE CONNECTED TO SENSOR_202")
 
Console.WriteLine("Database: " & my_Conn.Database)
 
Console.WriteLine("Database: " & my_Conn.ServerVersion)
 
Console.WriteLine("Database: " & my_Conn.DataSource)
 
[/size][size=2][color=#0000ff]Catch[/color][/size][size=2] e [/size][size=2][color=#0000ff]As[/color][/size][size=2] Exception
 
Console.WriteLine(e)
 
[/size][size=2][color=#0000ff]End[/color][/size][size=2][color=#0000ff]Try
 
[/color][/size][size=2]i = 0
 
filename = "C:\Out5.csv"
 
f2 = "C:\log.txt"
 
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] objreader [/size][size=2][color=#0000ff]As[/color][/size][size=2][color=#0000ff]New[/color][/size][size=2] StreamReader(filename)
 
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] objwriter [/size][size=2][color=#0000ff]As[/color][/size][size=2][color=#0000ff]New[/color][/size][size=2] StreamWriter(f2)
 
 
 
[/size][size=2][color=#0000ff]While[/color][/size][size=2] objreader.Peek <> -1
 
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] DBfields = Split(objreader.ReadLine(), ",")
 
Console.WriteLine(i)
 
my_cmd = "INSERT INTO Sensor_202(Array_ID,Yr,DOY,tme,Date_Stmp,Battery,Temp_Ref," _
 
& "Thermo_Cup6w, Thermo_Cup5w,Thermo_Cup4w,Thermo_Cup3w,Thermo_Cup2w,Thermo_Cup1w,Thermo_Cup1e,Thermo_Cup2e,Thermo_Cup3e,Thermo_Cup4e, Thermo_Cup5e, Thermo_Cup6e," _
 
& "Tipping_Buck6w, Tipping_Buck5w, Tipping_Buck4w, Tipping_Buck3w, Tipping_Buck2w, Tipping_Buck1w, Tipping_Buck1e, Tipping_Buck2e, Tipping_Buck3e, Tipping_Buck4e, Tipping_Buck5e, Tipping_Buck6e, " _
 
& "Dose_Counter6w, Dose_Counter5w, Dose_Counter4w, Dose_Counter3w, Dose_Counter2w, Dose_Counter1w, Dose_Counter1e, Dose_Counter2e, Dose_Counter3e, Dose_Counter4e, Dose_Counter5e, Dose_Counter6e, " _
 
& "XRD6w, XRD5w, XRD4w, XRD3w, XRD2w, XRD1w, XRD1e, XRD2e, XRD3e, XRD4e, XRD5e, XRD6e, " _
 
& "Cuml_DrainXRD6w, Cuml_DrainXRD5w, Cuml_DrainXRD4w, Cuml_DrainXRD3w, Cuml_DrainXRD2w, Cuml_DrainXRD1w, Cuml_DrainXRD1e, Cuml_DrainXRD2e, Cuml_DrainXRD3e, Cuml_DrainXRD4e, Cuml_DrainXRD5e, Cuml_DrainXRD6e, " _
 
& "Cuml_DrainTB6w, Cuml_DrainTB5w, Cuml_DrainTB4w, Cuml_DrainTB3w, Cuml_DrainTB2w, Cuml_DrainTB1w, Cuml_DrainTB1e, Cuml_DrainTB2e, Cuml_DrainTB3e, Cuml_DrainTB4e, Cuml_DrainTB5e, Cuml_DrainTB6e)" _
 
& " VALUES (" _
 
& DBfields(0) & "," & DBfields(1) & "," & DBfields(2) & "," & "'" & DBfields(3) & "'" & "," & DBfields(4) & "," & DBfields(5) & "," & DBfields(6) & "," & DBfields(7) & "," & DBfields(8) & "," & DBfields(9) & "," & DBfields(10) & "," _
 
& DBfields(11) & "," & DBfields(12) & "," & DBfields(13) & "," & DBfields(14) & "," & DBfields(15) & "," & DBfields(16) & "," & DBfields(17) & "," & DBfields(18) & "," & DBfields(19) & "," & DBfields(20) & "," & DBfields(21) & "," _
 
& DBfields(22) & "," & DBfields(23) & "," & DBfields(24) & "," & DBfields(25) & "," & DBfields(26) & "," & DBfields(27) & "," & DBfields(28) & "," & DBfields(29) & "," & DBfields(30) & "," & DBfields(31) & "," & DBfields(32) & "," _
 
& DBfields(33) & "," & DBfields(34) & "," & DBfields(35) & "," & DBfields(36) & "," & DBfields(37) & "," & DBfields(38) & "," & DBfields(39) & "," & DBfields(40) & "," & DBfields(41) & "," & DBfields(42) & "," & DBfields(43) & "," _
 
& DBfields(44) & "," & DBfields(45) & "," & DBfields(46) & "," & DBfields(47) & "," & DBfields(48) & "," & DBfields(49) & "," & DBfields(50) & "," & DBfields(51) & "," & DBfields(52) & "," & DBfields(53) & "," & DBfields(54) & "," _
 
& DBfields(55) & "," & DBfields(56) & "," & DBfields(57) & "," & DBfields(58) & "," & DBfields(59) & "," & DBfields(60) & "," & DBfields(61) & "," & DBfields(62) & "," & DBfields(63) & "," & DBfields(64) & "," & DBfields(65) & "," _
 
& DBfields(66) & "," & DBfields(67) & "," & DBfields(68) & "," & DBfields(69) & "," & DBfields(70) & "," & DBfields(71) & "," & DBfields(72) & "," & DBfields(73) & "," & DBfields(74) & "," & DBfields(75) & "," & DBfields(76) & "," _
 
& DBfields(77) & "," & DBfields(78) & ")"
 
 
 
i += 1
 
sql_cmd = [/size][size=2][color=#0000ff]New[/color][/size][size=2] SqlCommand(my_cmd, my_Conn)
 
[/size][size=2][color=#0000ff]Try
 
[/color][/size][size=2][color=#0000ff]Dim[/color][/size][size=2] inserter [/size][size=2][color=#0000ff]As[/color][/size][size=2][color=#0000ff]Integer[/color][/size][size=2] = sql_cmd.ExecuteNonQuery()
 
[/size][size=2][color=#008000]'Console.WriteLine(inserter)
 
[/color][/size][size=2][color=#0000ff]Catch[/color][/size][size=2] e [/size][size=2][color=#0000ff]As[/color][/size][size=2] Exception
 
[/size][size=2][color=#008000]'Console.WriteLine(e)
 
[/color][/size][size=2]objwriter.WriteLine("this is inserter: " & i)
 
objwriter.WriteLine(e)
 
[/size][size=2][color=#008000]'objwriter.WriteLine(my_cmd)
 
 
 
[/color][/size][size=2][color=#0000ff]End[/color][/size][size=2][color=#0000ff]Try
 
[/color][/size][size=2][color=#0000ff]End[/color][/size][size=2][color=#0000ff]While
 
[/color][/size][size=2]objreader.Close()
 
my_Conn.Close()
 
[/size][size=2][color=#0000ff]End[/color][/size][size=2][color=#0000ff]Sub
 
 
 
End[/color][/size][size=2][color=#0000ff]Module
 
[/color][/size]

text file is attached to post

 

[regwood79]

problem revisited

I have found a problem that could lead to the solution to my previous problem. I tried a smaller data set. When I did that I found that when I read the data from the text file using the Peek() method for some reason it reading one value from each line of my text file.

For example.

Say i had this text file:
5, tre, foo
2, soo, too
3, eer, boo


then i used the peek to go through the file line by line

then i do the split() on the text file

when i checked to see what is going into the array from the split() method
it produces results like
field(0) = 5
field(1) = soo
field(2) = boo

Instead of reading the values across

I believe it was it was doing this throught my entire original text file. Does anyone have any idea why it is doing this.

 

[jmcilhinney]

How about you read the line into a String variable, which you can then examine, and then Split that string instead of doing it all in one go. If you're sure it's working there is nothing wrong with combining operations like that but for debugging purposes how about you code it so you can see what is happening at each step.