how to count the number of records with ExecuteScalar if more than one then cannot insert into in the MS Access database on vb.net

Question

I tried with the code below it doesn't work, is there something wrong with my code, please guide me. or if there is another solution

Thanks

Private Sub Btnsave_Click(sender As Object, e As EventArgs) Handles Btnsave.Click
    If txtWCSKILL.Text = "" Or txtWCEXPERIENCE.Text = "" Or txtWCAPPEARANCE.Text = "" Or txtWCEDUCATION.Text = "" Then
        MessageBox.Show("WSKILL,WEXPERIENCE,WAPPEARANCE,WEDUCATION cannot be empty.", "Report Status",
            MessageBoxButtons.OK, MessageBoxIcon.Warning)
        txtWCSKILL.Focus()
        Exit Sub
    End If
    Dim count As Integer

    Using cmd As OleDbCommand = con.CreateCommand()
        cmd.CommandText = "Select COUNT(*) FROM tblWeightingCriteria"
        con.Open()
        If count = CInt(cmd.ExecuteScalar() > 1) Then
            MessageBox.Show("One Record Already Exist!", "Report Status",
                MessageBoxButtons.OK, MessageBoxIcon.Warning)
            con.Close()
            Exit Sub

        Else
            Try
                con.Open()
                cmd.CommandText = "INSERT INTO tblWeightingCriteria" & "([WCSKILL],[WCEXPERIENCE],[WCAPPEARANCE],[WCEDUCATION]) " & "VALUES(@WCSKILL,@WCEXPERIENCE,@WCAPPEARANCE,@WCEDUCATION)"
 cmd.Parameters.AddRange(New OleDbParameter() {
                         New OleDbParameter("@WCSKILL", DbNullOrStringValue(txtWCSKILL.Text)),
                         New OleDbParameter("@WCEXPERIENCE", DbNullOrStringValue(txtWCEXPERIENCE.Text)),
                         New OleDbParameter("@WCAPPEARANCE", DbNullOrStringValue(txtWCAPPEARANCE.Text)),
                         New OleDbParameter("@WCEDUCATION", DbNullOrStringValue(txtWCEDUCATION.Text))})
                Dim result = cmd.ExecuteNonQuery()
                If result = 0 Then
                    MsgBox("No Data has been Inserted!")
                Else
                    MsgBox("New Data is Inserted succesfully!")
                End If
            Catch ex As Exception
                 MsgBox(ex.Message, MsgBoxStyle.Information)
            End Try
        End If
    End Using

    con.Close()
End Sub

Answer 1

Several points to consider.

You have this line:

If count = CInt(cmd.ExecuteScalar() > 1) Then

count is of type Integer and CInt(cmd.ExecuteScalar() > 1) returns a Boolean value, so obviously you have the Option Strict set to Off and the implicit conversions are allowed. If you turn the option On, you'll get the BC30512 error:

Option Strict On disallows implicit conversions from 'Boolean' to 'Integer'.

To avoid getting unexpected results and errors like this, make sure to turn this and other on/off options on by code:

Option Explicit On
Option Strict On
Option Infer On

Or through the project's properties:

Project -> YourProjectName Properties -> Compile.

and turn the Explicit, Strict, and Infer On. I'm sure your debugger will start complaining and report some errors to fix. At least, consider doing this in your next project.

---

The COUNT(*) or COUNT(TheIdField) query returns the n rows/records of the given table. If the table is empty, then you'll get 0 for sure. So, if you want to allow inserting just one record:

Using con = New OleDbConnection("..."), cmd = con.CreateCommand()
    cmd.CommandText = "select count(*) from tblWeightingCriteria"
    con.Open()

    Dim count = Convert.ToInt32(cmd.ExecuteScalar())

    If count > 0 Then
        ' Alert and exit the sub.
        Exit Sub
    End If

    ' Otherwise, proceed...
End Using

Note, don't keep the data access objects (OleDbConnection, OleDbCommand, OleDbDataAdapter, ...etc) in class fields. Create them in the CRUD methods in Using block. This way, you don't need to explicitly close a connection or dispose of a disposable object. The Using statement will do that for you.

---

Separate your code and create specialized methods.

To create a connection:

Private Function CreateConnection() As OleDbConnection
    Return New OleDbConnection("Your connection string;")
End Function

For the CRUD operations and helpers methods:

Private Function InsertData() As Integer
    Using con = CreateConnection(), cmd = con.CreateCommand()
        cmd.CommandText = "INSERT INTO tblWeightingCriteria " &
            "([WCSKILL], [WCEXPERIENCE], [WCAPPEARANCE], [WCEDUCATION]) " &
            "VALUES (?, ?, ?, ?)"
        cmd.Parameters.AddRange({
            New OleDbParameter("?", OleDbType.VarChar) With {
            .Value = DbNullOrStringValue(txtWCSKILL.Text)},
            New OleDbParameter("?", OleDbType.VarChar) With {
            .Value = DbNullOrStringValue(txtWCEXPERIENCE.Text)},
            New OleDbParameter("?", OleDbType.VarChar) With {
            .Value = DbNullOrStringValue(txtWCAPPEARANCE.Text)},
            New OleDbParameter("?", OleDbType.VarChar) With {
            .Value = DbNullOrStringValue(txtWCEDUCATION.Text)}
        })
        con.Open()
        Return cmd.ExecuteNonQuery()
    End Using
End Function

Private Function DbNullOrStringValue(value As String) As Object
    If String.IsNullOrEmpty(value) Then
        Return DBNull.Value
    Else
        Return value
    End If
End Function

Private Function LoadData() As DataTable
    Dim dt As New DataTable()

    Using con = CreateConnection(), cmd = con.CreateCommand(),
        ta = New OleDbDataAdapter(cmd)
        cmd.CommandText = "Select * From tblWeightingCriteria"
        ta.Fill(dt)
    End Using

    Return dt
End Function

Private Function GetCount() As Integer
    Using con = CreateConnection(), cmd = con.CreateCommand()
        cmd.CommandText = "select count(*) from tblWeightingCriteria"
        con.Open()
        Return Convert.ToInt32(cmd.ExecuteScalar())
    End Using
End Function

' ...etc.

Note, the OLE DB provider does not support named parameters. Use ? as placeholders and add the parameters in the same order of the database fields as shown above.

Now your save caller should look like this:

