All posts by hs

Generate Google Site Map in VB.NET

Google Site Maps are used by Google to index your website. You can create a site map of your website and submit to Google Site Maps for telling Google about all your web pages. Here is the URL for Google Site Maps https://www.google.com/webmasters/tools

I use a simple technique to create the site maps of all my site.

First of create a new .ASPX page in Visual Studio and name it GoogleMap.aspx

Now go to the code file for this and add the following code. ( You would need to tweak the information for your own website)

Imports System.IO
Imports System.Data.SqlClient
Imports System.Xml
Imports System.Text
Partial Class GoogleMap
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
‘Put user code to initialize the page here
Dim cn As New SqlConnection
Dim cl As New clsMain
cl.OpenConnection(cn) ‘ This is my function to open a connection ( use your own code )
Response.Clear()
Response.ContentType = “text/xml”
Dim objX As New XmlTextWriter(Response.OutputStream, Encoding.UTF8)
objX.WriteStartDocument()
objX.WriteStartElement(“urlset”)
objX.WriteAttributeString(“xmlns”, “http://www.google.com/schemas/sitemap/0.84″)

‘Handles database connection string and command

‘ Index Page

objX.WriteStartElement(“url”)
objX.WriteElementString(“loc”, “http://test.com”)
objX.WriteElementString(“lastmod”, Format(Now, “yyyy-MM-dd”))
objX.WriteElementString(“priority”, “1.0”)
objX.WriteEndElement()

‘ New Articles Page

objX.WriteStartElement(“url”)
‘ ‘ objX.WriteElementString(“loc”, objReader(“Name”))
objX.WriteElementString(“loc”, “http://test.com/newarticles.aspx”)
objX.WriteElementString(“lastmod”, Format(Now, “yyyy-MM-dd”))
objX.WriteElementString(“priority”, “1.0”)
objX.WriteEndElement()

‘Now generate the url for each articles. The Articles are stored in tblArticles and each Article has a Article ID
Dim strSQL As String

strSQL = “Select ArticleID , ArticleDate from tblArticles”

Dim objCommand = New SqlCommand(strSQL, cn)
Dim objReader As SqlDataReader
objReader = objCommand.ExecuteReader()

‘Populate record for the RSS feed
While objReader.Read()
objX.WriteStartElement(“url”)
objX.WriteElementString(“loc”, “http://vbnet.redirect.com.au/ViewArticle?ArticleID= objReader(“ArticleID”) & “.aspx” ‘ For each Article ID a new page name is created
objX.WriteElementString(“lastmod”, Format(Now(), “yyyy-MM-dd”))
objX.WriteElementString(“priority”, “1.0”)
objX.WriteEndElement()
End While

objReader.Close()

cn.Close()

‘ objX.WriteEndElement()
objX.WriteEndElement()
objX.WriteEndDocument()
objX.Flush()
objX.Close()
Response.End()

End Sub
End Class

If you browse the page then you would see a XML output of all your pages. This page can be submitted to Google Site Maps and Google will index all your submitted pages.

This is a dynamic way of creating the site map and it will ensure that your site map is always updated without any intervention. When you create a new Article then the Google Site Map is always updated.

Now one more thing at the end . You can use the Cache in your page so that each time your Site Map is accessed your page does not need to open a connection and create a list of all your pages on the fly.

In the Google.aspx page add a line

<%@ OutputCache Duration="60000" VaryByParam="none" %>

as shown below in the following example

——————————————————

<%@ OutputCache Duration="60000" VaryByParam="none" %>

<%@ Page Language=”VB” AutoEventWireup=”false” CodeFile=”GoogleMap.aspx.vb” Inherits=”GoogleMap” %>

 

 

How to connect to a SQL Server in VB.NET

Connection to a SQL Server database canbe achieved in vb.net quite easily.
The Windows Forms based applications as well as ASP.NET based applications can connect to the SQL Server database in a similar way. But the way the ConnectionStrings are stored is slightly different.
1. Windows Forms Applications – How to make a connection in a Windows Forms based application
I normally use a module in which I have the following two functions – one for opening a connection and one for closing the connection.
Step 1:
At the top of the module write the following line

Imports System.Data.SqlClient

Step 2

Also declare a public string in which you can store the connection string as follows
( well there are different other ways to store the connection string but I will not discuss them here)

Public gsConnString As String = "Data Source=100.200.300.400;Initial Catalog=testdatabase;
User Id=testuser;Password=testpassword;"

Step 3

Function OpenConnection(ByVal cn As SqlConnection) As Boolean
Try
If cn.State <> ConnectionState.Closed Then
cn.Close()
End If
cn.ConnectionString = gsConnString
cn.Open()
Return True ' If the connection is made then it returns true
Catch
MsgBox("Unable to Connect to the database" & Err.Description, vbCritical)
Return False ' If the connection is not made then it returns false
End Try

End Function

Function CloseConnection(ByVal cn As SqlConnection) As Boolean

Try
If cn.State <> ConnectionState.Closed Then
cn.Dispose()
cn.Close()
End If

Return True
Catch
End Try

End Function

Now that you have written the functions and the connection string for your SQL Server . Now you are ready to open the connection in your form or module
Step 1
write the following line at the top of the form or the module

Imports System.Data.SqlClient

Step 2
You can use the openconnection and the close connection in your programs the following way.
This is a useful generic function which you can use to fill and return a datatable. You simply pass the sql statement to it and then you can get a datatable with the results
Once you get the datatable you can use it eg. use it in a datagrid

Function GetDataTable(ByVal sql As String) As DataTable
Dim dt As New DataTable
Dim dc As New SqlCommand
Dim cn As New SqlConnection
dc.CommandTimeout = 0
Dim da As New SqlDataAdapter
OpenConnection(cn)
da.SelectCommand = dc
dc.Connection = cn
dc.CommandText = sql
Try
da.Fill(dt)
Return dt
Catch
MsgBox("Error encountered, please try again:(" & sql & ")" & Err.Description, MsgBoxStyle.Critical)
Finally
dt.Dispose()
dc.Dispose()
CloseConnection(cn)
End Try

End Function

In the following function we are just passing the SQL string to the GetDataTable function which returns the datatable filled with the results. and the Datagrid is being populated with this table.

Private Sub btnFetchData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFetchData.Click
Dim sql As String
sql = "SELECT UserID,SectionID, PostID, ThreadID, PostAuthor, PostLevel,PostDate FROM dbo.cs_Posts order by PostID Desc"
Me.DataGridView1.DataSource = GetDataTable(sql)

End Sub

In ASP.NET the Connection string is normally stored in the web.config file

Following example shows a connection string for a sql server 2008 database.



This is how you would read the connection string from the web.config file

Public gsConnString As String = ConfigurationSettings.ConnectionStrings("vbnet_ConnectionString").connectionstring

They you can open the connection using the Openconnection function as described above.

How to execute a SQL Server Procedure in VB.NET

How to execute a Backend Procedure in SQL Server using VB.NET

Here is a function which allows you to execute a SQL Server Procedure in VB.NET

Function ExecuteProcedure_DeleteRecord() as boolean

Dim cn As New SqlConnection
Dim cmd As New SqlCommand
Dim sql As String

sql = "cs_Post_Delete"
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@SectionID", "200") ' string then enclose it in the double quotes
cmd.Parameters.AddWithValue("@SettingsID", 1000) ' numeric value then you can put it without any quotes
cmd.Parameters.AddWithValue("@PostID", Me.TextBox1.Text) ' Here we are picking a value from a textbox
cmd.Parameters.AddWithValue("@ResetStatistics", 1)
cmd.Parameters.AddWithValue("@DeletedBy", 2109)
cmd.Parameters.AddWithValue("@Reason", "Deleted due to SPAM")
cmd.CommandText = sql
cmd.Connection = cn

Try
'Call Open database - connect to the database
OpenConnection(cn) ' I use a seperate function to open a new connection
dc.ExecuteNonQuery()

Catch
MsgBox("Unable to Delete the record:," & Err.Description)
'Finally
da.Dispose()
dc.Dispose()
dcB.Dispose()
CloseConnection(cn) ' I use a seperate function to close an existing connection
MsgBox("Record Deleted")
End Try

End Sub

Extract the file name from the complete file path

Sometimes there is a need to get the file name from the complete path name

Use the following function

If you pass URL as “http:/vbnet.redirect.com.au/defaul.aspx ” the following

function will return “default.aspx”

Function GetFileNamefromURL(ByVal URL String) As String
Return FILEPath.Substring(FILEPath.LastIndexOf("/") + 1)
End Function

In this function if you pass “c:\temp\myfile.txt” it will return “myfile.txt”

Function GetFileNamefromFileSystemPath(ByVal FILEPath As String) As String
Return FILEPath.Substring(FILEPath.LastIndexOf("\") + 1)
End Function

How to Copy a File in vb.net

Copy a file in the file system

Supply a source file name and the destination file name

CopyFile(“c:\temp\firstfile.txt”, c:\temp\secondfile.txt”)

Function Copyfile(ByVal sSource As String, ByVal sDest As String) As Boolean

FileCopy(sSource, sDest)

If Err.Number = 0 Then Copyfile = True

End Function

Fix the SQL Single quote problems in SQL in vb.net

When the sql contains the single quotes then they have to be properly escaped otherwise it will give errors

just pass the field value which you want to check and the following function will return you the properly escaped string.

eg. o’brien will be returned as o”brien and this will not cause your sql to break.

Function FixSql(ByVal sStr As String) As String

FixSql = Replace(sStr, “‘”, “””)

End Function

How to get the file extension from the filepath

The following function returns the file extension only.

eg. using the following function

GetFileExtension(“c:\test\file.aspx”) will return “.aspx”

Here is the complete function, copy and paste it in your form or a module

Function GetFileExtension(ByVal sFilePath As String) As String
' Dim fullPath As String = "c:\MyDirectory\MYFile.txt"
Dim sExtension As String = IO.Path.GetExtension(sFilePath)
Return (sExtension)
End Function

Redirect to a new page in vb.net

Here is an example of a stand alone page which can help you to move your traffic to your new page.

Just copy the following lines into a note pad , remember to change the url to the actual page where you want to redirect.

Save the page with a .aspx extension.

eg. If you save this page as “OLD.ASPX” then every time someone types in this url eg. “http://vbnet.redirect.com.au/old.aspx”

it will redirect to the new page

<%@ Page Language="VB" Debug="true" %>

How to find First Sunday of each month

Here is the funtion in vb.net to find the first Sunday of each month

Function GetFirstSunday(ByVal dt As Date) As String

Dim inputDate As DateTime

Dim outputDate As DateTime

inputDate = Convert.ToDateTime(dt)

‘ Start at the day 1

outputDate = New DateTime(inputDate.Year, inputDate.Month, 1)

‘Loop through the month looking for the first Sunday

For i As Int32 = 0 To Date.DaysInMonth(outputDate.Year, outputDate.Month)

If outputDate.AddDays(i).DayOfWeek = DayOfWeek.Sunday Then

outputDate = outputDate.AddDays(i)

Exit For ‘We’re done.. get out of the loop

End If

Next

GetFirstSunday = FormatDateTime(outputDate, DateFormat.LongDate)

End Function

How to generate a random number in vb.net

How to generate a random number in vb.net

Function GenerateRandomNumber(iLowerRange As Integer, iUpperRange As Integer)
as integer

Dim RandomGenerator As Random
Dim intRandomNumber As Integer

RandomGenerator = New Random()

'Get the next random number
intRandomNumber = RandomGenerator.Next(iLowerRange, iUpperRange+ 1)

'Return the random # as the function's return value
return intRandomNumber

End Function