Expand my Community achievements bar.

Problem in setting a Data Connection

Avatar

Level 9

Hello All ,

I am trying to learn how to create a Database connection to a form . I am just only using a textfield which will act as UserID . I am Using MySQL 5 Client and the server lies on another machine . I have created a table where UserID = Varchar , Primary Key , NotNull . I have created a DSN using OLEDB database named as Mail . The table name is mail  in the database.I have followed the following methods.

File - > New Data Connection - > OLEDB Database - > Connection String -> Build ->Micosoft OLEDB Database Provider for ODBC drivers - > Use Connection String - > Build - > DSN Name (Selected Mail) - > ODBC dBASE setup -> OK - > Test Connection - > Succeeded - > OLEDB Connection - > Record Source - > Table .


Here the problem arrises . I am not able to see my table name in the dropdown list . I know its like an idiotic question . But I am not able to solve .Is there any one who can help me out ?? Anyone can suggest some good online videos about this things ?

Thanks .

Bibhu .

31 Replies

Avatar

Level 9

Hello All ,

I am able to create the DataBase connection now . The problem earlier was a faulty installation of Driver.

But now I am facing some weird problem . I did connection to the server and in mydata view panel it shows the field.

I have added a button next to the data field so that after entering the data when the user clicks the button , the data should be submitted to the table that I have made .I have written the following script to enter a new data.

Book.Page1.#subform[0].Button2::click - (JavaScript, client)

xfa.sourceSet.DataConnection.addNew();

But while previewing one Adobe Acrobat information message comes displaying : "Connection of source data connection failed because the enviornment is not trusted."

More over while clicking on the textfield the javascript debugger shows the following error.

Exception in line 1 of function top_level, script XFA:Book[0]:Page1[0]:#subform[0]:UserID[0]:click

GeneralError: Operation failed.
XFAObject.addNew:1:XFA:Book[0]:Page1[0]:#subform[0]:UserID[0]:click
addNew operation failed.  Operation is not allowed when the object is closed.

I am totally confused , moreover what's meant by the object is closed ??

Avatar

Former Community Member

Depending on the type of database that you have you may need to create a record and update that record instead of addnew. Some DBs do not support that.

The environment not trusted message is one that I have seen before and I know that it is a trust issue between the ODBC connection and the DB. Did you create the DSN as a user DSN or a system DSN. Where does the database reside (on another system) and what type of DB is it?

Lastly the closed object error - I think this is because the ODBC connection was not Opened before trying to affect the DB (just speculating at this point).

Paul

Avatar

Level 9

Hello Paul ,

Thanks for the response .I have created the DSN as a user DSN. In my system only MySQL 5 client installed . So I am using the database which reisides on another system where MySQL Server is installed .It has only two columns : UserID (Primary Key) , Name in My SQL 5. So now what to do ?

Thanks .

Bibhu.

Avatar

Level 9

Hello Paul ,

Now I am not getting any issues regarding the trust . When the form open there is message like this form wants to access .......Source . I clicked yes .

Then i entered some datas in the two fields , When i checked in the query browser in MySQL the same data which entered recently is not showing .

I am confused again , Please Help .

Thanks .

Bibhu .

Avatar

Former Community Member

Can you create the DSN as a system DSN and see if the trusted message goes away?

Paul

Avatar

Former Community Member

Thats good ....lets not try and update the DB just yet. If you bind fields between the database and the form

and you open the form does the 1st record show up?

Paul

Avatar

Level 9

Hello Paul ,

Yes , when I open the form the first record shows up . Now what's the next step ??

Thanks .

Bibhu .

Avatar

Former Community Member

Great that means that we have the conenctivity set up correctly.

Now to control the connection there are a couple of things I can recommend. I did a presentation on this for the Acrobat users group. Here is a link to that presentation:

http://adobechats.adobe.acrobat.com/p69655795/

Also Stefan Cameron has a blog where he describes some more advanced topics around DB connectivity. Here is a couple of links to his blog where he discusses DB stuff:

http://forms.stefcameron.com/2006/09/18/connecting-a-form-to-a-database/

