Scheme problems in VS2005 data connection

tomhirt · Post by **tomhirt** » Mon 02 Jul 2007 18:33

Our former Postgres DBA setup schemes for our main database. I'm new to working with Postgres and VS2005 and have been using a test database on MS SQL Server for development. I did this since our previous .net drivers for Postgres did not work with VS integrated designer. So I was happy to find Corelab's did and have been using the trial version.

However, I have found the only way I can build data connection to Postgres is to define the scheme name. This doesn't work for me as I need to get data from tables in differnt schemes. So I'm trying to build a data connection without defining the scheme name. I'm prepared in my SQL statements to identify the scheme name. However, the data connection appears to default to scheme name PUBLIC when I leave Scheme blank and only shows the one test table.

Any suggestions?

Alexey · Post by **Alexey** » Tue 03 Jul 2007 07:34

Use the following syntax in your SQL statements:

Code: Select all

tomhirt · Post by **tomhirt** » Tue 03 Jul 2007 14:25

Yes, I'm aware of that, and prepared to do that. But when I go into the data source for a gridview for example, it does not see the table even though I specify the scheme name.

I think this is because I'm not building the data connection string correctly. Here's the entry in the web.config:

So as you see, I'm leaving out the scheme name, but when looking at the data connection via the Server Explorer, I see that the scheme name PUBLIC has been added, and just one test table is visbile.

Using the PGadmin tool, signed into Postgres and their is a public scheme with just one table called test.

We have a lot of Access applicaiton in house using ODBC drivers to Postgres and we don't define the scheme and the SQL statements using . work fine. So I must be defining this wrong in VS2005.

Alexey · Post by **Alexey** » Wed 04 Jul 2007 06:49

I don't encounter any problem.
Please describe the steps you perform.

tomhirt · Post by **tomhirt** » Thu 05 Jul 2007 15:04

ok, I think I can explain this better now. Maybe my expectations on using this has to be toned down.

Adding Data connections Properties, I have the following defined -
Host: Server6
Port: 5432
userid:pg_user
password: pg_user
database: plus_be
Scheme:

Test connection ok. Press ok to save config and data connection name is "server6.plus_be.public"

How can I get the public scheme removed? There is a public scheme defined in the Postgres database and it contains one table called "test". So all I can see in the Server Explorer for this data connection is one table called "test".

When I try to build a Gridview, and create a data source on this connection, I'm unable to use the GUI tool to build the query that spans across mutiple schemes, as it only can see the single test table in the public scheme.

I created a new Grid view using the server6.plus_be.public connection string and defining straight forward simple SQL using . and it pulls in the data. I guess I was trying to use a complex query that used T-SQL specific code that needs to be converted to p-sql.

I was really hoping to avoid this and use the Integreated designer in VS2005. Maybe I can use a specific scheme data connector to build the SQL, and then switch to the public data connector when I need to join in tables.

Hope this all makes sense. thanks for putting up with me.

tomhirt · Post by **tomhirt** » Thu 05 Jul 2007 21:36

ok, I think I am able to get past this issue. I can build a query using the scheme specific data connection, and then switch to the neutral data connection and insert the additional ..

But I have a new problem. While I can build the data source, and test the query sucessfuly, I get the following error when press the last OK to save the data source update "Error Invoking 'Configure Data Source...'. Details: Index was out of range. Must be non-negative and less then the size of the collection. Paramater name: index".

Alexey · Post by **Alexey** » Fri 06 Jul 2007 11:07

tomhirt wrote:When I try to build a Gridview, and create a data source on this connection, I'm unable to use the GUI tool to build the query that spans across mutiple schemes, as it only can see the single test table in the public scheme.

I really can't get what is the problem. When you configure data source for your gridview, you can specify a custom SQL statement. Cannot you?

But I have a new problem. While I can build the data source, and test the query sucessfuly, I get the following error when press the last OK to save the data source update "Error Invoking 'Configure Data Source...'. Details: Index was out of range. Must be non-negative and less then the size of the collection. Paramater name: index".

Does this happen with even simplest querries? What is the full version of PostgreSQLDirect .NET you use?

tomhirt · Post by **tomhirt** » Fri 06 Jul 2007 14:33

I can create a custom SQL statement, but I really need to be able to use the GUI query tool to build my SQL. I need all the help I can get when it comes to writing SQL. I can only use the GUI query tool in the Data source configuration of the Gridview when I specify a data connection that points to a specific scheme.

I have the following schemes in Postegres (Plus, Forecast, Estimator).

So if I build a data connection that specifies the PLUS scheme, I can now use the GUI query tool in the Gridview's data source and build the SQL, and specify the paramaters to use session variables and jive.

If I use the Data connection with no scheme, I can create the customer sql, but I'm not familiar with P-SQL to be effective. That why I was hoping this .net driver would be fully supported with VS2005 integrated designer.

But this may not be a show stopper. If I can use the data connection that specifies a scheme, and build my basic queries, that could be enough to help me create custom SQL to use with the open data connection config to link to the other scheme tables.

As far as my second problem, I may have just been frustrated and tired. This morning I came in and blew away the gridview control I was working on, created a new one, using the scheme specific data source and able to retrieve results from a simple table.

