Problems with Updating Data

[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

 

[Cor Ligthert [MVP]]

Nijazi,

You can make in my opinion your problem a lot more easier by
using OleDB or SQLClient and/or a commandbuilder.

http://msdn.microsoft.com/library/d...ystemdataodbcodbccommandbuilderclasstopic.asp

I hope this helps,

Cor

 

[Nijazi Halimaji]

Hi Cor

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

Nijazi Halimaji

 

[Cor Ligthert [MVP]]

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

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

 

[Bart Mermuys]

Hi,

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)

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")

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

 

[Nijazi Halimaji]

Ok... first thanks for the tip

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

What now?

Nijazi Halimaji

 

[Nijazi Halimaji]

Hi

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

Thanks again!

Nijazi Halimaji