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

 

[outcast1881]

hi again,
indeed I have 2 data tables ,one can contain multiple values(material_number) and for the other can't contain multiple values(material_number)...So i think I can use the code and fill the first table with streamreader containing multiple values close the streamreader,and fill the other one with another streamreader excluding multiple values...

Again thank you very much for your help!!!!

CAn

 

[outcast1881]

Hi djjeavons,
I am getting an error during coding the sqlcommand (cmd2)

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

The error message is something like "the value from type system.data.sqlclient.sqlcommand can not be converted to one dimensional array by system.data.sqlclient.sqlcommand"....
my code is:


sr.Close()
sqlconnection1.Open()
ForEach distinctValue AsStringIn distinctValues
cmd2 = New SqlCommand("insert into STOCK(material_number,total) values (" & distinctValue & ",0)", sqlconnection1)
cmd2.executenonquery()
Next
sqlconnection1.Close()

I am confused again...
regards,


CAn

 

[djjeavons]

Hi

How have you declared your cmd2 variable? Make sure you haven't declared it like:

Dim cmd2() As SqlCommand

HTH

 

[outcast1881]

Hi,

I declared it like:
Dim cmd2 As SqlCommand...

I don't know what wrong is.I am thinking of creating another table fill it with double values by streamreader than using insert and select distinct commands to fill my original data table with unique values from that table....

and also I guess it is possible to read excel files with streamreader but is it also possible to choose the columns I want to insert into my datatable,there are examples in internet but importing the whole excel sheet...

Again thank you for everything!!!


Can

 

[djjeavons]

Hi

I don't believe you can read an Excel file using a StreamReader (I could be proved wrong but as far as I know, this is binary data and therefore, using a StreamReader would be like viewing the file in Notepad).

However, I would recommend that you using ADO.NET and the OleDb objects to read the Excel file, this way you can perform standard SQL queries against it, therefore being able to select the columns you want.


HTH

 

[outcast1881]

Hi djjeavons,
thank you again for the information.I think I know how to do it(OLEDB),before using streamreader I was also reading data from text files this way...

And with streamreader can I also choose from which row to which row(like read from 12 to 500) to read and trim the read data...
like for instance I am reading 11110001,thisisnotanexit ,122222

I want to trim the space after the second column..is it possible with streamreader??sorry I am asking to much I know but my programming background is 65 days

Thank you again!!!!

 

[djjeavons]

Hi

thank you again for the information.I think I know how to do it(OLEDB),before using streamreader I was also reading data from text files this way...

There are lots of resources available for detailing how to read an Excel spreadsheet using ADO.NET. The following is a link to a post I helped somebody with that shows how to open a spreadsheet and retrieve all of the data from a specific worksheet. It should only need some slight modification (specifically the SELECT statement) to retrieve only the columns you are interested in.
http://www.vbcity.com/forums/topic.asp?tid=102983&#RID326693

And with streamreader can I also choose from which row to which row(like read from 12 to 500) to read and trim the read data...
like for instance I am reading 11110001,thisisnotanexit ,122222

To read specific lines from a text file you would need to incorporate some logic so that you can keep a counter and determine when you want to read a specific line. For example:

Dim lineCounter As Int32 = 0
Dim sr As New StreamReader("YourFile.txt")
Dim lineValues() As String
 
Do While sr.Peek <> -1
 
    lineCounter += 1
 
    If lineCounter >=12 AndAlso lineCounter <= 500 Then
        lineValues = sr.ReadLine.Split(Char.Parse(";"))
    Else
        sr.ReadLine  'Read the line but don't use it
    End If
 
    'Extra logic here to exist the loop if you have reached 500 lines
    If lineCounter > 500 Then Exit Do
 
Loop

As for trimming strings you can simply use the Trim method which will trim all whitespace from the beginning and end of the string.


HTH

 

[outcast1881]

Hi again,
thank you for the valuable info,I tried reading this file and filling the my sql data table...

my txt file was like that(more than 100 rows,there is no space before the first "|"):
|100011 |london |Paris|
|100012 |berlin |Tokio|

my code was:
Dim lineCounter As Int32 = 0
Dim sr As New System.IO.StreamReader("c:\Materials.txt")Dim sqlconnection1 As SqlConnection = New SqlConnection("data source=.\SQLEXPRESS;database=RFID;User ID=can;Password=123123")
Dim lineValues() As String
Dim cmd1 As SqlCommand
Do While sr.Peek <> -1
lineCounter += 1
If lineCounter >= 10 AndAlso lineCounter <= 100 Then
lineValues = sr.ReadLine.Split(Char.Parse("|"))
cmd1 = New SqlCommand("insert into test1(material_number) values " & lineValues(1) & " ", sqlconnection1)
sqlconnection1.Open()
Try
cmd1.ExecuteNonQuery()
Catch ex As SqlException
MessageBox.Show(ex.Message)
sqlconnection1.Close()
End Try
Else
sr.ReadLine()
End If
If lineCounter > 100 Then Exit Do
Loop
sr.Close()

