CODEWITHSUNDEEP
vb.netsqldatasourceupdatecommandselectcommand
Black
Black

Reputation: 1

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

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...!

Upvotes: 0

Views: 148

Answers (1)

gsimo83
gsimo83

Reputation: 109

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)

Upvotes: 0

Related Questions