VB 2010 Advantage Database Example

Sybase Advantage Database With Visual Basic .Net 2010

Update Sybase Advantage Database using XML file from Visual Basic 2010 [Client-Server] Idea.

1. Create New Visual Basic 2010 WinForm Application Project

2. Path to Project (I.e. D:\MySyBase\)

3. Path to Database (I.e. D:\MySyBase\Bin\Debug\)

4. Sybase Advantage Database (MyDB.add)

5. Sybase Advantage Database Username : AdsSys - Password : ahmed

6. Sybase Advantage Database Table (I.e. MyInfo.adt)

We will create a personal Info application, so the table structure will be like that :

Visual Basic 2010 - Sybase Advantage Database

Note : All other fields will vary in [Size] only but all fields are [cicharacter] Data Type.

Visual Basic Form Design :

Visual Basic Online Courses

Note : I have made TextBoxes with Naming convention (I.e NameTxt, EmailTxt, MobileTxt .... etc). Also I've placed 1 DataGridView and some Buttons. I also have 1 Module [Module1].

SaveBtn : Save new record to the Advantage Database

SearchBtn : Search for records using by Sname

EditBtn : After search, edits the record

DeleteBtn : After search, deletes the record

CancelBtn : Reset the form and the DataGridView

ExitBtn : Exits the application

In order to use Sybase Advantage Database with Visual Basic 2010 and just like any other Database, you must call the References Advantage.Net Data Provider.

Update for Visual Studio 2015 : Download Advantage Data Provider 11.10.30

The installation file dataprovider.exe, installs :

1. Advantage CA-Clipper RDDs
2. Advantage CA-Visual Objects RDDs
3. Advantage Client Engine API
4. Advantage Crystal Reports Driver
5. Advantage Data Architect
6. Advantage JDBC Driver
7. Advantage .NET Data Provider
8. Advantage ODBC Driver
9. Advantage OLE DB Provider (for ADO)
10. Advantage TDataSet Descendant
11. Advantage Delphi Components
12. Advantage Web Platform
13. Advantage Python Interface
14. Advantage Rudy Interface
15. Advantage Perl DBI Driver
16. Advantage PHP Extension

Visual Basic 2010 - Add Reference to Sybase Advantage Database

The Codes
Module1

	'Visual Basic Online Courses
	'Sybase Advantage Database
	'Codes for Module1
	Imports Advantage.Data.Provider
	Module Module1
	Public CN As New AdsConnection
	Public CMD As New AdsCommand
	Public ADB As New AdsDataAdapter
	Public ATB As New DataTable
	Public DBS As New DataSet
	Public DR As AdsDataReader
	Public SqlStr, MyConStr, SearchX As String
	Public Sub ConnMe()
	Try
	MyConStr = ("Data Source=" & Application.StartupPath & "\MyDB.Add; Advantage Server Type=ADS_LOCAL_SERVER;" & _
	"TrimTrailingSpaces = true;")
	CN.ConnectionString = MyConStr
	CN.Open()
	Catch EX As Exception
	MsgBox(EX.Message)
	End Try
	End Sub
	Public Sub DGVConn(MySqlStr1 As String, _
	DataGrid As DataGridView, _
	MyCmd As AdsCommand, _
	MyDr As AdsDataReader, _
	MyAdt As DataTable)
	Dim MyCmd1 As New AdsCommand
	Dim MyTable1 As New DataTable
	Dim MyDr1 As AdsDataReader
	With MyCmd1
	.Connection = CN
	.CommandType = CommandType.Text
	.CommandText = SqlStr
	End With
	MyDr1 = MyCmd1.ExecuteReader
	Dim I As Integer
	For I = 0 To MyTable1.Rows.Count - 1
	MyDr1.Read()
	Next
	MyTable1.Load(MyDr1)
	DataGrid.DataSource = MyTable1
	Dim L As Integer = DataGrid.Columns.Count
	With DataGrid
	For I = 0 To L - 1
	DataGrid.Columns(I).Width = DataGrid.Width / L
	Next
	End With
	MyDr1.Close()
	End Sub
	End Module

view raw gistfile1.vb hosted with ❤ by GitHub

