Does Gridview UpdateCommand recognize IF?

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Mark B
    #1

    Does Gridview UpdateCommand recognize IF?

    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.tblLanguage Values(Language Code, LookupID,
    LanguageText)
    VALUES (@LanguageCode, @LookupID, @LanguageText)
    ELSE
    UPDATE dbo.tblLanguage Values
    SET LanguageText = @LanguageText
    WHERE LanguageCode = @LanguageCode AND LookupID = @LookupID"


    based on the SELECT stored procedure for the Gridview:


    ALTER PROCEDURE [dbo].[uspGeneralLangu ageMaintenanceV aluesGet]
    @LanguageCode varchar(20)
    AS
    IF @LanguageCode = 'EN-US'
    SELECT
    AutoNumberID, LanguageCode,
    LookupID, LanguageText, LanguageText as English, ToDo
    FROM
    dbo.tblLanguage Values
    WHERE
    LanguageCode = 'EN-US'
    ELSE
    SELECT
    LV.AutoNumberID , @LanguageCode AS LanguageCode,
    US.LookupID, LV.LanguageText , US.LanguageText as English,
    LV.ToDo
    FROM
    dbo.tblLanguage Values US
    LEFT OUTER JOIN dbo.tblLanguage Values LV
    ON US.LookupID = LV.LookupID AND
    LV.LanguageCode = @LanguageCode
    WHERE
    US.LanguageCode = 'EN-US'


    Tags: None
    • Uri Dimant
      #2
      Re: Does Gridview UpdateCommand recognize IF?

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

      "Mark B" <none123@none.c omwrote in message
      news:e8s5PKzBJH A.5196@TK2MSFTN GP04.phx.gbl...
      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.tblLanguage Values(Language Code, LookupID,
      LanguageText)
      VALUES (@LanguageCode, @LookupID, @LanguageText)
      ELSE
      UPDATE dbo.tblLanguage Values
      SET LanguageText = @LanguageText
      WHERE LanguageCode = @LanguageCode AND LookupID = @LookupID"
      >
      >
      based on the SELECT stored procedure for the Gridview:
      >
      >
      ALTER PROCEDURE [dbo].[uspGeneralLangu ageMaintenanceV aluesGet]
      @LanguageCode varchar(20)
      AS
      IF @LanguageCode = 'EN-US'
      SELECT
      AutoNumberID, LanguageCode,
      LookupID, LanguageText, LanguageText as English, ToDo
      FROM
      dbo.tblLanguage Values
      WHERE
      LanguageCode = 'EN-US'
      ELSE
      SELECT
      LV.AutoNumberID , @LanguageCode AS LanguageCode,
      US.LookupID, LV.LanguageText , US.LanguageText as English,
      LV.ToDo
      FROM
      dbo.tblLanguage Values US
      LEFT OUTER JOIN dbo.tblLanguage Values LV
      ON US.LookupID = LV.LookupID AND
      LV.LanguageCode = @LanguageCode
      WHERE
      US.LanguageCode = 'EN-US'
      >
      >

        Comment

        • Mark B
          #3
          Re: Does Gridview UpdateCommand recognize IF?


          It's part of the SELECT statement for the Gridview in the stored procedure
          (uspGeneralLang uageMaintenance ValuesGet) 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:SqlDataSou rce ID="SqlDataSour ce3" runat="server"
          ConnectionStrin g="<%$ ConnectionStrin gs:MyConnection String %>"
          DeleteCommand=" DELETE FROM [tblLanguageValu es] WHERE [LanguageCode] =
          @LanguageCode AND [LookupID] = @LookupID"
          InsertCommand=" INSERT INTO [tblLanguageValu es] ([LanguageText],
          [LanguageCode], [LookupID]) VALUES (@LanguageText, @LanguageCode,
          @LookupID)"
          SelectCommand=" uspGeneralLangu ageMaintenanceV aluesGet"
          SelectCommandTy pe="StoredProce dure"
          UpdateCommand="
          IF @AutoNumberID IS NULL
          INSERT INTO dbo.tblLanguage Values(Language Code, LookupID,
          LanguageText)
          VALUES (@LanguageCode, @LookupID, @LanguageText)
          ELSE
          UPDATE dbo.tblLanguage Values
          SET LanguageText = @LanguageText
          WHERE LanguageCode = @LanguageCode AND LookupID = @LookupID">

          <UpdateParamete rs>
          <asp:Paramete r Name="AutoNumbe rID" Type="Int32" />
          <asp:Paramete r Name="LanguageT ext" Type="String" />
          <asp:Paramete r Name="LanguageC ode" Type="String"/>
          <asp:Paramete r Name="LookupID" Type="Int32"/>
          </UpdateParameter s>

          <SelectParamete rs>
          <asp:Paramete r Name="LanguageC ode" Type="String" />
          </SelectParameter s>

          <DeleteParamete rs>
          <asp:Paramete r Name="LanguageC ode" Type="String" />
          <asp:Paramete r Name="LookupID" Type="Int32" />
          </DeleteParameter s>

          <InsertParamete rs>
          <asp:Paramete r Name="LanguageT ext" Type="String" />
          <asp:Paramete r Name="LanguageC ode" Type="String" />
          <asp:Paramete r Name="LookupID" Type="Int32" />
          </InsertParameter s>
          </asp:C>






          "Uri Dimant" <urid@iscar.co. ilwrote in message
          news:OThPeR0BJH A.3392@TK2MSFTN GP03.phx.gbl...
          Mark
          Where do you assign a value to @AutoNumberID variable?
          >
          "Mark B" <none123@none.c omwrote in message
          news:e8s5PKzBJH A.5196@TK2MSFTN GP04.phx.gbl...
          >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
          >@AutoNumberI D parameter. Any ideas?
          >>
          >UpdateCommand= "
          > IF @AutoNumberID IS NULL
          > INSERT INTO dbo.tblLanguage Values(Language Code, LookupID,
          >LanguageText )
          > VALUES (@LanguageCode, @LookupID, @LanguageText)
          > ELSE
          > UPDATE dbo.tblLanguage Values
          > SET LanguageText = @LanguageText
          > WHERE LanguageCode = @LanguageCode AND LookupID = @LookupID"
          >>
          >>
          >based on the SELECT stored procedure for the Gridview:
          >>
          >>
          >ALTER PROCEDURE [dbo].[uspGeneralLangu ageMaintenanceV aluesGet]
          > @LanguageCode varchar(20)
          >AS
          > IF @LanguageCode = 'EN-US'
          > SELECT
          > AutoNumberID, LanguageCode,
          > LookupID, LanguageText, LanguageText as English, ToDo
          > FROM
          > dbo.tblLanguage Values
          > WHERE
          > LanguageCode = 'EN-US'
          > ELSE
          > SELECT
          > LV.AutoNumberID , @LanguageCode AS LanguageCode,
          > US.LookupID, LV.LanguageText , US.LanguageText as English,
          >LV.ToDo
          > FROM
          > dbo.tblLanguage Values US
          > LEFT OUTER JOIN dbo.tblLanguage Values LV
          > ON US.LookupID = LV.LookupID AND
          > LV.LanguageCode = @LanguageCode
          > WHERE
          > US.LanguageCode = 'EN-US'
          >>
          >>
          >
          >

            Comment

            Previous template Next
            Working...