locked
Need help on using single parameter to insert into two values RRS feed

  • Question

  • User126014556 posted

    Is it possible to use one parameter to insert into two values? For parameter A can it be used to insert into two values in the insert statement?

    VALUES (CAST(RIGHT (CAST(YEAR(@A) AS char(4)), 2) + '' + LEFT ('0' + CAST(MONTH(@A) AS varchar(2)), 2) AS NUMERIC(4)), @A, @B, @C, @D, @E)

    I have in the ASP page the TransfertDate textbox control that I would like to have the parameter to pass into two values for the insert statement.

    <asp:TemplateField HeaderText=" Withdraw Date" SortExpression="TransferDate">
                    <InsertItemTemplate>
                        <asp:TextBox ID="txtWD" runat="server" Text='<%# Bind("TransferDate") %>'></asp:TextBox>
                        <ajaxToolkit:CalendarExtender ID="txtWD_CalendarExtender" runat="server" BehaviorID="txtWD_CalendarExtender" Format="dd/MM/yyyy" TargetControlID="txtWD" />
                    </InsertItemTemplate>
                    <ControlStyle BackColor="#CCCCCC" />
                    <HeaderStyle Font-Names="Verdana" Font-Size="Small" ForeColor="Navy" Wrap="False" />
                </asp:TemplateField>

    The insert statement ASP page

        <asp:SqlDataSource ID="SQLDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:INV_INDConnectionString %>" InsertCommand="INSERT INTO TABLE(P09YM, TransferDate, P09NUM, IND_ADJ, ADJ, STF_ADJ) VALUES (CAST(RIGHT (CAST(YEAR(@A) AS char(4)), 2) + '' + LEFT ('0' + CAST(MONTH(@A) AS varchar(2)), 2) AS NUMERIC(4)), @A, @B, @C, @D, @E)" SelectCommand="SELECT P09YM, TransferDate, P09NUM, IND_ADJ, ADJ, STF_ADJ FROM TABLE">
            <InsertParameters>
                <asp:Parameter Name="A" />
                <asp:Parameter Name="B" />
                <asp:Parameter Name="C" />
                <asp:Parameter Name="D" />
                <asp:Parameter Name="E" />
            </InsertParameters>
        </asp:SqlDataSource>

    I appreciate any help.

    Thanks in advance

    Saturday, June 16, 2018 11:50 AM

Answers

  • User475983607 posted

    Matt99

    The datetime entry is converted in the insert statement to numeric for mmyy format.

    The error that I am getting is

    Cannot insert the value NULL into column 'P09YM'

    I think that the single parameter cannot pass the value into the insert statement.

    INSERT INTO TABLE(P09YM, ABC) values (@A, @A)

    parameter A

    According to the error @A is null and P09YM does not accept null.   There is no indication in your posted code where @A is populated.

    https://msdn.microsoft.com/en-us/library/z72eefad.aspx

    Anyway, in TSQL syntax you can reuse the same variable as much as you like.  

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, June 16, 2018 6:30 PM

All replies

  • User475983607 posted

    Is it possible to use one parameter to insert into two values? For parameter A can it be used to insert into two values in the insert statement?

    It is possible but does not make logical sense if parameter @A is a DateTime type as standard date format functions handling this rather easily.  

    https://msdn.microsoft.com/en-us/library/az4se3k1(v=vs.85).aspx

    https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings

    Secondly, you must use the proper SQL script syntax with is not clear from your question.  Are you have an issue or error?

    Saturday, June 16, 2018 12:13 PM
  • User126014556 posted

    The datetime entry is converted in the insert statement to numeric for mmyy format.

    The error that I am getting is

    Cannot insert the value NULL into column 'P09YM'

    I think that the single parameter cannot pass the value into the insert statement.

    INSERT INTO TABLE(P09YM, ABC) values (@A, @A)

    parameter A

    Saturday, June 16, 2018 6:07 PM
  • User475983607 posted

    Matt99

    The datetime entry is converted in the insert statement to numeric for mmyy format.

    The error that I am getting is

    Cannot insert the value NULL into column 'P09YM'

    I think that the single parameter cannot pass the value into the insert statement.

    INSERT INTO TABLE(P09YM, ABC) values (@A, @A)

    parameter A

    According to the error @A is null and P09YM does not accept null.   There is no indication in your posted code where @A is populated.

    https://msdn.microsoft.com/en-us/library/z72eefad.aspx

    Anyway, in TSQL syntax you can reuse the same variable as much as you like.  

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, June 16, 2018 6:30 PM
  • User126014556 posted

    True,

    It should match the field names for the ASP page, but in TSQL it accepts any veriable, that was the confusing part.

    INSERT INTO TABLE(P09YM, ABC) values (@P09YM, @ABC)

    Saturday, June 16, 2018 8:10 PM