Writing to access db issue

[lhhippie1]

Hello all,
I am having a little issue I was hoping to get some help with. I am working on an app and everything was going great. I was able to write to my db and all feilds were working great, so then I wanted to add a user password confirmation before logging to the db. Well, I worked on that issue for a while and finally got it working properly (by reading many article here :).... (I also think there may be some sql issues with the way that I am confirming the password....but that can wait for now.....
All is good.....so I thought....

When I run the entire app, for some reason the loggin information is no longer working properly???? I then commented out the password confirmation section and the logging will work. So I am a little confussed why they will not work together. I would greatly appreciate it if someone could take a look and see where I went wrong.

Please be nice, I am a beginner with the vb stuff :) It to a very long time to get to this point and I do not realy understand all of the db connection stuff, so if possible write the reply so I will understand it..

 Thanks in advance

`

Public Class BaleCutterLC

    Private Sub TableLayoutPanel1_Paint(ByVal sender As System.Object, ByVal e As System.Windows.Forms.PaintEventArgs)

    End Sub

    'on user tab away from user name, app will confirm entry of password enterd to db password
    Private Sub TextBox4_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox4.Leave

        Dim connetionString As String
        Dim oledbCnn As OleDbConnection
        Dim oledbCmd As OleDbCommand
        Dim sql As String
        Dim UserPassword As String


        'path to database location
        connetionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\DT\Desktop\PaperlessAppdbFolder\SecAccess.accdb;"

        'sql statement looking for textbox entry
        sql = "SELECT * FROM SecAccess WHERE User = '" & TextBox4.Text & "'"


        'assign connection strinf to variable
        oledbCnn = New OleDbConnection(connetionString)

        'if exceptions catch and return error
        Try
            oledbCnn.Open()
            oledbCmd = New OleDbCommand(sql, oledbCnn)
            Dim oledbReader As OleDbDataReader = oledbCmd.ExecuteReader()

            While oledbReader.Read

                'assign read db password to usable variable
                UserPassword = oledbReader.Item(2)

                'assign password pop up box names
                Dim message, title, defaultValue As String
                Dim Confirmpassword As String

                'pop up configurations
                title = "Password Confirmation"              'Set title.
                message = "Please enter your user password"  'Set prompt.
                defaultValue = ""                            'Set default value.


                ' Display dialog box at position 100, 100 and assign user input to variable Userpassword
                Confirmpassword = InputBox(message, title, defaultValue, 100, 100)


                'confirm passwrod and entered password match
                If Confirmpassword = UserPassword Then
                    MsgBox("confirmed password")

                End If

            End While


            'close connection to db
            oledbReader.Close()
            oledbCmd.Dispose()
            oledbCnn.Close()
        Catch ex As Exception
            MsgBox("Can not open connection ! ")
        End Try

    End Sub


    'if safety check < 8 hrs complete new form


    Private Sub BaleCutLogButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BaleCutLogButton.Click

        'Establish connection with BaleCutterLC db
        Dim strConnection As String
        Dim newconnection As OleDbConnection
        strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\DT\Desktop\PaperlessAppdbFolder\ADCBaleCutterLC.accdb;"
        newconnection = New OleDbConnection(strConnection)

        'Open connection to BaleCutterLC db
        newconnection.Open()
        Dim addstr As String = "INSERT INTO `ADCBaleCutterLC` (`Old Formula`, `New Formula`,`Raw Material Name`,`Raw Material Number`,`Batch Number`,`Preformed By`,`Preformed by Date`,`Verified By`,`Verified By Date`) VALUES (add1, add2, add3, add4, add5, add6, add7, add8, add9)"
        Dim addnewcommand As New OleDb.OleDbCommand

        addnewcommand.Connection = newconnection
        addnewcommand.CommandText = addstr

        'propmt user of empty feilds
        If OldFormulaTextBox.Text = "" Then
            MsgBox("Empty feilds not Allowed", MsgBoxStyle.Information, "Verify")

        ElseIf NewFormulaTextBox.Text = "" Then
            MsgBox("Empty feilds not Allowed", MsgBoxStyle.Information, "Verify")

        ElseIf TextBox1.Text = "" Then
            MsgBox("Empty feilds not Allowed", MsgBoxStyle.Information, "Verify")

        ElseIf TextBox2.Text = "" Then
            MsgBox("Empty feilds not Allowed", MsgBoxStyle.Information, "Verify")


        ElseIf TextBox3.Text = "" Then
            MsgBox("Empty feilds not Allowed", MsgBoxStyle.Information, "Verify")


        ElseIf TextBox4.Text = "" Then
            MsgBox("Empty feilds not Allowed", MsgBoxStyle.Information, "Verify")


        ElseIf TextBox5.Text = "" Then
            MsgBox("Empty feilds not Allowed", MsgBoxStyle.Information, "Verify")


        ElseIf TextBox6.Text = "" Then
            MsgBox("Empty feilds not Allowed", MsgBoxStyle.Information, "Verify")


        ElseIf TextBox7.Text = "" Then
            MsgBox("Empty feilds not Allowed", MsgBoxStyle.Information, "Verify")

        End If


        'read values from textboxes
        addnewcommand.Parameters.AddWithValue("add1", OldFormulaTextBox.Text)
        addnewcommand.Parameters.AddWithValue("add2", NewFormulaTextBox.Text)
        addnewcommand.Parameters.AddWithValue("add3", TextBox1.Text)
        addnewcommand.Parameters.AddWithValue("add4", TextBox2.Text)
        addnewcommand.Parameters.AddWithValue("add5", TextBox3.Text)
        addnewcommand.Parameters.AddWithValue("add6", TextBox4.Text)
        addnewcommand.Parameters.AddWithValue("add7", TextBox5.Text)
        addnewcommand.Parameters.AddWithValue("add8", TextBox6.Text)
        addnewcommand.Parameters.AddWithValue("add9", TextBox7.Text)



        Try 'exception capture  
            'write to db
            addnewcommand.ExecuteNonQuery()
            MsgBox("Batch information successfully logged")

            'change color status of current RM
            If TextBox1.Text > "" Then
                TextBox1.BackColor = Color.LawnGreen
            End If
            If TextBox2.Text > "" Then
                TextBox2.BackColor = Color.LawnGreen
            End If
            If TextBox3.Text > "" Then
                TextBox3.BackColor = Color.LawnGreen
            End If
            If TextBox4.Text > "" Then
                TextBox4.BackColor = Color.LawnGreen
            End If
            If TextBox5.Text > "" Then
                TextBox5.BackColor = Color.LawnGreen
            End If
            If TextBox6.Text > "" Then
                TextBox6.BackColor = Color.LawnGreen
            End If
            If TextBox7.Text > "" Then
                TextBox7.BackColor = Color.LawnGreen
            End If


        Catch ex As Exception
        End Try



        'Close connection to db
        newconnection.Close()

    End Sub

    Private Sub ReturnButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ReturnButton.Click

        Dim oForm As MainForm
        oForm = New MainForm()
        oForm.Show()

        'add save function if return button clicked*********************
        'this opens a seconds main form*******************

        'close form if return button clicked
        Me.Close()


    End Sub


    Private Sub AddNewBatchButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddNewBatchButton.Click

        Dim choice = MsgBox("Is this for the same formula", 4, "ALERT")
        If choice = 7 Then 'yes 6 and no 7
            NewFormulaTextBox.Text = OldFormulaTextBox.Text
            OldFormulaTextBox.Text = ""

        End If


        If TextBox1.BackColor = Color.LawnGreen Then

            TextBox1.Text = ""
            TextBox1.BackColor = Color.White
            TextBox2.Text = ""
            TextBox2.BackColor = Color.White
            TextBox3.Text = ""
            TextBox3.BackColor = Color.White
            TextBox4.Text = ""
            TextBox4.BackColor = Color.White
            TextBox5.Text = ""
            TextBox5.BackColor = Color.White
            TextBox6.Text = ""
            TextBox6.BackColor = Color.White
            TextBox7.Text = ""
            TextBox7.BackColor = Color.White
        Else : MsgBox("You must log the current batch before entering a new one")

        End If


    End Sub


End Class

`

