[TUT] VB6 MSAccess Sample using ADO (UPDATED V3 20100216) now with ADD and DataReport

[ramo92]

Re: [TUT] VB6 MSAccess Sample using ADO (UPDATED V3 20100216) now with ADD and DataRe

walang primary key ang database mo, nag aatempt syang galawin ang isang record pero multiple records ang nakikita kasi nga walang primary key.

lagyan mo ng AutoIncrement Column siguro
basta merong UNIQUE na column

nalabas pala yung error na yun kapag dalawang bese kung sinisave, bro paano ba syntax nung kapag sinave ko na e lilipat na sa panibagong record o yung blank?

 

[ericgmejia]

Re: [TUT] VB6 MSAccess Sample using ADO (UPDATED V3 20100216) now with ADD and DataRe

nalabas pala yung error na yun kapag dalawang bese kung sinisave, bro paano ba syntax nung kapag sinave ko na e lilipat na sa panibagong record o yung blank?

bro alam mo iba kasi ang ginagamit mo e, so di ko alam lahat ng ginawa mo dyan,
kung yung sample ko ang sinundan mo matutulungan pa kita kasi may basis ako.

yung style mo sa programming kasi, thats not how i do it, pag school lang kasi yan.
i use mostly queries to INSERT nad UPDATE, just like my samples here.

those codes are tired and tested for more than 10yrs na. mga clients kong banks at AMTS yan ang gamit.

pagdating sa code mo limited lang maitutulong ko dyan

btw.
nilagyan mo na ba ng primary key ang table?

to give you and example
based sa code mo:

Public con As ADODB.Connection
Public rec As ADODB.Recordset



Public Sub connect()
    Set con = New ADODB.Connection
    With con
        .ConnectionString = App.Path + "db.mdb"
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .CursorLocation = adUseClient
        .Open
    End With
End Sub

Private Sub cmdsave_Click()
Set rec = New ADODB.Recordset
With rec
    .Open "select * from movie", con, adOpenDynamic, adLockOptimistic
    .AddNew
        rec(0) = txtid.Text
        .Update
        .Close
End Sub

sa add new:cmdsave_Click
yung OPEN "SELECT * FROM Movie"

then Addnew
then save

now bakit ko kukunin lahat ng records sa table at ilagay ko sya sa memory, what if meron akong 10000 records, so i load ko lahat yun sa memory, only to ADD SINGLE Record.
imagine ang load nyan sa computer.

compare to

Private Function InsertProduct() As Boolean
    Dim bReturn  As Boolean
    Dim strQuery As String

    Dim cn       As ADODB.Connection
    Set cn = New ADODB.Connection
    cn.ConnectionString = gstrConnectionString
    On Error GoTo ERR_LINE
    
    cn.Open
    strQuery = "INSERT INTO ProductsMaster ("
    strQuery = strQuery & "ProductCode, "
    strQuery = strQuery & "Description, "
    strQuery = strQuery & "Cost, "
    strQuery = strQuery & "Price, "
    strQuery = strQuery & "StockOnHand, "
    strQuery = strQuery & "ReorderPoint, "
    strQuery = strQuery & "LastUpdated "
    strQuery = strQuery & ")"
    strQuery = strQuery & "VALUES "
    strQuery = strQuery & "("
    strQuery = strQuery & "'" & txtProductCode.Text & "', "
    strQuery = strQuery & "'" & txtDescription.Text & "', "
    strQuery = strQuery & "'" & txtCost.Text & "', "
    strQuery = strQuery & "'" & txtPrice.Text & "', "
    strQuery = strQuery & "'" & txtStockOnHand.Text & "', "
    strQuery = strQuery & "'" & txtReorder.Text & "', "
    strQuery = strQuery & "'" & Now & "' "
    strQuery = strQuery & ")"
    
    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = cn
    cmd.CommandText = strQuery
    cmd.CommandType = adCmdText
    
    Dim n As Integer
    [COLOR="Red"]cmd.Execute n[/COLOR]
    If n = 1 Then
        bReturn = True
        mProductCode = txtProductCode.Text
        MsgBox "Product was Added", vbInformation
    Else
        bReturn = False
        MsgBox "Product was not Added", vbInformation
        mProductCode = ""
    End If
ERR_RESUME:
    cn.Close
    Set cn = Nothing
    InsertProduct = bReturn
    Exit Function
ERR_LINE:
    bReturn = False
    MsgBox Err.description
    Resume ERR_RESUME
End Function

open a connection, open a command
pass to the command the SQL INSERT statement "INSERT INTO ProductsMaster...."
then execute the command
ayun nasave na agad
mahaba lang kasi may mga notifications at mahaba yung query
aside from that its all straight forward.

 

[ramo92]

