Syntax Error On Declare.

May 21, 2008


I am trying to create a view or a table with in SQL system and it keeps coming up with incorrect syntax near the word declare.
It runs fine as long as I do not try and create a table or a view, which unfortunatly I need to do as I have to export the data into Excel for the finance guys.


The syntax is below....


Create View dbo.Z_Cashflow_Forc_Paymsdue
As
Declare @Firstday Int
Declare @Nextday Int
Set @Firstday = '10'
Set @Nextday = '25'
Select [Supplier Code], [Supplier Name], Stype, [Order No], [Line No], [Due Date], [Mat Part], [Qty Ordered], [Purch Price], [Value], Dateadd(dd,Screditdays,[Due Date]) as [Paym Date], Letype, Lecurcode,
Case
When Datepart(Dd, Dateadd(dd,Screditdays,[Due Date])) < '10' then Dateadd(dd, - Day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday, Dateadd(dd,Screditdays,[Due Date]))
When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '10' and Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) < '25' then Dateadd(dd, - Day(Dateadd(dd,Screditdays,[Due Date])) + @Nextday, Dateadd(dd,Screditdays,[Due Date]))
When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '25' and Datepart(mm, Dateadd(dd,Screditdays,[Due Date])) < '12' then Dateadd(dd, - day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday,(dateadd(mm,1,Dateadd(dd,Screditdays,[Due Date]))))
When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '25' and Datepart(mm, Dateadd(dd,Screditdays,[Due Date])) = '12' then Dateadd(dd, - day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday,(Dateadd(mm,1,DateAdd(yyyy,1,Dateadd(dd,Screditdays,[Due Date])))))
End AS [Paymdate],
Case
When Datepart(Dd, Dateadd(dd,Screditdays,[Due Date])) < '10' then Datepart(wk,Dateadd(dd, - Day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday, Dateadd(dd,Screditdays,[Due Date])))
When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '10' and Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) < '25' then Datepart(wk,Dateadd(dd, - Day(Dateadd(dd,Screditdays,[Due Date])) + @Nextday, Dateadd(dd,Screditdays,[Due Date])))
When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '25' and Datepart(mm, Dateadd(dd,Screditdays,[Due Date])) < '12' then Datepart(wk,Dateadd(dd, - day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday,(dateadd(mm,1,Dateadd(dd,Screditdays,[Due Date])))))
When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '25' and Datepart(mm, Dateadd(dd,Screditdays,[Due Date])) = '12' then Datepart(wk,Dateadd(dd, - day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday,(Dateadd(mm,1,DateAdd(yyyy,1,Dateadd(dd,Screditdays,[Due Date]))))))
End AS [PaymWk],
Case
When Datepart(Dd, Dateadd(dd,Screditdays,[Due Date])) < '10' then Datepart(yyyy,Dateadd(dd, - Day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday, Dateadd(dd,Screditdays,[Due Date])))
When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '10' and Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) < '25' then Datepart(yyyy,Dateadd(dd, - Day(Dateadd(dd,Screditdays,[Due Date])) + @Nextday, Dateadd(dd,Screditdays,[Due Date])))
When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '25' and Datepart(mm, Dateadd(dd,Screditdays,[Due Date])) < '12' then Datepart(yyyy,Dateadd(dd, - day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday,(dateadd(mm,1,Dateadd(dd,Screditdays,[Due Date])))))
When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '25' and Datepart(mm, Dateadd(dd,Screditdays,[Due Date])) = '12' then Datepart(yyyy,Dateadd(dd, - day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday,(Dateadd(mm,1,DateAdd(yyyy,1,Dateadd(dd,Screditdays,[Due Date]))))))
End AS [PaymYr]
from Z_Cashflow_Purchorders, Wsuppliers
Where [Supplier Code] = Supp

Union

Select [Supplier Code], [Supplier Name], Stype, [Order No], [Line No], [Due Date], [Mat Part], [Qty Ordered], [Purch Price], [Value], Dateadd(dd,Screditdays,[Due Date]) as [Paym Date], Letype, Lecurcode,
Case
When Datepart(Dd, Dateadd(dd,Screditdays,[Due Date])) < '10' then Dateadd(dd, - Day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday, Dateadd(dd,Screditdays,[Due Date]))
When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '10' and Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) < '25' then Dateadd(dd, - Day(Dateadd(dd,Screditdays,[Due Date])) + @Nextday, Dateadd(dd,Screditdays,[Due Date]))
When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '25' and Datepart(mm, Dateadd(dd,Screditdays,[Due Date])) < '12' then Dateadd(dd, - day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday,(dateadd(mm,1,Dateadd(dd,Screditdays,[Due Date]))))
When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '25' and Datepart(mm, Dateadd(dd,Screditdays,[Due Date])) = '12' then Dateadd(dd, - day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday,(Dateadd(mm,1,DateAdd(yyyy,1,Dateadd(dd,Screditdays,[Due Date])))))
End AS [Paymdate],
Case
When Datepart(Dd, Dateadd(dd,Screditdays,[Due Date])) < '10' then Datepart(wk,Dateadd(dd, - Day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday, Dateadd(dd,Screditdays,[Due Date])))
When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '10' and Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) < '25' then Datepart(wk,Dateadd(dd, - Day(Dateadd(dd,Screditdays,[Due Date])) + @Nextday, Dateadd(dd,Screditdays,[Due Date])))
When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '25' and Datepart(mm, Dateadd(dd,Screditdays,[Due Date])) < '12' then Datepart(wk,Dateadd(dd, - day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday,(dateadd(mm,1,Dateadd(dd,Screditdays,[Due Date])))))
When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '25' and Datepart(mm, Dateadd(dd,Screditdays,[Due Date])) = '12' then Datepart(wk,Dateadd(dd, - day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday,(Dateadd(mm,1,DateAdd(yyyy,1,Dateadd(dd,Screditdays,[Due Date]))))))
End AS [PaymWk],
Case
When Datepart(Dd, Dateadd(dd,Screditdays,[Due Date])) < '10' then Datepart(yyyy,Dateadd(dd, - Day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday, Dateadd(dd,Screditdays,[Due Date])))
When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '10' and Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) < '25' then Datepart(yyyy,Dateadd(dd, - Day(Dateadd(dd,Screditdays,[Due Date])) + @Nextday, Dateadd(dd,Screditdays,[Due Date])))
When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '25' and Datepart(mm, Dateadd(dd,Screditdays,[Due Date])) < '12' then Datepart(yyyy,Dateadd(dd, - day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday,(dateadd(mm,1,Dateadd(dd,Screditdays,[Due Date])))))
When Datepart(dd, Dateadd(dd,Screditdays,[Due Date])) >= '25' and Datepart(mm, Dateadd(dd,Screditdays,[Due Date])) = '12' then Datepart(yyyy,Dateadd(dd, - day(Dateadd(dd,Screditdays,[Due Date])) + @Firstday,(Dateadd(mm,1,DateAdd(yyyy,1,Dateadd(dd,Screditdays,[Due Date]))))))
End AS [PaymYr]
from Z_Cashflow_Schedorders, Wsuppliers
Where [Supplier Code] = Supp

View 4 Replies


ADVERTISEMENT

'declare' Syntax In A UDF

Jul 20, 2005

