Must Declare The Scalar Variable In Table-valued Function

May 18, 2007

Hi, I'm having trouble with this multi-statement table-valued function:

ALTER FUNCTION MakeArDetail
(
-- Add the parameters for the function here
@dateStart DATETIME,
@dateEnd DATETIME
)
RETURNS @arDetail TABLE
(
Insurer VARCHAR(50),
NABP INT DEFAULT 0,
Claim MONEY DEFAULT 0,
Payment MONEY DEFAULT 0,
NumRx CHAR(7),
PatientName VARCHAR(50),
Paid030 MONEY DEFAULT 0,
Paid3160 MONEY DEFAULT 0,
Paid6190 MONEY DEFAULT 0,
Paid91120 MONEY DEFAULT 0,
Paid121 MONEY DEFAULT 0
)
AS
BEGIN
DECLARE @arTemp TABLE
(
Insurer VARCHAR(50),
NABP INT DEFAULT 0,
Claim MONEY DEFAULT 0,
Payment MONEY DEFAULT 0,
NumRx CHAR(7),
PatientName VARCHAR(50),
Paid030 MONEY DEFAULT 0,
Paid3160 MONEY DEFAULT 0,
Paid6190 MONEY DEFAULT 0,
Paid91120 MONEY DEFAULT 0,
Paid121 MONEY DEFAULT 0
)

INSERT INTO @arTemp
SELECT DISTINCT Insurer,NABP,0,0,NumRx,Patient,0,0,0,0,0 FROM Pims;
UPDATE @arTemp SET Claim =
(SELECT SUM(Pims.AmtReq)
FROM Pims
WHERE Pims.Insurer = @arTemp.Insurer AND
Pims.NABP = @arTemp.NABP AND
Pims.NumRx = @arTemp.NumRx
);

INSERT INTO @arDetail SELECT * FROM @arTemp
RETURN
END
GO

I get
Msg 137, Level 15, State 2, Procedure MakeArDetail, Line 43
Must declare the scalar variable "@arTemp".

I don't understand why SQL thinks @arTemp is a scalar variable which has to be declared.
If I don't include the UPDATE command the thing works.

View 10 Replies


ADVERTISEMENT

Using A Scalar Valued Function As A Parameter Of A Table Valued Function?

Feb 1, 2006

