'Submit to Database' not available

[ClaireN]

I created a form from an Access database connection. I can 'Run Query' and
get the expected results back on my Master/Detail form. However, when I add a
'Submit' button, go to 'Button Properties', select 'Submit' action and click
on the 'Submit To' drop down list, 'Database' does not appear in the list. I
only see Email, Web Service, SharePoint Form Library, Web Server and 2 custom
submit options. I'd really appreciate any help you can give.

Thanks.

 

[Chris Mahoney]

An error I've had while attempting to submit is "One or more of the
columns in the data source has a long data type (text, ntext, hyperlink
or image). Long data types are not supported." Check that you're not
using any of these unsupported types.

Hope this helps
Chris

 

[ClaireN]

Thanks for the swift response Chris!

I just reduced all my memo fields to text(255) to see if that works. I
recreated a form from scratch as well but I still don't see 'database' in the
'Submit To' drop down list.

I think I'll try SQLServer to see if that helps since I will need long
datatypes anyway. Any other ideas?

Claire.

 

[ClaireN]

I assumed in my last post that the long datatype issue was Access but
re-reading, I guess long datatypes are not supported by InfoPath - right? so
it will make no difference if I shift to SQLServer now.

 

[Chris Mahoney]

I think I'll try SQLServer to see if that helps

I'm not sure that it does - I got the error I mentioned while using SQL
Server.

Are you attempting to submit back to your main database, or a secondary
source? I'm still quite new to InfoPath, but I think you can only
submit to the main DB.

 

[Adam Harding]

ClairN and Chris Mahoney

The only way an infopath form can directly submit to a database is if the
form was built from scratch using the "new from data connection" where you
specify the database and that submitting is OK.

Indirectly you can submit via a web service on to the database but you need
ASP knowledge.

Create a new form from sratch and select "New from Data Connection" connect
to your database and then copy and paste the controls from your form over to
the new form, right-click on each one and change their binding to the fields
on the database. There you both have it any further questions mail me at
(e-mail address removed)

Cheers Adam

 

[ClaireN]

Thanks both

I found out what was wrong - Where Adam said 'specify the database and that
submitting is OK' - there wasn't an option to say submitting is ok, so I
tried recreating the form with just 1 table - worked like a charm. Recreating
the form from scratch and joining 1 more table each time, I found that it was
a particular join that was causing the problem.

Thank you so much both for your assistance - I really appreciate it.

Have a great day,
Claire.

 

[warren- washington, dc]

Hi Claire-

You were able to finally use tables with long data types? I tried what you
suggested below with no success. As soon as I got to the table that
contained ntext (in SQL Server) or Memo (in Access), it would not allow
submitting.

Looking at the SQL Statement, I didn't find anything unusual with the joins.
Any suggestions is greatly appreciated!

 

[ClaireN]

Hi Warren

No, I didn't. I changed my datatypes to text(255) just to get over this
submit to database issue. If I cannot get away with 255 char limits I won't
be using InfoPath. I wonder if Office12 sorts this out?

Good Luck.

 

[warren- washington, dc]

Thanks for the quick response; you probably saved my head from a lot of
brick-pounding. I've already committed to using Sharepoint (my fault!), so
I'm going to have to do some sort of creative workaround.

FYI, I looked at the one memo field that's causing all this trouble, and
checked the length of text in it. Of the 62 records in the table:
36 - at or below 255 characters
12 - >256 and <=500
6 - >501 and <=1000
8 - >1000 (closer to 2,000 characters. Someone's got too much time).

But the point is people do use the memo field to write over 255 characters,
about 40% in my case. The inability to write this type of field can't be
ignored.