Executing a Query and updating a DataTable

[Simon]

Hi all,

I have the following query which I want to use to update a DataTable:


"SELECT
tblVmsResponse.IntDestZone,(tblVmsResponse.LngDestinationDiversionNum)
as Diversionnumber," & " (tblVmsResponse.strIncidentLink) as
Incidentlink ,(tblVmsResponse.bytMessageResponse) as response,
tblVmsResponse.strPlan" & " FROM (tblIncidentResponse INNER JOIN
tblVmsResponse ON
tblIncidentResponse.strLinkNum=tblVmsResponse.strIncidentLink) INNER
JOIN" & " tblPlanVmsSetting ON tblPlanVmsSetting.strVmsId
=tblVmsResponse.strVmsId" & " WHERE ((tblVmsResponse.strVmsId = ?) AND
(tblPlanVmsSetting.strPlan =?) AND" & "
(tblPlanVmsSetting.strPlan=tblIncidentResponse.strPlan))" & " GROUP BY
tblVmsResponse.intDestZone,tblVmsResponse.LngDestinationDiversionNum,"
& " tblVmsResponse.strIncidentLink,tblVmsResponse.bytMessageResponse,
tblVmsResponse.strPlan;"

I now want to be able to fill a DataTable and, at a later stage,
execute the above query with values for tblVmsResponse.strVmsId and
tblPlanVmsSetting.strPlan (which are currently unknown). I then want
to update the table based on the supplied values.

How can this be done?

Thanks in advance

Simon

 

[Simon]

I've figured out how to do this now - but is there a better way?

The code is as follows:

Dim connectionString As String = "Provider =
Microsoft.Jet.OLEDB.4.0;Data Source = " & CDBPath() & TacCDBDB
Dim TacCDB As OleDbConnection = New
OleDbConnection(connectionString)
Dim DestSQL As String = "SELECT
tblVmsResponse.IntDestZone,(tblVmsResponse.LngDestinationDiversionNum)
as Diversionnumber," & " (tblVmsResponse.strIncidentLink) as
Incidentlink ,(tblVmsResponse.bytMessageResponse) as response,
tblVmsResponse.strPlan" & " FROM (tblIncidentResponse INNER JOIN
tblVmsResponse ON
tblIncidentResponse.strLinkNum=tblVmsResponse.strIncidentLink) INNER
JOIN" & " tblPlanVmsSetting ON tblPlanVmsSetting.strVmsId
=tblVmsResponse.strVmsId" & " WHERE ((tblVmsResponse.strVmsId = ?) AND
(tblPlanVmsSetting.strPlan =?) AND" & "
(tblPlanVmsSetting.strPlan=tblIncidentResponse.strPlan))" & " GROUP BY
tblVmsResponse.intDestZone,tblVmsResponse.LngDestinationDiversionNum,"
& " tblVmsResponse.strIncidentLink,tblVmsResponse.bytMessageResponse,
tblVmsResponse.strPlan;"
Dim Destcmd As OleDbCommand = New OleDbCommand(DestSQL, TacCDB)
Destcmd.Parameters.Add("@VMSID", OleDbType.VarWChar, 50,
"tblVmsResponse.strVmsId") '.Value = "0043A"
Destcmd.Parameters.Add("@PLANID", OleDbType.VarWChar, 50,
"tblPlanVmsSetting.strPlan") '.Value = "2C/110"
Dim DestAdp As New OleDbDataAdapter
DestAdp.SelectCommand = Destcmd
Dim DestTable As New DataTable


and then later on in the code I update the parameter values and re-fill
the datatable.

For v = 1 To Plan.VMSCount
Destcmd.Parameters.Item(0).Value() = MOLAVMSID(Plan.VMSIndex(v))
Destcmd.Parameters.Item(1).Value() = Plan.ID
DestAdp.Fill(DestTable)
Dim DestRows() As DataRow = DestTable.Select()