help with datasets

  • 12 years ago

    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 see it in the 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:

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

    Code:
    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

  • 12 years ago

    You are working something strange way, rather use this for check:

    SQL = Select Count(*) Where ....

    ...New Connection
    ...New Command
    ...OleReader = Command.ExQuery
    If Reader.Read Then TestForDups = (Reader.GetInt32(0) > 0)
    Reader.Close

    It is much faster SQL, and also structures used are more optimal

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.

“Beware of bugs in the above code; I have only proved it correct, not tried it.” - Donald Knuth