help with datasets (i think)

[dreamdelerium]

hey everyone. just a quickquestion about datasets. in my application, i have a dialog that allows users to enter data into a table. before the data is entered, i have a function check the table to ensure there are no duplicates. if there arent any, another sub enters the data into the table. the problem is this: once the data is entered into the table (i know its entered because i can check the table), i close the dialog. when the user tries to enter the same data again, my sub to check for redundancies does not work. it only works when the application is closed and reopened. bellow are the two procedures:

check function:

Function CheckStaffDuplicates(ByRef UName As String)
  On Error GoTo errorhandler
        Dim con As New OleDb.OleDbConnection
        Dim ds As New DataSet
        Dim da As New OleDb.OleDbDataAdapter
        Dim sql As String
        Dim counter As Integer = 0
        Dim TestForDups As Boolean = True

        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data    Source=|DataDirectory|\SMSTracker.mdb;" & _
          "Persist Security Info=True;Jet OLEDB:Database Password=jasonwucinskibots5"

       con.Open()

        sql = "SELECT * FROM tblStaff where Combined_Name='" & UName & "'"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "tblStaff")
        con.Close()
        For Each row In ds.Tables("tblStaff").Rows
            counter = counter + 1
        Next
        If counter > 0 Then
        Else
            TestForDups = False
        End If

        Return TestForDups

Errorhandler:
        If Err.Number = 0 Then
        Else
            MsgBox(Err.Description)
        End If

    End Function

if the function returs false, i then enter the data into the table:

Public Sub InsertStaff(ByVal GetFirstName, ByVal GetLastName, ByVal GetCombinedName, ByVal GetPossition)
 On Error GoTo errorhandler

        Dim myOleDbConnection As OleDb.OleDbConnection
        Dim insertcommand As String
        Dim myConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
          "Data Source=C:\Documents and Settings\PHODISO SEEMA\My Documents\Visual Studio 2008\" & _ 
          "Projects\SMS Tracker\SMS Tracker\SMSTracker.mdb;Persist Security Info=True;" & _ 
          "Jet OLEDB:Database Password=jasonwucinskibots5"
        Dim myOleDbCommand As New OleDb.OleDbCommand(insertcommand, myOleDbConnection)
        Dim temp_num As Integer

        insertcommand = "insert into tblStaff (First_Name,Last_Name," _
        & "Combined_Name,Possition) values ('" & GetFirstName & "','" _
        & GetLastName & "','" & GetCombinedName & "','" & GetPossition & "')"

        myOleDbConnection = New OleDb.OleDbConnection(myConnectionString)
        myOleDbConnection.Open()        
        temp_num = myOleDbCommand.ExecuteNonQuery
        myOleDbConnection.Close()
        Dialog2.Close()

errorhandler:
        If Err.Number = 0 Then
        Else
            MsgBox(Err.Description)
        End If
    End Sub

End Module

any help would be appreciated. im new to this but if i had to guess i would say that the dataset is not being refreshed. if thats the case how do i refresh it

 

[cjard]

Please take a read of the PQ link and the DW2 link in my signature, section on Creating A Simple Data App..

That will get you doing your data access correctly.


Now, read up about Primary Keys on database tables. Set one for this situation so that your db cannot have duplicates. Now dont bother checking whether the record exists, jsut try to insert it and handle the case where it fails

 

[dreamdelerium]

hey thanks for the links. things should be a bit easier now. learning IS half the battle