Form1
Sub ClearAll()

	'Sub ClearAll
	'Visual Basic Online Courses
	'Sybase Advantage Database
	Public Class Form1
	Dim Ctrl As Control
	Sub clearall()
	For Each Ctrl In Me.Controls
	If TypeOf Ctrl Is TextBox Then
	Ctrl.Text = Trim("")
	End If
	Next
	SqlStr = Trim("")
	SqlStr = ("Select * From MyInfo")
	With CMD
	.Connection = CN
	.CommandType = CommandType.Text
	.CommandText = SqlStr
	End With
	DR = CMD.ExecuteReader
	DGVConn(SqlStr, DGV, CMD, DR, ATB)
	DR.Close()
	MsgTxt.ForeColor = Color.Green
	EditBtn.Enabled = False
	DeleteBtn.Enabled = False
	SaveBtn.Enabled = True
	End Sub

view raw gistfile1.vb hosted with ❤ by GitHub

Private Sub Form_Load()

	'Visual Basic 2010 Online Course
	'facebook.com/visual.basic.language
	'Form_load
	Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
	ConnMe()
	If CN.State = ConnectionState.Open Then
	Me.Enabled = True
	DBState.Image = Image.FromFile(Application.StartupPath & "\On.Png")
	DBState.Text = ("Database Connected")
	SqlStr = ("Select * From MyInfo")
	DGVConn(SqlStr, DGV, CMD, DR, ATB)
	DGV.Columns(0).HeaderText = ("Name")
	DGV.Columns(1).HeaderText = ("E-mail")
	DGV.Columns(2).HeaderText = ("Mobile")
	DGV.Columns(3).HeaderText = ("Country")
	DGV.Columns(4).HeaderText = ("City")
	Else
	Me.Enabled = False
	DBState.Image = Image.FromFile(Application.StartupPath & "\Off.Png")
	DBState.Text = ("Database Connected")
	End If
	End Sub

view raw gistfile1.vb hosted with ❤ by GitHub

Private Sub ExitBtn_Click()

	'Visual Basic 2010 Online Courses
	'Sybase
	Private Sub ExitBtn_Click(sender As System.Object, e As System.EventArgs) Handles ExitBtn.Click
	'exit
	End
	End Sub

view raw gistfile1.vb hosted with ❤ by GitHub

Private Sub SaveBtn_Click()

	'VB 2010 Online Course
	'Sybase Advantage
	Private Sub SaveBtn_Click(sender As System.Object, e As System.EventArgs) Handles SaveBtn.Click
	Dim Ctrl1 As Control
	For Each Ctrl1 In Me.Controls
	If TypeOf Ctrl1 Is TextBox And Ctrl1.Text = Trim("") Then
	MsgTxt.ForeColor = Color.Red
	MsgTxt.Text = ("All Fields required")
	Exit Sub
	End If
	Next
	SqlStr = Trim("")
	SqlStr = "Insert INTO MyInfo(Sname,Semail,Smobile,Scountry,Scity)"
	SqlStr = SqlStr + " VALUES ('" & NameTxt.Text.Trim & "','" & EmailTxt.Text.Trim & "','" & MobileTxt.Text.Trim & "', "
	SqlStr = SqlStr + "'" & CountryTxt.Text.Trim & "','" & CityTxt.Text.Trim & "')"
	With CMD
	.Connection = CN
	.CommandType = CommandType.Text
	.CommandText = SqlStr
	End With
	Dim N As Integer = CMD.ExecuteNonQuery
	MsgTxt.ForeColor = Color.Green
	MsgTxt.Text = (N & " Field(s) Added Successfully")
	clearall()
	End Sub

view raw gistfile1.vb hosted with ❤ by GitHub

