List of SQL server data type that works w/ infopath

[floppy]

Hi,

I am trying to creat InfoPath Forms and submit user input into a table in
SQL database. Spending all day trying to figure out why I didn't have
"Database" in the "Submit to," I realized that InfoPath doesn't accept alot
of data types I used in the table fields.

Is there a list of acceptable SQL data type that I can reference to? Let me
know. Thanks!

 

[Muhammad Sheraz Siddiqi]

Hi floppy,

You want to udpate database with the user input. Right ?
There are two ways to do it:

i) Develop/Deploy a web service and implement a method in it that updates
database with the user input. Call that web service from your infopath form.

ii) Use stored procedure to update the database. Make a dataconnection (or
use form's main dataconnection), change its query at runtime and make it run
stored procedure like (execute sp_MyStoredProc <params>) passing it user
input and run it.

I hope it helps.
-Attari-

 

[floppy]

Hi Muhammad,

I think stored procedure will work for me now. We are still evaluating
SharePoint and Infopath. I am not able to create a web service.

Do you know of sites where there are examples on SP calling on the onClick
event?

Thanks!

 

[floppy]

Hi,

I figured out part of the SP.

I used the following code to insert data. My sql stored procedure is working
properly. But user input is not being inserted into the DB.

Is this wrong? "XDocument.Query();" It suppose to retrieve data only, right?
-----------------------------------
var FN =
XDocument.DOM.selectSingleNode("/dfs:myFields/dfs:dataFields/d:Employees/@FirstName").text;
var LN =
XDocument.DOM.selectSingleNode("/dfs:myFields/dfs:dataFields/d:Employees/@LastName").text;


//Set the Command for the Query Adapter of the Data Source. Incorporate the
//parameter values that you want to use.
XDocument.QueryAdapter.Command = "execute spInsertEmployee " + "'" + FN +
"', '" + LN + "'";

XDocument.UI.Alert("done");

//Query the Data Source.
XDocument.Query();

 

[Muhammad Sheraz Siddiqi]

HI,

Floppy try the following article, It should many of your questions properly:

http://support.microsoft.com/?kbid=827007

If you still have queries, post them.

Thanks,
-Attari-

 

[floppy]

Hi Muhammad,

I followed the steps to submit data. I used "XDocument.UI.Alert" to output
my SQL Command and the data is just NOT inserted into the table.

I can't think of anything that might be wrong......except that when a Data
Source is created, both queryFields and dataFields are created. I am using
"dataFields" > "Controls in Layout Table."

Please help. Thanks!

 

[Muhammad Sheraz Siddiqi]

Hi,

Please make sure that you put the following instruction in your stored
procedure before any INSERT INTO statments:

SET IMPLICIT_TRANSACTIONS OFF

If it still wont work, you can email me your Form and Stored Procedure
Query. I'll try to make it work.

-Attari-

 

[floppy]

Thank you very much!!!! It's working now.

Hi,

Please make sure that you put the following instruction in your stored
procedure before any INSERT INTO statments:

SET IMPLICIT_TRANSACTIONS OFF

If it still wont work, you can email me your Form and Stored Procedure
Query. I'll try to make it work.

-Attari-

 

[SDecou]

I'm trying to do the same thing you mention below and it is not working for
me.

****Here is my code:
Sub Savetodatabase_OnClick(eventObj)
' Write your code here
Dim CLEmpID
dim id
id = 0
CLEmpID=XDocument.DOM.selectSingleNode("/dfs:myFields/dfs:dataFields/d:l_overtime/d:OverTime/@EmpID").text
XDocument.QueryAdapter.Command = "execute dbo.sp_insert_Overtime1 " + "'" +
CLEmpID + "'"
XDocument.Query()
End Sub

***Here is my SQL Stored procedure:
CREATE PROCEDURE [sp_insert_Overtime]
(@otid [int] output,
@empid [nvarchar](50))
AS INSERT INTO [Overtime]
( [empid])
VALUES
( @empid)
select @@identity as '@otid'
GO

I am receiving a "type mismatch error" on the XDocument.QueryAdapter.Command
line. Do you see anything wrong with either"

Thanks in advance.