InfoPath Dev

Really could use some help here guys. I found some sample code online which has helped me get steered in the right direction...somewhat.

 http://www.developerfusion.co.uk/show/6128/

SqlConnection MyConnection = new SqlConnection("server=sqlserver;database=yourdatabase;UID=;PWD=;");
MyConnection.Open();
SqlCommand Cmd = new SqlCommand();
Cmd.Connection = MyConnection;
Cmd.CommandType = CommandType.Text;
Cmd.CommandText = "select * from tblUser";
SqlDataAdapter DA = new SqlDataAdapter(Cmd);
DataSet DS = new DataSet();
DA.Fill(DS);

thisXDocument.DOM.selectSingleNode("/my:myFields/my:field1").text = DS.Tables[0].Rows[1][1].ToString();

if(DS.Tables[0].Rows[1][1].ToString() == "John Doe")
{
thisXDocument.UI.Alert("User name is John Doe.");
}

Now I've got a number of issues here. I'd REALLY appreciate it if I could send someone my form, and try to explain what I'm trying to do.

1. I can't seem to connect to the database on my local harddrive. I set up MS SQL Server Management Studio Express, so that it says the server is said to be ALMA4-XDSCAN5\SQLEXPRESS and it uses Windows Authentification making the username and password fields uneditable. So my question is, how exactly do I connect to that database?

 2. Further along, when the SQLDataAdapter is filled, if I go to edit the data in "DA" am I editing the actual data in the database, or am I editing data that's been pulled and isn't linked directly to the database?

So below is the basic outline of how I need to set this program up. If I knew the language, and if I knew how to work with databases through java script I'd have no problem executing this, but unfortunately I'm pretty lost in that regard.

Connect to database

CmdLoadForm:OnClick (user puts in a referenceID and clicks button to load info)
-Selects row from Requests table in LegalRequest database which has a referenceID matching the referenceID entered on the form.
-Fills in a few fields
-Selects rows from EventTracker table with matching referenceID
-Iterates through selection
--If 'Type' = one thing
---fill out these fields
--If Type = another thing
---insert another repeating section, and fill out those fields (question here, if there are repeating sections, how do you choose say the 2nd repititoin of that section)

CmdSubmitForm:OnClick
-If referenceID already in Requests table
--Update that row with data from form
-Else
--Insert new row

-If docTrackingID already in EventTracking table
--Update that row with data from form
-Else
--Insert new row

You can do the query with InfoPath's built in database support.

And you might be able to do the submit as well.

In the tools menu go to data connections and play around with the wizard.
 

Hi Nick,

We're you able to get the Database Accelerator installed? That should get you up and running in minimal time. No code. Form/DB changes down-the-road are easier to handle and you get the ref ID support that you want. It's a lot of work to code SQL connections up yourself and there are fundamental limitations (you have to start from SQL schema - which limits your data source structure and prevents easy additions later, or you have to write a ton of code and allow SQL Client Permissions which means full trust forms and lots of deployment hassle).