Update not possible in numeric fields

Question

User810354248 posted

In my asp.net+vb+sql db. I have a attendance table. as under

USE [FTH_BLR]
GO

/****** Object:  Table [dbo].[Attendance]    Script Date: 07-Mar-19 8:26:12 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Attendance](
	[AttID] [int] IDENTITY(1,1) NOT NULL,
	[EmpNo] [varchar](50) NULL,
	[Name] [varchar](50) NULL,
	[Designation] [nchar](10) NULL,
	[1] [varchar](50) NULL,
	[2] [varchar](50) NULL,
	[3] [varchar](50) NULL,
	[4] [varchar](50) NULL,
	[5] [varchar](50) NULL,
	[6] [varchar](50) NULL,
	[7] [varchar](50) NULL,
	[8] [varchar](50) NULL,
	[9] [varchar](50) NULL,
	[10] [varchar](50) NULL,
 CONSTRAINT [PK_Attendance] PRIMARY KEY CLUSTERED 
(
	[AttID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

i am using SqlDataSource and gridview to update it

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:baijuep %>" DeleteCommand="DELETE FROM [Attendance] WHERE [AttID] = @original_AttID AND (([EmpNo] = @original_EmpNo) OR ([EmpNo] IS NULL AND @original_EmpNo IS NULL)) AND (([Name] = @original_Name) OR ([Name] IS NULL AND @original_Name IS NULL)) AND (([Designation] = @original_Designation) OR ([Designation] IS NULL AND @original_Designation IS NULL)) AND (([1] = @original_column1) OR ([1] IS NULL AND @original_column1 IS NULL)) AND (([2] = @original_column2) OR ([2] IS NULL AND @original_column2 IS NULL)) AND (([3] = @original_column3) OR ([3] IS NULL AND @original_column3 IS NULL)) AND (([4] = @original_column4) OR ([4] IS NULL AND @original_column4 IS NULL)) AND (([5] = @original_column5) OR ([5] IS NULL AND @original_column5 IS NULL)) AND (([6] = @original_column6) OR ([6] IS NULL AND @original_column6 IS NULL)) AND (([7] = @original_column7) OR ([7] IS NULL AND @original_column7 IS NULL)) AND (([8] = @original_column8) OR ([8] IS NULL AND @original_column8 IS NULL)) AND (([9] = @original_column9) OR ([9] IS NULL AND @original_column9 IS NULL)) AND (([10] = @original_column10) OR ([10] IS NULL AND @original_column10 IS NULL))" InsertCommand="INSERT INTO [Attendance] ([EmpNo], [Name], [Designation], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10]) VALUES (@EmpNo, @Name, @Designation, @column1, @column2, @column3, @column4, @column5, @column6, @column7, @column8, @column9, @column10)" SelectCommand="SELECT * FROM [Attendance]" UpdateCommand="UPDATE [Attendance] SET [EmpNo] = @EmpNo, [Name] = @Name, [Designation] = @Designation, [1] = @column1, [2] = @column2, [3] = @column3, [4] = @column4, [5] = @column5, [6] = @column6, [7] = @column7, [8] = @column8, [9] = @column9, [10] = @column10 WHERE [AttID] = @original_AttID AND (([EmpNo] = @original_EmpNo) OR ([EmpNo] IS NULL AND @original_EmpNo IS NULL)) AND (([Name] = @original_Name) OR ([Name] IS NULL AND @original_Name IS NULL)) AND (([Designation] = @original_Designation) OR ([Designation] IS NULL AND @original_Designation IS NULL)) AND (([1] = @original_column1) OR ([1] IS NULL AND @original_column1 IS NULL)) AND (([2] = @original_column2) OR ([2] IS NULL AND @original_column2 IS NULL)) AND (([3] = @original_column3) OR ([3] IS NULL AND @original_column3 IS NULL)) AND (([4] = @original_column4) OR ([4] IS NULL AND @original_column4 IS NULL)) AND (([5] = @original_column5) OR ([5] IS NULL AND @original_column5 IS NULL)) AND (([6] = @original_column6) OR ([6] IS NULL AND @original_column6 IS NULL)) AND (([7] = @original_column7) OR ([7] IS NULL AND @original_column7 IS NULL)) AND (([8] = @original_column8) OR ([8] IS NULL AND @original_column8 IS NULL)) AND (([9] = @original_column9) OR ([9] IS NULL AND @original_column9 IS NULL)) AND (([10] = @original_column10) OR ([10] IS NULL AND @original_column10 IS NULL))" ConflictDetection="CompareAllValues" OldValuesParameterFormatString="original_{0}">
        <DeleteParameters>
            <asp:Parameter Name="original_AttID" Type="Int32" />
            <asp:Parameter Name="original_EmpNo" Type="String" />
            <asp:Parameter Name="original_Name" Type="String" />
            <asp:Parameter Name="original_Designation" Type="String" />
            <asp:Parameter Name="original_column1" Type="String" />
            <asp:Parameter Name="original_column2" Type="String" />
            <asp:Parameter Name="original_column3" Type="String" />
            <asp:Parameter Name="original_column4" Type="String" />
            <asp:Parameter Name="original_column5" Type="String" />
            <asp:Parameter Name="original_column6" Type="String" />
            <asp:Parameter Name="original_column7" Type="String" />
            <asp:Parameter Name="original_column8" Type="String" />
            <asp:Parameter Name="original_column9" Type="String" />
            <asp:Parameter Name="original_column10" Type="String" />
        </DeleteParameters>
        <InsertParameters>
            <asp:Parameter Name="EmpNo" Type="String" />
            <asp:Parameter Name="Name" Type="String" />
            <asp:Parameter Name="Designation" Type="String" />
            <asp:Parameter Name="column1" Type="String" />
            <asp:Parameter Name="column2" Type="String" />
            <asp:Parameter Name="column3" Type="String" />
            <asp:Parameter Name="column4" Type="String" />
            <asp:Parameter Name="column5" Type="String" />
            <asp:Parameter Name="column6" Type="String" />
            <asp:Parameter Name="column7" Type="String" />
            <asp:Parameter Name="column8" Type="String" />
            <asp:Parameter Name="column9" Type="String" />
            <asp:Parameter Name="column10" Type="String" />
        </InsertParameters>
        <UpdateParameters>
            <asp:Parameter Name="EmpNo" Type="String" />
            <asp:Parameter Name="Name" Type="String" />
            <asp:Parameter Name="Designation" Type="String" />
            <asp:Parameter Name="column1" Type="String" />
            <asp:Parameter Name="column2" Type="String" />
            <asp:Parameter Name="column3" Type="String" />
            <asp:Parameter Name="column4" Type="String" />
            <asp:Parameter Name="column5" Type="String" />
            <asp:Parameter Name="column6" Type="String" />
            <asp:Parameter Name="column7" Type="String" />
            <asp:Parameter Name="column8" Type="String" />
            <asp:Parameter Name="column9" Type="String" />
<asp:Parameter Name="column10" Type="String"></asp:Parameter>
            <asp:Parameter Name="original_AttID" Type="Int32" />
            <asp:Parameter Name="original_EmpNo" Type="String" />
            <asp:Parameter Name="original_Name" Type="String" />
            <asp:Parameter Name="original_Designation" Type="String" />
            <asp:Parameter Name="original_column1" Type="String" />
            <asp:Parameter Name="original_column2" Type="String" />
            <asp:Parameter Name="original_column3" Type="String" />
            <asp:Parameter Name="original_column4" Type="String" />
            <asp:Parameter Name="original_column5" Type="String" />
            <asp:Parameter Name="original_column6" Type="String" />
            <asp:Parameter Name="original_column7" Type="String" />
            <asp:Parameter Name="original_column8" Type="String" />
            <asp:Parameter Name="original_column9" Type="String" />
            <asp:Parameter Name="original_column10" Type="String" />
        </UpdateParameters>
    </asp:SqlDataSource>

If i change the field name to One instead of 1 then I can update it.

My idea is to maintain Present (P) into database for all days from 1 to 31 of the months

Answer 1

User753101303 posted

Hi,

You could use d01, d02, d03 etc... for example.

Always be explicit about what happens. You don't have any error message when it "doesn't work" ? It's been a while but if I remember a SqlDataSource is ignoring exceptions by default so you need to do that (likely in the Selected event to check if it worked fine or if you need to report something).

Edit: in tis case it would in the Updated event. Check if e.Exception is null. If I remember I had some kind of default event handler I was using to at least be warned about exceptions or something like that...

Answer 2

User-893317190 posted

Hi Baiju EP,

Not clear about how you update field, in my case  even the column is numeric , sqldatasource  could also update it.

Below is my code.

     <asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1" DataKeyNames="AttID"   AutoGenerateDeleteButton="true" AutoGenerateEditButton="true" >

        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:EntityExeConnectionString %>" DeleteCommand="DELETE FROM [Attendance] WHERE [AttID] = @original_AttID AND (([EmpNo] = @original_EmpNo) OR ([EmpNo] IS NULL AND @original_EmpNo IS NULL)) AND (([Name] = @original_Name) OR ([Name] IS NULL AND @original_Name IS NULL)) AND (([Designation] = @original_Designation) OR ([Designation] IS NULL AND @original_Designation IS NULL)) AND (([1] = @original_column1) OR ([1] IS NULL AND @original_column1 IS NULL)) AND (([2] = @original_column2) OR ([2] IS NULL AND @original_column2 IS NULL)) AND (([3] = @original_column3) OR ([3] IS NULL AND @original_column3 IS NULL))" InsertCommand="INSERT INTO [Attendance] ([EmpNo], [Name], [Designation], [1], [2], [3]) VALUES (@EmpNo, @Name, @Designation, @column1, @column2, @column3)" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT [AttID], [EmpNo], [Name], [Designation], [1] AS column1, [2] AS column2, [3] AS column3 FROM [Attendance]" UpdateCommand="UPDATE [Attendance] SET [EmpNo] = @EmpNo, [Name] = @Name, [Designation] = @Designation, [1] = @column1, [2] = @column2, [3] = @column3 WHERE [AttID] = @original_AttID AND (([EmpNo] = @original_EmpNo) OR ([EmpNo] IS NULL AND @original_EmpNo IS NULL)) AND (([Name] = @original_Name) OR ([Name] IS NULL AND @original_Name IS NULL)) AND (([Designation] = @original_Designation) OR ([Designation] IS NULL AND @original_Designation IS NULL)) AND (([1] = @original_column1) OR ([1] IS NULL AND @original_column1 IS NULL)) AND (([2] = @original_column2) OR ([2] IS NULL AND @original_column2 IS NULL)) AND (([3] = @original_column3) OR ([3] IS NULL AND @original_column3 IS NULL))">
            <DeleteParameters>
                <asp:Parameter Name="original_AttID" Type="Int32" />
                <asp:Parameter Name="original_EmpNo" Type="String" />
                <asp:Parameter Name="original_Name" Type="String" />
                <asp:Parameter Name="original_Designation" Type="String" />
                <asp:Parameter Name="original_column1" Type="String" />
                <asp:Parameter Name="original_column2" Type="String" />
                <asp:Parameter Name="original_column3" Type="String" />
            </DeleteParameters>
            <InsertParameters>
                <asp:Parameter Name="EmpNo" Type="String" />
                <asp:Parameter Name="Name" Type="String" />
                <asp:Parameter Name="Designation" Type="String" />
                <asp:Parameter Name="column1" Type="String" />
                <asp:Parameter Name="column2" Type="String" />
                <asp:Parameter Name="column3" Type="String" />
            </InsertParameters>
            <UpdateParameters>
                <asp:Parameter Name="EmpNo" Type="String" />
                <asp:Parameter Name="Name" Type="String" />
                <asp:Parameter Name="Designation" Type="String" />
                <asp:Parameter Name="column1" Type="String" />
                <asp:Parameter Name="column2" Type="String" />
                <asp:Parameter Name="column3" Type="String" />
                <asp:Parameter Name="original_AttID" Type="Int32" />
                <asp:Parameter Name="original_EmpNo" Type="String" />
                <asp:Parameter Name="original_Name" Type="String" />
                <asp:Parameter Name="original_Designation" Type="String" />
                <asp:Parameter Name="original_column1" Type="String" />
                <asp:Parameter Name="original_column2" Type="String" />
                <asp:Parameter Name="original_column3" Type="String" />
            </UpdateParameters>
        </asp:SqlDataSource>

The result.

Best regards,

Ackerly Xu

Answer 3

User810354248 posted

My field name are like this. [1], [2] to [31]

        [1] [varchar](50) NULL,
	[2] [varchar](50) NULL,
	[3] [varchar](50) NULL,
	[4] [varchar](50) NULL,
	[5] [varchar](50) NULL,
	[6] [varchar](50) NULL,
	[7] [varchar](50) NULL,
	[8] [varchar](50) NULL,
	[9] [varchar](50) NULL,
	[10] [varchar](50) NULL,

Answer 4

User-893317190 posted

Hi Baiju EP ,

Not sure why you want to use number as your column name, have the program shown any error messages?

Your type is varchar(50) , do you input a string having more characters then varchar(50) allows?

Best regards,

Ackerly Xu

Answer 5

User810354248 posted

This is an attendance page and there are days from one to thirty/ thirty one