-
Feb 6th, 2018, 08:39 AM
#1
Thread Starter
Junior Member
Access Database field not getting updated, no error?
I am inserting a value into a field of a table and if same customer already exists in the table then update that field instead of having duplicate entries.
Data being inserted successfully at first entry but not updating after more entries. Table only contains first inserted data.
This is my code. I think problem would be in my update method but i couldn't figure out.
Code:
Public Sub checkClient(ByVal cName As String, ByVal total1 As String, ByVal total2 As String)
Using connCheck As New OleDbConnection(myGlobals.getDBPath)
connCheck.Open()
Using cmdCheck As New OleDbCommand("SELECT * FROM " & bookTable & " WHERE CustomerName=@Name", connCheck)
cmdCheck.Parameters.AddWithValue("@Name", cName)
Dim reader As OleDbDataReader = cmdCheck.ExecuteReader
If reader.Read = True Then
updateClientBook(cName, total1, total2)
MsgBox("updated " + cName + " pos1: " + total1 + "pos2: " + total2)
Else
insertClientBook(cName, total1, total2)
End If
End Using
End Using
End Sub
Public Sub insertClientBook(ByVal cName As String, ByVal total1 As Integer, ByVal total2 As Integer)
Using connInsert As New OleDbConnection(myGlobals.getDBPath)
connInsert.Open()
Using cmdInsert As New OleDbCommand("INSERT INTO " & bookTable & " (CustomerName, posTeam1, posTeam2) VALUES (@Name, @p1, @p2)", connInsert)
cmdInsert.Parameters.AddWithValue("@Name", cName)
cmdInsert.Parameters.AddWithValue("@p1", total1)
cmdInsert.Parameters.AddWithValue("@p2", total2)
cmdInsert.ExecuteNonQuery()
End Using
End Using
End Sub
Public Sub updateClientBook(ByVal cName As String, ByVal total1 As Integer, ByVal total2 As Integer)
Using conn As New OleDbConnection(myGlobals.getDBPath)
Using cmd As New OleDbCommand("UPDATE " & bookTable & " SET posTeam1 = @p1, posTeam2 = @p2 WHERE CustomerName = @Name", conn)
conn.Open()
cmd.Parameters.AddWithValue("@Name", cName)
cmd.Parameters.AddWithValue("@p1", total1)
cmd.Parameters.AddWithValue("@p2", total2)
cmd.ExecuteNonQuery()
End Using
End Using
End Sub
-
Feb 6th, 2018, 11:17 AM
#2
Re: Access Database field not getting updated, no error?
You can do that faster, but that's not the issue, so I'll just mention it.
The UpdateNonQuery method returns an integer, which is the number of rows affected. The first thing to do is to get that return and have a look at it. If it is returning 0, then you know that no rows match the criteria. I think you'd be pretty surprised by that, but you have to start somewhere. After all, what if it returned 1? In that case, then the DB IS being updated, so you'd have to ask why you aren't seeing it. Thus you see, whether it returns 0 or 1, you have a pretty good mystery either way, so that's the first thing to look at. You need to know what mystery you are really trying to solve.
My usual boring signature: Nothing
-
Feb 6th, 2018, 03:56 PM
#3
Re: Access Database field not getting updated, no error?
Binding is your friend, he wants to help you.
Code:
Imports System.Data.OleDb
Public Class AccessInsertUpdate
Dim dt As New DataTable
Dim bs As New BindingSource
Private Sub AccessInsertUpdate_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Using conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=C:\DATA\Access\AccessDB.accdb;")
Using DA As New OleDbDataAdapter("SELECT * FROM Customers", conn)
DA.MissingSchemaAction = MissingSchemaAction.AddWithKey
DA.FillSchema(dt, SchemaType.Source)
DA.Fill(dt)
bs.DataSource = dt
DataGridView1.DataSource = bs
End Using
End Using
End Sub
Private Sub ButtonInsertUpdate_Click(sender As Object, e As EventArgs) Handles ButtonInsertUpdate.Click
Dim FindIdx As Integer = bs.Find("CustName", TextBoxCustName.Text)
If FindIdx >= 0 Then
'You has this customer lets update him
CType(bs(FindIdx), DataRowView)("CustInfos") = TextBoxInfos.Text
bs.EndEdit()
Else
'You dont has this customer lets insert
bs.AddNew()
CType(bs.Current, DataRowView)("CustName") = TextBoxCustName.Text
CType(bs.Current, DataRowView)("CustInfos") = TextBoxInfos.Text
bs.EndEdit()
End If
Using conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=C:\DATA\Access\AccessDB.accdb;")
Using DA As New OleDbDataAdapter("SELECT * FROM Customers", conn)
Dim cb As New OleDbCommandBuilder(DA)
cb.QuotePrefix = "["
cb.QuoteSuffix = "]"
DA.Update(dt)
dt.Rows.Clear()
DA.Fill(dt)
End Using
End Using
End Sub
End Class
-
Feb 6th, 2018, 07:43 PM
#4
Thread Starter
Junior Member
Re: Access Database field not getting updated, no error?
Originally Posted by Shaggy Hiker
You can do that faster, but that's not the issue, so I'll just mention it.
The UpdateNonQuery method returns an integer, which is the number of rows affected. The first thing to do is to get that return and have a look at it. If it is returning 0, then you know that no rows match the criteria. I think you'd be pretty surprised by that, but you have to start somewhere. After all, what if it returned 1? In that case, then the DB IS being updated, so you'd have to ask why you aren't seeing it. Thus you see, whether it returns 0 or 1, you have a pretty good mystery either way, so that's the first thing to look at. You need to know what mystery you are really trying to solve.
Shaggy Hiker, I got that returned number and that is 0. It means no row is being updated. Can you suggest what would be wrong with my update method? Anything wrong in my sql update query command or some other stuff? Because only sql update query command is different than insert method and insert method is perfectly working. Any help would be greatly appreciated
-
Feb 6th, 2018, 09:26 PM
#5
Re: Access Database field not getting updated, no error?
Well, it looks fairly good, but there is one issue: OleDB suggests an Access database. If that is the case, then the problem may well be the fact that named parameters mean nothing to that database. It doesn't care what name you give to the parameters, it puts them into the query in the order that you supply them, without regard for the name. In that case, you are passing the parameters in the order Name, total1, total2, but the query uses them in the order total1, total2, Name.
Try changing the order that you supply the parameters to the order in which the query would insert them. Ignore the parameter names. I always use ? for parameters in Access queries, because the name is meaningless. This would not be the case for other DB engines, such as SQL Server, but it is the case for Access, and possibly some others.
My usual boring signature: Nothing
-
Feb 6th, 2018, 10:48 PM
#6
Thread Starter
Junior Member
Re: Access Database field not getting updated, no error?
Shaggy Hiker, I changed the order as total1, total2 and name. Its working now. Thanx. BTW, I Wonder why naming parameters doesn't work as it suppose to be.
-
Feb 7th, 2018, 07:01 AM
#7
Re: Access Database field not getting updated, no error?
Originally Posted by Terrybogard911
Shaggy Hiker, I changed the order as total1, total2 and name. Its working now. Thanx. BTW, I Wonder why naming parameters doesn't work as it suppose to be.
OleDb/ms-access only has ordinal positioned parameters unlike SQL-Server which has named parameters. As Shaggy indicated he uses ? as the name does not matter.
If you look at this code sample I used named parameters but note they are in the exact order of the Values in the INSERT. Also it returns the newly add records key.
Code:
Public Function AddNewRow(
ByVal pName As String,
ByVal pContact As String,
ByVal pContactTitle As String,
ByRef pIdentfier As Integer) As Boolean
Dim Success As Boolean = True
Try
Using cn As New OleDbConnection(Builder.ConnectionString)
Using cmd As New OleDbCommand With {.Connection = cn}
cmd.CommandText =
<SQL>
INSERT INTO Customer
(
CompanyName,
ContactName,
ContactTitle
)
Values
(
@CompanyName,
@ContactName,
@ContactTitle
)
</SQL>.Value
cmd.Parameters.AddWithValue("@CompanyName", pName)
cmd.Parameters.AddWithValue("@ContactName", pContact)
cmd.Parameters.AddWithValue("@ContactTitle", pContactTitle)
cn.Open()
cmd.ExecuteNonQuery()
cmd.CommandText = "Select @@Identity"
pIdentfier = CInt(cmd.ExecuteScalar)
End Using
End Using
Catch ex As Exception
mHasException = True
mLastException = ex
Success = False
End Try
Return Success
End Function
-
Feb 7th, 2018, 07:20 AM
#8
Re: Access Database field not getting updated, no error?
Originally Posted by Terrybogard911
I Wonder why naming parameters doesn't work as it suppose to be.
It is working as it's supposed to. Parameters names are for your reference only. They are supposed to be ignored by the database engine and they were.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|