problem with dataset.Merge()

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am beginner and this is a small problem but I am stuck.
Dataset.Merge() method is not properly merging two dataset. I think my program in not creating update command properly.

Everyday I need to update table with the data I have in the flat file. I am reading data of flat file into dataset “sourceDSâ€. Dataset “destinationDS†has data from table. After I have data in both dataset I am calling Merge method
destinationDS.Merger(sourceDS, true)
I am not deleting any rows from table. I need to insert new rows and update old rows depending on the data. I am setting up query for UpdateCommand of dataAdapter as

updateSql = "Update " & tableName & " SET " & c7Name & "=@" & c7Name & ", " & c8Name & "=@" & c8Name & " WHERE ((" & _
c1Name & " =@" & c1Name & ") AND (" & c2Name & " =@" & c2Name & ") AND (" & c7Name & " =0" & _
") AND (" & c8Name & " = 0 ) )"

With cntDataDA.UpdateCommand.Parameters.Add("@" & c1Name, SqlDbType.BigInt)
.SourceColumn = c1Name
.SourceVersion = DataRowVersion.Current
End With

With cntDataDA.UpdateCommand.Parameters.Add("@" & c2Name, SqlDbType.BigInt)
.SourceColumn = c2Name
.SourceVersion = DataRowVersion.Current
End With

With cntDataDA.UpdateCommand.Parameters.Add("@" & c7Name, SqlDbType.BigInt)
.SourceColumn = c7Name
.SourceVersion = DataRowVersion.Current
End With

With cntDataDA.UpdateCommand.Parameters.Add("@" & c8Name, SqlDbType.BigInt)
.SourceColumn = c8Name
.SourceVersion = DataRowVersion.Current
End With

After statement destinationDS.Merge(sourceDS, true) is executed I am checking destinationDS into watch list and I found out that rows in the destinationDS are not update and still have rowstate = Modified.

I am creating table for modified row as
Dim ModifiedCntDataRecords As DataTable = cntDataDS.Tables(tableName).GetChanges(DataRowState.Modified)

Following statement throws exception
cntDataDA.Update(ModifiedCntDataRecords)
"Input string was not in a correct format."

Thanks for any help.

Thanks
 
SM, using the Params needs changed.

"Update TableName Set seomColumn = @SomeValue ,
SomeOtherColumn=SomeOtherValue" etc

then, add the CommandParameters

cmd.Parameters.Add("@SomeValue", SqlDbType.Whatever, 50).Value =
WhateerVariable.

It looks like your update command isn't working...You may want to use the
DataAdapter Configuration wizard just once so you can see what it creates
for you...you'll want to do a ColumnMapping which it looks like you already
have. Something isn't matching up there and I think those Param values is
waht it is.

HTH,

Bill



SM said:
Hi,

I am beginner and this is a small problem but I am stuck.
Dataset.Merge() method is not properly merging two dataset. I think my
Click to expand...
program in not creating update command properly.
Everyday I need to update table with the data I have in the flat file. I
Click to expand...
am reading data of flat file into dataset "sourceDS". Dataset
"destinationDS" has data from table. After I have data in both dataset I am
calling Merge method
destinationDS.Merger(sourceDS, true)
I am not deleting any rows from table. I need to insert new rows and
Click to expand...
update old rows depending on the data. I am setting up query for
UpdateCommand of dataAdapter as
updateSql = "Update " & tableName & " SET " & c7Name & "=@" & c7Name & ",
Click to expand...
" & c8Name & "=@" & c8Name & " WHERE ((" & _
c1Name & " =@" & c1Name & ") AND ("
Click to expand...
& c2Name & " =@" & c2Name & ") AND (" & c7Name & " =0" & _
") AND (" & c8Name & " = 0 ) )"

With cntDataDA.UpdateCommand.Parameters.Add("@" & c1Name, SqlDbType.BigInt)
.SourceColumn = c1Name
.SourceVersion = DataRowVersion.Current
End With

With cntDataDA.UpdateCommand.Parameters.Add("@" & c2Name, SqlDbType.BigInt)
.SourceColumn = c2Name
.SourceVersion = DataRowVersion.Current
End With

With cntDataDA.UpdateCommand.Parameters.Add("@" & c7Name, SqlDbType.BigInt)
.SourceColumn = c7Name
.SourceVersion = DataRowVersion.Current
End With

With cntDataDA.UpdateCommand.Parameters.Add("@" & c8Name, SqlDbType.BigInt)
.SourceColumn = c8Name
.SourceVersion = DataRowVersion.Current
End With

After statement destinationDS.Merge(sourceDS, true) is executed I am
Click to expand...
checking destinationDS into watch list and I found out that rows in the
destinationDS are not update and still have rowstate = Modified.
 
Back
Top