Updating records.

  • Thread starter Thread starter TerishD
  • Start date Start date
T

TerishD

Okay, people, this is where I was told that I could get
some help. Anyway, I have continued to work. The code
below does not error out, but it also does not update the
data (the data afterwards is the old data). What is
missing?

----------------------
CSQLa = New SqlDataAdapter
(SC, "Server=BLLSYS\GOMACOLA;database=data_00;Trusted_Conne
ction=Yes")

CSQLa.UpdateCommand = New SqlCommand("UPDATE OECTLFIL_SQL
WHERE start_ord_no = @start_ord_no", CSQL1)

CSQLd = New DataSet()
CSQLa.Fill(CSQLd, "OECTLFIL_SQL")
CSQLr = CSQLd2.Rows(0)
CSQLd2 = CSQLd.Tables("OECTLFIL_SQL")
CSQLr("start_ord_no") = S3
CSQLa.Update(CSQLd2.Select(Nothing, Nothing,
DataViewRowState.CurrentRows))
-------------------
 
Terish,
Your Update command doesn't look well-formed as you have no fields
specified to update and no parameters attached with data. As there are no
fields to update in the update command nothing gets changed. Also unless
you are selecting just one record you need the PrimaryKey of the DataTable
set and a parameter for in in the where clause.
A typical command would be
SqlCommand cmd = New SqlCommand("UPDATE myTable Set (Field1 = @f1, Field2 =
@f2) WHERE myKey = @key", connection);
cmd.Parameters.Add("@f1", SqlDbType.NVarChar, length, "Field1");
......and so on
myDa.UpdateCommand = cmd;
Normally for SQL server I do all the select/insert/delete/update with a
StoredProcedure and just reference the appropriate SP with the parameters
assigned correctly.
You can also choose to have the SqlCommandBuilder generate the
insert/delete/update commands for you if your select command is simple
enough for it.
Ron Allen
 
Okay, the code belows works. Let me tell you what
Microsoft does not -

1) That fancy @crap in the SET part is just that, CRAP.
Replace it with the actual data you want changed. In the
program below the replacement data is held in the variable
S3.

2) Once again, the new Microsoft believes in redundancy
(You know, one of the beauties of the BASIC language used
to be that it often did things without needing to waste
time typing). After you tell it what do with the UPDATE
string, you need to tell it again with .BeginEdit
and .EndEdit (Microsoft did NOT even mention these
commands, but it took a 3rd party book for me to learn).

3) For note, I did check to see if redundancy was
required. Well, if you leave out the first three lines,
you get an error on the .Update. If you leave out the
middle three lines, the file is not saved with the new
data.

-----------------
SC = "UPDATE OECTLFIL_SQL SET start_ord_no = " + S3
CSQLc.CommandText = SC
CSQLa.UpdateCommand = CSQLc

CSQLr.BeginEdit()
CSQLr("start_ord_no") = S3
CSQLr.EndEdit()

CSQLa.Update(CSQLd, "OECTLFIL_SQL")
-----------------------
 
Back
Top