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
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.