Does Gridview UpdateCommand recognize IF?

Discussion in 'ASP .Net' started by Mark B, Aug 26, 2008.

  1. Mark B

    Mark B Guest

    My gridview update command seems to always execute the first part of the SQL
    statement regardless of whether or not there is a value for the
    @AutoNumberID parameter. Any ideas?

    UpdateCommand="
    IF @AutoNumberID IS NULL
    INSERT INTO dbo.tblLanguageValues(LanguageCode, LookupID,
    LanguageText)
    VALUES (@LanguageCode, @LookupID, @LanguageText)
    ELSE
    UPDATE dbo.tblLanguageValues
    SET LanguageText = @LanguageText
    WHERE LanguageCode = @LanguageCode AND LookupID = @LookupID"


    based on the SELECT stored procedure for the Gridview:


    ALTER PROCEDURE [dbo].[uspGeneralLanguageMaintenanceValuesGet]
    @LanguageCode varchar(20)
    AS
    IF @LanguageCode = 'EN-US'
    SELECT
    AutoNumberID, LanguageCode,
    LookupID, LanguageText, LanguageText as English, ToDo
    FROM
    dbo.tblLanguageValues
    WHERE
    LanguageCode = 'EN-US'
    ELSE
    SELECT
    LV.AutoNumberID, @LanguageCode AS LanguageCode,
    US.LookupID, LV.LanguageText, US.LanguageText as English,
    LV.ToDo
    FROM
    dbo.tblLanguageValues US
    LEFT OUTER JOIN dbo.tblLanguageValues LV
    ON US.LookupID = LV.LookupID AND
    LV.LanguageCode = @LanguageCode
    WHERE
    US.LanguageCode = 'EN-US'
    Mark B, Aug 26, 2008
    #1
    1. Advertising

  2. Mark B

    Uri Dimant Guest

    Mark
    Where do you assign a value to @AutoNumberID variable?

    "Mark B" <> wrote in message
    news:...
    > My gridview update command seems to always execute the first part of the
    > SQL statement regardless of whether or not there is a value for the
    > @AutoNumberID parameter. Any ideas?
    >
    > UpdateCommand="
    > IF @AutoNumberID IS NULL
    > INSERT INTO dbo.tblLanguageValues(LanguageCode, LookupID,
    > LanguageText)
    > VALUES (@LanguageCode, @LookupID, @LanguageText)
    > ELSE
    > UPDATE dbo.tblLanguageValues
    > SET LanguageText = @LanguageText
    > WHERE LanguageCode = @LanguageCode AND LookupID = @LookupID"
    >
    >
    > based on the SELECT stored procedure for the Gridview:
    >
    >
    > ALTER PROCEDURE [dbo].[uspGeneralLanguageMaintenanceValuesGet]
    > @LanguageCode varchar(20)
    > AS
    > IF @LanguageCode = 'EN-US'
    > SELECT
    > AutoNumberID, LanguageCode,
    > LookupID, LanguageText, LanguageText as English, ToDo
    > FROM
    > dbo.tblLanguageValues
    > WHERE
    > LanguageCode = 'EN-US'
    > ELSE
    > SELECT
    > LV.AutoNumberID, @LanguageCode AS LanguageCode,
    > US.LookupID, LV.LanguageText, US.LanguageText as English,
    > LV.ToDo
    > FROM
    > dbo.tblLanguageValues US
    > LEFT OUTER JOIN dbo.tblLanguageValues LV
    > ON US.LookupID = LV.LookupID AND
    > LV.LanguageCode = @LanguageCode
    > WHERE
    > US.LanguageCode = 'EN-US'
    >
    >
    Uri Dimant, Aug 26, 2008
    #2
    1. Advertising

  3. Mark B

    Mark B Guest

    It's part of the SELECT statement for the Gridview in the stored procedure
    (uspGeneralLanguageMaintenanceValuesGet) I listed at the end of this post.
    From what I understand ASP.NET looks for a field in SqlDataSource3 that has
    exactly the same name as the parameter (less the @ character), even if no
    column uses that field name in the Gridview. In my case I added a column
    titled "#" with it's datavalue set to AutoNumberID just in case it needed
    it.

    The Autonumber field by the way is an identity field in the SQL database:
    [AutoNumberID] [int] IDENTITY(1,1) NOT NULL


    <asp:SqlDataSource ID="SqlDataSource3" runat="server"
    ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"
    DeleteCommand="DELETE FROM [tblLanguageValues] WHERE [LanguageCode] =
    @LanguageCode AND [LookupID] = @LookupID"
    InsertCommand="INSERT INTO [tblLanguageValues] ([LanguageText],
    [LanguageCode], [LookupID]) VALUES (@LanguageText, @LanguageCode,
    @LookupID)"
    SelectCommand="uspGeneralLanguageMaintenanceValuesGet"
    SelectCommandType="StoredProcedure"
    UpdateCommand="
    IF @AutoNumberID IS NULL
    INSERT INTO dbo.tblLanguageValues(LanguageCode, LookupID,
    LanguageText)
    VALUES (@LanguageCode, @LookupID, @LanguageText)
    ELSE
    UPDATE dbo.tblLanguageValues
    SET LanguageText = @LanguageText
    WHERE LanguageCode = @LanguageCode AND LookupID = @LookupID">

    <UpdateParameters>
    <asp:parameter Name="AutoNumberID" Type="Int32" />
    <asp:parameter Name="LanguageText" Type="String" />
    <asp:parameter Name="LanguageCode" Type="String"/>
    <asp:parameter Name="LookupID" Type="Int32"/>
    </UpdateParameters>

    <SelectParameters>
    <asp:parameter Name="LanguageCode" Type="String" />
    </SelectParameters>

    <DeleteParameters>
    <asp:parameter Name="LanguageCode" Type="String" />
    <asp:parameter Name="LookupID" Type="Int32" />
    </DeleteParameters>

    <InsertParameters>
    <asp:parameter Name="LanguageText" Type="String" />
    <asp:parameter Name="LanguageCode" Type="String" />
    <asp:parameter Name="LookupID" Type="Int32" />
    </InsertParameters>
    </asp:C>






    "Uri Dimant" <> wrote in message
    news:...
    > Mark
    > Where do you assign a value to @AutoNumberID variable?
    >
    > "Mark B" <> wrote in message
    > news:...
    >> My gridview update command seems to always execute the first part of the
    >> SQL statement regardless of whether or not there is a value for the
    >> @AutoNumberID parameter. Any ideas?
    >>
    >> UpdateCommand="
    >> IF @AutoNumberID IS NULL
    >> INSERT INTO dbo.tblLanguageValues(LanguageCode, LookupID,
    >> LanguageText)
    >> VALUES (@LanguageCode, @LookupID, @LanguageText)
    >> ELSE
    >> UPDATE dbo.tblLanguageValues
    >> SET LanguageText = @LanguageText
    >> WHERE LanguageCode = @LanguageCode AND LookupID = @LookupID"
    >>
    >>
    >> based on the SELECT stored procedure for the Gridview:
    >>
    >>
    >> ALTER PROCEDURE [dbo].[uspGeneralLanguageMaintenanceValuesGet]
    >> @LanguageCode varchar(20)
    >> AS
    >> IF @LanguageCode = 'EN-US'
    >> SELECT
    >> AutoNumberID, LanguageCode,
    >> LookupID, LanguageText, LanguageText as English, ToDo
    >> FROM
    >> dbo.tblLanguageValues
    >> WHERE
    >> LanguageCode = 'EN-US'
    >> ELSE
    >> SELECT
    >> LV.AutoNumberID, @LanguageCode AS LanguageCode,
    >> US.LookupID, LV.LanguageText, US.LanguageText as English,
    >> LV.ToDo
    >> FROM
    >> dbo.tblLanguageValues US
    >> LEFT OUTER JOIN dbo.tblLanguageValues LV
    >> ON US.LookupID = LV.LookupID AND
    >> LV.LanguageCode = @LanguageCode
    >> WHERE
    >> US.LanguageCode = 'EN-US'
    >>
    >>

    >
    >
    Mark B, Aug 26, 2008
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Qwert
    Replies:
    1
    Views:
    338
    Ken Cox [Microsoft MVP]
    Apr 13, 2005
  2. Lee Moore
    Replies:
    0
    Views:
    5,876
    Lee Moore
    Aug 18, 2005
  3. srathin

    DataGrid1 UpdateCommand calling DataGrid2 UpdateCommand

    srathin, Apr 9, 2005, in forum: ASP .Net Datagrid Control
    Replies:
    0
    Views:
    163
    srathin
    Apr 9, 2005
  4. Vaughn Haybittle

    Gridview UpdateCommand

    Vaughn Haybittle, Apr 4, 2007, in forum: ASP .Net Datagrid Control
    Replies:
    0
    Views:
    809
    Vaughn Haybittle
    Apr 4, 2007
  5. srathin

    DataGrid1 UpdateCommand calling DataGrid2 UpdateCommand

    srathin, Apr 9, 2005, in forum: ASP .Net Web Controls
    Replies:
    2
    Views:
    153
    srathin
    Apr 11, 2005
Loading...

Share This Page