«

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
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'
>
>

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="uspGeneralLanguageMaintenanceValues Get"
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>
<asparameter Name="AutoNumberID" Type="Int32" />
<asparameter Name="LanguageText" Type="String" />
<asparameter Name="LanguageCode" Type="String"/>
<asparameter Name="LookupID" Type="Int32"/>
</UpdateParameters>

<SelectParameters>
<asparameter Name="LanguageCode" Type="String" />
</SelectParameters>

<DeleteParameters>
<asparameter Name="LanguageCode" Type="String" />
<asparameter Name="LookupID" Type="Int32" />
</DeleteParameters>

<InsertParameters>
<asparameter Name="LanguageText" Type="String" />
<asparameter Name="LanguageCode" Type="String" />
<asparameter 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'
>>
>>
>
>