http://forms.stefcameron.com/2006/09/29/selecting-specific-database-records/

Paul

Avatar

Level 9

Hello  Paul ,

I was waiting for your response since last 8 hours .I have created a button after the two fields . In the button Click event i have written xfa.sourceSet.Test.addNew(); How ever this button on clicking after entering the data doesn't update the table .More Over if for the second time I insert some data into the field anc click the button then an error comes.

Exception in line 1 of function top_level, script XFA:Book[0]:Page1[0]:#subform[0]:Button2[0]:click

GeneralError: Operation failed.
XFAObject.addNew:1:XFA:Book[0]:Page1[0]:#subform[0]:Button2[0]:click
addNew operation failed.  Empty row cannot be inserted. Row must have at least one column value set.

What should I do ??

Thanks.

Bibhu.

Avatar

Level 9

Hello Paul ,

If for the same button where I have putted addNew script changed to xfa.sourceSet.Test.update(); then it updates successfully , but what's happening in case of addNew ?? What about the error that comes in JavaScript console ??

Thanks .

Bibhu.

Avatar

Former Community Member

Thsi is difficult without seeing yor database but do you have a required field in your database when you add a record. In most database you add a new record and then update that record with information. It looks like your add a new record is failing because teh DB is not allowing it.

Just a guess at this point.

paul

Avatar

Level 9

No , I don't have a required filed . Ther are two fields i.e UserID and Name . UserID is the primary key in the database.

Thanks.

Bibhu.

Avatar

Level 9

I can also perfrom previous and next operation , but I really can not understand what's wrong in add operation .

Thanks.

Bibhu.

Avatar

Former Community Member

You may not be able to add a new record unless there is a primary key.

Paul

Avatar

Level 9

Yes I do have a primary key which is my UserID. But According to Stefan Cameron :

"The very last part of the error message you quoted is "[ID:11]". This  is usually the name of the column (and the value associated to it) for  which there was an error.

Since the name is "ID", I'm wondering if you might be attempting to  set the value of an auto-incremented column. This is something that you  cannot do using a data connection. In that case, you must provide an SQL  Query, when setting-up your data connection, which excludes the "ID"  column from the connection.

You can edit an existing data connection by right-clicking on it in  the Data View palette and choosing the "Connection Properties" command."

So , I am confused again , what SQL query should I write ?

Thanks.

Bibhu

Avatar

Former Community Member

I cannot tell you wich query to write as I am not a SQL expert. My knowledge is very limited.I can tell you how to set up th econnection to execute the query.

paul

Avatar

Level 9

Hello Paul ,

I tried everything . I made my UserID column as not autoincrementing and tried . But again the same problem . I tried to use SQL query instead of table option . In the query I wrote SELECT Name from table ; there by excluding the column UserID which has an auto increment value . Still the same problem appears . Every function is working except addNew () . I am totally frustrated Paul , what should I do ?? Please help me out .

Thanks .

Bibhu.

Avatar

Former Community Member

If you are using the SQL option then you woudl be writing a SQL command like

Insert into tablename ........

Then this sql needs to be executed in the context of the data connection ....if that still does not work then I woudl suggest looking to see if there is something wrong with your DB...maybe you do not have rights to add a new record?

Can you add a record using another tool (some tool that lets you write SQL statements)?

Paul

Avatar

Level 9

Hello Paul ,

Sorry for the late reply as I was busy with other projects . The form is quite simple as it has only 2 fields . If i would select SQL query instead of table and in addition if I write the INSERT statement then Is it possible to perform other options like update , next etc ?? I have no other tools installed in my system so can't tell more about other systems.

Thanks .

Bibhu

Avatar

Level 9

Hello Paul ,

That insert staement showed some errors. Below I am giving my XML codes.

