open text file write to file close file

[outcast1881]

Hi everyone,
I am using vb express and sql express.I can transfer a complete data table to a text file using sql and OLE connections.

but the thing I am trying to do is,after I insert a new row to my data table,
I want to open a text file,write the values in my new row to that file and save it and let's say the file called test1.txt(it will be csv of course),the logic is something like that:

open sql connection
insert into table a(col,col2) values (@a1,@a2)
open test1.txt
write (the row containing new col1,col2 values)
close test1.txt

do I have to use textwriter or streamwriter or smthg else?

Thank you in advance!

Can

 

[djjeavons]

Hi

I am not 100% sure I follow the question but I assume that once you have added your data to your table you still have access to the values that make up that new data. Assuming that you do, then you can use the StreamWriter to open a file and append your new values to it:

[SIZE=1][/SIZE][SIZE=1][COLOR=#0000ff][SIZE=1][COLOR=#008000]'The True flag instructs the StreamWriter to append data to the file
[/COLOR][/SIZE]Dim[/COLOR][/SIZE] sw [COLOR=#0000ff]As[/COLOR] [COLOR=#0000ff]New[/COLOR] System.IO.StreamWriter([COLOR=#800000]"c:\temp\test.csv"[/COLOR], [COLOR=#0000ff]True[/COLOR])
sw.WriteLine([COLOR=#800000]value1, value2[/COLOR])
sw.Close()[SIZE=1]

HTH
[/SIZE]

 

[outcast1881]

Hi djjeavons,
Thank you for your reply.I think I need to use streamwriter to write the data from the dataset and when I run the program 2nd time it must not overwrite the values which are saved in the text file.

[SIZE=2][COLOR=#008000]'Dim strLine As String, fName, i, j[/COLOR][/SIZE]
[SIZE=2][COLOR=#008000]'Dim objFileStream As FileStream[/COLOR][/SIZE]
[SIZE=2][COLOR=#008000]'Dim objStreamWriter As StreamWriter[/COLOR][/SIZE]
[SIZE=2][COLOR=#008000]'fName = "c:\myFile.txt"[/COLOR][/SIZE]
[SIZE=2][COLOR=#008000]'objFileStream = New FileStream(fName, FileMode.Create, FileAccess.Write)[/COLOR][/SIZE]
[SIZE=2][COLOR=#008000]'objStreamWriter = New StreamWriter(objFileStream)[/COLOR][/SIZE]
[SIZE=2][COLOR=#008000]'For i = 0 To dataset1.Tables(0).Rows.Count - 1[/COLOR][/SIZE]
[SIZE=2][COLOR=#008000]'For j = 0 To dataset1.Tables(0).Columns.Count - 1[/COLOR][/SIZE]
[SIZE=2][COLOR=#008000]'strLine = strLine & dataset1.Tables(0).Rows(i).Item(j).ToString & Chr(9)[/COLOR][/SIZE]
[SIZE=2][COLOR=#008000]'Next[/COLOR][/SIZE]
[SIZE=2][COLOR=#008000]'objStreamWriter.WriteLine(strLine)[/COLOR][/SIZE]
[SIZE=2][COLOR=#008000]'strLine = ""[/COLOR][/SIZE]
[SIZE=2][COLOR=#008000]'Next[/COLOR][/SIZE]
[SIZE=2][COLOR=#008000]'objStreamWriter.Close()[/COLOR][/SIZE]
[SIZE=2][COLOR=#008000]'objFileStream.Close()[/COLOR][/SIZE]

The code above overwrites the first written values in text file... Do you have an idea how I can do it?

Thanks&regards

 

[djjeavons]

Hi

If you look at the code that I posted earlier you will see that I am using the Streamwriter to write to a file and using the True flag to state that when writing to the file, contents should be appended and the file not overwritten. The way this works is that if the file doesn't exist, it will be created and the contents wrote, otherwise, if the file does exist then the contents will be appended to the end of the file.


HTH

 

[outcast1881]

Hi,
thank you,this way it works:

[/COLOR]
[SIZE=2]Dim[/SIZE][/COLOR][SIZE=2] strLine [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2], fName, i, j[/SIZE]
[SIZE=2][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] objStreamWriter [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] System.IO.StreamWriter([/SIZE][SIZE=2][COLOR=#800000]"c:\myFile.txt"[/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2]strLine = [/SIZE][SIZE=2][COLOR=#800000]""[/COLOR][/SIZE]
[SIZE=2]fName = [/SIZE][SIZE=2][COLOR=#800000]"c:\myFile.txt"[/COLOR][/SIZE]
[SIZE=2][COLOR=#800000][SIZE=2][COLOR=#0000ff]or[/COLOR][/SIZE][SIZE=2][COLOR=#000000] i = 0 [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]To[/COLOR][/SIZE][SIZE=2][COLOR=#000000] dataset1.Tables(0).Rows.Count - 1[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2] j = 0 [/SIZE][SIZE=2][COLOR=#0000ff]To[/COLOR][/SIZE][SIZE=2] dataset1.Tables(0).Columns.Count - 1[/SIZE]
[SIZE=2]strLine = strLine & dataset1.Tables(0).Rows(i).Item(j).ToString & ","[/SIZE]
[SIZE=2][COLOR=#0000ff]Next[/COLOR][/SIZE]
[SIZE=2]objStreamWriter.WriteLine(strLine)[/SIZE]
[SIZE=2][COLOR=#0000ff]Next[/COLOR][/SIZE]
[SIZE=2]objStreamWriter.Close()[/SIZE]
 
[COLOR=black]

and for doing just the opposite,I mean reading the csv files and saving it to sql data table,does streamreader work?'cause I am using oledb and sql connections and stored procedures for that...

Thank you again!

[/SIZE][/SIZE]

 

[djjeavons]

Hi

You could use a StreamReader to read a CSV file. The StreamReader has a method called ReadLine which can be used to read the file a line at a time. If the file is a CSV file then for each line you could also split the line based on the comma delimiter to retrieve a string array containing each individual field value.


HTH

 

[outcast1881]

hi,
Thank you for the reply,I am using this code for streamreader.BUt this code inserts the read data only to my first column.I have 4 columns and i am trying to insert the read the only into 2 of them,my csv file is like that:

112223;12313
132131;12341

Do you know what I am doing wrong?
regards,

Can

[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] sr1 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] StreamReader[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] strInput, arrStrInput() [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] intCurrPos [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] strbir, strdort [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] sqlconnection1 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlConnection = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlConnection([/SIZE][SIZE=2][COLOR=#800000]"data source=.\SQLEXPRESS;database=RFID;User ID=;Password="[/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] cmd1 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlCommand = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlCommand([/SIZE][SIZE=2][COLOR=#800000]"insert into test(bir,dort) values('"[/COLOR][/SIZE][SIZE=2] & strbir & [/SIZE][SIZE=2][COLOR=#800000]"','"[/COLOR][/SIZE][SIZE=2] & strdort & [/SIZE][SIZE=2][COLOR=#800000]"')"[/COLOR][/SIZE][SIZE=2], sqlconnection1)[/SIZE]
[SIZE=2]sr1 = File.OpenText([/SIZE][SIZE=2][COLOR=#800000]"c:\can2.txt"[/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2][COLOR=#0000ff]While[/COLOR][/SIZE][SIZE=2] sr1.Peek <> -1[/SIZE]
[SIZE=2]strInput = sr1.ReadLine[/SIZE]
[SIZE=2]arrStrInput = Split(strInput, [/SIZE][SIZE=2][COLOR=#800000]";"[/COLOR][/SIZE][SIZE=2], , CompareMethod.Text)[/SIZE]
[SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2] intCurrPos = 0 [/SIZE][SIZE=2][COLOR=#0000ff]To[/COLOR][/SIZE][SIZE=2] arrStrInput.Length - 1[/SIZE]
[SIZE=2][COLOR=#0000ff]Select[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][SIZE=2] intCurrPos[/SIZE]
[SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][SIZE=2] 0[/SIZE]
[SIZE=2]strbir = arrStrInput(intCurrPos)[/SIZE]
[SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][SIZE=2] 1[/SIZE]
[SIZE=2]strdort = arrStrInput(intCurrPos)[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Select[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Next[/COLOR][/SIZE]
[SIZE=2]sqlconnection1.Open()[/SIZE]
[SIZE=2]cmd1.ExecuteNonQuery()[/SIZE]
[SIZE=2]sqlconnection1.Close()[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]While[/COLOR][/SIZE]
[SIZE=2]sr1.Close()

[/SIZE]

 

[djjeavons]

Hi

The following is air coded so may contain some errors, but I think the logic you want would be something like:

Dim sqlconnection1 As SqlConnection = New SqlConnection("data source=.\SQLEXPRESS;database=RFID;User ID=;Password=")
Dim cmd1 As SqlCommand
Dim sr1 As New System.IO.StreamReader("C:\Can2.txt")
Dim strbir, strdort As Integer
Dim strInput() As String
 
Do While sr1.Peek <> -1
    strInput = sr1.ReadLine.Split(Char.Parse(";"))    
    cmd1 = New SqlCommand("INSERT INTO test (bir, dort) VALUES (" & strInput(0) & "," & strInput(1) & ")", sqlConnection1)
    sqlConnection1.Open()
    cmd1.ExecuteNonQuery()
    sqlConnection1.Close()
Loop
 
sr1.Close

The query assumes that bir and dort are both numeric fields as you are using Integer variables but modify accordingly if they are not.


HTH

 

[outcast1881]

thank you againit works!it is adding 2 empty rows as well to my datatable,I don't know why but I'll work on that now!

regards,

Can

 

[outcast1881]

not empty indeed their value is "0"...

 

[outcast1881]

Hi djjeavons,

I am trying to insert my data table from a textfile.
In my data table I have 4 fields and I am trying to fill the 3 fields with streamreader.The first field is the primary key,and I am filling it in the database before executing insert command.But I am getting an error message that says the value in the first field can be null ,it doesn't allow null(primary key) ,ERROR by Insert...

my insert command is:

md1 = New SqlCommand("INSERT INTO test1(material_number,description,PSP) VALUES (" & strInput(0) & ",'" & strInput(1) & "','" & strInput(2) _
& "')", sqlconnection1)

material_number:int description and psp are nvarchar...


I don't know what to do....I hope you have an idea...
Thank you for all for your help!

Can

 

[outcast1881]

hmmm,I am using update like but then it fills my datatable with the values of the last line only...

[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] sqlconnection1 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlConnection = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlConnection([/SIZE][SIZE=2][COLOR=#800000]"data source=.\SQLEXPRESS;database=RFID;User ID=catalay;Password=12301230"[/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] cmd1 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlCommand[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] sr1 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] System.IO.StreamReader([/SIZE][SIZE=2][COLOR=#800000]"c:\can.txt"[/COLOR][/SIZE][SIZE=2])[/SIZE]
 
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] strInput() [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Do[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]While[/COLOR][/SIZE][SIZE=2] sr1.Peek <> -1[/SIZE]
[SIZE=2]strInput = sr1.ReadLine.Split([/SIZE][SIZE=2][COLOR=#0000ff]Char[/COLOR][/SIZE][SIZE=2].Parse([/SIZE][SIZE=2][COLOR=#800000]","[/COLOR][/SIZE][SIZE=2]))[/SIZE]
[SIZE=2]cmd1 = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlCommand([/SIZE][SIZE=2][COLOR=#800000]"update test1 set material_number = "[/COLOR][/SIZE][SIZE=2] & strInput(0) & [/SIZE][SIZE=2][COLOR=#800000]", description = '"[/COLOR][/SIZE][SIZE=2] & strInput(1) & [/SIZE][SIZE=2][COLOR=#800000]"', PSP='"[/COLOR][/SIZE][SIZE=2] _[/SIZE]
[SIZE=2]& strInput(2) & [/SIZE][SIZE=2][COLOR=#800000]"' "[/COLOR][/SIZE][SIZE=2], sqlconnection1)[/SIZE]
[SIZE=2]sqlconnection1.Open()[/SIZE]
[SIZE=2]cmd1.ExecuteNonQuery()[/SIZE]
[SIZE=2]sqlconnection1.Close()[/SIZE]
[SIZE=2][COLOR=#0000ff]Loop[/COLOR][/SIZE]
[SIZE=2]sr1.Close()[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]

I am too confused but I hope I am on the right track

 

[djjeavons]

Hi

Your UPDATE command will update all records in your table with the values of the current line as you haven't supplied a WHERE clause to limit the number of records affected by the UPDATE statement. So when the code is processed, you will see that the table contains the values of the last line only.

Do you have anything in your file that uniquely identifies the record in the table that you want to update that can then be used as part of your WHERE clause? I would assume that this might be your material_number field.

As for the INSERT command, the SQL syntax looks alright, although (i may be wrong) but description might be a reserved word so you may want to enclose this field name in square brackets ([]) or change the field name in the table. Also, have you tried using an exception handler to determine what the error you are receiving may be:

Try
    'Your command declaration and execution
Catch ex As SqlException
    MessageBox.Show(ex.Message)
Catch ex As Exception
    MessageBox.Show(ex.Message)
End Try

HTH

 

[outcast1881]

Thank you again!
I did it this way:
cmd1 = New SqlCommand("update TAGINFO set material_number = " & strInput(0) & ", description = '" & strInput(1) & "', PSP='" & strInput(2) & "' where epc = (SELECT TOP 1 epc FROM TAGINFO WHERE material_number IS NULL ORDER BY epc ASC)", sqlconnection1)

But now I have another problem..I also have insert unique values from streamreader to a data table column.But how to specify DISTINCT in the code?

cmd2 = New SqlCommand("insert into STOCK(material_number,total) values (" & strInput(0) & ",0)", sqlconnection1)

strinput(0) is the value I am getting from streamreader and I only want to insert distinct values to my data table...

Thank you again!
Can

 

[djjeavons]

Hi

Do you mean that the CSV file you are reading from contains duplicated data and you only want to store unique values in the database?

If so, then one way you could do this is to store a list of values read from the CSV file and before adding the value to the list you can check for it's existence. Once you have done this, you can then iterate through the list and insert each value into your table knowing that they are unique.

[COLOR=#0000ff]Dim[/COLOR] sr [COLOR=#0000ff]As[/COLOR] [COLOR=#0000ff]New[/COLOR] StreamReader([COLOR=#800000]"YourFile"[/COLOR])
[COLOR=#0000ff]Dim[/COLOR] distinctValues [COLOR=#0000ff]As[/COLOR] [COLOR=#0000ff]New[/COLOR] List([COLOR=#0000ff]Of[/COLOR] [COLOR=#0000ff]String[/COLOR])
[COLOR=#0000ff]Dim[/COLOR] currentLineValues() [COLOR=#0000ff]As[/COLOR] [COLOR=#0000ff]String[/COLOR]
[COLOR=#0000ff] 
[/COLOR][COLOR=#0000ff]Do[/COLOR] [COLOR=#0000ff]While[/COLOR] sr.Peek <> -1
[COLOR=#008000][/COLOR] 
[COLOR=#008000]    'Retrieve the current line and split it
[/COLOR][COLOR=#008000]    [/COLOR]currentLineValues = sr.ReadLine.Split([COLOR=#0000ff]Char[/COLOR].Parse([COLOR=#800000]";"[/COLOR]))
[COLOR=#008000][/COLOR] 
[COLOR=#008000]    'Determine if the value at element 0 of currentLineValues array
[/COLOR][COLOR=#008000]    'exists in the distinctValues list.
[/COLOR][COLOR=#0000ff][COLOR=#008000]    [/COLOR]If[/COLOR] distinctValues.IndexOf(currentLineValues(0)) = -1 [COLOR=#0000ff]Then[/COLOR]
[COLOR=#0000ff][/COLOR] 
[COLOR=#008000]        'Value does not exist, add it
[/COLOR][COLOR=#008000]        [/COLOR]distinctValues.Add(currentLineValues(0))
 
[COLOR=#0000ff][COLOR=#008000]    [/COLOR]End[/COLOR] [COLOR=#0000ff]If[/COLOR]
[COLOR=#0000ff] 
[/COLOR][COLOR=#0000ff]Loop
[/COLOR] 
sr.Close()
[COLOR=#008000][/COLOR] 
[COLOR=#008000]'Loop through your distinctValues list adding each one to the database
[/COLOR][COLOR=#0000ff]For[/COLOR] [COLOR=#0000ff]Each[/COLOR] distinctValue [COLOR=#0000ff]As[/COLOR] [COLOR=#0000ff]String[/COLOR] [COLOR=#0000ff]In[/COLOR] distinctValues
[COLOR=#008000]    [/COLOR]cmd2 = [COLOR=#0000ff]New[/COLOR] SqlCommand([COLOR=#800000]"insert into STOCK(material_number,total) values ("[/COLOR] & distinctValue & [COLOR=#800000]",0)"[/COLOR], sqlconnection1)
[COLOR=#0000ff]Next[/COLOR]
[SIZE=1][COLOR=#0000ff]

[/COLOR][/SIZE]


To use the List object you will need to ensure you have imported the System.Collections.Generic namespace.


HTH