how to update rows in an mdb data source??

how to update rows in an mdb data source??

• Spog Mog

  Deema,

  I have to explain something about the board'd editor.

  It will introduce line wraps and induce syntax errors. I'm sorry about that. Let me see if I can do something about that after dinner.

  Renee

  
  
  

• Marian Major

  What is the failure that you get Is there an error message Does the code execute but the data is not updated

  The first block of code you tried looks about right; assuming that the connection and command are setup properly. The test for success is a bit mis-designed though... Try this code and see what happens:

  'Make sure the user entered a customer name in TextBox2
  If TextBox2.Text.Length < 1 Then

  'If not, give them a warning message
  MessageBox.Show("Please supply a customer name and try again.", "No Customer Name", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)

  Else

  'If there is a customer name, try the update

  'Create the connection
  Dim conn As New OleDb.OleDbConnection
  'Create the command
  Dim upd As New OleDb.OleDbCommand

  'Set the connection string
  conn.ConnectionString = "provider=microsoft.jet.oledb.4.0;data source=Cars.mdb"

  'Set the command properties
  upd.CommandType = CommandType.Text
  upd.Connection = conn
  upd.CommandText = "UPDATE Customers SET Cust_No= " & TextBox1.Text & ", Cust_Address='" & TextBox3.Text & "', Cust_Phone='" & TextBox4.Text & "' WHERE Cust_Name='" & TextBox2.Text & "'"

  'Store the number of records affected by the query
  Dim results As Integer

  'Try to open the connection and update the table
  Try

  conn.Open()
  results = upd.ExecuteNonQuery

  Catch ex As Exception

  'Show the error if either the open or execute fails
  MessageBox.Show(ex.Message)

  Finally

  'Whether the open and execute are successful or not,
  'check the state of the connection and close it if necessary
  If Not conn.State = ConnectionState.Closed Then conn.Close()

  End Try

  'Report the number of updated records to the user
  MessageBox.Show(String.Format("There were {0} record(s) updated", results))

  End If

  Let us know if that works for you. If not, please explain how it fails (ie error message or 0 records updated).

  GL!

  
  
  

• jpmv

  Thanks Ms.ReneeC for this URL, I read it and tried to uderstand what should I to do but I didn't .How can I write the update statement ,Please any helpe or clues at least.

  Thanks ,

  
  

• indiansmart

  http://forums.microsoft.com/MSDN/ShowPost.aspx PageIndex=2&SiteID=1&PostID=409971#409971

  
  
  

• ivanwong

  Your quite welcome Deema. If you need anything else, just start a new thread.

  And pay no attention to ReeneC's last post about "developing some interpersonal boundaries", that comment was aimed directly at me and should have been email to me, not posted in here to confuse you. She wasn't mad at you - she's mad at me.

  Since you didn't do anything wrong I don't want you to feel bad!!

  Good luck in finishing your degree!

  
  
  

• sureshbabu

  Deema,

  It's Ms. Renee....

  I call this Routine several hundred time a day. It's very reliable.

  Public Function PersistTable(ByVal Table As DataTable, Optional ByVal NewRow As DataRow = Nothing) As Boolean

  ' adodb.PersistTable - called by any routine needing to make permanent changes in a table.

  ' Usually this would be midlevel procedures in IOSUBS

  ' PersistTable has an optional argument for appending a new row on a table.

  Adapter.SelectCommand = New OleDbCommand("Select * from [" & Table.TableName & "]", con)

  Dim builder As New OleDbCommandBuilder(Adapter)

  builder.QuotePrefix = "["

  builder.QuoteSuffix = "]"

  If NewRow IsNot Nothing Then

  NewRow.Item(DataRecords.ciRecordNo) = Table.Rows.Count

  Table.Rows.Add(NewRow)

  End If

  Try

  Adapter.Update(Table)

  Catch e As Exception

  'Throw e

  MsgBox("Error persisting Table: " + Table.TableName + vbCrLf + "Exception was: " + e.Message, _

  MsgBoxStyle.Information, "ADONET.PersistTable")

  End Try

  End Function

  It relies upon the following:

  Imports System.Data

  and the instantation of an adapter somewhere else in the code:

  Public Adapter As OleDb.OleDbDataAdapter

  To update a table called Deema's Table, call it like this:

  PersistTable(DeemasTable)

  To append a row, if you haven't already, call it like this:

  PersistTable(DeemasTable,DeemasNewRow)

  and there you have it.

  
  
  