Hi, I'm trying to create a function that returns a table, however I wantto use a local variable in there and enterprise manager ain't liking it!The error I get is number 156 'incorrect syntax near the keyword'declare'.. hopefully this is just a simple thing where I've put it inthe wrong place.The code follows:CREATE FUNCTION AFGroupedTotals (@campaign nvarchar(30),@datefromsmalldatetime, @dateto smalldatetime, @prospect nvarchar(30), @typenvarchar(20))RETURNS TABLE ASRETURNdeclare @set nvarchar(150)select "Total Pledged" as info, sum(total) as totFROM AFresponseTotals (@campaign, @datefrom, @dateto,@prospect)Cheers for any help,Chris

View 2 Replies View Related

Incorrect Syntax Near The Keyword 'Declare'.

Jul 23, 2005

Dear Group,I am trying to create a view and keep getting the Incorrect syntax near thekeyword 'Declare'" error.Here is the code I am writing.Create view fixed_airs (sid, fad_a2, fad_a3) asDeclare @sid int,@fad_a2 int,@fad_a3 intselect @sid=cast(substring(subject_id,1,8)as int) ,@fad_a2 =cast (substring(fad_2_4,1,1) as int),@fad_a3=cast(substring(fad_2_4,2,1) as int)from parentpacket.Thanks for the help in advance.Jeff Magouirk

View 1 Replies View Related

Incorrect Syntax Near 'nvarchar'. Must Declare The Scalar Variable @CODE.

Jan 6, 2008

I have this issue and I can not figure out the problem. I have 4 other forms from the same database using practly the same code, slight variations based on datavalidation requirements. IIS6 SQL Express 2005.
 
I have tried to defint eh colum for CODE as a bound filed and as a templated field. I get the same error.ASPX Page <%@ Page Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true" CodeFile="MaintainBSP.aspx.cs" Inherits="MaintainBSP" Title="Maintain BSP Codes" %>
<%@ MasterType VirtualPath="~/Site.master" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"><br />
<table class="mainTable" cellspacing="0" cellpadding="3" align="center">
<tr><td class="mainTableTitle">BSP Codes</td></tr>
<tr><td>
<table align="center">
<tr>
<td><asp:GridView ID="GridView1" runat="server"
AutoGenerateColumns="False"BorderColor="Silver"
BorderStyle="Solid" BorderWidth="1px" HorizontalAlign="Center"
CellPadding="3"DataKeyNames="CODE" DataMember="DefaultView"
DataSourceID="SqlDataSource1"
OnRowEditing="GridView1_OnRowEditing"
OnRowCancelingEdit="GridView1_EndEdit"
OnRowUpdated="GridView1_EndEdit">
<Columns>
<asp:CommandField ShowEditButton="True" EditText="Edit" CancelText="Cancel" UpdateText="Update" HeaderStyle-CssClass="rptTblTitle" >
<HeaderStyle CssClass="rptTblTitle"></HeaderStyle>
</asp:CommandField>
<asp:BoundField DataField="CODE" HeaderText="Code" ReadOnly="true" HeaderStyle-CssClass="rptTblTitle" />
<asp:TemplateField HeaderText="Bottle Size" SortExpression="Btl Sz">
<EditItemTemplate>
<asp:TextBox ID="TextBox1" MaxLength="10" Columns="10" runat="server" Text='<%# Bind("[BOTTLE$SIZE]") %>'></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ErrorMessage="Bottle Size is a required field." Text="*" ControlToValidate="TextBox1"></asp:RequiredFieldValidator><asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server" ErrorMessage="Bottle size must be a number followed by 'ML' or 'L'" Text="*" ControlToValidate="TextBox1"
ValidationExpression="[0-9.]+(ML|L)"></asp:RegularExpressionValidator>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("[BOTTLE$SIZE]") %>'></asp:Label>
</ItemTemplate>
<HeaderStyle CssClass="rptTblTitle" />
</asp:TemplateField>
<asp:TemplateField HeaderText="Labeled" SortExpression="Labeled">
<EditItemTemplate>
<asp:TextBox ID="TextBox2" MaxLength="1" Columns="2" runat="server" Text='<%# Bind("LABELED") %>'></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ErrorMessage="Labeled is a required field" Text="*" ControlToValidate="TextBox2"></asp:RequiredFieldValidator>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Bind("LABELED") %>'></asp:Label>
</ItemTemplate>
<HeaderStyle CssClass="rptTblTitle" />
</asp:TemplateField>
<asp:TemplateField HeaderText="Bottles Per Case" SortExpression="Btls Per Case">
<EditItemTemplate>
<asp:TextBox ID="TextBox3" Columns="4" runat="server" Text='<%# Bind("[BOTTLES$PER$CASE]") %>'></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ErrorMessage="Bottles per case must be a whole number." Text="*" ControlToValidate="TextBox3"></asp:RequiredFieldValidator>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Bind("[BOTTLES$PER$CASE]") %>'></asp:Label>
</ItemTemplate>
<HeaderStyle CssClass="rptTblTitle" />
</asp:TemplateField>
<asp:TemplateField HeaderText="Liters Per Case" SortExpression="Ltrs Per Case">
<EditItemTemplate>
<asp:TextBox ID="TextBox4" MaxLength="8" Columns="8" runat="server" Text='<%# Bind("[LITERS$PER$CASE]") %>'></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server" ErrorMessage="Liters per case must be a number." ControlToValidate="TextBox4" Text="*"></asp:RequiredFieldValidator>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label4" runat="server" Text='<%# Bind("[LITERS$PER$CASE]") %>'></asp:Label>
</ItemTemplate>
<HeaderStyle CssClass="rptTblTitle" />
</asp:TemplateField>
<asp:CommandField ShowDeleteButton="True" DeleteText="Delete" HeaderStyle-CssClass="rptTblTitle" >
<HeaderStyle CssClass="rptTblTitle"></HeaderStyle>
</asp:CommandField>
</Columns>
 
</asp:GridView>
 
