Technical tutorials

Saturday, 30 May 2020

VBScript: How to use Dictionary in VBScript | VBScript objects

=>To declare and create object of a vbscript dictionary:

Dim objDictionary
Set objDictionary = CreateObject("Scripting.Dictionary")

=>To add key and items into Dictionary

objDictionary.Add "1stKey", "Apple"
objDictionary.Add "2ndKey", "Ball"

=>To get count of items in dictionary

count= objDictionary.Count

=>To retrieve a item value from VBDictionary using key

msgbox objDictionary.item("1stkey")

=>To remove item use this below method

objDictionary.Remove("1stKey")

=>To remove all items or erase contents of vbDictionary

objDictionary.RemoveAll()

VBScript : How to add controls on to the HTA form and How to change a textbox value in vbscript

To add a label box and text box in to the HTA form:

<body>
<h1>Heading</h1>
<form action="" method="">
<p> This is a label box and text box section </p>
<label for="UserName">UserName:</label>
<input type="text" value="Alex" size="30" id="UserName" name="UserName">
</form>
</body>

To add a button control /submit button

<label for="ClickButton">Click on this button</label> <input type="button" id="ClickButton" name="ClickButton" value="Click Button">

How to change a textbox value in a vbscript in HTA applications:

<script language="VBScript">
Document.getelementbyID("Textbox").innertext = "New value of text box"
</script>

This script will update/change the textbox value to new value feeded

To add a combo box in to the HTA form:

<label for="Lblappname">Application Name</label>

<select id="testcombobox" name="testcombobox">
<option value="---Select Application name---">---Select Application name---</option>
<script Language="VBScript">
'FindApplicationName()
</script>

</select>

Saturday, 23 May 2020

VBScript: HTA Code to Fetch recordset values and fill to a combo box

VBScript Help: Create recordset and loop to fetch values:

<script language="VBScript">

'======================
' Function - FetchSubscriberName
'======================
Function FetchSubscriberName
strSQL = ""
strSQL = strSQL & "SELECT distinct sub_name as sub_name FROM testsubscription
Set recsetApp = RunSQL(strSQL)
With recsetApp
Do While Not recsetApp.EOF
msgbox recsetApp.Fields("sub_name").Value
recsetApp.MoveNext
Loop
End with
End Function

</script>

VBScript Help: Create recordset and fill to a combox box

<script language="VBScript">

'======================

' Function - FetchSubscriberName
'======================

Function FetchSubscriberName

strSQL = ""

strSQL = strSQL & "SELECT distinct sub_name as sub_name FROM testsubscription where event_name = '" & combobox2.value & "'"

Set recsetApp = RunSQL(strSQL)
With recsetApp

Set oDD = document.getelementbyID("Subname")

Do While Not recsetApp.EOF

Set oOpt = oDD.document.createElement("option")

oOpt.Text = recsetApp.Fields("sub_name").Value

oOpt.Value = oOpt.Text

oDD.Options.Add oOpt

'msgbox recsetApp.Fields("sub_name").Value
recsetApp.MoveNext
Loop

End with
End Function

</script>

Wednesday, 4 March 2020

SQL Server- Restore database scripts to handle multiple logical files..primary files, data files and index files

When our database backup has multiple logical files and physical file names like mdf,ndf and ldf files, our normal restore backup query will not work..to restore files from those backups we need to write restore query to handle the move logic dynamically, this code below works magically.

DECLARE @drivelocation varchar(25)
DECLARE @directoryname varchar(2000)
DECLARE @databasename as varchar(128)

SET @drivelocation= "D:/Test"
SET @directoryname = @drivelocation + '\New' + '.bak';

SET @databasename = 'NewDatabase'

IF (EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = @databasename))
BEGIN
EXEC('DROP DATABASE ' + @databasename)
END
-- Create a table to hold the logical names of the source database we are restoring from
CREATE TABLE #t
(id int identity,
LogicalName VARCHAR(128)
,PhysicalName VARCHAR(260)
,Type CHAR(1)
,FileGroupName NVARCHAR(128)
,Size NUMERIC(20,0)
,MaxSize NUMERIC(20,0)
,FileID BIGINT
,CreateLSN NUMERIC(25,0)
,DropLSN NUMERIC(25,0) NULL
,UniqueID UNIQUEIDENTIFIER
,ReadOnlyLSN NUMERIC(25,0) NULL
,ReadWriteLSN NUMERIC(25,0) NULL
,BackupSizeInBytes BIGINT
,SourceBlockSize INT
,FileGroupID INT
,LogGroupGUID UNIQUEIDENTIFIER NULL
,DifferentialBaseLSN NUMERIC(25,0) NULL
,DifferentialBaseGUID UNIQUEIDENTIFIER
,IsReadOnly BIT
,IsPresent BIT
,TDEThumbprint CHAR(1)
)

