How to remove the empty rows in a DataTable using vb.net and one line of code.

Here’s a challenge for you. Using only one line of code, remove all the empty lines in a data table.

Empty is defined by a Null value or an empty string (/whitespace).

DataTableObject = DataTableObject.Rows.Cast(Of DataRow)().Where(Function(row) Not row.ItemArray.All(Function(field) TypeOf field Is System.DBNull OrElse String.Compare(TryCast(field, String).Trim(), String.Empty) = 0)).CopyToDataTable()

Advertisements

Code to copy content to clipboard

  Private _val As String
    Public Property Val() As String
        Get
            Return _val
        End Get
        Set(ByVal value As String)
            _val = value
        End Set
    End Property

    Protected Sub ClipboardBtn_Click(sender As Object, e As EventArgs)
        Val = sender.CommandArgument
        Dim staThread As Thread = New Thread(New ThreadStart(AddressOf myMethod))
        staThread.SetApartmentState(ApartmentState.STA)
        staThread.Start()
    End Sub

If this is working for Windows Forms Applications, you can use the following in IE to copy text:

holdtext.innerText = copytext.innerText;
Copied = holdtext.createTextRange();
Copied.execCommand("Copy");

How to obtain the subdomain of a domain name using VB.NET

How to obtain the subdomain of a domain name using VB.NET:

I thought that the System.Uri class contained that information in an easy retrievable way, but no.

Here’s what I came up with instead. It still uses the System.Uri to find the subdomain.

 Function GetSubdomain() As String
        Dim url As Uri = Request.Url
        Dim host As String = url.Host
        If host.Split(".").Length > 1 Then
            Dim index As Integer = host.IndexOf(".")
            Return host.Substring(0, index)
        End If
        Return ""
    End Function

How to insert data into an Excel File using VB.NET

excel-logo

Declaring the connection

If you are using Excel 2007 and above, create the file where you would like to save your data and add the following to your web.config

<appSettings>
<add value="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:File.xlsx;Extended Properties='Excel 12.0;ReadOnly=False;HDR=YES;'" key="ExcelLink" />
</appSettings>

Read from an Excel file

First, we initialize the connection to the Excel File:

'connection details
Dim _connectionString As String = System.Configuration.ConfigurationManager.AppSettings("ExcelLink")
'get last value of the id in excel file
Dim factory As DbProviderFactory = DbProviderFactories.GetFactory("System.Data.OleDb")
Dim strErrorsFound As New StringBuilder
Dim blnErrorFound As Boolean = False
Using connection As DbConnection = factory.CreateConnection()
connection.ConnectionString = _connectionString

Then, we create the command that will select information from the sheet:

Using command As DbCommand = connection.CreateCommand()
'Get the RefNo for the latest entry
command.CommandText = "SELECT TOP 1 ID FROM [Feedback$] ORDER BY ID DESC"

We will put our code in a Try-Catch block to prevent any errors bleeding into the Client-Side.

Try
connection.Open()

Using dr As DbDataReader = command.ExecuteReader()
If dr.HasRows Then

While dr.Read()
‘store last ref_number value
lastID = If(dr(“ID”).ToString() <> “”, Int32.Parse(dr(“ID”).ToString()), 0)
End While
End If
End Using

‘clean up- close first connection to the excel file
connection.Dispose()
connection.Close()

Catch ex As Exception
PanelServerError.Visible = True
Exit Sub
End Try

Don’t forget to close the Using Tags

End Using
End Using

Get data from SQL Server and save it into Excel

Dim myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("CN").ConnectionString)

Dim myCommand As New SqlCommand("[Your SP]", myConnection)
myCommand.CommandType = CommandType.StoredProcedure

Try

myConnection.Open()

Dim myReader As SqlDataReader = myCommand.ExecuteReader()

While myReader.Read()

IDNum = myReader.Item("ID")
contactName = myReader.Item("ContactName")
contactCompany = myReader.Item("ContactCompany")
contactPosition = myReader.Item("ContactPosition")
contactEmail = myReader.Item("ContactEmail")
project = myReader.Item("Project")

'insert into excel file
Dim factory2 As DbProviderFactory = DbProviderFactories.GetFactory("System.Data.OleDb")

Dim connection2 As DbConnection = factory2.CreateConnection()
connection2.ConnectionString = _connectionString

Dim command2 As DbCommand = connection2.CreateCommand()

'Build SQL string
Dim SQLQuery As New StringBuilder()
SQLQuery.Append("INSERT INTO [Feedback$] ")
SQLQuery.Append("(ID, Name, Company, Position_, Email, Project, ")

