Need help on using single parameter to insert into two values

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

Answer 1

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?

Answer 2

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

Answer 3

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.  

Answer 4

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)