How to update/records table uisng OleDbCommand

Hello I'm trying to create a UPDATE function to update a Record and a DELETE Function using OleDbCommand to delete a record. Here is my code so far.
Thanks
my update function code is here
Private Sub UpdateData()
        Dim SchoolCode As String
        ' Fill Dataset and Get Data Table
 
        Dim DBName As String = Application.StartupPath & "\schools.mdb"
        'MsgBox(DBName)
        Dim Connection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBName & ";User ID=Admin;Password=")
        Dim Command As New OleDb.OleDbCommand("SELECT * FROM tblSchools", Connection)
 
       
        ' Update  the COMMENTS column in  the Tasks table
        OleDbCommand(" Update tblSchools(Comments) VALUES ('" & Comments.Text & "')")
 
 
 
 
        Catch ex As Exception
            MsgBox("Error running SQL Code", MsgBoxStyle.OkOnly, "ERROR: 132")
        Finally
            Connection.Close()
        End Try
 
 
 
    End Sub
 
Private Sub DeleteData()
        Dim SchoolCode As String
        ' Fill Dataset and Get Data Table
 
        Dim DBName As String = Application.StartupPath & "\schools.mdb"
        'MsgBox(DBName)
        Dim Connection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBName & ";User ID=Admin;Password=")
        Dim Command As New OleDb.OleDbCommand("SELECT * FROM tblSchools", Connection)
 
       
        'Delete a record in  the Tasks table
        OleDbCommand(" Delete * from tblSchool WHERE LIKE "SchoolCode" = txtSearch.Text & "')")
 
 
 
 
        Catch ex As Exception
            MsgBox("Error running SQL Code", MsgBoxStyle.OkOnly, "ERROR: 132")
        Finally
            Connection.Close()
        End Try
 
 
 
    End Sub

Open in new window

cybecksAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Jorge PaulinoConnect With a Mentor IT Pro/DeveloperCommented:
Don't you have an ID ? That way you're trying to update a field based on the new value (that doesn't exist in the database.
You should use an unique identifier like this:
UPDATE tblSchools SET [Sch Comments]= '" & txtComments.Text & "' WHERE ID  = "  & ItemID
If you want to change ALL you can do:
UPDATE tblSchools SET [Sch Comments]= '" & txtComments.Text & "'"
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
You don't need to select and then update or delete, you just have to do an executenonquery
http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.executenonquery.aspx 
0
 
CodeCruiserConnect With a Mentor Commented:
Try the following code.
Remember to add a where clause to the update command where i have added ???
my update function code is here
Private Sub UpdateData()
  Dim SchoolCode As String
  Dim DBName As String = Application.StartupPath & "\schools.mdb"
  Dim Connection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBName & ";User ID=Admin;Password=")
  Dim Command As New OleDb.OleDbCommand
 try       
  command.connection = connection
  command.commandtext = " Update tblSchools set Comments='" & Comments.Text & "' Where ???"
 Catch ex As Exception
  MsgBox("Error running SQL Code", MsgBoxStyle.OkOnly, "ERROR: 132")
 Finally
  Connection.Close()
 End Try
End Sub
 
Private Sub DeleteData()
  Dim SchoolCode As String
  Dim DBName As String = Application.StartupPath & "\schools.mdb"
  Dim Connection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBName & ";User ID=Admin;Password=")
  Dim Command As New OleDb.OleDbCommand
  command.connection = connection
 try      
  'Delete a record in  the Tasks table
  command.Commandtext = "Delete * from tblSchool WHERE [" & SchoolCode & "] = '" & txtSearch.Text & "'"
 Catch ex As Exception
  MsgBox("Error running SQL Code", MsgBoxStyle.OkOnly, "ERROR: 132")
 Finally
  Connection.Close()
 End Try
End Sub

Open in new window

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
cybecksAuthor Commented:
hello CodeCruiser:
Well I got my delete to work but unable to get the Update to work.  I tried your code for update and well I updated every record in the table.  I only want to uptate a few fields like the Sch Comments  field in the table .lucky for me Im working on a test copy.
any how her is my code for the update


Private Sub UpdateData()

        Dim DBName As String = Application.StartupPath & "\schools.mdb"
        Dim Connection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBName & ";User ID=Admin;Password=")
        Dim cmd As New OleDb.OleDbCommand("UPDATE tblSchools SET [Sch Comments]= '" & txtComments.Text & " 'WHERE txtComments.text  = '" & txtComments.Text.ToString() & "'", Connection)
     
          Connection.Open()

        Try
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox("Error running SQL Code", MsgBoxStyle.OkOnly, "ERROR: 132")
        Finally

        End Try
        Connection.Close()
        txtComments.Text = ""


    End Sub
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
What error do you have ?
Try to change to this T-SQL command

Dim cmd As New OleDb.OleDbCommand("UPDATE tblSchools SET [Sch Comments]= '" & txtComments.Text & "' WHERE txtComments.text  = '" & txtComments.Text.ToString() & "'", Connection)

Open in new window

0
 
cybecksAuthor Commented:
when I run the code i get the Error running SQL Code
0
 
Jorge PaulinoConnect With a Mentor IT Pro/DeveloperCommented:
>> when I run the code i get the Error running SQL Code
That's your custom message. Change the msgbox to:
MsgBox(ex.Message, MsgBoxStyle.OkOnly, "ERROR: 132")
 
0
 
cybecksAuthor Commented:
I get the following error message
error message is NO value given for one or more required parameters
0
 
cybecksAuthor Commented:
[Sch Comments] is a field in my table
txtComments.Text is my input text field on my form.

does this help
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
You have the SQL statement wrong !
"UPDATE tblSchools SET [Sch Comments]= '" & txtComments.Text & "' WHERE txtComments.text  = '" & txtComments.Text.ToString() & "'"
 
"WHERE txtComments.text  "
should be
"WHERE YourFieldName "
0
 
cybecksAuthor Commented:
When I changed the statement to
Dim cmd As New OleDb.OleDbCommand("UPDATE tblSchools SET [Sch Comments]= '" & txtComments.Text & " 'WHERE [Sch Comments]  = '" & txtComments.Text.ToString() & "'", Connection)

It does not add update any data in the table but I don't get any errors.
0
 
cybecksAuthor Commented:
Thanks you have been GREAT. I updated my code to :

Dim cmd As New OleDb.OleDbCommand("UPDATE tblSchools SET [Sch Comments]= '" & txtComments.Text & "'WHERE [strCd]  = '" & txtSCode.Text & "'", Connection)
        works fine now...


0
 
Jorge PaulinoIT Pro/DeveloperCommented:
Glad I could help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.