Thirt
asked on
How to create Update SQL for Postgres in VS2005
Using the Corelab's .net driver for Postgres in VS2005 to hit our production database. I've been developing against MS SQL server and using the Gridview Data Source wizard option to Generate the SQL code for Update/Insert and Delete. This option is found under the Advance button in the wizard.
I notice with MS SQL server, that I had to make sure I have the primary key defined for the table, or the Generate SQL for Update/Insert and Delete will be grayed out. I had to check this since the DTS package to import the Postgres tables didn't include the PK.
So now I'm updating the pages to point to postgres, running the Gridview data source wizard, and the Generate update/insert and delete SQL code option is grayed out. I've doubled check the PK and they are in place. I've testing this again simple tables, making sure to include the primary keys in the SQL. No go.
Could this be a permission issue? I wonder if this isn't an issue with Schemes. I've tried to create a data connection against a specific scheme so that I don't have to inclue the scheme name in the SQL, same problem.
The reason I need to run the generate option because my current Update SQL for MS SQL Server is not working when using Postgres. Current Update SQL:
UPDATE plus.tblprojreqsummary SET fldprprob = @fldprprob, fldprprobwork = @fldprprobwork WHERE (fldprindexno = @fldprindexno)
keep getting the error when I invoke the update on the web page:
Exception Details: CoreLab.PostgreSql.PgSqlEx
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[PgSqlException (0x80004005): Parameter name is missing.]
Any suggestions?
I notice with MS SQL server, that I had to make sure I have the primary key defined for the table, or the Generate SQL for Update/Insert and Delete will be grayed out. I had to check this since the DTS package to import the Postgres tables didn't include the PK.
So now I'm updating the pages to point to postgres, running the Gridview data source wizard, and the Generate update/insert and delete SQL code option is grayed out. I've doubled check the PK and they are in place. I've testing this again simple tables, making sure to include the primary keys in the SQL. No go.
Could this be a permission issue? I wonder if this isn't an issue with Schemes. I've tried to create a data connection against a specific scheme so that I don't have to inclue the scheme name in the SQL, same problem.
The reason I need to run the generate option because my current Update SQL for MS SQL Server is not working when using Postgres. Current Update SQL:
UPDATE plus.tblprojreqsummary SET fldprprob = @fldprprob, fldprprobwork = @fldprprobwork WHERE (fldprindexno = @fldprindexno)
keep getting the error when I invoke the update on the web page:
Exception Details: CoreLab.PostgreSql.PgSqlEx
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[PgSqlException (0x80004005): Parameter name is missing.]
Any suggestions?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
When I try to specify the following update query: UPDATE tblprojreqsummary SET fldprprob = ?, fldprprobwork = ? WHERE fldprindexno = ?
I get the error: Error Invoking 'configure data source..' Details: Index was out of range. Must be non-negative and less then the size of the collection. Paramter name: index
When I look at the source of the gridview I'm apply this data source to, I see:
<cc2:PgSqlDataSource ID="PgSqlPMProposals" runat="server" ConnectionString="<%$ ConnectionStrings:plus_be_
ProviderName="<%$ ConnectionStrings:plus_be_
UpdateCommand="UPDATE tblprojreqsummary SET fldprprob = ?, fldprprobwork = ? WHERE fldprindexno = ? ">
</cc2:PgSqlDataSource>
Maybe this is more of a VS2005 question now, unless you see something I'm screwing up.
But when I look at the data source using MS SQL is see:
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:PP_PlusD
SelectCommand="SELECT [probindexno], [probdesc] FROM [tlkpprobtypes] ORDER BY [probindexno]"
UpdateCommand="UPDATE tblprojreqsummary SET fldprprob = @fldprprob, fldprprobwork = @fldprprobwork WHERE (fldprindexno = @fldprindexno)">
<UpdateParameters>
<asp:Parameter Name="fldprprob" />
<asp:Parameter Name="fldprprobwork" />
<asp:Parameter Name="fldprindexno" />
</UpdateParameters>
So do I need to define the parameters?
I get the error: Error Invoking 'configure data source..' Details: Index was out of range. Must be non-negative and less then the size of the collection. Paramter name: index
When I look at the source of the gridview I'm apply this data source to, I see:
<cc2:PgSqlDataSource ID="PgSqlPMProposals" runat="server" ConnectionString="<%$ ConnectionStrings:plus_be_
ProviderName="<%$ ConnectionStrings:plus_be_
UpdateCommand="UPDATE tblprojreqsummary SET fldprprob = ?, fldprprobwork = ? WHERE fldprindexno = ? ">
</cc2:PgSqlDataSource>
Maybe this is more of a VS2005 question now, unless you see something I'm screwing up.
But when I look at the data source using MS SQL is see:
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:PP_PlusD
SelectCommand="SELECT [probindexno], [probdesc] FROM [tlkpprobtypes] ORDER BY [probindexno]"
UpdateCommand="UPDATE tblprojreqsummary SET fldprprob = @fldprprob, fldprprobwork = @fldprprobwork WHERE (fldprindexno = @fldprindexno)">
<UpdateParameters>
<asp:Parameter Name="fldprprob" />
<asp:Parameter Name="fldprprobwork" />
<asp:Parameter Name="fldprindexno" />
</UpdateParameters>
So do I need to define the parameters?
ASKER
I wonder if I took you too literal Earthman. Do I need to define the Update query as:
UPDATE plus.tblprojreqsummary SET fldprprob = ?fldprprob, fldprprobwork = ?fldprprobwork WHERE fldprindexno = ?fldprindexno
I was wondering what was making the "?" paramaters unquie.
However, with the above update sql set to the data source, when I click finish and return to design view for the gridview control, I get the error: Error invoking "Configure Data Source..." Details: Index was out of range. Must be non-negative and less the size of the collection. Paramater name:index
I tried running the page just to see how far the update process would go. This time when I click Edit, update the field fldprprob value and click Update, I get the error: CoreLab.PostgreSql.PgSqlEx
UPDATE plus.tblprojreqsummary SET fldprprob = ?fldprprob, fldprprobwork = ?fldprprobwork WHERE fldprindexno = ?fldprindexno
I was wondering what was making the "?" paramaters unquie.
However, with the above update sql set to the data source, when I click finish and return to design view for the gridview control, I get the error: Error invoking "Configure Data Source..." Details: Index was out of range. Must be non-negative and less the size of the collection. Paramater name:index
I tried running the page just to see how far the update process would go. This time when I click Edit, update the field fldprprob value and click Update, I get the error: CoreLab.PostgreSql.PgSqlEx
ASKER
Did some additional searching on the "index out of range" and found the following article: ms-help://MS.VSCC.v80/Core
I like the concept of using the named paramters as I better understand that.
So I have updated my Update sql to read:
UpdateCommand="UPDATE plus.tblprojreqsummary SET fldprprob = :fldprprob, fldprprobwork = :fldprprobwork WHERE fldprindexno = @fldprindexno">
And I no longer get the Index out of range error when completing the data source wizard.
However, on the update page, I get an error as it's looking for a paramter:
Exception Details: CoreLab.PostgreSql.PgSqlEx
So back in the gridview update source, I'm trying to figure out how to specify the paramters. But I can't seem to use what the above link recoments for named paramters.
their example:
[Visual Basic]
Dim pgSqlCommand1 as PgSqlCommand
...
pgSqlCommand1.CommandText = "UPDATE dept SET dname = :dname, loc = :loc WHERE deptno = @deptno"
pgSqlCommand1.Parameters.A
pgSqlCommand1.Parameters.A
pgSqlCommand1.Parameters.A
So at this point, I can't figure out how to update my below code with the above example. Any suggestions?
<cc1:PgSqlDataSource ID="PgSqlPlusProposals" runat="server" ConnectionString="<%$ ConnectionStrings:plus_be_
ProviderName="<%$ ConnectionStrings:plus_be_
SelectCommand="SELECT plus.tblprojreqsummary.fld
UpdateCommand="UPDATE plus.tblprojreqsummary SET fldprprob = :fldprprob, fldprprobwork = :fldprprobwork WHERE fldprindexno = @fldprindexno">
<UpdateParameters>
<asp:Parameter Name="fldprprob" Type="String" />
<asp:Parameter Name="fldprprobwork" Type="String" />
<asp:Parameter Name="fldprindexno" Type="String" />
</UpdateParameters>
</cc1:PgSqlDataSource>
I like the concept of using the named paramters as I better understand that.
So I have updated my Update sql to read:
UpdateCommand="UPDATE plus.tblprojreqsummary SET fldprprob = :fldprprob, fldprprobwork = :fldprprobwork WHERE fldprindexno = @fldprindexno">
And I no longer get the Index out of range error when completing the data source wizard.
However, on the update page, I get an error as it's looking for a paramter:
Exception Details: CoreLab.PostgreSql.PgSqlEx
So back in the gridview update source, I'm trying to figure out how to specify the paramters. But I can't seem to use what the above link recoments for named paramters.
their example:
[Visual Basic]
Dim pgSqlCommand1 as PgSqlCommand
...
pgSqlCommand1.CommandText = "UPDATE dept SET dname = :dname, loc = :loc WHERE deptno = @deptno"
pgSqlCommand1.Parameters.A
pgSqlCommand1.Parameters.A
pgSqlCommand1.Parameters.A
So at this point, I can't figure out how to update my below code with the above example. Any suggestions?
<cc1:PgSqlDataSource ID="PgSqlPlusProposals" runat="server" ConnectionString="<%$ ConnectionStrings:plus_be_
ProviderName="<%$ ConnectionStrings:plus_be_
SelectCommand="SELECT plus.tblprojreqsummary.fld
UpdateCommand="UPDATE plus.tblprojreqsummary SET fldprprob = :fldprprob, fldprprobwork = :fldprprobwork WHERE fldprindexno = @fldprindexno">
<UpdateParameters>
<asp:Parameter Name="fldprprob" Type="String" />
<asp:Parameter Name="fldprprobwork" Type="String" />
<asp:Parameter Name="fldprindexno" Type="String" />
</UpdateParameters>
</cc1:PgSqlDataSource>
ASKER
I'm still struggling with this issue. I have another effort going on to track the SQL log. But I think I can keep this discussion going by the following trouble shooting.
I keep getting the error when I try to update a record:
Parameter name is missing. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: CoreLab.PostgreSql.PgSqlEx
I've been trying every which way to get the paramters defined in the update statement of the gridview and I was like, why don't I try to pass a hard coded value SQL update and see what happens. So I updated the update SQL to the below and I'm still getting the exact error message when I click on Update:
<cc1:PgSqlDataSource ID="PgSqlPlusProposals" runat="server" ConnectionString="<%$ ConnectionStrings:plus_be_
ProviderName="<%$ ConnectionStrings:plus_be_
UpdateCommand="UPDATE plus.tblprojreqsummary SET fldprprob = .1, fldprprobwork = .1 WHERE fldprindexno = 995">
<UpdateParameters>
<asp:Parameter Name="fldprprob" Type="Object"/>
<asp:Parameter Name="fldprprobwork" Type="Object"/>
<asp:Parameter Name="fldprindexno" Type="Object"/>
</UpdateParameters>
</cc1:PgSqlDataSource>
I've take the SQL itself "UPDATE plus.tblprojreqsummary SET fldprprob = .1, fldprprobwork = .1 WHERE fldprindexno = 995" and tested on the PGadmin tool in Postgres, and in the query builder tool in VS2005 and the update works. So what am I missing here?
I keep getting the error when I try to update a record:
Parameter name is missing. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: CoreLab.PostgreSql.PgSqlEx
I've been trying every which way to get the paramters defined in the update statement of the gridview and I was like, why don't I try to pass a hard coded value SQL update and see what happens. So I updated the update SQL to the below and I'm still getting the exact error message when I click on Update:
<cc1:PgSqlDataSource ID="PgSqlPlusProposals" runat="server" ConnectionString="<%$ ConnectionStrings:plus_be_
ProviderName="<%$ ConnectionStrings:plus_be_
UpdateCommand="UPDATE plus.tblprojreqsummary SET fldprprob = .1, fldprprobwork = .1 WHERE fldprindexno = 995">
<UpdateParameters>
<asp:Parameter Name="fldprprob" Type="Object"/>
<asp:Parameter Name="fldprprobwork" Type="Object"/>
<asp:Parameter Name="fldprindexno" Type="Object"/>
</UpdateParameters>
</cc1:PgSqlDataSource>
I've take the SQL itself "UPDATE plus.tblprojreqsummary SET fldprprob = .1, fldprprobwork = .1 WHERE fldprindexno = 995" and tested on the PGadmin tool in Postgres, and in the query builder tool in VS2005 and the update works. So what am I missing here?
ASKER
I was able to log the SQL activity on the Postgres server and found that the hard coded SQL is passed correctly. By that I mean, I can cut and paste the Update sql from the log into the PGadmin SQL view tool and run the same query and it works.
Howerver, when I switch the gridview back to using the "?" parameters, I see the following sql display in the log:
<2007-07-23 18:37:16 EDT%7868%pg_user%plus_be>L
<2007-07-23 18:37:16 EDT%7868%pg_user%plus_be>L
At this point, there should be actual values, not $1, $2, $3.
Here's the Update code in the gridview control:
UpdateCommand="UPDATE plus.tblprojreqsummary SET fldprprob = ?, fldprprobwork = ? WHERE fldprindexno = ?">
<UpdateParameters>
<asp:Parameter Name="fldprprob" Type="Object"/>
<asp:Parameter Name="fldprprobwork" Type="Object"/>
<asp:Parameter Name="fldprindexno" Type="Object"/>
Howerver, when I switch the gridview back to using the "?" parameters, I see the following sql display in the log:
<2007-07-23 18:37:16 EDT%7868%pg_user%plus_be>L
<2007-07-23 18:37:16 EDT%7868%pg_user%plus_be>L
At this point, there should be actual values, not $1, $2, $3.
Here's the Update code in the gridview control:
UpdateCommand="UPDATE plus.tblprojreqsummary SET fldprprob = ?, fldprprobwork = ? WHERE fldprindexno = ?">
<UpdateParameters>
<asp:Parameter Name="fldprprob" Type="Object"/>
<asp:Parameter Name="fldprprobwork" Type="Object"/>
<asp:Parameter Name="fldprindexno" Type="Object"/>
ASKER
I thought I would be stuck defining the scheme names because I do have another gridview that needs to gather data based in differnt schemes. So I don't think the Search_path will help me in that instance?