VB Desktop

Add This To Del.icio.us Share on Facebook StumbleUpon This Add to Technorati Digg This

Cn.open "provider=Microsoft.jet.oledb.4.0;data source=full path of database.mdb"

Getting dynamicly full paths:-

Cn.open "provider=Microsoft.jet.oledb.4.0;data source=" & app.path & ("\database.mdb")

Opening recordsource:-

a) open "table"

b) connection variable

c) adopen type

d) adlock type

syntax:-

rs.open "table_name",cn,adopendynamic,adlockpessimistik

1.EOF(Ending of field)

2.BOF(Beginning of Field)

1.EOF(Ending of field)

Syntax:-

Rs.movenext

If rs.eof=true then

Msgbox " "

Rs.movelast

Else

Call subprocedure

End if

2.BOF(Beginning of Field)

Rs.moveprevious

If rs.bof=true then

Msgbox " "

Rs.movefirst

Else

Call subprocedure

End if

Rs.addnew

Call [procedure]

Syntax for finding records:-

Rs.movefirst

Rs.find "columnname= ‘" & inputcontrol & "‘"

Call [equivalent to call data in front end]

Define new separate recordsets for combobox:-

Dim rscbo As New ADODB.Recordset

Adding new records in combo box:-

Syntax:-

Rscbo.open "table",cn,adopn,adlock

While not rscbo.eof=true

Combo.additem rscbo.fileds(index)

Rscbo.movenext

wend

rscbo.close

set rscbo=nothing

Finding data (records) from combo of textbox:-

a. You need to use the recordset of text box.

b. You need to use the click event of combobox.

c. You have to add data in combo box already.

d. You can use the "rs.find"method.

Example:-

Closing connection or recordsert objects:-

closing connection if opened:-

if cn.state=adstateopened then

cn.close

end if

or

if cn.state=adstateopened then cn.close

closing recordset if opened:-

if rs.state=adstateopened then

rs.close

end if

or

if rs.state=adstateopened then rs.close

Datagrid syntax:-

Rsdatagrid.cursorlocation=aduseclient

Rsdatagrid.open "table",cn,adopn,adlock

Set datagrid.datasource=rsdatagrid

Filter syntax:-

Rsdatagrid.filter="columnname=value"

Syntax:-

Variable="sql syntax"

Cn.execute variable

To create table :-

Create table [table name]

(fields name data type)

Example:-

Table:-security

Fields:-username varchar(100)

Password varchar(10)

Insert records:-

TO insert new records the following sql syntax is used:-

Sql synax:-

Insert into [table] values(‘value’,’value’)

Updating synatax:-

Update [table] set [fields,] where [referencesfields=][value]

Deleting syntax:-

Delete from [table] where [reference field=][value]

Syntax for all retrieval:-

Select * from [table]

For partial:-

Select [field,] from [table]

Arrangin order:-

Select * from [table] order by [field] asc/desc

Finding data of table through the any references value:-

Single:-

Rs.open "select [field,] from table where [reference field=][reference field],cn,adopen,adlock

Call procedure

Rangewise showing in data grid:-

Rs.open "select [field] from table where [field] between [value ] and [value]

Working with sql(by code):-

1)provider:-

Syntax:-

Cn.provider="sqloledb.1"

2)Connectionstring:-

Connectionstring caintains the following arguments:-

Arguments meaning:-

User id -valid user name

Password -valid password

Data source -name of the server i.e. computer's name where the server is running eg.cse01

Initial catalog -data base name that you are using and where you are connecting server.

Connection syntax:-

with cn

.provider="sqloledb.1"

.Connectionstring="user id=;password=;data source=;initial catalog="

.open

End with

Disconnection syntax:-

[connection code]

Cn.close

Set cn=nothing

Or

If cn.state=adstateopen then cn.close

[connection code]

Rs.open "select * from [table] ",cn,adopen,…

Call [procedure]

Connectiong server by windows authentication:-

Cn.open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=departmental;Data Source=CSECLASS"

Cursor movement:-

We can use the following cursor movement methods:-

1. move

2. movenext

3. moveprevious

4. movefirst

5. movelast

1. move:-

It moves the cursor to steped rows forward or backward.

Syntax:-

Forward to two rows steped.

Rs.move [number]

Call [procedure]

Backward to two rows steped.

Rs.move [-number]

Call [procedure]

2. movenext:-

It is used to move cursor forward.

Syntax:-

Rs.movenext

Call procedure

3.moveprevious:-

It is used to move cursor previous.

Syntax:-

Rs.moveprevious

Call procedure

4.movefirst:-

Used to move the cursor directly to the first rows.

Syntax:-

Rs.movefirst

Call procedure

5.movelast:-

Used to move the cursor directly to the last rows.

Syntax:-

Rs.movelast

Call procedure

Error Handling while cursor movement:-

(using while-----wend)

While movenext

Rs.movenext

While rs.eof

Msg "it is last"

Rs.movelast

Wend

Call [procedure]

While moveprevious:-

Rs.moveprevious

While rs.bof

Msg "sorry,it is first"

Rs.movefirst

Wend

Call [procedure]

Column showing syntax:-

Datareport.sections("section2").controls("label").caption="Column heading"

Value passing syntax:-

Datareport.sections("section1").controls("textbox").datafield=rs.fields(index).name

Function using syntax:-

Datareport.Sections("section5").Controls("Funct control").DataField = rs.Fields(index).Name

For Further Reading,

Software Developing