Hi all.
I have a number of records in an access db and would like to update various rows by using one sql statement. I would like to replace an empty field with a set value but do not know the correct wording, or functions. For example:
sql = "Update SourceTable set EmptyFieldName = '" & strNewValue & "' Where IsDBNull(EmptyFieldName)"
As a work around I have had to do this:
Dim Adap As New OleDbDataAdapter("Select * from SourceTable Order By EmptyFieldName", Cn)
Dim Dataset1 As New DataSet()
Dim DView As New DataView()
Adap.Fill(Dataset1)
DView.Table = Dataset1.Tables(0)
If Dataset1.Tables(0).Rows.Count > 0 Then
Dim RowView As DataRowView Dim I As Integer = 0 For Each RowView In DView If RowView("EmptyFieldName").ToString = "" Then Dim cmd1 As New OleDbCommand("Update SourceTable set EmptyFieldName = '" & strNewValue & "' Where RowID = " & Trim(RowView("RowID").ToString), Cn) Trycmd1.ExecuteNonQuery()
Catch ex As ExceptionMessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Finallycmd1.Dispose()
End TryI += 1
End If Next Else End IfAdap.Dispose()
Dataset1.Dispose()
Anybody know an easier solution
Many thanks in advance.
Keith.
Update Command and the use of IsDBNull!! ???
Rajneesh Noonia
sql = "Update SourceTable set EmptyFieldName = '" & strNewValue & "' Where EmptyFieldName Is NULL"
ZopoStyle
The commandbuider idea is good, but what I really wanted was an sql command or function that would do this in one sql statement. I dont know if one exists, in VB6 DAO you could use Null but it doesnt work with .net
Thanks,
Keith.
cues7a
Hi!
I could think of two solutions:
1) Set the default value of column to "New Entry" in the database. Whenever new record is added column's value will be set to this value until it is changed.
2) Change your code as following:
Dim Adap As New OleDbDataAdapter("Select * from SourceTable Order By EmptyFieldName", Cn)
Dim Dataset1 As New DataSet()
Adap.Fill(Dataset1)
If Dataset1.Tables(0).Rows.Count > 0 Then
Dim DR As DataRow Dim I As Integer = 0 For Each RowView In DView If DR("EmptyFieldName").ToString = "" ThenDR("EmptyFieldName") = strNewValue
I += 1
End If
NextDim cb AS New OleCommandBuilder(Adap) ' It'll generate Update,Delete,Insert Statment from Adap based on the Select statement you provided.
'Note You must have primary key in you SourceTable for this code to work.
Adap.Update(DataSet1)
Adap.Dispose()Dataset1.Dispose()