Does Gridview UpdateCommand recognize IF?

  • Thread starter Thread starter Mark B
  • Start date Start date
M

Mark B

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'
 
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 said:
Mark
Where do you assign a value to @AutoNumberID variable?

Mark B said:
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'
Click to expand...
Click to expand...
 
Back
Top