I got this ex.Message with insert command:"wrong syntax near 100011"
it continued untill the last record...like "wrong syntax near 100012" ...so on...I think it is because of the spaces in the text file...I didn't know how to use trim...

Then I tried update command instead of insert(i also removed try end try block it was giving invalid operation exception in open.sqlconnection1() line ,"the connection is not closed,the actual status of the sql connection is open"...anyway my command was:

cmd1 = New SqlCommand("update test1 set material_number = " & lineValues(1) & " where epc = (SELECT TOP 1 epc FROM test1 WHERE material_number IS NULL)", sqlconnection1)


It worked but I had only 5 epc values in my data table(EPC column is the primary key) and it filled the first and second rows with the same value "100011" and the other three rows were filled 100012,100013,100014...I don't know why...
then I filled 10 EPC rows and tried update command again then it worked....I am happy and thankful to you that it works but why with 10 rows it works and with 5 rows I got multiple vales...

Sorry,I am still asking too much and it is 03:25 at night but I couldn't find it out...

Thanks and regards,

Can

 

[djjeavons]

Hi

Your INSERT statement is a little off which may well explain the syntax error. It should read:

cmd1 = New SqlCommand("insert into test1(material_number) values (" & lineValues(1) & ")", sqlconnection1)

Notice the additional parenthesis after the Values keyword and after the value you are inserting.

As for trimming the string value you can change the statement too:

cmd1 = New SqlCommand("insert into test1(material_number) values (" & lineValues(1).Trim & ")", sqlconnection1)

All string values have methods for manipulating strings such as the Trim method. Regardless of whether the string is a single string or an array of strings, each element in the array is still a string and therefore has the same methods.

With regards to your UPDATE statement, I would assume that the reason why only so many rows were updated when you ran it five times is that you only had a couple of rows that had a material_number of Null. Unless you have a specific reason to only update records that do not have a material_number then I would suggest that you look at modifying the WHERE clause so that it filters the data to be updated to something more unique.


HTH

 

[outcast1881]

Thank you a million,everything works!!!!You are a good teacher by the wayI am writing my graduate thesis about RFID technology now I have to move on to more difficult stage like getting data from the serial port(rfid device).

Again thank you very much for your help and support!

Best regards,


Can

 

[outcast1881]

Hi again,
now I get syntax error from streamreader with such words 'London' I mean the words in Apostrophes I get syntax error with these words...Do you have an idea how I can solve it?

Thanks again,

Can

 

[djjeavons]

Hi

Do you mean that there is a syntax error with your SQL statements when you are faced with words that contain apostraphe's. If so, then this is a common issue and is caused by the fact that string values are enclosed within apostraphe's, and if the value itself also contains an apostraphe then the string is not well formed. For example, consider the name O'Connell in an INSERT SQL statement:

INSERT INTO Table (Surname) Values ('O'Connell)

Note that the Connell part is outside of the apostraphe and therefore will cause a syntax error. The fix is to escapse the apostraphe by doubling it up so that the string looks like:

INSERT INTO Table (Surname) Values ('O''Connell')

Which when inserted into the database will retain the O'Connell format.

So to apply this to your code you can make use the of the Strings Replace method and have this replace all single apostraphe's with two apostraphe's:

Dim surname As String = "O'Connell"
Dim insertSQL As String = "INSERT INTO Table (Surname) Values ('" & surname.Replace("'", "''") & "')"

HTH

 

[outcast1881]

Hi,
The problem is streamreader is giving syntax error...I don't know if it is also related with my sql command...Before your reply I thought it was only related eith streamreader....The problem is there are many words in my text file like 'london' 'paris' etc...so the text file has to be changed I think...Otherwise I can't define every word like you mentioned above,too many words...

Anyway again thank you for your help!!!


regards,

Can

 

[djjeavons]

Hi

I wouldn't have thought that the StreamReader would throw an exception simply because it came to a word that was enclosed within apostraphe's. Have you stepped through your code and pinpointed the exact line that is causing the error? If you could then post the code and highlight the offending line, it may be eaiser to help.

The problem is there are many words in my text file like 'london' 'paris' etc...so the text file has to be changed I think...Otherwise I can't define every word like you mentioned above,too many words...

Not totally sure what you mean here but it seems that you may have misunderstood my post. The Replace method is used to find an occurrence of a series of characters and replace it with a different series of characters. This means that you are only using the replace method on the value of the string you are using and not on the file itself. You should not need to change your file.


HTH

 

[outcast1881]

hi again,
You are right actually.The error comes from cmd.executenonquery()
And you are also right about misunderstanding you
I tried this:

cmd1 = New SqlCommand("insert into taginfo(material_number,description) values (" & lineValues(1) & ",'" & lineValues(2).Replace("'", "''") & "')", sqlconnection1)


And it works!I have many lines in the text file so it takes a bit long but it works.
Again thank you for your help!

Can