[Reverend Jim]

I have a bit of a family thing at the moment (had to take my father-in-law to emergency) so I can't address the bigger issue just now, but I have a suggestion to simplify the username/password verification. Have the user enter both the username and password at the same time then do

sql = "SELECT * FROM SecAccess " & _
      " WHERE User = '" & txtUser.Text & "'" & _
      "   AND Pswd = '" & txtPswd.Text & "'"

If the number of records returned is zero then either the username or password was incorrect and the login should fail. This is a lot simpler than retrieving the record then comparing fields. Note that I use names for the textbox controls that reflect their function. Always a good practice.

[lhhippie1]

Thanks Jim,
Sorry to here about your farther in law!
I will give your suggestion a shoot, maybe just removing some of the complexcity of the varification may do the trick????

[Reverend Jim]

He's home from the hospital. Had surgery for a hernia last night.

[lhhippie1]

Glad to hear he is home, hope all is going well..

I went from bad to worse here...now even if I comment out the password confirmation the data will not log to the db, not sure what happened there.... I must have deleted something important :)

Still doing reasearch and it appears there are many ways to do the exact same thing and I am finding it difficult make headway. If you have any time and could look at my code I would greatly appreciate it. Maybe a present from you to me :)

[Reverend Jim]

When the boys get done playing their Christmas (networked) game I'll have a look. While I am waiting, could you possibly post the structure and types of the relevant database tables? If they are not overly large I'll recreate them here for testing.