<table id="tblAddBSP" runat="server" width="100%">
<tr><td colspan="2" align="center"><asp:Label ID="lblAddMessage" runat="server" Text="" style="color:Red;font-weight:bold;"/></td></tr>
<tr>
<td style="font-weight:bold;">Code</td>
<td><asp:TextBox ID="txtAddCode" runat="server" MaxLength="1" Columns="2"></asp:TextBox><asp:RequiredFieldValidator ID="rfv_txtAddCode" runat="server" ErrorMessage="Please Supply a BSP Code." Text="*"
ControlToValidate="txtAddCode" SetFocusOnError="True"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td style="font-weight:bold;">Bottle Size</td>
<td><asp:TextBox ID="txtAddSize" runat="server" MaxLength="10" Columns="10"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ErrorMessage="Bottle Size is a required field." Text="*" ControlToValidate="txtAddSize"></asp:RequiredFieldValidator><asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server" ErrorMessage="Bottle size must be a number followed by 'ML' or 'L'" Text="*" ControlToValidate="txtAddSize"
ValidationExpression="[0-9.]+(ML|L)"></asp:RegularExpressionValidator>
</td>
</tr>
<tr>
<td style="font-weight:bold;">Labeled</td>
<td><asp:TextBox ID="txtAddLabeled" runat="server" MaxLength="1" Columns="2"></asp:TextBox></td>
</tr>
<tr>
<td style="font-weight:bold;">Bottles Per Case</td>
<td><asp:TextBox ID="txtAddBottlesPerCase" runat="server" MaxLength="4" Columns="4"></asp:TextBox></td>
</tr>
<tr>
<td style="font-weight:bold;">Liters Per Case</td>
<td><asp:TextBox ID="txtAddLitersPerCase" runat="server" MaxLength="8" Columns="8"></asp:TextBox></td>
</tr><tr><td colspan="2" align="right"><asp:Button ID="btnAddNew" runat="server"
Text="Add BSP" onclick="btnAddNew_Click" /></td></tr>
</table>
</td>
</tr>
</table>
</td></tr>
</table><asp:ValidationSummary ID="ValidationSummary1" runat="server"
ShowMessageBox="True" ShowSummary="False" /><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:SqlConnectionString %>"
ProviderName="<%$ ConnectionStrings:SqlConnectionString.ProviderName %>"DeleteCommand="DELETE FROM BSP WHERE CODE = @CODE" InsertCommand="INSERT INTO BSP (CODE, BOTTLE$SIZE, LABELED, BOTTLES$PER$CASE, LITERS$PER$CASE) VALUES (@CODE, @BOTTLE$SIZE, @LABELED, @BOTTLES$PER$CASE, @LITERS$PER$CASE)"
SelectCommand="SELECT CODE, BOTTLE$SIZE, LABELED, BOTTLES$PER$CASE, LITERS$PER$CASE FROM BSP order by CODE"
UpdateCommand="UPDATE BSP SET BOTTLE$SIZE = @BOTTLE$SIZE, LABELED = @LABELED, BOTTLES$PER$CASE = @BOTTLES$PER$CASE, LITERS$PER$CASE = @LITERS$PER$CASE WHERE [CODE] = @CODE">
<UpdateParameters>
<asp:Parameter Name="BOTTLE$SIZE" type="String" />
<asp:Parameter Name="LABELED" type="Char" />
<asp:Parameter Name="BOTTLES$PER$CASE" type="Int32" />
<asp:Parameter Name="LITERS$PER$CASE" type="Decimal" />
<asp:Parameter Name="CODE" type="Char" />
</UpdateParameters>
<InsertParameters>
<asp:ControlParameter ControlID="txtAddSize" Name="BOTTLE$SIZE" type="String" />
<asp:ControlParameter ControlID="txtAddLabeled" Name="LABELED" type="Char" />
<asp:ControlParameter ControlID="txtAddBottlesPerCase" Name="BOTTLES$PER$CASE" type="Int32" />
<asp:ControlParameter ControlID="txtAddLitersPerCase" Name="LITERS$PER$CASE" type="Decimal" />
<asp:ControlParameter ControlID="txtAddCode" Name="CODE" type="Char" />
</InsertParameters>
</asp:SqlDataSource>
 
</asp:Content>
 
CODE BEHIND
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using reports;
using System.Data.SqlClient;
public partial class MaintainBSP : System.Web.UI.Page
{protected void Page_Load(object sender, EventArgs e)
{Master.ActiveTab = Helpers.Tabs.Admin;Security.CheckPageAccess(Security.AccessTypes.Administrator);
}protected void GridView1_OnRowEditing(Object sender, GridViewEditEventArgs e)
{tblAddBSP.Visible = false;
}protected void GridView1_EndEdit(Object sender, EventArgs e)
{tblAddBSP.Visible = true;
}protected void btnAddNew_Click(object sender, EventArgs e)
{
try
{
SqlDataSource1.Insert();lblAddMessage.Text = "Add BSP '" + txtAddCode.Text + "' successful.";
txtAddCode.Text = String.Empty;txtAddSize.Text = String.Empty;
txtAddLabeled.Text = String.Empty;txtAddBottlesPerCase.Text = String.Empty;txtAddLitersPerCase.Text = String.Empty;
}catch (SqlException ex)
{if (ex.Number == 2627)
{
lblAddMessage.Text = "The code '" + txtAddCode.Text + "' is already in the database.<br />Select another code for this BSP.";txtAddCode.Text = String.Empty;
}
elselblAddMessage.Text = ex.Number + " - " + ex.ErrorCode.ToString() + " - " + ex.Message;
}
catch
{lblAddMessage.Text = "There was an issue inserting the BSP Code '" + txtAddCode.Text + "'. Please check the values and try again.";
}
}
}
 

View 5 Replies View Related

Error 137: Must Declare Variable ...

Dec 10, 2007

Hello,

I have the following SP, which gives out a "Error 137: Must declare variable @tmp_return_tbl" error.

This is the important part of the code:
.
.
.
-- DECLARE TABLE VARIABLE
DECLARE @tmp_return_tbl TABLE (tID int, Text_Title nvarchar(30), Text_Body nvarchar(100))

-- fill out table variable USING A SELECT FROM ANOTHER TABLE VARIABLE
-- NO PROBLEM HERE
INSERT INTO @tmp_return_tbl
SELECT TOP 1 * FROM @tmp_tbl
ORDER BY NEWID()

-- TRYING TO UPDATE A TABLE
UPDATE xTable
SET xTable.fieldY = xTable.fieldY + 1
WHERE xTable.tID = @tmp_return_tbl.tID --THIS PRODUCES THE ERROR
.
.
.

I know I cannot use a table variable in a JOIN without using an Alias, or use it directly in dynamic SQL (different scope) - but is this the problem here? What am I doing wrong?

Your help is much appreciated.

View 3 Replies View Related

Declare Variable Error

Jan 14, 2008

Can anyone tell me why I keep getting this error? I am declaring the variable, but it's not recognizing it? What am I missing?

------error---------------
Server: Msg 137, Level 15, State 2, Procedure sp_CopyData, Line 85
Must declare the variable '@DatabaseFrom'.

-----sp----

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


create Procedure dbo.sp_CopyData
(@ClientAbbrev nchar(4) )
AS
DECLARE @DatabaseFrom varchar(100)

Set @DatabaseFrom = @ClientAbbrev + '.dbo.tsn_ClaimStatus'

--------------------------------------------------------------
delete from sherrisplayground.dbo.tsn_ClaimStatus
where csclientcode = @ClientAbbrev


---Insert Data from Original table into copied table---------
Insert into [AO3AO3].sherrisplayground.dbo.tsn_ClaimStatus (
CsClientCode,
ClaimStatusID,
Pat,
Claim,
[ID],
Code,
[Date],
ActionID,
Comment2,
Comment3,
Comment4,
[Followup Date],
Checkamt,
UserName)
select
@ClientAbbrev,
ClaimStatusID,
Pat,
Claim,
[ID],
Code,
[Date],
ActionID,
Comment2,
Comment3,
Comment4,
[Followup Date],
Checkamt,
UserName
from @DatabaseFrom


return


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

View 2 Replies View Related

Error Message: Must Declare Variable @ID

May 3, 2007

I’m having trouble with a datalist. The default view is the Item Template which has an Edit button. When I click the Edit button, I run the following code (for the EditCommand of the Datalist):
 
DataList1.EditItemIndex = e.Item.ItemIndex
DataBind()
 
It errors out with the message “Must declare variable @ID�.
 
I’ve used this process on other pages without problem.
 
The primary key for the recordsource that populates this datalist is a field named “AutoID�. There is another field named ID that ties these records to a master table. The list of rows returned in the datalist is based off the ID field matching a value in a dropdown list on the page (outside of the datalist). So my SQLdatasource has a parameter to match the ID field to @ID. For some reason, it's not finding it and I cannot determine why. I haven't had this issue on other pages.
 
Here’s my markup of the SQLDataSource and the Datalist/Edit Template:
 
