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