SQLQuery.Append("VALUES ('" & IDNum & "', '" & contactName & "','" & contactCompany & "','" & contactPosition & "','" & contactEmail & "','" & project & "','")

Try
connection2.Open()

'insert data into excel spreadsheet
command2.CommandText = SQLQuery.ToString()
command2.ExecuteNonQuery()

Catch ex As SystemException
blnErrorFound = True
strErrorsFound.AppendLine("Line / ID " + IDNum)
strErrorsFound.AppendLine(ex.Message)
strErrorsFound.AppendLine("Query: " + SQLQuery.ToString())
strErrorsFound.AppendLine("******************************************************************************")
Finally
connection2.Dispose()
connection2.Close()
End Try

'****************excel insert code *******************

End While

myReader.Close()

Catch ex As SystemException
blnErrorFound = True
strErrorsFound.AppendLine("Generic error: " + ex.Message)
strErrorsFound.AppendLine("******************************************************************************")

Finally

'clean up
myConnection.Close()
myConnection.Dispose()
End Try

Errors

Operation must use an updateable query.

Resolution

Granting access to the file being written to (IISUSR User or the IIS App Pool User) solved this issue for me.

The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.

Resolution

While importing from an Excel spread sheet you see the following error message “[Microsoft][ODBC Excel Driver] The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data”. This is problem with the Excel ODBC driver which can often be worked around by reordering the records in the spreadsheet..
Fundamentally, Excel is NOT a database and the columns in a spreadsheet do not have a data type associated with them in the same way that that a database does. The Excel ODBC driver has to make an assumption about the data type for a given column. The driver does this by reading ahead 8 records and looking at the data found. It then makes a decision about the data type based upon what it has read. Problems occur when the assumption about data type is proven wrong by subsequent records. Consider two scenarios….
A column in an Excel spreadsheet has a column in it which contains string data. The first 8 records contain short strings, (let’s say 20-30 characters). The Excel ODBC driver reads this data and assumes that a short string data type will be appropriate for this column. If a subsequent record contains a longer string, (let’s say 300 characters). The data type may prove inappropriate and unable to store the longer string and the error above is raised by the Excel ODBC driver. Moving the record with the long string to the beginning of the dataset will allow the Excel ODBC to select a more appropriate data type for the column which will apply to all records in the spreadsheet.
A column in a spreadsheet has numeric strings for the first 8 records, For example “123”, “456” etc. Excel decides that this column has numeric data. A subsequent record contains a string which is not numeric data, for example “Hello World”. The assumption made by the Excel ODBC driver will prove incorrect and the above error message will be raised by the ODBC driver. The problem can be worked around by reconsidering the order of the records. If the record containing “hello world” is placed within the first 8 records. The Excel ODBC driver will determine that this column contains string data and hopefully a string data type will be selected which will be appropriate for all data records.
In all cases the technique is to arrange the order of the records such that the Excel ODBC driver is allowed to make the correct selection of data type.

How to upload all files from a folder on Microsoft Azure Storage

If you are working with Microsoft Cloud, you must have had the need to move a few files (in bulk) from a folder onto the Azure Storage System.
To get started, you will need to have a Microsoft Azure Account, create a storage account, get the connection string for it.
If you are unsure about how to proceed, check out this great introductory article: How to use Blob Storage from .NET
mobile-blob-storage-account
This is a demo connection string that you will need to add to your web.config file:
<add key="StorageConnectionString" value="DefaultEndpointsProtocol=https;AccountName=srmd1983;AccountKey=JyUOu3/iv+0UMjzI/PtoHd2JKhKx4SOSSxJcsvVp95isAZH6hKpPs/AQDOPxgVXjTNGWCYCSssgiwVVun0rlWXFgJ6A==" />

In your code, import the needed Microsoft.WindowsAzure.Storage namespace. To add this DLL, install the Azure SDK package for your version of Visual Studio and add the DLL into references.

Imports Microsoft.WindowsAzure.Storage
Imports Microsoft.WindowsAzure.Storage.Auth
Imports Microsoft.WindowsAzure.Storage.Blob