Ok, I'm pretty knowledgable about T-SQL, but I've hit something that seems should work, but just doesn't...
I'm writing a stored procedure that needs to use the primary key fields of a table that is being passed to me so that I can generate what will most likely be a dynamically generated SQL statement and then execute it.
So the first thing I do, is I need to grab the primary key fields of the table.  I'd rather not go down to the base system tables since we may (hopefully) upgrade this one SQL 2000 machine to 2005 fairly soon, so I poke around, and find sp_pkeys in the master table.  Great.  I pass in the table name, and sure enough, it comes back with a record set, 1 row per column.  That's exactly what I need.
Umm... This is the part where I'm at a loss.  The stored procedure outputs the resultset as a resultset (Not as an output param).  Now I want to use that list in my stored procedure, thinking that if the base tables change, Microsoft will change the stored procedure accordingly, so even after a version upgrade my stuff SHOULD still work.  But... How do I use the resultset from the stored procedure?  You can't reference it like a table-valued function, nor can you 'capture' the resultset for use using the  syntax like:
DECLARE @table table@table=EXEC sp_pkeys MyTable
That of course just returns you the RETURN_VALUE instead of the resultset it output.  Ugh.  Ok, so I finally decide to just bite the bullet, and I grab the code from sp_pkeys and make my own little function called fn_pkeys.  Since I might also want to be able to 'force' the primary keys (Maybe the table doesn't really have one, but logically it does), I decide it'll pass back a comma-delimited varchar of columns that make up the primary key.  Ok, I test it and it works great.
Now, I'm happily going along and building my routine, and realize, hey, I don't really want that in a comma-delimited varchar, I want to use it in one of my queries, and I have this nice little table-valued function I call split, that takes a comma-delimited varchar, and returns a table... So I preceed to try it out...
SELECT *FROM Split(fn_pkeys('MyTable'),DEFAULT)
Syntax Error.  Ugh.  Eventually, I even try:
SELECT *FROM Split(substring('abc,def',2,6),DEFAULT)
Syntax Error.
Hmm...What am I doing wrong here, or can't you use a scalar-valued function as a parameter into a table-valued function?
SELECT *FROM Split('bc,def',DEFAULT) works just fine.
So my questions are:
Is there any way to programmatically capture a resultset that is being output from a stored procedure for use in the stored procedure that called it?
Is there any way to pass a scalar-valued function as a parameter into a table-valued function?
Oh, this works as well as a work around, but I'm more interested in if there is a way without having to workaround:
DECLARE @tmp varchar(8000)
SET @tmp=(SELECT dbo.fn_pkeys('MyTable'))
SELECT *
FROM Split(@tmp,DEFAULT)

View 1 Replies View Related

SQL Server 2008 :: Temp Table - Must Declare The Scalar Variable

Mar 25, 2015

I can create a temp table like this, and immediately read the data:

declare @NAICSGroups table (NAICS2DigitCode int, NAICSShortTitle varchar(35));
insert into @NAICSGroups (NAICS2DigitCode, NAICSShortTitle)
values
(11,'Agriculture'),
(21,'Mining'),
(22,'Utilities'),

[Code] .....

View 2 Replies View Related

Must Declare The Scalar Variable

Jul 17, 2006

Hello!

I have a aspx page in which I have a Gidview populated by a sqlDataSouce.
This is my code:



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

<!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:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4"
DataSourceID="SqlDataSource1" ForeColor="#333333" GridLines="None" Style="z-index: 100; left: 0px; position: absolute; top: 0px"> <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" /> <Columns> <asp:TemplateField> <ItemTemplate> <asp:LinkButton ID="Editar" Text="Editar" runat="server" CommandName="Edit"></asp:LinkButton> </ItemTemplate> <EditItemTemplate> <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="True" CommandName="Update"
Text="Actualizar" style="color: white"></asp:LinkButton> <asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" CommandName="Cancel"
Text="Cancelar" style="color: white"></asp:LinkButton> </EditItemTemplate> </asp:TemplateField> <asp:TemplateField> <ItemTemplate> <asp:LinkButton ID="Apagar" Text="Apagar" runat="server" CommandName="Delete" OnClientClick='return confirm("Tem a certeza que deseja apagar este registo?");' CausesValidation="false"></asp:LinkButton> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Id_CostEmployee" InsertVisible="False" SortExpression="Id_CostEmployee"> <EditItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Eval("Id_CostEmployee") %>'></asp:Label> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Bind("Id_CostEmployee") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Id_Employee" SortExpression="Id_Employee"> <EditItemTemplate> <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("Id_Employee") %>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label2" runat="server" Text='<%# Bind("Id_Employee") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="FullName" SortExpression="FullName"> <EditItemTemplate> <asp:TextBox ID="textbox5" runat="server" Text='<%# Bind("FullName")%>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label6" runat="server" Text='<%# Bind("FullName") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="NumEmployee" SortExpression="NumEmployee"> <EditItemTemplate> <asp:TextBox ID="TextBox6" runat="server" Text='<%# Bind("NumEmployee") %>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label7" runat="server" Text='<%# Bind("NumEmployee") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Period" SortExpression="Period"> <EditItemTemplate> <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("Period") %>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label3" runat="server" Text='<%# Bind("Period") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="CostHour" SortExpression="CostHour"> <EditItemTemplate> <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("CostHour") %>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label4" runat="server" Text='<%# Bind("CostHour") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Date" SortExpression="Date"> <EditItemTemplate> <asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("Date") %>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label5" runat="server" Text='<%# Bind("Date") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> </Columns> <RowStyle BackColor="#EFF3FB" /> <EditRowStyle BackColor="#2461BF" /> <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" /> <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" /> <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" /> <AlternatingRowStyle BackColor="White" /> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:EuroscutConnectionString %>" SelectCommand="SELECT [HR.CostEmployee].Id_CostEmployee, [HR.CostEmployee].Id_Employee, [HR.CostEmployee].Period, [HR.CostEmployee].CostHour, [HR.CostEmployee].Date, [HR.Employee].FullName, [HR.Employee].NumEmployee FROM [HR.CostEmployee] INNER JOIN [HR.Employee] ON [HR.CostEmployee].Id_Employee = [HR.Employee].Id_Employee"
UpdateCommand="UPDATE [HR.CostEmployee] set Period = @Period, CostHour = @CostHour where Id_CostEmployee = @Id_CostEmployee"
DeleteCommand="DELETE from [HR.CostEmployee] where (Id_CostEmployee = @Id_CostEmployee)"> <UpdateParameters> <asp:Parameter Name="Period" /> <asp:Parameter Name="CostHour" /> <asp:Parameter Name="Id_CostEmployee" /> </UpdateParameters> <DeleteParameters> <asp:Parameter Name="Id_CostEmployee" Type="int32" /> </DeleteParameters> </asp:SqlDataSource> </div> </form></body></html> When I run the page I'm able to edit the row but when I try to delete it gives me the error:

Must declare the scalar variable "@Id_CostEmployee".

I'm tired of "googling" this error, and I've tried all the advices, nothing...
I don't know what is happening here, I have 5 other forms, all simillar and they all work!

Any suggestions, pleeeeaaaase?

Thank's!
Paula

View 10 Replies View Related

Must Declare The Scalar Variable

Feb 20, 2007

I'm making an ecommerce web app from following the Apress "Beginning ASP.Net 2 E-commerce with C#" book, and when I implement a stored procedure (I made a mdf DB in the app_Data folder), I get the following message: Must declare the scalar variable @CategoryIDThe code used to obtain this error is below: CREATE PROCEDURE DeleteCategory(@CategoryINT int)ASDELETE FROM CategoryWHERE CategoryID = @CategoryID I get this error with every Stored Procedure I try to implement. What should I do to fix this? In SQL Server 2k5 Management Studio, this problem does not present itself.

View 1 Replies View Related

Must Declare The Scalar Variable

Mar 30, 2007

Hi with the code below I am getting the error
Error inserting record. Must declare the scalar variable "@contractWHERE"
 I removed @contract and it then gave me the error
Error inserting record. Must declare the scalar variable "@zipWHERE"
 I was wondering if some can point me in the right direction for fixxing this
protected void cmdUpDate_Click(Object sender, EventArgs e)
{
//Define ADO.NET Objects.
string updateSQL;
updateSQL = "UPDATE Authors SET ";
updateSQL += "au_id=@au_id, au_fname=@au_fname, au_lname=@au_lname, ";
updateSQL += "phone=@phone, address=@address, city=@city,state=@state, ";
updateSQL += "zip=@zip, contract=@contract";
updateSQL += "WHERE au_id@au_id_original";
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(updateSQL, con);
//Add the parameters.
cmd.Parameters.AddWithValue("@au_id", txtID.Text);
cmd.Parameters.AddWithValue("@au_fname", txtFirstName.Text);
cmd.Parameters.AddWithValue("@au_lname", txtLastName.Text);
cmd.Parameters.AddWithValue("@phone", txtPhone.Text);
cmd.Parameters.AddWithValue("@address", txtAddress.Text);
cmd.Parameters.AddWithValue("@city", txtCity.Text);
cmd.Parameters.AddWithValue("@state", txtState.Text);
cmd.Parameters.AddWithValue("@zip", txtZip.Text);
cmd.Parameters.AddWithValue("@contract", Convert.ToInt16(chkContract.Checked));
cmd.Parameters.AddWithValue("au_id_original", lstAuthor.SelectedItem.Value);
//Try to open the database and execute the update
try
{
con.Open();
int updated = cmd.ExecuteNonQuery();
lblStatus.Text = updated.ToString() + " records inserted.";
}
catch (Exception err)
{
lblStatus.Text = "Error inserting record. ";
lblStatus.Text += err.Message;
}
finally
{
con.Close();
}
}
}
 
