Database Application

databases Cook Islands
  • 14 years ago

        Hey guys.... m totally new to Vb and i havent yet madea database project of my own... i have alot of readings about ADo, DAO and DataEnvironment and User Control, .... but havnt done anything practical yet...


    I tried using data environment.... can get connection, but dnt know how to use it....
    I dnt know the procedure of using database in code .... I guess i have to establish connection, then create rescordsets and queries but i dnt kow how to go on doin that...

    I have a project in whish i need to save to a DB and then print from the database...

    can someone help me with the simplest method of database reading, updating and deleting... and also bout grids... how u we store stuff in columns and also can we stil use grid without using database(ie add to grid from variables)...

    thanks for your help guys....









  • 14 years ago

    hi this is the easiest way i have found to do it... does require abit of SQL! but if you can learn the alphabet you can learn sql!

    first things first, make a reference to "Microsoft ActiveX Data Objects Library 2.8" (or which ever version you have listed) to do this you press Alt+P then click refences.

    add three command buttons and a module!

    'stick this in a module...

    Public ReturnedDBData As ADODB.Recordset
    Public oConn As ADODB.Connection


    ' now create a function...

    Public Function Connect(dbpath As String) As String
       
    'CREATING CONNECTION
        Set oConn = CreateObject("ADODB.Connection")
        On Error Resume Next
        With oConn
            .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=" & _
                dbpath & _
                ";jet OLEDB:database"
            .Open
        End With










    End Function

    Public Function ViewData ()

    dim strSQL as string

    strsql = "Select * from TableName"   ' change TableName to the actual name of the table

     Set ReturnedDBData = CreateObject("ADODB.Recordset")  ' creates recordset

        Set ReturnedDBData = oConn.Execute(strSQL)  ' sends the sql statement

    If Not (ReturnedDBData.EOF) Then
       
        ReturnedDBData.MoveFirst

       Do While Not ReturnedDBData.EOF
      msgbox ReturnedDBData.Fields(0).Value & vbtab & ReturnedDBData.Fields(1).Value ' displays 'field 0 and 1 from the table.. do this as for as many fields as there is!

      ReturnedDBData.MoveNext
        Loop

    Else
            MsgBox "no records found"
       
    End If


    end function

    public function UpdateData()

    dim strSQL as string

     strSQL = "UPDATE TableName SET FieldName = 'Value' where AnotherFieldName = 'Something'"      '  for example  "update addresses set TelNumber = '999' where Forename = 'Emergency'"

     Set ReturnedDBData = CreateObject("ADODB.Recordset")  ' creates recordset

        Set ReturnedDBData = oConn.Execute(strSQL)  ' sends the sql statement

    ' call viewdata                   ' uncomment this line if you want it to automatically call the view again

    end function

    public function DeleteData()

    dim strsql as string

        strSQL = "DELETE FROM Tablename WHERE Fieldname = 'Value'"        ' for example "delete from Contacts where Email = 'harry@domain.com'"

     Set ReturnedDBData = CreateObject("ADODB.Recordset")  ' creates recordset

        Set ReturnedDBData = oConn.Execute(strSQL)  ' sends the sql statement

    end function

    ' now goto your main form code..

    private sub form_load

    call Connect ("C:\desktop\database.mdb")

    end sub

    private sub command1_click()

    call viewdata

    end sub

    private sub command2_click()

    call updateData

    end sub

    private sub command3_click()

    call deletedata

    end sub

     

     

    ' ok this isnt tried and tested as im too tired.. but all should be ok! need ne more info i will help you out... jus giv us a shout

     

    init

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity.” - Dennis Ritchie