• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

SQL Insert Query with Var Q.

I am trying to build an INSERT form in VS05. I have bound all the fields successfully except 1. It is a variable and this is what it should be:   StoreNum ='" & Left(StoreName,2) & "'"  

What is the correct syntax to do this?

SQL:

InsertCommand="INSERT INTO [NumbersGoals] ([CurrentMonth], [StoreNum], [StoreName], [SalesGoal], [EFTGoal]) VALUES (@CurrentMonth, @StoreNum, @StoreName, @SalesGoal, @EFTGoal)"

TIA,
Andrew
0
Ahelbling
Asked:
Ahelbling
  • 8
  • 5
1 Solution
 
AhelblingAuthor Commented:
I have tried this and it doesnt work. I get cannot insert NULL into StoreNum.

InsertCommand="INSERT INTO [NumbersGoals] ([CurrentMonth], [StoreNum], [StoreName], [SalesGoal], [EFTGoal]) VALUES (@CurrentMonth, Left(@StoreName,2), @StoreName, @SalesGoal, @EFTGoal)"
0
 
AhelblingAuthor Commented:
i have also tried this but get invalid syntax near "="

InsertCommand="INSERT INTO [NumbersGoals] ([CurrentMonth], [StoreNum], [StoreName], [SalesGoal], [EFTGoal]) VALUES (@CurrentMonth, @StoreNum = Left(@StoreName,2), @StoreName, @SalesGoal, @EFTGoal)"
0
 
RainUKCommented:
It sounds like the data that you send in the parameter @StoreName does not contain a name. Have you checked all your data to check that it is correct and contains a store name?

If data is correct - how are you passing the variables in? With command.Parameters.AddWithValue? I am guessing that because you are passing in StoreName after StoreNum in the list of parameters, when you execute the command, StoreName does not contain anything.

Why not do the LEFT function on the VB.NET side rather than in the insert statment?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
RainUKCommented:
OR switch the variables around

InsertCommand="INSERT INTO [NumbersGoals] ([CurrentMonth], [StoreName], [StoreNum], [SalesGoal], [EFTGoal]) VALUES (@CurrentMonth, @StoreName, Left(@StoreName,2), @SalesGoal, @EFTGoal)"
0
 
AhelblingAuthor Commented:
New to .Net and not sure on the variable passing you speak of......in classic ASP i would use request.Querystring

CODE:


<%@ Page Language="VB" AutoEventWireup="false" CodeFile="AddGoal1.aspx.vb" Inherits="AddGoal1" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:CorpWebConnectionString %>"
            DeleteCommand="DELETE FROM [NumbersGoals] WHERE [CurrentMonth] = @CurrentMonth AND [StoreNum] = @StoreNum"
            InsertCommand="INSERT INTO [NumbersGoals] ([CurrentMonth], [StoreNum], [StoreName], [SalesGoal], [EFTGoal]) VALUES (@CurrentMonth, @StoreNum = Left(@StoreName,2), @StoreName, @SalesGoal, @EFTGoal)"
            SelectCommand="SELECT * FROM [NumbersGoals]" UpdateCommand="UPDATE [NumbersGoals] SET [StoreName] = @StoreName, [SalesGoal] = @SalesGoal, [EFTGoal] = @EFTGoal WHERE [CurrentMonth] = @CurrentMonth AND [StoreNum] = @StoreNum">
            <DeleteParameters>
                <asp:Parameter Name="CurrentMonth" Type="String" />
                <asp:Parameter Name="StoreNum" Type="String" />
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="StoreName" Type="String" />
                <asp:Parameter Name="SalesGoal" Type="Decimal" />
                <asp:Parameter Name="EFTGoal" Type="Decimal" />
                <asp:Parameter Name="CurrentMonth" Type="String" />
                <asp:Parameter Name="StoreNum" Type="String" />
            </UpdateParameters>
            <InsertParameters>
                <asp:Parameter Name="CurrentMonth" Type="String" />
                <asp:Parameter Name="StoreNum" Type="String" />
                <asp:Parameter Name="StoreName" Type="String" />
                <asp:Parameter Name="SalesGoal" Type="Decimal" />
                <asp:Parameter Name="EFTGoal" Type="Decimal" />
            </InsertParameters>
        </asp:SqlDataSource>
        <br />
        <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:CorpWebConnectionString %>"
            DeleteCommand="DELETE FROM [CorpLocations2] WHERE [Location] = @Location" InsertCommand="INSERT INTO [CorpLocations2] ([Location]) VALUES (@Location)"
            SelectCommand="SELECT [Location] FROM [CorpLocations2]">
            <DeleteParameters>
                <asp:Parameter Name="Location" Type="String" />
            </DeleteParameters>
            <InsertParameters>
                <asp:Parameter Name="Location" Type="String" />
            </InsertParameters>
        </asp:SqlDataSource>
   
    </div>
        <asp:FormView ID="FormView1" runat="server" CellPadding="4" DataKeyNames="CurrentMonth,StoreNum"
            DataSourceID="SqlDataSource1" DefaultMode="Insert" HorizontalAlign="Center" ForeColor="#333333">
            <FooterStyle BackColor="#5D7B9D" ForeColor="White" Font-Bold="True" />
            <EditRowStyle BackColor="#999999" />
            <EditItemTemplate>
                CurrentMonth:
                <asp:Label ID="CurrentMonthLabel1" runat="server" Text='<%# Eval("CurrentMonth") %>'>
                </asp:Label><br />
                StoreNum:
                <asp:Label ID="StoreNumLabel1" runat="server" Text='<%# Eval("StoreNum") %>'></asp:Label><br />
                StoreName:
                <asp:TextBox ID="StoreNameTextBox" runat="server" Text='<%# Bind("StoreName") %>'>
                </asp:TextBox><br />
                SalesGoal:
                <asp:TextBox ID="SalesGoalTextBox" runat="server" Text='<%# Bind("SalesGoal") %>'>
                </asp:TextBox><br />
                EFTGoal:
                <asp:TextBox ID="EFTGoalTextBox" runat="server" Text='<%# Bind("EFTGoal") %>'>
                </asp:TextBox><br />
                <asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True" CommandName="Update"
                    Text="Update">
                </asp:LinkButton>
                <asp:LinkButton ID="UpdateCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"
                    Text="Cancel">
                </asp:LinkButton>
            </EditItemTemplate>
            <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
            <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
            <InsertItemTemplate>
                CurrentMonth:
                <asp:DropDownList ID="DropDownList2" runat="server" style="font-family: Tahoma" SelectedValue='<%# Bind("CurrentMonth") %>'>
                    <asp:ListItem Selected="True" Value="01">January</asp:ListItem>
                    <asp:ListItem Value="02">February</asp:ListItem>
                    <asp:ListItem Value="03">March</asp:ListItem>
                    <asp:ListItem Value="04">April</asp:ListItem>
                    <asp:ListItem Value="05">May</asp:ListItem>
                    <asp:ListItem Value="06">June</asp:ListItem>
                    <asp:ListItem Value="06">July</asp:ListItem>
                    <asp:ListItem Value="08">August</asp:ListItem>
                    <asp:ListItem Value="09">September</asp:ListItem>
                    <asp:ListItem Value="10">October</asp:ListItem>
                    <asp:ListItem Value="11">November</asp:ListItem>
                    <asp:ListItem Value="12">December</asp:ListItem>
                </asp:DropDownList><br />
                StoreName:
                <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2"
                    DataTextField="Location" DataValueField="Location">
                </asp:DropDownList><br />
                SalesGoal:
                <asp:TextBox ID="SalesGoalTextBox" runat="server" Text='<%# Bind("SalesGoal") %>' Width="50px"></asp:TextBox><br />
                EFTGoal:
                <asp:TextBox ID="EFTGoalTextBox" runat="server" Text='<%# Bind("EFTGoal") %>' Width="50px"></asp:TextBox><br />
                <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert"
                    Text="Insert"></asp:LinkButton>&nbsp;
                <asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"
                    Text="Cancel"></asp:LinkButton>
            </InsertItemTemplate>
            <ItemTemplate>
                CurrentMonth:
                <asp:DropDownList ID="DropDownList2" runat="server">
                    <asp:ListItem Value="&quot;01&quot;">January</asp:ListItem>
                    <asp:ListItem Value="&quot;02&quot;">February</asp:ListItem>
                    <asp:ListItem Value="03">March</asp:ListItem>
                    <asp:ListItem Value="04">April</asp:ListItem>
                    <asp:ListItem Value="05">May</asp:ListItem>
                    <asp:ListItem Value="06">June</asp:ListItem>
                    <asp:ListItem Value="06">July</asp:ListItem>
                    <asp:ListItem Value="08">August</asp:ListItem>
                    <asp:ListItem Value="09">September</asp:ListItem>
                    <asp:ListItem Value="10">October</asp:ListItem>
                    <asp:ListItem Value="11">November</asp:ListItem>
                    <asp:ListItem Value="12">December</asp:ListItem>
                </asp:DropDownList><br />
                StoreNum:
                <asp:Label ID="StoreNumLabel" runat="server" Text='<%# Eval("StoreNum") %>'></asp:Label><br />
                StoreName:
                <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2"
                    DataTextField="Location" DataValueField="Location">
                </asp:DropDownList><br />
                SalesGoal:
                <asp:Label ID="SalesGoalLabel" runat="server" Text='<%# Bind("SalesGoal") %>'></asp:Label><br />
                EFTGoal:
                <asp:Label ID="EFTGoalLabel" runat="server" Text='<%# Bind("EFTGoal") %>'></asp:Label><br />
                <asp:LinkButton ID="EditButton" runat="server" CausesValidation="False" CommandName="Edit"
                    Text="Edit"></asp:LinkButton>
                <asp:LinkButton ID="DeleteButton" runat="server" CausesValidation="False" CommandName="Delete"
                    Text="Delete"></asp:LinkButton>
                <asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" CommandName="New"
                    Text="New"></asp:LinkButton>
            </ItemTemplate>
            <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
        </asp:FormView>
        &nbsp;&nbsp;&nbsp;
    </form>