Many Thanks in advance
 

View 3 Replies View Related

Must Declare The Scalar Variable?

Jun 17, 2013

CREATE PROCEDURE [dbo].[Testing]
@FilteredID VARCHAR (MAX),
@SchoolCode VARCHAR (MAX),

[Code]....

I tried to execute above sproc in SQL Server Management Studio , I received the error: Must declare the scalar variable "@Score1".

View 5 Replies View Related

Must Declare Scalar Variable???

Aug 31, 2006

OK i have my stored procedure all set up and working.. But when i try and add a second variable called @iuser and then after i execute the stored procedure, i get an error saying:-
ERROR
"Must declare scalar variable @iuser"

Here is the code i am using in my stored proc, also my stored proc worked fine before i used a second variable??!

//BEGIN

ALTER PROCEDURE [dbo].[putpending]
(@cuuser nvarchar(1000), @iuser nvarchar(1000))

AS
Declare @sql nvarchar(1000)

SELECT @sql =
'INSERT INTO ' +
@cuuser +
' (Pending) VALUES (@iuser)'

EXECUTE (@sql)

RETURN
//END

And i know my VB.NET code is working but i will put it in anyway:-

//BEGIN

'variables
Dim user As String
user = Profile.UserName
Dim intenduser As String
intenduser = DetailsView1.Rows(0).Cells(1).Text.ToString()