Private Sub btnInsert_Click(sender As Object, e As EventArgs) _
    Handles btnInsert.Click
    Dim inputBoxes = {
        txtWCSKILL,
        txtWCEXPERIENCE,
        txtWCAPPEARANCE,
        txtWCEDUCATION
    }

    If inputBoxes.Any(Function(tb) tb.Text.Trim().Length = 0) Then
        MessageBox.Show("... cannot be empty.",
                        "Report Status",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Warning)
        inputBoxes(0).Select()
        Return ' or Exit Sub            
    End If

    Try
        If GetCount() > 0 Then
            MessageBox.Show("One Record Already Exists!",
                        "Report Status",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Warning)
            Return
        End If

        Dim result = InsertData()

        If result = 0 Then
            MessageBox.Show("No Data has been Inserted!")
        Else
            MessageBox.Show("New Data is Inserted succesfully!")
        End If
    Catch ex As Exception
        MessageBox.Show(ex.Message, "Error",
                        MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Try
End Sub

---

Also, consider implementing the n-tier or a similar purpose architecture. Have the UI or the presentation in a layer/tier in one project. The data access in another layer/tier (DAL) and project. And a middle business logic layer (BLL) to communicate between them.

Further reading and examples.

• Work with datasets in n-tier applications
• Coding an N-Tier Application in C#

Answer 2

First of all, you don't show where your connection object was created.

Coming from MS Access, it is long time coding approach to persist the connection, but you do not do this when using .net. In other words, don't try and persist a connection object. So, when writing code in MS Access + VBA, you can and often should force a connection to remain open to the database.

In vb.net, you don't have to do this, and in fact you don't want to.

Next up, fire up access and create a query, and type in this:

Note that EVEN when there are zero rows in the database, you STILL get a row back!

When I run the above query, I get this result:

So, the data returned is ONE row of data, and a count = 0.

So, you will ALWAYS get 1 record from such a query, EVEN when zero rows exist in the table.

Your logic is that once a record is inserted to that table, you will NEVER for the next 100 years ever again insert a row into that table. That means the insert code can and will only run one time for the WHOLE LIFE of the application.

I suspect that you looking to test/check if the current record exists, since the logic you have so far does not make a whole lot of sense.

I mean, once you inserted a record into that table, you are to never use that that one row, and you NEVER insert again into that table?

So, in theory, you have a table that will only ever have one row of data, but after you insert a record, you will never insert data again to that table?

Are you sure that's the design you want here? So that table only is ever to have one inserted row of data, and from that point for next 100 years, you never add another row?

I guess the big question then do you ever want to update or change that one row in that table that will only ever have one row?

And does your above code ever supposed to update that one row again?

Now, I can't tell if you logic is:

• We only ever for 100 years insert one row, and NEVER touch that one row of data again for the next 100 years?

• Or we want to check if no rows exist, and if no rows, we insert the one row, however, if one row exists, then I want to update that one existing row with the new values?

If no rows, insert new row with your values.

If 1 existing row, then update that row with your new values.

Seems to me, the above makes more sense.

However, let's write this to only EVER for the next 100 years insert the one row , and thus NEVER again ever insert.

So, this code looks to be workable:

    If txtWCSKILL.Text = "" Or
        txtWCEXPERIENCE.Text = "" Or
        txtWCAPPEARANCE.Text = "" Or
        txtWCEDUCATION.Text = "" Then
        MessageBox.Show("WSKILL,WEXPERIENCE,WAPPEARANCE,WEDUCATION cannot be empty.", "Report Status",
            MessageBoxButtons.OK, MessageBoxIcon.Warning)
        txtWCSKILL.Focus()
        Exit Sub
    End If

    Using cmd As New OleDbCommand("SELECT * FROM tblWeightingCriteria")
        Using conn As New OleDbConnection(My.Settings.TEST4)
            conn.Open()

            Dim rstData As New DataTable
            rstData.Load(cmd.ExecuteReader)

            If rstData.Rows.Count > 0 Then
                MessageBox.Show("One Record Already Exist!", "Report Status",
                MessageBoxButtons.OK, MessageBoxIcon.Warning)
                Exit Sub
            End If

            ' does not exist, so create the row
            Dim MyRow As DataRow = rstData.NewRow
            MyRow("WCSKILL") = txtWCSKILL.Text
            MyRow("WCEXPERIENCE") = txtWCSKILL.Text
            MyRow("WCAPPEARANCE") = txtWCAPPEARANCE.Text
            MyRow("WCEDUCATION") = txtWCEDUCATION.Text
            rstData.Rows.Add(MyRow)
            Dim da As New OleDbDataAdapter(cmd)
            Dim daU As New OleDbCommandBuilder(da)
            da.Update(rstData)
        End Using
    End Using

Note that we ALWAYS assume the connection was and will be and has been closed. If you get a error about the connection open already, then we can assume you have a bug in your code.

Now, it is VERY possible that you need/want to update the 1 row with the new values, but create the row if ZERO rows exist. Thus, we can use the above same code for a insert, or a update.

So, the code then becomes this:

(logic: if no rows, create + then edit) (if existing row, then edit)_

    If txtWCSKILL.Text = "" Or
        txtWCEXPERIENCE.Text = "" Or
        txtWCAPPEARANCE.Text = "" Or
        txtWCEDUCATION.Text = "" Then
        MessageBox.Show("WSKILL,WEXPERIENCE,WAPPEARANCE,WEDUCATION cannot be empty.", "Report Status",
            MessageBoxButtons.OK, MessageBoxIcon.Warning)
        txtWCSKILL.Focus()
        Exit Sub
    End If

    Using cmd As New OleDbCommand("SELECT * FROM tblWeightingCriteria")
        Using conn As New OleDbConnection(My.Settings.TEST4)
            conn.Open()

            Dim rstData As New DataTable
            rstData.Load(cmd.ExecuteReader)

            Dim MyRow As DataRow
            If rstData.Rows.Count > 0 Then
                ' row does not exist, so create it
                MyRow = rstData.NewRow
            Else
                ' row eixsts, so we will modify this existing row
                MyRow = rstData.Rows(0)
            End If

            MyRow("WCSKILL") = txtWCSKILL.Text
            MyRow("WCEXPERIENCE") = txtWCSKILL.Text
            MyRow("WCAPPEARANCE") = txtWCAPPEARANCE.Text
            MyRow("WCEDUCATION") = txtWCEDUCATION.Text
            If rstData.Rows.Count = 0 Then
                rstData.Rows.Add(MyRow)
            End If

            Dim da As New OleDbDataAdapter(cmd)
            Dim daU As New OleDbCommandBuilder(da)
            da.Update(rstData)

        End Using
    End Using

And note in above code, we CREATE the connection object each time. You have to change my.Settings.TEST4 to your connecting that you build and placed in the application settings (since you not going to type in connecting string in the code, as that too difficult to maintain).