<asp:SqlDataSource ID="SqlDataSource4" runat="server" ConnectionString="<%$ ConnectionStrings:SMARTConnectionString %>"
    DeleteCommand="DELETE FROM [tblSalesSupport] WHERE [NBID] = @NBID"
    InsertCommand="INSERT INTO [tblSalesSupport] ([ID], [NBNC], [NBEC], [Description], [Estimate], [CompanyID], [CompanyName], [ProjectNumber]) VALUES (@ID, @NBNC, @NBEC, @Description, @Estimate, @CompanyID, @CompanyName, @ProjectNumber)"
    SelectCommand="SELECT * FROM [tblSalesSupport] WHERE ([ID] = @ID)"
    UpdateCommand="UPDATE [tblSalesSupport] SET [ID] = @ID, [NBNC] = @NBNC, [NBEC] = @NBEC, [Description] = @Description, [Estimate] = @Estimate, [CompanyID] = @CompanyID, [CompanyName] = @CompanyName, [ProjectNumber] = @ProjectNumber WHERE [NBID] = @NBID">
        <DeleteParameters>
            <asp:Parameter Name="NBID" Type="Int32" />
        </DeleteParameters>
        <UpdateParameters>
            <asp:Parameter Name="ID" Type="Int32" />
            <asp:Parameter Name="NBNC" Type="Boolean" />
            <asp:Parameter Name="NBEC" Type="Boolean" />
            <asp:Parameter Name="Description" Type="String" />
            <asp:Parameter Name="Estimate" Type="Decimal" />
            <asp:Parameter Name="CompanyID" Type="Int32" />
            <asp:Parameter Name="CompanyName" Type="String" />
            <asp:Parameter Name="ProjectNumber" Type="String" />
            <asp:Parameter Name="NBID" Type="Int32" />
        </UpdateParameters>
        <SelectParameters>
           <asp:ControlParameter ControlID="ddlFind" Name="ID" PropertyName="SelectedValue" Type="Int32" />
        </SelectParameters>
        <InsertParameters>
            <asp:Parameter Name="ID" Type="Int32" />
            <asp:Parameter Name="NBNC" Type="Boolean" />
            <asp:Parameter Name="NBEC" Type="Boolean" />
            <asp:Parameter Name="Description" Type="String" />
            <asp:Parameter Name="Estimate" Type="Decimal" />
            <asp:Parameter Name="CompanyID" Type="Int32" />
            <asp:Parameter Name="CompanyName" Type="String" />
            <asp:Parameter Name="ProjectNumber" Type="String" />
        </InsertParameters>
    </asp:SqlDataSource>
   
       <asp:DataList CssClass="MainFormDisplay" ID="DataList1" runat="server" DataKeyField="NBID" DataSourceID="SqlDataSource1" width="100%">
        <HeaderTemplate>….</HeaderTemplate>
        <ItemTemplate>….</ItemTemplate>
        <EditItemTemplate>
            <table border="0"  style="width: 100%">
                <tr class="MainFormDisplay"  valign="top">
                    <td colspan="8">
                        <asp:TextBox ID="txtNBID" runat="server" Text='<%# Eval("NBID") %>' Visible="true"></asp:TextBox>
                        <asp:TextBox ID="txtID" runat="server" Text='<%# Bind("ID") %>' Visible="True"></asp:TextBox></td>
                </tr>
                <tr class="MainFormDisplay">
                    <td valign="top" style="width: 100px"><asp:Checkbox ID="chkNBNC" runat="server" Checked='<%# Bind("NBNC") %>' /></td>
                    <td style="width: 100"><asp:CheckBox ID="chkNBEC" runat="server" Checked='<%# Bind("NBEC") %>' Width="100px" /></td>
                    <td style="width: 100px"><asp:TextBox ID="txtCompanyName" runat="server" Text='<%# Bind("CompanyName")%>' Width="100px"></asp:TextBox></td>
                    <td style="width: 100px"><asp:TextBox ID="txtProjectNumber" runat="server" Text='<%# Bind("ProjectNumber") %>' Width="100px"></asp:TextBox></td>
                    <td style="width: 100px"><asp:TextBox ID="txtDescription" runat="server" Text='<%# Bind("Description") %>' Width="100px"></asp:TextBox></td>
                    <td style="width: 100px"><asp:TextBox ID="txtEstimate" runat="server" Text='<%# Bind("Estimate","{0:N2}") %>' Width="100px"></asp:TextBox></td>
                    <td style="width: 55px"><asp:CheckBox ID="ckDeleteFlag" runat="server" /></td>
                    <td style="width: 100px"><asp:Button ID="ItemSaveButton" runat="server" CommandName="Update" Text="Save" />
                    <asp:Button ID="ItemCancelButton" runat="server" CommandName="Cancel" Text="Cancel" /></td>
                </tr>               
            </table>
            &nbsp;
        </EditItemTemplate>
    </asp:DataList><br />

View 2 Replies View Related

Must Declare The Scalar Variable Error

Jan 19, 2008

Hi All,

I'm totaly new to administrating databases.

All I want to do is run the sql server script located at http://www.data-miners.com/sql_companion.htm#downloads.

This creates some tables and uploads a series of text files into them.

When I run the script through SQL Server Express 2005 I get the error Must declare the scalar variable "@DATADIR". I suspect it's something with me putting in the wrong path.

The text files that the script needs to load into the table are located on the K drive, and I have changed the path in

declare @DATADIR varchar(128)
set @DATADIR='C:gordonookdatafinal extfiles'


to


declare @DATADIR varchar(128)
set @DATADIR='k: extfiles'

I suspect this is the wrong syntax that's why it's not working but I might be totally wrong.

The text file and the server are both saved on the k drive.

Regards,

Seaweed

View 3 Replies View Related

Must Declare The Scalar Variable @ Error

Apr 28, 2007

I am trying to run a query in the data window and get the following error. How do I resolve?



query:



select distinct [Client ID] as ClientID
FROM ITSTAFF.Incident
WHERE [Group Name] = 'ITSTAFF' and [Client ID] is not null and [Company ID] = @[Company ID]



error:



TITLE: Microsoft Report Designer
------------------------------

An error occurred while executing the query.
Must declare the scalar variable "@".

------------------------------

View 1 Replies View Related

Error: 'Must Declare The Scalar Variable @StartDate.'

Jan 7, 2007

Hi I’m getting an error that says “Must declare the scalar variable "@StartDate".â€? for the following line of code :
 
dt = ((DataView)(EventDataSource1.Select(dssa))).ToTable() 
 
Can anyone help me out? Here is my entire code.
 
 
 
Dim EventDataSource1 As New SqlDataSource()EventDataSource1.ConnectionString =
 
ConfigurationManager.ConnectionStrings("ASPNETDBConnectionString").ToStringDim dssa As New DataSourceSelectArguments()Dim EventID As String = ""Dim DataView = ""Dim dt As New Data.DataTableDim conn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ASPNETDBConnectionString").ToString())Dim cmd As New Data.SqlClient.SqlCommand("SELECT EventID FROM Event WHERE ([StartDate] = @StartDate)", conn)EventDataSource1.SelectCommand = ("SELECT EventID FROM Event WHERE ([StartDate] = @StartDate)")conn.Open()dt = ((DataView)(EventDataSource1.Select(dssa))).ToTable()EventID = dt.Rows(0)(0).ToString()EventDataSource1.SelectParameters.Add("@StartDate",StartDate)EventID = cmd.ExecuteScalar()tbEventIDTest.Text = EventID
 

View 2 Replies View Related

Must Declare The Scalar Variable @UserName ERROR