'connection settings
Dim cs As String
cs = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|friends.mdf;Integrated Security=True;User Instance=True"
Dim scn As New SqlConnection(cs)
'parameters
Dim cmd As New SqlCommand("putpending", scn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@cuuser", SqlDbType.NVarChar, 1000)
cmd.Parameters("@cuuser").Value = user
cmd.Parameters.Add("@iuser", SqlDbType.NVarChar, 1000)
cmd.Parameters("@iuser").Value = intenduser
'execute
scn.Open()

cmd.ExecuteNonQuery()

scn.Close()
//END

Any ideas why i am getting this error message?

View 10 Replies View Related

Must Declare Scalar Variable @ID

Mar 24, 2007

the following is my code
can anybody rectify my problem that i get when running my application
"Must declare scalar variable @ID"
<asp:GridView ID="GridView1" DataKeyNames="ID" runat="server" AutoGenerateColumns="False" BackImageUrl="~/App_Themes/SkinFile/back1.jpg"
BorderColor="Teal" BorderStyle="Solid" DataSourceID="SqlDataSource1">
<Columns>
<asp:CommandField ButtonType="Button" ShowSelectButton="True" />
<asp:BoundField DataField="ID" HeaderText="ID" SortExpression="ID" />
<asp:BoundField DataField="Answers" HeaderText="Answers" SortExpression="Answers" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:YahooConnectionString7 %>"
DeleteCommand="DELETE FROM Answers WHERE (ID = @ID)" InsertCommand="INSERT INTO Answers(ID, Answers) VALUES (@ID, @Answers)"
SelectCommand="SELECT Answers.* FROM Answers" UpdateCommand="UPDATE Answers SET ID = @ID, Answers = @Answers WHERE (ID = @ID)">
<DeleteParameters>
<asp:Parameter Name="ID" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="ID" />
<asp:Parameter Name="Answers" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="ID" />
<asp:Parameter Name="Answers" />
</InsertParameters>
</asp:SqlDataSource>

View 1 Replies View Related

Must Declare The Scalar Variable

Mar 24, 2008

Following stored proc uses dynamic sql but it gives the error
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@ProjectBenefitID".
though its declared .please. tell the workaround



ALTER PROCEDURE [dbo].[spPMPT_GetBenefit]

@ProjectBenefitID INT,

@OrderBYVARCHAR(40),
-- Parmeters for Paging [Start]
@TotalPages INT OUT ,
@CurrentPageNumber INT OUT ,
@NumberOfRecordsINT = 5, /*PageSize*/
@CurrentPage INT = 0/*PageNumber*/

-- Parmeters for Paging [End]

AS

SET NOCOUNT ON

DECLARE @TMPFLOAT
DECLARE @ErrorMsgID INT
DECLARE@ErrorMsg VARCHAR(200)


----- Paging declarations start
DECLARE @SQLFinal NVARCHAR(4000)
DECLARE @Count INT
DECLARE @SC VARCHAR(4000)
----- Paging declarations end


DECLARE@SelectASVARCHAR(4000)
DECLARE@FromASVARCHAR(4000)
DECLARE@WhereASVARCHAR(4000)
DECLARE@LsOrderBy ASVARCHAR(4000)



-- Initialize vars
SET @SC= ''
SET @From= ''
SET @Where= ''
SET @Select= ''
SET @SQLFinal= ''
SET @Count= 0


IF (@CurrentPage = 0 OR @CurrentPage IS NULL)
BEGIN
--Generate error message
SELECT @ErrorMsg = 'Error occured in Stored Procedure ' + (SELECT name from sysobjects WHERE ID = @@PROCID) + '. The Page Number cannot be zero.'
--Raise error to the user
RAISERROR (@ErrorMsg,16/*severity*/,1/*state*/)
--Return error indicator
RETURN (-1)
END
IF (@NumberOfRecords = 0 OR @NumberOfRecords IS NULL )
BEGIN
--Generate error message
SELECT @ErrorMsg = 'Error occured in Stored Procedure ' + (SELECT name from sysobjects WHERE ID = @@PROCID) + '. Number of records per page cannot be zero.'
--Raise error to the user
RAISERROR (@ErrorMsg,16/*severity*/,1/*state*/)
--Return error indicator
RETURN (-1)
END
IF (@Orderby IS NULL OR @Orderby = '')
BEGIN
--Generate error message
SELECT @ErrorMsg = 'Error occured in Stored Procedure ' + (SELECT name from sysobjects WHERE ID = @@PROCID) + '. The Order by cannot be null.'
--Raise error to the user
RAISERROR (@ErrorMsg,16/*severity*/,1/*state*/)
--Return error indicator
RETURN (-1)
END




CREATE TABLE #TEMP_BENEFIT1
(

AssessBenefitID INT,
ProjectBenefitID INT,
ExpectedQuantity INT,
ExpectedQuality VARCHAR(2000),
Comments VARCHAR(2000)
)

INSERT INTO #TEMP_BENEFIT1 SELECT AssessBenefitID,ProjectBenefitID,
Quantity,Quality,
Comments
FROM PMPT_AssessBenefit
WHERE ProjectBenefitID=@ProjectBenefitID AND AssessFlag='E' --and AssessBenefitID=@IterationID

CREATE TABLE #TEMP_BENEFIT2
(

AssessBenefitID INT,
ProjectBenefitID INT,
ActualQuantity INT,
QtyFileID INT,
QtyFileName VARCHAR(100),
QtyFilepath VARCHAR(100),
ActualQuality VARCHAR(2000),
QuaFileID INT,
QualFileName VARCHAR(100),
QualFilepath VARCHAR(100),
Comments VARCHAR(2000),
refAssessBenefitID INT,
DateasON DATETIME
)

INSERT INTO #TEMP_BENEFIT2 SELECT PAB.AssessBenefitID,PAB.ProjectBenefitID,
PAB.Quantity,pab.qtyFileID,
(SELECT FileName FROM PMPT_Files WHERE FileID = pab.qtyFileID) as QtyFileName,
(SELECT UploadedFilePath FROM PMPT_Files WHERE FileID = pab.qtyFileID) as QtyFilepath,
PAB.Quality,pab.quaFileID,
(SELECT FileName FROM PMPT_Files WHERE FileID = pab.quaFileID) AS QualFileName,
(SELECT UploadedFilePath FROM PMPT_Files WHERE FileID = pab.quaFileID) as QuaFilepath,
PAB.Comments,PAB.refEXPAssessBenefitID,PAB.DateasON
FROM PMPT_AssessBenefit PAB
WHERE ProjectBenefitID=@ProjectBenefitID AND AssessFlag='A'


DECLARE @UNIT VARCHAR(100)
SELECT @UNIT=NAME FROM PMPT_Picklists WHERE PicklistID = (SELECT unitID FROM PMPT_ProjectBenefits WHERE ProjectBenefitID=@ProjectBenefitID)

IF @UNIT IS NULL
SET @UNIT = ''
SET @Select='
DECLARE @UNIT VARCHAR(100)
SELECT @UNIT=NAME FROM PMPT_Picklists WHERE PicklistID = (SELECT unitID FROM PMPT_ProjectBenefits WHERE ProjectBenefitID='+CONVERT(VARCHAR(10),@ProjectBenefitID))+'
SELECTT1.AssessBenefitID, CAST(T1.ExpectedQuantity AS VARCHAR)+'' ''+ @UNIT as ExpectedQuantity,
CAST( T2.ActualQuantity AS VARCHAR)+'' ''+ @UNIT as ActualQuantity, T2.QtyFileID, T2.QtyFileName AS QtyFileName ,T2.QtyFilepath, T1.ExpectedQuality AS ExpectedQuality , T2.ActualQuality AS ActualQuality ,
T2.QuaFileID,T2.QualFileName AS QualFileName ,T2.QualFilepath, T2.COMMENTS,CONVERT(VARCHAR(10),T2.DateasON,103) AS DateasON
FROM#TEMP_BENEFIT1 T1,#TEMP_BENEFIT2 T2
WHERET1.AssessBenefitID = T2.refAssessBenefitID'

View 1 Replies View Related

Must Declare The Scalar Variable

Feb 29, 2008



create PROCEDURE [Update_Purged]



AS

Declare @Stg_Purged_union table

(PurgedAccountUnionID int IDENTITY,

Coid char(50),

FacilityId int,

AccountID int,

PatientName char(50),

PatientNo char(50),

PT char(50),

ST char(50),

PurgeDt datetime);

BEGIN

INSERT INTO @Stg_Purged_Union

([Coid]
,null
,null

,[PatientName]

,[PatientNo]

,[PT]

,

,[PurgeDt])

select coid, patientname, patientno, pt, st, purgedt from

[Stg_PurgedAccount]


--updating the facilityid in @stg_purged_account_file_union


update @stg_purged_union set facilityid=(select

facilityid from appfacility as b

where b.unitnum=(select

unitnum from appfacility as c

where c.unitnum=b.unitnum) and @Stg_Purged_Union.coid=b.unitnum)



I am getting the following error.

Must declare the scalar variable "@Stg_Purged_Union"

View 4 Replies View Related

Must Declare The Scalar Variable @RefNo

Jul 25, 2006

ive encounter this problem when i install the application at client side..enfect, i try it on all the PC at my office, i wouldnt encounter this problem..can anyone tell me what is possibly wrong with this?here is the code possible code effectedASPDim myDataset As Data.DataSet = New Data.DataSet        Dim conn As Connection = New Connection("AbnormalControlDataConnString", "P_CreateAbnormalFormSect0")        conn.AddParameter("Pending", Me.Session.Item("UserId"), Data.SqlDbType.Char, 80)        conn.AddParameter("CreateBy", Me.Session.Item("UserId"), Data.SqlDbType.Char, 80)        conn.executeSproc(myDataset)        Me.Session.Add("RefNo", myDataset.Tables(0).Rows(0).Item(0))        conn = Nothing        myDataset = Nothing       gridview1.dataBind() <~~~~~~ i have this gridview bind another sproc                                                                  named[P_GetAbnormalFormSect0)conn.vb    Public Sub New(ByVal con As String, ByVal sprocName As String)        configCon = con        connectionString = System.Configuration.ConfigurationManager.ConnectionStrings(configCon).ConnectionString '        myConnection = New System.Data.SqlClient.SqlConnection(connectionString)        myCommand = New System.Data.SqlClient.SqlCommand()        myCommand.Connection = myConnection        myCommand.CommandType = CommandType.StoredProcedure        myCommand.CommandText = sprocName    End Sub    Public Sub AddParameter(ByVal name As String, ByVal value As String, ByVal type As     SqlDbType, ByVal size As Int16)        myCommand.Parameters.Add(name, type, size)        myCommand.Parameters.Item(myCommand.Parameters.Count - 1).SqlValue = value    End Sub    Public Sub executeSproc(ByRef dataset As Data.DataSet)        myConnection.Open()        myDataSet = New System.Data.DataSet()        myDataSet.CaseSensitive = True        dataAdapter = New System.Data.SqlClient.SqlDataAdapter()        dataAdapter.SelectCommand = myCommand        If Not dataset Is Nothing Then            dataAdapter.TableMappings.Add("asd", "UserInfo")            dataAdapter.Fill(myDataSet)            dataset = myDataSet            myConnection.Close()        Else            myCommand.ExecuteNonQuery()        End If    End SubSprocset ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[P_GetAbnormalFormSect0]     -- Add the parameters for the stored procedure here    @REFNO CHAR(11) = NULLASBEGIN    -- SET NOCOUNT ON added to prevent extra result sets from    -- interfering with SELECT statements.    SET NOCOUNT ON;    DECLARE    @ErrorCode int    SET @ErrorCode = 0        SELECT * FROM dbo.AFStatusInfo        WHERE dbo.AFStatusInfo.RefNo = @REFNO        IF(@@ROWCOUNT = 0)        BEGIN            SET @ErrorCode = -1        END        IF( @@ERROR <> 0 )        BEGIN            SET @ErrorCode = @@ERROR        END                RETURN @ErrorCodeEND-----------------------------------------------------------------------------------------------set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[P_CreateAbnormalFormSect0]     -- Parameters for the stored procedure    @Pending CHAR(80),    @CreateBy CHAR(80) ASBEGIN    -- SET NOCOUNT ON added to prevent extra result sets from    -- interfering with SELECT statements.    SET NOCOUNT ON;        -- @ErrorCode : 0    No Error    --                1    No Data (Ignore when no ResultSet required)    --                2    Parameter not complete    --                3    Conflict with existing data    -- Other error code please refer to @@ERROR code    DECLARE    @ErrorCode int    SET @ErrorCode = 0    DECLARE @Id VARCHAR(11)    DECLARE @Temp VARCHAR(4)    SET @Temp = CAST((SELECT COUNT(*) FROM [AFStatusInfo] WHERE RefNo like ('BS200607%'))AS CHAR(3))    SET @Temp = @Temp + 1000    SET @Id = 'BS'    SET @Id = @Id + CONVERT(CHAR(4),GETDATE(),20)    SET @Id = @Id + CONVERT(CHAR(2),GETDATE(),10)    SET @Id = @Id + SUBSTRING(@Temp,2,3)            INSERT INTO [AFStatusInfo]               (RefNo               ,FlowStatus               ,[Pending]               ,[CreateBy]               ,[DateCreated])            VALUES               (@Id               ,'New'               ,@Pending               ,@CreateBy               ,CONVERT(CHAR(8), GETDATE(), 3))    SELECT @Id AS "RefNo"        IF( @@ERROR <> 0 )        BEGIN            SET @ErrorCode = @@ERROR        END                RETURN @ErrorCode        OnError:            RETURN @ErrorCodeEND----------------------------------------------------------------------------------------------------------------

View 1 Replies View Related

Must Declare The Scalar Variable @recCount.

Aug 21, 2007

Hi,Can somone please tell me why I get the  error 'Must declare the scalar variable "@recCount".' when I execute this Store Procedure? ALTER PROCEDURE dbo.CountTEst AS   SET NOCOUNT ON    DECLARE @sql as nvarchar(100);    DECLARE @recCount as int SET @SQL = 'SELECT @recCount = COUNT(*)   FROM     Pool'   exec sp_executesql @SQL   RETURN @recCount  In this case I expect RETURN value = 4.I've tried various approaches, including adding an OUTPUT parameter to the exec statement, but just can't get the syntax correct. Thanks  

View 14 Replies View Related

Must Declare The Scalar Variable @company.

Feb 22, 2008

 I am not seeing the problem with this.  I have a button at the bottom of this form that does this...
protected void Button1_Click(object sender, EventArgs e)
{
SqlDataSource1.Insert();
}
And the SqlDataSource code...<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:calldbConnectionString2 %>"
 
ProviderName="<%$ ConnectionStrings:calldbConnectionString2.ProviderName %>"
InsertCommand="INSERT INTO IDcall(IDcompany, enteredBy, likelihood6mo, liklihood12mo, stamp, objective, dateOfCall, applications, specifics, developmentStage, results, nextStep, salesStrategy, potentialFirstYear, other, valueOfSale, alternateContacts, IDcontact1) VALUES (@company, @entered, @mo6, @mo12, @stamp, @objective, @dateOfCall, @applications, @specifics, @development, @results, @next, @strategy, @potential, @other, @value, @alternate, @IDcontact1)">
<InsertParameters><asp:ControlParameter ControlID="DropDownList_Company" Name="@company"
PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="TextBox_signature" Name="@entered"
PropertyName="Text" />
<asp:ControlParameter ControlID="DropDownList_6mo" Name="@mo6"
PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="DropDownList_12mo" Name="@mo12"
PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="TextBox_submittedOn" Name="@stamp"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_Purpose" Name="@objective"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_dateOfCall" Name="@dateOfCall"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_applications" Name="@applications"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_specifics" Name="@specifics"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_development" Name="@development"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_results" Name="@results"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_nextStep" Name="@next"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_strategy" Name="@strategy"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_potentialFirstYear" Name="@potential"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_other" Name="@other"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_Value" Name="@value"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_alternateContacts" Name="@alternate"
PropertyName="Text" />
<asp:ControlParameter ControlID="DropDownList_Contact" Name="IDcontact1"
PropertyName="SelectedValue" />
</InsertParameters>
</asp:SqlDataSource>
 
When I click the button I get Must declare the scalar variable "@company".  How is @company not declared?
And here's my stackStack Trace:



[SqlException (0x80131904): Must declare the scalar variable "@company".]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +925466
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800118
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1932
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) +1005
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) +149
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135
System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +404
System.Web.UI.WebControls.SqlDataSourceView.ExecuteInsert(IDictionary values) +447
System.Web.UI.WebControls.SqlDataSource.Insert() +13
Calls_CallNew.Button1_Click(Object sender, EventArgs e) in c:InetpubwwwrootCallsCallNew.aspx.cs:88
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) +1746



Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433

