removeChild function example

[ridawg]

Hey,

I need to write some custom code that deletes a record from a database. I
know InfoPath has some defualt functionality for this but it doesn't
completely do what I need.

I've written my own submit (submit new record and update an existing record)
code but haven't figured out how to do the delete code.

I found another post that mentioned using a function called removeChild. If
someone could share an example of this that would be great. I'm writing all
my code in VBScript.

 

[Greg Collins [InfoPath MVP]]

removeChild is for an XML DOM, not for SQL.

Are you trying to remove a row in your DOM before submitting to SQL or directly remove a row from the SQL table?

 

[ridawg]

Yeah - I jus realized the whole removechild is the wrong approach. Basically,
my form queries my database and returns some data. The submit code I already
wrote uses the queryadapter. My guess is I should be using the queryadapter
and writing some SQL code. Does that make sense?

 

[ridawg]

Hey,

I tried to write some SQL to do this but I can't seem to get it to work.
When I wrote the code to submit new data and update existing records in the
database I was able to use .Submit. This doesn't seem to work when I try to
delete records. I get the following error - "InfoPath cannot submit the form.
The form does not contain any new data to submit to the data source."

I also tried to use .Query thinking that if in the query I had a delete
statement it would work. No luck - nothing happens.

Below is the code for both attempts. I'm sure I just missing something stupid.

Sub btnDeleteFiling_OnClick(eventObj)
'Store the original command text of the query adapter
Dim strOriginalCommand
strOriginalCommand = XDocument.QueryAdapter.Command

'Get the Filings data node
Dim queryFilings
Set queryFilings =
XDocument.DOM.selectSingleNode("/dfs:myFields/dfs:queryFields/q:T_Filing")

'Get the activeFilingID
Dim objactiveFilingID
set objactiveFilingID =
XDocument.DOM.selectSingleNode("//dfs:myFields/my:activeFilingID")

'Build the new sql command to delete the filing
Dim strSQL
strSQL = "delete [T_Filing] " &_
"from [T_Filing] " &_
"where Filing_ID = " & objactiveFilingID.text & " " _

'Set the query adapter command to the new SQL text
XDocument.QueryAdapter.Command = strSQL

Dim DeleteFiling
set DeleteFiling = XDocument.QueryAdapter

'Execute the query
DeleteFiling.Submit
'XDocument.Query 'tried this as well - did not work

If eventObj.ReturnStatus = True Then
XDocument.UI.Alert "Filing was successfully deleted!"
Else
XDocument.UI.Alert "Delete failed!"
End If

XDocument.View.SwitchView "Search"
XDocument.View.ForceUpdate

'Reset the query adapter back to the original command so it is ready for
the next query
XDocument.QueryAdapter.Command = strOriginalCommand

End Sub

Any help would be greatly appreciated! Thanks

 

[S.Y.M. Wong-A-Ton]

All data in InfoPath is represented as XML even if it's coming from a SQL
table. If your form is bound to a database table, you can use removeChild on
the XML of the Main DOM and submit the changes back to the database. If you
are getting your data from a secondary data source that gets its data from a
SQL table, using removeChild will not work, since changes made to the XML of
secondary data sources are not persisted. In the latter case you'll have to
write your own data access code to delete records from the SQL table.

 

[S.Y.M. Wong-A-Ton]

If you get the error "InfoPath cannot submit the form. The form does not
contain any new data to submit to the data source." it means that InfoPath
did not detect any changes made to the XML of the Main DOM, so does not see
the need to perform the update/submit.

"Query" is used to retrieve data. "Submit" is used to persist/submit data.

Since I do not have a clear picture of your scenario I'm unable to offer you
any pointers at this time.
---
S.Y.M. Wong-A-Ton

Hey,

I tried to write some SQL to do this but I can't seem to get it to work.
When I wrote the code to submit new data and update existing records in the
database I was able to use .Submit. This doesn't seem to work when I try to
delete records. I get the following error - "InfoPath cannot submit the form.
The form does not contain any new data to submit to the data source."