[lhhippie1]

Thanks Jim,
the tables are about as basic as they come. the first one is called ADCBaleCutterLC and has these columns: Old Formula,New Formula,Raw Material Name,Raw Material Number,Batch Number,Preformed By,Preformed by Date,Verified By,Verified By Date. I did not format any of the columns so what ever they started as that's what they still are. The second one is called SecAccess and has two columns: User and password.

I am beginning to think that the reason why the data is not logging right now is because of the insert statment....but not 100%

Thanks for your time

[Reverend Jim]

Wow. Marathon gaming session. Just got the computer back.

To the best of my knowledge, oledb lacks the parameter naming used by SqlClient so you have to use sequential parameters as in

    Dim con As New OleDbConnection("Provider=SQLNCLI10;Server=.\SQLEXPRESS;Database=mydb;Trusted_Connection=Yes;Connect Timeout=15;")
    Dim qry As String = "INSERT INTO test3 (username,pswd) Values(?,?)"

    Dim cmd As New OleDbCommand(qry, con)

    cmd.Parameters.AddWithValue("col1", "Barney")
    cmd.Parameters.AddWithValue("col2", "Rubble")

    con.Open()
    cmd.ExecuteNonQuery()
    con.Close()

You indicate the number of columns in the insert query by adding one "?" for each column. Each instance of cmd.Parameters.AddWithValue replaces the next "?" with an actual value. In your case the query would look like

Dim qry As String = "INSERT INTO ADCBaleCutterLC " & _
                    " (Old Formula,New Formula,Raw Material Name," & _
                    "  Raw Material Number,Batch Number,Preformed By," & _
                    "  Preformed by Date,Verified By,Verified By Date) " & _
                    "VALUES (?,?,?,?,?,?,?,?,?)"

and your calls to AddWithValue can stay the way they are. Note that you don't need the single quotes around the field names. Side note - I have always found that blanks in field names are a problem. I suggest replacing them with underscores or using camel case. ie

Raw_Material_Name
RawMaterialName

Let me know how that works.

[Reverend Jim]

To validate a user you can use

Private LoggedIn As Boolean = False   'class level variable

Private Sub btnLogIn_Click(sender As System.Object, e As System.EventArgs) Handles btnLogIn.Click

    Dim con As New OleDbConnection("Provider=SQLNCLI10;Server=.\SQLEXPRESS;Database=mydb;Trusted_Connection=Yes;")
    Dim qry As String = "SELECT COUNT(*) FROM test3 WHERE username = ? and pswd = ?"
    Dim cmd As New OleDbCommand(qry, con)

    cmd.Parameters.AddWithValue("col1", txtUser.Text)
    cmd.Parameters.AddWithValue("col2", txtPswd.Text)

    con.Open()
    LoggedIn = cmd.ExecuteScalar <> 0
    con.Close()

End Sub

PS, with SqlClient you could do (named parameters)

Dim qry As String = "INSERT INTO test3 (username,pswd) Values(@user,@pswd)"
Dim cmd As New OleDbCommand(qry, con)

cmd.Parameters.AddWithValue("@user", "Barney")
cmd.Parameters.AddWithValue("@pswd", "Rubble")