Private Sub SearchBtn_Click()

	'Visual Basic 2010 Online Courses
	'www.evry1.net
	Private Sub SearchBtn_Click(sender As System.Object, e As System.EventArgs) Handles SearchBtn.Click
	'search
	SearchX = InputBox("Enter Name")
	If String.IsNullOrEmpty(SearchX) = True Then
	MsgTxt.Text = ("Provide Name")
	Exit Sub
	End If
	SqlStr = Trim("")
	SqlStr = "Select * from MyInfo Where Sname = '" & SearchX & "'"
	With CMD
	.Connection = CN
	.CommandType = CommandType.Text
	.CommandText = SqlStr
	End With
	DR = CMD.ExecuteReader
	DR.Read()
	If DR.HasRows = False Then
	MsgTxt.ForeColor = Color.Red
	MsgTxt.Text = ("Not found")
	DR.Close()
	EditBtn.Enabled = False
	DeleteBtn.Enabled = False
	Exit Sub
	End If
	EditBtn.Enabled = True
	DeleteBtn.Enabled = True
	SaveBtn.Enabled = False
	MsgTxt.ForeColor = Color.Green
	MsgTxt.Text = ("")
	NameTxt.Text = Trim(DR!Sname)
	EmailTxt.Text = Trim(DR!Semail)
	CountryTxt.Text = Trim(DR!Scountry)
	MobileTxt.Text = Trim(DR!Smobile)
	CityTxt.Text = Trim(DR!Scity)
	DGVConn(SqlStr, DGV, CMD, DR, ATB)
	DR.Close()
	End Sub

view raw gistfile1.vb hosted with ❤ by GitHub

Private Sub CancelBtn_Click()

	'www.evry1.net/VBNet
	'vb2010
	Private Sub CancelBtn_Click(sender As System.Object, e As System.EventArgs) Handles CancelBtn.Click
	clearall()
	End Sub

view raw gistfile1.vb hosted with ❤ by GitHub

Private Sub EditBtn_Click()

	'Visual Basic 2010
	'http://adonetaccess2003.blogspot.com
	'Sybase
	Private Sub EditBtn_Click(sender As System.Object, e As System.EventArgs) Handles EditBtn.Click
	If SearchX = Trim("") Then
	MsgTxt.ForeColor = Color.Red
	MsgTxt.Text = ("Search First")
	Exit Sub
	End If
	SqlStr = Trim("")
	SqlStr = "Update MyInfo Set Sname = '" & NameTxt.Text.Trim & "', Semail = '" & EmailTxt.Text.Trim & "', "
	SqlStr = SqlStr + "Scity = '" & CityTxt.Text.Trim & "', Scountry = '" & CountryTxt.Text.Trim & "', "
	SqlStr = SqlStr + "Smobile = '" & MobileTxt.Text.Trim & "' Where Sname = '" & SearchX & "'"
	With CMD
	.Connection = CN
	.CommandType = CommandType.Text
	.CommandText = SqlStr
	End With
	Dim N As Integer = CMD.ExecuteNonQuery
	MsgTxt.Text = (N & " Field(s) Updated Successfully")
	clearall()
	End Sub

view raw gistfile1.vb hosted with ❤ by GitHub

Private Sub DeleteBtn_Click()

	'Visual Basic 2010 Online Courses
	'www.evry1.net/VBNet
	Private Sub DeleteBtn_Click(sender As System.Object, e As System.EventArgs) Handles DeleteBtn.Click
	If String.IsNullOrEmpty(NameTxt.Text) = True Then
	MsgTxt.ForeColor = Color.Red
	MsgTxt.Text = ("Not found, Search first")
	Exit Sub
	End If
	SqlStr = Trim("")
	SqlStr = ("Delete From MyInfo Where Sname = '" & SearchX & "'")
	With CMD
	.Connection = CN
	.CommandType = CommandType.Text
	.CommandText = SqlStr
	End With
	Dim N As Integer = CMD.ExecuteNonQuery
	MsgTxt.ForeColor = Color.Green
	MsgTxt.Text = (N & " Field(s) deleted")
	clearall()
	End Sub
	End Class

view raw gistfile1.vb hosted with ❤ by GitHub

Note : You will need 2 Pictures PNG [On : Off] to refer to the Database Status, path [..Bin/Debug].

Another Lesson : How to Remote Database using XML :

1) Create an XML file in path [.\bin\Debug\] Folder and call it [XmlFile.xml]
2) Open [XmlFile.xml] with NotePad.exe and paste the following code into it :

	<?xml version="1.0"?>
	<DB>
	<Cversion>
	<number>
	0.00.000
	</number>
	<date>
	01/11/2013
	</date>
	<SqlStatemnet>
	Update MyInfo Set Sname = 'Ahmed'
	Where Scity = 'Alex'
	</SqlStatemnet>
	</Cversion>
	</DB>

view raw gistfile1.xml hosted with ❤ by GitHub

3) Save the Xml file.
4) Open our VB 2010 Project and add a ToolStrip to the Form1, Dock = Buttom

ToolStrip VB 2010 Xml Database Update

5) How does it work "The Main Idea" : When you want to Alter structure or Edit the Advantage Database of your Advantage Database "DB.Add" on a Client's "User's" computer you will need to do something like what I did, easy and guaranteed. Find a Server and send SQL Statements to the Xml File and when the user checks for update [Check For Update] button, the application will open the XmlFile.xml and execute the Sql-Statement within, simply like that. When you use Version Number along with the update, it makes the Update process so easy to be tracked, so we will need to create and extra Database Table in the Advantage Database and call it [DB_Version].

6) DB_Version table structure :

Update Advantage Database [MyDB.Add] using XML and Visual Basic 2010

Note : UpdateDT column is Date DataType.

7) Now each time you send updates to XML file, the user Checks for update and choose the XML file from Open Dialog Box and it compares the Date and Version of the Update, if New then it executes the Sql Statement line, if not, then it gives message [MsgTxt.Text] You're Up-To-Date.
8) Download Example from MediaFire.com [Testing.ZIP]

AdsConnection GetSchema tip :

How to retrieve Advantage Data Dictionary Schema in VB 2010.

VB 2010 GetSchema - Retrieve Tables, Columns From Ads Data Dictionary (AdsConnection)

	'AdsConnection.GetSchema
	'How to retrieve Ads Data Dictionary Tables, Columns in a treeview control
	Imports Advantage.Data.Provider
	'Create VB 2010 Project (MyProject)
	Public Class MyProject
	Dim CN As New AdsConnection
	Dim CMD As New AdsCommand
	Public Sub GetAllTables(con As AdsConnection, TrV As TreeView)
	Dim ATB As DataTable 'DataTable
	Dim RootNode As TreeNode 'For Tables
	Dim tableName As String 'For Tables Names
	ATB = con.GetSchema("TABLES")
	Dim I As Integer
	'Place a treeview control (name: TV1) on the form
	TrV.Nodes.Clear()
	TrV.Nodes.Add(key:="Root", text:="Tables")
	For I = 0 To ATB.Rows.Count - 1
	tableName = CType(ATB.Rows(I)("TABLE_NAME"), String)
	RootNode = TrV.Nodes.Add(key:="table", text:=tableName)
	With CMD
	.Connection = con
	.CommandType = CommandType.Text
	.CommandText = "SELECT * FROM [" & tableName & "]"
	End With
	Dim DR As AdsDataReader = CMD.ExecuteReader
	DR.Read()
	Dim COL As String
	For I1 As Integer = 0 To DR.FieldCount - 1
	COL = DR.GetName(I1).ToString
	Dim Childnode As TreeNode = RootNode.Nodes.Add(key:="Column", text:=COL)
	Next
	DR.Close()
	Next
	End Sub
	'Place a button on the Form (Name: Button1, text: Populate)
	Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
	'Open AdsConnection
	If CN.State = ConnectionState.Open Then CN.Close()
	Dim DAPath as String = ("D:\Project\MyProjectFolder\Bin\Debug\")
	Dim UsrNm As String = "" 'Provide a Username if there is one, or leave it blank
	CN.ConnectionString = ("Data Source=" & DAPath & ";" & _
	"User ID=" & UsrNm & ";" & _
	"Advantage Server Type=ADS_LOCAL_SERVER;")
	CN.Open()
	GetAllTables(CN,TV1)
	End Sub
	End Class

view raw gistfile1.vb hosted with ❤ by GitHub

Advantage Data Types conversion in VB 2010

Type	Length	Available in DBF Table	VB 2010 Value
Character	1 to 65530	Yes	4
CICharacter	1 to 65530	No	20
Date	4	Yes	3
Logical	1	Yes	1
Memo	9	Yes	5
Double	8	VFP, Extended	10
Integer	4	VFP, Extended	11
Numeric	2 to 32	Yes	2
Image	9	Extended	7
Binary	9	Extended	6
ShortInteger	2	No	12
Time	4	No	13
TimeStamp	8	No	14
AutoIncrement	4	No	15
Raw	1 to 65530	No	16
CurDouble	8	No	17
Money	8	No	18
ModTime	8	No	22
RowVersion	8	No	21

Best Use, For example :

When you try to Retrieve Advantage Data Dictionary Table Structure you will get column names, and you will may need to retrieve Data Types [Field_Type], Advantage Data Arc. provides System.Columns Field_Type column within the System.Columns Table that return values like [Character], now when you do that in Visual Basic 2010 or any Visual Basic .Net version, you will not get [Character] but you will get [4] instead and you will want to convert it into [Character] name, you can do that using a function or a Select Case expression in VB 2010.

So, in order to retrieve Field_Type or DataType of a certain column in Advantage Database, you may use this code:

	'Visual Basic Online Course
	'Advantage Database in VB2010
	'Get Columns DataType or Field_Type Name
	Private Sub Button1_Click(sender As System.Object, _
	e As System.EventArgs) _
	Handles Button1.Click
	Dim CN1 As New OleDbConnection
	CN1.ConnectionString = ("Your ConStr")
	CN1.Open
	Dim Tcmd As New OleDbCommand
	With Tcmd
	.Connection = CN1
	.CommandType = CommandType.Text
	.CommandText = _
	("Select Field_Type From System.Columns Where Name = 'My_Column_Name'")
	End With
	Dim Rder As OleDbDataReader = Tcmd.ExecuteReader
	Rder.Read()
	Dim Sresult As Integer = Rder!Field_Type
	'Result is : 4 [My_Column_Name] Field_Type is : Character
	TextBox.Text = GetNameType(Sresult)
	Rder.Close()
	End Sub
	'Create a function to convert this
	'Number 4 into Character name
	Private Function GetNameType(ThisType As Integer) As String
	Dim TheName As String = ""
	Select Case ThisType
	Case Is = 4
	TheName = "Character"
	End Select
	Return TheName
	End Function

view raw gistfile1.vb hosted with ❤ by GitHub

Some Exceptions and Errors noticed :

Error : 

Table doesn't appear in the Data Dictionary [ARC.exe] but though it appears in the Data Dictionary Folder.

Description :

	'Visual Basic 2010 Advantage Data Dictionary
	'AdsConnection from VB 2010
	Imports Advantage.Data.Provider
	Public Class Connection
	Private Sub CreateNewTable()
	Dim Con As New AdsConnection
	Con.ConnectionString = _
	("Data Source=D:\Project\Bin\Debug\MyDb.add;" & _
	"User ID=;" & _
	"Advantage Server Type=ADS_LOCAL_SERVER;")
	Con.Open
	Dim CMD As New AdsCommand
	Dim SqlStr As String = _
	("Create Table TestTable (ID memo, Sname memo)")
	With CMD
	.Connection = Con
	.CommandType = CommandType.Text
	.CommandText = SqlStr
	End With
	Dim Onh As Integer = CMD.ExecuteNonQuery()
	End Sub
	End Class

view raw gistfile1.vb hosted with ❤ by GitHub

When you try to create a free Table [ADT Table] using Sql Statement from VB 2010 like this. The result will be a free Table but not included in the Data Dictionary, but you will only see it in the Folder contains the Database.

Cause :

The User Id section in the connectionString is Empty, but if you tried to create another table with the same name you will get an Error specifying that ISAM found a table with the same name "Table already exists" though it doesn't appear in the Data Dictionary.

Solution :

Provider a User ID or a User Id and Password if possible in the connectionString Whether you are using OleDBConnection or AdsConnection to work with this Data Dictionary.

---

♥ Here are some online Visual Basic lessons and courses :

• Visual Basic .Net snippets collection
• Visual Basic .Net - How to check for the internet connection
• Visual Basic .Net - POP3 and Receiving E-mails
• Visual Basic .Net - Generate Random Combinations
• Visual Basic .Net - Play sounds on Button Click or Mouse Hover
• Visual Basic .Net - Progressbar control
• Visual Basic .Net Solution - The application failed to initialize
• Visual Basic .Net - Working with Database full example
• Visual Basic .Net - There is already an open DataReader associated