VB.Net help - Various Issues

VB.Net help - Various Issues

• mbaciak

  Ahhh...I see. I am using 2003. I have the full Studio 2003 Suite. I have not installed Express yet because I have not had time to look at what the differences were.
  

• Sanjay Patel

  spotty wrote:

  At this point are you reading from the CSV File or the Excel worksheet.

  It is just a csv file.

  Once you have this string you can then use the My.Computer.Filesystem.WriteAllText method to write this string to a CSV File

  Whenever I try to use My.Anything it says "Name "My" is not declared, and it has the wonderful blue squiggly under My. Am I supposed to import something or add a reference to be able to use this

  
  

• NanaKV

  Well, that worked about halfway. It had an error because it did not recognize my previous DataGrid1 from v.2003 so I had to add a DataGridView. This actually worked better because it did not display NULL in all teh empty fields...BUT.....(and there is always a BUT lol)

  I got this error that said "Conversion from type 'DBNull' to type String is not valid. I assume that is because the empty fields are imported as empty strings so it automatically converts them to Null. So obviously I need some kind of check to see if the field is empty and if so to do something to it so that the program reads it as a string. Is there a way to do that programaticallyor will I need it to add a string character to all empty fields (something I would like to avoid) suing something like:

  IF field is null Then field = "0"

  What is your opinion

  
  

• Lajos Malozsak

  As to why it is returning this error 'NullReferenceException was unhandled; Object reference not set to an instance of an object.". You are passing in a null/othing into the function - that is its not a dbnull but a real null. This causes it to try and do a tostring on a null and hence you get the exception.

  I would check the value of what you are passing to the function. When it breaks look at the value of the dbvalue in the FixNull function. I've shown a modified the function at the bottom here - which you should stick a breakpoint on the return "" if it is nothing. It should never get here but if it does you want to see why and look at the row and column that it is processing at the time.

  This should take at maximum seconds to process even large datagridviews. Start by code stepping the functionality for one or two rows in the datagridview, maybe even populate it with only a very small 2 or 3 row CSV to ensure that the process is good before throwing a large file at it.

  
  As far as the second issue you need to look at what parameters the my.computer.filesystem.writealltext is looking for.

  http://msdn2.microsoft.com/en-us/library/27t17sxs(VS.80).aspx

  It details that the parameters as the following

  file -String. File to be written to. Required.
  text - String. Text to be written to file. Required.
  append -Boolean. Whether to append text or overwrite existing text. Default is False. Required.
  encoding -Encoding. What encoding to use when writing to file. Required. Default is UTF-8.
  Now as you can see some of encoding is optional but the others are required.

  
  So you need to pass a string representing the filename, a string representing the text and a boolean with a true/false value as to whether you want to append the text to the file or overwrite the existing contents.

  The stuff with

  Dim fi As New FileInfo(saveFileDialog.FileName)

  is not needed and passing fi.directoryname is passing a directoryname as a string but this is not the filename you are trying to write a file called the same as the directory name which is going to cause problems, as it cant write a file that is the same as the directory name. This is what this message is telling you. You simply need to tell it the file you want to write - you can pass a complete path such as "C:\test.csv" in which case it will write this file to this path or just the filename "test.csv" in which case it will write the file to the appliction directory.

  Either way its simply a string containing the name of the file you want to write - nothing more.

  Dim outputstring As String = ""
  Dim slastcolumn As String = Me.DataGridView1.Columns(Me.DataGridView1.Columns.Count - 1).Name

  For Each r As DataGridViewRow In Me.DataGridView1.Rows
  For Each c As DataGridViewColumn In Me.DataGridView1.Columns
  Dim s As String = FixNull(r.Cells(c.Name).Value)
  outputstring = outputstring & s
  If c.Name <> slastcolumn Then
  outputstring = outputstring & ","
  End If
  Next
  outputstring = outputstring & vbCrLf
  Next

  My.Computer.FileSystem.WriteAllText("Recs.csv", outputstring , False)

  Public Function FixNull(ByVal dbvalue as Object) As String
  If dbvalue Is DBNull.Value Then
  Return ""
  Else

  if dbvalue is nothing then
  Return ""
  else
  Return dbvalue.ToString
  end if
  End If
  End Function

  
  