Then you will need to create a method that will do all the dirty work. The files will be uploaded in a subdirectory for each folder (in this case, the subdirectory is generated by the ClientID value).

   1:  Imports Microsoft.WindowsAzure.Storage
   2:  Imports Microsoft.WindowsAzure.Storage.Auth
   3:  Imports Microsoft.WindowsAzure.Storage.Blob
   4:  Imports System.IO
   5:  Public Class Azure
   6:  Shared Function UploadAllFilesToBlob(ClientID As String) As String
   7:  Dim err As String = ""
   8:  Try
   9:  Dim storageAccount As CloudStorageAccount = CloudStorageAccount.Parse( _
  10:  ConfigurationManager.AppSettings("StorageConnectionString"))
  11:  Dim blobClient As CloudBlobClient = storageAccount.CreateCloudBlobClient()
  12:  ' // Retrieve a reference to a container. 
  13:  Dim container As CloudBlobContainer = blobClient.GetContainerReference("uploads")
  14:  '// Create the container if it doesn't already exist.
  15:  container.CreateIfNotExists()
  16:  Dim dir As CloudBlobDirectory
  17:  dir = container.GetDirectoryReference(ClientID)
  18:  '// Create or overwrite the "myblob" blob with contents from a local file.
  19:  Dim path As String = System.Configuration.ConfigurationManager.AppSettings("SavePath")
  20:  Dim di As New DirectoryInfo(path)
  21:  For Each fi As FileInfo In di.GetFiles()
  22:  Dim fileStream = System.IO.File.OpenRead(fi.FullName)
  23:  '      // Retrieve reference to a blob named the same as the uploaded file. If the file exists, it will be overwritten.
  24:  Dim blockBlob As CloudBlockBlob = dir.GetBlockBlobReference(fi.Name)
  25:  '//create or replace
  26:  fileStream.Position = 0
  27:  blockBlob.UploadFromStream(fileStream)
  28:  fileStream.Close()
  29:  fi.Delete()
  30:  Next
  31:  Catch ex As Exception
  32:  err = "Error: " + ex.Message + "<br />" + ex.StackTrace
  33:  End Try
  34:  Return err
  35:  End Function

Upload file to FTP Server via VBS Script

We always requirements to put files on a server without installing additional software on the source server.

An easy way to accomplish this is by using VBS (Visual Basic Scripts).

 

Using Chilkat FTP

One method is to use the Chilkat FTP Software (1 license costs about $289). You can install a 30-day trial version by clicking here.

 

Dim fso, outFile
Set fso = CreateObject("Scripting.FileSystemObject")
Set outFile = fso.CreateTextFile("output.txt", True)

' This CreateObject statement uses the new single-DLL ActiveX for v9.5.0
set ftp = CreateObject("Chilkat_9_5_0.Ftp2")

'  Any string unlocks the component for the 1st 30-days.
success = ftp.UnlockComponent("Anything for 30-day trial")
If (success <> 1) Then
    outFile.WriteLine(ftp.LastErrorText)
    WScript.Quit
End If

ftp.Hostname = "ftp.chilkatsoft.com"
ftp.Username = "****"
ftp.Password = "****"

'  The default data transfer mode is "Active" as opposed to "Passive".

'  Connect and login to the FTP server.
success = ftp.Connect()
If (success <> 1) Then
    outFile.WriteLine(ftp.LastErrorText)
    WScript.Quit
End If

'  Change to the remote directory where the file will be uploaded.
success = ftp.ChangeRemoteDir("junk")
If (success <> 1) Then
    outFile.WriteLine(ftp.LastErrorText)
    WScript.Quit
End If

'  Upload a file.
localFilename = "c:/temp/hamlet.xml"
remoteFilename = "hamlet.xml"

success = ftp.PutFile(localFilename,remoteFilename)
If (success <> 1) Then
    outFile.WriteLine(ftp.LastErrorText)
    WScript.Quit
End If

ftp.Disconnect

MsgBox "File Uploaded!"
outFile.Close

Free Version

The example VBS script below shows how to upload and save a local file to a remote webserver using Microsoft VBS scripting. The upload method in this example is HTTP PUT, meaning that the webserver needs to be WebDAV-enabled. Since file upload is typically password-protected, the script implements access with basic authentication.

'======================================================================
' https-upload.vbs 1.0  @2009 by Frank4dd http://www.frank4dd.com/howto
' This script demonstrates a file upload to a WebDAV enabled webserver,
' using https (and proxy settings from IE) with basic web authentication
'
' Original authors and code references:
' - "ASP - File upload with HTTP Put" by Martin Clark
'
' This program comes with ABSOLUTELY NO WARRANTY. You may redistribute
' copies of it under the terms of the GNU General Public License.
'======================================================================

'======================================================================
' Global Constants and Variables
'======================================================================
Const scriptVer  = "1.0"
Const UploadDest = "https://mywebdavserver.com/uploadurl"
Const UploadFile = "localpath-and-file"
Const UploadUser = "username"
Const UploadPass = "password"
Const UploadType = "binary"
dim strURL