I also tried to use .Query thinking that if in the query I had a delete
statement it would work. No luck - nothing happens.

Below is the code for both attempts. I'm sure I just missing something stupid.

Sub btnDeleteFiling_OnClick(eventObj)
'Store the original command text of the query adapter
Dim strOriginalCommand
strOriginalCommand = XDocument.QueryAdapter.Command

'Get the Filings data node
Dim queryFilings
Set queryFilings =
XDocument.DOM.selectSingleNode("/dfs:myFields/dfs:queryFields/q:T_Filing")

'Get the activeFilingID
Dim objactiveFilingID
set objactiveFilingID =
XDocument.DOM.selectSingleNode("//dfs:myFields/my:activeFilingID")

'Build the new sql command to delete the filing
Dim strSQL
strSQL = "delete [T_Filing] " &_
"from [T_Filing] " &_
"where Filing_ID = " & objactiveFilingID.text & " " _

'Set the query adapter command to the new SQL text
XDocument.QueryAdapter.Command = strSQL

Dim DeleteFiling
set DeleteFiling = XDocument.QueryAdapter

'Execute the query
DeleteFiling.Submit
'XDocument.Query 'tried this as well - did not work

If eventObj.ReturnStatus = True Then
XDocument.UI.Alert "Filing was successfully deleted!"
Else
XDocument.UI.Alert "Delete failed!"
End If

XDocument.View.SwitchView "Search"
XDocument.View.ForceUpdate

'Reset the query adapter back to the original command so it is ready for
the next query
XDocument.QueryAdapter.Command = strOriginalCommand

End Sub

Any help would be greatly appreciated! Thanks
--
-ridawg

Yeah - I jus realized the whole removechild is the wrong approach. Basically,
my form queries my database and returns some data. The submit code I already
wrote uses the queryadapter. My guess is I should be using the queryadapter
and writing some SQL code. Does that make sense?

 

[ridawg]

Thanks for the reply.

Here's the scenario. I would like to write my own delete code. I read
another one of your posts and I now think I do need to use the removeChild
statement - I'm just not doing it right. My database consists of 2 related
tables - T_Filing and T_Action_Items.

My form works as follows:

1. View one is a Search page. The search returns just a couple fields with a
button called Details. A user clicks on the Details button - this executes
another query that returns all the fields for the record they selected. I do
this with code and a hidden field where I store the selected record id.

2. In the Details view they can update the record (by changing an existing
field or adding a new Action Item). They can also delete the record as well.
I've been able to write my own submit update code and for my New Filing view
I wrote my own submit new record code. Just can't seem to figure out the
delete part.

I tried to write some code using the removeChild but I kept getting errors
saying I didn't have an object.

If you could give me a of basic example that would be a big help.

 

[S.Y.M. Wong-A-Ton]

Could you please post part of the code for your "submit new record" so that I
can get an idea in what direction you headed, since there are several ways of
doing this? And could you please confirm whether you bound your form to the
database? Thanks.

 

[S.Y.M. Wong-A-Ton]

Here's some demo VBScript code from a solution I never got to publish on my
website. This code was put behind a "Delete" button in a repeating table. The
form was bound to a database table.

Dim intSelectedRecordID
Dim objCompanyToDelete
Dim objFieldsDataNode

' Retrieve the selected record ID
intSelectedRecordID =
XDocument.DOM.selectSingleNode("/dfs:myFields/my:selectedRecordID").text

If Trim(intSelectedRecordID) <> "" Then

If IsNumeric(intSelectedRecordID) Then

' Retrieve the Company node to delete
Set objCompanyToDelete =
XDocument.DOM.selectSingleNode("/dfs:myFields/dfs:dataFields/d:Company[@id="
& intSelectedRecordID & "]")
If Not objCompanyToDelete Is Nothing Then