• James Agnew

  Your somewhat missing the point here....

  The idea of the function is that you are passing in the value of the cell and will always get a value back - that is it will return a blank string if the value of the cell is dbnull

  So the code should look something like the following.

  Youve probably got option strict on which is why you are getting the green squiggly with the lines ... (ByVal dbvalue) and you'll either have to turn option strict off or add as object onto these lines so they say ... (ByVal dbvalue as object)

  and the other error message is because in your calling code you are setting dbvalue as string and never assigning it to anything before you call a function using it. As you do not need to even define a variable dbvalue in the calling code - as your code should be passing the cell value as the parameter to the function - this should not be present with the following code.

  
  Dim outputstring As String = ""
  Dim slastcolumn As String = Me.DataGridView1.Columns(Me.DataGridView1.Columns.Count - 1).Name

  
  For Each r As DataGridViewRow In Me.DataGridView1.Rows
  For Each c As DataGridViewColumn In Me.DataGridView1.Columns

  Dim s As String
  s = FixNull(r.Cells(c.Name).Value)

  outputstring = outputstring & s

  If c.Name <> slastcolumn Then
  outputstring = outputstring & ","
  End If

  Next

  outputstring = outputstring & vbCrLf
  Next
  

  
  

• Cari Begle

  Which version of VB.Net are you using

  2005 / Express/ 2003

  My should be supported in 2005 or Express but not in earlier versions.

  
  

• Morpheus72

  OK from what I can see in you code to save...

  You are doing a

  Dim X As String

  My.Computer.FileSystem.WriteAllText("Recs.csv", X, False)

  But nowhere between these two statement is x being set to a value.... Your still going to have to turn x into a string representing the data in the grid.

  Whether thats by iterating through the cells and setting X or another means thats what is going on. You are probably simply writing a null into the file and no data. The squiggly is telling you you are using the variable before you have set it to anything.

  For the datagridview in vb express. You can write a simple iterative process to process around them something like the following.

  Dim outputstring As String = ""
  Dim slastcolumn As String = Me.DataGridView1.Columns(Me.DataGridView1.Columns.Count - 1).Name

  
  For Each r As DataGridViewRow In Me.DataGridView1.Rows
  For Each c As DataGridViewColumn In Me.DataGridView1.Columns
  Dim s As String = r.Cells(c.Name).Value
  outputstring = outputstring & s
  If c.Name <> slastcolumn Then
  outputstring = outputstring & ","
  End If
  Next
  outputstring = outputstring & vbCrLf
  Next
  

  This should give you a string representing the contents of the CSV file. Which is what you are going to write.

  
  

• uXuf

  I am HORRIBLE when it comes to functions. I do not know why I have such a hard time with them.

  When I execute the program and click the save button, it freezes for a long time. At least 10+ minutes because thats when I left to eat. When I came back it had broken on the line 'Return dbvalue.ToString in the FixNull function. It says 'NullReferenceException was unhandled; Object reference not set to an instance of an object." So I remove ToString from 'Return dbvalue.ToString and everything seems to work excellent except for two problems.

  1. It takes forever for it actually save the file.

  2. Even though the save dialog opens and I gave it a unique name, it still saved the file as "Recs.csv" obviously because o fthe line

  My.Computer.FileSystem.WriteAllText("Recs.csv", outputstring, False)

  So I borrowed something from my import procedure and changed it to work with the save procedure. I added this line

  Dim fi As New FileInfo(saveFileDialog.FileName)

  and changed "Recs.csv" to be fi.DirectoryName so it now reads

  My.Computer.FileSystem.WriteAllText(fi.DirectoryName, outputstring, False)

  but when I execute the save procedure it breaks on my line

  Dim fi As New FileInfo(saveFileDialog.FileName)

  and says the path is not of a legal form.

  So my (hopefully last and thanks for ALL you help!) two questions are:

  1. Why is it taking so long to save (I assume it is because it is having to read each cell individually and the file has LOTS of cells).

  2. How is the line 'Dim fi As New FileInfo(saveFileDialog.FileName)' not of legal form if it works fine for storing the name of the file I tell it to import

  
  