View 3 Replies View Related

AddWithValue : Must Declare The Scalar Variable @…

Apr 18, 2008

  I’m getting the error messages: Must declare the scalar variable "@CustomerName".Must declare the scalar variable "@CustomerEmail". When using the code:sqlcommand = "UPDATE Transactions SET CustomerName=@CustomerName WHERE transid='" + thetransid.ToString().Trim() + "' ";
sqlcommand += "UPDATE Transactions SET CustomerEmail=@CustomerEmail WHERE transid='" + thetransid.ToString().Trim() + "' ";
cmd.Parameters.Add("@CustomerName", CustomerNameTextBox.Text);
cmd.Parameters.Add("@CustomerEmail", CustomerEmailTextBox.Text);
cmd = new SqlCommand(sqlcommand, conn);
cmd.ExecuteNonQuery();
conn.Close();

View 8 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

Contador - Must Declare Scalar Variable

Jun 5, 2015

I have the following code:

begin
SET @CONTADOR = @CONTADOR + 1;
set @strQuery='';
set @intYDS = 0;
SET @strQUERY = 'SELECT @intYDS = ISNULL(COALESCE(YDS,0),0)'

[Code] ....

But when I run returned me the following error: Must declared the scalar variable.

View 3 Replies View Related

Declare The Scalar Variable @average

