Database-login

'** Betingelser:
'** Reference: Microsoft ActiveX Data Objects 2.5 Library

    Dim objConn As New Connection
    Dim objRs As Recordset
    Dim strSQL As String
   
  Function Connection()
      objConn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & App.Path & "\DB.mdb"
  End Function

'** Udskrivning **
    Connection
    strSQL = "SELECT * FROM tabel"
    Set objRs = objConn.Execute (strSQL)

    txtNavn = objRs("navn")

    objConn.Close

//>Rune

Hvis så du vil indsætte/updatere noget data skal du bare lave SQL sætningen om!

//>Rune

medions << Jeg er nybegynder i databaser, så jeg kan ikke alle de der 'SQL-sætninger' :) Kan du ikke uddybe det en lille smule?

'** Betingelser:
'** Reference: Microsoft ActiveX Data Objects 2.5 Library

    Dim Conn As New Connection
    Dim rs As Recordset
    Dim strSQL As String
   
  Function Connection()
      Conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & App.Path & "\DB.mdb"
  End Function

'** Udskrivning **
    Connection
    strSQL = "SELECT * FROM tabel"
    Set rs = Conn.Execute (strSQL)

    rs("navn")

    Conn.Close

'** Indsætning **
    Connection
    sql = "Insert Into Bruger(Tabel1, Tabel2, Tabel3, Tabel4,Tabel5, Tabel6, Tabel7, dato) Values('"
    sql = sql & Text1.Text & "','"
    sql = sql & Text2.Text & "','"
    sql = sql & Text3.Text & "','"
    sql = sql & Text4.Text & "','"
    sql = sql & Text5.Text & "','"
    sql = sql & Text6.Text & "','"
    sql = sql & Text7.Text & "','"
    sql = sql & Text8.Text "')"

conn.execute(sql)
    Conn.Close

'** Updatering **
      Connection
      strSQL = "UPDATE tabel SET efternavn = '" & Text1.Text & "' WHERE navn = '" & Label1.Caption & "'"
      Conn.Execute (strSQL)
      Conn.Close

syntax:
Update tabelnavn Set kolonne1 = 'værdi1' , kolonne2 = 'værdi2' Where betingelse

'** Sletning **
    Connection
    strSQL = "DELETE * FROM efternavn WHERE  navn = '" & Label1.Caption & "'"

syntex:
    Delete from tabelnavn where betingelse

//>Rune

1) Hvordan opretter man en 'post' i en database
2) Hvordan læser man fra en database
3) Hvordan sletter man poster fra en database

'------------------------------------- Form1 -------------------------------------

'------------- Database.mdb -------------
'Tabel1 = {
'
'ID = (Autonummerering)
'Felt1 = Text
'Felt2 = Text
'Felt3 = Text
'
'}
'------------- Database.mdb -------------

Option Explicit

Private m_Conn As Object  'Connection object
Private m_Rs As Object    'RecordSet object

Private DataPath As String 'Stig til databasen
Private DataPass As String 'Password til databasen

Private Sub Command1_Click()
Dim lngID As Long
  lngID = DB_AddNew("Tabel1")    'Tilføje
 
  Call DB_Edit(lngID, "Tabel1")  'Rediger
 
  Call DB_View(lngID, "Tabel1")  'Vis ID
 
  Call DB_Loop("Tabel1")          'Vis en liste
 
  Call DB_Delete(lngID, "Tabel1") 'Slet ID
End Sub

Private Sub Form_Load()
  Set m_Conn = CreateObject("ADODB.Connection")
  Set m_Rs = CreateObject("ADODB.RecordSet")

  DataPass = "Password"
  DataPath = App.Path & "\Database.mdb"
End Sub

Private Sub Form_Unload(Cancel As Integer)
  Set m_Rs = Nothing
  Set m_Conn = Nothing
End Sub

Public Function DB_AddNew(ByVal strTabel As String) As Long
Dim strSQL As String
  m_Conn.Open "PROVIDER=MSDASQL;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & DataPath & ";PWD=" & DataPass & ";"

  strSQL = "SELECT * FROM " & strTabel

    With m_Rs
      .Open strSQL, m_Conn, 1, 3

      .AddNew

        m_Rs("Felt1") = "Felt1" 'Tilføje
        m_Rs("Felt2") = "Felt2" 'Tilføje
        m_Rs("Felt3") = "Felt3" 'Tilføje

      .Update

      DB_AddNew = m_Rs("ID")

      .Close
    End With
  m_Conn.Close
End Function

Public Function DB_Edit(ByVal intID As Long, ByVal strTabel As String)
Dim strSQL As String
  m_Conn.Open "PROVIDER=MSDASQL;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & DataPath & ";PWD=" & DataPass & ";"

  strSQL = "SELECT * FROM " & strTabel & " WHERE ID = " & CLng(intID)

    With m_Rs
      .Open strSQL, m_Conn, 1, 3

        If Not .EOF Then

          m_Rs("Felt1") = "Felt1" 'Rediger
          m_Rs("Felt2") = "Felt2" 'Rediger
          m_Rs("Felt3") = Empty  'Brug Empty hvis den skal være tom.

          .Update
        End If

      .Close
    End With
  m_Conn.Close
End Function

Public Function DB_View(ByVal intID As Long, ByVal strTabel As String)
Dim strDSN As String
Dim strSQL As String

Dim strFelt1, strFelt2, strFelt3