<?xml version="1.0" encoding="UTF-8"?>
<?xfa generator="AdobeLiveCycleDesignerES_V8.2.1.3144.1.471865" APIVersion="2.8.8118.0"?>
<xdp:xdp xmlns:xdp="http://ns.adobe.com/xdp/" timeStamp="2010-06-07T13:01:23Z" uuid="62ca1680-7f1c-4384-9f43-260d44cc6bab">
<template xmlns="http://www.xfa.org/schema/xfa-template/2.6/">
   <?formServer defaultPDFRenderFormat acrobat8.1static?>
   <?formServer allowRenderCaching 0?>
   <?formServer formModel both?>
   <subform name="form1" layout="tb" locale="en_US" restoreState="auto">
      <pageSet>
         <pageArea name="Page1" id="Page1">
            <contentArea x="0.25in" y="0.25in" w="576pt" h="756pt"/>
            <medium stock="default" short="612pt" long="792pt"/>
            <?templateDesigner expand 1?></pageArea>
         <?templateDesigner expand 1?></pageSet>
      <subform w="576pt" h="756pt">
         <field name="UserID" y="41.275mm" x="41.275mm" w="62mm" h="9mm">
            <ui>
               <numericEdit>
                  <border>
                     <?templateDesigner StyleID aped3?>
                     <edge stroke="lowered"/>
                  </border>
                  <margin/>
               </numericEdit>
            </ui>
            <font typeface="Myriad Pro"/>
            <margin topInset="1mm" bottomInset="1mm" leftInset="1mm" rightInset="1mm"/>
            <para vAlign="middle"/>
            <value>
               <integer/>
            </value>
            <caption reserve="25mm">
               <font typeface="Myriad Pro"/>
               <para vAlign="middle"/>
               <value>
                  <text>User I D</text>
               </value>
            </caption>
            <bind match="dataRef" ref="$record.Demo.UserID"/>
         </field>
         <field name="Name" y="41.275mm" x="103.275mm" w="62mm" h="9mm">
            <ui>
               <textEdit>
                  <border>
                     <?templateDesigner StyleID aped3?>
                     <edge stroke="lowered"/>
                  </border>
                  <margin/>
               </textEdit>
            </ui>
            <font typeface="Myriad Pro"/>
            <margin topInset="1mm" bottomInset="1mm" leftInset="1mm" rightInset="1mm"/>
            <para vAlign="middle"/>
            <caption reserve="25mm">
               <font typeface="Myriad Pro"/>
               <para vAlign="middle"/>
               <value>
                  <text>Name</text>
               </value>
            </caption>
            <bind match="dataRef" ref="$record.Demo.Name"/>
         </field>
         <field name="Button1" y="63.5mm" x="82.55mm" w="28.575mm" h="6mm">
            <ui>
               <button highlight="inverted"/>
            </ui>
            <font typeface="Myriad Pro"/>
            <caption>
               <value>
                  <text>Button</text>
               </value>
               <para vAlign="middle" hAlign="center"/>
               <font typeface="Myriad Pro"/>
            </caption>
            <border hand="right">
               <?templateDesigner StyleID apbx2?>
               <edge stroke="raised"/>
               <fill>
                  <color value="212,208,200"/>
               </fill>
            </border>
            <bind match="none"/>
            <event activity="click" name="event__click">
               <script>
xfa.sourceSet.Demo.addNew()
</script>
            </event>
         </field>
         <?templateDesigner expand 1?></subform>
      <proto/>
      <desc>
         <text name="version">8.2.1.3144.1.471865.466429</text>
      </desc>
      <?templateDesigner expand 1?></subform>
   <?templateDesigner Zoom 98?>
   <?templateDesigner Grid show:1, snap:1, units:0, color:ff8080, origin:(0,0), interval:(125000,125000)?>
   <?templateDesigner FormTargetVersion 26?>
   <?originalXFAVersion http://www.xfa.org/schema/xfa-template/2.6/?>;
   <?templateDesigner DefaultLanguage FormCalc?>
   <?templateDesigner DefaultRunAt client?>
   <?acrobat JavaScript strictScoping?>
   <?PDFPrintOptions embedViewerPrefs 0?>
   <?PDFPrintOptions embedPrintOnFormOpen 0?>
   <?PDFPrintOptions duplexMode 0?>
   <?templateDesigner DefaultPreviewType interactive?>
   <?templateDesigner XDPPreviewFormat 20?>
   <?templateDesigner SaveTaggedPDF 1?>
   <?templateDesigner SavePDFWithEmbeddedFonts 1?>
   <?templateDesigner Rulers horizontal:1, vertical:1, guidelines:1, crosshairs:0?></template>