Dec 8, 2007

Hi all,

In my SQL Server Management Studio Express, I have a Database "testDb" that has a dbo.Inventory with a column "quantity". I executed the following sql script:

USE testDb

GO

CREATE FUNCTION AverageQuantity(@funcType varchar(20))

RETURNS numeric AS

BEGIN

DECLARE @average numeric

SELECT @average = AVG(quantity) FROM Inventory WHERE type=@funcType

RETURN @averge

END

GO


I got the following error message:

Msg 137, Level 15, State 2, Procedure AverageQuantity, Line 6

Must declare the scalar variable "@averge".

I think it was declared already in the BEGIN - END block. Please tell me why I got this error and advise me how to fix this problem.

Thanks in advance,
Scott Chang

View 1 Replies View Related

Must Declare The Scalar Variable In Sql 2005

Dec 18, 2007

Code Block
CREATE FUNCTION myFUNCTION (@YearMounth varchar(5) =[84/08] )
RETURNS table AS
return (
SELECT dbo.T1.PersonelNo, dbo.T1.OfficeCode, dbo.T1.jobCode
FROM dbo.T1
WHERE (LEFT(dbo.T1.StartDate, 5) <= @yearMounth) AND
(LEFT(dbo.T1.EndDate, 5) >=@yearMounth)
)





this FUNCTION run in SQL Server 2000 But Not Run In SQL Server 2005 And This Error




Code Block
Msg 137, Level 15, State 2, Procedure myFUNCTION, Line 6
Must declare the scalar variable "@yearMounth".

View 4 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

Asp.net 2.0 Registration: Must Declare The Scalar Variable '@BillingAddress'

Aug 29, 2007

 I started to receive this error as soon I added the line