' Retrieve the dataFields node
Set objFieldsDataNode =
XDocument.DOM.selectSingleNode("/dfs:myFields/dfs:dataFields")

' Remove the selected Company node from the dataFields node
Call objFieldsDataNode.removeChild(objCompanyToDelete)

' Submit the form to delete the record
XDocument.Submit

End If

End If

End If

 

[ridawg]

Thanks for this. I think this is what I'm looking for. My form is bound to
the database. My first attempt to write my own delete code was pretty close
to this so I'm going to give this a try and see if I can get it to work. I'll
get back to you. Once again thanks!

--
-ridawg

Here's some demo VBScript code from a solution I never got to publish on my
website. This code was put behind a "Delete" button in a repeating table. The
form was bound to a database table.

Dim intSelectedRecordID
Dim objCompanyToDelete
Dim objFieldsDataNode

' Retrieve the selected record ID
intSelectedRecordID =
XDocument.DOM.selectSingleNode("/dfs:myFields/my:selectedRecordID").text

If Trim(intSelectedRecordID) <> "" Then

If IsNumeric(intSelectedRecordID) Then

' Retrieve the Company node to delete
Set objCompanyToDelete =
XDocument.DOM.selectSingleNode("/dfs:myFields/dfs:dataFields/d:Company[@id="
& intSelectedRecordID & "]")
If Not objCompanyToDelete Is Nothing Then

' Retrieve the dataFields node
Set objFieldsDataNode =
XDocument.DOM.selectSingleNode("/dfs:myFields/dfs:dataFields")

' Remove the selected Company node from the dataFields node
Call objFieldsDataNode.removeChild(objCompanyToDelete)

' Submit the form to delete the record
XDocument.Submit

End If

End If

End If

---
S.Y.M. Wong-A-Ton

Thanks for the reply.

Here's the scenario. I would like to write my own delete code. I read
another one of your posts and I now think I do need to use the removeChild
statement - I'm just not doing it right. My database consists of 2 related
tables - T_Filing and T_Action_Items.

My form works as follows:

1. View one is a Search page. The search returns just a couple fields with a
button called Details. A user clicks on the Details button - this executes
another query that returns all the fields for the record they selected. I do
this with code and a hidden field where I store the selected record id.

2. In the Details view they can update the record (by changing an existing
field or adding a new Action Item). They can also delete the record as well.
I've been able to write my own submit update code and for my New Filing view
I wrote my own submit new record code. Just can't seem to figure out the
delete part.

I tried to write some code using the removeChild but I kept getting errors
saying I didn't have an object.

If you could give me a of basic example that would be a big help.

 

[ridawg]

Just wanted to say thanks! I was able to get this working. Your help is much
appreciated.

--
-ridawg

Here's some demo VBScript code from a solution I never got to publish on my
website. This code was put behind a "Delete" button in a repeating table. The
form was bound to a database table.

Dim intSelectedRecordID
Dim objCompanyToDelete
Dim objFieldsDataNode

' Retrieve the selected record ID
intSelectedRecordID =
XDocument.DOM.selectSingleNode("/dfs:myFields/my:selectedRecordID").text

If Trim(intSelectedRecordID) <> "" Then

If IsNumeric(intSelectedRecordID) Then

' Retrieve the Company node to delete
Set objCompanyToDelete =
XDocument.DOM.selectSingleNode("/dfs:myFields/dfs:dataFields/d:Company[@id="
& intSelectedRecordID & "]")
If Not objCompanyToDelete Is Nothing Then

' Retrieve the dataFields node
Set objFieldsDataNode =
XDocument.DOM.selectSingleNode("/dfs:myFields/dfs:dataFields")

' Remove the selected Company node from the dataFields node
Call objFieldsDataNode.removeChild(objCompanyToDelete)

' Submit the form to delete the record
XDocument.Submit

End If

End If

End If

---
S.Y.M. Wong-A-Ton

Thanks for the reply.

