asked on

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

Andrew

ASKER

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)"

Andrew

ASKER

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)"

RainUK

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?

RainUK

OR switch the variables around

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

Andrew

ASKER

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> 
<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=""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 />
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>
   
</form>
</body>
</html>

RainUK

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>

Andrew

ASKER

Cannot insert the value NULL into column 'StoreNum', table 'CorpWeb.dbo.NumbersGoals'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Andrew

ASKER

I will try that now

Andrew

ASKER

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.

Andrew

ASKER

how can i test what is being passed to the variables?

ASKER CERTIFIED SOLUTION

RainUK

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Andrew

ASKER

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.

RainUK

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.