"DataSource.SelectParameters.Clear()" Otherwise I was getting"variable
userid already decalred". I'm pasting the code below, please let me
know what I'm doing wrong. I think it is a minor problem. 1 <%@ Page Language="VB" AutoEventWireup="false" CodeFile="Registration.aspx.vb" Inherits="Members_Registration" Trace="true" %>
2
3 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
4
5 <html xmlns="http://www.w3.org/1999/xhtml" >
6 <head runat="server">
7 <title>Untitled Page</title>
8 </head>
9 <body>
10 <form id="form1" runat="server">
11 <div>
12 <asp:CreateUserWizard ID="CreateUserWizard1" runat="server" BackColor="#FFFBD6" BorderColor="#FFDFAD" BorderStyle="Solid" BorderWidth="1px" Font-Names="Verdana" Font-Size="0.8em" OnCreatedUser="CreateUserWizard1_CreatedUser">
13 <WizardSteps>
14 <asp:WizardStep ID="CreateUserWizardStep0" runat="server">
15 <table>
16 <tr>
17 <th>Billing Information</th>
18 </tr>
19 <tr>
20 <td>Billing Address:</td>
21 <td>
22 <asp:TextBox runat="server" ID="BillingAddress" MaxLength="50" />
23 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator1" ControlToValidate="BillingAddress"
24 ErrorMessage="Billing Address is required." />
25 </td>
26 </tr>
27 <tr>
28 <td>Billing City:</td>
29 <td>
30 <asp:TextBox runat="server" ID="BillingCity" MaxLength="50" Columns="15" />
31 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator2" ControlToValidate="BillingCity"
32 ErrorMessage="Billing City is required." />
33 </td>
34 </tr>
35 <tr>
36 <td>Billing State:</td>
37 <td>
38 <asp:TextBox runat="server" ID="BillingState" MaxLength="25" Columns="10" />
39 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator3" ControlToValidate="BillingState"
40 ErrorMessage="Billing State is required." />
41 </td>
42 </tr>
43 <tr>
44 <td>Billing Zip:</td>
45 <td>
46 <asp:TextBox runat="server" ID="BillingZip" MaxLength="10" Columns="10" />
47 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator4" ControlToValidate="BillingZip"
48 ErrorMessage="Billing Zip is required." />
49 </td>
50 </tr>
51 </table>
52 </asp:WizardStep>
53 <asp:WizardStep ID="CreateUserWizardStep1" runat="server">
54 <table>
55 <tr>
56 <th>Shipping Information</th>
57 </tr>
58 <tr>
59 <td>Shipping Address:</td>
60 <td>
61 <asp:TextBox runat="server" ID="ShippingAddress" MaxLength="50" />
62 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator5" ControlToValidate="ShippingAddress"
63 ErrorMessage="Shipping Address is required." />
64 </td>
65 </tr>
66 <tr>
67 <td>Shipping City:</td>
68 <td>
69 <asp:TextBox runat="server" ID="ShippingCity" MaxLength="50" Columns="15" />
70 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator6" ControlToValidate="ShippingCity"
71 ErrorMessage="Shipping City is required." />
72 </td>
73 </tr>
74 <tr>
75 <td>Shipping State:</td>
76 <td>
77 <asp:TextBox runat="server" ID="ShippingState" MaxLength="25" Columns="10" />
78 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator7" ControlToValidate="ShippingState"
79 ErrorMessage="Shipping State is required." />
80 </td>
81 </tr>
82 <tr>
83 <td>Shipping Zip:</td>
84 <td>
85 <asp:TextBox runat="server" ID="ShippingZip" MaxLength="10" Columns="10" />
86 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator8" ControlToValidate="ShippingZip"
87 ErrorMessage="Shipping Zip is required." />
88 </td>
89 </tr>
90 </table>
91 </asp:WizardStep>
92 <asp:CreateUserWizardStep ID="CreateUserWizardStep2" runat="server">
93 <ContentTemplate>
94 <table>
95 <tr>
96 <th>User Information</th>
97 </tr>
98 <tr>
99 <td>Username:</td>
100 <td>
101 <asp:TextBox runat="server" ID="UserName" />
102 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator9" ControlToValidate="UserName"
103 ErrorMessage="Username is required." />
104 </td>
105 </tr>
106 <tr>
107 <td>Password:</td>
108 <td>
109 <asp:TextBox runat="server" ID="Password" TextMode="Password" />
110 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator10" ControlToValidate="Password"
111 ErrorMessage="Password is required." />
112 </td>
113 </tr>
114 <tr>
115 <td>Confirm Password:</td>
116 <td>
117 <asp:TextBox runat="server" ID="ConfirmPassword" TextMode="Password" />
118 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator13" ControlToValidate="ConfirmPassword"
119 ErrorMessage="Confirm Password is required." />
120 </td>
121 </tr>
122 <tr>
123 <td>Email:</td>
124 <td>
125 <asp:TextBox runat="server" ID="Email" />
126 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator11" ControlToValidate="Email"
127 ErrorMessage="Email is required." />
128 </td>
129 </tr>
130 <tr>
131 <td>Question:</td>
132 <td>
133 <asp:TextBox runat="server" ID="Question" />
134 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator12" ControlToValidate="Question"
135 ErrorMessage="Question is required." />
136 </td>
137 </tr>
138 <tr>
139 <td>Answer:</td>
140 <td>
141 <asp:TextBox runat="server" ID="Answer" />
142 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator14" ControlToValidate="Answer"
143 ErrorMessage="Answer is required." />
144 </td>
145 </tr>
146 <tr>
147 <td colspan="2">
148 <asp:CompareValidator ID="PasswordCompare" runat="server" ControlToCompare="Password"
149 ControlToValidate="ConfirmPassword" Display="Dynamic" ErrorMessage="The Password and Confirmation Password must match."></asp:CompareValidator>
150 </td>
151 </tr>
152 <tr>
153 <td colspan="2">
154 <asp:Literal ID="ErrorMessage" runat="server" EnableViewState="False"></asp:Literal>
155 </td>
156 </tr>
157 </table>
158
159
160 <asp:SqlDataSource ID="InsertExtraInfo" runat="server"
161 ConnectionString="<%$ ConnectionStrings:Member %>"
162 InsertCommand="INSERT INTO UserAddress (Userid, BillingAddress, BillingCity,BillingState,BillingZip, ShippingAddress,ShippingCity,ShippingState,ShippingZip) VALUES (@userid, @BillingAddress, @BillingCity, @BillingState, @BillingZip, @ShippingAddress, @ShippingCity, @ShippingState, @ShippingZip)"
163 SelectCommand="SELECT UserAddress.* FROM UserAddress">
164 <InsertParameters>
165 <asp:ControlParameter Name="BillingAddress" Type="String" ControlID="BillingAddress" PropertyName="Text" />
166 <asp:ControlParameter Name="BillingCity" Type="String" ControlID="BillingCity" PropertyName="Text" />
167 <asp:ControlParameter Name="BillingState" Type="String" ControlID="BillingState" PropertyName="Text" />
168 <asp:ControlParameter Name="BillingZip" Type="String" ControlID="BillingZip" PropertyName="Text" />
169 <asp:ControlParameter Name="ShippingAddress" Type="String" ControlID="ShippingAddress" PropertyName="Text" />
170 <asp:ControlParameter Name="ShippingCity" Type="String" ControlID="ShippingCity" PropertyName="Text" />
171 <asp:ControlParameter Name="ShippingState" Type="String" ControlID="ShippingState" PropertyName="Text" />
172 <asp:ControlParameter Name="ShippingZip" Type="String" ControlID="ShippingZip" PropertyName="Text" />
173 </InsertParameters>
174 </asp:SqlDataSource>
175
176 </ContentTemplate>
177 </asp:CreateUserWizardStep>
178 <asp:CompleteWizardStep ID="CompleteWizardStep1" runat="server">
179 </asp:CompleteWizardStep>
180 </WizardSteps>
181 <NavigationButtonStyle BackColor="White" BorderColor="#CC9966" BorderStyle="Solid"
182 BorderWidth="1px" Font-Names="Verdana" ForeColor="#990000" />
183 <HeaderStyle BackColor="#FFCC66" BorderColor="#FFFBD6" BorderStyle="Solid" BorderWidth="2px"
184 Font-Bold="True" Font-Size="0.9em" ForeColor="#333333" HorizontalAlign="Center" />
185 <CreateUserButtonStyle BackColor="White" BorderColor="#CC9966" BorderStyle="Solid"
186 BorderWidth="1px" Font-Names="Verdana" ForeColor="#990000" />
187 <ContinueButtonStyle BackColor="White" BorderColor="#CC9966" BorderStyle="Solid"
188 BorderWidth="1px" Font-Names="Verdana" ForeColor="#990000" />
189 <SideBarStyle BackColor="#990000" Font-Size="0.9em" VerticalAlign="Top" />
190 <TitleTextStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
191 <SideBarButtonStyle ForeColor="White" />
192 </asp:CreateUserWizard>
193
194 </div>
195
196 </form>
197 </body>
198 </html>
199
200  Partial Class Members_Registration
Inherits System.Web.UI.Page

