Creating submitting new records to SQL stand alone SQL table

[kfrost]

I've got a form I created from a data connection to SQL. I have a table
called customers that I use to populate a field in a table called Trips which
is the table my data connection for the form is based off of.

I need to create a view, popup or something that allows admins to enter in
new companies and their information.

This table just has a columns like companyID, CompanyName, address, city,
state

There doesn't need to be a relationship with any other tables because all
it's used for is to populate field values for other fields. It's mainly just
a source for drop down list boxes.

It works great but I have to go into SQL and add all the info and what I'm
trying to do is figure out how to create a view in my form that contains a
data connection to this table for the sole purpose of just adding and
removing companies.

I've tried adding the table to the Main data connection but I have to
specify some type of relationship when there doesn't need to be one. I even
went ahead and created one but submit button still fails.

I'm doing this all in a VB.Net project. Am I going to have to create an
HTML popup of Windows form that connects directly to SQL to do this? Or
create a web service so I have the option of creating a second data
connection with the functionality to submit data to a database?

Thanks

 

[Scott L. Heim [MSFT]]

Hi Kris,

You could use a web service - and this may (in the end) be the easiest
approach. However, another option would be to create a separate XML/XSN
file that is specifically for adding customers. You could then use a button
to launch that XML file using the "New" method:

Dim objXDoc
Dim strXMLFile
Dim strXSN

strXMLfile = "C:\Customers.XML"
strXSN = "C:\Customers.XSN"

'Create the ExternalApplication object and open a specified form.
Set objXDoc = CreateObject("InfoPath.ExternalApplication")
objXDoc.Open strXMLFile

Set objXDoc = Nothing

There are a few caveats:
- This requires your form to be fully trusted
- Obviously it means additional files with your solution
- I don't yet have a method to update the original drop-down list with the
modified data other that a button that simply queries the secondary data
source.

Hopefully this will maybe give you some ideas!

Best regards,

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[kfrost]

Thank you Scott,

It most definitely does, but I want these records to be kept in database.
One central place to update things should I need to do it via other methods.
Meaning, I want the customer to be able to do it via Infopath, say if they
need me to do it, I create a secure webpage to where I connect and do it
remotely.

But I just want to clarify and make sure there is no way to do something
like this to a "stand alone" table via "Main Connection" data connection?

Thanks