Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

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

Level 10

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

Level 10

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

Paul

Avatar

Level 10

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

Level 10

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

Level 10

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

Level 10

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

Level 10

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