DECLARE @stmt nVARCHAR(1024)
SET @stmt = 'RESTORE FILELISTONLY FROM DISK=' + '''' + @directoryname + ''''
INSERT INTO #t EXEC(@stmt)

print @stmt

DECLARE @id int;
DECLARE @Max int;
DECLARE @string1 varchar(max);
DECLARE @logicalname as varchar(128);
DECLARE @physicalname as varchar(128);

SET @id = 0
SET @string1=''
SELECT @Max = max(id) from #t

WHILE @id < @Max
BEGIN
SET @id = @id + 1
IF @id =1 and @id <@max
SET @logicalname='';
SET @physicalname='';

SELECT @logicalname=logicalname,@physicalname=physicalname FROM #t WHERE id=@id
IF CHARINDEX('mdf',@physicalname,1)> 0
SET @physicalname = @drivelocation+'EHI_ExportDir\Data_dev\' +@databasename + '.mdf'
ELSE IF CHARINDEX('ndf',@physicalname,1)> 0
SET @physicalname = @drivelocation+'EHI_ExportDir\Data_dev\' +@databasename + '.ndf'
ELSE IF CHARINDEX('ldf',@physicalname,1)> 0
SET @physicalname = @drivelocation+'EHI_ExportDir\Log_dev\' +@databasename + '.ldf'

IF @id =1
SET @string1 = @string1 + ' WITH MOVE ' + '''' + @logicalname + '''' + ' TO ' + '''' + @physicalname + '''' + ','
ELSE
SET @string1 = @string1 + ' MOVE ' + '''' + @logicalname + '''' + ' TO ' + '''' + @physicalname + '''' + ','

END

print @string1

SET @stmt = 'USE MASTER;' + 'RESTORE DATABASE ' + @databasename +
' FROM DISK = N''' + @directoryname + '''' + @string1 +
' REPLACE, FILE = 1, NOUNLOAD'
print (@stmt)
drop table #t
print (@stmt)

Tuesday, 3 March 2020

VB6 Program to read and print into a text file using freefile

Program to read and print into a text file

Dim sFileText as String
Dim iFileNo as Integer

iFileNo = FreeFile

'open the file for writing
Open "C:\Test.txt" For Output As #iFileNo
'please note, if this file already exists it will be overwritten!

'write some example text to the file
Print #iFileNo, "first line of text"
Print #iFileNo, " second line of text"
Print #iFileNo, "" 'blank line
Print #iFileNo, "some more text!"

'close the file (if you dont do this, you wont be able to open it again!)

Close #iFileNo

VB6 Program to read/write text file using filesystemobject

Program to read/write text file using filesystemobject

Private Sub Command2_Click()
'read text file and print

Dim fs As FileSystemObject
Dim ts As TextStream

Set fs = New FileSystemObject
If fs.FileExists("C:\Users\Desktop\text1.txt") Then
Set ts = fs.OpenTextFile("C:\Users\Desktop\text1.txt", ForReading, True)
Do While Not ts.AtEndOfStream
strnewlines = strnewlines & ts.ReadLine & vbCrLf

Loop
ts.Close
End If

'for writing into text file
Set ts = fs.OpenTextFile("C:\Users\Desktop\text2.txt", ForWriting, True)
ts.Write strnewlines

End Sub

VB6 Program to connect to access database

VB6 Code to connect to access database

Dim connection1 As ADODB.Connection
Set connection1 = CreateObject("ADODB.Connection")

'connection string
constring = "provider=microsoft.jet.oledb.4.0;data source=C:\Users\Desktop\vb pgms\Database1.mdb"

connection1.Open constring

MsgBox connection1.State

Technical tutorials

Saturday, 30 May 2020

VBScript: How to use Dictionary in VBScript | VBScript objects

VBScript : How to add controls on to the HTA form and How to change a textbox value in vbscript

Saturday, 23 May 2020

VBScript: HTA Code to Fetch recordset values and fill to a combo box

Wednesday, 4 March 2020

SQL Server- Restore database scripts to handle multiple logical files..primary files, data files and index files

Tuesday, 3 March 2020

VB6 Program to read and print into a text file using freefile

VB6 Program to read/write text file using filesystemobject

VB6 Program to connect to access database

VBScript: How to use Dictionary in VBScript | VBScript objects

Report Abuse

Labels