[lhhippie1]

Man, this little side project has been excruciating....but very rewarding!!!

After many hours/days of reading and trying to figure out why my code was not working, because for the most part I think I was on the right path. I find that I changed a letter from "B" to "b" and that was the cause of all my problems

In my sql statement I incorrectly identified one of my columns (note: "b" instead of "B")
Which is why your suggestion was the best Christmas present (although a day late :)).
While I was going through and removing the spaces..... I see the stupid, little, annoying, lower case letter, and the bells starting ringing and all was right in the world:) at least my little odd world.

So, thank you for taking the time helping me I greatly appreciate it!!!!

I am sure a full time programmer would write this much differently but I figured I would supply the functioning code. Incase any other part timer's like myself are having issues. Maybe they can read through this and it can be of some use to them.

Imports System.Data.OleDb

Public Class BaleCutterLC

    'on user tab away from user name, app will confirm entry of password enterd to SecAccess db
    Private Sub TextBox4_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox4.Leave


        Dim connetionString As String
        Dim oledbCnn As OleDbConnection
        Dim oledbCmd As OleDbCommand
        Dim sql As String
        Dim UserPassword As String


        'path to database location
        connetionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\DT\Desktop\PaperlessAppdbFolder\SecAccess.accdb;"

        'sql statement looking for textbox entry
        sql = "SELECT * FROM SecAccess WHERE User = '" & TextBox4.Text & "'"

        'assign connection strinf to variable
        oledbCnn = New OleDbConnection(connetionString)

        'if exceptions catch and return error
        Try
            oledbCnn.Open()
            oledbCmd = New OleDbCommand(sql, oledbCnn)
            Dim oledbReader As OleDbDataReader = oledbCmd.ExecuteReader()

            While oledbReader.Read

                'assign read db password to usable variable
                UserPassword = oledbReader.Item(2)

                'assign password pop up box names
                Dim message, title, defaultValue As String
                Dim Confirmpassword As String

                'pop up configurations
                title = "Password Confirmation"              'Set title.
                message = "Please enter your user password"  'Set prompt.
                defaultValue = ""                            'Set default value.


                ' Display dialog box at position 100, 100 and assign user input to variable Userpassword
                Confirmpassword = InputBox(message, title, defaultValue, 100, 100)


                'confirm passwrod and entered password match
                If Confirmpassword = UserPassword Then
                    MsgBox("confirmed password")

                Else
                    MsgBox("Wrong password")


                End If

            End While


            'close connection to db
            oledbReader.Close()
            oledbCmd.Dispose()
            oledbCnn.Close()
        Catch ex As Exception
            MsgBox("Can not open connection ! ")
        End Try

    End Sub


    Private Sub BaleCutLogButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BaleCutLogButton.Click


        'Establish connection with BaleCutterLC db
        Dim strConnection As String
        Dim newconnection As OleDbConnection
        strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\DT\Desktop\PaperlessAppdbFolder\ADCBaleCutterLC.accdb;"
        newconnection = New OleDbConnection(strConnection)

        'Open connection to BaleCutterLC db
        newconnection.Open()

        'statement to insert data into variable
        Dim addstr As String = "INSERT INTO ADCBaleCutterLC (OldFormula,NewFormula,RawMaterialName,RawMaterialNumber,BatchNumber,PreformedBy,PreformedByDate,VerifiedBy,VerifiedByDate) VALUES (add1, add2, add3, add4, add5, add6, add7, add8, add9)"
        Dim addnewcommand As New OleDb.OleDbCommand
        addnewcommand.Connection = newconnection
        addnewcommand.CommandText = addstr

        'propmt user of empty feilds
        If OldFormulaTextBox.Text = "" Then
            MsgBox("Empty feilds not Allowed", MsgBoxStyle.Information, "Verify")
        ElseIf NewFormulaTextBox.Text = "" Then
            MsgBox("Empty feilds not Allowed", MsgBoxStyle.Information, "Verify")
        ElseIf TextBox1.Text = "" Then
            MsgBox("Empty feilds not Allowed", MsgBoxStyle.Information, "Verify")
        ElseIf TextBox2.Text = "" Then
            MsgBox("Empty feilds not Allowed", MsgBoxStyle.Information, "Verify")
        ElseIf TextBox3.Text = "" Then
            MsgBox("Empty feilds not Allowed", MsgBoxStyle.Information, "Verify")
        ElseIf TextBox4.Text = "" Then
            MsgBox("Empty feilds not Allowed", MsgBoxStyle.Information, "Verify")
        ElseIf TextBox5.Text = "" Then
            MsgBox("Empty feilds not Allowed", MsgBoxStyle.Information, "Verify")
        ElseIf TextBox6.Text = "" Then
            MsgBox("Empty feilds not Allowed", MsgBoxStyle.Information, "Verify")
        ElseIf TextBox7.Text = "" Then
            MsgBox("Empty feilds not Allowed", MsgBoxStyle.Information, "Verify")
        End If

        'read values from textboxes
        addnewcommand.Parameters.AddWithValue("add1", OldFormulaTextBox.Text)
        addnewcommand.Parameters.AddWithValue("add2", NewFormulaTextBox.Text)
        addnewcommand.Parameters.AddWithValue("add3", TextBox1.Text)
        addnewcommand.Parameters.AddWithValue("add4", TextBox2.Text)
        addnewcommand.Parameters.AddWithValue("add5", TextBox3.Text)
        addnewcommand.Parameters.AddWithValue("add6", TextBox4.Text)
        addnewcommand.Parameters.AddWithValue("add7", TextBox5.Text)
        addnewcommand.Parameters.AddWithValue("add8", TextBox6.Text)
        addnewcommand.Parameters.AddWithValue("add9", TextBox7.Text)

        'exception capture 
        Try

            'write to db
            addnewcommand.ExecuteNonQuery()
            MsgBox("Batch information successfully logged")

            'change textbox color status aftre logging complete and password varification
            If TextBox1.Text > "" Then
                TextBox1.BackColor = Color.LawnGreen
            End If
            If TextBox2.Text > "" Then
                TextBox2.BackColor = Color.LawnGreen
            End If
            If TextBox3.Text > "" Then
                TextBox3.BackColor = Color.LawnGreen
            End If
            If TextBox4.Text > "" Then
                TextBox4.BackColor = Color.LawnGreen
            End If
            If TextBox5.Text > "" Then
                TextBox5.BackColor = Color.LawnGreen
            End If
            If TextBox6.Text > "" Then
                TextBox6.BackColor = Color.LawnGreen
            End If
            If TextBox7.Text > "" Then
                TextBox7.BackColor = Color.LawnGreen
            End If

            'catch any errors so app will not crash
        Catch ex As Exception
            MsgBox("Can't log to LC database ")
        End Try

        'Close connection to db
        newconnection.Close()

    End Sub
    Private Sub ReturnButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ReturnButton.Click


        Dim oForm As MainForm
        oForm = New MainForm()
        oForm.Show()
        'add save function if return button clicked*********************
        'this opens a seconds main form*******************
        'close form if return button clicked
        Me.Close()
    End Sub
    Private Sub AddNewBatchButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddNewBatchButton.Click

        Dim choice = MsgBox("Is this for the same formula", 4, "ALERT")

        If choice = 7 Then 'yes 6 and no 7
            NewFormulaTextBox.Text = OldFormulaTextBox.Text
            OldFormulaTextBox.Text = ""
        End If

        If TextBox1.BackColor = Color.LawnGreen Then
            TextBox1.Text = ""
            TextBox1.BackColor = Color.White
            TextBox2.Text = ""
            TextBox2.BackColor = Color.White
            TextBox3.Text = ""
            TextBox3.BackColor = Color.White
            TextBox4.Text = ""
            TextBox4.BackColor = Color.White
            TextBox5.Text = ""
            TextBox5.BackColor = Color.White
            TextBox6.Text = ""
            TextBox6.BackColor = Color.White
            TextBox7.Text = ""
            TextBox7.BackColor = Color.White
        Else : MsgBox("You must log the current batch before entering a new one")
        End If
    End Sub
End Class

[Reverend Jim]

I once left one comma out of a call (from FORTRAN) to an assembler routine. It caused all kinds of problems in a production system and took weeks to track down. It looked something like

CALL MDREAD(PARM1,PARM2,PARM3,,,,,,,,,,,,,,,TEMP)

If you are done then please mark this thread as solved.