SqlDataSource InsertCommand and UpdateCommand throwing "illegal variable name/number""

Question

I am trying to insert and update the table through SqlDataSource

<asp:SqlDataSource ID="SqlDataSource1" OnSelecting="SqlDataSource1_Selecting" runat="server" ConnectionString="<%$ ConnectionStrings:ORAConnString %>" ProviderName="<%$ ConnectionStrings:ORAConnString.ProviderName %>"
    SelectCommand="SELECT DEPT_NAME,CATEGORY_NAME FROM SDIX_TCKT_CATEGORY"
    InsertCommand="Insert into SDIX_TCKT_CATEGORY (DEPT_NAME,CATEGORY_NAME,MODIFIED_BY,MODIFIED_DTTM)  values(:DEPTNAME,:CATEGORYNAME,'','')"  
    UpdateCommand="Update SDIX_TCKT_CATEGORY set CATEGORY_NAME=:CATEGORYNAME,MODIFIED_BY='',MODIFIED_DTTM=SYSDATE where(DEPT_NAME=:DEPTNAME) "                                       >
    <InsertParameters>
        <asp:Parameter Name="DEPTNAME" />
        <asp:Parameter Name="CATEGORYNAME" />                                     
    </InsertParameters>
    <UpdateParameters>
        <asp:Parameter Name="DEPTNAME" />
        <asp:Parameter Name="CATEGORYNAME" />
        <asp:Parameter Name="MODIFIED_BY" />
        <asp:Parameter Name="MODIFIED_DTTM" />
    </UpdateParameters>
</asp:SqlDataSource>

I need to insert null value for MODIFIED_BY,MODIFIED_DTTM when insert new record?

I need to update userid(Need to get from code behind) and sysdate when updating the record in table?

Thanks in advance. Any help is appreciated...!

Your ':CATEGORY_NAME' parameter is spelt different in the statement and the parameter name array -> ':CATEGORYNAME' — Andrew Mortimer, Oct 08 '18 at 11:46
If you need to insert `NULL` then why are you actually inserting an empty string? — jmcilhinney, Oct 08 '18 at 13:28
Correct me if i am wrong. If i use empty string ('')this will insert null into DB. — Black, Oct 08 '18 at 13:36
Is that possible to set the value from code behind for only certain parameter? — Black, Oct 08 '18 at 13:44

gsimo83 · Answer 1 · 2018-10-09T08:33:54.023

0

DBNull.value instead of ''

or simply do not specify null parameters.

EDIT 1: I tell you to use DBNull.value but i forgot that i use it by code

es. YourSqlcommand.Parameters.AddWithValue("@ParameterName", DBNull.value)

so in your sql update or insert probably you have to use the Sql NULL:

 Insert into SDIX_TCKT_CATEGORY (DEPT_NAME,CATEGORY_NAME,MODIFIED_BY,MODIFIED_DTTM) values(:DEPTNAME,:CATEGORYNAME,NULL,NULL)

edited Oct 09 '18 at 08:33

answered Oct 08 '18 at 14:41

gsimo83

109
2
9

No luck,Still i am getting same issue. – Black Oct 09 '18 at 08:06
You can try with: – gsimo83 Oct 09 '18 at 08:27
And i tell you to use DBNull.value but i forgot that i use it by code es. Sqlcommand.Parameters.AddWithValue("@ParameterName", DBNull.value) so in your sql update or insert probably you have to use the Sql NULL: Insert into SDIX_TCKT_CATEGORY (DEPT_NAME,CATEGORY_NAME,MODIFIED_BY,MODIFIED_DTTM) values(:DEPTNAME,:CATEGORYNAME,NULL,NULL) – gsimo83 Oct 09 '18 at 08:31

SqlDataSource InsertCommand and UpdateCommand throwing "illegal variable name/number""

1 Answers1