BAlance in payment..

[paoi00]

hello again :D i want to update my database if an enrollee have balance or dont have.. if they have balance , the record of payment of student will be updated..

here's my code :

Dim cn As OleDbConnection
    If TextBox1.Text = "" Then
        MsgBox("Enter a Student Number! ", MsgBoxStyle.Information, "Warning")
    ElseIf TextBox1.TextLength = 9 Then
        con = New OleDbConnection("Provider= Microsoft.ACE.oledb.12.0; Data Source=C:\Users\user\Desktop\CBFMNHS Enrollment System\CBFMNHS Enrollment System\bin\Debug\Enrollment System.accdb")
        Dim ewaaa As String = "Select * from Payment  where StudentNumber = '" & TextBox1.Text & "'"
        com = New OleDbCommand(ewaaa, con)
        con.Open()
        com.ExecuteNonQuery()
        rid = com.ExecuteReader
        rid.Read()
        If (rid.HasRows) Then
            TextBox1.Text = rid(2)

            cn = New OleDbConnection("Provider=Microsoft.ace.oledb.12.0;Data Source=C:\Users\user\Desktop\CBFMNHS Enrollment System\CBFMNHS Enrollment System\bin\Debug\Enrollment System.accdb")
            cn.Open()

            cmda = New OleDbCommand("UPDATE Payment SET DateAndTime = '" & Label1.Text & "' And AmountPaid = '" & TextBox5.Text & "' And Balances = '" & TextBox7.Text & "' ", cn)
            cmda.ExecuteNonQuery() <<<== error

            cn.Close()
            MsgBox("Transaction Updated", MsgBoxStyle.Information, "Update")

        Else
            con = New OleDbConnection("Provider=Microsoft.ace.oledb.12.0;Data Source=C:\Users\user\Desktop\CBFMNHS Enrollment System\CBFMNHS Enrollment System\bin\Debug\Enrollment System.accdb")
            da.SelectCommand = New OleDbCommand("Select* from Payment", con)
            cmd = New OleDbCommandBuilder(da)
            If con.State = ConnectionState.Closed Then con.Open()
            ds = New DataSet
            da.Fill(ds, "Payment")
            ds.Tables("Payment").Rows.Add()
            lastrow = ds.Tables("Payment").Rows.Count - 1
            myrow = ds.Tables("Payment").Rows.Item(lastrow)
            myrow.Item("DateAndTime") = Label1.Text
            myrow.Item("StudentNumber") = TextBox1.Text
            myrow.Item("NameofStudent") = TextBox2.Text
            myrow.Item("ModeofPayment") = ComboBox1.Text
            myrow.Item("IDFees") = TextBox3.Text
            myrow.Item("PTAFees") = TextBox6.Text
            myrow.Item("MiscellaneousFee") = TextBox4.Text
            myrow.Item("AmountPaid") = TextBox5.Text
            myrow.Item("Balances") = TextBox7.Text
            ds.Tables("Payment").GetChanges()
            cmd.GetInsertCommand()
            da.Update(ds, "Payment")
            MsgBox("Successfully Saved!")
            con.Close()
            TextBox2.Enabled = False
            TextBox3.Enabled = False
            TextBox4.Enabled = False
            TextBox5.Enabled = False
            TextBox1.Clear()
            TextBox2.Clear()
            TextBox3.Clear()
            TextBox4.Clear()
            TextBox5.Clear()
            TextBox6.Clear()
            TextBox7.Clear()
            ComboBox1.SelectedIndex = -1
        End If
    End If

the problem with this code. : error occured says "Data type mismatch in criteria expression." please help me..

[Mitja Bonca]

Hmm, can you be a bit more specific where this error occurs? Please use break point, and then go through the code line by line, so you will exactly see where it happens.

Just something (and I hope the error is here), about UPDATE query, you dont use and WHERE clause in it.

Update query should look like: "UPDATE TableName SET ColumnName2, ColumnName3, WHERE ColumnName 1 = @someUniqueParameter"

And you actually dont have any WHERE clause, thats why the code doesnt know what to update.

If there is anything else, let us know.

[Mitja Bonca]

One more thing about Update query, as you will take a look my example query in details, you will see many differences between yours and mine. I have noticed you used AND while stating columns to update. Remember, there is NO AND between columnNames, but only a comma ",". Ok?

Take a look my example and do it the same way.
bye

[paoi00]

then i edited my update statement. :

cmda = New OleDbCommand("UPDATE Payment SET DateAndTime = '" & Label1.Text & "' And AmountPaid = '" & TextBox5.Text & "' And Balances = '" & TextBox7.Text & "' where StudentNumber = '" & TextBox1.Text & "' ", cn)
            cmda.ExecuteNonQuery()

same error occured.. at
cmda.ExecuteNonQuery()

[Mitja Bonca]

NO. READ my last post.
NO "AND" between columns to update.
Chanage to:

"UPDATE Payment SET DateAndTime = '" & Label1.Text & "', AmountPaid = '" & TextBox5.Text & "',  Balances = '" & TextBox7.Text & "' where StudentNumber = '" & TextBox1.Text & "' "

[Mitja Bonca]

And one more thing, which type is your StudentNumber in database? If its varchar, then OK, if its not (if its number), then you will have to parse textBox1.Text to integer, or other value.

[paoi00]

the datatype of StudentNumber in my database is Text.. and the AmountPAid,Balances have Currency on their dattype.. should i change it?