Protected Sub CreateUserWizard1_CreatedUser(ByVal sender As Object, ByVal e As System.EventArgs) Handles CreateUserWizard1.CreatedUser
Dim UserNameTextBox As TextBox = DirectCast(CreateUserWizardStep2.ContentTemplateContainer.FindControl("UserName"), TextBox)
Dim DataSource As SqlDataSource = DirectCast(CreateUserWizardStep2.ContentTemplateContainer.FindControl("InsertExtraInfo"), SqlDataSource)

Dim User As MembershipUser
User = Membership.GetUser(UserNameTextBox.Text)
Dim UserGUID

UserGUID = User.ProviderUserKey

Trace.Write("userid", UserGUID.ToString)
DataSource.SelectParameters.Clear()
' DataSource.InsertParameters.Clear()
DataSource.InsertParameters.Add("UserId", UserGUID.ToString)

DataSource.Insert()

End Sub
End Class 
  

View 1 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

Stores Procedure - Must Declare Scalar Variable

Apr 7, 2015

I am creating a SP below and it keep saying must declare scalar variable @comments,I have already declared @comments varchar (255)

error---> (Msg 137, Level 15, State 2, Procedure CheckPatientComments, Line 26
Must declare the scalar variable "@commnets".
)

quote:
CREATE PROCEDURE [dbo].[CheckPatientComments] (@enc_id varchar(36) OUTPUT, @data_ind CHAR(1) OUTPUT)
AS
BEGIN
DECLARE
@comments varchar(255)

[code]....

View 2 Replies View Related

T-SQL (SS2K8) :: Proper Use Of CTE In Scalar Valued Function?

Dec 1, 2014

I have troubles with this scalar-valued UDF:

I get the error:

Select statements included within a function cannot return data to a client.

Is this a proper way to include a CTE in a function?

USE [DB1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[_Pink_FN_StartingDatePLGeographical](@StartingDate AS DATETIME) RETURNS NVARCHAR(20)

[code].....

View 4 Replies View Related

Return NULL From A CLR Scalar-Valued Function

Jul 11, 2007

Hi,



I have an assembly that contains the following function:



Public Class Lookup



<SqlFunction()> _

Public Shared Function MyTest() As Integer

Return System.Data.SqlTypes.SqlInt64.Null

End Function



End Class



Then in SSMS:



CREATE ASSEMBLY IRS_MyTest

FROM '\machine empmyAssembly.dll'

GO

CREATE FUNCTION dbo.MyTest() RETURNS INT

AS EXTERNAL NAME IRS_MyTest.[MyClass].MyTest

GO



when I run:



SELECT dbo.MyTest()



the following is returned:



Msg 6522, Level 16, State 2, Line 1

A .NET Framework error occurred during execution of user defined routine or aggregate 'MyTest':

System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.

System.Data.SqlTypes.SqlNullValueException:

at System.Data.SqlTypes.SqlInt64.get_Value()

at System.Data.SqlTypes.SqlInt64.op_Explicit(SqlInt64 x)

at Informed.DCLG.IRS.SQL.IncidentTransform.Lookup.MyTest()





Can anyone please advise on how to return back a null value. Currently, my only other option is to return nothing (actually returns 0) and then wrap this up to convert the value to null - not ideal.







Thanks,



Jan.

View 1 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

SQL Reporting Services 2005: Must Declare Scalar Variable

Mar 4, 2008

I have a report that calls a stored procedure that builds a temp table and then runs a select that joins on that temp table. The only parameter I have is @Name, an optional parameter that can be filled or NULL.

I keep getting the error "An error has occurred during local report processing. An error has occurred during report processing. Query execution failed for data set. Must declare the scalar variable "@Name".

I have checked to make sure this parameter is connected to the dataset by looking at the parameter tab. I've searched on posts but haven't seen an answer. Does anyone have any idea what this could be about and how to fix it?

View 20 Replies View Related

Problem Passing A Variable Into A Table-valued Function

Sep 26, 2007

Hi,

i am encountering a problem in a stored procedure when a pass a variable value into a table-valued function. The table-valued function is named getCurrentDriver and has 1 attribute: car-ID.

The syntax is as follows:

select car.id, car.licenceNumber, car.brand, car.model,
(select driverName from getCurrentDriver(car.id)) as driverName
from car

When I try to compile I get following error on the line of the function:
Incorrect syntax near '.'

The database version is SQL Server 2000 SP3.

What am I doing wrong? Is there a workaround for this error?

View 10 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

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

SQL XML :: Scalar Valued Function Slow Querying XML Passed As Parameter

May 28, 2015

I have a procedure that calls a SVF to convert an xmldocument. The ultimate purpose is to update the xml in a column in a multi-million row table. The xml is stored as varchar(MAX), it was supposed to carry any type of text, initially at least.

My question is: why is the xml-parsing performed inside the function much slower when i pass the xmldocument as type xml than when it is passed as varchar(MAX) and the CAST to xml is within the function? Does processing the xml input parameter in SlowFunction involve expensive crossing of some context border?

The two versions of the SVF (they return the rowcount in this simplified example):

CREATE FUNCTION [dbo].[FastFunction]
(
@inDetaljerText varchar(MAX)
)
RETURNS int

[Code] ....

The two versions of the SP

CREATE PROCEDURE [dbo].[FastProcedure]
AS
BEGIN
SET NOCOUNT ON;
select
dbo.FastFunction(al.Detaljer)

[Code] ....

View 2 Replies View Related







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