Solved

How to update/records  table uisng OleDbCommand

Posted on 2009-03-31
13
740 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:cybecks
  • 6
  • 6
13 Comments
 
LVL 48

Expert Comment

by:jpaulino
ID: 24030773
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
 
LVL 83

Assisted Solution

by:CodeCruiser
CodeCruiser earned 50 total points
ID: 24037196
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
 

Author Comment

by:cybecks
ID: 24041424
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
 
LVL 48

Expert Comment

by:jpaulino
ID: 24041518
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
 

Author Comment

by:cybecks
ID: 24041689
when I run the code i get the Error running SQL Code
0
 
LVL 48

Assisted Solution

by:jpaulino
jpaulino earned 450 total points
ID: 24041743
>> 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:cybecks
ID: 24041986
I get the following error message
error message is NO value given for one or more required parameters
0
 

Author Comment

by:cybecks
ID: 24042009
[Sch Comments] is a field in my table
txtComments.Text is my input text field on my form.

does this help
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 24042060
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
 

Author Comment

by:cybecks
ID: 24042201
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
 
LVL 48

Accepted Solution

by:
jpaulino earned 450 total points
ID: 24042243
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
 

Author Comment

by:cybecks
ID: 24042580
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
 
LVL 48

Expert Comment

by:jpaulino
ID: 24042760
Glad I could help!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Make a border less form movable 2 22
Showdialog 8 29
DataGridView does not show data rows correctly in vb.net 2013 3 29
DataGridView Events ? 3 37
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now