Here's the scenario. I would like to write my own delete code. I read
another one of your posts and I now think I do need to use the removeChild
statement - I'm just not doing it right. My database consists of 2 related
tables - T_Filing and T_Action_Items.

My form works as follows:

1. View one is a Search page. The search returns just a couple fields with a
button called Details. A user clicks on the Details button - this executes
another query that returns all the fields for the record they selected. I do
this with code and a hidden field where I store the selected record id.

2. In the Details view they can update the record (by changing an existing
field or adding a new Action Item). They can also delete the record as well.
I've been able to write my own submit update code and for my New Filing view
I wrote my own submit new record code. Just can't seem to figure out the
delete part.

I tried to write some code using the removeChild but I kept getting errors
saying I didn't have an object.

If you could give me a of basic example that would be a big help.

 

[S.Y.M. Wong-A-Ton]

You're very welcome! Glad I was able to help.
---
S.Y.M. Wong-A-Ton

Just wanted to say thanks! I was able to get this working. Your help is much
appreciated.

--
-ridawg

Here's some demo VBScript code from a solution I never got to publish on my
website. This code was put behind a "Delete" button in a repeating table. The
form was bound to a database table.

Dim intSelectedRecordID
Dim objCompanyToDelete
Dim objFieldsDataNode

' Retrieve the selected record ID
intSelectedRecordID =
XDocument.DOM.selectSingleNode("/dfs:myFields/my:selectedRecordID").text

If Trim(intSelectedRecordID) <> "" Then

If IsNumeric(intSelectedRecordID) Then

' Retrieve the Company node to delete
Set objCompanyToDelete =
XDocument.DOM.selectSingleNode("/dfs:myFields/dfs:dataFields/d:Company[@id="
& intSelectedRecordID & "]")
If Not objCompanyToDelete Is Nothing Then

' Retrieve the dataFields node
Set objFieldsDataNode =
XDocument.DOM.selectSingleNode("/dfs:myFields/dfs:dataFields")

' Remove the selected Company node from the dataFields node
Call objFieldsDataNode.removeChild(objCompanyToDelete)

' Submit the form to delete the record
XDocument.Submit

End If

End If

End If

---
S.Y.M. Wong-A-Ton

Thanks for the reply.

Here's the scenario. I would like to write my own delete code. I read
another one of your posts and I now think I do need to use the removeChild
statement - I'm just not doing it right. My database consists of 2 related
tables - T_Filing and T_Action_Items.

My form works as follows:

1. View one is a Search page. The search returns just a couple fields with a
button called Details. A user clicks on the Details button - this executes
another query that returns all the fields for the record they selected. I do
this with code and a hidden field where I store the selected record id.

2. In the Details view they can update the record (by changing an existing
field or adding a new Action Item). They can also delete the record as well.
I've been able to write my own submit update code and for my New Filing view
I wrote my own submit new record code. Just can't seem to figure out the
delete part.

I tried to write some code using the removeChild but I kept getting errors
saying I didn't have an object.

If you could give me a of basic example that would be a big help.
--
-ridawg


:

All data in InfoPath is represented as XML even if it's coming from a SQL
table. If your form is bound to a database table, you can use removeChild on
the XML of the Main DOM and submit the changes back to the database. If you
are getting your data from a secondary data source that gets its data from a
SQL table, using removeChild will not work, since changes made to the XML of
secondary data sources are not persisted. In the latter case you'll have to
write your own data access code to delete records from the SQL table.
---
S.Y.M. Wong-A-Ton


:

Yeah - I jus realized the whole removechild is the wrong approach. Basically,
my form queries my database and returns some data. The submit code I already
wrote uses the queryadapter. My guess is I should be using the queryadapter
and writing some SQL code. Does that make sense?

--
-ridawg


:

removeChild is for an XML DOM, not for SQL.

Are you trying to remove a row in your DOM before submitting to SQL or directly remove a row from the SQL table?