strSQL = "SELECT * FROM " & strTabel & " WHERE ID = " & CLng(intID)
strDSN = "PROVIDER=MSDASQL;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & DataPath & ";PWD=" & DataPass & ";"

  With m_Rs
    .Open strSQL, strDSN, 3

      If Not (.BOF Or .EOF) Then

        strFelt1 = m_Rs("Felt1") 'Vis ID
        strFelt2 = m_Rs("Felt2") 'Vis ID
        strFelt3 = m_Rs("Felt3") 'Vis ID

      End If

    .Close
  End With
End Function

Public Function DB_Loop(ByVal strTabel As String)
Dim strDSN As String
Dim strSQL As String

Dim strFelt1, strFelt2, strFelt3

strSQL = "SELECT * FROM " & strTabel
strDSN = "PROVIDER=MSDASQL;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & DataPath & ";PWD=" & DataPass & ";"

  With m_Rs
    .Open strSQL, strDSN, 3

      If Not (.BOF Or .EOF) Then

        Do While Not .EOF
          strFelt1 = m_Rs("Felt1") 'Vis en liste
          strFelt2 = m_Rs("Felt2") 'Vis en liste
          strFelt3 = m_Rs("Felt3") 'Vis en liste
          .MoveNext
        Loop

      End If

    .Close
  End With
End Function

Public Function DB_Delete(ByVal intID As Long, ByVal strTabel As String)
Dim strDSN As String
Dim strSQL As String

  strSQL = "DELETE FROM " & strTabel & " WHERE ID = " & CLng(intID) 'Slet ID
  strDSN = "PROVIDER=MSDASQL;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & DataPath & ";PWD=" & DataPass & ";"

  m_Conn.Open strDSN
  m_Conn.Execute strSQL
  m_Conn.Close
End Function
'------------------------------------- Form1 -------------------------------------

Hvis du bruger Empty giver det Null når du skal have det ud igen + error hvis det er til en string, så brug denne funktion.


Public Function IFNull(ByVal vNewValue As Variant)
  If VarType(vNewValue) = vbNull Then
    IFNull = ""
      Else
    IFNull = vNewValue
  End If
End Function

strFelt1 = IFNull(m_Rs("Felt1")) 'Vis ID
strFelt2 = IFNull(m_Rs("Felt2")) 'Vis ID
strFelt3 = IFNull(m_Rs("Felt3")) 'Vis ID

strFelt1 = IFNull(m_Rs("Felt1")) 'Vis en liste
strFelt2 = IFNull(m_Rs("Felt2")) 'Vis en liste
strFelt3 = IFNull(m_Rs("Felt3")) 'Vis en liste

En lille funktion mere, bare for at gøre det nemmere :-)

Public Function IFEmpty(ByVal vNewValue As Variant)
  If Len(vNewValue) = 0 Then
    IFEmpty = Empty
      Else
    IFEmpty = vNewValue
  End If
End Function

m_Rs("Felt1") = IFEmpty("Felt1") 'Rediger
m_Rs("Felt2") = IFEmpty("Felt2") 'Rediger
m_Rs("Felt3") = IFEmpty("")      'Brug Empty hvis den skal være tom.

for at finde ID brug:

lngID = m_Rs("ID")

under DB_Loop()

Sjh << Det ser ud til at det var noget jeg kunne bruge :) Men jeg vil lige uddybe det jeg vil lidt mere:

Når man starter programmet ser man login-formen. Man skriver sit brugernavn og password, programmet chekker om posten eksisterer, hvis ikke MsgBox "Forkert brugernavn eller adgangskode", hvis den gør chekker den rang-feltet i db'en. Hvis rang = 1 sender den brugeren til frmBruger, hvis rang = 2, sender den brugeren til frmAdmin. I frmAdmin skal man kunne slette brugere og give brugere admin-status.


Bare sig til hvis jeg skal uddybe det lidt mere.

Jeg sætter lige pointene lidt op, for det lyder til at det er svært :o)

Her et accepteret svar om hvordan man kan lave en login-procedure til en database i VB: www.eksperten.dk/spm/215623 ...

If DB_Chk_User("Tabel1", "User", "Pass") Then
  MsgBox "Ok" 'Her kan du loade din forml
    Else
  MsgBox "Error"
End If



Public Function DB_Chk_User(ByVal strTabel As String, ByVal strUser As String, ByVal strPass As String) As Boolean
Dim strDSN As String
Dim strSQL As String

Dim strFelt1, strFelt2, strFelt3 As String

'Felt1 = User
'Felt2 = Password

strSQL = "SELECT * FROM " & strTabel & " WHERE Felt1 = '" & strUser & "' And Felt2 = '" & strPass & "'"
strDSN = "PROVIDER=MSDASQL;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & DataPath & ";PWD=" & DataPass & ";"

  With m_Rs
    .Open strSQL, strDSN, 3

      If Not (.BOF Or .EOF) Then

        If strUser = IFNull(m_Rs("Felt1")) And strPass = IFNull(m_Rs("Felt2")) Then

          strFelt1 = IFNull(m_Rs("Felt1")) 'Vis ID
          strFelt2 = IFNull(m_Rs("Felt2")) 'Vis ID

          strFelt3 = IFNull(m_Rs("Felt3")) 'Vis ID
          DB_Chk_User = True

        End If

      End If

    .Close
  End With
End Function

'Vis ID skulle ikke være der :)