<config xmlns="http://www.xfa.org/schema/xci/2.6/">
   <agent name="designer">
      <destination>pdf</destination>
      <pdf>
         <!--  [0..n]  -->
         <fontInfo/>
      </pdf>
   </agent>
   <present>
      <!--  [0..n]  -->
      <pdf>
         <!--  [0..n]  -->
         <version>1.7</version>
         <adobeExtensionLevel>1</adobeExtensionLevel>
      </pdf>
      <common/>
      <script>
         <runScripts>server</runScripts>
      </script>
      <cache>
         <macroCache/>
      </cache>
      <xdp>
         <packets>*</packets>
      </xdp>
   </present>
   <?originalXFAVersion http://www.xfa.org/schema/xci/2.8/?></config>;
<sourceSet xmlns="http://www.xfa.org/schema/xfa-source-set/2.4/">
   <source name="Demo" db="ado">
      <connect delayedOpen="0" timeout="15">
         <connectString>Provider=MSDASQL.1;Persist Security Info=False;User ID=root;Data Source=Demo;Mode=ReadWrite</connectString>
         <user>root</user>
         <password>mindfire</password>
      </connect>
      <command timeout="30">
         <query commandType="table">
            <select>mail</select>
            <recordSet cursorType="static" cursorLocation="client" lockType="optimistic" bofAction="moveFirst" eofAction="moveLast"/>
            <map from="UserID" bind="#bind4">
               <?templateDesigner DataType 19?></map>
            <map from="Name" bind="#bind5">
               <?templateDesigner DataType 129?></map>
         </query>
      </command>
      <bind id="bind4" ref="$record.Demo.UserID"/>
      <bind id="bind5" ref="$record.Demo.Name"/>
   </source>
</sourceSet>
<localeSet xmlns="http://www.xfa.org/schema/xfa-locale-set/2.6/">
   <locale name="en_US" desc="English (United States)">
      <calendarSymbols name="gregorian">
         <monthNames>
            <month>January</month>
            <month>February</month>
            <month>March</month>
            <month>April</month>
            <month>May</month>
            <month>June</month>
            <month>July</month>
            <month>August</month>
            <month>September</month>
            <month>October</month>
            <month>November</month>
            <month>December</month>
         </monthNames>
         <monthNames abbr="1">
            <month>Jan</month>
            <month>Feb</month>
            <month>Mar</month>
            <month>Apr</month>
            <month>May</month>
            <month>Jun</month>
            <month>Jul</month>
            <month>Aug</month>
            <month>Sep</month>
            <month>Oct</month>
            <month>Nov</month>
            <month>Dec</month>
         </monthNames>
         <dayNames>
            <day>Sunday</day>
            <day>Monday</day>
            <day>Tuesday</day>
            <day>Wednesday</day>
            <day>Thursday</day>
            <day>Friday</day>
            <day>Saturday</day>
         </dayNames>
         <dayNames abbr="1">
            <day>Sun</day>
            <day>Mon</day>
            <day>Tue</day>
            <day>Wed</day>
            <day>Thu</day>
            <day>Fri</day>
            <day>Sat</day>
         </dayNames>
         <meridiemNames>
            <meridiem>AM</meridiem>
            <meridiem>PM</meridiem>
         </meridiemNames>
         <eraNames>
            <era>BC</era>
            <era>AD</era>
         </eraNames>
      </calendarSymbols>
      <datePatterns>
         <datePattern name="full">EEEE, MMMM D, YYYY</datePattern>
         <datePattern name="long">MMMM D, YYYY</datePattern>
         <datePattern name="med">MMM D, YYYY</datePattern>
         <datePattern name="short">M/D/YY</datePattern>
      </datePatterns>
      <timePatterns>
         <timePattern name="full">h:MM:SS A Z</timePattern>
         <timePattern name="long">h:MM:SS A Z</timePattern>
         <timePattern name="med">h:MM:SS A</timePattern>
         <timePattern name="short">h:MM A</timePattern>
      </timePatterns>
      <dateTimeSymbols>GyMdkHmsSEDFwWahKzZ</dateTimeSymbols>
      <numberPatterns>
         <numberPattern name="numeric">z,zz9.zzz</numberPattern>
         <numberPattern name="currency">$z,zz9.99|($z,zz9.99)</numberPattern>
         <numberPattern name="percent">z,zz9%</numberPattern>
      </numberPatterns>
      <numberSymbols>
         <numberSymbol name="decimal">.</numberSymbol>
         <numberSymbol name="grouping">,</numberSymbol>
         <numberSymbol name="percent">%</numberSymbol>
         <numberSymbol name="minus">-</numberSymbol>
         <numberSymbol name="zero">0</numberSymbol>
      </numberSymbols>
      <currencySymbols>
         <currencySymbol name="symbol">$</currencySymbol>
         <currencySymbol name="isoname">USD</currencySymbol>
         <currencySymbol name="decimal">.</currencySymbol>
      </currencySymbols>
      <typefaces>
         <typeface name="Myriad Pro"/>
         <typeface name="Minion Pro"/>
         <typeface name="Courier Std"/>
         <typeface name="Adobe Pi Std"/>
         <typeface name="Adobe Hebrew"/>
         <typeface name="Adobe Arabic"/>
         <typeface name="Adobe Thai"/>
         <typeface name="Kozuka Gothic Pro-VI M"/>
         <typeface name="Kozuka Mincho Pro-VI R"/>
         <typeface name="Adobe Ming Std L"/>
         <typeface name="Adobe Song Std L"/>
         <typeface name="Adobe Myungjo Std M"/>
      </typefaces>
   </locale>
   <?originalXFAVersion http://www.xfa.org/schema/xfa-locale-set/2.6/?></localeSet>;
