[RESOLVED] [2005] Record Changes [DeadLine]

[Loraine]

How to make changes directly in database ? I read jm tutorial about RetrieveData

Private data As DataRow

Public Sub New(ByVal data As DataRow)

InitializeComponents()

Me.Data = data
Me.txtRecord1.Text =  data("FirstName").ToString()
Me.txtRecord2.Text =  CStr(data("LastName"))

End Sub

Private Sub Edit_Click(ByVal .....) Handles .....

Dim dataRow As DataRow
Dim objDataTable As DataTable

dt = DirectCast(Bank.DataSource, DataTable)
dataRow = dt.NewRow()
dataRow = dt.Rows(Bank.CurrentRows.Index)

End Sub

I dont know how to make update it in database

dt.Update("Bank")

Nothing change

[talkro]

dim con as new sqlCOnnection("Your connectionString")
dim query as String = "Insert into table ....."
dim cmd as new sqlCommand(query, con)
 
try
  con.open()
  cmd.executeNonQuery()
catch ex as exception
    messagebox.show(ex.message)
finally
    con.close
end try

this will directly update your db one row at a time

[texas]

from the MSDN

Update Existing Records Using TableAdapters

TableAdapters provide different ways to update records in a database depending on the requirements of your application.

If your application uses datasets to store data, then you can simply update the records in the desired DataTable and then call the TableAdapter.Update method and pass in either the DataSet, DataTable, DataRow, or array of DataRows. The table above describes the different Update methods.

To update records in a database with the TableAdapter.Update method that takes DataSet, DataTable, DataRow, or DataRows()
Edit records in the desired DataTable by directly editing the DataRow in the DataTable. For more information, see How to: Edit Rows in a DataTable.

After the rows are edited in the DataTable, call the TableAdapter.Update method. You can control the amount of data to update by passing in either an entire DataSet, a DataTable, an array of DataRows, or a single DataRow.

The following code shows how to edit a record in a DataTable and then call the TableAdapter.Update method to save the changes to the database. (This example uses the Northwind database Region table.)

' Locate the row you want to update.
Dim regionRow As NorthwindDataSet.RegionRow
regionRow = NorthwindDataSet._Region.FindByRegionID(1)

' Assign the new value to the desired column.
regionRow.RegionDescription = "East"

' Save the updated row to the database
Me.RegionTableAdapter.Update(Me.NorthwindDataSet._Region)

If your application uses objects to store the data in your application, you can use the TableAdapter's DBDirect methods to send data from your objects directly to the database. These methods allow you to pass individual values for each column as method parameters. Calling this method updates an existing record in the database with the column values passed into the method.

The following procedure uses the Northwind Region table as an example.

To update records in a database using the TableAdapter.Update method that takes column values
Call the TableAdapter's Update method, passing in the new and original values for each column as parameters.

Dim regionTableAdapter As New NorthwindDataSetTableAdapters.RegionTableAdapter

regionTableAdapter.Update(1, "East", 1, "Eastern")

Update Records Using Command Objects

The following example updates existing records directly in a database using command objects. For more information on using command objects to execute commands and stored procedures, see Fetching Data into Your Application.

The following procedure uses the Northwind Region table as an example.

To update existing records in a database using command objects
Create a new command object; set its Connection, CommandType, and CommandText properties; and then open a connection and execute the command.

Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection("YOUR CONNECTION STRING")

Dim cmd As New System.Data.SqlClient.SqlCommand
cmd.CommandType = System.Data.CommandType.Text
cmd.CommandText = "UPDATE Region SET RegionID = 1, RegionDescription = 'East' WHERE RegionID = 1"
cmd.Connection = sqlConnection1

sqlConnection1.Open()
cmd.ExecuteNonQuery()
sqlConnection1.Close()

[Loraine]

@talkro = i provide that excution but my problem is the SQL for update i dont know what is the correct syntax i use this syntax

sql = "UPDATE Contacts SET FirstName = '" & txtFirst.Text & "', " & _
        "LastName = '" & txtLast.Text & "'," & "WHERE " &

But im stuck from it ....

@texas -... but my problem only is in the SQL UPDATE .... dont know how its done /

[talkro]

updateQuery = "UPDATE Contacts SET FirstName = @fname, LastName = @lastName WHERE ID = @ID"
 
dim command as New sqlCommand(updateQuery, con)
command.Parameters.AddWithValue("@fname", textfirst.text)
command.Parameters.AddWithValue("@lastName", textLast.text)
command.Parameters.AddWithValue("@ID", textID.text)
 
con.open()
command.executeNonQuery()
con.close

[Loraine]

@talkro
I used OleDb command so i change your code

Dim sql As String v= String.Empty

     sql = "UPDATE Contacts SET FirstName = @fname, LastName = @lastName WHERE ID = @ID"

      dim command as New OleDbCommand(sql, con)
      command.Parameters.AddWithValue("@fname", textfirst.text)
      command.Parameters.AddWithValue("@lastName", textLast.text)
      command.Parameters.AddWithValue("@ID", textID.text)
   
      con.open()
      command.executeNonQuery()
      con.close

I have a question, can you clarify it to me.
I have only FirstName and LastName on the Database

So if change the code like this

sql = "UPDATE Contacts SET FirstName = @fname WHERE LastName = @lastName

1.is this correct.
2.what is "@fname, @lastname" means in the query
3. if ever i have a ID why did you put it in the "WHERE" statement?

[talkro]

I assumed that your primary key is under a column named ID in the database.

since you apparently have no primary (which is bad specially in this case because it is very like for two person(people) to have the exact same name. check the phoneBook and tell me lol lol).

sql = "UPDATE Contacts SET FirstName = @fname WHERE LastName = @lastName

with this statement, you are going to modify all the records where the last is whtever you specify, so if you and your sister are i that database you are going to become the same.

@fname = fname is just a name you can put whatever you want there bu @ in from is used to specifiy to the oledbCommand that this value is a parameter. that why I used the command

command.parameters.addWithValue("@fname", textFirst.text)

where the second parameters provide the value that is to be used in the query.

If yu have an d id which unique for every row, you can use it in the where clause

[talkro]

your query is correct the only problem is that it s going to change all the people with the same lastname (This really likely to happen check the phonebook lolol)

by using an ID as unique key, you are sure to change only the desired record and the id is used in the where clause

@ is used to specify to the command that the value to be used there is a parameter and the will be specify later on

[Loraine]

So whenever i have i primary key or lets just say all primary key must put in the "WHERE" statement, So in that case if i chose the first column its says that only the first column that i want to change not all the records that is the same ....

About the " @fname, @lastName " i can put anything that i want, the important is the parameters must the same to the "@name" that i place ...... is it right ?

[talkro]

About the " @fname, @lastName " i can put anything that i want, the important is the parameters must the same to the "@name" that i place ...... is it right ?

if you meant The parameter "@fname" tha I place in the query
then it's right

[Loraine]

Thank you very much, now i know about the use parameters.