updating a database
-
i'm trying to update a checkbox state from vb to an ms access database. can anyone fix my code for me pleae? it says "no value given for one or more parameters(what does this mean?). Imports System.Data.OleDb Public Class Block Public conn As OleDbConnection Public comm As OleDbCommand Public dr As OleDbDataReader Public da As OleDbDataAdapter Dim icount As Integer Private Sub Btnblock_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBlock.Click 'when the logon button is pressed Dim ManagerLogin As String 'declares the variable Try conn = New OleDbConnection 'establishes that conn is a new connection conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\PCBank.mdb" 'the name of the connection (name of the database on the end) conn.Open() 'opens the connection comm = New OleDbCommand 'establishes that comm is a new command comm.Connection = conn 'the connection for the command is the connection specified comm.CommandType = CommandType.Text 'the commands are in text ManagerLogin = "SELECT DISTINCT Password FROM Accounts WHERE ManagerID='" & Me.tbID.Text & "' " 'the variables value is an SQL command, selects value that is equal to the id AND password. ''MessageBox.Show(ManagerLogin) 'shows the sql command (only needed for testing) comm.CommandText = ManagerLogin 'the variable is declared as a command dr = comm.ExecuteReader 'gives the variable a value conn = New OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=PCBank.mdb") conn.Open() comm = New OleDbCommand("update into Accounts(Blocked) values('" & (chBlock.CheckState) & "')", conn) icount = comm.ExecuteNonQuery() MsgBox("record inserted") chBlock.Checked = False Catch ex As Exception MsgBox(ex.Message) If dr.Read() Then (it doesn't like this line either)'if the entry is correct (readable) Me.Close():confused: Form2.Show() 'show the index varibale (form) 'close the current form Else 'if the entry is incorrect MessageBox.Show("Invalid ID, please try again") 'displays error message tbID.Clear() 'clears the managerid
-
i'm trying to update a checkbox state from vb to an ms access database. can anyone fix my code for me pleae? it says "no value given for one or more parameters(what does this mean?). Imports System.Data.OleDb Public Class Block Public conn As OleDbConnection Public comm As OleDbCommand Public dr As OleDbDataReader Public da As OleDbDataAdapter Dim icount As Integer Private Sub Btnblock_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBlock.Click 'when the logon button is pressed Dim ManagerLogin As String 'declares the variable Try conn = New OleDbConnection 'establishes that conn is a new connection conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\PCBank.mdb" 'the name of the connection (name of the database on the end) conn.Open() 'opens the connection comm = New OleDbCommand 'establishes that comm is a new command comm.Connection = conn 'the connection for the command is the connection specified comm.CommandType = CommandType.Text 'the commands are in text ManagerLogin = "SELECT DISTINCT Password FROM Accounts WHERE ManagerID='" & Me.tbID.Text & "' " 'the variables value is an SQL command, selects value that is equal to the id AND password. ''MessageBox.Show(ManagerLogin) 'shows the sql command (only needed for testing) comm.CommandText = ManagerLogin 'the variable is declared as a command dr = comm.ExecuteReader 'gives the variable a value conn = New OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=PCBank.mdb") conn.Open() comm = New OleDbCommand("update into Accounts(Blocked) values('" & (chBlock.CheckState) & "')", conn) icount = comm.ExecuteNonQuery() MsgBox("record inserted") chBlock.Checked = False Catch ex As Exception MsgBox(ex.Message) If dr.Read() Then (it doesn't like this line either)'if the entry is correct (readable) Me.Close():confused: Form2.Show() 'show the index varibale (form) 'close the current form Else 'if the entry is incorrect MessageBox.Show("Invalid ID, please try again") 'displays error message tbID.Clear() 'clears the managerid
Problem could be that you're trying to use a data reader object to do the update when a data reader provides forward-only, read-only access. You may find this[^] helpful when it comes to using data readers. For updating a database you should rather use a DataAdapter[^]. Seems like you're using .NET 1.1, right? Also, why are you initializing & opening your connection object twice? If it's not giving you errors it's at least incurring unnecessary overheads. Try refining your code to be a bit simpler, you'll feel the benefit in time :)