Aug 23, 2007

 I'm attempting to create my first login form using the CreateUserWizard. I've spent this week reading up on how to create and customizing it. I want it to 1) the required user name is an email address (which seems to be working fine) and 2) having extra information inserted into a separate customized table. I now have the form working to the point where it accepts an email address for the username and it then writes that information along with the password to the aspnetdb.mdf...but i can't get the rest of the information to write to my custom table.I am getting the error  "Must declare the scalara variable "@UserName"  here's my .cs code:public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void CreateUserWizard1_CreatedUser(object sender, EventArgs e)
{
TextBox UserNameTextBox =
(TextBox)CreateUserWizardStep1.ContentTemplateContainer.FindControl("UserName");

SqlDataSource DataSource =
(SqlDataSource)CreateUserWizardStep1.ContentTemplateContainer.FindControl("InsertCustomer");

MembershipUser User = Membership.GetUser(UserNameTextBox.Text);

object UserGUID = User.ProviderUserKey;

DataSource.InsertParameters.Add("UserId", UserGUID.ToString());

DataSource.Insert();

}


protected void CreateUserWizard1_CreatingUser(object sender, LoginCancelEventArgs e)
{
CreateUserWizard cuw = (CreateUserWizard)sender;
cuw.Email = cuw.UserName;

}
}     protected void CreateUserWizard1_CreatingUser(object sender, LoginCancelEventArgs e)    {        CreateUserWizard cuw = (CreateUserWizard)sender;        cuw.Email = cuw.UserName;    }}  and the asp<asp:SqlDataSource ID="InsertCustomer" runat="server" ConnectionString="<%$ ConnectionStrings:kalistaConnectionString %>"                        InsertCommand="INSERT INTO [Customer] ([CustID], [CustEmail], [CustFN], [CustLN], [CustAddress], [CustCity], [AreaTaxID], [CustPostal_Zip], [CustCountry], [CustPhone], [CustAltPhone]) VALUES (@UserId, @UserName, @FirstName, @LastName, @Address, @City, @ProvinceState, @PostalZip, @Country, @Phone, @AltPhone)"                        ProviderName="<%$ ConnectionStrings:kalistaConnectionString.ProviderName %>">                        <InsertParameters>                            <asp:ControlParameter Name="CustEmail" Type="String" ControlID="UserName" PropertyName="Text" />                            <asp:ControlParameter Name="CustFN" Type="String" ControlID="FirstName" PropertyName="Text" />                            <asp:ControlParameter Name="CustLN" Type="String" ControlID="LastName" PropertyName="Text" />                            <asp:ControlParameter Name="CustAddress" Type="String" ControlID="Address" PropertyName="Text" />                            <asp:ControlParameter Name="CustCity" Type="String" ControlID="City" PropertyName="Text" />                            <asp:ControlParameter Name="AreaID" Type="String" ControlID="AreaID" PropertyName="SelectedValue" />                            <asp:ControlParameter Name="CustPostal_Zip" Type="String" ControlID="PostalZip" PropertyName="Text" />                            <asp:ControlParameter Name="CustCountry" Type="String" ControlID="Country" PropertyName="SelectedValue" />                            <asp:ControlParameter Name="CustPhone" Type="String" ControlID="Phone" PropertyName="Text" />                            <asp:ControlParameter Name="CustAltPhone" Type="String" ControlID="AltPhone" PropertyName="Text" />                        </InsertParameters>                    </asp:SqlDataSource>  thanks for the help

View 5 Replies View Related

Must Declare Scalar Variable Error... Need Help URGENT!

Jan 28, 2008

 doing insert using this method             Dim insertSQL As String            insertSQL = "Insert into " & myDB & " (student_name, student_passport, student_rcnumber, " & _                            "test_level, test_venue1, test_venue2, test_row, test_column, " & _                            "student_sex, student_age, student_dob,student_country, student_state, " & _                            "guardian_name, guardian_passport, guardian_relation, " & _                            "guardian_address1, guardian_address2, guardian_postcode, " & _                            "guardian_homephone, guardian_mobilephone, guardian_otherphone, " & _                            "payment, remarks, student_att) " & _                            "" & _                            "Values(@student_name, @student_passport, @student_rcnumber, " & _                            "@test_level, @test_venue1, @test_venue2, @test_row, @test_column, " & _                            "@student_sex, @student_age, @student_dob,@student_country, @student_state, " & _                            "@guardian_name, @guardian_passport, @guardian_relation, " & _                            "@guardian_address1, @guardian_address2, @guardian_postcode, " & _                            "@guardian_homephone, @guardian_mobilephone, @guardian_otherphone, " & _                            "@payment, @remarks, @student_att)"            Dim conn As New OleDbConnection(myNorthWind)            Dim cmd As New OleDbCommand(insertSQL, conn)          cmd.Parameters.AddWithValue("@student_name", txtName.Text.Trim)                cmd.Parameters.AddWithValue("@student_passport", txtPassport.Text.Trim)                cmd.Parameters.AddWithValue("@student_rcnumber", txtReceipt.Text.Trim)                cmd.Parameters.AddWithValue("@test_level", txtTestLevel.Text)                cmd.Parameters.AddWithValue("@test_venue1", txtVenue1.Text.Trim)                cmd.Parameters.AddWithValue("@test_venue2", txtVenue2.Text.Trim)                cmd.Parameters.AddWithValue("@test_row", dropAlpha.SelectedItem)                cmd.Parameters.AddWithValue("@test_column", dropNumeric.SelectedItem)                cmd.Parameters.AddWithValue("@student_sex", dropSex.SelectedItem)                cmd.Parameters.AddWithValue("@student_age", dropAge.SelectedItem)                '------------Assembly Date Format                Dim dob As New Date                dob = dropDay.SelectedItem & "/" & dropMonth.SelectedItem & "/" & dropYear.SelectedItem                dob = String.Format("{0:MM/dd/yyyy}", dob)                cmd.Parameters.AddWithValue("@student_dob", dob)                '------------End Assembly                cmd.Parameters.AddWithValue("@student_country", txtCountry.Text)                cmd.Parameters.AddWithValue("@student_state", txtState.Text)                cmd.Parameters.AddWithValue("@guardian_name", txtGdName.Text.Trim)                cmd.Parameters.AddWithValue("@guardian_passport", txtGdPassport.Text.Trim)                cmd.Parameters.AddWithValue("@guardian_relation", txtGdRelation.Text.Trim)                cmd.Parameters.AddWithValue("@guardian_address1", txtAddress1.Text.Trim)                cmd.Parameters.AddWithValue("@guardian_address2", txtAddress2.Text.Trim)                cmd.Parameters.AddWithValue("@guardian_postcode", txtPostal.Text)                cmd.Parameters.AddWithValue("@guardian_homephone", txtHome.Text)                cmd.Parameters.AddWithValue("@guardian_mobilephone", txtMobile.Text)                cmd.Parameters.AddWithValue("@guardian_otherphone", txtOther.Text)                cmd.Parameters.AddWithValue("@payment", txtPayment.Text.Trim)                cmd.Parameters.AddWithValue("@remarks", txtRemarks.Text.Trim)                If rdbAbsent.Checked = True Then                    cmd.Parameters.AddWithValue("@student_att", 0)                ElseIf rdbPresent.Checked = True Then                    cmd.Parameters.AddWithValue("@student_att", 1)                End If             conn.Open()                cmd.ExecuteNonQuery()            conn.Close()Then i got this error must declar scalar variable @student_name need some enlighten plzz T_T

View 7 Replies View Related

T-SQL (SS2K8) :: Cursor - Declare Variable Error