Re: [TUT] VB6 MSAccess Sample using ADO (UPDATED V3 20100216) now with ADD and DataRe

@TS
sensya pre ah, di ko kasi gaanong makuha yung code na ginamit mo. Baguhan lang kasi ako. Saan mo ba nalaman definition nung mga objects , methods, at function na ginamit mo sa code mo? Adik kasi yung Teachear namin, kahit ako nabigla sa pinagawa sa amin, alam mo naman siguro yung feeling nung first time makaencounter ng new type of programming , diba.

 

[ericgmejia]

Re: [TUT] VB6 MSAccess Sample using ADO (UPDATED V3 20100216) now with ADD and DataRe

@TS
sensya pre ah, di ko kasi gaanong makuha yung code na ginamit mo. Baguhan lang kasi ako. Saan mo ba nalaman definition nung mga objects , methods, at function na ginamit mo sa code mo? Adik kasi yung Teachear namin, kahit ako nabigla sa pinagawa sa amin, alam mo naman siguro yung feeling nung first time makaencounter ng new type of programming , diba.

very straightforward na yung code ko dyan, may error handlling pa. just follow the steps

nakuha ko yan sa sipag sa pagbabasa, pag eexperiment ng code at commonsense.
walang tigil na trial and error para makuha ang pinaka maganda at pinaka optimized na code para sakin.

in fact kung masipag ka lang you can search yung mga words sa google.

 

[ramo92]

Re: [TUT] VB6 MSAccess Sample using ADO (UPDATED V3 20100216) now with ADD and DataRe

@TS
yep. Nagsesearch din ako, puro complex yung nahahanap ko. Frustrate tuloy ako. Ang mahirap nito nagaaral pa ako kaya dapat matutunan gawa magpproceed na agad sa new topic, malalagpasan ko ata ADO ng di gaanong naintindihan T_T

 

[ericgmejia]

Re: [TUT] VB6 MSAccess Sample using ADO (UPDATED V3 20100216) now with ADD and DataRe

@TS
yep. Nagsesearch din ako, puro complex yung nahahanap ko. Frustrate tuloy ako. Ang mahirap nito nagaaral pa ako kaya dapat matutunan gawa magpproceed na agad sa new topic, malalagpasan ko ata ADO ng di gaanong naintindihan T_T

Ano na ang naintindihan mo sa ADODB?

lay out what you know, ill try to fill in ang kulang

 

[silentcuteb0y14]

Re: [TUT] VB6 MSAccess Sample using ADO (UPDATED V3 20100216) now with ADD and DataRe

tnx poh d2 ^^

 

[ramo92]

Re: [TUT] VB6 MSAccess Sample using ADO (UPDATED V3 20100216) now with ADD and DataRe

@TS patulong naman nung code sa Update at paglalagay nung mga record mula sa access papuntang ListBox sa VB.?

 

[ericgmejia]

Re: [TUT] VB6 MSAccess Sample using ADO (UPDATED V3 20100216) now with ADD and DataRe

@TS patulong naman nung code sa Update at paglalagay nung mga record mula sa access papuntang ListBox sa VB.?

its in the sample na po how to do it.

nsa frmProductList
Function ListProducts()

listview ang ginamit po dyan, try to read it and understand how it works

Private Function ListProducts() As Boolean
    On Error GoTo ERROR_LINE
    Dim bReturn As Boolean
    Dim sSql    As String
    sSql = "SELECT ProductCode, Description,Cost,Price,StockOnHand,ReorderPoint FROM ProductsMaster ORDER BY ProductCode"
    'sa TAGALOG: KUNIN (SELECT) ANG ProductCode AT Description MULA SA (FROM) ProductsMaster IHANAY (ORDER BY) BASE SA ProductCode
    
    Dim sConn As New ADODB.Connection
    sConn.Open gstrConnectionString
    Dim rsADO As New ADODB.Recordset
   [COLOR="red"] lvwProducts.ListItems.Clear
    Dim xList As ListItem
    lvwProducts.ListItems.Clear[/COLOR]
    If sConn.State = adStateOpen Then
        rsADO.Open sSql, sConn, adOpenForwardOnly
        Do While Not rsADO.EOF 'habang wala pa sa dulo
            
            [COLOR="Red"]Set xList = lvwProducts.ListItems.Add(, , rsADO("ProductCode"))
            xList.SubItems(1) = rsADO("Description")
            xList.SubItems(2) = Format(rsADO("StockOnHand"), "###,##0.00")
            xList.SubItems(3) = Format(rsADO("ReorderPoint"), "###,##0.00")
            xList.Tag = rsADO("ProductCode")[/COLOR]
            
            rsADO.MoveNext  'lumipat sa susunod na record
            '            DoEvents
        Loop
    Else
        Err.Raise vbObjectError + 513, ERR_CAN_NOT_OPEN_CONNECTION
    End If
    bReturn = True
EXIT_FUNCTION:
    ListProducts = bReturn
    Set rsADO = Nothing
    sConn.Close 'isara ang nakabukas, yan ang malinis at maayos na programmer
    Set sConn = Nothing
    Exit Function
ERROR_LINE:
    MsgBox Err.description
    bReturn = False
    Resume EXIT_FUNCTION
End Function

 

[ramo92]

Re: [TUT] VB6 MSAccess Sample using ADO (UPDATED V3 20100216) now with ADD and DataRe

Cge. Sir. Try ko yan. Kung kelan medyo nagets ko na to dun ba ako late sa pagsubmit. Hehe. Salamat sa lahat sir

 

[ramo92]

Re: [TUT] VB6 MSAccess Sample using ADO (UPDATED V3 20100216) now with ADD and DataRe

sir bakit di ka nagamit ng With - End With?
diba mas magandang gumamit nan? ano po ba disadvantage ng paggamit nan? yan kasi turo sa amin ng teacher namin

 

[ericgmejia]

Re: [TUT] VB6 MSAccess Sample using ADO (UPDATED V3 20100216) now with ADD and DataRe

sir bakit di ka nagamit ng With - End With?
diba mas magandang gumamit nan? ano po ba disadvantage ng paggamit nan? yan kasi turo sa amin ng teacher namin

ok ang WITH.... END WITH
para sa tamad mag type.
pang shortcut lang kasi yan.


gusto ko kasi ipakita ng malinaw bawat line.
With...EndWith just adds to the confusion.

pero syempre tahts just my style,

marerealize mo ang sinasabi ko kung meron ka ng mga 50,000 lines ng code sa harap mo.

 

[smexxy]

Re: [TUT] VB6 MSAccess Sample using ADO (UPDATED V3 20100216) now with ADD and DataRe

ty sir, ask nalng po ako ulit if what ung probs. hehe salamat po nga marami ^_^

 

[ramo92]

Re: [TUT] VB6 MSAccess Sample using ADO (UPDATED V3 20100216) now with ADD and DataRe

ok ang WITH.... END WITH
para sa tamad mag type.
pang shortcut lang kasi yan.


gusto ko kasi ipakita ng malinaw bawat line.
With...EndWith just adds to the confusion.

pero syempre tahts just my style,

marerealize mo ang sinasabi ko kung meron ka ng mga 50,000 lines ng code sa harap mo.

oo nga, kaya siguro confuse ako sa tut mo at lecture ng teacher namin

 

[aroCarlisle]

Re: [TUT] VB6 MSAccess Sample using ADO (UPDATED V3 20100216) now with ADD and DataRe

thanks po dito sir!

 

[Norbies]

Re: [TUT] VB6 MSAccess Sample using ADO (UPDATED V3 20100216) now with ADD and DataRe

Good Day Po, Ok po ung program pero my kunting error prompt but ok nmn, ung code u po medyo complicated.

 

[ericgmejia]

Re: [TUT] VB6 MSAccess Sample using ADO (UPDATED V3 20100216) now with ADD and DataRe

Good Day Po, Ok po ung program pero my kunting error prompt but ok nmn, ung code u po medyo complicated.

ano ang prompt?

bakit mo nasabing complicated?
complicated ba dahil di mo naintindihan?
complicated ba na i trap ko ang error?
complicated ba na gumamit ng ADODBConnection?
complicated ba na gumamit ng SQL Statements?

thats how we code sa mga actual na trabaho, thats how we code sa mga serious applications
if you are looking for codes na pang students lang then hindi po yan yun.

im sharing that kind of coding so that pag nsa actual na trabaho kayo di kayo mashock at maligaw.
basic SQL statements lang yan for CRUD, wala pa nga JOINS yan e

try to keep up sa code, you will realize its value sooner or later,
maybe di mo pa ma appreciate yan ngayon, but if you fail to appreciate it in the future, its your loss

 

[bryan1207]

Re: [TUT] VB6 MSAccess Sample using ADO (UPDATED V3 20100216) now with ADD and DataRe

Asan si shaolinkungfu? Anu ulit problem mo?

 

[shaolinkungfu]

Re: [TUT] VB6 MSAccess Sample using ADO (UPDATED V3 20100216) now with ADD and DataRe

Originally Posted by ericgmejia
dim bReturn as boolean;

tama ka.
bReturn is a variable sa loob ng function testConnection
it will pass the result of the function OUTSIDE of the function
becuase of
testConnection = bReturn

so pag tinawag mo ang function frm another function like

dim result as boolean
result = TestConnection

you will know ang resulta ng function via vaiable 'result'
kung true or false ang testing ng connection.

what do u mean by "Set conn = Nothing",

mapapansin mo na yung function ay walang ginamit na variable na form level, public or module level variable EXCEPT 'gstrConnectionString'

i want to keep my functions independent as much as possible, so lahat ng kelangan ng function para tumakbo ay nasa loob na neto, except for one or 2 na kelangan talaga.

connection object 'conn' was declared inside the function, tama?
so i want to clear my objects from memory before i exit from the function
setting the object to 'nothing' is a good way to do it.
remember VB6 has no Garbage Collector like VB.NET
so i keep my object as tight as possible,
meaning smallest scope as much as possible, smallest scope is inside a function, not within a form or public.

i did not declare my 'conn' in module, form or public to save memory,
may overhead yan sa speed during the first few connections since bago ang connection mo everytime,
pero if you use SQLServer or server based RDBMS, meron Connection Pooling, meaning SQLServer will reuse the last connection for you, bibilis na sya nun.

by saving memory maiiwasan mo ang HANG pag matagal na tumatakbo ang program mo, hindi magiging sluggish ang gui.

setting an object to NOTHING destroys the object,
opposite of declaring the object.

ginawa ang VB6 para di na problemahin ng programmer ang mga memory issues unlike sa C++, pero responsible ka parin dapat na maging maayos ang program mo na tumtakbo.


sir pa-refer nalang po dito. salamat.


so meaning to say, declaring conn in a form or module will cause a slow in speed when executing the program.

the best way to make ur project independent as much as possible at para mapabilis is to declare the conn to function not to a module, form or public.

ang kahalagahan nito para sa stand-alone na program ya maiiwasan ang hung kahit matagal syang nagra-run. ok sir thanks...

so in everytime i will add a new data to database, tatawagin ko ung ginawa kong object sa function para makapagsave?

for me, studying your code will take a long time to understand the flow and logic because i need to know every function of the syntax on it. very challenging sir. is the reward for completing the add command is still open? thanks anyway.

 

[bryan1207]

Re: [TUT] VB6 MSAccess Sample using ADO (UPDATED V3 20100216) now with ADD and DataRe

Originally Posted by ericgmejia
dim bReturn as boolean;

tama ka.
bReturn is a variable sa loob ng function testConnection
it will pass the result of the function OUTSIDE of the function
becuase of
testConnection = bReturn

so pag tinawag mo ang function frm another function like

dim result as boolean
result = TestConnection

you will know ang resulta ng function via vaiable 'result'
kung true or false ang testing ng connection.

what do u mean by "Set conn = Nothing",

mapapansin mo na yung function ay walang ginamit na variable na form level, public or module level variable EXCEPT 'gstrConnectionString'

i want to keep my functions independent as much as possible, so lahat ng kelangan ng function para tumakbo ay nasa loob na neto, except for one or 2 na kelangan talaga.

connection object 'conn' was declared inside the function, tama?
so i want to clear my objects from memory before i exit from the function
setting the object to 'nothing' is a good way to do it.
remember VB6 has no Garbage Collector like VB.NET
so i keep my object as tight as possible,
meaning smallest scope as much as possible, smallest scope is inside a function, not within a form or public.

i did not declare my 'conn' in module, form or public to save memory,
may overhead yan sa speed during the first few connections since bago ang connection mo everytime,
pero if you use SQLServer or server based RDBMS, meron Connection Pooling, meaning SQLServer will reuse the last connection for you, bibilis na sya nun.

by saving memory maiiwasan mo ang HANG pag matagal na tumatakbo ang program mo, hindi magiging sluggish ang gui.

setting an object to NOTHING destroys the object,
opposite of declaring the object.

ginawa ang VB6 para di na problemahin ng programmer ang mga memory issues unlike sa C++, pero responsible ka parin dapat na maging maayos ang program mo na tumtakbo.


sir pa-refer nalang po dito. salamat.


so meaning to say, declaring conn in a form or module will cause a slow in speed when executing the program.

the best way to make ur project independent as much as possible at para mapabilis is to declare the conn to function not to a module, form or public.

ang kahalagahan nito para sa stand-alone na program ya maiiwasan ang hung kahit matagal syang nagra-run. ok sir thanks...

so in everytime i will add a new data to database, tatawagin ko ung ginawa kong object sa function para makapagsave?

for me, studying your code will take a long time to understand the flow and logic because i need to know every function of the syntax on it. very challenging sir. is the reward for completing the add command is still open? thanks anyway.

Tapos na po yung Add eh. Try mo download ung last part. Andun ung pinoproblema mo. Yung Adding. Try to understand, logic logic. And mga commands, searchable naman.