</body>
</html>
0
 
RainUKCommented:
Having a guess but:

InsertCommand="INSERT INTO [NumbersGoals] ([CurrentMonth], [StoreName], [StoreNum], [SalesGoal], [EFTGoal]) VALUES (@CurrentMonth, @StoreName, Left(@StoreName,2), @SalesGoal, @EFTGoal)"


And in the ASP change the INSERT parameters order to this:

<InsertParameters>
                <asp:Parameter Name="CurrentMonth" Type="String" />
                <asp:Parameter Name="StoreName" Type="String" />
                <asp:Parameter Name="StoreNum" Type="String" />
                <asp:Parameter Name="SalesGoal" Type="Decimal" />
                <asp:Parameter Name="EFTGoal" Type="Decimal" />
            </InsertParameters>


0
 
AhelblingAuthor Commented:
Cannot insert the value NULL into column 'StoreNum', table 'CorpWeb.dbo.NumbersGoals'; column does not allow nulls. INSERT fails.
The statement has been terminated.
0
 
AhelblingAuthor Commented:
I will try that now
0
 
AhelblingAuthor Commented:
same deal....

Cannot insert the value NULL into column 'StoreNum', table 'CorpWeb.dbo.NumbersGoals'; column does not allow nulls. INSERT fails.
The statement has been terminated.
0
 
AhelblingAuthor Commented:
how can i test what is being passed to the variables?
0
 
RainUKCommented:
I can only think that you are passing an empty string.

To make sure, hardcode a value into storename if that works then you are passing an empty string to the variable.

Add a breakpoint to click event of the 'New' button.

Sorry but ASP is not my forte
0
 
AhelblingAuthor Commented:
it looks like you are correct that StoreName is null as well. I allowed NULL for StoreNum in the DB and now get:

Cannot insert the value NULL into column 'StoreName', table 'CorpWeb.dbo.NumbersGoals'; column does not allow nulls. INSERT fails.
The statement has been terminated.
0
 
RainUKCommented:
So you are not passing the StoreName (as its empty string). So you need to check why its not coming from the textbox, then that should pretty much be it.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now