• Vincent Zhao

  "1. The worksheet is 82 columns wide. I need to be able to only import specific columns into the datagrid."

  At this point are you reading from the CSV File or the Excel worksheet.

  If your reading from the CSV File and want to only deal with certain fields you will need to parse the file. Luckily VB.Net provides a nice way to deal with delimited files like CSV. http://msdn2.microsoft.com/en-us/library/4cwxw7dx.aspx

  So what you would do is parse each line on the CSV (this will break it down into its appropriate fields. Then build another string with just the fields you want.

  Then use this for however you are populating the datagrid.

  2. Taking you data in the datagrid and generating a CSV file.

  This is going to involve you iterating over each row in the datagrid and constructing a CSV string. The general pseudo code would be

  For Each Row in Datagrid.rows

  For Each Column in Row

  A column value to string

  if not the last column in the row then

  add a ',' character to the string

  end if

  Next

  Add a CR to the string

  Next

  Once you have this string you can then use the My.Computer.Filesystem.WriteAllText method to write this string to a CSV File

  
  

• tonyGasparich

  You are right, VBExpress does seem to be much easier to code BUT.....

  I am still haveing an issue. Here is my code to save the file.

  Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

  Dim saveFileDialog As New SaveFileDialog

  Dim X As String

  SaveFileDialog1.Filter = "CSV|*.csv"

  SaveFileDialog1.AddExtension = True

  If SaveFileDialog1.ShowDialog(Me) = Windows.Forms.DialogResult.OK Then

  My.Computer.FileSystem.WriteAllText("Recs.csv", X, False)

  End If

  End Sub

  Now, my open file code works perfectly and displays all the data from the csv file I am importing in the datagrid. When I try to save, the "save in" dialog box open and asks me for the location and filename just as it should. When I go to the directory where I saved the file and open it though, instead of what was in the datagrid, I get only one field with an upside and a few other characters.

  My code now has the squiggly under X in the My.Computer.FileSystem.WriteText(Recs.csv", X, False) line and says "variable X is being used before it is assigned a value. So what I suppose I need to do is somehow assign the data that currently resides in the datagrid/dataset to X but I cannot figure out how. As a side note, the file is saved as Recs.csv, not as teh naem I gave it in teh save file dialog box.

  The data is loaded INTO the dataset using this method: (which is working perfectly)

  Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoad.Click

  If OpenFileDialog1.ShowDialog(Me) = Windows.Forms.DialogResult.OK Then

  Dim fi As New FileInfo(OpenFileDialog1.FileName)

  Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Text;Data Source=" & fi.DirectoryName

  Dim objConn As New OleDbConnection(sConnectionString)

  objConn.Open()

  Dim objCmdSelect As New OleDbCommand("SELECT * FROM " & fi.Name, objConn)

  Dim objAdapter1 As New OleDbDataAdapter

  objAdapter1.SelectCommand = objCmdSelect

  Dim objDataset1 As New DataSet

  objAdapter1.Fill(objDataset1, "Reconciles")

  DataGrid1.DataSource = objDataset1.Tables(0).DefaultView

  objConn.Close()

  End If

  Soooo....any idea how I do that

  
  

• Unknown Name

  Thanks for all your help but even when I get it to work right, I still have many errors. Maybe I am going about this the hard way. I have decided to change my program and use actual tables instead of reading the csv as that seems to give me more flexability.

  Using Visual Web Developer 2005 and VB, I have created a web form page with a datagrid that shows the data which is stored in an SQL database Table. Everything shows perfectly but it requires the data to already be in the database. Right now, I am exporting the data as a csv file, clearing the data out of the table in the database, and manually importing the csv file. What I would like is to have an Upload procedure that will automatically clear the table and input the new data into that same table without any extra measures by the user.
  
  I created a FileUpload procedure (FileUpload1) which allows the user to browse to the file they want. I have set up a string that holds the location of that file (fi as string). I also have a Import button (btnImport) which is where I need to write the code which will take the csv file in the above location (fi) and save it to the database table I have set up. I basically want to be able to skip having to go to the SQL Server Manager and import the csv into the table whenever they need it. I also want there to be no interaction for this part by the user other than choosing the file to import.

  Any thought's.

  
  

• Drew Wildner

  No problems....

  I would write a couple of simple function which would convert the dbnull to blank strings and be used to determine if the value is a dbnull and use these.

  Public Function IsDBNull(ByVal dbvalue) As Boolean
  Return dbvalue Is DBNull.Value
  End Function

  Public Function FixNull(ByVal dbvalue) As String
  If dbvalue Is DBNull.Value Then
  Return ""
  Else
  'NOTE: This will cast value to string if
  'it isn't a string.

  Return dbvalue.ToString
  End If
  End Function

  
  

• PAS30339

  Well now I am lost. With the two functions I should be able to call them using:

  IsDBNull(dbvalue)

  FixNull(dbvalue)

  But I obviously have to also declare them in the save sub. Since you can only declare dbvalue once, yet in one return it is a boolean value and the other it is a string, how can I declare it

  In the two functions the dbvalue in the line ...(ByVal dbvalue)... is green squiggled and the error says:

  Warning 5 Variable declaration without an 'As' clause; type of Object assumed.

  When I try to use the function FixNull it says:

  Warning 4 Variable 'dbvalue' is used before it has been assigned a value. A null reference exception could result at runtime.

  So obviously I still am not getting the problem fixed because the null error is what is breaking my program. I am at a loss. When I run the program it is actually breaking on the line FixNull(dbvalue) with the error "Object reference not set to an instance of an object." Her is my code for the save sub and the two functions.

  Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

  Dim saveFileDialog As New SaveFileDialog

  Dim X As String

  Dim outputstring As String = ""

  Dim slastcolumn As String = Me.DataGridView1.Columns(Me.DataGridView1.Columns.Count - 1).Name

  Dim dbvalue As String

  For Each r As DataGridViewRow In Me.DataGridView1.Rows

  For Each c As DataGridViewColumn In Me.DataGridView1.Columns

  Dim s As String = r.Cells(c.Name).Value

  IsDBNull(dbvalue)

  FixNull(dbvalue.ToString)

  outputstring = outputstring & s

  If c.Name <> slastcolumn Then

  outputstring = outputstring & ","

  End If

  Next

  outputstring = outputstring & vbCrLf

  Next

  SaveFileDialog1.Filter = "CSV|*.csv"

  SaveFileDialog1.AddExtension = True

  If SaveFileDialog1.ShowDialog(Me) = Windows.Forms.DialogResult.OK Then

  My.Computer.FileSystem.WriteAllText("Recs.csv", X, False)

  End If

  End Sub

  Public Function IsDBNull(ByVal dbvalue) As Boolean

  Return dbvalue Is DBNull.Value

  End Function

  Public Function FixNull(ByVal dbvalue) As String

  If dbvalue Is DBNull.Value Then

  Return ""

  Else

  'NOTE: This will cast value to string if

  'it isn't a string.

  Return dbvalue.ToString

  End If

  End Function

  
  

• mirren_st

  I'd definately give it a try as it makes a lot of functions such as reading and writing files much more simple and intuitive rather than having to deal with streamreaders and writers to access files.

  For a 2003 to 2005 I think you'll find that the experience is better as a lot of things have been improved and simplified in many respects.

  The following provides some nice examples at file functions in VB 2003

  http://www.builderau.com.au/program/windows/0,39024644,20267367,00.htm

  http://www.startvbdotnet.com/files/default.aspx

  
  
• VB.Net help - Various Issues