Creating chart in Excel:

The class "cExcelChart" does everything, just use this class.

Using example:This will create graph and convert graph to image and return image path.

call this code in form:
Dim sGraphQuery As String = "SELECT Periodname,"
sGraphQuery = sGraphQuery & " Instructed,Completed,Cancelled "
'sGraphQuery = sGraphQuery & " Instructed + 8 as Instructed ,Completed + 8 as completed, Cancelled + 8 as cancelled "
sGraphQuery = sGraphQuery & " FROM vwCaseTypeInstructionsCompletionsCancellations"
sGraphQuery = sGraphQuery & " where casetypeid = 0 "


Dim arCols(3) As String
'"Period"
arCols(0) = "Period"
arCols(1) = "Cases Instructed"
arCols(2) = "Cases Completed"
arCols(3) = "Cases Cancellated"

Dim chartTitle As String = "" '"Sale Summary"
Dim xaxisTitle As String = "Period"
Dim yaxisTitle As String = "Instructions"
Dim ChartFullpath As String = System.IO.Path.Combine(chartOutputPath, "ICC_Sale.gif")

Dim sPath As String = generateGraph(sGraphQuery, ChartFullpath, chartTitle, xaxisTitle, yaxisTitle, arCols)

Public Function generateGraph(ByVal sQuery As String, Optional ByVal outputImagePath As String = "", Optional ByVal ChartTitle As String = "", Optional ByVal xAxisTitle As String = "", Optional ByVal yAxisTitle As String = "", Optional ByVal arrColumns() As String = Nothing) As String
Dim ds As DataSet = getDataset(sQuery)

Dim objChart As Object
If arrColumns.Length = 0 Then
objChart = New cExcelChart(ds)
Else
objChart = New cExcelChart(ds, arrColumns)
End If

'Dim objChart As New cExcelChart(ds)
'objChart.ColumnNames = arrColumns

objChart.ChartTitle = ChartTitle
objChart.xAxisTitle = xAxisTitle
objChart.yAxisTitle = yAxisTitle
objChart.AxisTitleFontColor = Color.Blue
objChart.chartOutputPath = outputImagePath

'settings
'ChartTitle
objChart.ChartTitleFontName = "Arial"
objChart.ChartTitleFontSize = 20
objChart.ChartTitleFontColor = Color.Blue

'axes title
objChart.AxisTitleFontColor = Color.DodgerBlue
objChart.AxisTitleFontName = "Arial"
objChart.AxisTitleFontSize = 8

'x and y and legand text colors
objChart.AxisTickLabelsFontColor = Color.DodgerBlue

Return objChart.GenerateChart
End Function


'getDataset
Public Function getDataset(ByVal sQuery As String) As DataSet
'Status Display
lblStatus.Text = "Status:" & vbCrLf & "1. Process started... "
lblStatus.Refresh()

Dim con As SqlConnection
con = New SqlConnection(My.Settings.SQLConnString)
Dim da As SqlDataAdapter = New SqlDataAdapter(sQuery, con)
da.SelectCommand.CommandType = CommandType.Text
Dim ds As DataSet = New DataSet("Report")
con.Open()
System.Threading.Thread.Sleep(1500)
da.Fill(ds)
Return ds
End Function




Imports System
Imports System.Data
Imports System.IO
Imports System.Runtime.InteropServices


Public Class cExcelChart
'constructers

#Region "Constructors"
Friend Sub New(ByVal ds As DataSet)
m_ds = ds
Dim iColumns As Integer

'default column names
For iColumns = 0 To m_ds.Tables(0).Columns.Count - 1
ReDim Preserve m_ColumnNames(iColumns)
m_ColumnNames(iColumns) = m_ds.Tables(0).Columns(iColumns).ColumnName
Next
End Sub

Friend Sub New(ByVal ds As DataSet, ByVal ColumnNames() As String)
m_ColumnNames = ColumnNames
m_ds = ds
End Sub

'If File.Exists(Filename) Then
' File.Delete(Filename)
'End If

Friend Sub New(ByVal ds As DataSet, ByVal ColumnNames() As String, ByVal chartImageFullPath As String)
m_ColumnNames = ColumnNames
m_ds = ds
m_chartImageFullPath = chartImageFullPath
End Sub

#End Region

#Region "properties"

'properties
' Filename = AppDomain.CurrentDomain.BaseDirectory & "abc.xls"
'Private m_Filename As String = "abc.xls"
Private m_ColumnNames() As String 'column names
Private m_ChartTitle As String = "" ' Chart title
Private m_xAxisTitle As String = "" 'x axis title
Private m_yAxisTitle As String = "" 'y axis
Private m_FileFullpath As String = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "abc.xls")
Private m_ds As DataSet 'dataset
Private m_chartImageFullPath As String = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, Now.Millisecond & ".gif")

Private m_ChartTitleFontColor As Color = Color.Black
Private m_ChartTitleFontName As String = "Arial"
Private m_ChartTitleFontSize As Integer = 14

Private m_AxisTitleFontColor As Color = Color.Black
Private m_AxisTitleFontName As String = "Arial"
Private m_AxisTitleFontSize As Integer = 10

Private m_AxisTickLabelsFontColor As Color = Color.Black

Public Property ColumnNames() As Array
Get
Return m_ColumnNames
End Get
Set(ByVal value As Array)
m_ColumnNames = value
End Set
End Property

Public Property ChartTitle() As String
Get
Return m_ChartTitle
End Get
Set(ByVal value As String)
m_ChartTitle = value
End Set
End Property

Public Property ChartTitleFontColor() As Color
Get
Return m_ChartTitleFontColor
End Get
Set(ByVal value As Color)
m_ChartTitleFontColor = value
End Set
End Property


Public Property ChartTitleFontName() As String
Get
Return m_ChartTitleFontName
End Get
Set(ByVal value As String)
m_ChartTitleFontName = value
End Set
End Property

Public Property ChartTitleFontSize() As Integer
Get
Return m_ChartTitleFontSize
End Get
Set(ByVal value As Integer)
m_ChartTitleFontSize = value
End Set
End Property

Public Property AxisTitleFontColor() As Color
Get
Return m_AxisTitleFontColor
End Get
Set(ByVal value As Color)
m_AxisTitleFontColor = value
End Set
End Property

Public Property AxisTitleFontSize() As Integer
Get
Return m_AxisTitleFontSize
End Get
Set(ByVal value As Integer)
m_AxisTitleFontSize = value
End Set
End Property

Public Property AxisTitleFontName() As String
Get
Return m_AxisTitleFontName
End Get
Set(ByVal value As String)
m_AxisTitleFontName = value
End Set
End Property

Public Property AxisTickLabelsFontColor() As Color
Get
Return m_AxisTickLabelsFontColor
End Get
Set(ByVal value As Color)
m_AxisTickLabelsFontColor = value
End Set
End Property


Public Property xAxisTitle() As String
Get
Return m_xAxisTitle
End Get
Set(ByVal value As String)
m_xAxisTitle = value
End Set
End Property

'TickLabels
Public Property yAxisTitle() As String
Get
Return m_yAxisTitle
End Get
Set(ByVal value As String)
m_yAxisTitle = value
End Set
End Property

'excel filepath
Public Property chartOutputPath() As String
Get
Return m_chartImageFullPath
End Get
Set(ByVal value As String)
m_chartImageFullPath = value
End Set
End Property

Public WriteOnly Property dsDataset() As DataSet
Set(ByVal value As DataSet)
m_ds = value
End Set
End Property
#End Region

'Function GenerateChart(ByVal arrColumns() As String, ByVal ds As DataSet) As String 'Excel.Chart
Function GenerateChart() As String 'Excel.Chart
'create new excel application
Dim oexcel As Excel.Application
Dim obook As Excel.Workbook
Dim osheet As Excel.Worksheet
oexcel = CreateObject("Excel.Application")
Try
'add a new workbook
obook = oexcel.Workbooks.Add

'check total sheets in workboob
Dim S As Integer = oexcel.Application.Sheets.Count()

'leaving first sheet delete all the remaining sheets
If S > 1 Then
oexcel.Application.DisplayAlerts = False
Dim J As Integer = S
Do While J > 1
oexcel.Application.Sheets(J).delete()
J = oexcel.Application.Sheets.Count()
Loop
End If
osheet = oexcel.Worksheets(1)
oexcel.Visible = True

'column heading
Dim iColumns As Integer
For iColumns = 0 To m_ColumnNames.Length - 1
osheet.Range(getChar(iColumns + 1) & "3").Value = m_ColumnNames(iColumns)
Next

'start of row
Dim R As Integer = 3
Dim intI As Integer
For intI = 0 To m_ds.Tables(0).Rows.Count - 1
R = R + 1
'For iColumns = 0 To m_ColumnNames.Length - 1
For iColumns = 0 To m_ds.Tables(0).Columns.Count - 1
osheet.Range(getChar(iColumns + 1) & 4 + intI).Value = m_ds.Tables(0).Rows(intI).Item(iColumns)
Next
Next

'create chart objects
Dim oChart As Excel.Chart
Dim MyCharts As Excel.ChartObjects
Dim MyCharts1 As Excel.ChartObject
MyCharts = osheet.ChartObjects

'set chart location
MyCharts1 = MyCharts.Add(150, 130, 430, 250)
oChart = MyCharts1.Chart

With oChart

'set data range for chart
Dim chartRange As Excel.Range
chartRange = osheet.Range("A3", getChar(m_ds.Tables(0).Columns.Count) & R)
.SetSourceData(chartRange)

'set how you want to draw chart i.e column wise or row wise
.PlotBy = Excel.XlRowCol.xlColumns

.HasTitle = True
'.ChartTitle.Font.Bold = True
'.ChartTitle.Caption = "mix"
.ChartTitle.Font.Size = ChartTitleFontSize
.ChartTitle.Font.Name = ChartTitleFontName
.ChartTitle.Font.Color = ColorTranslator.ToOle(ChartTitleFontColor)

.ChartTitle.Text = ChartTitle

'select chart type
.ChartType = Excel.XlChartType.xlLineMarkers

'set data lables for bars
'.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowLabelAndPercent)
.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowNone)

'set legend to be displayed or not
.HasLegend = True
'
''remove border
.ChartArea.Border.LineStyle = Excel.XlLineStyle.xlLineStyleNone

'set legend location
.Legend.Position = Excel.XlLegendPosition.xlLegendPositionRight

''format legend keys
Dim le As Excel.LegendEntry
Dim lk As Excel.LegendKey
For Each le In .Legend.LegendEntries
lk = le.LegendKey
lk.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone
le.Font.Color = ColorTranslator.ToOle(AxisTickLabelsFontColor) 'RGB(1, 2, 3)
lk.Border.Weight = Excel.XlBorderWeight.xlThin
'le.LegendKey.Border.LineStyle = Excel.XlLineStyle.xlContinuous
Next

'Dim i As Integer = 1
'For i = 1 To .SeriesCollection.count()
' .SeriesCollection(i).Border.Color = ColorTranslator.ToOle(Color.AliceBlue)
'Next

'series colors
'.SeriesCollection(1).Border.Color = ColorTranslator.ToOle(Color.AliceBlue)

'display title for X axis and Y axis
Dim xlAxisCategory, xlAxisValue As Excel.Axes

xlAxisCategory = CType(oChart.Axes(, Excel.XlAxisGroup.xlPrimary), Excel.Axes)
xlAxisCategory.Item(Excel.XlAxisType.xlCategory).HasTitle = True

xlAxisCategory.Item(Excel.XlAxisType.xlCategory).AxisTitle.Font.Name = AxisTitleFontName
xlAxisCategory.Item(Excel.XlAxisType.xlCategory).AxisTitle.Font.Size = AxisTitleFontSize
xlAxisCategory.Item(Excel.XlAxisType.xlCategory).AxisTitle.Font.Color = ColorTranslator.ToOle(AxisTitleFontColor)
xlAxisCategory.Item(Excel.XlAxisType.xlCategory).AxisTitle.Characters.Text = xAxisTitle

xlAxisValue = CType(oChart.Axes(, Excel.XlAxisGroup.xlPrimary), Excel.Axes)
xlAxisValue.Item(Excel.XlAxisType.xlValue).HasTitle = True

xlAxisValue.Item(Excel.XlAxisType.xlValue).AxisTitle.Font.Name = AxisTitleFontName
xlAxisValue.Item(Excel.XlAxisType.xlValue).AxisTitle.Font.Size = AxisTitleFontSize
xlAxisValue.Item(Excel.XlAxisType.xlValue).AxisTitle.Font.Color = ColorTranslator.ToOle(AxisTitleFontColor)
xlAxisValue.Item(Excel.XlAxisType.xlValue).AxisTitle.Characters.Text = yAxisTitle

'to start x axes from zero
xlAxisValue.Item(Excel.XlAxisType.xlCategory).AxisBetweenCategories = False

'color of x and y axis labels
xlAxisValue.Item(Excel.XlAxisType.xlCategory).TickLabels.Font.Color = ColorTranslator.ToOle(AxisTickLabelsFontColor)
xlAxisValue.Item(Excel.XlAxisType.xlValue).TickLabels.Font.Color = ColorTranslator.ToOle(AxisTickLabelsFontColor)

'x-axis grid lines
xlAxisValue.Item(Excel.XlAxisType.xlValue).MajorGridlines.Border.Color = ColorTranslator.ToOle(AxisTickLabelsFontColor)
xlAxisValue.Item(Excel.XlAxisType.xlValue).MajorGridlines.Border.Weight = Excel.XlBorderWeight.xlHairline
'xlAxisValue.Item(Excel.XlAxisType.xlCategory).MajorGridlines.Select()
'xlAxisValue.Item(Excel.XlAxisType.xlCategory).MajorGridlines.Border.Color = ColorTranslator.ToOle(AxisTickLabelsFontColor)
xlAxisValue.Item(Excel.XlAxisType.xlCategory).Border.Color = ColorTranslator.ToOle(AxisTickLabelsFontColor)

'xlAxisValue.Item(Excel.XlAxisType.xlCategory).Border.Color = ColorTranslator.ToOle(Color.Transparent)
'xlAxisValue.Item(Excel.XlAxisType.xlCategory).Border.LineStyle = Excel.XlLineStyle.xlLineStyleNone

'xlAxisValue.Item(Excel.XlAxisType.xlValue).MinimumScale = 0

'Yaxis grid lines
'xlAxisValue.Item(Excel.XlAxisType.xlCategory).MajorGridlines.Border.Color = RGB(0, 255, 0)
End With
'oChart.Export("C:\download\GraphSheetControl\" & Now.Millisecond & ".gif", "GIF")
oChart.Export(m_chartImageFullPath, System.IO.Path.GetExtension(m_chartImageFullPath).Replace(".", ""))

osheet = Nothing
oexcel.Application.DisplayAlerts = False
obook.Close()
oexcel.Application.DisplayAlerts = True
obook = Nothing
Catch ex As Exception
Throw ex
Finally
oexcel.Quit()
oexcel = Nothing
End Try

Return m_chartImageFullPath
End Function

Function getChar(ByVal iNum As Integer) As Char
Select Case iNum
Case 1
Return "A"
Case 2
Return "B"
Case 3
Return "C"
Case 4
Return "D"
Case 5
Return "E"
Case 6
Return "F"
Case 7
Return "G"
Case 8
Return "H"
Case 9
Return "I"
Case 10
Return "J"
Case 11
Return "K"
Case 12
Return "L"
Case 13
Return "M"
Case 14
Return "N"
Case 15
Return "O"
Case 16
Return "P"
Case 17
Return "Q"
Case 18
Return "R"
Case 19
Return "S"
Case 20
Return "T"
Case 21
Return "U"
Case 22
Return "V"
Case 23
Return "W"
Case 24
Return "X"
Case 25
Return "Y"
Case 26
Return "Z"
End Select
End Function

End Class

solution for combo box and listbox value and text

define a class for value and text strings and use them to get the information

example as below:


Dim sValue As String
Dim sText As String

Dim mli As MyListBoxItem = CType(cboMonth.SelectedItem, MyListBoxItem)
sText=mli.ToString 'or sText=cboMonth.Text
svalue=mli.Value.ToString()


If cboMonth.Text = "" Then
MsgBox("please select a month")
Exit Sub
Else
sMonth = mli.ToString
End If


Class MyListBoxItem

Protected m_text As String
Protected m_value As Integer

Public Sub New(ByVal Text As String)
m_text = Text
m_value = 0
End Sub

Public Sub New(ByVal Text As String, ByVal Value As Integer)
m_text = Text
m_value = Value
End Sub

Public Property Value() As Integer
Get
Return m_value
End Get
Set(ByVal Value As Integer)
m_value = Value
End Set
End Property

Public Overrides Function ToString() As String
Return m_text
End Function


End Class

Combine file path with out worring about '\' problems

System.IO.Path.Combine does the '\' checking for us, we don't need to worry about \\ problems which occur sometimes.
when building a path string use System.IO.Path.Combine

example:
System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "filename.gif")
will output: fullpath\filename.gif

how do i find all the foreign keys in a database

SELECT
K_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
-- optional:
ORDER BY
1,2,3,4
and limit values by adding where condition

WHERE PK.TABLE_NAME='something'WHERE FK.TABLE_NAME='something'

WHERE PK.TABLE_NAME IN ('one_thing', 'another')

WHERE FK.TABLE_NAME IN ('one_thing', 'another')

Ref: how do i find all the foreign keys in a database

Query between 2 servers

We can query between two servers
SQL Server query between two servers.

as below
select IntroducerID as eh_IntroducerID, [name] as ec_InroducerName, userid as ec_userid from [192.168.NNN.XXX].[mydatabasename1].[dbo].[table1] as t1
inner join [192.168.NNN.XYZ.[mydatabasename2].[dbo].[table2] as t2
on t1.ColumnId=t2.Columnid
where t1.status=1 and t2.columnname in( 'djgfd','skdd')

VB and VBScript passing array to a function

Private Sub PassArray()
Dim myArray() 'Declare a Static Integer Array of 4 elements
Dim arrayIndex

arrayIndex = 0
ReDim Preserve myArray(arrayIndex)
myArray(0) = "one"

arrayIndex=arrayIndex+1
ReDim Preserve myArray(arrayIndex)
myArray(1) = "two"

arrayIndex=arrayIndex+1
ReDim Preserve myArray(arrayIndex)
myArray(2) = "three"

arrayIndex=arrayIndex+1
ReDim Preserve myArray(arrayIndex)
myArray(3) = "four"

Call AcceptArray(myArray) 'Call the procedure and pass the Array
End Sub

Private Sub AcceptArray(outArray())
dim iCount
For iCount=0 to ubound(outArray)
WScript.echo outArray(iCount)
Next
End Sub

CDONTS multiple attachments sendmail

CDONTS_Object.AttachFile(Physical_Path_to_File, English_Name_For_File)

Assuming your files already exist on the server:

1) Include the Server.MapPath() or specify the full file path.
It doesn't seem to like virtual paths
2) Set the BodyFormat and Mail Format to Zero(0)
3) Then repeat the Attachfile method for each file

<%
Dim objMail
Set objMail = Server.CreateObject("CDONTS.NewMail")
objMail.From = "test@myserver.com"
objMail.To = "youremail@account.net"
objMail.Subject = "Multiple File Attachments"
objMail.Body = "Intentionally Left Blank"
objMail.BodyFormat = 0
objMail.MailFormat = 0
objMail.AttachFile( Server.MapPath("images") & "\file1.jpg")
objMail.AttachFile( Server.MapPath("images") & "\file2.jpg")
objMail.AttachFile( Server.MapPath("images") & "\file3.jpg")
objMail.Send
Set objMail=Nothing
%>

VB, VBScript Method to cut long words in a text,

'function to add spaces between long string
Function FormatString(LongString, maxWordLen)
' Split the string at the space characters.
Dim words
words = Split(LongString, " ")

Dim iWordsCount,iCharCount, newString, stringSegment

'for each words
For iWordsCount = 0 To UBound(words)

'for lenght of the string
if len(words(iWordsCount))>maxWordLen then
for iCharCount = 1 to len(words(iWordsCount)) step maxWordLen
'get string segment
stringSegment=mid(words(iWordsCount),iCharCount,maxWordLen)
if len(newString) > 0 then
'add a space char for long string
newString = newstring & " " & replace(stringSegment," ","")
else
newString=replace(stringSegment," ","")
end if
next
Else
if len(newString) > 0 then
'add a space char between words
newString = newString & " " & replace(words(iWordsCount)," ","")
else
newString=replace(words(iWordsCount)," ","")
end if
End if
Next
FormatString = newString
End Function

Call webservice without adding webreference:

1. Create a class as with "DynamicWebService" and new method "CallWebService".
2. Create an object od this class and call method "CallWebService" with web service url and other paramaters.

CLASS:
Create a new class and replace the code for this:

<------------------------------

Imports System.CodeDom
Imports System.CodeDom.Compiler
Imports System.Security.Permissions
Imports System.Web.Services.Description
Imports System.Reflection

Public Class DynamicWebService

Public Function CallWebService(ByVal webServiceAsmxUrl As String, ByVal serviceName As String, ByVal methodName As String, ByVal args() As Object) As Object
Try

Dim client As System.Net.WebClient = New System.Net.WebClient()

'-Connect To the web service
Dim stream As System.IO.Stream = client.OpenRead(webServiceAsmxUrl + "?wsdl")

'Read the WSDL file describing a service.
Dim description As ServiceDescription = ServiceDescription.Read(stream)

'LOAD THE DOM'''''''''''''''''''''''''''
'--Initialize a service description importer.
Dim importer As ServiceDescriptionImporter = New ServiceDescriptionImporter()
importer.ProtocolName = "Soap12" ' Use SOAP 1.2.
importer.AddServiceDescription(description, Nothing, Nothing)

'--Generate a proxy client.
importer.Style = ServiceDescriptionImportStyle.Client

'--Generate properties to represent primitive values.
importer.CodeGenerationOptions = System.Xml.Serialization.CodeGenerationOptions.GenerateProperties

'Initialize a Code-DOM tree into which we will import the service.
Dim nmspace As CodeNamespace = New CodeNamespace()
Dim unit1 As CodeCompileUnit = New CodeCompileUnit()
unit1.Namespaces.Add(nmspace)

'Import the service into the Code-DOM tree.
'This creates proxy code that uses the service.
Dim warning As ServiceDescriptionImportWarnings = importer.Import(nmspace, unit1)
If warning = 0 Then

'--Generate the proxy code
Dim provider1 As CodeDomProvider = CodeDomProvider.CreateProvider("VB")

'--Compile the assembly proxy with the // appropriate references
Dim assemblyReferences() As String
assemblyReferences = New String() {"System.dll", "System.Web.Services.dll", "System.Web.dll", "System.Xml.dll", "System.Data.dll"}

Dim parms As CompilerParameters = New CompilerParameters(assemblyReferences)
Dim results As CompilerResults = provider1.CompileAssemblyFromDom(parms, unit1)

'-Check For Errors
If results.Errors.Count > 0 Then
Dim oops As CompilerError
For Each oops In results.Errors
System.Diagnostics.Debug.WriteLine("========Compiler error============")
System.Diagnostics.Debug.WriteLine(oops.ErrorText)
Next
Throw New System.Exception("Compile Error Occured calling webservice.")
End If

'--Finally, Invoke the web service method
Dim wsvcClass As Object = results.CompiledAssembly.CreateInstance(serviceName)
Dim mi As MethodInfo = wsvcClass.GetType().GetMethod(methodName)
Return mi.Invoke(wsvcClass, args)
Else
Return Nothing
End If
Catch ex As Exception
Throw ex
End Try
End Function
End Class
----------------------------------------------->

CALLING:

Call this web service in a form or method etc... as below..

--------------------------
Dim WebserviceUrl As String = "http://www.abc.com/lgl/test/webservice/v1_00/security.asmx"

'specify service name
Dim serviceName As String = "SecurityAndSessionManagement"

'specify method name to be called
Dim methodName As String = "Session_Start"

'Paraments passed to the method
Dim arArguments(1) As String
arArguments(0) = "abc"
arArguments(1) = "xxxx"

Dim objCallWS As New DynamicWebService
sSessionID = objCallWS.CallWebService(WebserviceUrl, serviceName, methodName, arArguments)
MsgBox("new SessionID: " & sSessionID)
---------------------

good article for XSL: The Complete Guide

http://www.sagehill.net/docbookxsl/

Build custon XML from database using XmlTextWriter

Simple way to out put as xml is using
dim ds as dataset
ds.WriteXml("C:\test\HCSReports\createdXML\employee.xml")

if you need custom xml output then use the below function

Imports System.Xml
Imports System.Xml.XPath
Imports System.Xml.Xsl

  Public Function CreateCustomXML(ByVal objDataSet As DataSet) As String

        Dim intI As Integer
        Dim strPath As String

        'create an instance of the XmlTextWriter object
        Dim objWriter As XmlTextWriter = Nothing

        Try

            ' location to the XML file to write
            strPath = strFilepath & Now.Second & ".xml"

            objWriter = New XmlTextWriter(strPath, System.Text.Encoding.Default)

            ' start writing the XML document
            objWriter.WriteStartDocument()

            ' write a comment in our XML file
            objWriter.WriteComment("PanelManagerMonthlyReport")

            ' starting with the root element i.e. "movies"
            objWriter.WriteStartElement("PMMonthlyReport")

            'Set data to XML tags
            If objDataSet.Tables(0).Rows.Count > 0 Then
                For intI = 0 To objDataSet.Tables(0).Rows.Count - 1
                    objWriter.WriteStartElement("Period")    ' output the "Employee" element
                    objWriter.WriteElementString("Name", objDataSet.Tables(0).Rows(intI).Item("Name").ToString)
                    objWriter.WriteElementString("Prospects", objDataSet.Tables(0).Rows(intI).Item("Prospects").ToString)
                    objWriter.WriteElementString("Instructions", objDataSet.Tables(0).Rows(intI).Item("Instructions"))
                    objWriter.WriteElementString("Cancellations", objDataSet.Tables(0).Rows(intI).Item("Cancellations"))
                    objWriter.WriteElementString("Completions", objDataSet.Tables(0).Rows(intI).Item("Completions"))
                    objWriter.WriteElementString("Quotes", objDataSet.Tables(0).Rows(intI).Item("Quotes"))
                    objWriter.WriteElementString("IntroducerUserRegistrations", objDataSet.Tables(0).Rows(intI).Item("IntroducerUserRegistrations"))
                    objWriter.WriteElementString("IntroducerUserTrained", objDataSet.Tables(0).Rows(intI).Item("IntroducerUserTrained"))
                    objWriter.WriteElementString("SolicitorAppsIssued", objDataSet.Tables(0).Rows(intI).Item("SolicitorAppsIssued"))
                    objWriter.WriteElementString("SolicitorContractsReturned", objDataSet.Tables(0).Rows(intI).Item("SolicitorContractsReturned"))
                    objWriter.WriteElementString("Index", objDataSet.Tables(0).Rows(intI).Item("Index"))
                    objWriter.WriteEndElement()                 ' close "Employee" element
                Next
            End If

            ' end the "Menu" element
            objWriter.WriteEndElement()

            ' flush and write XML data to the file
            objWriter.Flush()

            Return strPath
        Catch ex As Exception
            Return False
        Finally
            ' clear up memory
            objWriter.Close()
            objWriter = Nothing
            objDataSet = Nothing
        End Try
    End Function

Design Patterns

http://www.dofactory.com/Patterns/Patterns.aspx#list
scroll down...

Creational Patterns
Abstract Factory	Creates an instance of several families of classes
Builder	Separates object construction from its representation
Factory Method	Creates an instance of several derived classes
Prototype	A fully initialized instance to be copied or cloned
Singleton	A class of which only a single instance can exist
Structural Patterns
Adapter	Match interfaces of different classes
Bridge	Separates an object’s interface from its implementation
Composite	A tree structure of simple and composite objects
Decorator	Add responsibilities to objects dynamically
Facade	A single class that represents an entire subsystem
Flyweight	A fine-grained instance used for efficient sharing
Proxy	An object representing another object
Behavioral Patterns
Chain of Resp.	A way of passing a request between a chain of objects
Command	Encapsulate a command request as an object
Interpreter	A way to include language elements in a program
Iterator	Sequentially access the elements of a collection
Mediator	Defines simplified communication between classes
Memento	Capture and restore an object's internal state
Observer	A way of notifying change to a number of classes
State	Alter an object's behavior when its state changes
Strategy	Encapsulates an algorithm inside a class
Template Method	Defer the exact steps of an algorithm to a subclass
Visitor	Defines a new operation to a class without change

'if the enum is :
Public Enum aslamEnum
Created = 0
AwaitingPayment
Active
InProduction
Complete
Cancelled
End Enum


'The function will return description if exist else the name:

Private Function GetEnumDescription(ByVal Value As aslamEnum) As String
Dim objFieldInfo As FieldInfo
Dim sRetVal As String
Dim objDescriptionAttributes() As DescriptionAttribute
Dim tEnum As Type = GetType(aslamEnum)

objFieldInfo = tEnum.GetField([Enum].GetName(tEnum, Value))
objDescriptionAttributes = objFieldInfo.GetCustomAttributes(GetType(DescriptionAttribute), False)
With objDescriptionAttributes
If .Length > 0 Then
sRetVal = objDescriptionAttributes(0).Description
Else
sRetVal = [Enum].GetName(tEnum, Value)
End If
End With

Return Trim(sRetVal)
End Function


'To get the value back either in Name or Description use
value=GetEnumDescription(varmyEnum)

Simple chat application for ASP.NET

http://www.codeproject.com/aspnet/SimpleChat.asp?df=100&forumid=53269&fr=26

Forum

http://www.codeproject.com/aspnet/JumpyForum.asp

asp.net vb.net shopping cart and c#

http://www.dotnetheaven.com/Uploadfile/munnamax/ShoppingCart02102006010016AM/ShoppingCart.aspx

c# http://www.codeproject.com/aspnet/ShoppingCartCSharp.asp

Payment Gateway:
http://www.vbdotnetheaven.com/UploadFile/munnamax/PaymentGateway02152006022631AM/PaymentGateway.aspx

Drag and Drop Products to the Shopping Basket Using JavaScript
http://www.codeproject.com/aspnet/DragAndDropShopping.asp

URL Rewriting with ASP.NET

http://www.codeproject.com/aspnet/URLRewriter.asp

Search Engine Optimization (SEO) ASP.NET

click on
1. http://www.codeproject.com/useritems/10_SEO_Tips.asp?msg=2263438

2. http://www.wwwcoder.com/main/parentid/457/site/6173/68/default.aspx

Build Google IG like Ajax Start Page

Nice article and code here

open pdf in webform

convert the file in byte array(i.e byte()) using function :

'get data from file and return as byte array
Public Function GetFileData(path as string) As Byte()
Try
Dim fsReader As New IO.FileStream(path , IO.FileMode.Open)
Dim data() As Byte = New Byte(fsReader.Length) {}
fsReader.Read(data, 0, fsReader.Length)
fsReader.Close()
Return data
Catch
Throw
End Try
End Function

dim buf() as byte
buf=GetFileData()

If buf.Length > 0 Then
'flush retrieved document
Response.Clear()
Response.ClearHeaders()
Response.ClearContent()
Response.ContentType = "Application/pdf"
Response.AddHeader("content-disposition", "inline; filename=ehips_SW_" & sTaskRef & ".pdf")
Response.AddHeader("content-transfer-encoding", "binary")
Response.BinaryWrite(buf)
Response.End()
End If



should display PDF in browser

Stored procedure using Cursors

the below is a sample example for stored procedure and cursors
CREATE PROCEDURE prcCursorExample
AS

-- declare all variables!

DECLARE @iRowId int,
@vchCustomerName nvarchar(255),
@vchCustomerNmbr nvarchar(10)

-- declare the cursor
DECLARE Customer CURSOR FOR
SELECT iRowId,
vchCustomerNmbr,
vchCustomerName
FROM CustomerTable

OPEN Customer

FETCH Customer INTO @iRowId,
@vchCustomerNmbr,
@vchCustomerName


-- start the main processing loop.
WHILE @@Fetch_Status = 0

BEGIN

-- This is where you perform your detailed row-by-row processing. INSERT INTO ORDER(CUSTOMERNUMBER,CUSTOMERNAME) values(@vchCustomerNmbr,@vchCustomerName)

-- Get the next row.
FETCH Customer INTO @iRowId,
@vchCustomerNmbr,
@vchCustomerName
END

CLOSE Customer
DEALLOCATE Customer
RETURN

sql server tutorial

sql server tutorial

SQL Server views

click here

SQL Server : How to simulate the GROUP_CONCAT function

there is no groupconcat in sql server (only available in Mysql), but could be made using the example in the link below

Click the link

SQL Server : How to simulate the GROUP_CONCAT function

SQL Server How To

following questions have been asnwered in the link
Add a column to a table unless it already exists
Count the total number of records in all user tables
Create a table unless it already exists
Create a temporary table
Create a view unless it already exists
Create an index unless it already exists
Determine the name of the database you are connected to
Determine the name of the user for the current session
Determine which service pack is installed
Export data to a comma separated file
Import data from a comma separated file
List all currently connected users
List all columns in a table
List all databases
List all indexes on a table
List all filegroups
List all tables
List all tables or indexes within a filegroup
List all tables that contain a given column name
List the size of each table in the database
List table information
Move a database file
Obtain record field size information
Restore an NT4 system and SQL Server 7 databases from a full backup
Restore a W2K system and SQL Server 2000 databases from a full backup
Run dbcc checkdb on each database (using cursors)
Script a full NT4 and SQL Server 7 backup
Script a full W2K and SQL Server 2000 backup
Set a single database into Single User Mode
SQL Server Replication
Start SQL Server 2000 in Single User Mode
Test if a trigger exists

Using the CASE in sql server

select title,
case
when price < 12.00 then 'Cheap'
when price < 3.00 then 'Really Cheap'
when price > 12.00 and price < 20.00 then 'Average'
else 'Expensive' end 'Price Category'
from pubs.dbo.titles

The output looks like this:

title Price Category
-------------------------------------------------------- ------------
The Busy Executive's Database Guide Average
Cooking with Computers: Surreptitious Balance Sheets Cheap
You Can Combat Computer Stress! Cheap
Straight Talk About Computers Average
Silicon Valley Gastronomic Treats Average


from http://www.databasejournal.com/features/mssql/article.php/3288921

Most useful shortcuts in VS.NET

Switching between Windows:

Ctrl+F6 - navigate between various panes that appear in the base code editing window.
Shift+Alt+Enter - full-screen mode at any time. In full-screen mode, only the active window is visible in full screen.
Alt+F6/Alt +Shift+F6 - move cursor away from the main editing section into docked windows like Properties, Help, Dynamic help, Server Explorer (if these winows are open).
F7 - Jump to Code Behind/Base Code editing window

Editing:
Ctrl+Shift+V - cycle through the clipboard ring.
Ctrl+- (Ctrl + Hyphen) - similar with Internet Explorer, very easy to navigate from page to page.
Ctrl+Shift+- - cycles in the opposite direction.
Block Selection: - press Alt and then select the area you want with your mouse.
Line No in Code - Tools>Options>Text Editor>All Languages>General>Line numbers.
Ctrl+] :matching brace/comment/region/quote
F4: Property Window
Ctrl+Alt+L - Solution Explorer
Ctrl+Alt+O - Output Window
Ctrl+Alt+K - Task List
Ctrl+Shift+Space - intellisense window.
Ctrl+R - Word Wrap

BookMark:
Ctrl+K, Ctrl+K - Create/Remove Bookmark
Ctrl+K, Ctrl+N - Move to next bookmark
Ctrl+K, Ctrl+P - Move to previous bookmark
Ctrl+K, Ctrl+L - Clear all bookmarks

Code Format:
Ctrl+K, Ctrl+F - Auto-format selection
Ctrl+U - Convert to lower case
Ctrl+Shift+U - Convert to upper case
Ctrl+K, Ctrl+C - Comment selection
Ctrl+K, Ctrl+U - Uncomment selection

Code Outline:
Ctrl+M, Ctrl+M - Fold/Unfold the current code block
Ctrl+M, Ctrl+L - Unfold all
Ctrl+M, Ctrl+P - Stop outlining
Ctrl+M, Ctrl+O - Fold all

Running/Debugging:
F5 - Start Application in debug Mode
Ctrl+F5 - Start Without debugging
F11 - Step into
F10 – Step over.
Shift + F11 – Step Out.
Shift + F5 – Stop debugging.
Ctrl+Shift+F5 - Restart Debugging

from http://visualstudiotips.wordpress.com/

How to debug classic ASP pages in VS 2005

Here is how to make ASP debugging work:

1. Enable ASP debugging on the server. (IIS 5.1 right click on Virtual directory->properties->Configuration(in directory tab)->debugging tab->enable asp debugging)
2. Open classic ASP in VS 2005.
3. Set breakpoint.
4. View page in browser or run without debugging.
5. Debug | Attach to Process
6. Locate IIS ASP worker process (w3wp.exe on IIS6) which exposes x86 and Script and attach as Script.

When running on XP Pro/IIS 5, you need to attach to dllhost.exe instead of w3wp.exe. look for the one with Type "Script, x86" and the User Name column indicates your IIS process account (IWAM_machinename).
from http://blogs.msdn.com/mikhailarkhipov/archive/2005/06/24/432308.aspx

Remove leading and trailing zeros from a numeric string

declare @str nvarchar(10)

set @str = '00101abc2300'

select replace(ltrim(rtrim(replace(@str, '0', ' '))), ' ', '0')

SQL Server Joins good link

# Types of inner joins
* 1 Equi-join
1 Natural join
* 2 Cross join

# Outer joins
* 1 Left outer join
* 2 Right outer join
* 3 Full outer join


http://en.wikipedia.org/wiki/Join_(SQL)

Use XML and XSL to create HTML and PDF files

LINK:
HTML
http://xmlfiles.com/articles/sample_chapters/sams_xmlforaspnet/default.asp

PDF

http://www.perfectxml.com/XSLFO.PDF

http://www-128.ibm.com/developerworks/library/x-xslfo2app/#table

file read/write

'get data from file(hard disc) and return as byte array
Public Function GetFileData() As Byte()
Try
Dim fsReader As New IO.FileStream(m_sFilePath, IO.FileMode.Open)
Dim data() As Byte = New Byte(fsReader.Length) {}
fsReader.Read(data, 0, fsReader.Length)
fsReader.Close()
Return data
Catch
Throw
End Try
End Function


'Write to file with byte array
Public Sub WriteFile(ByVal Data As Byte())
'Save file to disk (throw exception if exists already and OverwriteIfExists=False)
Dim sUploadPath As String = Workspace.FileStoreLocation.ToString()
m_sFilePath = System.IO.Path.Combine(sUploadPath, m_sFileName)

'Create an output File and write Data in to this
Dim stOutputStream As Stream = System.IO.File.Create(Filepath)
stOutputStream.Write(Data, 0, Data.Length)
stOutputStream.Close() 'Close stream
End Sub

'Delete file
Public Sub DeleteFile()
System.IO.File.Delete(m_sFilePath)
End Sub

'get file from URL(Web) , return byte array
Public Function GetURLData(ByVal URL As String) As Byte()
Dim Req As HttpWebRequest
Dim SourceStream As System.IO.Stream
Dim Response As HttpWebResponse

'create a web request to the URL
Req = HttpWebRequest.Create(URL)

'get a response from web site
Response = Req.GetResponse()

'Source stream with requested document
SourceStream = Response.GetResponseStream()

'SourceStream has no ReadAll, so we must read data block-by-block
'Temporary Buffer and block size
Dim Buffer(4096) As Byte, BlockSize As Integer

'Memory stream to store data
Dim TempStream As New MemoryStream
Do
BlockSize = SourceStream.Read(Buffer, 0, 4096)
If BlockSize > 0 Then TempStream.Write(Buffer, 0, BlockSize)
Loop While BlockSize > 0

'return the document binary data
Return TempStream.ToArray()
End Function

Upload file using FileUpload

check the link:
http://www.codeproject.com/useritems/ASPNET20FileUpload.asp


Sub UploadButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)

' Before attempting to save the file, verify
' that the FileUpload control contains a file.
If (FileUpload1.HasFile) Then
' Call a helper method routine to save the file.
SaveFile(FileUpload1.PostedFile)
Else
' Notify the user that a file was not uploaded.
UploadStatusLabel.Text = "You did not specify a file to upload."
End If

End Sub

Sub SaveFile(ByVal file As HttpPostedFile)

' Specify the path to save the uploaded file to.
Dim savePath As String = "c:\temp\uploads\"

' Get the name of the file to upload.
Dim fileName As String = FileUpload1.FileName

' Create the path and file name to check for duplicates.
Dim pathToCheck As String = savePath + fileName

' Create a temporary file name to use for checking duplicates.
Dim tempfileName As String

' Check to see if a file already exists with the
' same name as the file to upload.
If (System.IO.File.Exists(pathToCheck)) Then
Dim counter As Integer = 2
While (System.IO.File.Exists(pathToCheck))
' If a file with this name already exists,
' prefix the filename with a number.
tempfileName = counter.ToString() + fileName
pathToCheck = savePath + tempfileName
counter = counter + 1
End While

fileName = tempfileName

' Notify the user that the file name was changed.
UploadStatusLabel.Text = "A file with the same name already exists." + "
" + _
"Your file was saved as " + fileName

Else

' Notify the user that the file was saved successfully.
UploadStatusLabel.Text = "Your file was uploaded successfully."

End If

' Append the name of the file to upload to the path.
savePath += fileName

' Call the SaveAs method to save the uploaded
' file to the specified directory.
FileUpload1.SaveAs(savePath)

End Sub

Storing Uploaded Files in a Database or in the File System

Link
http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=414

Regular expressions

Link:

http://yorktown.cbe.wwu.edu/sandvig/docs/RegExp.aspx

sendmail

1. Add reference to System.Web.dll
Project > Add Reference... > .NET > System.Web.dll > Select > OK

2. should have smtpserver.

3. use the below code to send email.

Public Shared Sub SendMail(strFrom as String, strTo as String, strCC as String,
strSubject as String, strBody as String, strAttachments as String, strSMTPServer as String)

'send the email
Try
Dim insMail As New MailMessage()
With insMail
.From = strFrom
.To = strTo
.Subject = strSubject
.Body = strBody
.Cc = strCC
If Not strAttachments.Equals(String.Empty) Then
Dim strFile As String
Dim strAttach() As String = strAttachments.Split(";")
For Each strFile In strAttach
.Attachments.Add(New MailAttachment(strFile.Trim()))
Next
End If
End With

If Not strSMTPServer.Equals(String.Empty) Then
SmtpMail.SmtpServer = strSMTPServer
End If

SmtpMail.Send(insMail)

Catch e As Exception
Console.WriteLine(e.Message)
End Try

End Sub

Create, read, write and delete event logs in VB.NET

In .NET the EventLog class from the System.Diagnostics namespace lets you read from existing logs, write entries to logs, create or delete event sources, delete logs, and respond to log entries. This can be useful when errors occur within your code. This code shows how to use the EventLog class.


Imports System.Diagnostics

Dim aLog As EventLog
Dim myLog As New EventLog
Dim aEventLogList() As EventLog
Dim aLogEntry As EventLogEntry
Dim aLogEntries As EventLogEntryCollection
'
' Create a new log.
'
If Not EventLog.SourceExists("MyNewSource") Then
EventLog.CreateEventSource("MyNewSource", "MyNewLog")
End If
'
' Add an event to fire when an entry is written.
'
AddHandler myLog.EntryWritten, AddressOf OnEntryWritten

With myLog
.Source = "MyNewSource"
.Log = "MyNewLog"
.EnableRaisingEvents = True
'
' Write a few entries.
'
.WriteEntry("Writing Error entry to event log", EventLogEntryType.Error)
.WriteEntry("Writing Information entry to event", EventLogEntryType.Information)
.WriteEntry("Writing Warning entry to event", EventLogEntryType.Warning)
'
' Output all events in the new log.
'
aLogEntries = .Entries()
For Each aLogEntry In aLogEntries
With aLogEntry
Console.WriteLine( _
"Source: {0}" & vbCrLf & _
"Category: {1}" & vbCrLf & _
"Message: {2}" & vbCrLf & _
"EntryType: {3}" & vbCrLf & _
"EventID: {4}" & vbCrLf & _
"UserName: {5}", _
.Source, .Category, .Message, .EntryType, .EventID, .UserName)
End With
Next
'
' Delete your new log.
'
.Clear()
.Delete("MyNewLog")
'
' Output the names of all logs on the system.
'
aEventLogList = .GetEventLogs()
For Each aLog In aEventLogList
Console.WriteLine("Log name: " & aLog.LogDisplayName)
Next
End With


Public Sub OnEntryWritten(ByVal source As Object, ByVal e As EntryWrittenEventArgs)
Console.WriteLine(("Written: " + e.Entry.Message))
End Sub

Using the Visual Studio .NET 2003 Debugger with ASP.NET Applications

the link goes here
http://msdn2.microsoft.com/en-us/library/aa290100(VS.71).aspx

Creating a Data Access Layer VS2005

Part1: http://www.asp.net/learn/data-access/tutorial-01-vb.aspx

Part2: http://msdn2.microsoft.com/en-us/library/aa581778.aspx

ASP.NET Products, Freeware and Shareware

The link:

http://www.deitel.com/ResourceCenters/Programming/ASPNET/ASPNETProductsFreewareandShareware/tabid/371/Default.aspx

good articles

link:

http://www.codeproject.com/dotnet/

Using IIS with VS 2005 and the new Web Project system

How does IIS map applications and sites

good article available at: http://weblogs.asp.net/scottgu/archive/2005/08/23/423409.aspx

Extract data from Websites.

I was'nt knowing that this can be done until I got the requirement to do this job.

good example for login in to rediff is here http://www.codeproject.com/useritems/kirangoka.asp

My code:

Imports mshtml
Imports System.Xml
Imports System.IO

Imports System.Data
Imports System.Data.SqlClient



Public Class getInspectorDetails

Const conStr As String = "Data Source=myservername1;Initial Catalog=Aslam_Test;User Id=sa; Password=mypass;"
'Dim conStrPostCode As String = "Data Source=myservername1;Initial Catalog=Common;User Id=sa; Password=mypassword2;"
Dim conn As SqlConnection = New SqlConnection(conStr)
Dim IntroducerName As String = ""

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
On Error Resume Next
Dim CertNum As String
Dim dr As SqlDataReader

If conn.State = ConnectionState.Closed Then
With conn
.Open()
End With
End If

Dim cmd As New SqlCommand
Dim sqlStr As String
cmd.Connection = conn
sqlStr = "Select distinct(Accreditation_Certification_Number) FROM [Aslam_Test].[dbo].[AssessorInspector]"
Accreditation_Certification_Number FROM InspectorDetails)"
sqlStr = sqlStr + " order by [Accreditation_Certification_Number]"

cmd.CommandText = sqlStr
cmd.CommandType = CommandType.Text
dr = cmd.ExecuteReader()

While dr.Read()
CloseAll()
CertNum = dr("Accreditation_Certification_Number")
Dim wbBrowser As New SHDocVw.InternetExplorer
'wbBrowser.Visible = True
wbBrowser.Visible = False
wbBrowser.Navigate("https://www.hcrregister.com/FindAssessorInspector", Nothing, Nothing, Nothing, Nothing)

Do
Loop Until Not wbBrowser.Busy

SearchForInspectorByCertNum(wbBrowser, CertNum)
ClickInspectorName(wbBrowser)
ReadAndRecordInspectorDetails(IntroducerName, CertNum)
wbBrowser.Quit()
wbBrowser = Nothing
End While

dr.Close()
dr = Nothing
cmd = Nothing

conn.Close()
conn = Nothing
MsgBox("Updated Database from HCR")
End Sub

Public Sub SearchForInspectorByCertNum(ByRef wbBrowser As SHDocVw.InternetExplorer, ByVal CertNum As String)


Dim HTMLDoc As mshtml.HTMLDocument

Do
Loop Until Not wbBrowser.Busy

HTMLDoc = wbBrowser.Document

Dim iHTMLCol As IHTMLElementCollection
Dim iHTMLEle As IHTMLElement
Dim str As String

iHTMLCol = HTMLDoc.getElementsByTagName("input")


' Type the user name in the username text box
For Each iHTMLEle In iHTMLCol
If Not iHTMLEle.getAttribute("name") Is Nothing Then
str = iHTMLEle.getAttribute("name").ToString
If str = "hiCertNumber" Then
iHTMLEle.setAttribute("value", CertNum)
Exit For
End If
End If
Next

' Press the submit button
For Each iHTMLEle In iHTMLCol
If Trim(iHTMLEle.outerHTML) = "" Then
iHTMLEle.click()
Exit For
End If
Next

Do
Loop Until Not wbBrowser.Busy
End Sub

'click on inspector link
Public Sub ClickInspectorName(ByRef wbBrowser As SHDocVw.InternetExplorer)

Dim HTMLDoc As mshtml.HTMLDocument

Do
Loop Until Not wbBrowser.Busy

HTMLDoc = wbBrowser.Document

Dim iHTMLCol As IHTMLElementCollection
iHTMLCol = HTMLDoc.links
iHTMLCol.item(13).click() 'click on link

IntroducerName = iHTMLCol.item(13).innerhtml

Do
Loop Until Not wbBrowser.Busy
End Sub

Public Sub ReadAndRecordInspectorDetails(ByVal name As String, ByVal CertNum As String)
On Error Resume Next
Dim wbBrowser As New SHDocVw.InternetExplorer

Dim winds As New SHDocVw.ShellWindows()
System.Threading.Thread.Sleep(2500)
Dim noIE As Boolean = False
For Each ie As SHDocVw.InternetExplorer In winds
If ie.MenuBar = False Then
'If ie.LocationURL = "https://www.hcrregister.com/FindAssessorInspector" And CInt(ie.Height) = 583 And CInt(ie.Width) = 512 Then
wbBrowser = ie
noIE = True
Exit For
'Else
' ie.Quit()
End If
Next

If noIE = True Then
Dim HTMLDoc As mshtml.HTMLDocument
Do
Loop Until Not wbBrowser.Busy

HTMLDoc = wbBrowser.Document
Dim iHTMLCol As IHTMLElementCollection 'for rows
Dim cell As IHTMLElement

iHTMLCol = HTMLDoc.getElementsByTagName("tr")

Dim i As HTMLTableRow
Dim iCountRow As Integer
Dim Inspectors(,) As String
ReDim Inspectors(iHTMLCol.length - 1, 4)

Dim tmpArr() As String

For Each i In iHTMLCol
cell = i.cells.item(0)
If Not i.cells.item(1) Is Nothing Then

If cell.innerText Is Nothing Then
Inspectors(iCountRow, 0) = ""
Else
tmpArr = cell.innerText.Split(". ")
Inspectors(iCountRow, 0) = Trim(tmpArr(tmpArr.Length - 1))
End If

cell = i.cells.item(1)
If cell.innerText Is Nothing Then
Inspectors(iCountRow, 1) = ""
Else
Inspectors(iCountRow, 1) = cell.innerText
End If
iCountRow = iCountRow + 1
End If
Next

If iCountRow > 0 Then
Dim connNew As SqlConnection = New SqlConnection(conStr)
If connNew.State = ConnectionState.Closed Then
With connNew
.Open()
End With
End If

Dim cmdInspectorInsert As New SqlCommand
cmdInspectorInsert.Connection = connNew
cmdInspectorInsert.CommandType = CommandType.StoredProcedure
cmdInspectorInsert.CommandText = "sp_InspectorDetails_save"
cmdInspectorInsert.Parameters.Clear()
cmdInspectorInsert.Parameters.Add("Name", SqlDbType.VarChar).Value = name
cmdInspectorInsert.Parameters.Add("Accreditation_Certification_Number", SqlDbType.VarChar).Value = Inspectors(0, 1)
cmdInspectorInsert.Parameters.Add("Status", SqlDbType.VarChar).Value = Inspectors(1, 1)
cmdInspectorInsert.Parameters.Add("Accreditation_Certification_Date", SqlDbType.DateTime).Value = Inspectors(2, 1)
cmdInspectorInsert.Parameters.Add("CompanyName", SqlDbType.VarChar).Value = Inspectors(3, 1)
cmdInspectorInsert.Parameters.Add("PostcodeCoverage", SqlDbType.VarChar).Value = Inspectors(4, 1)
cmdInspectorInsert.Parameters.Add("Qualifications", SqlDbType.VarChar).Value = Inspectors(5, 1)
cmdInspectorInsert.Parameters.Add("Specialisms", SqlDbType.VarChar).Value = Inspectors(6, 1)
cmdInspectorInsert.Parameters.Add("AddressLine1", SqlDbType.VarChar).Value = Inspectors(7, 1)
cmdInspectorInsert.Parameters.Add("AddressLine2", SqlDbType.VarChar).Value = Inspectors(8, 1)
cmdInspectorInsert.Parameters.Add("AddressLine3", SqlDbType.VarChar).Value = Inspectors(9, 1)
cmdInspectorInsert.Parameters.Add("PostTown", SqlDbType.VarChar).Value = Inspectors(10, 1)
cmdInspectorInsert.Parameters.Add("postcode", SqlDbType.VarChar).Value = Inspectors(11, 1)
cmdInspectorInsert.Parameters.Add("Website", SqlDbType.VarChar).Value = Inspectors(12, 1)
cmdInspectorInsert.Parameters.Add("Email", SqlDbType.VarChar).Value = Inspectors(13, 1)
cmdInspectorInsert.Parameters.Add("Telephone", SqlDbType.VarChar).Value = Inspectors(14, 1)
cmdInspectorInsert.Parameters.Add("Fax", SqlDbType.VarChar).Value = Inspectors(15, 1)
cmdInspectorInsert.ExecuteNonQuery()

cmdInspectorInsert = Nothing
connNew.Close()
connNew = Nothing
End If
Do
Loop Until Not wbBrowser.Busy

wbBrowser.Quit()
wbBrowser = Nothing
End If
End Sub


Public Sub CloseAll()
'Dim wbBrowser As New SHDocVw.InternetExplorer
'Dim winds As New SHDocVw.ShellWindows()
'For Each ie As SHDocVw.InternetExplorer In winds
' If ie.MenuBar = False Then
' ie.Quit()
' End If
'Next
End Sub


End Class

Creating a simple, threaded forum

AJAY Start here

A nice tutorial for AJAX
http://www.asp.net/ajax/documentation/live/default.aspx

Encryption/Decryption with Base64

Copied from http://addressof.com/blog/archive/2004/10/19/997.aspx


Private Function TripleDESEncode(ByVal value As String, ByVal key As String) As String
Dim des As New Security.Cryptography.TripleDESCryptoServiceProvider
des.IV = New Byte(7) {}
Dim pdb As New Security.Cryptography.PasswordDeriveBytes(key, New Byte(-1) {})
des.Key = pdb.CryptDeriveKey("RC2", "MD5", 128, New Byte(7) {})
Dim ms As New IO.MemoryStream((value.Length * 2) - 1)
Dim encStream As New Security.Cryptography.CryptoStream(ms, des.CreateEncryptor(), Security.Cryptography.CryptoStreamMode.Write)
Dim plainBytes As Byte() = Text.Encoding.UTF8.GetBytes(value)
encStream.Write(plainBytes, 0, plainBytes.Length)
encStream.FlushFinalBlock()
Dim encryptedBytes(CInt(ms.Length - 1)) As Byte
ms.Position = 0
ms.Read(encryptedBytes, 0, CInt(ms.Length))
encStream.Close()
Return Convert.ToBase64String(encryptedBytes)
End Function



Public Function TripleDESDecode(ByVal value As String, ByVal key As String) As String
Dim des As New Security.Cryptography.TripleDESCryptoServiceProvider
des.IV = New Byte(7) {}
Dim pdb As New Security.Cryptography.PasswordDeriveBytes(key, New Byte(-1) {})
des.Key = pdb.CryptDeriveKey("RC2", "MD5", 128, New Byte(7) {})
Dim encryptedBytes As Byte() = Convert.FromBase64String(value)
Dim ms As New IO.MemoryStream(value.Length)
Dim decStream As New Security.Cryptography.CryptoStream(ms, des.CreateDecryptor(), Security.Cryptography.CryptoStreamMode.Write)
decStream.Write(encryptedBytes, 0, encryptedBytes.Length)
decStream.FlushFinalBlock()
Dim plainBytes(CInt(ms.Length - 1)) As Byte
ms.Position = 0
ms.Read(plainBytes, 0, CInt(ms.Length))
decStream.Close()
Return Text.Encoding.UTF8.GetString(plainBytes)
End Function


TEST This from:
Sub Main()
Dim key As String = "a1B@c3D$"
Dim original As String = "This is a test, blah, blah, blah."
Console.WriteLine("Original" & vbCrLf & "-----------------")
Console.WriteLine(original & vbCrLf)
Dim encrypted As String = TripleDESEncode(original, key)
Console.WriteLine("Encrypted" & vbCrLf & "-----------------")
Console.WriteLine(encrypted & vbCrLf)
Dim decrypted As String = TripleDESDecode(encrypted, key)
Console.WriteLine("Decrypted" & vbCrLf & "-----------------")
Console.WriteLine(decrypted & vbCrLf)
Console.ReadLine()
End Sub