Sep 9, 2014

The below cursor is giving an error

DECLARE @Table_Name NVARCHAR(MAX) ,
@Field_Name NVARCHAR(MAX) ,
@Document_Type NVARCHAR(MAX)

DECLARE @SOPCursor AS CURSOR;
SET
@SOPCursor = CURSOR FOR

[Code] ....

The @Table_Name variable is declared, If I replace the delete statement (DELETE FROM @Table_Name ) with (PRINT @table_name) it works and print the table names.

Why does the delete statement give an error ?

View 3 Replies View Related

Need Help With SQL UPDATE...and The Error: Must Declare The Scalar Variable @UserName.

Jul 11, 2007

I am trying to update a field in a pre-existing record in my database.  This update is supposed to happen when the protected sub entitled "PictureUpload" is called by button click.  I listed the code immediately below and then I listed the error that I am getting further below that.
Does anybody know what I am doing wrong and why I am getting this error?
Thanks in advance and forgive my newbie ignorance!
 
Here is a portion of the Protected Sub that I am using in an attempt to update a field in a pre-existing record...
Protected Sub PictureUpload(ByVal sender As Object, ByVal e As System.EventArgs)
    Dim ImageUploaded As Integer = 1
    srcprofiles_BasicProperties.UpdateParameters("ImageUploaded").DefaultValue = ImageUploaded
    srcprofiles_BasicProperties.Update()
End Sub
Here is the SqlDataSource control I included on the page with (what I hope is) appropriate formatting...
<asp:SqlDataSource ID="srcprofiles_BasicProperties" runat="server" ConnectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|UserProfilesDB.mdf;Integrated Security=True;User Instance=True" ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM [profiles_BasicProperties] WHERE ([UserName] = @UserName)" UpdateCommand="UPDATE [profiles_BasicProperties] SET [ImageUploaded] = @ImageUploaded WHERE ([UserName] = @UserName)">    <SelectParameters>        <asp:Parameter DefaultValue="imageuploaded01" Name="UserName" Type="String" />    </SelectParameters>    <UpdateParameters>        <asp:Parameter Name="ImageUploaded" Type="Int32" />    </UpdateParameters></asp:SqlDataSource>
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^...and now the error...^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Server Error in '/PC_Dev' Application.


Must declare the scalar variable "@UserName".
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Must declare the scalar variable "@UserName".Source Error:



Line 164: Dim ImageUploaded As Integer = 1
Line 165: srcprofiles_BasicProperties.UpdateParameters("ImageUploaded").DefaultValue = ImageUploaded
Line 166: srcprofiles_BasicProperties.Update()
Line 167:
Line 168: Source File: C:UsersMDocumentsPC_DevProfiles_BuildProfile.aspx    Line: 166 Stack Trace:



[SqlException (0x80131904): Must declare the scalar variable "@UserName".]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +859322
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +736198
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1959
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +149
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +903
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +415
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135
System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +401
System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +721
System.Web.UI.WebControls.SqlDataSource.Update() +17
ASP.profiles_buildprofile_aspx.PictureUpload(Object sender, EventArgs e) in C:UsersMDocumentsPC_DevProfiles_BuildProfile.aspx:166
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

View 3 Replies View Related

Crystal Report Error Message (Must Declare The Variable)

Apr 2, 2008

hi there,

I have a crystal report that calls a stored procedure from SQL Server 2000. The only parameter I have is @A
the SP is:
--------------
CREATE PROCEDURE Final
@A INT
AS
Declare @SQL VARCHAR(2000)
SET @SQL = 'SELECT * FROM Schools where Areano = @A'
EXEC(@SQL)
GO


-------------

From the standard report creation wizard, i choose the data (SP). and select the SP, then i got window to enter a parameter values for @A, and i check ( Set to Null value), then press OK, the following error occured

Must Declare the variable @A


Please help me!

Best Regards

View 5 Replies View Related

Must Declare The Scalar Variable @CategoryID. Error In Stored Procdure.

Dec 12, 2007

Hi People, i'm having some trouble with a stored proceddure used for an updat from a Formview.
Error message i'm getting is Must declare the scalar variable "@CategoryID".
I can't seem to work out why this is occuring and not allowing my proc to run properly. If anyone could help me that would be great :-)
Here is the whole store procedure.
 ALTER PROCEDURE dbo.DeluxeGuideAdmin_Update
@ID int,@CategoryID varchar(10),
@RegionID int,@CompanyName nvarchar(25),
@Email nvarchar(25),@PDFFront nvarchar(50),
@PDFBack nvarchar(50),@ThumbFront nvarchar(50),
@ThumbBack nvarchar(50),@Notes nvarchar(max)
 
AS
DECLARE @SQL varchar(1000)
SET @SQL = 'UPDATE DeluxeGuide SET CategoryID = @CategoryID, RegionID = @RegionID, CompanyName = @CompanyName, Email = @Email, Notes = @Notes'IF (@PDFFront IS NOT NULL) OR (@PDFFront != '')
SET @SQL = @SQL + ', PDFFront = @PDFFront'IF (@PDFBack IS NOT NULL) OR (@PDFBack != '')
SET @SQL = @SQL + ', PDFBack = @PDFBack'IF (@ThumbFront IS NOT NULL) OR (@ThumbFront != '')
SET @SQL = @SQL + ', ThumbFront = @ThumbFront'IF (@ThumbBack IS NOT NULL) OR (@ThumbBack != '')
SET @SQL = @SQL + ', ThumbBack = @ThumbBack'
SET @SQL = @SQL + ' WHERE (ID = @ID)'
Print '@SQL = ' + @SQLEXEC(@SQL)
 
RETURN

View 14 Replies View Related

Help With Inserting A New Record Into Database - Error Must Declare The Scalar Variable @BookMarkArrayA.

Mar 24, 2006

Hi,
Can anybody help me with this, I've got a simple program to add a new record to a table (2 items ID - Integer and Program - String) that matches all examples I can find, but when I run it I get the error :
Must declare the scalar variable "@BookMarkArrayA".
when it reaches the .insert command, I've tried using a local variable temp in place of the array element and .ToString , but still get the same error  
This is the code :
Public Sub NewCustomer()
Dim temp As String = " "
Dim ID As Integer = 1
'Restore the array from the view state
BookMarkArrayA = Me.ViewState("BookMarkArrayA")

temp = BookMarkArrayA(6)
Dim Customer As SqlDataSource = New SqlDataSource()

Customer.ConnectionString = ConfigurationManager.ConnectionStrings("CustomerConnectionString").ToString()
Customer.InsertCommand = "INSERT INTO [Table1] ([ID],[Program]) VALUES (@ID, @BookMarkArrayA(6))"
Customer.InsertParameters.Add ("ID", ID)
Customer.InsertParameters.Add ("Program", @BookMarkArrayA(6))
Customer.Insert()    

End Sub
Cheers
Ken
 

View 11 Replies View Related

SQL Server 2012 :: Stored Procedure Execution Error - Must Declare Scalar Variable

Oct 15, 2014

I have created stored procedure to find out first word of the keyword. I am getting error below on execution:

"Must declare the scalar variable "@SubjectBeginning"."

View 9 Replies View Related

ERROR:Syntax Error Converting Datetime From Character String. With Stored Procedure

Jul 12, 2007

Hi All,





i have migrated a DTS package wherein it consists of SQL task.

this has been migrated succesfully. but when i execute the package, i am getting the error with Excute SQL task which consists of Store Procedure excution.



