Need VB Help Really Bad (*pic*)

Discussion in 'OT Technology' started by sablehonda, Oct 27, 2003.

  1. sablehonda

    sablehonda New Member

    Joined:
    Sep 21, 2002
    Messages:
    8,514
    Likes Received:
    0
    Location:
    Chicago
    Need VB Help Really Bad (*pic*) (EDIT: Source Code & DB Added)

    I'm developing a Time Clock application in VB .Net. It's a basic punch-in/punch-out system that records an employee's id, a timestamp, and a couple other pieces of information in an Access database.

    I've done a lot of Web database applications but nothing in VB. I just need help getting started with how to connect to the database and get things from it and into it.

    I have attached a picture of the VB interface and the access database.

    The first thing I need is to take the number an employee enters into the employee ID field and see if it exists in the employee table.

    Any help is appreciated.

    [​IMG]

    Source Code & DB:
    http://hilltop.bradley.edu/~jbair/projects/labTimeClock/DB and Code.zip
     
    Last edited: Oct 30, 2003
  2. VBGOD

    VBGOD Guest

    ADO.NET is your answer my friend. By the way, your post is very clear and easy to read. On top of that, what you want to do is pretty universal. Most business applications are basically GUI's that interact with a database. This is standard issue stuff.

    First off, at the top of your form (Code view), you'll have to tap into the ADO.NET library. Since you're using an Access Database, you'll want to use the System.Data.OleDb libabrty.

    Code:
    Imports System.Data.OleDb
    To check to see if an EmployeeID exists, there are several ways of doing this.
    P.S Please change field 'EmployeeID' to an integer/number type. It should not be a string. Also, leave it as the primary key.

    Code:
    	If txtEmployeeID.text.Trim.Length = 0 Then Exit Sub
    
    	Dim EmployeeID as Integer = CINT(txtEmployeeID.text)
    	Dim SQL as String = "Select Top 1 * From Employee Where EmployeeID = EmployeeID
    
            'Database connection.
            Dim MyConnection As OleDbConnection = New OleDbConnection(ConnectionString)
    
            'Command object (Feed it the SQL query string along with the Connection object)
            Dim MyCommand As OleDbCommand = New OleDbCommand(SQL, MyConnection)
    
            'Variable that will hold the data coming back from the database.
            Dim ReaderStr As String = ""
    
            Try
                'Establish the connection to the database.
                MyConnection.Open()
    
                'ExecuteScalar returns either a value or Nothing.
                ReaderStr = MyCommand.ExecuteScalar
    
                'Close the connection and other cleanup work.
                MyConnection.Close()
                MyCommand.Dispose()
    
                If ReaderStr="" Then
    		'Nothing Found
    		Else
    		'Something found! Yeah!
    	    End If
    
            Catch OtherErr As Exception
                messagebox.show(OtherErr.Message)
    
            Finally
                If MyConnection.State = ConnectionState.Open Or MyConnection.State = ConnectionState.Broken Then
                    MyConnection.Close()
                    MyConnection.Dispose()
                End If
            End Try
    
    
    
    There you have it. I've been doing these types of applications for many years now. A good idea/design goes a long way. Example: I would ditch the whole textbox bit. That's dangerous and unprofessional. I would have a dropdown list box that the user can not edit directly. Here's an example of this:

    Code:
    	Dim Conn As OleDbConnection = New OleDbConnection(User.ConnectionString)
            Dim DA As OleDbDataAdapter = New OleDbDataAdapter("Select EmployeeID, EmployeeName From Employee Order By EmployeeName Asc", Conn)
            Dim DS As DataSet = New DataSet("DaDropDown")
    
            Try
                Conn.Open()
                DA.Fill(DS, "Facts")
    
                ListBox1.DataSource = DS.Tables("DaDropDown").DefaultView
                ListBox1.DisplayMember = "EmployeeName"
                ListBox1.ValueMember = "EmployeeID"
    
                Conn.Close()
    
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
    
    
    The code I just typed out is fantastic and should be used for your application. Simply put, it will display the employee name to the user, and it's value will be the employeeID. It's automatic. The [ListBox1.SelectedValue] is the employeeID. You can also enhanced this. Let me know if you need any more help. I'll be around tomorrow night.

    Oh, and believe me - there's A LOT more! Adding, updating, deleting, etc. I'll answer those questions when the time comes. In the mean time, check out my website: http://www.vbgod.com/ - check out the ADO section.
     
  3. sablehonda

    sablehonda New Member

    Joined:
    Sep 21, 2002
    Messages:
    8,514
    Likes Received:
    0
    Location:
    Chicago
    Wow, I was surprised to see someone reply at all much less the amount of help you gave me. You rule! :)

    I will try this at work tomorrow and let you know how it goes. Thank you.
     
  4. sablehonda

    sablehonda New Member

    Joined:
    Sep 21, 2002
    Messages:
    8,514
    Likes Received:
    0
    Location:
    Chicago
    Ok, I have some questions/notes:

    - I must leave employeeID as a string for scalability purposes

    - Syntactical question: Why doesn't this work?:

    Me.OleDbSelectCommand1.CommandText = "SELECT employeeName, employeeID FROM employee WHERE employeeID='#employeeID.text#'"

    Basically I'm asking how to get employeeID.text to be non-literal. It's pretty much a string issue.
     
  5. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Could you explain why you need to keep the EmployeeID as a string? Even backward compatibility is not really a reason since you could assign an autonumber to every existing employee. Your performance will get a hit - especially if you are worried about "scalability".

    And not sure if this is the same in vbnet but in access I would use the dlookup command.
     
  6. sablehonda

    sablehonda New Member

    Joined:
    Sep 21, 2002
    Messages:
    8,514
    Likes Received:
    0
    Location:
    Chicago
    Have to keep it as a string due to the possibility of alphabetical characters
     
  7. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    That's still not a reason in the world of DB. EmployeeID is autonumbered, EmployeeNumber could be an indexed unique string and then of course EmployeeName etc...

    You could still index by EmployeeNumber for other things, but you would have the flexibility of changing the scheme whenever you want since that number does not flow through to other tables. THink of the EmployeeID as something only you know about. Add another field for EmployeeNumber.

    Theres a few other changes I would do from a db stand point:

    A facility table with the FacilityID as an autonumber and FacilityName as a string. Then in your employee table, refer to it as FacilityID. Same thing in your punch table.

    Your state field should be a number field and a separate table with a list of states.

    There has to be a very good reason to use text as a linking field - more often than not you can avoid it.
     
  8. sablehonda

    sablehonda New Member

    Joined:
    Sep 21, 2002
    Messages:
    8,514
    Likes Received:
    0
    Location:
    Chicago
    The employeeIDs already exist and some have alphabetical characters.
     
  9. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Even more reason to do it how I laid out. If some knob comes along and changes the criteria for that employeeID field, you're going to have to change all of the linking information (unless you set up referential integrity etc...). My way, you change it in one place and you're done.
     
  10. sablehonda

    sablehonda New Member

    Joined:
    Sep 21, 2002
    Messages:
    8,514
    Likes Received:
    0
    Location:
    Chicago
    That'd be nice but I don't have that much capbility. I'm just here to use the data I'm given.

    Database performance isn't a large issue.
     
  11. VBGOD

    VBGOD Guest

    Get rid of the pound symbols. Also, you may want to use a LIKE.
     
  12. VBGOD

    VBGOD Guest

    5Gen_Prelude is dead on. In the DB world, such fields as CustomerID, EmployeeID, ProductID, (other unique ID) etc... are all number types. They are NEVER a string. Hey, do what your told, but it kind of goes against the grain. If you want to see a good database design, check out the Northwind database.
     
  13. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Yeah I was under the impression you had control over the database itself.
     
  14. sablehonda

    sablehonda New Member

    Joined:
    Sep 21, 2002
    Messages:
    8,514
    Likes Received:
    0
    Location:
    Chicago
    I got rid of the pound symbols and tried 'LIKE' instead of '='. I'm still not getting any records from the database. When I print the contents of Me.OleDbSelectCommand1.CommandText to a label, employeeID.text is literally in the criteria area and is not resolved to its value in the field.
     
  15. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    As I mentioned before, try using the dlookup command. It will return the first match, and if not found it will return null

    =iif(isnull(dlookup("EmployeeName","Employee","EmployeeID = '" & me!employeeid" & '")),"Unknown Employee",dlookup("EmployeeName","Employee","EmployeeID = '" & me!employeeid & '"))
     
  16. sablehonda

    sablehonda New Member

    Joined:
    Sep 21, 2002
    Messages:
    8,514
    Likes Received:
    0
    Location:
    Chicago
    *bump* I'm dying here
     
  17. panzerfaust

    panzerfaust New Member

    Joined:
    Oct 13, 2003
    Messages:
    1,604
    Likes Received:
    0
    Location:
    Far far away
    attach code and db. it's easier to figure out the problem when you can look at it yourself
     
  18. sablehonda

    sablehonda New Member

    Joined:
    Sep 21, 2002
    Messages:
    8,514
    Likes Received:
    0
    Location:
    Chicago
  19. VBGOD

    VBGOD Guest

    I just downloaded that file, and there's no source code. None at all.

    Code:
        Private Sub timeLogOut_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles timeLogOut.SelectedIndexChanged
    
        End Sub
    
        Private Sub Go_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Go.Click
    
        End Sub
    
        Private Sub labTimeClock_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    
        End Sub
    
    You need to find a hungry VB person that is willing to spend the time to do your homework. This VB person isn't going to do that. :hs:

    In the mean time, at least start the database work.
     
  20. VBGOD

    VBGOD Guest

    If you have any specific questions, I and others here will be most happy to answer them.
     
  21. sablehonda

    sablehonda New Member

    Joined:
    Sep 21, 2002
    Messages:
    8,514
    Likes Received:
    0
    Location:
    Chicago
    You'll be paid :hs:
     
  22. kingtoad

    kingtoad OT Supporter

    Joined:
    Sep 2, 2003
    Messages:
    55,924
    Likes Received:
    11
    Location:
    Los Angeles
    :ugh: How pathetic.
     
  23. sablehonda

    sablehonda New Member

    Joined:
    Sep 21, 2002
    Messages:
    8,514
    Likes Received:
    0
    Location:
    Chicago
    Sorry that I'm not proficient at -everything-. I'm sure you are though. Asshole.
     
  24. kingtoad

    kingtoad OT Supporter

    Joined:
    Sep 2, 2003
    Messages:
    55,924
    Likes Received:
    11
    Location:
    Los Angeles
  25. sablehonda

    sablehonda New Member

    Joined:
    Sep 21, 2002
    Messages:
    8,514
    Likes Received:
    0
    Location:
    Chicago

Share This Page