But I'm having problems now building the Update queries. I've used the Advance button option in the past which automatically builds my update statements which I later go back and tweak. But using this .net driver, I press the ADVANCE button in the data source configuration and can not select the option to automatically build the Update/Insert/delete queries (They were all grayed out). The back end table in Postgres has a primary key defined. Is there anything else in Postgres I need to do to allow this option to be ungreyed?

At his point, this may no longer be an issue with the Corelab .net driver and more Postgres and VB2005 functionality. However, I'm so new to all of this, it would be greatly appreciated if you could point me in the direction to understand how to manually build an update query using the parameter and session variables in VS for Postgres.

Alexey · Post by **Alexey** » Fri 06 Jul 2007 15:04

OK, this will be considered as user's request.
We will investigate the possibility of adding such functionality to our data provider.
I'll let you know our progress as soon as possible.

tomhirt · Post by **tomhirt** » Fri 06 Jul 2007 16:43

Which issue, displaying the Schemes in the Server Exployer data connection, or providing the integration to use the Advance button to generate the insert/update/delete statements?

Alexey · Post by **Alexey** » Mon 09 Jul 2007 07:09

I was talking about multiple schemas, because I can't reproduce the second problem. Provide me with definition of your table. I'll try to create SqlDataSource for it.

tomhirt · Post by **tomhirt** » Tue 10 Jul 2007 14:16

Thanks! I appreciate that! Quick question, I've been testing with the trial version. Now I need to purchase, does it matter which version of the .net driver I download, Professional or Standard, for best integrated designer support in VS2005?

Here's the definition for the table in question that needs to get updated. I just need to update the values to the two fields (fldprprob,fldprprobwork).

-- Table: plus.tblprojreqsummary

-- DROP TABLE plus.tblprojreqsummary;

CREATE TABLE plus.tblprojreqsummary
(
fldprindexno integer NOT NULL DEFAULT nextval('plus.fldPRIndexNo18_seq '::text),
fldprprojectno character varying(50),
fldprprojectindexno integer DEFAULT 0,
fldprname character varying(50),
fldprstatus character varying(50),
fldprstatusreason character varying(50),
fldprdatecreated date,
fldprdatesubmitted date,
fldprcomplexid integer DEFAULT 0,
fldprdesignbuild boolean,
fldprponum character varying(50),
fldprpoamt numeric(19,4) DEFAULT 0,
fldprpluspm integer DEFAULT 0,
fldprapprovedby integer DEFAULT 0,
fldpradminapproveby integer DEFAULT 0,
fldprforecastapproveby integer DEFAULT 0,
fldprparentproject boolean,
fldprmasterprojid integer,
fldprtype integer DEFAULT 0,
fldprfiletype integer DEFAULT 0,
fldprautocad boolean,
fldprcadlead integer,
fldprprob double precision DEFAULT 0,
fldprprobwork double precision DEFAULT 0,
fldprsitework boolean,
fldprmemo text,
fldprcompanyid integer DEFAULT 0,
fldprcompanysiteid integer DEFAULT 0,
fldpraddressid integer DEFAULT 0,
fldprbillingnameid integer DEFAULT 0,
fldprclientpm integer DEFAULT 0,
fldprbillingcontactid integer DEFAULT 0,
fldprcontracttype character varying(50),
fldprinvoicefreq character varying(50),
fldprterms character varying(50),
fldprspecialinvoice text,
fldprratesched character varying(50),
fldprrateschedother character varying(50),
fldprrateschedmaster boolean,
fldprtotalcont double precision DEFAULT 0,
fldprcreatedby integer DEFAULT 0,
flddelstartdate date,
fldprtotalequip numeric(19,4) DEFAULT 0,
fldprtotaleng numeric(19,4) DEFAULT 0,
fldprtotalexp numeric(19,4) DEFAULT 0,
fldprtotalsub numeric(19,4) DEFAULT 0,
fldprtotalproject numeric(19,4) DEFAULT 0,
fldprcontractsigner integer DEFAULT 0,
fldedmsoption integer DEFAULT 0, -- used to track EDMS option found in related lkp
fldedmskeepinmaster boolean, -- Keep all dwgs in master project?
fldprtotalcontamt numeric(19,4) DEFAULT 0,
CONSTRAINT tblprojreqsummary_pkey PRIMARY KEY (fldprindexno)
)

Alexey · Post by **Alexey** » Thu 12 Jul 2007 07:43

I can't reproduce the problem. All update commands are generated correctly. Try to reinstall.
For best integration with VS2005 you would need Professional version of our provider.

tomhirt · Post by **tomhirt** » Thu 12 Jul 2007 12:11

Just ordered the prof version and hope to install today. I read somewhere that I need to remove the trial version first and then install the prof version. And I assume I then need to install the same .net driver on the IIS server? My IT folks are resistent, but I told them this needs to happen.

Thanks for testing that table. Maybe as a better way to reproduce my problem, please create a scheme in your test database called "Plus", and then add the table to that scheme and see if you can reproduce my problem. But I'm glad to hear the Update/Insert/Delete options were avail to you.

Alexey · Post by **Alexey** » Thu 12 Jul 2007 13:02

Just ordered the prof version and hope to install today. I read somewhere that I need to remove the trial version first and then install the prof version. And I assume I then need to install the same .net driver on the IIS server? My IT folks are resistent, but I told them this needs to happen.

Yes, you need to uninstall trial first. What is the purpose of installing PostgreSQLDirect .NET on IIS server? This is not necessary.