Dataview Update

[Pryer]

I am extremely new to ASP.net and I am trying to get my gridview to update my Postgresql database. I've tried just about everything i've read on this forum, that i could find, with no success. I've seen examples programatically as well as just using the updatecommand, which really confuses me. Can anyone please help or guide me in the right direction? I am using VB I have a connection string and I can get the table in my database to view in the gridview, but nothing but errors when i try and update.

Below are my attempts with the updatecommand.

updatecommand="Update web.dept_aitem set d_asup = @d_asup where d_serial = @d_serial"


updatecommand="Update web.dept_aitem set d_asup = :d_asup where d_serial = :d_serial"

I don't really know what the "@" or the ":" are intended to do, but i saw them in other examples so I tried them with no success.

Any help is much appreciated.

 

[tperri]

Please post your code for the GridView and (Sql)DataSource

 

[Pryer]

tperri,

Below is the requested code, forgive me if i sound ignorant. I am extremely new to all of this.

thank you,

Brent

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:PryerDataConnectionString %>"
        ProviderName="<%$ ConnectionStrings:PryerDataConnectionString.ProviderName %>"
        SelectCommand="select * from web.dept_aitem" UpdateCommand="Update web.dept_aitem set d_asup = @a_asup where d_serial=@d_serial">
    </asp:SqlDataSource>
    <asp:GridView ID="GridView1" runat="server" AllowSorting="True" AutoGenerateColumns="False"
        AutoGenerateDeleteButton="True" AutoGenerateEditButton="True" DataSourceID="SqlDataSource1">
        <Columns>
            <asp:BoundField DataField="d_anum" HeaderText="d_anum" SortExpression="d_anum" />
            <asp:BoundField DataField="d_adept" HeaderText="d_adept" SortExpression="d_adept" />
            <asp:BoundField DataField="d_asup" HeaderText="d_asup" SortExpression="d_asup" />
            <asp:BoundField DataField="d_adesc" HeaderText="d_adesc" SortExpression="d_adesc" />
            <asp:BoundField DataField="d_acomp" HeaderText="d_acomp" SortExpression="d_acomp" />
            <asp:BoundField DataField="d_adue" HeaderText="d_adue" SortExpression="d_adue" />
            <asp:BoundField DataField="d_serial" HeaderText="d_serial" SortExpression="d_serial" />
        </Columns>
    </asp:GridView>

 

[tperri]

Can you show the errors too please?

Thanks

 

[Pryer]

tperri,

here is the error page

 ERROR [HY000] ERROR:  operator does not exist: @ character varying
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: System.Data.Odbc.OdbcException: ERROR [HY000] ERROR:  operator does not exist: @ character varying

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:

[OdbcException (0x80131937): ERROR [HY000] ERROR:  operator does not exist: @ character varying]
   System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) +80
   System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod) +3341
   System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader) +91
   System.Data.Odbc.OdbcCommand.ExecuteNonQuery() +92
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +493
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +919
   System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +179
   System.Web.UI.WebControls.GridView.HandleUpdate(GridViewRow row, Int32 rowIndex, Boolean causesValidation) +1140
   System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +835
   System.Web.UI.WebControls.GridView.OnBubbleEvent(Object source, EventArgs e) +162
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +56
   System.Web.UI.WebControls.GridViewRow.OnBubbleEvent(Object source, EventArgs e) +118
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +56
   System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +107
   System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +175
   System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +31
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +32
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +244
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3840

 

[tperri]

The code looked right, but I think the problem is with the syntax with Postgresql.

I found a link from someone that was having postgresql problems with asp.net, here it is:

http://www.experts-exchange.com/Databases/PostgreSQL/Q_21640437.html

I've no clue about postgresql, so I can't give you the best way to do this. However, here is what I'm seeing.

From your error message it looks like .net framwork is converting your SqlDataSource to an Obdc one, and that is what you really should be usuing with postgresql. Try changing your datasource to an ODBC one instead. I know your GridView is populating, btu I dont think it plays well with SqlServer variable syntax.

If that doesnt work, from what I've had a chance to read on Google links, I don't think that postgresql likes the @ character, so look at this link to see how people did their selects. You may want to programmatically create your selects, updates, inserts, etc.

More links you may be able to dig more into:

http://forums.asp.net/1323952/ShowThread.aspx

http://crlab.com/pgsqlnet/

http://support.jodohost.com/showthread.php?t=6636

http://beta.linuxports.com/pgsql-odbc/2005-07/msg00012.php