function sendit()
  sData = getFileBytes(UploadFile, UploadType)
  sfileName= mid(UploadFile, InstrRev(UploadFile,"")+1,len(UploadFile))

  dim xmlhttp
  set xmlhttp=createobject("MSXML2.XMLHTTP.3.0")
  strURL = UploadDest & "/" & UploadFile
  msgbox "Upload-URL: " & strURL
  xmlhttp.Open "PUT", strURL, false, UploadUser, UploadPass
  xmlhttp.Send sData
  Wscript.Echo "Upload-Status: " & xmlhttp.statusText
  set xmlhttp=Nothing
End function

function showresult()
  Wscript.Echo "Complete. Check upload success at: " & strURL
end function

function getFileBytes(flnm, sType)
  Dim objStream
  Set objStream = CreateObject("ADODB.Stream")
  if sType="binary" then
    objStream.Type = 1 ' adTypeBinary
  else
    objStream.Type = 2 ' adTypeText
    objStream.Charset ="ascii"
  end if
  objStream.Open
  objStream.LoadFromFile flnm
  if sType="binary" then
    getFileBytes=objStream.Read 'read binary'
  else
    getFileBytes= objStream.ReadText 'read ascii'
  end if
  objStream.Close
  Set objStream = Nothing
end function

'=======================================================================
' End Function Defs, Start Main
'=======================================================================
' Get cmdline params and initialize variables
If Wscript.Arguments.Named.Exists("h") Then
  Wscript.Echo "Usage: https-upload.vbs"
  Wscript.Echo "version " & scriptVer
  WScript.Quit(intOK)
End If

sendit()
showresult()
Wscript.Quit(intOK)
'=======================================================================
' End Main
'=======================================================================

How to Create an Excel Chart in VB.NET Picture Box

The following program shows how to get the Excel chart as an image in Picture Box. Before we crate a Chart, we have to fill data in Excel sheet. After entering data your Excel sheet is look like the following picture.

excel-create-chart

After filling in the data, we have to create a chart object in VB.NET and configure the Chart object with necessary data like positions, size, data range, chart type etc.. and use the command for export chart as picture file, then load the picture from the saved path and into the picture box.

Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, _
  ByVal e As System.EventArgs) Handles Button1.Click

        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value

        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")

        'add data
        xlWorkSheet.Cells(1, 1) = ""
        xlWorkSheet.Cells(1, 2) = "Student1"
        xlWorkSheet.Cells(1, 3) = "Student2"
        xlWorkSheet.Cells(1, 4) = "Student3"

        xlWorkSheet.Cells(2, 1) = "Term1"
        xlWorkSheet.Cells(2, 2) = "80"
        xlWorkSheet.Cells(2, 3) = "65"
        xlWorkSheet.Cells(2, 4) = "45"

        xlWorkSheet.Cells(3, 1) = "Term2"
        xlWorkSheet.Cells(3, 2) = "78"
        xlWorkSheet.Cells(3, 3) = "72"
        xlWorkSheet.Cells(3, 4) = "60"

        xlWorkSheet.Cells(4, 1) = "Term3"
        xlWorkSheet.Cells(4, 2) = "82"
        xlWorkSheet.Cells(4, 3) = "80"
        xlWorkSheet.Cells(4, 4) = "65"

        xlWorkSheet.Cells(5, 1) = "Term4"
        xlWorkSheet.Cells(5, 2) = "75"
        xlWorkSheet.Cells(5, 3) = "82"
        xlWorkSheet.Cells(5, 4) = "68"

        'create chart
        Dim chartPage As Excel.Chart
        Dim xlCharts As Excel.ChartObjects
        Dim myChart As Excel.ChartObject
        Dim chartRange As Excel.Range

        xlCharts = xlWorkSheet.ChartObjects
        myChart = xlCharts.Add(10, 80, 300, 250)
        chartPage = myChart.Chart
        chartRange = xlWorkSheet.Range("A1", "d5")
        chartPage.SetSourceData(Source:=chartRange)
        chartPage.ChartType = Excel.XlChartType.xlColumnClustered

        'exporting chart as picture file
        xlWorkSheet.ChartObjects(1).chart.Export(FileName:= _
        "C:excel_chart_export.bmp", FilterName:="BMP")

        'load the pipcture into the picture box
        PictureBox1.Image = New System.Drawing.Bitmap _
			("C:excel_chart_export.bmp")

        xlWorkSheet.SaveAs("C:vbexcel.xlsx")
        xlWorkBook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

        MsgBox("Chart File Exported !")
    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Class

When you execute this program you will get the picture in Picture Box from Excel Chart .