But the SP can executed in the client server. can any body help in this regard.





Thanks in advance,

Anand

View 4 Replies View Related

Getting Server Error Syntax Error Converting The Nvarchar Value 'Sonoma' To A Column Of Data Type Int.

Apr 20, 2007

Hi, all
I'm getting this error at runtime when my page tries to populate a datagrid. Here's the relevant code.
First, the user selects his choice from a dropdownlist, populated with a sqldatasource control on the aspx side:<asp:SqlDataSource ID="sqlDataSourceCompany" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [PayrollCompanyID], [DisplayName] FROM [rsrc_PayrollCompany] ORDER BY [DisplayName]">
</asp:SqlDataSource>
 And the dropdown list's code:<asp:DropDownList ID="ddlPayrollCompany" runat="server" AutoPostBack="True" DataSourceID="sqlDataSourcePayrollCompany"
DataTextField="DisplayName" DataValueField="PayrollCompanyID">
</asp:DropDownList>
Then, I use the selectedindexchanged event to bind the data to the datagrid. Here's that code:
 1 Sub BindData()
2
3 Dim ds As New DataSet
4 Dim sda As SqlClient.SqlDataAdapter
5 Dim strSQL As String
6 Dim strCon As String
7
8 strSQL = "SELECT [SocialSecurityNumber], [Prefix], [FirstName], [LastName], [HireDate], [PayrollCostPercent], " & _
9 "[Phone], [BadgeNumber], [IsSupervisor], [SupervisorID], [IsUser], [IsScout] FROM [rsrc_Personnel] " & _
10 "WHERE ([PayrollCompanyID] = @PayrollCompanyID)"
11
12 strCon = "Data Source=DATASOURCE;Initial Catalog=DATABASE;User ID=USERID;Password=PASSWORD"
13
14 sda = New SqlClient.SqlDataAdapter(strSQL, strCon)
15
16 sda.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@PayrollCompanyID", Me.ddlPayrollCompany.SelectedItem.ToString()))
17
18 sda.Fill(ds, "rsrc_Personnel")
19
20 dgPersonnel.DataSource = ds.Tables("rsrc_Personnel")
21 dgPersonnel.DataBind()
22
23 End Sub
24

 
I'm assuming my problem lies in line 16 of the above code. I've tried SelectedItemIndex, SelectedItemValue too and get errors for those, as well.
What am I missing?
Thanks for anyone's help!
Cappela07

View 2 Replies View Related

Execute SQL Task: Executing The Query Exec (?) Failed With The Following Error: Syntax Error Or Access Violation. Possible F

Jan 23, 2008

Hi,
I'm having an SSIS package which gives the following error when executed :

Error: 0xC002F210 at Create Linked Server, Execute SQL Task: Executing the query "exec (?)" failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.Task failed: Create Linked Server

The package has a single Execute SQL task with the properties listed below :

General Properties
Result Set : None

ConnectionType : OLEDB
Connection : Connected to a Local Database (DB1)
SQLSourceType : Direct Input
SQL Statement : exec(?)
IsQueryStorePro : False
BypassPrepare : False

Parameter Mapping Properties

variableName Direction DataType ParameterName

User::AddLinkSql Input Varchar 0


'AddLinkSql' is a global variable of package scope of type string with the value
Exec sp_AddLinkedServer 'Srv1','','SQLOLEDB.1',@DataSrc='localhost',@catalog ='DB1'

When I try to execute the Query task, it fails with the above error. Also, the above the sql statement cannot be parsed and gives error "The query failed to parse. Syntax or access violation"

I would like to add that the above package was migrated from DTS, where it runs without any error, eventhough
it gives the same parse error message.

I would appreciate if anybody can help me out of this issue by suggeting where the problem is.

Thanks in Advance.

View 12 Replies View Related

Error 241: Syntax Error Converting Datetime From Character String

Jan 7, 2004

Hi All, can someone help me,
i've created a stored procedure to make a report by calling it from a website.
I get the message error "241: Syntax error converting datetime from character string" all the time, i tryed some converting things but nothig works, probably it is me that isn't working but i hope someone can help me.
The code i use is:


CREATE proc CP_Cashbox @mID varchar,@startdate datetime,@enddate datetime
as
set dateformat dmy
go
declare @startdate as varchar
declare @enddate as varchar

--print "query aan het uitvoeren"

select sum(moneyout) / sum(moneyin)*100 as cashbox
from dbo.total
where machineID = '@mID' and njdate between '@startdate' and '@enddate'
GO



Thanx in front
Cya

View 14 Replies View Related

Snapshot Agent Gives Error: Line38: Syntax Error Near 'tabl'

Oct 1, 2005

Hi all,

View 4 Replies View Related

Alter Table Syntax Error - Anyone Sees The Error?

Nov 24, 2004

Hello,

the following alter table statement:

ALTER TABLE [dbo].[CalCalendar]
ALTER COLUMN [OID] uniqueidentifier NOT NULL PRIMARY KEY NONCLUSTERED

is answered with:

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'PRIMARY'.

which I consider to be interesting. Anyone has an idea why? I checked documentation but I do not see an error.

Note that:

ALTER TABLE [dbo].[CalCalendar]
ALTER COLUMN [OID] uniqueidentifier NOT NULL

DOES get executed, and

ALTER TABLE [dbo].[CalCalendar]
ALTER COLUMN [OID] uniqueidentifier NOT NULL PRIMARY KEY

produces the same error.

Now, in my understanding this has nothing to do with an index may already exist etc. - the eror indicates a SYNTAX error, before any checking. Makes no sense to me, though, reading the documentation.

So - anyone an idea?

View 4 Replies View Related

Error 0: Syntax Error Or Access Violation

Aug 1, 2004

Hi,
I'm writing a stored procedure and when I click on the Check Syntax button its giving me the error in the subject. I'm not really sure whats wrong with this. Here is my Stored Procedure code. Any help wud be appreciated.


CREATE PROC CabsSchedule_Insert
{
@JulianDatesmallint,
@SiteCodesmallint,
@CalendarDaysmallint,
@BillPeriodsmallint,
@WorkDaysmallint,
@CalDayBillRcvd varchar(30),
@Remarksvarchar(50)
}
AS
INSERT INTO CabsSchedule
(JulianDate, SiteCode, CalendarDay, BillPeriod, WorkDay, CalDayBillRcvd, Remarks)
VALUES
(@JulianDate, @SiteCode, @CalendarDay, @BillPeriod, @WorkDay, @CalDayBillRcvd, @Remarks)


Thanks,

View 2 Replies View Related

SQL Syntax Error

Apr 7, 2007

Hi Guys, I'm hoping somebody can help me with this really frustrating problem that I'm having.......
 I'm developing a peer to peer file sharing application (final year degree project) in which I use a web service & sql database as the management server. For some strange reason, I'm getting an SQL syntax error on some machines but not on others when I call the method to submit a file list to the server (see below for code for the method). Another strange thing is that on different machines, I'm getting a different error. I've seen "incorrect syntax near 'd' ". and also "incorrect syntax near 've' ", while on two other machines it works just fine - It appears that the connection to the webservice and to the database is working just fine on all machines as before this method I have a login which works perfectly and the data is represented in the database.
