Problems with Updating Data

  • Thread starter Thread starter Nijazi Halimaji
  • Start date Start date
N

Nijazi Halimaji

Hi Newsgroup

I am making a selection with a ODBC-Dataadapter into a Dataset. Then I
create Parameters for Updating the Data with Dataadapter.

When I create the Parameters then I cannot update because I have set the
value of the parameters a fixes value...

My question now is: How must the parameters look like to be connected to the
dataset for updating? This will mean when the dataadapter.update(Dataset) is
executed then the parameters must be dynamic from the Dataset.

Here is my code for better understanding (Please read my Remarks i made here
for understanding):

dim DBC_Command as New OdbcCommand("")

dim ODBC_Connection as New OdbcConnection("")

ODBC_Connection.ConnectionString = "DSN=Muschi2;"

dim ODBC_Adapter as New OdbcDataAdapter("SELECT SMSVersNr, NatelNr,
NatelRueckNr, Nachricht, AnzahlVersuche FROM SMSVers

ODBC_Adapter.SelectCommand.Connection = ODBC_Connection

ODBC_Adapter.UpdateCommand = ODBC_Command

ODBC_Adapter.UpdateCommand.CommandText = "UPDATE SMSVers set VersandDatum =
?, VersandZeit = ?, AnzahlVersuche = ? where SMSVersNr = ?"

ODBC_Adapter.UpdateCommand.Connection = ODBC_Connection

ODBC_Adapter.UpdateCommand.Parameters.Add("@VersandDatum",
OdbcType.DateTime)

ODBC_Adapter.UpdateCommand.Parameters.Add("@VersandZeit", OdbcType.DateTime)

ODBC_Adapter.UpdateCommand.Parameters.Add("@AnzahlVersuche", OdbcType.Int)

ODBC_Adapter.UpdateCommand.Parameters.Add("@SMSVersNr", OdbcType.Text)



Now I fill the dataset with data and adding 2 colums

Dim tmpds As New DataSet

ODBC_Adapter.Fill(tmpds)

tmpds.Tables(0).Columns.Add("VersandZeit")

tmpds.Tables(0).Columns.Add("VersandDatum")



Now I make changes on my dataset

ds.Tables(0).Rows(2).Item("AnzahlVersuche")= 9



ds.Tables(0).Rows(0).Item("AnzahlVersuche") = 7

ds.Tables(0).Rows(1).Item("AnzahlVersuche") = 5

Now this is where I am making the mistake, because I am Setting the values
of the Parameters to fixed Values

ODBC_Adapter.UpdateCommand.Parameters(0).Value =
myDataSet.Tables(0).Rows(0).Item("VersandDatum")

ODBC_Adapter.UpdateCommand.Parameters(1).Value =
myDataSet.Tables(0).Rows(0).Item("VersandZeit")

ODBC_Adapter.UpdateCommand.Parameters(2).Value =
myDataSet.Tables(0).Rows(0).Item("AnzahlVersuche")

ODBC_Adapter.UpdateCommand.Parameters(3).Value =
myDataSet.Tables(0).Rows(0).Item("SMSVersNr")

ODBC_Adapter.Update(myDataSet)



Is there a possiblity to make this update dynamic?



Thanks alot



Nijazi Halimaji
 
Hi Cor

This won't help me with the command builder... I have to make it the hard
way...

Nijazi Halimaji
 
This won't help me with the command builder... I have to make it the hard
way...
Click to expand...
Why?

The dataadapter does not know a where clause, it gives normally everything
that has changed rows back to the database with the accoording update
command.

Cor
 
Hi,

Nijazi Halimaji said:
Hi Newsgroup

I am making a selection with a ODBC-Dataadapter into a Dataset. Then I
create Parameters for Updating the Data with Dataadapter.

When I create the Parameters then I cannot update because I have set the
value of the parameters a fixes value...

My question now is: How must the parameters look like to be connected to
the dataset for updating? This will mean when the
dataadapter.update(Dataset) is executed then the parameters must be
dynamic from the Dataset.

Here is my code for better understanding (Please read my Remarks i made
here for understanding):

dim DBC_Command as New OdbcCommand("")

dim ODBC_Connection as New OdbcConnection("")

ODBC_Connection.ConnectionString = "DSN=Muschi2;"

dim ODBC_Adapter as New OdbcDataAdapter("SELECT SMSVersNr, NatelNr,
NatelRueckNr, Nachricht, AnzahlVersuche FROM SMSVers

ODBC_Adapter.SelectCommand.Connection = ODBC_Connection

ODBC_Adapter.UpdateCommand = ODBC_Command

ODBC_Adapter.UpdateCommand.CommandText = "UPDATE SMSVers set VersandDatum
= ?, VersandZeit = ?, AnzahlVersuche = ? where SMSVersNr = ?"

ODBC_Adapter.UpdateCommand.Connection = ODBC_Connection

ODBC_Adapter.UpdateCommand.Parameters.Add("@VersandDatum",
OdbcType.DateTime)

ODBC_Adapter.UpdateCommand.Parameters.Add("@VersandZeit",
OdbcType.DateTime)
Click to expand...

After adding each parameter set the SourceColumn, SourceColumn must be the
name of a column in the DataTable.
Eg:

Dim parVersandZeit As OdbcParameter =
ODBC_Adapter.UpdateCommand.Parameters.Add("@VersandZeit",
OdbcType.DateTime )

parVersandZeit.SourceColumn = "VersandZeit"
ODBC_Adapter.UpdateCommand.Parameters.Add("@AnzahlVersuche", OdbcType.Int)

ODBC_Adapter.UpdateCommand.Parameters.Add("@SMSVersNr", OdbcType.Text)



Now I fill the dataset with data and adding 2 colums

Dim tmpds As New DataSet

ODBC_Adapter.Fill(tmpds)

tmpds.Tables(0).Columns.Add("VersandZeit")

tmpds.Tables(0).Columns.Add("VersandDatum")
Click to expand...

Are you sure about this, the default type for a column is string, so maybe
it should be :

tmpds.Tables(0).Columns.Add("VersandZeit", GetType(DateTime))
tmpds.Tables(0).Columns.Add("VersandDatum", GetType(DateTime))


HTH,
Greetings
 
Ok... first thanks for the tip

Doing this will cause me a ISAM error?!?!

What now?

Nijazi Halimaji
 
Hi

Thanks alot to Cor and Bart for all your help. Finally i did it!!

Thanks again!

Nijazi Halimaji
 
Back
Top