<xfa:datasets xmlns:xfa="http://www.xfa.org/schema/xfa-data/1.0/">
   <xfa:data xfa:dataNode="dataGroup"/>
</xfa:datasets>
<x:xmpmeta xmlns:x="adobe:ns:meta/" x:xmptk="Adobe XMP Core 4.2.1-c041 52.337767, 2008/04/13-15:41:00        ">
   <rdf:RDF xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#">
      <rdf:Description xmlns:xmp="http://ns.adobe.com/xap/1.0/" rdf:about="">
         <xmp:MetadataDate>2010-06-07T13:01:23Z</xmp:MetadataDate>
         <xmp:CreatorTool>Adobe LiveCycle Designer ES 8.2</xmp:CreatorTool>
         <xmp:ModifyDate>2010-06-07T10:50:06+05:30</xmp:ModifyDate>
         <xmp:CreateDate>2010-06-04T12:08:32+05:30</xmp:CreateDate>
      </rdf:Description>
      <rdf:Description xmlns:pdf="http://ns.adobe.com/pdf/1.3/" rdf:about="">
         <pdf:Producer>Adobe LiveCycle Designer ES 8.2</pdf:Producer>
      </rdf:Description>
      <rdf:Description xmlns:xmpMM="http://ns.adobe.com/xap/1.0/mm/" rdf:about="">
         <xmpMM:DocumentID>uuid:62ca1680-7f1c-4384-9f43-260d44cc6bab</xmpMM:DocumentID>
         <xmpMM:InstanceID>uuid:1a6259a7-8fcc-414a-9c0a-40b62c25e679</xmpMM:InstanceID>
      </rdf:Description>
      <rdf:Description xmlns:dc="http://purl.org/dc/elements/1.1/" rdf:about="">
         <dc:format>application/pdf</dc:format>
      </rdf:Description>
      <rdf:Description xmlns:desc="http://ns.adobe.com/xfa/promoted-desc/" rdf:about="">
         <desc:version rdf:parseType="Resource">
            <rdf:value>8.2.1.3144.1.471865.466429</rdf:value>
            <desc:ref>/template/subform[1]</desc:ref>
         </desc:version>
      </rdf:Description>
   </rdf:RDF>
</x:xmpmeta>
<xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">
   <annots/>
</xfdf></xdp:xdp>