Does anybody have any pointers or even the slighest idea what can cause an error like this or have seen anything like this before. Hoping to get this sorted pretty soon as the deadline is nearing. All and any help is very much appreciated!!!!
Kevin
public void submitFiles(FileObject[] files, string peerID)
{
foreach (FileObject fo in files)
{ System.Text.StringBuilder submit = new System.Text.StringBuilder("INSERT INTO SharedFiles (FileID, FileName, FileType, FileSize, PeerID) VALUES ('" + fo.guid.ToString() + "', '" + fo.name + "' ,'" + fo.name + "', '" + fo.size + "', '" + peerID + "')");
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(submit.ToString(), con);
try { con.Open();
cmd.ExecuteNonQuery();
 } finally { con.Close(); } 
}
 }

View 2 Replies View Related

SQL Syntax Error

Aug 8, 2007

Hello, I have this sql quiery: sqlcommand2.CommandText = "Select Count(UserIP) From InboundTraffic Where InboundURL Contains('" & SiteDomain(i).ToString & "') and DateTimeReceived > " & Last30Days

 
SiteDomain is placing a string variable such as website.com and Last30Days is a date variable which = now - 30days
Im getting this error "Syntax error (missing operator) in query expression 'InboundURL Contains('website.com') and DateTimeReceived > 7/9/2007 8:20:30 PM'"
 
What am I mising?  THANKS!

View 5 Replies View Related

Syntax Error

Jan 17, 2008

Could someone help me with this error message:
Syntax error converting the varchar value '180 Ways to Walk the Leadership Talk by John Baldoni' to a column of data type int.
Getting error on the Titles.title column.  Tried casting it but it still does not work.  What am I missing? 
CREATE procedure GetRequestInfo@Requestorid int
ASselect distinct requestors.Requestorid, CAST(Titles.title AS VARCHAR(255)), requestors.requestorEmail,Requestors.requestdate, fname, lname, phonenum,StreetAddress1, City, State, Zip,LibraryRequest.ShipDate,LibraryRequest.DueDate,LibraryRequest.ReturnDate,
Cast(DATEPART(m, requestors.requestDate) as Varchar(5)) + '/' +Cast(DATEPART(d, requestors.requestDate) as Varchar(5)) + '/' + Cast(DATEPART(yy, requestors.RequestDate) as Varchar(5)) as 'RequestDate'
from Requestorsjoin Titles on titles.Titleid = requestors.Titleidjoin libraryRequest on LibraryRequest.Titleid = LibraryRequest.Titleidwhere Requestors.requestorid = requestors.requestoridGO

View 2 Replies View Related

Syntax Error In IF ELSE

Apr 17, 2008

Can anyone tell me why I get a syntax error on the THEN and the ELSE?
@Start datetime,@End datetime,@EmailAck bit,@SelectedProcess nvarchar(25)ASBEGINIF @SelectedProcess = 'Monthly' THENUPDATE tblReminderSchedule SETPrintedDate=GETDATE(),[Status]=1FROM  tblReminderSchedule INNER JOIN  tbllOAN ON tblReminderSchedule.lOAN_ID = tbllOAN.lOAN_IDWHERE (tblReminderSchedule.ReminderDate BETWEEN @Start AND @End) AND (dbo.tblReminderSchedule.ReceivedDate IS NULL) AND   (tbllOAN.ReminderByEmail = @EmailAck) AND (tbllOAN.Frequency = 'Monthly')ELSEUPDATE tblReminderSchedule SETPrintedDate=GETDATE(),[Status]=1FROM  tblReminderSchedule INNER JOIN  tbllOAN ON tblReminderSchedule.lOAN_ID = tbllOAN.lOAN_IDWHERE (tblReminderSchedule.ReminderDate BETWEEN @Start AND @End) AND (dbo.tblReminderSchedule.ReceivedDate IS NULL) AND   (tbllOAN.ReminderByEmail = @EmailAck) AND (tbllOAN.Frequency <> 'Monthly')END 

View 4 Replies View Related

Syntax Error

Mar 21, 2004

I am trying to open a table in my DB to check for login ids:

The code I used is below> I will appreciate any help on this matter

<CODE>
Public Function Authorize(ByVal Username As String, ByVal Password As String) As Integer
Dim sql As String
Dim con As New SqlConnection("data source=localhost; initial catalog=Jasist; Integrated Security = SSPI")
sql = "Select * from USER Where User_Name = '" & Username & "' and User_Passwd = '" & Password & "'"
con.Open()
Dim cmd As New SqlCommand(sql, con)
Dim Id1 As Integer
Dim dr As SqlDataReader = cmd.ExecuteReader
If dr.Read Then
Id1 = 1
con.Close()
dr.Close()
Return Id1
Else
con.Close()
dr.Close()
Return 0
End If
End Function
<CODE>



THE ERROR GENERATED WAS->

Server Error in '/Jasist' Application.
--------------------------------------------------------------------------------

Incorrect syntax near the keyword 'USER'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'USER'.

Source Error:


Line 19: con.Open()
Line 20: Dim cmd As New SqlCommand(sql, con)
Line 21: Dim Id1 As Integer
Line 22: Dim dr As SqlDataReader = cmd.ExecuteReader
Line 23:


Source File: C:InetpubwwwrootJasistClass1.vb Line: 21



--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573

View 2 Replies View Related

Syntax Error

Mar 27, 2001

Hi, hope someone can spend a minute checking out my script error. The following is part of my SQL statement. It has got syntax error near "="
I was hoping the script could run 100 times and print number 1 to 100.

DECLARE @ID int
SET @id = 1
EXEC (' WHILE ' + @id + ' <= 100 ' + ' BEGIN SELECT ' + @id +
' SET ' + @id + ' = ' + @id + ' + 1 ' + ' END ' )

Any input is most welcome.


Richard

View 4 Replies View Related

Need Help With Syntax Error.

Oct 17, 2000

Can anyone tell me what is wrong with the script below?

INSERT INTO #SUBSCRIBERLIST(EMEMBER_GUID,EHBSID)SELECT (M.EMEMBER_GUID,@EHBSID)
FROM EMEMBER M WHERE M.EMEMBER_GUID = @EM_ID1 AND M.MEMBERTYPE <>1

This is the error I am getting.

Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near ','.


Thanks in advance.

Matt

View 1 Replies View Related

Syntax Error Near '='

Aug 31, 2004

hi
i am getting this error while running SQL query : syntax error near '='

Query is :

SELECT People.People, People.Name,
Sum(([ProjectStatusReport].[Week]), 0, ([ProjectStatusReport].[Week] = #8/27/2004#, 1, 0)) AS Created,
Sum((ProjectStatusReport.Week), 0, (ProjectStatusReport.Week = #8/27/2004#, (ProjectStatusReport.Accomplishments) Or (ProjectStatusReport.Plans), 0, 1, 0)) AS Complete,
People.email FROM Register INNER JOIN SR_Status ON Register.SR_Status = SR_Status.SR_Status
LEFT JOIN ProjectStatusReport ON Register.Register = ProjectStatusReport.Project INNER JOIN StakeHolders
ON Register.Register = StakeHolders.Register INNER JOIN People ON StakeHolders.People = People.People
WHERE SR_Status.Status='Active' AND StakeHolders.Status = 'Yes' GROUP BY People.People, People.Name, People.email
HAVING (Sum(ProjectStatusReport.Week), 0, ((ProjectStatusReport.Week) = #8/27/2004#, 1, 0) > 0))
AND Sum(ProjectStatusReport.Week),0,((ProjectStatusRep ort.Week) = #8/27/2004#,(ProjectStatusReport.Accomplishments) Or (ProjectStatusReport.Plans),0,1,0)>0
ORDER BY People.People

Any help

Regards

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved