Visual Basic.net

Just some notes  

These are my personal notes that I use as a quick help in my work.
You are welcome to read them.

Contents of current page Top-level home page
 
Index  Java Internet Oracle Notes
Linux Basics Web Basics SQL Notes
Informatica Servlets Apache BkpRstore SQL*Plus
Teradata   LDAP Storage PL/SQL
Windows     Tables OEM
UML Lang.   Net8 Portal
SQL Server Python perl Performance OLAP
Vmware Visual Basic PHP/MySQL User Mgmt  
Git        
More technical pages here

Contents

 

Introduction

 

Just in case some things have changed, see the old page of notes for VB6.

vb.net IDE:

 

Statements

 

' comment
rem comment 		Comments
safdasfd _
asdf 		Continue line

Do [ { While | Until } cond ]
    Exit Do
Loop

Do
    Exit Do
Loop [ { While | Until } cond ]

 Two forms of do..loop
While cond
    ' no exit Do
Wend 		While
If cond Then ... [Else ...] Statement on one line
If cond Then
Elsif cond Then
Else
End If		  
For i=start_i to end_i [step n]
     Exit For
Next		  
For Each element In group
    ' variant for arrays, variant or object for collections
    Exit For
Next [element]		  
Call procedure(arg1, arg2)
procedure arg1, arg2 		Call procedure (note that parentheses are needed with "call"). Use empty parentheses after array parameter.
ChDir
ChDrive 		 

Open pathname For mode [Access access] [lock] As [#]filenumber [Len=reclength]
Width #filenumber, width

Lock [#]filenumber[, recordrange]
Unlock [#]filenumber[, recordrange]
Get [#]file_num, [recnumber], varname
Put [#]filenumber, [recnumber], varname
Input #filenumber, varlist
Write #filenumber, [outputlist] ' use Input and Write together
Print #filenumber, [outputlist]
Line Input #filenumber, varname
Seek [#]filenumber, position
Reset
Close #file_num

 Note: use Get and Put together
console.writeline(..)
MessageBox.Show()
a_var.ToString()		 Display on console
Message box for windows applications
Gives string representation
Date = ...
Time = ... 		Set date / time
Dim varname
ReDim [Preserve] varname(subscripts) [As type] [, varname(subscripts) [As type]] . . . 		 
[Public | Private] Enum name
     membername [= constantexpression]
     membername [= constantexpression]
End Enum 		 
[Private | Public] Type varname
     elementname [([subscripts])] As type
     [elementname [([subscripts])] As type] . . .
End Type 		 
Erase arraylist release memory

Error err_num

On Error GoTo line
On Error Resume Next
On Error GoTo 0

Resume [0]
Resume Next
Resume line

 
[Public] Event procedurename [(arglist)]  
[Public | Private | Friend] [Static] Function name [(arglist)]
    [As type]
    ...
    name = expression ' return value
    Exit Function ' Can be anywhere in function
End Function 		 
[Private | Public | Friend] [Static] Sub name [(arglist)]
     ...
    Exit Sub] [statements]
    ....
End Sub 		 
[Public | Private | Friend] [Static] Property Get name [(arglist)]
[As type]
    ... [statements]
    [name = expression]
    [Exit Property] [statements] [name = expression]
End Property 		 
[Public | Private | Friend] [Static] Property Let name ([arglist,] value)
    ...
    [Exit Property]
End Property 		 
[Public | Private | Friend] [Static] Property Set name ([arglist,] reference)
    ...
    [Exit Property]
End Property 		 
[Optional] [ByVal | ByRef] [ParamArray] varname[( )] [As type] [= defaultvalue] Argument list for functions and subs
GoSub line
...
line
   ...
Return 		 
Implements [InterfaceName | Class]  
[Let] varname = expression The "let" is not necessary

Load object
Unload object
Set objectvar = {[New] objectexpression | Nothing}

With object
     [statements]
End With

 
Mid(stringvar, start[, length]) = string  
Option Base {0 | 1} Default lower bound for arrays, at start of module
Option Compare {Binary | Text | Database}  
Option Explicit All variables must be explicitely defined
Option Private Module  
RaiseEvent eventname [(argumentlist)]  
Randomize [number]  
SavePicture picture, stringexpression  
SaveSetting appname, section, key, setting  
Select Case testexpression
Case expr, expr
    ...
[Case Else
    ... ]
End Select 		 
SendKeys string[, wait]  
Stop
Application.Exit() 		Stops execution
Exit application
Try
  ....
  Catch exc as System.NullReferenceException
    ' Handle a specific exception here
  Catch exc as Exception
    ' Handle other errors here.     ' Scope of exc is local to the block
  Finally
    ' This block is executed always, error or no error
End Try		  
Throw New Exception("This is a custom exception") Stops execution
Exit application

 

[Public | Private] Const constname [As type] = expression

[Public | Private] Declare Sub name Lib "libname" [Alias "aliasname"] [([arglist])]
[Public | Private] Declare Function name Lib "libname" [Alias "aliasname"] [([arglist])] [As type]

 

 

 

 

Operators

 

& Concatenation
+ - * /  
a \ b
a Mod b		 Division with integer result
Integer remainder
a ^ b a raised to power of b (a^2 is square)
AddressOf procedurename Address of a procedure
a Is b Returns true if a and b refer to the same object
Like  
Eqv  
Imp Logical implication
Mod  
And Or Xor  

 

 

Functions

Explicit conversions:

Format or Val functions (formats to string, and val gives number equivalent of string)

In .NET: something = system.convert.toInt32(somethingElse) where ToInt32 can be any data type.

Constants

 

Objects

The My namespace contains both .NET framework classes and the classes
The Me object corresponds to the current window.

Button
.text is the label
WebBrowser.Navigate(string)
Give URL to web browser object
SaveFileDialog.ShowDialog()
Show the save file dialog box. Cancel event needs no treatment. Put code in the FileOK event.
Attribute FileName gives the file name returned by the object
Computer.FileSystem.WriteAllText(filename, the text, true if append)
For writing to disk
Dim aWindow as new the_name_of_the_form()
Create an instance of the form. Do this at the top of the main form's class (just after the "public class form_name")
NotifyIcon
Common control that appears in the notification area in the bottom right of the screen. Add a context menu strip if needed (create first then attach in the properties of the notify icon).
Context menu strip
Edit items: smart tag > edit item. Add elements (they are in fact tool strip menu items).
form / me
event FormClosing: put code to handle closing or prevent closing. e.CloseReason give what triggered the event. To cancel, set e.Cancel=True.
method hide: sets visible to false
Property AcceptButton: put OK button
Property CancelButton: put the cancel button
Splash screen
Use the template for splash screens. Attach to the application using the application properties.
About box
Use the template. Attach by putting "aboutForm.ShowDialog()" in the appropriate menu item click event.
 

 

 

Database connections

Data source is what the application sees. The data source connects through a connection to the database. The data source is stored as an .xsd file.
Four objects: binding source, binding navigator, typed dataset, table adapter. Each combo also has a binding source and a table adapter.
The table adapters hold the queries that are used on the table. By default four queries are built in: insert, update, select and delete. Add query if needed. Example: search with " (t.col LIKE '%' + @A_label + '%')". Then add "FillBy" queries to the table adapter component of the form.

Combo box for reference tables: drag and drop the table from the "Data Source" window onto the combo.

 

Project Properties

Main page

Settings: allows storage of custom settings, such as username. The names of web services are kept here too. Get settings with "My.Settings.theNameOfTheSetting". Save settings with My.Settings.Save

 

Web Services

right-click on project, choose add web reference. VB then adds all the necessary files, including a proxy of the web service so that some off-line development work can be done. The data source is added too.

Drag the data set onto the form. A binding source and a binding navigator are added.

BackGroundWorker component: launches a separate thread so as to give the control back to the user. Program the DoWork event to call the web service:
Dim OneArg = e.Argument
Dim theWebService As New nameOfWebService.Service
e.Result = theWebService.aMethodOfWebService(arg as defined for web service)

Start the background worker with:
If Not TheBackgroundWorkerObject.IsBusy() then
    TheBackgroundWorkerObject.RunWorkerAsync(whatever args)
End If

 

Imports System
Imports System.Web.Services
Imports Microsoft.VisualBasic
Public Class the_name : Inherits WebService
Public Function <WebMethod()> a_function (strWhatEver as String) as
String
...
End Function
End Class

Render Blocks:
<% %>
<% Dim I As Integer
For I = 0 to 7 %>
<font size="<%=I%>"> Growing display </font> <br>
<% Next %>

All controls are mapped to one of the controls in the
"System.Web.UI.HtmlControls" namespace.
Non-standard controls map to "System.Web.UI.HtmlControls.HtmlGenericControl"
List of controls:
http://samples.gotdotnet.com/quickstart/aspplus/doc/webcontrolsref.aspx

 

 

Classes

 

DateTime Class

If possible, use the System.DateTime data type which allows interoperation with the .NET framework.

The Date type (in VB) or System.DateTime (in .NET) contains IEEE (8-byte) values that represent dates from year 0001 to 9999.

The date object without the constructor new contains the current date and time.

The two static methods are UTC and parse.

Create a date with (hours, minutes, seconds and milliseconds are optional):
Dim MyDate As New System.DateTime(2007, 2, 13, 10, 32, 0, 0)
Then display it with a format:
MyString = MyDate.ToString("dd/MM/yyyy gg HH:mm:ss (zzz)")
The previous line is equivalent to:
MyString = Format(MyDate, "dd/MM/yyyy gg HH:mm:ss (zzz)")
Get the current time and date:
Dim theMoment as DateTime = Now()

Custom date formats:

Differing options refer to the format with/without leading zeros or abbreviated/full names

The use of the date formats requires: Imports System.Globalization.

See the TimeSpan object for representing positive and negative time spans. See the calendar class for handling Gregorian and other calendars.

Pre-defined format names for the format function:

VB Script constants (also for .NET?):

Use the methods System.DateTime.FromOADate and System.DateTime.ToOADate to convert to or from OLE Automation dates.

Example of the use of parsing:
Imports System.Globalization
Dim MyCultureInfo As CultureInfo = new CultureInfo("de-DE")
Dim MyString As String = "12 April 2006"
Dim MyDateTime As DateTime = DateTime.Parse(MyString, MyCultureInfo)
Console.WriteLine(MyDateTime)
The ParseExact method allows specification of one and only of the possible formats (see list above).

 

Time Span

Sample code:
dim aTimeSpan as TimeSpan = New TimeSpan(3, 12, 0, 0) ' 3 days, 12 hours
aTimeSpan = aDatetime.Subtract(anotherDateTime)

Sample timing operation:
dim StartTime, EndTime as DateTime
dim theDuration as TimeSpan
StartTime = Now()
.....
EndTime = Now()
theDuration = EndTime - StartTime
Debug.Write("Duration is " & theDuration.duration )

 

Winsock Object

Examples

tcpServer.Protocol = sckTCPProtocol ' Set the protocol

tcpServer.LocalPort = 1001 ' Set local port
tcpServer.Listen ' Now wait

Private Sub tcpServer_ConnectionRequest _ (ByVal requestID As Long) ' Event ConnectionRequest
  If tcpServer.State <> sckClosed Then tcpServer.Close
  ' Close previous connection first
  tcpServer.Accept requestID
End Sub

tcpClient.RemoteHost = "the remote computer name"
tcpClient.RemotePort = 1001
tcpClient.Connect ' Initiate a connection.

tcpServerOrClient.SendData "a text"

Private Sub tcpServerOrClient_DataArrival (ByVal bytesTotal As Long) ' Event DataArrival
  Dim strData As String
  tcpServerOrClient.GetData strData
End Sub

Handle multiple connection requests:

intMax = 0
sckServer(0).LocalPort = 1001
sckServer(0).Listen

Private Sub sckServer_ConnectionRequest (Index As Integer, ByVal requestID As Long)
    If Index = 0 Then
        intMax = intMax + 1
        Load sckServer(intMax)
        sckServer(intMax).LocalPort = 0
        sckServer(intMax).Accept requestID
    End If
End Sub

 

Properties

object.BytesReceived
amount of data received, use GetData to read data
object.LocalHostName
Returns local host (read-only)
object.LocalIP
Returns local IP (read-only)
object.LocalPort = long
Port where client sends and server listens; 0 --> dynamically set
object.Protocol n
Use sckTCPProtocol (0) or sckUDPProtocol (1)
object.RemoteHost = string
Name "FTP://ftp.server.com," or an IP address "a.b.c.d"
object.RemoteHostIP
IP of remote machine
client: returns IP after connecting
server: IP of initiating computer
object.RemotePort = port
Default values exist depending on protocol
object.SocketHandle
Socket handle, read-only
object.State
sckClosed (0) --> Closed
sckOpen (1) --> Open
sckListening (2) --> Listening
sckConnectionPending (3) --> Connection pending
sckResolvingHost (4) --> Resolving host
sckHostResolved (5) --> Host resolved
sckConnecting (6) --> Connecting
sckConnected (7) --> Connected
sckClosing (8) --> Peer is closing the connection
sckError (9) --> Error
object.Tag [= expression]
A string

Methods

Winsock.Accept requestID
Accept a new connection (TCP); use with ConnectionRequest event
Close beforehand (requestID As Long)
 
object.Bind LocalPort, LocalIP
Specifications before listening (TCP)
object.Close
Close connection
object.GetData (format)
Returns a graphic
object.GetData data, [type,] [maxLen]
object.PeekData data, [type,] [maxLen]
Use with the DataArrival event; put maxLen the same as totalbytes; data is a variant or appropriately defined variable
Type is vbByte, vbInteger, vbLong, vbSingle, vbDouble, vbCurrency, vbDate, vbBoolean, vbError, vbString, vbArray + vbByte (Byte Array)
PeekData does not remove from the data queue
object.SendData data
Data to be sent. For binary data, byte array should be used.

Events

Close
Computer closed the connection
Connect()
A connection has been made; ErrorOccured is true if there is an error
Object_ConnectionRequest (requestID As Long)
Use the accept method with the requestID
object_DataArrival (bytesTotal As Long)
Data has arrived; use GetData method.
It is also possible to use ReceivedData to know if there is data.
object_Error(number As Integer, Description As String, Scode As Long, Source As String,
HelpFile as String, HelpContext As Long, CancelDisplay As Boolean)
Set CancelDisplay to true for not displaying the default message box.
object_SendComplete
When transmission is complete
object_SendProgress (bytesSent As Long, bytesRemaining As Long)
Show progress
 

 

Tricks for VB Access

 

Confirmation of each change to the data (deletion or insertion):
menu tools > options > tab edit/find > confirm: record changes, document deletions and action queries

At the beginning of each module
Option Compare Database 'Utilise l'ordre de la base pour les comparaisons de chaînes
Option Explicit

Minimum for a sub
Private Sub whatever(params)
   On Error GoTo whateverError
   ...
whateverExit:
   Exit Sub
whateverError:
   msgbox "Error number " & err & " in sub-routine 'whatever'" & Chr$(13) & Error(err)
   ' undo or rollback here
   Resume whateverExit
End Sub

Code for opening a form with parameter
Private Sub xyz_Click()
   Dim stDocName As String
   Dim stLinkCriteria As String
   stDocName = "a_form_name"
   stLinkCriteria = "[AccountNo]=" & Me![AccountNo]
   DoCmd.OpenForm stDocName, A_NORMAL, A_EDIT , stLinkCriteria
End Sub

Private Sub SEARCH_Enter()
or: Private Sub SEARCH_LostFocus()
  DocName = "Filtre de recherche"
  DoCmd.OpenQuery DocName, A_NORMAL, A_EDIT
End Sub

Prompt user to confirm changes
Called Sub CheckUnsavedData() in Form_BeforeUpdate() and Form_Unload()
' Use this with an OK button
Private AskToSaveRecord As Boolean
' Put AskToSaveRecord = True in the Form_Current and save events
' Put AskToSaveRecord = False in the cmdOK_Click event so that the user is not prompted
' However, in some of my notes, I do NOT use the variable AskToSaveRecord.

Private Sub CheckUnsavedData(theForm as form)
   ' AskToSaveRecord is set to no when user clicks on OK, otherwise yes. Don't use if no OK button
   ...
   If theform.Dirty And AskToSaveRecord Then
     If vbNo = MsgBox("The data has changed" & Chr$(13) & "Do you want to save the changes?", vbYesNo) Then
       theform.Undo ' Undo the changes here
     End If
   End If
   AskToSaveRecord = True
   ...
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
  Call CheckUnsavedData(me)
End Sub

Private Sub Form_Unload(Cancel As Integer)
  Call CheckUnsavedData(me)
End Sub

Private Sub fieldName_AfterUpdate()
   Forms!xyz.Refresh
End Sub

Private Sub Form_Close()
   DoCmd.Quit
End Sub

Private Sub Form_Open(Cancel As Integer)
   Forms!Accounts.Caption = "the accounts"
   Forms!Accounts!Abc.Form.Filter = "a condition"
End Sub

Put this in general module
Public Sub ErrMsg(TheError, SubName As String)
  Dim ErrorLine1$, ErrorLine2$
  Dim ErrNum As Long

  If IsNumeric(TheError) Then
    'ErrNum = TheError

    ErrorLine1$ = "Error number " & TheError & " in sub-routine '" & SubName & "'"
    ErrorLine2$ = Error(TheError) 'Error$ ' The error number is ignored because of a bug.
    ' Take the current error
    ' or use object err: err.description
   Else
    ErrorLine1$ = "Custom error in sub-routine '" & SubName & "'"
    ErrorLine2$ = TheError
  End If

  If 0 < Len(ErrorLine1$) + Len(ErrorLine2$) Then MsgBox ErrorLine1$ & Chr$(13) & ErrorLine2$

End Sub

 

DoCmd.Close
Close window, put in "cancel" button
DoCmd.Quit
Quit
DoCmd.GoToControl "a_control"
Set focus on a control

 

 

 

 

 

Basics

Execute immediate: create a module with
Option Compare Database
Sub a_sub()
DoCmd.RunSQL "update table set ....;"
End Sub
Then do F5 or Run. Optionally compile beforehand (menu debug > compile)

 

Option Compare Database
Option Explicit

Private Sub Command53_Click()
On Error GoTo Err_Command53_Click
   DoCmd.GoToRecord , , acFirst
Exit_Command53_Click:
   Exit Sub
Err_Command53_Click:
   MsgBox Err.Description
   Resume Exit_Command53_Click
End Sub

' commands
DoCmd.GoToRecord , , acFirst ' goto first
DoCmd.GoToRecord , , acPrevious
DoCmd.GoToRecord , , acNext
DoCmd.GoToRecord , , acLast
DoCmd.Close
DoCmd.GoToRecord , , acNewRec
DoCmd.PrintOut

' Delete:
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70


' Search :
Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

' Also:
Private Sub Descriptor_DblClick(Cancel As Integer)
  DoCmd.OpenForm "tbl_descriptors"
End Sub

Forms

' open a form
   DoCmd.OpenForm "Maintenanceform", , , , , acNormal
' or
   Dim stDocName As String
   Dim stLinkCriteria As String
   stDocName = "lkp_Descriptors"
   DoCmd.OpenForm stDocName, , , stLinkCriteria
' open a report
   DoCmd.OpenReport stDocName, acViewPreview

Private Sub Form_Activate()
  DoCmd.Maximize
  DoCmd.ShowToolbar "Reference", acToolbarYes
End Sub

Private Sub Form_Close()
  DoCmd.ShowToolbar "Reference", acToolbarNo
End Sub

 

updates

Private Sub control_BeforeUpdate(Cancel As Integer)
  asdf
  If intCountDuplicate > 0 Then
     MsgBox " Found a duplicate record"
  End If
End Sub

Private Sub Control_LostFocus()
  asdf
End Sub

misc

DoCmd.Hourglass True

' Build search query
   If Not IsNull(Me!cboCountry) Then
     strFilter = strFilter & " Country = '" & Me!cboCountry & "' AND"
   End If
   If Me!ExpertisePolicy = True Then
     strFilter = strFilter & " ExpertisePolicy = True AND"
   End If

  ' fill a list with the results of a search
   strFilter = strFilter & " " & mcmdSort.ControlTipText ' Add ORDER BY clause
   Me!lstOrder.RowSource = mcstrSQL & strFilter ' Put SQL in the list box
   Me!lstOrder.Requery
   DoCmd.Hourglass False

   If Not IsNull(Me!lstOrder) Then
     Me!lstOrder.Tag = Me!lstOrder.Column(0)
     DoCmd.OpenForm "ViewContact"
   End If

  ' clear:
   Me![cboGeoExp] = Null
   Me![ExpertisePolicy] = False

  ' what is this
   mcmdSort.FontWeight = mcintNormal
   Set mcmdSort = Me!cmdLocation
   mcmdSort.FontWeight = mcintBold
   Call cbfRequery ' Rebuild the list

 

 

expression builder for validation rule: to see fields, first save text.

Sort reports in "Sorting and Grouping"

Functions

trim(str)
left(str,3)

N-to-M relationship

link table

Primary key is made of both foreign keys

Combo

Displays options from a reference table TR. The data is stored in the main table MT.

control source main-table.id
Row source type Table/Query
Row source SELECT id, label FROM TR; order by id
Bound column 1
Limit to list Yes
column count 2
Column widths 0;3cm (trick to hide the ID)

Sub-form

On the form is a combo-box, as described above

Default view continuous forms
data source Link form to the link table
(the table representing the m-to-n relationship)
order by id of TR
Record Selectors yes
Navigation Buttons no
   

Main form

Add a child form. Link child field id1 with master field id1.

 

Integrate these notes:


prototype for M-n relationship:
Create a sub-form (wizard, columnar view) on the table representing the m-n relationship

this is correct:
Create a sub-form (wizard, autoformat-datasheet) on the table representing the m-n relationship When inserting sub-form, choose "use an existing form" and for linked fields, choose "define my own"

enforce Referential integrity for the m-to-n relationships: "cascade delete related records" for other relationships, do NOT cascade delete

for BackgroundInformation, no referential integrity is enforced.
What if records in the main table are deleted?
Test this.

Integrity error when inserting: set the default values for columns with lookups to null or to a valid value in the lookup table

 

 

date() --> current date

docmd.requery

Handle unsaved data
(call before leaving the form)

Public Sub CheckUnsavedData(theForm As Form)
' get user confirmation for changes
    On Error GoTo CheckUnsavedDataError
    If theForm.Dirty Then
        If vbNo = MsgBox("The data has changed" & Chr$(13) & "Do you want to save the changes?", vbYesNo) Then
            theF orm.Undo ' Undo the changes here
        End If
    End If
    Exit Sub
    
CheckUnsavedDataError:
    MsgBox "Error number " & Err & " in sub-routine 'CheckUnsavedData'" & Chr$(13) & Error(Err)
    Exit Sub
End Sub

 

 

VBA for Word and Excel

To add an object on the page, first open VB editor, then go into design mode "menu Run > design mode" and return to the document.

To manage security for macros in Word: menu tools > macro > security. Set security to medium so that macros can be enabled.

Object Properties

Autotab
If autotab false and TabKeyBehavior true, user can insert tabs with tab key
Enabled
If false, object is dimmed
Locked
If true, the object cannot be modified by the user, only by code
Multiline
If true, allow several lines
SpecialEffect
0-fmSpecialEffectFlat --> flat
Autosize
Grows with the size of the text (add space at beginning and end)
SelectionMargin
Set to false in locked textbox

 

Insert a date-time in current cell. Define a macro that uses this procedure.
Sub InsertDateInCurrentCell()
    ActiveCell.Value = Now
End Sub

 

Miscellaneous

 

 

Source Safe

Use "lock-modify-unlock" mode (only one user can change one file at time) --> minimizes the need for merging files.

Check in a copy of all files at the end of the day, but they must compile.

Use labels with version information and comments. Implement keyword expansion in SourceSafe and add the appropriate comments to all SQL scripts.

Label projects with version name --> allows identification of projects and files for particular milestones.

Encrypt in the root folder using EFS. Remove permissions from the Everyone user for these folders, and assign specific permission to SourceSafe users or groups.

Problems and Solutions