-
May 7th, 2019, 07:46 AM
#1
Thread Starter
Member
ADD,DELETE,UPDATE the records in the access database
Hi All,
I have 7 tables in my access database. i wrote a code to select the data table using the combobox and to display the selected table in the datagridview.
Now i need to add,edit or update, delete the data into the selected tables. In which the text boxes are common to the all the tables, for ex: if i select the table "Capacitor" in the combobox, whatever the data i entered into the textboxes will go to the "capacitor" tables, likewise other tables. Please anyone help me to resolve this code, i am struck in it for long time, as i am new to vb. Below is my code.
Public Class Form1
'To get the tables in the combobox
Dim cnn As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection()
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
cnn.ConnectionString = ("ConnectionString")
cnn.Open()
Me.ComboBox1.DisplayMember = "TABLE_NAME"
Dim restrictions() As String = New String(3) {}
restrictions(3) = "Table"
Me.ComboBox1.DataSource = cnn.GetSchema("Tables", restrictions)
BindGrid()
End Sub
'To display the selected table in the datagridview
Private Sub BindGrid()
Dim cnn As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection()
cnn.ConnectionString = ("ConnectionString")
Dim con As New OleDb.OleDbConnection
Dim sSql As String = "Select * From [" & ComboBox1.Text & " ]"
Dim cmd As New OleDb.OleDbCommand(sSql, cnn)
cmd.CommandType = CommandType.Text
Dim sda As New OleDb.OleDbDataAdapter(cmd)
Dim dt As New DataTable()
sda.Fill(dt)
DataGridView1.DataSource = dt
End Sub
Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
BindGrid()
End Sub
Private Sub btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd.Click
If ComboBox1.Text = "Capacitor" Then
CapacitorBindingsource.AddNew()
End If
End Sub
Private Sub btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click
If ComboBox1.Text = "Capacitor" Then
CapacitorBindingsource.Removecurrent()
End If
End Sub
End Class
Last edited by Ashwin975; May 8th, 2019 at 12:21 AM.
-
May 7th, 2019, 08:55 AM
#2
Re: ADD,DELETE,UPDATE the records in the access database
Please format your code snippets for readability.
vb.net Code:
Imports System.Data.OleDb Public Class Form1 'To get the tables in the combobox Dim cnn As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection() Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'TODO: This line of code loads data into the 'Mentor_DX_Library_DatabaseDataSet.Capacitor' table. You can move, or remove it, as needed. Me.CapacitorTableAdapter.Fill(Me.Mentor_DX_Library_DatabaseDataSet.Capacitor) cnn.ConnectionString = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source = C:\Users\ashwin_seshasrinivas\Desktop\odbc\Edited database\Mentor_DX_Library_Database.accdb;Persist Security Info=False;") cnn.Open() Me.ComboBox1.DisplayMember = "TABLE_NAME" Dim restrictions() As String = New String(3) {} restrictions(3) = "Table" Me.ComboBox1.DataSource = cnn.GetSchema("Tables", restrictions) BindGrid() End Sub 'To display the selected table in the datagridview Private Sub BindGrid() Dim cnn As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection() cnn.ConnectionString = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source = C:\Users\ashwin_seshasrinivas\Desktop\odbc\Edited database\Mentor_DX_Library_Database.accdb;Persist Security Info=False;") Dim con As New OleDb.OleDbConnection Dim sSql As String = "Select * From [" & ComboBox1.Text & " ]" Dim cmd As New OleDb.OleDbCommand(sSql, cnn) cmd.CommandType = CommandType.Text Dim sda As New OleDb.OleDbDataAdapter(cmd) Dim dt As New DataTable() sda.Fill(dt) DataGridView1.DataSource = dt End Sub ' Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click 'BindGrid() 'End Sub Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged BindGrid() ' If ComboBox1.Text = "Capacitor" Then ' End If End Sub Private Sub btnpassword_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnpassword.Click TabControl1.Visible = False If txtUsername.Text = "Vikingidc" And txtPassword.Text = "Viking123" Then GroupBox1.Visible = False TabControl1.Visible = True Else MessageBox.Show("You have entered incorrect login crendentials") 'MessageBoxButtons.OK, MessageBoxIcon.Question ' TabControl1.Visible = False txtPassword.Clear() txtUsername.Clear() txtUsername.Focus() End If End Sub Private Sub btnreset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnreset.Click txtPassword.Clear() txtUsername.Clear() End Sub Private Sub btnend_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnend.Click Dim result As String '= MessageBox.Show("Are you sure you would like to exit?", MessageBoxButtons.YesNo) ',MessageBoxButtons.YesNo , MessageBoxIcon.Question result = MsgBox("Are you sure you would like to exit?", vbYesNo) If result = DialogResult.Yes Then Application.Exit() End If End Sub Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click On Error GoTo SaveErr If ComboBox1.Text = "Capacitor" Then CapacitorBindingsource.EndEdit() CapacitorTableAdapter.Update(Mentor_DX_Library_DatabaseDataSet.Capacitor) End If MessageBox.Show("Saved") SaveErr: Exit Sub End Sub Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click Me.txtdes.Text = "" Me.txtrohs.Text = "" Me.txtmanf.Text = "" Me.txtmanfpn.Text = "" Me.txtagile.Text = "" Me.txttype.Text = "" Me.txtpack.Text = "" Me.txtvalue.Text = "" Me.txtvoltage.Text = "" Me.txttolerance.Text = "" Me.txttemp.Text = "" Me.txtpartnum.Text = "" Me.txtcell.Text = "" Me.txtsym.Text = "" Me.txtagile.Tag = "" End Sub Private Sub btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd.Click If ComboBox1.Text = "Capacitor" Then CapacitorBindingsource.AddNew() End If End Sub Private Sub btnedit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnedit.Click End Sub Private Sub btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click End Sub End Class
Also, please only post relevant code. You've got two empty event handlers in there so they're obviously not relevant. How much of the rest has nothing to do with the question? The more difficult you make it for us, the less likely you will get the help you want.
-
May 7th, 2019, 09:28 PM
#3
Re: ADD,DELETE,UPDATE the records in the access database
Here is an example,
Code:
Imports System.Data.OleDb
Public Class Form4
Private con As New OleDbConnection(My.Settings.waterConnectionString)
Private da As New OleDbDataAdapter()
Private dt As New DataTable
Private Sub Form4_Load(sender As Object, e As EventArgs) Handles Me.Load
Try
con.Open()
Dim restrictions() As String = New String(3) {}
restrictions(3) = "Table"
Me.ComboBox1.DisplayMember = "TABLE_NAME"
Me.ComboBox1.DataSource = con.GetSchema("Tables", restrictions)
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Sub
Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
Me.DataGridView1.DataSource = Nothing
dt = New DataTable
da.SelectCommand = New OleDbCommand("Select * from " & Me.ComboBox1.Text, con)
da.Fill(dt)
Me.DataGridView1.DataSource = dt
End Sub
Private Sub AddButton_Click(sender As Object, e As EventArgs) Handles AddButton.Click
Dim cmdbldr As New OleDbCommandBuilder(da)
Dim row As DataRow = dt.NewRow
row("Field1") = Me.Field1TextBox.Text
row("Field2") = Me.Field2TextBox.Text
dt.Rows.Add(row)
da.Update(dt)
End Sub
End Class
-
May 7th, 2019, 11:25 PM
#4
Thread Starter
Member
Re: ADD,DELETE,UPDATE the records in the access database
HI wes4dbt,
The code doesn't work. Because i have already get the tables and listed through the bindgrid function. I am ending with the error, can you please modify in the same code if possible. Please help me to resolve this one.
-
May 8th, 2019, 12:04 AM
#5
Thread Starter
Member
Re: ADD,DELETE,UPDATE the records in the access database
imcilhinney, i have removed the unwanted code from the post and made code readable. Thank you for sorting out the mistake.
-
May 8th, 2019, 01:26 AM
#6
Re: ADD,DELETE,UPDATE the records in the access database
First, for me the code does work. It's your job to adapt it to work with your data
Second, just saying "I am ending with the error" isn't helpful at all. What is the error? What line of code causes the error? Post your current code.
-
May 8th, 2019, 01:54 AM
#7
Thread Starter
Member
Re: ADD,DELETE,UPDATE the records in the access database
Error in this statement, I dont know what would be the issue is.
Private Sub btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd.Click
Dim cmdbldr As New OleDbCommandBuilder(da)
Dim row As DataRow = dt.NewRow
row("Agile number") = Me.txtagile.Text
row("Description") = Me.txtdes.Text
dt.Rows.Add(row)
da.Update(dt) --> Syntax error in INSERT INTO statement
End Sub
-
May 8th, 2019, 03:05 AM
#8
Re: ADD,DELETE,UPDATE the records in the access database
Originally Posted by Ashwin975
Error in this statement, I dont know what would be the issue is.
Private Sub btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd.Click
Dim cmdbldr As New OleDbCommandBuilder(da)
Dim row As DataRow = dt.NewRow
row("Agile number") = Me.txtagile.Text
row("Description") = Me.txtdes.Text
dt.Rows.Add(row)
da.Update(dt) --> Syntax error in INSERT INTO statement
End Sub
You have a space in your column name, which is bad. You should always avoid doing so if at all possible. Just as you can't have an identifier (type, member or variable name) that contains spaces in VB, so you can't in SQL either. If it's within your power to do so, fix your database and remove all spaces in column names, i.e. use 'AgileNumber' or 'Agile_number' as you would in VB rather than 'Agile number'. If that's not your call to make then you must wrap your identifiers in brackets. You can make a command builder do that by setting its QuotePrefix and QuoteSuffix properties.
-
May 8th, 2019, 04:06 AM
#9
Thread Starter
Member
Re: ADD,DELETE,UPDATE the records in the access database
I have changed them without the spaces, still i am getting the same error.
Also if i add the data and clicked the save button, the data didn't saved.
Private Sub btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd.Click
Dim cmdbldr As New OleDbCommandBuilder(da)
Dim row As DataRow = dt.NewRow
If ComboBox1.Text = "Capacitor" Then
row("Agile_number") = Me.txtagile.Text
row("Description") = Me.txtdes.Text
row("Rohs") = Me.txtrohs.Text
row("Manufacturer") = Me.txtmanf.Text
row("Manufacturer_Part_number") = Me.txtmanfpn.Text
'row("Description") = Me.txtagile.Text
row("Type") = Me.txttype.Text
row("Pack_type") = Me.txtpack.Text
row("Value") = Me.txtvalue.Text
row("Voltage") = Me.txtvoltage.Text
row("Tolerance") = Me.txttolerance.Text
row("Temp_Coefficient") = Me.txttemp.Text
row("Part_number") = Me.txtpartnum.Text
row("Cell_Name") = Me.txtcell.Text
row("Symbol") = Me.txtsym.Text
End If
dt.Rows.Add(row)
da.Update(dt) -->Syntax error in INSERT INTO statement.(Also if i removed this line and run, i can able to add the data but it is not saved in the database)
End Sub
For the save Process
Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click
On Error GoTo SaveErr
MentorDXLibraryDatabaseDataSetBindingSource.EndEdit()
MessageBox.Show("Saved")
SaveErr:
Exit Sub
End Sub
-
May 8th, 2019, 05:34 AM
#10
Re: ADD,DELETE,UPDATE the records in the access database
Originally Posted by Ashwin975
Also if i removed this line and run, i can able to add the data but it is not saved in the database
Of course it's not saved, because that's the line that does the saving.
If you're still getting a syntax error in your without any spaces in the column names then at least one of the columns must be a reserved word. Again, just as you can't use a VB keyword as an identifier without wrapping it in brackets, so you can't do it in SQL either. You can check the documentation for your database to see what reserved words there are but the likely candidates seem to be "Type" and "Value". Again, avoid reserved words in table or column names if you can but there are times where they are the natural choice. In such cases, you just need to escape them in your SQL. As I said before, you can do that by setting the QuotePrefix and QuoteSuffix properties of your command builder.
-
May 8th, 2019, 07:10 AM
#11
Thread Starter
Member
Re: ADD,DELETE,UPDATE the records in the access database
Thank you very much, it works now. i have added the commandbuilder prefix and suffix and it got worked now.
Dim cb As New OleDb.OleDbCommandBuilder(da)
cb.QuotePrefix = "["
cb.QuoteSuffix = "]"
da.Update(dt)
For the deletion below code deleting the current row, if i open again it shows it deleted row ?
Private Sub btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click
Dim inde As Integer
inde = DataGridView1.CurrentCell.RowIndex
Dim result As String '= MessageBox.Show("Are you sure you would like to exit?"
result = MsgBox("Are you sure you would like to delete current row?", vbYesNo)
If result = DialogResult.Yes Then
DataGridView1.Rows.RemoveAt(inde)
End If
End Sub
-
May 8th, 2019, 09:34 AM
#12
Re: ADD,DELETE,UPDATE the records in the access database
If you're not already, bind your DataTable to your DataGridView via a BindingSource. To delete the current row, you call RemoveCurrent on that BindingSource. That will flag the underlying DataRow as Deleted, which what you're doing now does not. That way, the corresponding record will be deleted from the database when you call Update.
-
May 8th, 2019, 04:54 PM
#13
Re: ADD,DELETE,UPDATE the records in the access database
I updated my example to include Deletes and Edits, there are lots of changes so review it carefully.
Code:
Imports System.ComponentModel
Imports System.Data.OleDb
Public Class Form4
Private con As New OleDbConnection(My.Settings.waterConnectionString)
Private da As New OleDbDataAdapter()
Private dt As New DataTable
Private cmdbldr As OleDbCommandBuilder
Private bs As New BindingSource
Private Sub Form4_Load(sender As Object, e As EventArgs) Handles Me.Load
Try
con.Open()
Dim restrictions() As String = New String(3) {}
restrictions(3) = "Table"
Me.ComboBox1.DisplayMember = "TABLE_NAME"
Me.ComboBox1.DataSource = con.GetSchema("Tables", restrictions)
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Sub
Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
Me.DataGridView1.DataSource = Nothing
bs.DataSource = Nothing
dt = New DataTable
da.SelectCommand = New OleDbCommand("Select * from " & Me.ComboBox1.Text, con)
da.Fill(dt)
bs.DataSource = dt
Me.DataGridView1.DataSource = bs
cmdbldr = New OleDbCommandBuilder(da)
End Sub
Private Sub AddButton_Click(sender As Object, e As EventArgs) Handles AddButton.Click
Dim row As DataRow = dt.NewRow
row("Field1") = Me.Field1TextBox.Text
row("Field2") = Me.Field2TextBox.Text
dt.Rows.Add(row)
SaveData()
End Sub
Private Sub DeleteButton_Click(sender As Object, e As EventArgs) Handles DeleteButton.Click
bs.RemoveCurrent()
SaveData()
End Sub
Private Sub SaveData()
bs.EndEdit()
da.Update(dt)
End Sub
Private Sub Form4_Closing(sender As Object, e As CancelEventArgs) Handles Me.Closing
If bs.DataSource IsNot Nothing Then
bs.EndEdit()
da.Update(dt)
End If
End Sub
End Class
As jmc said in post #2, format your code so we can read it easily. You do that by clicking either "VB" or "#" icon from the toolbat and then insert your code between the code tags.
-
May 10th, 2019, 12:02 AM
#14
Thread Starter
Member
Re: ADD,DELETE,UPDATE the records in the access database
yeah right now i can able to the add data. for the deleting i facing this error,
Private Sub savedata()
bs.EndEdit()
da.Update(dt) --> Dynamic SQL generation for the DeleteCommand is not supported against a SelectCommand that does not return any key column information.
End Sub
Also in the database the table which is having more than 1000 rows, 1001 is in the as first row, the actual row no.1 is below that. After adding row in the database, i couldn't see the rows updated. once i close that and open again, i can see the added row in the database, for these type of tables.
-
May 10th, 2019, 12:09 AM
#15
Re: ADD,DELETE,UPDATE the records in the access database
Please stop posting unformatted code snippets. Have you not noticed how everyone else's code is much more readable, primarily due to indenting. If you can make something bold then you can format it as text.
-
May 10th, 2019, 12:15 AM
#16
Re: ADD,DELETE,UPDATE the records in the access database
Originally Posted by Ashwin975
yeah right now i can able to the add data. for the deleting i facing this error,
Private Sub savedata()
bs.EndEdit()
da.Update(dt) --> Dynamic SQL generation for the DeleteCommand is not supported against a SelectCommand that does not return any key column information.
End Sub
The clue is in the error message. In order to be able to use a command builder, your query must involve a single table and retrieve the primary key for that table. You may also have to set the MissingSchemaAction property of the data adapter to AddWithKey, although I'm not 100% sure that that is necessary.
Originally Posted by Ashwin975
Also in the database the table which is having more than 1000 rows, 1001 is in the as first row, the actual row no.1 is below that. After adding row in the database, i couldn't see the rows updated. once i close that and open again, i can see the added row in the database, for these type of tables.
Calling Update on a data adapter will simply save the changes in the specified DataTable to the database. If that DataTable is bound to a grid or something then you are already looking at the changes. If you have some other data displayed elsewhere in your app, even if it is from the same source, you won't see that magically change. Data you display only exists locally, even if you retrieved it from a remote location. Changes to that remote location won't be automatically reflected locally. If you want to update the local data then you have to do so explicitly, which is what happens when you close and open anew.
-
May 10th, 2019, 05:10 AM
#17
Thread Starter
Member
Re: ADD,DELETE,UPDATE the records in the access database
jim, even if i changed the primary key in the tables. i'm end up with the error. I don't know what add schema you mean it. can Please edit in the code, if possible.
-
May 10th, 2019, 07:06 AM
#18
Re: ADD,DELETE,UPDATE the records in the access database
Originally Posted by Ashwin975
jim, even if i changed the primary key in the tables. i'm end up with the error.
Then you did it wrong. If you're not going to tell us what you actually did then we can't tell you what'
Originally Posted by Ashwin975
can Please edit in the code, if possible.
I've told you what to do. Just do it.
your query must involve a single table and retrieve the primary key for that table. You may also have to set the MissingSchemaAction property of the data adapter to AddWithKey
There's nothing complicated in that. The first part isn't a code change and the second part is setting one property.
-
May 10th, 2019, 01:28 PM
#19
Re: ADD,DELETE,UPDATE the records in the access database
What field is your Primary Key?
It's not uncommon for data not to be displayed in the same order as they are in the database. If you want to retrieve them in a certain order then you use the Order By clause in the Select statement.
Code:
Select fld1, fld2 from someTable ORDER BY fld2
-
May 14th, 2019, 01:46 AM
#20
Thread Starter
Member
Re: ADD,DELETE,UPDATE the records in the access database
Code:
Private Sub btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click
bs.RemoveCurrent()
savedata()
End Sub
Private Sub Form1_FormClosing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
If bs.DataSource IsNot Nothing Then
bs.EndEdit()
da.Update(dt)
End If
Private Sub savedata()
bs.EndEdit()
da.Update(dt)
End Sub
Private Sub btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd.Click
Dim cmdbldr As New OleDbCommandBuilder(da)
Dim row As DataRow = dt.NewRow
If ComboBox1.Text = "Capacitor" Then
row("Agile_number") = Me.txtagile.Text
row("Description") = Me.txtdes.Text
row("Rohs") = Me.txtrohs.Text
row("Manufacturer") = Me.txtmanf.Text
row("Manufacturer_Part_number") = Me.txtmanfpn.Text
'row("Description") = Me.txtagile.Text
row("Type") = Me.txttype.Text
row("Pack_type") = Me.txtpack.Text
row("Value") = Me.txtvalue.Text
row("Voltage") = Me.txtvoltage.Text
row("Tolerance") = Me.txttolerance.Text
row("Temp_Coefficient") = Me.txttemp.Text
row("Part_number") = Me.txtpartnum.Text
row("Cell_Name") = Me.txtcell.Text
row("Symbol") = Me.txtsym.Text
End If
dt.Rows.Add(row)
da.Update(dt)
savedata()
End Sub
Above is my code. I set the primary key as the ID in the table, Applies for all the tables. If i try to change that, i cannot able to save the database in access.
-
May 14th, 2019, 01:12 PM
#21
Re: ADD,DELETE,UPDATE the records in the access database
Do you retrieve the ID field with your Select statement?
This shouldn't be in the Add routine,
Code:
Dim cmdbldr As New OleDbCommandBuilder(da)
Not if your trying to follow my example.
You have to have more code than what you posted.
-
May 15th, 2019, 06:30 AM
#22
Thread Starter
Member
Re: ADD,DELETE,UPDATE the records in the access database
Code:
Imports System.Data.OleDb
Public Class Form1
'To get the tables in the combobox
Dim cnn As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection()
Private da As New OleDbDataAdapter
Private dt As New DataTable
Private bs As New BindingSource
Private cmdbldr As OleDbCommandBuilder
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'Mentor_DX_Library_DatabaseDataSet.Inductor' table. You can move, or remove it, as needed.
Me.InductorTableAdapter.Fill(Me.Mentor_DX_Library_DatabaseDataSet.Inductor)
'TODO: This line of code loads data into the 'Mentor_DX_Library_DatabaseDataSet.Connector' table. You can move, or remove it, as needed.
Me.ConnectorTableAdapter.Fill(Me.Mentor_DX_Library_DatabaseDataSet.Connector)
'TODO: This line of code loads data into the 'Mentor_DX_Library_DatabaseDataSet.Resistor' table. You can move, or remove it, as needed.
Me.ResistorTableAdapter.Fill(Me.Mentor_DX_Library_DatabaseDataSet.Resistor)
cnn.ConnectionString = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source = C:\Users\ashwin_seshasrinivas\Desktop\odbc\Edited database\Mentor_DX_Library_Database.accdb;Persist Security Info=False;")
Try
cnn.Open()
Dim restrictions() As String = New String(3) {}
restrictions(3) = "Table"
Me.ComboBox1.DisplayMember = "TABLE_NAME"
Me.ComboBox1.DataSource = cnn.GetSchema("Tables", restrictions)
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
cnn.Close()
End Sub
'To display the selected table in the datagridview
' Private Sub BindGrid()
' End Sub
' Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'BindGrid()
'End Sub
Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
Dim sSql As String = "Select * From [" & ComboBox1.Text & " ]"
Me.DataGridView1.DataSource = Nothing
dt = New DataTable
da.SelectCommand = New OleDbCommand(sSql, cnn)
da.Fill(dt)
bs.DataSource = dt
Me.DataGridView1.DataSource = bs
cmdbldr = New OleDbCommandBuilder(da)
End Sub
Private Sub btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd.Click
Dim cmdbldr As New OleDbCommandBuilder(da)
Dim row As DataRow = dt.NewRow
If ComboBox1.Text = "Capacitor" Then
row("Agile_number") = Me.txtagile.Text
row("Description") = Me.txtdes.Text
row("Rohs") = Me.txtrohs.Text
row("Manufacturer") = Me.txtmanf.Text
row("Manufacturer_Part_number") = Me.txtmanfpn.Text
'row("Description") = Me.txtagile.Text
row("Type") = Me.txttype.Text
row("Pack_type") = Me.txtpack.Text
row("Value") = Me.txtvalue.Text
row("Voltage") = Me.txtvoltage.Text
row("Tolerance") = Me.txttolerance.Text
row("Temp_Coefficient") = Me.txttemp.Text
row("Part_number") = Me.txtpartnum.Text
row("Cell_Name") = Me.txtcell.Text
row("Symbol") = Me.txtsym.Text
ElseIf ComboBox1.Text = "Resistor" Then
row("Agile_number") = Me.txtagile.Text
row("Description") = Me.txtdes.Text
row("Rohs") = Me.txtrohs.Text
row("Manufacturer") = Me.txtmanf.Text
row("Manufacturer_Part_number") = Me.txtmanfpn.Text
'row("Description") = Me.txtagile.Text
row("Type") = Me.txttype.Text
row("Pack_type") = Me.txtpack.Text
row("Value") = Me.txtvalue.Text
'row("Voltage") = Me.txtvoltage.Text
row("Tolerance") = Me.txttolerance.Text
'row("Temp_Coefficient") = Me.txttemp.Text
row("Part_number") = Me.txtpartnum.Text
row("Cell_Name") = Me.txtcell.Text
row("Symbol") = Me.txtsym.Text
ElseIf ComboBox1.Text = "Connector" Then
row("Agile_number") = Me.txtagile.Text
row("Description") = Me.txtdes.Text
row("Rohs") = Me.txtrohs.Text
row("Manufacturer") = Me.txtmanf.Text
row("Manufacturer_Part_number") = Me.txtmanfpn.Text
'row("Description") = Me.txtagile.Text
'row("Type") = Me.txttype.Text
'row("Pack_type") = Me.txtpack.Text
'row("Value") = Me.txtvalue.Text
'row("Voltage") = Me.txtvoltage.Text
'row("Tolerance") = Me.txttolerance.Text
'row("Temp_Coefficient") = Me.txttemp.Text
row("Part_number") = Me.txtpartnum.Text
row("Cell_Name") = Me.txtcell.Text
row("Symbol") = Me.txtsym.Text
ElseIf ComboBox1.Text = "Inductor" Then
row("Agile_number") = Me.txtagile.Text
row("Description") = Me.txtdes.Text
row("Rohs") = Me.txtrohs.Text
row("Manufacturer") = Me.txtmanf.Text
row("Manufacturer_Part_number") = Me.txtmanfpn.Text
'row("Description") = Me.txtagile.Text
'row("Type") = Me.txttype.Text
row("Pack_type") = Me.txtpack.Text
row("Value") = Me.txtvalue.Text
'row("Voltage") = Me.txtvoltage.Text
'row("Tolerance") = Me.txttolerance.Text
'row("Temp_Coefficient") = Me.txttemp.Text
row("Part_number") = Me.txtpartnum.Text
row("Cell_Name") = Me.txtcell.Text
row("Symbol") = Me.txtsym.Text
End If
dt.Rows.Add(row)
'da.FillSchema(dt)
da.Update(dt)
savedata()
End Sub
Private Sub savedata()
bs.EndEdit()
da.Update(dt)
End Sub
Private Sub btnedit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnedit.Click
End Sub
Private Sub btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click
bs.RemoveCurrent()
savedata()
End Sub
Private Sub Form1_FormClosing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
If bs.DataSource IsNot Nothing Then
bs.EndEdit()
da.Update(dt)
End If
End Sub
Private Sub btnpassword_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnpassword.Click
TabControl1.Visible = False
If txtUsername.Text = "Vikingidc" And txtPassword.Text = "Viking123" Then
GroupBox1.Visible = False
TabControl1.Visible = True
Else
MessageBox.Show("You have entered incorrect login crendentials") 'MessageBoxButtons.OK, MessageBoxIcon.Question
' TabControl1.Visible = False
txtPassword.Clear()
txtUsername.Clear()
txtUsername.Focus()
End If
End Sub
Private Sub btnreset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnreset.Click
txtPassword.Clear()
txtUsername.Clear()
End Sub
Private Sub btnend_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnend.Click
Dim result As String '= MessageBox.Show("Are you sure you would like to exit?", MessageBoxButtons.YesNo) ',MessageBoxButtons.YesNo , MessageBoxIcon.Question
result = MsgBox("Are you sure you would like to exit?", vbYesNo)
If result = DialogResult.Yes Then
Application.Exit()
End If
End Sub
Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click
On Error GoTo SaveErr
CapacitorBindingsource.EndEdit()
ResistorBindingSource.EndEdit()
ConnectorBindingSource.EndEdit()
MessageBox.Show("Saved")
SaveErr:
Exit Sub
End Sub
Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
Me.txtdes.Text = ""
Me.txtrohs.Text = ""
Me.txtmanf.Text = ""
Me.txtmanfpn.Text = ""
Me.txtagile.Text = ""
Me.txttype.Text = ""
Me.txtpack.Text = ""
Me.txtvalue.Text = ""
Me.txtvoltage.Text = ""
Me.txttolerance.Text = ""
Me.txttemp.Text = ""
Me.txtpartnum.Text = ""
Me.txtcell.Text = ""
Me.txtsym.Text = ""
Me.txtagile.Tag = ""
End Sub
End Class
this is my full code. i have used the commandbuilder too.
-
May 15th, 2019, 04:13 PM
#23
Re: ADD,DELETE,UPDATE the records in the access database
Modify your savedata method,
Code:
Private Sub SaveData()
bs.EndEdit()
da.Update(dt)
da.Fill(dt)
bs.MoveLast()
End Sub
Remove this from the btnadd,
Code:
Dim cmdbldr As New OleDbCommandBuilder(da)
I'm not sure why you have this,
Code:
'TODO: This line of code loads data into the 'Mentor_DX_Library_DatabaseDataSet.Inductor' table. You can move, or remove it, as needed.
Me.InductorTableAdapter.Fill(Me.Mentor_DX_Library_DatabaseDataSet.Inductor)
'TODO: This line of code loads data into the 'Mentor_DX_Library_DatabaseDataSet.Connector' table. You can move, or remove it, as needed.
Me.ConnectorTableAdapter.Fill(Me.Mentor_DX_Library_DatabaseDataSet.Connector)
'TODO: This line of code loads data into the 'Mentor_DX_Library_DatabaseDataSet.Resistor' table. You can move, or remove it, as needed.
Me.ResistorTableAdapter.Fill(Me.Mentor_DX_Library_DatabaseDataSet.Resistor)
This code does nothing,
Code:
Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click
On Error GoTo SaveErr
CapacitorBindingsource.EndEdit()
ResistorBindingSource.EndEdit()
ConnectorBindingSource.EndEdit()
MessageBox.Show("Saved")
SaveErr:
Exit Sub
End Sub
Last edited by wes4dbt; May 15th, 2019 at 08:23 PM.
-
May 15th, 2019, 10:25 PM
#24
Thread Starter
Member
Re: ADD,DELETE,UPDATE the records in the access database
Code:
Imports System.Data.OleDb
Public Class Form1
'To get the tables in the combobox
Dim cnn As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection()
Private da As New OleDbDataAdapter
Private dt As New DataTable
Private bs As New BindingSource
Private cmdbldr As OleDbCommandBuilder
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'Mentor_DX_Library_DatabaseDataSet.Inductor' table. You can move, or remove it, as needed.
Me.InductorTableAdapter.Fill(Me.Mentor_DX_Library_DatabaseDataSet.Inductor)
'TODO: This line of code loads data into the 'Mentor_DX_Library_DatabaseDataSet.Connector' table. You can move, or remove it, as needed.
Me.ConnectorTableAdapter.Fill(Me.Mentor_DX_Library_DatabaseDataSet.Connector)
'TODO: This line of code loads data into the 'Mentor_DX_Library_DatabaseDataSet.Resistor' table. You can move, or remove it, as needed.
Me.ResistorTableAdapter.Fill(Me.Mentor_DX_Library_DatabaseDataSet.Resistor)
cnn.ConnectionString = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source = C:\Users\ashwin_seshasrinivas\Desktop\odbc\Edited database\Mentor_DX_Library_Database.accdb;Persist Security Info=False;")
Try
cnn.Open()
Dim restrictions() As String = New String(3) {}
restrictions(3) = "Table"
Me.ComboBox1.DisplayMember = "TABLE_NAME"
Me.ComboBox1.DataSource = cnn.GetSchema("Tables", restrictions)
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
cnn.Close()
End Sub
'To display the selected table in the datagridview
' Private Sub BindGrid()
' End Sub
' Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'BindGrid()
'End Sub
Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
Dim sSql As String = "Select * From [" & ComboBox1.Text & " ]"
Me.DataGridView1.DataSource = Nothing
dt = New DataTable
da.SelectCommand = New OleDbCommand(sSql, cnn)
da.Fill(dt)
bs.DataSource = dt
Me.DataGridView1.DataSource = bs
cmdbldr = New OleDbCommandBuilder(da)
End Sub
Private Sub btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd.Click
Dim row As DataRow = dt.NewRow
If ComboBox1.Text = "Capacitor" Then
row("Agile_number") = Me.txtagile.Text
row("Description") = Me.txtdes.Text
row("Rohs") = Me.txtrohs.Text
row("Manufacturer") = Me.txtmanf.Text
row("Manufacturer_Part_number") = Me.txtmanfpn.Text
row("Description") = Me.txtagile.Text
row("Type") = Me.txttype.Text
row("Pack_type") = Me.txtpack.Text
row("Value") = Me.txtvalue.Text
row("Voltage") = Me.txtvoltage.Text
row("Tolerance") = Me.txttolerance.Text
row("Temp_Coefficient") = Me.txttemp.Text
row("Part_number") = Me.txtpartnum.Text
row("Cell_Name") = Me.txtcell.Text
row("Symbol") = Me.txtsym.Text
ElseIf ComboBox1.Text = "Resistor" Then
row("Agile_number") = Me.txtagile.Text
row("Description") = Me.txtdes.Text
row("Rohs") = Me.txtrohs.Text
row("Manufacturer") = Me.txtmanf.Text
row("Manufacturer_Part_number") = Me.txtmanfpn.Text
'row("Description") = Me.txtagile.Text
row("Type") = Me.txttype.Text
row("Pack_type") = Me.txtpack.Text
row("Value") = Me.txtvalue.Text
'row("Voltage") = Me.txtvoltage.Text
row("Tolerance") = Me.txttolerance.Text
'row("Temp_Coefficient") = Me.txttemp.Text
row("Part_number") = Me.txtpartnum.Text
row("Cell_Name") = Me.txtcell.Text
row("Symbol") = Me.txtsym.Text
ElseIf ComboBox1.Text = "Connector" Then
row("Agile_number") = Me.txtagile.Text --> Column 'Agile_number' does not belong to table "This error after running the application but the actually the table has the coloumn"
row("Description") = Me.txtdes.Text
row("Rohs") = Me.txtrohs.Text
row("Manufacturer") = Me.txtmanf.Text
row("Manufacturer_Part_number") = Me.txtmanfpn.Text
row("Part_number") = Me.txtpartnum.Text
row("Cell_Name") = Me.txtcell.Text
row("Symbol") = Me.txtsym.Text
ElseIf ComboBox1.Text = "Inductor" Then
row("Agile_number") = Me.txtagile.Text
row("Description") = Me.txtdes.Text
row("Rohs") = Me.txtrohs.Text
row("Manufacturer") = Me.txtmanf.Text
row("Manufacturer_Part_number") = Me.txtmanfpn.Text
row("Description") = Me.txtagile.Text
row("Type") = Me.txttype.Text
row("Pack_type") = Me.txtpack.Text
row("Value") = Me.txtvalue.Text
row("Voltage") = Me.txtvoltage.Text
row("Tolerance") = Me.txttolerance.Text
row("Temp_Coefficient") = Me.txttemp.Text
row("Part_number") = Me.txtpartnum.Text
row("Cell_Name") = Me.txtcell.Text
row("Symbol") = Me.txtsym.Text
End If
dt.Rows.Add(row)
da.Update(dt) --> Syntax INSERT statement is missing
savedata()
End Sub
Private Sub savedata()
bs.EndEdit()
da.Update(dt)
da.Fill(dt)
bs.MoveLast()
End Sub
Private Sub btnedit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnedit.Click
End Sub
Private Sub btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click
bs.RemoveCurrent()
savedata()
End Sub
Private Sub Form1_FormClosing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
If bs.DataSource IsNot Nothing Then
bs.EndEdit()
da.Update(dt)
End If
End Sub
Private Sub btnpassword_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnpassword.Click
TabControl1.Visible = False
If txtUsername.Text = "Vikingidc" And txtPassword.Text = "Viking123" Then
GroupBox1.Visible = False
TabControl1.Visible = True
Else
MessageBox.Show("You have entered incorrect login crendentials") 'MessageBoxButtons.OK, MessageBoxIcon.Question
' TabControl1.Visible = False
txtPassword.Clear()
txtUsername.Clear()
txtUsername.Focus()
End If
End Sub
Private Sub btnreset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnreset.Click
txtPassword.Clear()
txtUsername.Clear()
End Sub
Private Sub btnend_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnend.Click
Dim result As String '= MessageBox.Show("Are you sure you would like to exit?", MessageBoxButtons.YesNo) ',MessageBoxButtons.YesNo , MessageBoxIcon.Question
result = MsgBox("Are you sure you would like to exit?", vbYesNo)
If result = DialogResult.Yes Then
Application.Exit()
End If
End Sub
Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click
On Error GoTo SaveErr
CapacitorBindingsource.EndEdit()
ResistorBindingSource.EndEdit()
ConnectorBindingSource.EndEdit()
MessageBox.Show("Saved")
SaveErr:
Exit Sub
End Sub
Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
Me.txtdes.Text = ""
Me.txtrohs.Text = ""
Me.txtmanf.Text = ""
Me.txtmanfpn.Text = ""
Me.txtagile.Text = ""
Me.txttype.Text = ""
Me.txtpack.Text = ""
Me.txtvalue.Text = ""
Me.txtvoltage.Text = ""
Me.txttolerance.Text = ""
Me.txttemp.Text = ""
Me.txtpartnum.Text = ""
Me.txtcell.Text = ""
Me.txtsym.Text = ""
Me.txtagile.Tag = ""
End Sub
End Class
I have facing the error, in the specified the code. i cannot able to any data in to my database. Please help.
-
May 16th, 2019, 01:40 PM
#25
Re: ADD,DELETE,UPDATE the records in the access database
One problem I see is you have a field name "Value", this is a reserve word and shouldn't be used as a field name. Access even tells you that when you create the field but you have chosen to ignore the warning. You can still use it if you enclose the field names in brackets, like this
Code:
cmdbldr = New OleDbCommandBuilder(da)
cmdbldr.QuotePrefix = "["
cmdbldr.QuoteSuffix = "]"
-
Jul 10th, 2019, 01:45 AM
#26
Thread Starter
Member
Re: ADD,DELETE,UPDATE the records in the access database
Code:
Imports System.Data.OleDb
Public Class Form1
'To get the tables in the combobox
Dim cnn As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection()
Private da As New OleDbDataAdapter
Private dt As New DataTable
Private bs As New BindingSource
Private cmdbldr As OleDbCommandBuilder
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
cnn.ConnectionString = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source = C:\Users\ashwin_seshasrinivas\Desktop\odbc\Edited database\Mentor_DX_Library_Database.accdb;Persist Security Info=False;")
Try
cnn.Open()
Dim restrictions() As String = New String(3) {}
restrictions(3) = "Table"
Me.ComboBox1.DisplayMember = "TABLE_NAME"
Me.ComboBox1.DataSource = cnn.GetSchema("Tables", restrictions)
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
cnn.Close()
End Sub
'To display the selected table in the datagridview
' Private Sub BindGrid()
' End Sub
' Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'BindGrid()
'End Sub
Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
Dim sSql As String = "Select * From [" & ComboBox1.Text & " ]"
Me.DataGridView1.DataSource = Nothing
dt = New DataTable
da.SelectCommand = New OleDbCommand(sSql, cnn)
da.Fill(dt)
bs.DataSource = dt
Me.DataGridView1.DataSource = bs
cmdbldr = New OleDbCommandBuilder(da)
cmdbldr.QuotePrefix = "["
cmdbldr.QuoteSuffix = "]"
End Sub
Private Sub btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd.Click
Dim row As DataRow = dt.NewRow
cmdbldr = New OleDbCommandBuilder(da)
cmdbldr.QuotePrefix = "["
cmdbldr.QuoteSuffix = "]"
If ComboBox1.Text = "Capacitor" Then
row("Agile number") = Me.txtagile.Text
row("Description") = Me.txtdes.Text
row("Rohs") = Me.txtrohs.Text
row("Manufacturer") = Me.txtmanf.Text
row("Manufacturer Part number") = Me.txtmanfpn.Text
row("Description") = Me.txtagile.Text
row("Type") = Me.txttype.Text
row("Pack type") = Me.txtpack.Text
row("Value") = Me.txtvalue.Text
row("Voltage") = Me.txtvoltage.Text
row("Tolerance") = Me.txttolerance.Text
row("Temp-Coefficient") = Me.txttemp.Text
row("Part number") = Me.txtpartnum.Text
row("Cell Name") = Me.txtcell.Text
row("Symbol") = Me.txtsym.Text
ElseIf ComboBox1.Text = "Resistor" Then
row("Agile_number") = Me.txtagile.Text
row("Description") = Me.txtdes.Text
row("Rohs") = Me.txtrohs.Text
row("Manufacturer") = Me.txtmanf.Text
row("Manufacturer_Part_number") = Me.txtmanfpn.Text
'row("Description") = Me.txtagile.Text
row("Type") = Me.txttype.Text
row("Pack_type") = Me.txtpack.Text
row("Value") = Me.txtvalue.Text
'row("Voltage") = Me.txtvoltage.Text
row("Tolerance") = Me.txttolerance.Text
'row("Temp_Coefficient") = Me.txttemp.Text
row("Part_number") = Me.txtpartnum.Text
row("Cell_Name") = Me.txtcell.Text
row("Symbol") = Me.txtsym.Text
ElseIf ComboBox1.Text = "Connector" Then
row("Agile_number") = Me.txtagile.Text
row("Description") = Me.txtdes.Text
row("Rohs") = Me.txtrohs.Text
row("Manufacturer") = Me.txtmanf.Text
row("Manufacturer_Part_number") = Me.txtmanfpn.Text
'row("Description") = Me.txtagile.Text
'row("Type") = Me.txttype.Text
'row("Pack_type") = Me.txtpack.Text
'row("Value") = Me.txtvalue.Text
'row("Voltage") = Me.txtvoltage.Text
'row("Tolerance") = Me.txttolerance.Text
'row("Temp_Coefficient") = Me.txttemp.Text
row("Part_number") = Me.txtpartnum.Text
row("Cell_Name") = Me.txtcell.Text
row("Symbol") = Me.txtsym.Text
ElseIf ComboBox1.Text = "Inductor" Then
row("Agile_number") = Me.txtagile.Text
row("Description") = Me.txtdes.Text
row("Rohs") = Me.txtrohs.Text
row("Manufacturer") = Me.txtmanf.Text
row("Manufacturer_Part_number") = Me.txtmanfpn.Text
row("Description") = Me.txtagile.Text
row("Type") = Me.txttype.Text
row("Pack_type") = Me.txtpack.Text
row("Value") = Me.txtvalue.Text
row("Voltage") = Me.txtvoltage.Text
row("Tolerance") = Me.txttolerance.Text
row("Temp_Coefficient") = Me.txttemp.Text
row("Part_number") = Me.txtpartnum.Text
row("Cell_Name") = Me.txtcell.Text
row("Symbol") = Me.txtsym.Text
End If
dt.Rows.Add(row)
da.Update(dt)
savedata()
End Sub
Private Sub savedata()
bs.EndEdit()
da.Update(dt) -->Dynamic SQL generation for the DeleteCommand is not supported against a SelectCommand that does not return any key column information.
da.Fill(dt)
bs.MoveLast()
End Sub
Private Sub btnedit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnedit.Click
End Sub
Private Sub btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click
bs.RemoveCurrent()
da.Fill(dt)
savedata()
End Sub
Private Sub Form1_FormClosing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
If bs.DataSource IsNot Nothing Then
bs.EndEdit()
da.Update(dt)
cmdbldr = New OleDbCommandBuilder(da)
cmdbldr.QuotePrefix = "["
cmdbldr.QuoteSuffix = "]"
End If
End Sub
Private Sub btnpassword_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnpassword.Click
TabControl1.Visible = False
If txtUsername.Text = "Vikingidc" And txtPassword.Text = "Viking123" Then
GroupBox1.Visible = False
TabControl1.Visible = True
Else
MessageBox.Show("You have entered incorrect login crendentials") 'MessageBoxButtons.OK, MessageBoxIcon.Question
' TabControl1.Visible = False
txtPassword.Clear()
txtUsername.Clear()
txtUsername.Focus()
End If
End Sub
Private Sub btnreset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnreset.Click
txtPassword.Clear()
txtUsername.Clear()
End Sub
Private Sub btnend_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnend.Click
Dim result As String '= MessageBox.Show("Are you sure you would like to exit?", MessageBoxButtons.YesNo) ',MessageBoxButtons.YesNo , MessageBoxIcon.Question
result = MsgBox("Are you sure you would like to exit?", vbYesNo)
If result = DialogResult.Yes Then
Application.Exit()
End If
End Sub
Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click
' On Error GoTo SaveErr
' CapacitorBindingsource.EndEdit()
' ResistorBindingSource.EndEdit()
' ConnectorBindingSource.EndEdit()
savedata()
MessageBox.Show("Saved")
'SaveErr:
' Exit Sub
End Sub
Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
Me.txtdes.Text = ""
Me.txtrohs.Text = ""
Me.txtmanf.Text = ""
Me.txtmanfpn.Text = ""
Me.txtagile.Text = ""
Me.txttype.Text = ""
Me.txtpack.Text = ""
Me.txtvalue.Text = ""
Me.txtvoltage.Text = ""
Me.txttolerance.Text = ""
Me.txttemp.Text = ""
Me.txtpartnum.Text = ""
Me.txtcell.Text = ""
Me.txtsym.Text = ""
Me.txtagile.Tag = ""
End Sub
End Class
Marked error i'm getting while trying to delete and save the data.
-
Jul 10th, 2019, 10:57 AM
#27
Re: ADD,DELETE,UPDATE the records in the access database
-
Jul 22nd, 2019, 07:43 AM
#28
Thread Starter
Member
Re: ADD,DELETE,UPDATE the records in the access database
The primary is automatically created in the Database and also it is unique, which is my ID (unique). This problem persists even after that.
-
Jul 23rd, 2019, 12:11 AM
#29
Thread Starter
Member
Re: ADD,DELETE,UPDATE the records in the access database
Code:
Private Sub savedata()
bs.EndEdit()
da.MissingSchemaAction = MissingSchemaAction.AddWithKey
da.Update(dt) --> dynamic sql generation for the delete command is not supported against a select command
da.Fill(dt)
bs.MoveLast()
End Sub
Even if i add the missingschema action with Addwithkey i still get the same error. My database has the PK of ID which is unique. Please help me to resolve this error.
-
Jul 23rd, 2019, 02:58 AM
#30
Re: ADD,DELETE,UPDATE the records in the access database
Your code doesn't make sense. You need to set MissingSchemaAction when you create the data adapter, before you call Fill. When it comes time to save the changes, you only need to call Update, not Fill or set MissingSchemaAction. It's pretty simple stuff: create the data adapter, configure it (i.e. set all its properties and create a command builder if you want one), call Fill to retrieve data into a DataTable, edit the data, call Update to save those changes. If your query involves a single table with a PK and you retrieve that PK then you will be able to generate the InsertCommand, UpdateCommand and DeleteCommand automatically via the command builder.
Last edited by jmcilhinney; Jul 23rd, 2019 at 03:02 AM.
-
Jul 25th, 2019, 10:20 PM
#31
Thread Starter
Member
Re: ADD,DELETE,UPDATE the records in the access database
Code:
Private Sub btnedit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnedit.Click
'open connection if not already opened
If Not cnn.State = ConnectionState.Open Then
cnn.Open()
End If
Dim intID As Integer = Me.DataGridView1.SelectedRows(0).Cells("ID").Value
Dim myPrimaryKey As Integer = Me.DataGridView1.SelectedRows(0).Cells("ID").Value
If ComboBox1.Text = "Capacitor" Then
Dim str As String = "UPDATE Capacitor SET Agile Number=?, [Description]=?, Rohs=?, Manufacturer=?, Manufacturer Part number=?, [Type]=?, Pack type=?, Value=?, Voltage=?, Tolerance=?, Temp-Coefficient=?, Part number=?, Cell Name=?, Symbol=? WHERE ID = @ID"
Dim cd As New OleDbCommand
cd = New OleDbCommand(str, cnn)
cd.Parameters.AddWithValue("Agile Number", txtagile.Text)
cd.Parameters.AddWithValue("Description", txtdes.Text)
cd.Parameters.AddWithValue("Rohs", txtrohs.Text)
cd.Parameters.AddWithValue("Manufacturer", txtagile.Text)
cd.Parameters.AddWithValue("Manufacturer Part number", txtagile.Text)
cd.Parameters.AddWithValue("Type", txtagile.Text)
cd.Parameters.AddWithValue("Pack type", txtagile.Text)
cd.Parameters.AddWithValue("Value", txtagile.Text)
cd.Parameters.AddWithValue("Voltage", txtagile.Text)
cd.Parameters.AddWithValue("Tolerance", txtagile.Text)
cd.Parameters.AddWithValue("Temp-Coefficient", txtagile.Text)
cd.Parameters.AddWithValue("Part number", txtagile.Text)
cd.Parameters.AddWithValue("Cell Name", txtagile.Text)
cd.Parameters.AddWithValue("Symbol", txtagile.Text)
cd.ExecuteNonQuery() --> Syntax error in UPDATE statement.
MsgBox("Updated")
cnn.Close()
ElseIf ComboBox1.Text = "Connector" Then
' Dim str As String = "UPDATE Connector SET AgileNumber= ""txtagile.Text", Description = "txtdes.Text", Rohs = "txtrohs.Text", Manufacturer = "txtmanf.Text", ManufacturerPartnumber = "txtmanfpn.Text", Partnumber = "txtpartnum.Text", CellName = "txtcell.Text", Symbol = "txtsym.Text"
Dim cd As New OleDbCommand
Dim str As String = "UPDATE Capacitor SET Agile Number=?, [Description]=?, Rohs=?, Manufacturer=?, Manufacturer Part number=?, [Type]=?, Pack type=?, Value=?, Voltage=?, Tolerance=?, Temp-Coefficient=?, Part number=?, Cell Name=?, Symbol=? WHERE ID = @ID"
cd = New OleDbCommand(str, cnn)
cd.Parameters.AddWithValue("Agile Number", txtagile.Text)
cd.Parameters.AddWithValue("Description", txtdes.Text)
cd.Parameters.AddWithValue("Rohs", txtrohs.Text)
cd.Parameters.AddWithValue("Manufacturer", txtmanf.Text)
cd.Parameters.AddWithValue("Manufacturer Part number", txtmanfpn.Text)
cd.Parameters.AddWithValue("Part number", txtpartnum.Text)
cd.Parameters.AddWithValue("CellName", txtcell.Text)
cd.Parameters.AddWithValue("Symbol", txtsym.Text)
MsgBox("LA")
cd.ExecuteNonQuery() --> Syntax error in UPDATE statement.
MsgBox("Updated")
cnn.Close()
End If
End Sub
Above is my code for updating the current record and save it. While doing so i am ending with the Syntax error in UPDATE statement.. I don't what's the issue. While i am not using the ID textbox here, since it is autogenerated in the access.
Also for the Delete and update command it works fine. Thanks jmcilhinney and wes4dbt.
-
Jul 25th, 2019, 10:29 PM
#32
Re: ADD,DELETE,UPDATE the records in the access database
You should avoid using spaces or other special characters in table or column names. Just as you would use AgileNumber as an identifier in your VB app, so you should in your database too. If you absolutely can't do that for some reason, you need to force those names to be treated as a single identifier in the SQL code. You already know how to do that because you're already doing it for column names that are keywords.
-
Jul 25th, 2019, 11:42 PM
#33
Thread Starter
Member
Re: ADD,DELETE,UPDATE the records in the access database
I have removed all the spaces in column names. Since i haven't given the textbox for the ID, whether that causing any issue. ID is autogenarated in the access Database.
OleDbException was unhandled
No value given for one or more required parameters.
-
Jul 26th, 2019, 12:23 AM
#34
Re: ADD,DELETE,UPDATE the records in the access database
That means that you haven't added a parameter for every place-holder in your SQL. Looking at your ElseIf block, I see 15 parameter place-holders in the SQL code and not nearly that many parameters added to the command.
-
Jul 29th, 2019, 07:30 AM
#35
Thread Starter
Member
Re: ADD,DELETE,UPDATE the records in the access database
Code:
Private Sub btnedit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnedit.Click
DataGridView1.BeginEdit(True)
If Me.DataGridView1.Rows.Count > 0 Then
If Me.DataGridView1.SelectedRows.Count < 2 Then
If Not cnn.State = ConnectionState.Open Then
cnn.Open()
End If
If ComboBox1.Text = "Connector" Then
Dim cmd As New OleDb.OleDbCommand
cmd.Connection = cnn
cmd.CommandText = "Update Connector SET Agilenumber=@Agilenumber, Description=@Description, Rohs=@Rohs, Manufacturer=@Manufacturer, ManufacturerPartnumber=@ManufacturerPartnumber, PartNumber=@PartNumber, [CellName]=@CellName, [Symbol]=@Symbol Where ID=@ID"
cmd.Parameters.AddWithValue("@ID", txtID.Text)
cmd.Parameters.AddWithValue("@Agilenumber", txtagile.Text)
cmd.Parameters.AddWithValue("@Description", txtdes.Text)
cmd.Parameters.AddWithValue("@Rohs", txtrohs.Text)
cmd.Parameters.AddWithValue("@Manufacturer", txtmanf.Text)
cmd.Parameters.AddWithValue("@ManufacturerPartnumber", txtmanfpn.Text)
cmd.Parameters.AddWithValue("@PartNumber", txtpartnum.Text)
cmd.Parameters.AddWithValue("@CellName", txtcell.Text)
cmd.Parameters.AddWithValue("@Symbol", txtsym.Text)
cmd.ExecuteNonQuery()
cnn.Close()
MessageBox.Show("User updated!")
End If
Else
MessageBox.Show("Select 1 row before you hit Update")
End If
End If
End Sub
For updating the existing record, i wrote this code. It runs without error, but the database and datagridview is not updating on the instance.
-
Jul 29th, 2019, 07:40 AM
#36
Re: ADD,DELETE,UPDATE the records in the access database
As you are using an Access based database, it is important that you add the parameters in the same order that they appear in the SQL statement.
At the moment you add @ID first, but it is the last one in the SQL statement, so it needs to be added last.
-
Jul 30th, 2019, 05:23 AM
#37
Thread Starter
Member
Re: ADD,DELETE,UPDATE Search the records in the access database
SI, that works fine. Thank you.
The below code is for the search button, but it couldn't worked out. Running without the error.
Code:
Private Sub Search_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Search.Click
' bs.Filter = "Agilenumber LIKE'" & txtsearch.Text & "%'"
Dim strsearch As String
Dim Task As String
strsearch = Me.txtsearch.Text
Task = "SELECT * FROM [" & ComboBox1.Text & " ] WHERE ((Agilenumber Like ""*" & strsearch & "*"") OR (Description Like ""*" & strsearch & "*"") OR (CellName Like ""*" & strsearch & "*""))"
da.SelectCommand = New OleDbCommand(Task, cnn)
End Sub
-
Jul 30th, 2019, 12:14 PM
#38
Re: ADD,DELETE,UPDATE the records in the access database
This is a new topic, you should start a new thread. btw - that code doesn't retrieve anything, it just sets up a select command.
-
Aug 1st, 2019, 04:26 AM
#39
Thread Starter
Member
Re: ADD,DELETE,UPDATE the records in the access database
I am getting the error for the update command.
For the Capacitor the error is Syntax error in UPDATE statement. and for the Other table the error is No value given for one or more required parameters., Connector and IC is working fine, dont know what is the issue
Code:
Private Sub btnedit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnedit.Click
DataGridView1.BeginEdit(True)
If Me.DataGridView1.Rows.Count > 0 Then
If Me.DataGridView1.SelectedRows.Count < 2 Then
Dim cmd As New OleDb.OleDbCommand
If Not cnn.State = ConnectionState.Open Then
cnn.Open()
End If
If ComboBox1.Text = "Capacitor" Then
' Dim cmd As New OleDb.OleDbCommand
cmd.Connection = cnn
cmd.CommandText = "Update Capacitor SET Agilenumber=@Agilenumber, Description=@Description, Rohs=@Rohs, Manufacturer=@Manufacturer, ManufacturerPartnumber=@ManufacturerPartnumber, [Type] = @Type, Packtype = @Packtype, [Value] = @Value, Voltage = @Voltage, [Tolerance] = @Tolerance, Temp-Coefficient = @Temp-Coefficient, PartNumber=@PartNumber, [CellName]=@CellName, [Symbol]=@Symbol Where ID=@ID"
cmd.Parameters.AddWithValue("@Agilenumber", txtagile.Text)
cmd.Parameters.AddWithValue("@Description", txtdes.Text)
cmd.Parameters.AddWithValue("@Rohs", txtrohs.Text)
cmd.Parameters.AddWithValue("@Manufacturer", txtmanf.Text)
cmd.Parameters.AddWithValue("@ManufacturerPartnumber", txtmanfpn.Text)
cmd.Parameters.AddWithValue("@Type", txttype.Text)
cmd.Parameters.AddWithValue("@Packtype", txtpack.Text)
cmd.Parameters.AddWithValue("@Value", txtvalue.Text)
cmd.Parameters.AddWithValue("@Voltage", txtvoltage.Text)
cmd.Parameters.AddWithValue("@Tolerance", txttolerance.Text)
cmd.Parameters.AddWithValue("@Temp-Coefficient", txttemp.Text)
cmd.Parameters.AddWithValue("@PartNumber", txtpartnum.Text)
cmd.Parameters.AddWithValue("@CellName", txtcell.Text)
cmd.Parameters.AddWithValue("@Symbol", txtsym.Text)
cmd.Parameters.AddWithValue("@ID", txtID.Text)
ElseIf ComboBox1.Text = "Connector" Then
' Dim cmd As New OleDb.OleDbCommand
cmd.Connection = cnn
cmd.CommandText = "Update Connector SET Agilenumber=@Agilenumber, Description=@Description, Rohs=@Rohs, Manufacturer=@Manufacturer, ManufacturerPartnumber=@ManufacturerPartnumber, PartNumber=@PartNumber, [CellName]=@CellName, [Symbol]=@Symbol Where ID=@ID"
cmd.Parameters.AddWithValue("@Agilenumber", txtagile.Text)
cmd.Parameters.AddWithValue("@Description", txtdes.Text)
cmd.Parameters.AddWithValue("@Rohs", txtrohs.Text)
cmd.Parameters.AddWithValue("@Manufacturer", txtmanf.Text)
cmd.Parameters.AddWithValue("@ManufacturerPartnumber", txtmanfpn.Text)
cmd.Parameters.AddWithValue("@PartNumber", txtpartnum.Text)
cmd.Parameters.AddWithValue("@CellName", txtcell.Text)
cmd.Parameters.AddWithValue("@Symbol", txtsym.Text)
cmd.Parameters.AddWithValue("@ID", txtID.Text)
ElseIf ComboBox1.Text = "Crystal" Then
' Dim cmd As New OleDb.OleDbCommand
cmd.Connection = cnn
cmd.CommandText = "Update Crystal SET Agilenumber=@Agilenumber, Description=@Description, Rohs=@Rohs, Manufacturer=@Manufacturer, ManufacturerPartnumber=@ManufacturerPartnumber, Frequency = @Frequency, [Voltage] = @Voltage, [Value] = @Value, PartNumber=@PartNumber, [CellName]=@CellName, [Symbol]=@Symbol Where ID=@ID"
cmd.Parameters.AddWithValue("@Agilenumber", txtagile.Text)
cmd.Parameters.AddWithValue("@Description", txtdes.Text)
cmd.Parameters.AddWithValue("@Rohs", txtrohs.Text)
cmd.Parameters.AddWithValue("@Manufacturer", txtmanf.Text)
cmd.Parameters.AddWithValue("@ManufacturerPartnumber", txtmanfpn.Text)
cmd.Parameters.AddWithValue("@Frequency", txttolerance.Text)
cmd.Parameters.AddWithValue("@Voltage", txtvoltage.Text)
cmd.Parameters.AddWithValue("@Value", txtvalue.Text)
'cmd.Parameters.AddWithValue("@Type", txttype.Text)
'cmd.Parameters.AddWithValue("@Packtype", txtpack.Text)
'cmd.Parameters.AddWithValue("@Temp-Coefficient", txttemp.Text)
cmd.Parameters.AddWithValue("@PartNumber", txtpartnum.Text)
cmd.Parameters.AddWithValue("@CellName", txtcell.Text)
cmd.Parameters.AddWithValue("@Symbol", txtsym.Text)
cmd.Parameters.AddWithValue("@ID", txtID.Text)
ElseIf ComboBox1.Text = "Diode_Led" Then
' Dim cmd As New OleDb.OleDbCommand
cmd.Connection = cnn
cmd.CommandText = "Update Diode_Led SET Agilenumber=@Agilenumber, Description=@Description, Rohs=@Rohs, Manufacturer=@Manufacturer, ManufacturerPartnumber=@ManufacturerPartnumber, Frequency = @Frequency, [Voltage] = @Voltage, [Value] = @Value, PartNumber=@PartNumber, [CellName]=@CellName, [Symbol]=@Symbol Where ID=@ID"
cmd.Parameters.AddWithValue("@Agilenumber", txtagile.Text)
cmd.Parameters.AddWithValue("@Description", txtdes.Text)
cmd.Parameters.AddWithValue("@Rohs", txtrohs.Text)
cmd.Parameters.AddWithValue("@Manufacturer", txtmanf.Text)
cmd.Parameters.AddWithValue("@ManufacturerPartnumber", txtmanfpn.Text)
cmd.Parameters.AddWithValue("@Packtype", txtpack.Text)
cmd.Parameters.AddWithValue("@Voltage", txtvoltage.Text)
cmd.Parameters.AddWithValue("@Value", txtvalue.Text)
'cmd.Parameters.AddWithValue("@Frequency", txttolerance.Text)
'cmd.Parameters.AddWithValue("@Type", txttype.Text)
'cmd.Parameters.AddWithValue("@Temp-Coefficient", txttemp.Text)
cmd.Parameters.AddWithValue("@PartNumber", txtpartnum.Text)
cmd.Parameters.AddWithValue("@CellName", txtcell.Text)
cmd.Parameters.AddWithValue("@Symbol", txtsym.Text)
cmd.Parameters.AddWithValue("@ID", txtID.Text)
ElseIf ComboBox1.Text = "IC" Then
' Dim cmd As New OleDb.OleDbCommand
cmd.Connection = cnn
cmd.CommandText = "Update IC SET Agilenumber=@Agilenumber, Description=@Description, Rohs=@Rohs, Manufacturer=@Manufacturer, ManufacturerPartnumber=@ManufacturerPartnumber, PartNumber=@PartNumber, [CellName]=@CellName, [Symbol]=@Symbol Where ID=@ID"
cmd.Parameters.AddWithValue("@Agilenumber", txtagile.Text)
cmd.Parameters.AddWithValue("@Description", txtdes.Text)
cmd.Parameters.AddWithValue("@Rohs", txtrohs.Text)
cmd.Parameters.AddWithValue("@Manufacturer", txtmanf.Text)
cmd.Parameters.AddWithValue("@ManufacturerPartnumber", txtmanfpn.Text)
cmd.Parameters.AddWithValue("@PartNumber", txtpartnum.Text)
cmd.Parameters.AddWithValue("@CellName", txtcell.Text)
cmd.Parameters.AddWithValue("@Symbol", txtsym.Text)
cmd.Parameters.AddWithValue("@ID", txtID.Text)
ElseIf ComboBox1.Text = "Inductor" Then
' Dim cmd As New OleDb.OleDbCommand
cmd.Connection = cnn
cmd.CommandText = "Update Inductor SET Agilenumber=@Agilenumber, Description=@Description, Rohs=@Rohs, Manufacturer=@Manufacturer, ManufacturerPartnumber=@ManufacturerPartnumber, [Packtype] = @Packtype, [Value] = @Value, Amps = @Amps, Resistance = @Resistance, PartNumber=@PartNumber, [CellName]=@CellName, [Symbol]=@Symbol Where ID=@ID"
cmd.Parameters.AddWithValue("@Agilenumber", txtagile.Text)
cmd.Parameters.AddWithValue("@Description", txtdes.Text)
cmd.Parameters.AddWithValue("@Rohs", txtrohs.Text)
cmd.Parameters.AddWithValue("@Manufacturer", txtmanf.Text)
cmd.Parameters.AddWithValue("@ManufacturerPartnumber", txtmanfpn.Text)
' cmd.Parameters.AddWithValue("@Type", txttype.Text)
cmd.Parameters.AddWithValue("@Packtype", txtpack.Text)
cmd.Parameters.AddWithValue("@Value", txtvalue.Text)
cmd.Parameters.AddWithValue("@Amps", txtvoltage.Text)
cmd.Parameters.AddWithValue("@Resistance", txttolerance.Text)
'cmd.Parameters.AddWithValue("@Temp-Coefficient", txttemp.Text)
cmd.Parameters.AddWithValue("@PartNumber", txtpartnum.Text)
cmd.Parameters.AddWithValue("@CellName", txtcell.Text)
cmd.Parameters.AddWithValue("@Symbol", txtsym.Text)
cmd.Parameters.AddWithValue("@ID", txtID.Text)
ElseIf ComboBox1.Text = "Resistor" Then
' Dim cmd As New OleDb.OleDbCommand
cmd.Connection = cnn
cmd.CommandText = "Update Resistor SET Agilenumber=@Agilenumber, Description=@Description, Rohs=@Rohs, Manufacturer=@Manufacturer, ManufacturerPartnumber=@ManufacturerPartnumber, [Type] = @Type, Packtype = @Packtype, [Value] = @Value, Wattage = @Wattage, Tolerance = @Tolerance, PartNumber=@PartNumber, [CellName]=@CellName, [Symbol]=@Symbol Where ID=@ID"
cmd.Parameters.AddWithValue("@Agilenumber", txtagile.Text)
cmd.Parameters.AddWithValue("@Description", txtdes.Text)
cmd.Parameters.AddWithValue("@Rohs", txtrohs.Text)
cmd.Parameters.AddWithValue("@Manufacturer", txtmanf.Text)
cmd.Parameters.AddWithValue("@ManufacturerPartnumber", txtmanfpn.Text)
cmd.Parameters.AddWithValue("@Type", txttype.Text)
cmd.Parameters.AddWithValue("@Packtype", txtpack.Text)
cmd.Parameters.AddWithValue("@Value", txtvalue.Text)
cmd.Parameters.AddWithValue("@Wattage", txtvoltage.Text)
cmd.Parameters.AddWithValue("@Tolerance", txttolerance.Text)
'cmd.Parameters.AddWithValue("@Temp-Coefficient", txttemp.Text)
cmd.Parameters.AddWithValue("@PartNumber", txtpartnum.Text)
cmd.Parameters.AddWithValue("@CellName", txtcell.Text)
cmd.Parameters.AddWithValue("@Symbol", txtsym.Text)
cmd.Parameters.AddWithValue("@ID", txtID.Text)
Else
End If
Dim Answer As Integer
Answer = MsgBox("Are you sure you wish to Update this record?", vbYesNo + vbExclamation + vbDefaultButton2, "Update Confirmation")
If Answer = vbYes Then
cmd.ExecuteNonQuery() --> No value given for one or more required parameters. and Syntax error in UPDATE statement.
refreshdata()
cnn.Close()
MessageBox.Show("User updated!")
End If
Else
MessageBox.Show("Select 1 row before you hit Update")
End If
End If
End Sub
-
Aug 1st, 2019, 04:39 AM
#40
Re: ADD,DELETE,UPDATE the records in the access database
For the Capacitor the error is Syntax error in UPDATE statement.
The names of fields/tables/etc should only contain letters and numbers, and possibly an underscore... Temp-Coefficient is not valid due to the - character. The parameter placeholder @Temp-Coefficient is also not valid.
No value given for one or more required parameters.
That one is more awkward to solve, because there are lots of possible causes... and a quick look at "Crystal" didn't show an obvious issue.
Check that you have the names of the fields/tables exactly correct, and try putting [] around the field/table names that don't already have them.
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
|