• almargob

  Thanks Mr.Reed kimble for your kind offer ,it’s really what I wanted to do but I hesitated. Your first sentence related to what I did write at the end of the last paragraph in my last post,but I deleted it before I post it , I decided to wait tell reading ReeneC‘s replay. I wrote”I’ve another question can I write it here ,I hope if someone could help me ”,So,I really became happy for your generosity. I've a difficult question ,but I’ll write it in another post as you said.

  Thanks also for clarifying to me the big misunderstanding which I did ,I don’t like other to be upset from me,even if I don’t know them in person.

  
  

• vludyeidiet

   

  "Let us know if that works for you."

  As a personal note, moderator hat off, I would appreciate your developing some interpersonal boundaries and speaking for yourself.

  It's true you are here and I am here and so are many others, but I'd definitely like to be in choice about who and what constitutes "us" and NOT have you speak for me (yet again).

  
  
  

• The Aqua

  Deema,

  Try this. There were no errors in what I gave you but the board editor will introduce line wraps. I've done what I could to take care of them.

  At risk of offense.... I would recommend that you learn basic syntax. It is part of computing.

  Public Function PersistTable(ByVal Table As DataTable, Optional _
  ByVal NewRow As DataRow = Nothing) As Boolean

  ' adodb.PersistTable - called by any routine needing to make permanent changes in a table.

  ' Usually this would be midlevel procedures in IOSUBS

  ' PersistTable has an optional argument for appending a new row on a table.

  Adapter.SelectCommand = New OleDbCommand("Select * from [" _
  & Table.TableName & "]", con)

  Dim builder As New OleDbCommandBuilder(Adapter)

  builder.QuotePrefix = "["

  builder.QuoteSuffix = "]"

  If NewRow IsNot Nothing Then

  NewRow.Item(DataRecords.ciRecordNo) = Table.Rows.Count

  Table.Rows.Add(NewRow)

  End If

  Try

  Adapter.Update(Table)

  Catch e As Exception

  'Throw e

  MsgBox("Error persisting Table: " + Table.TableName _

  + vbCrLf + "Exception was: " + e.Message, _

  MsgBoxStyle.Information, "ADONET.PersistTable")

  End Try

  End Function

  
  
  

• ztc

  Hi dear Ms.ReneeC,

  First of all ,sorry for giving you the wrong title. Secondly,my last access to this forum was since I posted my second replay here at 6:29 AM UTC,you can’t imagine how I was so frustrated, for I didn’t find the answer in many forums.So I didn’t brows the net tell the last hour. .

   

  Believe me ReneeC and all of you ,THANKING others is a crucial morality in my ethics ,even if I didn’t get the answer ,I find my self ought to thank whoever tried to give me a hand of help , so what about you ReneeC and rkimble you both deserve a bunch of Thanks for your codes,and patient with one like me asking a very easy question.

  I used to thank others by giving them  flowers or unforgettable kiss or just saying thanks with a Smile ,but generally speakin,through this world of digital read connection between others, Written Thanks is the only way to express my real satisfaction about you both for your help.Thanks for you from the depth of my heart.

  Returning Now to the codes ,

  I forgot to tell you that I’m working on an Access database and on the provider

  Imports System.Data.OleDb

  To rkimble, My first codes in the first post gave me this error” The connection properety has not been initialized” .

  Do you know what your code gave me it gave me this message which I was happy to see it "There were {1} record(s) updated",Thanks for it.

   

  Back to Ms.ReneeC, I tried your code after making the required changing ,but there were a curly blue line under many sentences ,I know that I’m who is mistaken .I’m sure dear that your code is 100% correct but,I don’t know how to make the suitable changes,I’m just studying this course to finish my diploma in programming tech.,so I just study little about programming fields,so I think for that I can’t understand most of the statements,even I did not tought how to work on

  Imports System.Data.SqlDbType ,I just can work on Access data bases.

  Thanks for you,I do appreciate your efforts.

  Please Ms.ReneeC ,don’t prevent me from speaking to you.

   

   

   

  
  
• how to update rows in an mdb data source??