vb2010 data_redundancy on MS ACC 2007 pt.2

[jeffersonalomia]

pls help me on my code.. i had successfully avoid data redundant but there is an error

"Item cannot be found in the collection corresponding to the requested name or ordinal." and because of that, i wasn't been able to store new data that wasn't exist in my database.

please help me... heres my code.

* Jefferson Alomia * 
** VB 2010 Express Edition **
***** Record Management System for Government Project ****
*
*

 Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click

        rs = New ADODB.Recordset



        With rs


            'check if important item is null '

            If txtappln.Text = "" Or txtappfn.Text = "" Or txtappmn.Text = "" Or txtclass.Text = "" Or txtaddr.Text = "" Or txtcnum.Text = "" Or txtbrgy.Text = "" Then
                MsgBox("Some object in the Applicant Personal Information or Classification or Ctrl Number is not filled up", MessageBoxIcon.Warning)
                .Cancel()

            End If

            sql = "Select COUNT(*) As numrec from Applicant where CONTROL_NO='" & txtcnum.Text & "'"
            'Save'

            Debug.WriteLine(sql)

            .Open(sql, con, 2, 3)

            If rs("numrec").Value = 1 Then
                MsgBox("dwadawdwa")
                .Close()
            Else

                .AddNew()




                .Fields("LAST_NAME").Value = txtappln.Text
                .Fields("FIRST_NAME").Value = txtappfn.Text
                .Fields("MIDDLE_NAME").Value = txtappmn.Text
                .Fields("ADDRESS").Value = txtaddr.Text
                .Fields("CLASSIFICATION").Value = txtclass.Text
                .Fields("CONTROL_NO").Value = txtcnum.Text
                .Fields("BARANGAY").Value = txtbrgy.Text



                MsgBox("Record has been save !!", vbInformation)
                .Update()
                .Close()


            End If

        End With
        With rs
            sql = "Select COUNT(*) As numrec from Spouse where CONTROL_NO='" & txtcnum.Text & "'"
            .Open(sql, con, 2, 3)


            If txtappln.Text = "" Or txtappfn.Text = "" Or txtappmn.Text = "" Or txtclass.Text = "" Or txtaddr.Text = "" Or txtcnum.Text = "" Or txtbrgy.Text = "" Then

                .Cancel()

            End If

            If rs("numrec").Value = 1 Then
                MsgBox("dwadawdwa")
                .Close()
            Else

                .AddNew()


                .Fields("LAST_NAME").Value = txtspln.Text
                .Fields("FIRST_NAME").Value = txtspfn.Text
                .Fields("MIDDLE_NAME").Value = txtspmn.Text
                .Fields("BARANGAY").Value = txtbrgy.Text
                .Fields("CONTROL_NO").Value = txtcnum.Text

                txtappln.Text = ""
                txtappfn.Text = ""
                txtappmn.Text = ""
                txtspln.Text = ""
                txtspfn.Text = ""
                txtspmn.Text = ""
                txtaddr.Text = ""
                txtclass.Text = ""
                txtcnum.Text = ""
                txtbrgy.Text = ""



                .Update()
                .Close()
            End If
        End With

[Reverend Jim]

You could tell us what line is causing the error.

Instead of doing an AddNew, do an insert query such as

sql = "INSERT INTO Applicant " &
      "       (LAST_NAME, FIRST_NAME, MIDDLE_NAME, ADDRESS, " &
      "        CLASSIFICATION, CONTROL_NO, BARANGAY) " &
      "VALUES('" & txtappln.Text & "'," &
      "       '" & txtappn.Text & "'," &
      "       '" & txtappmn.Text & "'," &
      "       '" & txtaddr.Text & "'," &
      "       '" & txtclass.Text & "'," &
      "       '" & txtcnum.Text & "'," &
      "       '" & txtbrgy.Text & "')"

con.Execute(sql)          

However, even better would be to use SqlDB (if using MS SQL) or OleDB (if using another database, then using parameterized queries instead of building the query using string concatenation. Examples of both can be found here.

[jeffersonalomia]

the line that causes an error is the

.Fields("LAST_NAME").Value = txtappln.Text
.Fields("FIRST_NAME").Value = txtappfn.Text
.Fields("MIDDLE_NAME").Value = txtappmn.Text
.Fields("ADDRESS").Value = txtaddr.Text
.Fields("CLASSIFICATION").Value = txtclass.Text
.Fields("CONTROL_NO").Value = txtcnum.Text
.Fields("BARANGAY").Value = txtbrgy.Text

[jeffersonalomia]

pls re-write my code if there's something wrong
thanks. :D

[Reverend Jim]

Your recordset does not have the fields that you are trying to set. It does not know what fields are in your database table. That's why I suggested you use an INSERT instead.

[jeffersonalomia]

pls help me on my problem the error is all .fields() and it says that Item cannot be found in the collection corresponding to the requested name or ordinal." and it has been error after i include COUNT(*) ON sql and debug.writeline

[jeffersonalomia]

ok i will try that

[jeffersonalomia]

should i include COUNT(*) on statement

INSERT COUNT(*) into Applicant " &
      "       (LAST_NAME, FIRST_NAME, MIDDLE_NAME, ADDRESS, " &
      "        CLASSIFICATION, CONTROL_NO, BARANGAY) " &

[jeffersonalomia]

is there any shortcut on the sql statement

[Reverend Jim]

COUNT(*) returns the number of matching records. You don't want to include that ini the INSERT. The format of a simple INSERT is

INSERT INTO tableName
    (fldname1, fldname2, ...)
VALUES(value1, value2, ...)

While it is possible to omit the second line (the field names) this is not wise because then your INSERT values must match up one-for-one in the correct order with the fields (columns) in your table. If you add a column to the table your query breaks and you have to change your code. With parameterized queries you can code like

qry = "INSERT INTO myTable(custID, lastName, firstName) " &
      "VALUES(@cust, @last, @first)"
Dim cmd As New SqlCommand("", con)
cmd.CommandText = qry

if you are using SqlDB or

qry = "INSERT INTO myTable(custID, lastName, firstName) " &
      "VALUES(?, ?, ?)"
Dim cmd As New OleDbCommand("", con)
cmd.CommandText = qry

if you are using OleDB. Then you add the actual values with

cmd.Parameters.AddWithValue

I linked to full examples using both methods in an earlier reply.