The ALLMEMBERS Function Expects A Hierarchy Expression For The Argument

Argument Data Type Text Is Invalid For Argument 1 Of Replace Function.
 

Hi There,
 
Could someone please tell me why I am getting the above error on this code:
 
select  (replace
            (replace
                  (replace
                        (replace (serviceType, 'null', ' ')
                        ,  '<values><value>', ' ')
                  ,  '</value><value>', ',')
            , '</value></values>', ' '))
from credit
 
 
serviceType (text,null)
 
Thanks,
Rhonda
 

View Replies !

---

Procedure Or Function Has Too Many Argument Specified.
 Hey, friends, i have a problem on my bank transaction page now: Procedure or function has too many argument specified.people can log in by their usernames, 1 username can have many accounts, there are 2 account types:'c', 's'. after people login, they are only allowed to withdraw money from their own account: here is some of my codes:  1 else if (DropDownList3.SelectedItem.Text == "withdraw")2 {3 SqlConnection sqlCon = new SqlConnection("Data Source=bandicoot.cs.rmit.edu.au;Initial Catalog=shuli;User ID=test;Password=test");4 SqlCommand cmd = new SqlCommand("usp_withdraw", sqlCon);5 string spResult = "";6 cmd.CommandType = CommandType.StoredProcedure;7 cmd.Parameters.Add("@username", SqlDbType.NVarChar).Value = Session["username"].ToString();8 cmd.Parameters.Add("@AccountFrom", SqlDbType.VarChar).Value = TextBox3.Text;9 cmd.Parameters.Add("@Amount", SqlDbType.Decimal).Value = TextBox5.Text;10 cmd.CommandType = CommandType.StoredProcedure;11 sqlCon.Open();12 spResult = cmd.ExecuteScalar().ToString();13 if (string.Compare(spResult, "Execute successfully") == 0)14 {15 SqlDataSource with = new SqlDataSource();16 with.ConnectionString = ConfigurationManager.ConnectionStrings["shuliConnectionString"].ToString();17 18 with.InsertCommand = "insert into Transactions(TransactionType,AccountNumber,DestAccount,Amount,Comment,ModifyDate) values ('W','" + TextBox3.Text + "','" + TextBox3.Text + "','" + TextBox5.Text + "','" + TextBox2.Text + "','" + DateTime.Now.ToLocalTime() + "')";19 int inertRowNum = with.Insert();20 Server.Transfer("~/deposit_withdraw_confirm.aspx");21 }22 else23 {24 Server.Transfer("~/deposit_withdraw_error.aspx");25 }26
  here is my store procedure set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo-- =============================================-- Author:        <Author,,Name>-- Create date: <Create Date,,>-- Description:    <Description,,>-- =============================================ALTER Procedure [dbo].[usp_withdraw](    @AccountFrom Varchar(20),    @Amount Decimal(10,2))AsDECLARE @ReturnMsg AS VARCHAR(20)DECLARE @balance AS Decimal(10,2)DECLARE @username AS nvarchar(50)SELECT @balance = balanceFROM Account,LoginWHERE   Account.CustomerID=Login.CustomerID AND Login.UserID= @username and AccountNumber = @AccountFromIF (    SELECT AccountType     FROM Account, Login    Where  Account.CustomerID=Login.CustomerID AND Login.UserID= @username and AccountNumber = @AccountFrom    ) ='c'BEGIN     BEGIN TRAN        IF (@balance - @Amount) > 199                                    BEGIN            UPDATE Account            SET balance= balance-@Amount            WHERE AccountNumber=@AccountFrom                  And (balance - @Amount) > 199                                            SET @ReturnMsg = 'Execute successfully'            COMMIT TRAN        END        ELSE        BEGIN            SET @ReturnMsg = 'Execute with error'            ROLLBACK TRAN                  END    SELECT @ReturnMsg     ENDIF (    SELECT AccountType     FROM Account    Where AccountNumber = @AccountFrom    ) ='s'BEGIN     BEGIN TRAN        IF (@balance - @Amount) > 0                                    BEGIN            UPDATE Account            SET balance= balance-@Amount            WHERE AccountNumber=@AccountFrom                  And (balance - @Amount) > 0                                            SET @ReturnMsg = 'Execute successfully'            COMMIT TRAN        END        ELSE        BEGIN            SET @ReturnMsg = 'Execute with error'            ROLLBACK TRAN                  END    SELECT @ReturnMsg     ENDI think the problem is in Line 7cmd.Parameters.Add("@username", SqlDbType.VarChar).Value = Session["username"].ToString();this is to get the current login user's uername.  

View Replies !

---

TSQL - Using More Than One Argument In LIKE Function
Hi guys,
 
I am using the LIKE function combined with a CASE WHEN to change a long list of words, but the list is too long...
Is there any posibility to insert more than one argument into one like function...?
Any other good ideas?
Below an example of the code I am using..
 
Thanks in advance,
Aldo.
 



Code Snippet
Case
WHEN JurnalTrans.DESCRIPTION LIKE '%myArgument01%' THEN 'Result'
WHEN JurnalTrans.DESCRIPTION LIKE '%myArgument02%' THEN 'Result'
WHEN JurnalTrans.DESCRIPTION LIKE '%myArgument03%' THEN 'Result'
WHEN JurnalTrans.DESCRIPTION LIKE '%myArgument04%' THEN 'Result'
ELSE ''
END AS 'Result'

 
 

View Replies !

---

Error In Function Argument
I have a table with over 11,000 records and I need to do a find and replace using SET and Where conditions. Basically I have one column in the table called RealAudioLink. It contains entries like: wkdy20070416-a.rm and wkdy20070416-b.rm and conv20070416.rm.

I need the select statement to find all wkdy entries and replace those characters with Weekday. I also need it to find all dashes and small a's and b's and replace with null or nothing. Then I need it to insert a capital letter A or B in the
wkdy20070416-a.rm filename so that when it's all said and done that entry would read:

WeekdayA20070416.rm
WeekdayB20070416.rm
Conversation20070416.rm

Here is the code I am working with. It needs help. I'm close but I'm not knowledgeable with using SET or with removing dashes and inserting capital letters all in the same select statement.



Code Snippet

UPDATE T_Programs_TestCopy
(SET  RealAudioLink = REPLACE(RealAudioLink, '-a', '')
AND
(SET RealAudioLink = REPLACE(RealAudioLink, 'wkdy', 'WeekdayA')
WHERE (RealAudioLink LIKE 'wkdy%'))

I've never done anything like this before so I would be very appreciative of any assistance with the select statement. I am reading up on it but it would be great to get another perspective from a more experienced sql developer.

Thanks

View Replies !

---

SQL Problem (The MAX Function Requires 1 Argument(s).)
Hey Guys,
I'm having problems with my SQL statement and Im wondering if anyone could help?  My code is below.
The Label6 is filled up using a previous SQL Statement, and when Ive had a fiddle around it is using the KnowledgeID It works in reading in the date, it just doesnt appear to want to work for the second statement as I am getting the error shown in the subject.
Thanks in advance =)
C# Codeprotected void Button2_Click(object sender, EventArgs e)
{KnowledgeID = Convert.ToInt32(Label5.Text);
string path = Server.MapPath(" ") + "\X_Drive\";FileLocation = path + Convert.ToString(FileUpload1.FileName);SqlConnection myConnection = new SqlConnection(myConnectionString);
 SqlCommand myCommand2 = new SqlCommand("select * from Knowledge WHERE KnowledgeID = '" + KnowledgeID + "'", myConnection);
myConnection.Open();
SqlDataReader myReader = myCommand2.ExecuteReader();if (myReader.HasRows)
{while (myReader.Read())
{Add = myReader["DateAdded"].ToString();
}
myReader.Close();
}
else
{
myConnection.Close();
}
 SqlTransaction trans = myConnection.BeginTransaction();
{
try
{
 SqlCommand myCommand3 = new SqlCommand("Select ISNULL(MAX(Version,0)+1 FROM Archive WHERE KnowledgeID = "+KnowledgeID+"",myConnection);
myCommand3.Transaction=trans;int nextVersion =(int)myCommand3.ExecuteScalar();if (File != null)
{myCommand3 = new SqlCommand("INSERT INTO Archive (FixName, Description, Location, DateAdded, DateArchived, Version, KnowledgeID) SET (@FixName,@Description,@File,@Add,@AddDate,@SAPPS,@Version,'" + KnowledgeID + "')", myConnection);
}
else
{myCommand3 = new SqlCommand("INSERT INTO Archive (FixName, Description, DateAdded, DateArchived, Version, KnowledgeID) SET (@FixName,@Description,@Add,@AddDate,@SAPPS,@Version,'" + KnowledgeID + "')", myConnection);
}myCommand3.Parameters.AddWithValue("@FixName", TextBox1.Text);
myCommand3.Parameters.AddWithValue("@Description",TextBox2.Text);myCommand3.Parameters.AddWithValue("@File",FileLocation);
myCommand3.Parameters.AddWithValue("@Add",Add);myCommand3.Parameters.AddWithValue("@AddDate",AddDate);myCommand3.Parameters.AddWithValue("@Versions",nextVersion);
myCommand3.ExecuteNonQuery();
trans.Commit();
}catch (Exception ex)
{
//TextBox2.Text = ex.Message;
trans.Rollback();
myConnection.Close();
}
//}

View Replies !

---

Initial Visibility Expression Stays In Effect On A Recursive Hierarchy/drilldown App
does anyone know why my initial visibility expression (=Level()>1) stays in effect after generating my report?  The idea was to display only a couple of levels in the hierarchy at first, so that the user could more easily decide what he wants to expand.  But expand doesnt do anything after initial rendering.
 
The only way I could get this kind of report to work (show hierarchy and expand/collapse) was to live with initial visibility = "visible", but unfortunately, that setting shows all levels right away, not a very user friendly thing.
 
I am running Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)   Feb  9 2007 22:47:07   Copyright (c) 1988-2005 Microsoft Corporation  Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

View Replies !

---

Can We Use A Variable As An Argument In A User Defined Function
Hi,
I have written a user defined function to convert a date from an OLD time zone to NEW time Zone. The three parameters are (date, OLDTz,NEWTZ).

=====================================
declare
@v_tz varchar(10) ,
@v_da integer,
@v_ss varchar(100)
select @v_da=7
select @v_tz='GMT'
select @v_ss='soumilpatel'
select substring('soumilpatel',2,3)
select substring(@v_ss,2,3)
select getdate(), dateadd(day, 7, getdate()), dateadd(day, @v_da, getdate())
select top 2 timestampwhenset, dbo.tc(timestampwhenset,'GMT','PST') from s_monitorevents
select top 2 timestampwhenset, dbo.tc(timestampwhenset,'GMT', @v_tz ) from s_monitorevents
go
=====================================

In real life the value of @v_tz is coming from a table. How do I go about resolving the value of the variable before I run the function. I was able to use the variable substitution successfully in a numeric function as well as in a string/character function.

Your input is very much appreciated.
-soumil

View Replies !

---

Procedure Or Function 'selectFromView' Expects Parameter '@Year', Which Was Not Supplied. ???
 hay all, i'm trying to pass parameters to a stored procedure ..and i keep getting this error "Procedure or function 'selectFromView' expects parameter '@Year', which was not supplied." this is the procedure implementation : ALTER PROCEDURE dbo.selectFromView

@Year as varchar(10),
@Country as varchar(10),
@Family as varchar(10),
@Manu as varchar(10),
@Status as varchar(10),
@Type as varchar(10),
@Operator as varchar(10)


AS
SELECT * FROM ViewofAll
WHERE
ProductionYear = @Year and
CountryName = @Country and
Family = @Family and
Manufacturer = @Manu and
Status = @Status and
Type = @Type and
OperatorName = @Operator

RETURN and below how i call the procedure :   Dim connection As SqlConnection
connection = New SqlConnection()
connection.ConnectionString = "Data Source=BLACKIRIS3SQLEXPRESS;Initial Catalog=Aircrafts;Integrated Security=True"
connection.Open()
Dim command As SqlCommand
command = New SqlCommand("selectFromView", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add(New SqlParameter("@Year", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input
command.Parameters.Add(New SqlParameter("@Country", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input
command.Parameters.Add(New SqlParameter("@Family", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input
command.Parameters.Add(New SqlParameter("@Manu", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input 'Stored prcedure parameters
command.Parameters.Add(New SqlParameter("@Status", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input
command.Parameters.Add(New SqlParameter("@Type", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input
command.Parameters.Add(New SqlParameter("@Operator", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input
command.Parameters.Item("@Year").Value = myArray(0)
command.Parameters.Item("@Country").Value = myArray(1)
command.Parameters.Item("@Family").Value = myArray(2)
command.Parameters.Item("@Manu").Value = myArray(3)
command.Parameters.Item("@Status").Value = myArray(4)
command.Parameters.Item("@Type").Value = myArray(5)
command.Parameters.Item("@Operator").Value = myArray(5)
DSGrid.SelectCommand = command.CommandText With DSGrid
.DataBind()
End With  i'm not really familiar with stored procedures ...any one can help me plz ?  thanx in advance .  

View Replies !

---

Procedure Or Function 'SubscribeToNewsletters' Expects Parameter '@emailAddress', Which Was Not Supplied.
Hi i have been trying to insert some values selected in a checkbox list into the database, however it is giving me the following error "Procedure or Function 'SubscribeToNewsletters' expects parameter '@emailAddress', which was not supplied. "public void Page_Load(object sender, EventArgs e) -- on page load the checkbox list is populated through stored procedure (stream_NewsletterTypes)
{
try
{SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);
SqlCommand command = new SqlCommand("stream_NewsletterTypes", conn); command.CommandType = CommandType.StoredProcedure;
command.Connection.Open();SqlDataReader datareader = command.ExecuteReader();
AlertList.DataSource = datareader;AlertList.DataTextField = "newsletterName";
AlertList.DataBind();
command.Connection.Close();
command.Connection.Dispose();
}catch (Exception ex)
{throw new Exception("Exception. " + ex.Message);
}
}
 protected void Btn_Subscribe_Click(object sender, EventArgs e) when the button is clicked, the values should be sent to the database
{SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);
SqlCommand command = new SqlCommand("stream_NewsletterTypes", conn);command.CommandType = CommandType.StoredProcedure;for (int i = 0; i < AlertList.Items.Count; i++)
{if (AlertList.Items[i].Selected)
{
command.Parameters.AddWithValue("firstName", txtFirstName.Text);command.Parameters.AddWithValue("surname", txtSurname.Text);
command.Parameters.AddWithValue("emailAddress", txtEmail.Text);command.Parameters.AddWithValue("newsletterID", AlertList.Items[i].Value);
}
}
try
{
conn.Open();
command.ExecuteNonQuery();
}catch (Exception ex)
{throw new Exception("Exception adding account. " + ex.Message);
}
finally
{
conn.Close();
}
}
 
And my stored procedure;ALTER PROCEDURE [dbo].[SubscribeToNewsletters]
@emailAddress VARCHAR(250),
@firstName VARCHAR(250),
@surname VARCHAR(250),
@newsletterID INT,@userID INT OUTPUT
AS
INSERT INTO ThinkUsers (emailAddress, firstName, surname)
VALUES
(@emailAddress, @firstName, @surname)
Select @userID = @@Identity -- this is the ID created in the TheUserTable
-- when you make that first insert, the database
-- will generate the next ID value in that table
INSERT INTO SubscribedNewsletters (userID, newsletterID)
VALUES
(@userID, @newsletterID)

View Replies !

---

Procedure Or Function 'retrieve_product' Expects Parameter '@productPrice', Which Was Not Supplied.
Hi i am getting this error whenever i try to execute a stored procedure, below is my stored procedure;
 ALTER PROCEDURE [dbo].[retrieve_product]
-- Add the parameters for the stored procedure here
@productPrice decimal (10,2),
@productInfoURL varchar (255),
@categoryName varchar (255),@companyName varchar (255),
@subCategoryName varchar (255),
@companyWebsiteURL varchar (255)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT Categories.categoryName, SubCategories.subCategoryName,Companies.companyName, Products.productPrice,
Products.productInfoURL FROM Categories
INNER JOIN Products ON Categories.categoryID = Products.categoryID
INNER JOIN Companies ON Products.companyID = Companies.companyID
INNER JOIN SubCategories ON Categories.categoryID = SubCategories.categoryID AND Products.subcategoryID = SubCategories.subCategoryID
END
 

View Replies !

---

Procedure Or Function 'syl_EmailQueueInsert' Expects Parameter '@EmailCC', Which Was Not Supplied.
We have a email table in the database (SQL Server 2005). When we try to execute the Insert Stored Proc in the database to add a record and leave the EmailCC and EmailBCC fields blank we are getting the following error: Msg 201, Level 16, State 4, Procedure syl_EmailQueueInsert, Line 0Procedure or Function 'syl_EmailQueueInsert' expects parameter '@EmailCC', which was not supplied. The EmailCC and EmailBCC are set to allow nulls. Shouldn’t the system automatically insert nulls into those colums if no value is supplied??? What am I doing wrong? Newbie

View Replies !

---

Procedure Or Function 'insertProjectDetails' Expects Parameter '@ProjectTitle', Which Was Not Supplied.?
Running [dbo].[insertProjectDetails] ( @ProjectTitle = <DEFAULT>, @ProjectPeriod = <DEFAULT>, @Client = <DEFAULT>, @Position = <DEFAULT>, @ProjectDescription = <DEFAULT>, @Responsiblities = <DEFAULT>, @OtherInformation = <DEFAULT> ).

Procedure or Function 'insertProjectDetails' expects parameter '@ProjectTitle', which was not supplied.

No rows affected.

(0 row(s) returned)

@RETURN_VALUE =

Finished running [dbo].[insertProjectDetails].

Can any body send solution for this error?

 

View Replies !

---

I Want A Function Like IfNull Function To Use In Expression Builder
Hi,

I wonder if there a function that i can use in the expression builder that return a value (e.g o)  if the input value is null ( Like ifnull(colum1,0)  )

 

i hope to have the answer because i need it so much.

 

Maylo

View Replies !

---

Receive &&"Procedure Or Function Expects Parameter...&&" Error Unexpectedly
Maybe I am missing something completely obvious but as far as I can tell I aren't!
 
I am trying to simply pass a parameter to a stored proc to return results. Easy? So I thought so too...
 
This is my stored procedure below (in it's ALTER PROCEDURE form):
 

ALTER PROCEDURE [dbo].[spd_Tester]

-- Add the parameters for the stored procedure here

@Check int

AS

BEGIN

SELECT * FROM tblPerson

WHERE PersonID = @Check

END

 
Now I know this is a SQL Server forum but I figure anyone who reads this probably does some application programming as well, so here is the code that calls this.  It's in VBA using Access 2003.  I figure the error is being returned from SQL Server Express so that's why I have come to this forum instead of an Access forum! I could be wrong though...
 
Here is the code that calls this:
 
    Dim rs As ADODB.Recordset
    Dim cmd As ADODB.Command
   
    Set cmd = New ADODB.Command
   
    'Run sql command
    With cmd
        .ActiveConnection = CurrentProject.Connection

        .CommandText = "dbo.spd_Tester"
        .Parameters.Append .CreateParameter("@Check", adInteger, adParamInput, , Me.txtFirstName)

        
        'Execute, return recordset
        Set rs = .Execute
       
    End With
 
Now I figure this should work.  However, I get the error "Procedure or function 'spd_Tester' expects parameter '@Check', which was not supplied." when I execute the procedure with the number 1 in the txtFirstName text box.
 
Can someone tell me what I am doing wrong?? The tblPerson table has data in it, if I run the select from a query window, specifying a value for @Check, it works just fine.
 
Thanks in advance!

View Replies !

---

Procedure Or Function 'stored Procedure Name' Expects Parameter Which Was Not Supplied
Has anyone encountered this before?
Procedure or Function 'stored procedure name' expects parameter '@parameter', which was not supplied.
It seems that my code is not passing the parameter to the stored procedure.
When I click this hyperlink:
<asp:HyperLink
ID="HyperLink1"
Runat="server"
NavigateUrl='<%# "../Division.aspx?CountryID=" + Eval("CountryID")%>'
Text='<%# Eval("Name") %>'
ToolTip='<%# Eval("Description") %>'
CssClass='<%# Eval("CountryID").ToString() == Request.QueryString["CountryID"] ? "CountrySelected" : "CountryUnselected" %>'>
</asp:HyperLink>
it is suppose to get the country name and description, based on the country id.
I am passing the country id like this.
protected void Page_Load(object sender, EventArgs e)
{
PopulateControls();
}
private void PopulateControls()
{
string countryId = Request.QueryString["CountryID"];
if (countryId != null)
{
CountryDetails cd = DivisionAccess.GetCountryDetails(countryId);
divisionNameLabel.Text = cd.Name;
divisionDescriptionLabel.Text = cd.Description;
}
}
To my app code like this:
public struct CountryDetails
{
public string Name;
public string Description;
}
public static class DivisionAccess
{
static DivisionAccess()
public static DataTable GetCountry()
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetCountry";
return GenericDataAccess.ExecuteSelectCommand(comm);
}
public static CountryDetails GetCountryDetails(string cId)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetCountryDetails";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@CountryID";
param.Value = 2;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
CountryDetails details = new CountryDetails();
if (table.Rows.Count > 0)
{
details.Name = table.Rows[0]["Name"].ToString();
details.Description = table.Rows[0]["Description"].ToString();
}
return details;
}
 
As you can see I have two stored procedures I am calling, one does not have a parameter and the other does. The getcountry stored procedure returns the list of countries in a menu that I can click to see the details of that country. That is where my problem is when I click the country name I get
Procedure or Function 'GetCountryDetails' expects parameter '@CountryID', which was not supplied
Someone please help!
 
Thanks Nickdel68

View Replies !

---

Argument Not Specified For Parameter 'arguments' Of Public Function Select(arguments As System.Web.DatasourceSelect Arguments As Collections Ienumerable
I have an SqlDataSource control on my aspx page, this is connected to database by a built in procedure that returns a string dependent upon and ID passed in.
 I have the followinbg codewhich is not complet, I woiuld appriciate any help to produce the correct code for the code file
 Function GetCategoryName(ByVal ID As Integer) As String          sdsCategoriesByID.SelectParameters("ID").Direction = Data.ParameterDirection.Input          sdsCategoriesByID.SelectParameters.Item("ID").DefaultValue = 3          sdsCategoriesByID.Select() <<<< THIS LINE COMES UP WITH ERROR 1End Function
ERROR AS FOLLOWS
argument not specified for parameter 'arguments' of public function Select(arguments as System.Web.DatasourceSelect Arguments as Collections ienumerable
 
Help I have not got much more hair to loose
Thanks Steve

View Replies !

---

Error: Procedure Or Function &"Roles_delete&" Expects Parameter &"@Role_name&", Which Not Supplied...
Hello guy...i have a problem with my web application...i'm using SqlDataSource and GridView....i'm using store procedure that i've created in my mssql server...the problem is when i'm trying to delete current row...this error alert and said
Error

"Procedure or Function "Roles_delete" expects parameter "@Role_name", which not supplied"
Im new user in asp.net....how i can passing my selected value to this parameter??? This is my code...please help me guy...
Asp.net code
<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:PVMCCon %>"
DeleteCommand="Roles_delete" DeleteCommandType="StoredProcedure" SelectCommand="Roles_select"
SelectCommandType="StoredProcedure">
<DeleteParameters>
<asp:Parameter Name="Role_name" Type="String" />
</DeleteParameters>
</asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BackColor="White"
BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px" CellPadding="4" DataKeyNames="Role_application_id"
DataSourceID="SqlDataSource1" ForeColor="Black" GridLines="Vertical">
<FooterStyle BackColor="#CCCC99" />
<Columns>
<asp:CommandField ShowDeleteButton="True" />
<asp:BoundField DataField="Role_application_id" HeaderText="Role_application_id"
ReadOnly="True" SortExpression="Role_application_id" />
<asp:BoundField DataField="Role_name" HeaderText="Role_name" SortExpression="Role_name" />
<asp:BoundField DataField="Role_description" HeaderText="Role_description" SortExpression="Role_description" />
</Columns>
<RowStyle BackColor="#F7F7DE" />
<SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
<HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
</ContentTemplate>
</asp:UpdatePanel>
 
Store Procedure
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[Roles_delete]
@Role_name varchar(50)
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM Role
WHERE Role_name=@Role_name
END

 Error

Procedure or Function "Roles_delete" expects parameter "@Role_name", which not supplied"
Please help me guy to settle my problem...

View Replies !

---

Using Cint Function In Expression Editor
Hi, I am a new to Reporting Services...

I am using RS2000

I am trying to apply cint function in the expression editor for the textbox

eg. i type in =cint(Fields!firstfield.Value/Fields!secondfield.Value).

This is returning error

The value expression for the textbox €˜HoldDuration€™ contains an error: Arithmetic operation resulted in an overflow.

Could you please let me know what I did wrong in this and how to correctly apply VB or any custom functions in the expression editor where the expession output would be a decimal value and you are interested in getting an integer value by rounding the fractional part.

View Replies !

---

Function With Expression To Return Values
I have created a function to return values, which works fine, but I can't do calculations in it.

CREATE FUNCTION [dbo].[tf_Asset_Portfolio](@deal_id int,
@as_of_date datetime)
RETURNS TABLE
AS
RETURN ( SELECT DISTINCT dbo.Assets.issue_id, SUM(DISTINCT dbo.Assets.par_amount) AS par_amount, SUM(DISTINCT dbo.Assets.par_amount) AS market_value
FROM dbo.Issue INNER JOIN
dbo.Assets ON dbo.Issue.issue_id = dbo.Assets.issue_id INNER JOIN
dbo.Issuer_Rating_History ON dbo.Issue.issuer_id = dbo.Issuer_Rating_History.issuer_id
WHERE (dbo.Issuer_Rating_History.as_of_date <= @as_of_date)
GROUP BY ALL dbo.Assets.issue_id, dbo.Assets.deal_id, dbo.Issue.default_date
HAVING (dbo.Assets.deal_id = @deal_id) )

I need to do calculations on market value based on the default date.
If default date isn't specified then it should be 100% of par amount.
If default date is less than one year ago - 65% of the par_amount.
If default date is one or more years ago - 0.
I have no idea about how to do this and everything I try wont work.
I created another function to do the calculations and this seems to work, but it only does one record instead of all of them.

CREATE FUNCTION dbo.tf_Asset_Portfolio2
(@deal_id int,
@as_of_date datetime)
RETURNS @Market TABLE
(issue_id int, par_amount money, market_value money)
AS
BEGIN
DECLARE @ReturnDate datetime
DECLARE @DD datetime
DECLARE @PA money
DECLARE @MV money
DECLARE @ID int
DECLARE @DateD int

SELECT TOP 1
@ReturnDate = LAST_BATCH
FROM master..sysprocesses
WHERE SPId = @@SPID

SELECT @ID = issue_id FROM Assets WHERE Assets.deal_id = @deal_id
SELECT @PA = SUM(DISTINCT par_amount) FROM Assets WHERE Assets.issue_id = @ID AND Assets.deal_id = @deal_id
SELECT @DD = default_date FROM Issue WHERE Issue.issue_id = @ID

SET @DateD = DateDiff("yyyy", @DD, @ReturnDate)

If @DD = Null
BEGIN
SET @MV = @PA
END
Else If @DD > @ReturnDate
BEGIN
SET @MV = @PA
END
Else If @DateD < 1
BEGIN
SET @MV = @PA * .65
END
Else If @DateD >= 1
BEGIN
SET @MV = 0
END

insert into @Market
(issue_id, par_amount, market_value)
values
(@ID,@PA,@MV)

RETURN
END

I need to combine the functionality of being able to return mutliple records that isn't in the 2nd function and being able to calculate the market value which isn't in the first one. Please help. Thank you in advance.

View Replies !

---

SUM Of Report Field With Expression Which Has COUNT Aggregate Function
Hi everyone,

 

I have created a report which has 3 groups. The report output as shown below. I am having trouble getting the SUM of Total Credtis for each Org.

Can't seem to get the total  42 and 16 (highlighted), but can get total unists 11 and 13. I get expression contains aggregate function. This is because Units assessed is the Count of IDs (details hidden from the report).  

 

Report has three groups Org , Assessor and Unit.

Can someone please help me with this?

Appreciate help.

Thank you,

Ski

 

 

Org 1(Group1)

                            Unit      Credits   Units Assessed(# of Trainees) TotalCredits

 

 Assessor 1      Unit 1          2            4 (Count of Ids)               8 (2*4)

 Assessor 2      Unit 2          1            2                                       2 (1*2)

 Assessor 3      Unit 3          5            2                                     10 (5*2)

                           Unit 4          2            1                                       2

 Assessor 4       Unit 5          10           2                                    20

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

                                                              11                      42                                                                                                          -----------------------------------------------------------------------------------------------------------

 

 Org 2

   Assessor 3    Unit 1           2              3                                     6

   Assessor 4    Unit 6           1             10                                   10

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

                                                                13                                  16

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

 

 

 

 

 

View Replies !

---

How To Find Terms In A File Name: Regular Expression OR A String Function
hi,

i am using a forEach look to import each file within a folder, but i also need to calculate the dataset related ot these files.
the file are named as:

ff_inbound_20071008_1.csv
ff_inbound_20071008_2.csv


where for file ff_inbound_20071008_1.csv:

ff => flat file
inbound => dataset of this csv
20071008 => date
1=> file count

having in mind that they are store in the variable as a full path:

z:myFlatFilesexportsproj01ff_inbound_20071008_2.csv

i need to extract the dataset and the date for each file.

how can i extract these terms from the file name?


many thanks,

nicolas

View Replies !

---

How To Convert A Returning String From Custom Code To A Function In Expression?
I have a custom code function that return string like:

 

"SUM(Fields(Parameters!dept.Value + ""_1HeadCount"").Value) +

SUM(Fields(Parameters!dept.Value + ""_2HeadCount"").Value) +

SUM(Fields(Parameters!dept.Value + ""_3HeadCount"").Value) "

 

How do I convert this string to a function inside the expression (in this case "SUM", "Fields", "Parameters!", etc.)?

 

The expression of my TextBox show the string as a string instead of converting them to  proper function and display the corrent value.

 

The reason I need to build the string is because the Fields number is dynamic and I need a loop to increase the number in the field name.

 

Any suggest is highly appreciated!

 

Thanks,

Tabbey

View Replies !

---

Can I Apply A Database Function Or Assembly Call In An Expression For Filter Data?
I need to translate a user€™s regional setting into one of our own language codes before I send it through as a filter to the model query.  If our language codes were the same, the filter would look like this in the report filter -
                     Language Code = GetUserCulture()
Which translates to this in the database query (for us english) -
                        table.language_code = 'EN-us'
And of course I need it to look like this -
                             table.language_code = 'ENG'
 
I would like the logic to be globally available to all report writers (ie not forcing each report writer to have an iif or case stataement).  I was thinking custom assemblies or maybe a database function, but at this level of the filter, I cannot seem to figure out how to embed a database function call to apply to the filter criteria like this
              Language Code = dbo.ConvertFcnIWrote(GetUserCulture())
Or how I would access the custom assembly in the filter expression.
 
Do you have a recommended implementation for this situation?
 
Thanks,
Toni Fielder

View Replies !

---

Expression: !&&"No Exceptions Should Be Raised By This Code&&" When Selecting From System Function
One of my users is receiving the error below when selecting from this function:

 

select * from sys.dm_db_index_operational_stats (null,null,null,null)

 

 I have already granted him "grant view server state" rights and this did not change anything. However, as a test I temporarily granted him sysadmin rights and he did not receive the error. I am curious as to what missing rights are causing the error.

 

The server is sql version is 2005 standard edition 64 bit SP2: Microsoft SQL Server 2005 - 9.00.3042.00 (X64) 

 

All the posts about this error that I have seen indicate that this error is resolved after applying SP2. Anyone have any other suggestions?

 

 

Error

Location: qxcntxt.cpp:956

Expression: !"No exceptions should be raised by this code"

SPID: 58

Process ID: 2000

Msg 21, Level 20, State 1, Line 3

Warning: Fatal error 1203 occurred at Jun 15 2007 10:32AM. Note the error and time, and contact your system administrator.

Msg 0, Level 20, State 0, Line 0

A severe error occurred on the current command. The results, if any, should be discarded.

 

 

 

The following is from the SQL Log:

Process 58 unlocking unowned resource: DATABASE: 35
Error: 1203, Severity: 20, State: 1.
Process ID 58 attempted to unlock a resource it does not own: DATABASE: 35 . Retry the transaction, because this error may be caused by a timing condition. If the problem persists, contact the database administrator.
Process 58 unlocking unowned resource: DATABASE: 35
Using 'dbghelp.dll' version '4.0.5'
**Dump thread - spid = 58, PSS = 0x00000000A11EDBE0, EC = 0x00000000A11EDBF0
***Stack Dump being sent to D:Datase-k-MSSQLServer-OCSQLOGSQLDump0027.txt
* *******************************************************************************
*
* BEGIN STACK DUMP:
*   06/15/07 10:32:59 spid 58
*
* Location:  qxcntxt.cpp:956
* Expression:  !"No exceptions should be raised by this code"
* SPID:   58
* Process ID:  2000
*
* Input Buffer 276 bytes -
*             execute as login = 'INTNPU22537'  select suser_name()  selec
*  t * from sys.dm_db_index_operational_stats (null,null,null,null) 
* 
*
*  MODULE                          BASE      END       SIZE
* sqlservr                       0000000001000000  0000000003501FFF  02502000
* ntdll                          0000000077EC0000  0000000077FF7FFF  00138000
* kernel32                       0000000077D40000  0000000077EB2FFF  00173000
* MSVCR80                        0000000078130000  00000000781FEFFF  000cf000
* msvcrt                         000007FF7FC00000  000007FF7FC85FFF  00086000
* MSVCP80                        000000007C420000  000000007C530FFF  00111000
* ADVAPI32                       000007FF7FEE0000  000007FF7FFE4FFF  00105000
* RPCRT4                         000007FF7FD30000  000007FF7FED8FFF  001a9000
* USER32                         0000000078C30000  0000000078D3BFFF  0010c000
* GDI32                          000007FF7FC90000  000007FF7FD28FFF  00099000
* CRYPT32                        000007FF7D180000  000007FF7D2DEFFF  0015f000
* MSASN1                         000007FF7D150000  000007FF7D179FFF  0002a000
* Secur32                        000007FF7E7F0000  000007FF7E811FFF  00022000
* MSWSOCK                        000007FF76FE0000  000007FF7705BFFF  0007c000
* WS2_32                         000007FF77150000  000007FF7717FFFF  00030000
* WS2HELP                        000007FF77140000  000007FF7714BFFF  0000c000
* USERENV                        000007FF7C4A0000  000007FF7C5A8FFF  00109000
* opends60                       00000000333E0000  00000000333E7FFF  00008000
* NETAPI32                       000007FF77370000  000007FF77407FFF  00098000
* SHELL32                        000007FF7F190000  000007FF7FB97FFF  00a08000
* SHLWAPI                        000007FF7EF60000  000007FF7EFFAFFF  0009b000
* comctl32                       0000000000AF0000  0000000000C76FFF  00187000
* psapi                          000007FF7E1B0000  000007FF7E1BFFFF  00010000
* instapi                        0000000048060000  000000004806CFFF  0000d000
* sqlevn70                       000000004F610000  000000004F7B8FFF  001a9000
* SQLOS                          00000000344D0000  00000000344D5FFF  00006000
* rsaenh                         000000000FFB0000  000000000FFEDFFF  0003e000
* AUTHZ                          000007FF7E2C0000  000007FF7E2ECFFF  0002d000
* MSCOREE                        000006427EE60000  000006427EED3FFF  00074000
* ole32                          000007FF7ECE0000  000007FF7EF51FFF  00272000
* msv1_0                         000007FF7E330000  000007FF7E373FFF  00044000
* iphlpapi                       000007FF57250000  000007FF57280FFF  00031000
* kerberos                       000007FF77410000  000007FF774C2FFF  000b3000
* cryptdll                       000007FF7DAB0000  000007FF7DABEFFF  0000f000
* schannel                       000007FF7DB70000  000007FF7DBB0FFF  00041000
* COMRES                         000007FF7E920000  000007FF7E9E5FFF  000c6000
* XOLEHLP                        000007FF5C560000  000007FF5C566FFF  00007000
* MSDTCPRX                       000007FF67140000  000007FF67210FFF  000d1000
* msvcp60                        0000000007420000  0000000007509FFF  000ea000
* MTXCLU                         000007FF7B540000  000007FF7B569FFF  0002a000
* VERSION                        000007FF7FBF0000  000007FF7FBFAFFF  0000b000
* WSOCK32                        000007FF770F0000  000007FF770F9FFF  0000a000
* OLEAUT32                       000007FF7E9F0000  000007FF7EB03FFF  00114000
* CLUSAPI                        000007FF7B1C0000  000007FF7B1E3FFF  00024000
* RESUTILS                       000007FF7B310000  000007FF7B32BFFF  0001c000
* DNSAPI                         0000000007520000  000000000756DFFF  0004e000
* winrnr                         000007FF7E820000  000007FF7E82AFFF  0000b000
* WLDAP32                        000007FF7E780000  000007FF7E7E5FFF  00066000
* rasadhlp                       0000000007610000  0000000007616FFF  00007000
* security                       000007FF77530000  000007FF77534FFF  00005000
* msfte                          0000000049980000  0000000049D2DFFF  003ae000
* dbghelp                        0000000009630000  000000000978CFFF  0015d000
* WINTRUST                       000007FF7E210000  000007FF7E25DFFF  0004e000
* imagehlp                       000007FF7E2A0000  000007FF7E2B2FFF  00013000
* dssenh                         000000000FF70000  000000000FFABFFF  0003c000
* hnetcfg                        000007FF6D1F0000  000007FF6D280FFF  00091000
* wshtcpip                       000007FF76FA0000  000007FF76FAAFFF  0000b000
* NTMARTA                        000007FF7E2F0000  000007FF7E32BFFF  0003c000
* SAMLIB                         000007FF76F80000  000007FF76F95FFF  00016000
* ntdsapi                        000007FF7DAD0000  000007FF7DAF3FFF  00024000
* xpsp2res                       000007FF5C210000  000007FF5C4D6FFF  002c7000
* CLBCatQ                        0000000003510000  00000000035ECFFF  000dd000
* sqlncli                        00000000337A0000  0000000033A5FFFF  002c0000
* COMCTL32                       0000000003610000  00000000036FDFFF  000ee000
* comdlg32                       000007FF7D360000  000007FF7D3D6FFF  00077000
* SQLNCLIR                       0000000035000000  0000000035032FFF  00033000
* msftepxy                       000000000AA10000  000000000AA30FFF  00021000
* xpsqlbot                       000000004A7C0000  000000004A7C7FFF  00008000
* xpstar90                       0000000053C30000  0000000053CB5FFF  00086000
* SQLSCM90                       0000000053AD0000  0000000053ADBFFF  0000c000
* ODBC32                         000007FF63EB0000  000007FF63F16FFF  00067000
* BatchParser90                  00000000520C0000  00000000520ECFFF  0002d000
* ATL80                          000000007C630000  000000007C64DFFF  0001e000
* odbcint                        000007FF63DC0000  000007FF63DD7FFF  00018000
* xpstar90                       000000000B440000  000000000B465FFF  00026000
* xplog70                        0000000034730000  000000003473FFFF  00010000
* xplog70                        000000000B480000  000000000B482FFF  00003000
* odsole70                       0000000033380000  0000000033397FFF  00018000
* SQLDMO                         000000001C0B0000  000000001C6E2FFF  00633000
* odbcbcp                        000007FF63E90000  000007FF63E97FFF  00008000
* SQLDMO                         000000001C000000  000000001C08DFFF  0008e000
* SXS                            000007FF7CA80000  000007FF7CC6CFFF  001ed000
* SQLSRV32                       000007FF5FD60000  000007FF5FE05FFF  000a6000
* sqlsrv32                       00000000093C0000  00000000093D6FFF  00017000
* odbccp32                       000007FF63E20000  000007FF63E44FFF  00025000
* sqlevn70                       000000000C5D0000  000000000C7D4FFF  00205000
* sqlevn70                       000000000C7E0000  000000000C9E2FFF  00203000
* sqlevn70                       000000000C9F0000  000000000CB98FFF  001a9000
* sqlevn70                       000000000CBA0000  000000000CD8BFFF  001ec000
* sqlevn70                       000000000CD90000  000000000CF87FFF  001f8000
* sqlevn70                       000000000CF90000  000000000D173FFF  001e4000
* sqlevn70                       000000000D180000  000000000D33FFFF  001c0000
* sqlevn70                       000000000D340000  000000000D51FFFF  001e0000
* sqlevn70                       000000000D520000  000000000D6F9FFF  001da000
* sqlevn70                       000000000D700000  000000000D8A8FFF  001a9000
* sqlevn70                       000000000D8B0000  000000000DA58FFF  001a9000
* sqlevn70                       000000000DA60000  000000000DC08FFF  001a9000
*
*     P1Home: 0000000000000001: 
*     P2Home: 000000000FAD3E00:  0030004600420044  000000000000000A  0000000000000000  0000000000000000  0000000500000030  0000000000000000 
*     P3Home: 0000000000000000: 
*     P4Home: 0000000077EF40D2:  00000320249C8B48  0000031824B48B48  0000031024BC8B48  0000030824A48B4C  0000030024AC8B4C  000002F824B48B4C 
*     P5Home: 0000000000000000: 
*     P6Home: 0000000000000000: 
* ContextFlags: 000000000010000F: 
*      MxCsr: 0000000000001FA0: 
*      SegCs: 0000000000000033: 
*      SegDs: 000000000000002B: 
*      SegEs: 000000000000002B: 
*      SegFs: 0000000000000053: 
*      SegGs: 000000000000002B: 
*      SegSs: 000000000000002B: 
*     EFlags: 0000000000000206: 
*        Rax: 000000000FAD3E88:  0000000077D67D4D  0000000009307780  0000000000000000  000000000FAD4140  00000000A11EDBE0  00000000000042AC 
*        Rcx: 000000000FAD39B0:  0000000000000001  000000000FAD3E00  0000000000000000  0000000077EF40D2  0000000000000000  0000000000000000 
*        Rdx: 0000000000000000: 
*        Rbx: 0000000000000000: 
*        Rsp: 000000000FAD3E90:  0000000009307780  0000000000000000  000000000FAD4140  00000000A11EDBE0  00000000000042AC  0000000000000000 
*        Rbp: 000000000FAD5620:  FFFFFFFF000003BC  000000000FAD9324  000003E500000001  0000000000000001  0000000000000007  000000000FAD935C 
*        Rsi: 000000000FAD4140:  0000000009307780  0000000000000000  0000000000000000  0000000000000000  0000000000000001  0000000000000000 
*        Rdi: 00000000A11EDBE0:  00000000A11ECB00  0000000000000000  00000000A11EDBE0  0000000000000000  0000000000000000  00000000A11EDC08 
*         R8: 0000000000000000: 
*         R9: 0000000000000000: 
*        R10: 0000000000000000: 
*        R11: 000000000FAD4680:  0000000000000001  000000000FAD3E00  0000000000000000  0000000077EF40D2  0000000000000000  0000000000000000 
*        R12: 000000000000003F: 
*        R13: 00000000A11EDBF0:  00000000A11EDBE0  0000000000000000  0000000000000000  00000000A11EDC08  00000000A11EDC08  00000000A11EDC18 
*        R14: 000000000FAD5530:  00610063006F004C  006E006F00690074  007100200009003A  0074006E00630078  0063002E00740078  0039003A00700070 
*        R15: 0000000000000001: 
*        Rip: 0000000077D67D4D:  C3000000C8C48148  15FF984B8D49D233  8699E990FFFD971F  9090909090900000  834853C08B909090  0C0D8A158B4830EC 
* *******************************************************************************
* -------------------------------------------------------------------------------
* Short Stack Dump
0000000077D67D4D Module(kernel32+0000000000027D4D)
0000000001D345DE Module(sqlservr+0000000000D345DE)
0000000001D3F3A9 Module(sqlservr+0000000000D3F3A9)
000000000247E28C Module(sqlservr+000000000147E28C)
00000000015D069F Module(sqlservr+00000000005D069F)
00000000781CF4B0 Module(MSVCR80+000000000009F4B0)
000000007816AA10 Module(MSVCR80+000000000003AA10)
0000000077EF3722 Module(ntdll+0000000000033722)
00000000010DEBF1 Module(sqlservr+00000000000DEBF1)
00000000010DE6EF Module(sqlservr+00000000000DE6EF)
00000000010DF551 Module(sqlservr+00000000000DF551)
00000000010E2B62 Module(sqlservr+00000000000E2B62)
00000000010E4115 Module(sqlservr+00000000000E4115)
00000000010E4695 Module(sqlservr+00000000000E4695)
00000000010E375D Module(sqlservr+00000000000E375D)
00000000781CF4B0 Module(MSVCR80+000000000009F4B0)
000000007816AA10 Module(MSVCR80+000000000003AA10)
0000000077EF3722 Module(ntdll+0000000000033722)
000000000102F94A Module(sqlservr+000000000002F94A)
000000000102F375 Module(sqlservr+000000000002F375)
0000000001030872 Module(sqlservr+0000000000030872)
0000000001270CE7 Module(sqlservr+0000000000270CE7)
000000000100685E Module(sqlservr+000000000000685E)
0000000001006F11 Module(sqlservr+0000000000006F11)
0000000001006A01 Module(sqlservr+0000000000006A01)
00000000010EF7A7 Module(sqlservr+00000000000EF7A7)
00000000010F051A Module(sqlservr+00000000000F051A)
00000000010F03F1 Module(sqlservr+00000000000F03F1)
00000000010F0289 Module(sqlservr+00000000000F0289)
00000000781337A7 Module(MSVCR80+00000000000037A7)
0000000078133864 Module(MSVCR80+0000000000003864)
0000000077D6B69A Module(kernel32+000000000002B69A)
Stack Signature for the dump is 0x00000000A0A891FD
External dump process return code 0x20000001.  External dump process returned no errors.
Error: 17066, Severity: 16, State: 1.
SQL Server Assertion: File: <qxcntxt.cpp>, line=956 Failed Assertion = '!"No exceptions should be raised by this code"'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted. 

View Replies !

---

CASE Function Result With Result Expression Values (for IN Keyword)
I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function.

            WHERE                    GROUP.GROUP_ID = 2                AND    DEPT.DEPT_ID = 'D'                AND    WORK_TYPE_ID IN                     (                        CASE DEPT_ID                            WHEN 'D' THEN 'A','B','C'  <---- ERROR                            WHEN 'F' THEN 'C','D                             ELSE 'A','B','C','D'                           END                    )

I kept on getting errors, like

Msg 156, Level 15, State 1, Line 44Incorrect syntax near the keyword 'WHERE'.
which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.

View Replies !

---

No &&"Left&&" Function In Expression Builder?
Anyone know the reaon the Left function was left out of the list of string functions in the Expression Builder?

Danno

View Replies !

---

Cannot Perform An Aggregate Function On An Expression Containing An Aggregate Or A Subquery.
Can any1 tell me why i am getting an error
 

SELECT DISTINCT

--p.voucher,

--p.amount,

p.siteID,

b.siteID,

SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS OutStandingBalance,

SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS CashCheque,

SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS Vouchers

FROM

BillingTotal b,

Payment p

--WHERE

-- s.sitename=@cmb1

--AND p.siteid = s.siteid

-- p.voucher = 0

-- p.voucher = 1

GROUP BY p.siteID,b.siteID
 
 

Msg 130, Level 15, State 1, Line 1

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

View Replies !

---

URGENT - My Error Or Bug? The Result Of The Expression Cannot Be Written To The Property. The Expression Was Evaluated, But
Error    3    Error loading MLS_AZ_PHX.dtsx: The result of the expression ""C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property.      c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx    1    1   


"C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"

Directly using C:sql_working_directoryMLSAZPhoenixDocsArmls_Schema Updated 020107.xls
as connectionString works

However - I'm trying to deploy the package - and trying to use expression:
@[User::DIR_WORKING] + "\Docs\Armls_Schema Updated 020107.xls"
which causes the same error to occur

(Same error with other Excel source also:
Error    5    Error loading MLS_AZ_PHX.dtsx: The result of the expression "@[User::DIR_WORKING] + "\Docs\Armls_SchoolCodesJuly06.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property.      c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx    1    1   
)

View Replies !

---

Creating A New Expression And Using A Previously Created Expression In The Definition
Can I reference a previously created expression in a new expression?  I took a very quick look and not having much luck?

 

Thanks

View Replies !

---

SProc Expects Parameter...Help
This code works EXACTLY as it should on a development server that has a Framework 1.1 hotfix (operating in the 'localhost' webserver environment) but not on a production server (in a live www. web app) that does NOT have the 1.1 hotfix. Is there a code problem (if so, why should it work in the localhost) or is the hotfix on the development server needed on the production server.
(in a button sub that does an update)...Dim currentUser As New AccBusiness.User( _                                      CType(Context.User, SitePrincipal))                Dim DDName As String = currentUser.DDName                Dim NosData As String                Dim AccountDetails As New SecureNums(txtDDPhone.Text, txtDDAddress.Text)                NosData = AccountDetails.EncryptedData()                AccountDetails.UpdateAcctDetails()----------------------(business object for the update)... Public Function UpdateAcctDetails() As Boolean            Dim DataUserAcctNo As New Data.SecureCard(myModuleSettings.ConnectionString)            Return DataUserAcctNo.UpdateAcctDetails( _                myDDName, _                myNosData)        End Function--------------------------------(data object)...Public Function UpdateAcctDetails(ByVal DDName As String, _                                            ByVal NosData As String) As Boolean            Dim rowsAffected As Integer            Dim parameters As SqlParameter() = { _                New SqlParameter("@DDName", SqlDbType.VarChar, 50), _                New SqlParameter("@NosData", SqlDbType.VarChar, 512)}            parameters(0).Value = DDName            parameters(1).Value = NosData            RunProcedure("sp_Accounts_UpdateAccount", parameters, rowsAffected)            Return CBool(rowsAffected = 1)        End Function------------------------------and the sproc:ALTER PROCEDURE sp_Accounts_UpdateAccount@DDNameVarChar(50),@NosDataVarChar(512)AsUPDATE Accounts_UsersSet NosData = @NosDataWHERE DDName = @DDName------------------------------------------------------------
It all works on the development server but on the production server it gives me an error :SProc sp_Accounts_UpdateAccount expects parameter "@DDName" which was not supplied.
Can anyone suggest why this works in one environment and not the other?
Thx for looking/Reid C. 

View Replies !

---

SQLDataSource Expects Parameter
Problem: The system throws the following error"Procedure or Function 'sp_TestRequestFormMaster_StatusChange' expects parameter '@Status', which was not supplied."I'm using VS 2005 Final.Recreate the problem:I've created a simple stored procedure with two parameters on SQL 2005 on Win 2003 Server. @ID INT, & @Stutus INTOn a SQLDataSource Control for the Delete query, using the build button to open the Command and Parameter Editor, I click the Refresh Paramater.I set ID Parameter Source: Control, ControlID: GridView1I set Status Parameter Source: None, DefaultValue: 1001.Partial Source View:
<DeleteParameters><asp:ControlParameter ControlID="GridView1" Name="ID" propertyName="SelectedValue" Type="Int32" DefaultValue="" /> <asp:Parameter DefaultValue="1001" Name="Status" Type="Int32" /><asp:Parameter DefaultValue="" Direction="ReturnValue" Name="RETURN_VALUE" Type="Int32" /></DeleteParameters>I run the code, click the delete in the GridView and the error appears.  How can I pass a status value without relating it to a source.

View Replies !

---

Expects Parameter Which Was Not Supplied....need Help....
I wanted to add new record into two different tables. By using one web form...
Not so sure I coded it correctly. And I got this error trying to insert new record

" Procedure 'sp_insert' expects parameter '@mid', which was not supplied. "

Isn't it i have include @mid in my code ...??


<STORED PROCEDURES>
sp_insert

(
@mid char(10),
@status char(10),
@name char(20),
@dj char(20),
@rank char(20),
@nric char(14),
@dob char(20),
@age char(10),
@add char(80),
@school char(50),
@hp char(10),
@mp char(10),
@email char(30),
@pgname char(20),
@relationship char(20),
@contact char(10)

)
AS
insert into Table1(mid,status,name,date_joined,rank,nric,dob,age,address,school,house_phone,Mobile_phone,email,[P/G_name],[P/G_relationship],[P/G_contacts])
values (@mid,@status,@name,@dj,@rank,@nric,@dob,@age,@add,@school,@hp,@mp,@email,@pgname,@relationship,@contact)

insert into Table2 (name) values (@name)

RETURN

<WEB FORM>
mycommand = New SqlCommand("sp_insert", mycon)
mycommand.CommandType = CommandType.StoredProcedure

Dim midpar As New SqlParameter("@mid", SqlDbType.Char, 10)
midpar.Direction = ParameterDirection.Input
midpar.Value = Tb1.Text

Dim statuspar As New SqlParameter("@status", SqlDbType.Char, 10)
statuspar.Direction = ParameterDirection.Input
statuspar.Value = ddl1.SelectedItem.Text

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

View Replies !

---

Argument Not Specified For Parameter
Hi,I'm working with visual studio 2008 and a sql server database. I'm also a bit of a beginner. I've written a stored procedure as below. My aim is to try and retrieve the data from field 'caption' through a paramater being passed.
I'm using the function as below to call this procedure through objectdatasource.
i'm getting the following error:
Compiler Error Message: BC30455: Argument not specified for parameter 'caption' of 'Public Sub New(id As Integer, count As Integer, caption As String, ispublic As Boolean)'.Source Error:





Line 98: Using reader As SqlDataReader = command.ExecuteReader()
Line 99: Do While (reader.Read())
Line 100: Dim temp2 As New Album(CStr(reader("Caption")))
Line 101: list2.Add(temp2)
Line 102: Loop
Any help will be apreciated, a lot of my code is copy and pasted and i don't fully understand every line which is where i assume the error is being made!
Thanks 
 
FUNCTIONSPublic Shared Function GetAlbumName(ByVal AlbumID As Integer) As Generic.List(Of Album)
Using connection As New SqlConnection(ConfigurationManager.ConnectionStrings("Personal").ConnectionString)Using command As New SqlCommand("GetAlbumName", connection)
command.CommandType = CommandType.StoredProcedurecommand.Parameters.Add(New SqlParameter("@AlbumID", AlbumID))
connection.Open()Dim list2 As New Generic.List(Of Album)()
Using reader As SqlDataReader = command.ExecuteReader()Do While (reader.Read())Dim temp2 As New Album(CInt(reader("AlbumID")), CStr(reader("Caption")))
list2.Add(temp2)
Loop
End UsingReturn list2
End Using
End Using
End Function
 
SQL STORED PROCEDURE
ALTER PROCEDURE GetAlbumName@AlbumID int
AS
SELECT
*FROM [Albums]WHERE [Albums].[AlbumID] = @AlbumID
 
 
RETURN

View Replies !

---

Argument Not Specified For Parameter
I try to replace the following statement : CmdPuzzle.Parameters.Append CmdPuzzle.CreateParameter("@Length",adTinyInt,adParamInput,,6)
 with:
Dim retLengthParam As New SqlParameter("@Length", SqlDbType.TinyInt, , 6)
it highlighted the retLengthParam saying:
Argument not specified for parameter 'size' of 'Public Sub New(parameterName As String, dbType As System.Data.SqlDbType, size As Integer, sourceColumn As String)
Logic:


set that the input is only allowed 6 integer.

View Replies !

---

How Do I Specify More Than I Argument In A Called SP?
CREATE PROCEDURE sp_getT
@m1 int ,
@txn int ,
@Pan varchar(50) ,
@Act varchar(50) OUTPUT,
@Bal Decimal(19,4) OUTPUT,
@CBal Decimal(19,4) OUTPUT
AS

declare @pBal money, @pCbal money, @pAct money
SET NOCOUNT ON


IF @m1 = 200
BEGIN
IF @txn = 31
BEGIN
exec ChkBal @Pan, @pBal output, @pCbal output, @pAct out
END
END

SET @Act = @pAct
SET @Bal = cast(@pBal as Decimal(19,4))
SET @CBal = cast(@pCBal as Decimal(19,4))

return @Act
return @Bal
return @CBal

the above code returns this error message

"Server: Msg 8144, Level 16, State 2, Procedure CheckBalance, Line 0
Procedure or function ChkBal has too many arguments specified."


How do i specify all the arguments i want in the called procedure?

View Replies !

---

Optional Argument In UDF?
Is it possible to define an argument as optional for a UDF? I have a financial calculation that may or may not require a defined date range depending on the status of an individual item. Is there a way to avoid requiring the date range where it's not necessary?

View Replies !

---

Procedure Expects Parameter Which Was Not Supplied
 Hi everyone, can you please take a look at this code and tell me where I'm wrong.I am getting the error Procedure 'usp_Insert_Attribution' expects parameter '@At_Code', which was not supplied <asp:SqlDataSource
id="ds_Attribution"
runat="server"
SelectCommand="usp_Select_Attribution"
SelectCommandType="StoredProcedure"
InsertCommand="usp_Insert_Attribution"
InsertCommandType="StoredProcedure"
DeleteCommand="usp_Delete_Attribution"
DeleteCommandType="StoredProcedure"
UpdateCommand="usp_Update_Attribution"
UpdateCommandType="StoredProcedure">
<SelectParameters>
<asp:Parameter Name="At_ID" Type="Int32"/>
</SelectParameters>
<InsertParameters>
<asp:Parameter Name="Identity" Direction="Output" Type="Int32"/>
</InsertParameters>
<DeleteParameters>
<asp:Parameter Name="At_ID" Type="Int32"/>
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="At_ID" Type="Int32"/>
</UpdateParameters>
</asp:SqlDataSource>   <InsertItemTemplate>
<table>
<tr>
<td>Attribution Code</td>
<td>
<asp:Textbox ID="txtAt_Code" runat="server" Text='<%# Eval("At_Code") %>' CssClass="textbox" />
<asp:RequiredFieldValidator ID="rfvAt_Code" runat="server" ControlToValidate="txtAt_Code"
Display="Static" Text="*" ErrorMessage="Attribution Code must be entered." />
<asp:CustomValidator ID="cvAt_Code" runat="server" Text="*" ErrorMessage="Attribution Code already exists."
Display="Static" />
</td>
</tr>
<tr>
<td>Attribution Description</td>
<td>
<asp:Textbox ID="txtAt_Description" runat="server" CssClass="textBox_Multiline" maxlength="250" TextMode="MultiLine" Text='<%# Bind("At_Description") %>'></asp:Textbox>
</td>
</tr>
</table>

</table>
<asp:Panel ID="pnlAttributionOptions_I" runat="server" GroupingText="Options" CssClass="panel">
<table width="100%">
<tr>
<td><asp:LinkButton ID="lnkCancel" runat="server" Text="Cancel" CommandName="Cancel" CssClass="button" CausesValidation="false"></asp:LinkButton>
</td><td><asp:LinkButton ID="lnkInsert" runat="server" Text="Save" CommandName="Insert" CssClass="button"></asp:LinkButton>
</td></tr></table>
</asp:Panel>

</InsertItemTemplate>  CREATE PROCEDURE dbo.usp_Insert_Attribution
(
@At_Code varchar(10),
@At_Description varchar(250),
@At_SystemDate datetime,
@Identity int OUT
)
AS
SET NOCOUNT OFF;
INSERT INTO Attribution (At_Code, At_Description, At_SystemDate)
VALUES (@At_Code,@At_Description, @At_SystemDate);

SELECT * FROM Attribution WHERE (At_ID = SCOPE_IDENTITY())
SET @Identity = SCOPE_IDENTITY()
GO
  

View Replies !

---

SP Failing As It Expects A Value For The Output Parameter
ok  i have the following sp i want to return the id of the newly inserted row.....but when i try to execute this SP im getting an exception as it is expecting me to insert  @idOfNewlyInsertedNote parameter!but that is an output parameter!....please help thanks ALTER PROCEDURE dbo.insertUserNote
@title varchar(50),
@note varchar(MAX),
@memberID int,
@idOfNewlyInsertedNote int output
AS

INSERT INTO userNotes
(title
,note
,dateAdded
,memberID)
VALUES
(@title,
@note,
GETDATE(),
@memberID)

select @idOfNewlyInsertedNote=SCOPE_IDENTITY()
RETURN @idOfNewlyInsertedNote   

View Replies !

---

Procedure Expects Parameter Which Was Not Supplied
Here is my code. Please help me evaluate? Why do i get thiis error. What am i doing wrong: System.Data.SqlClient.SqlException: Procedure 'SearchHRMS' expects parameter
'@SearchString', which was not supplied 1 If Not IsPostBack Then
2 '2 - declare variables
3 Dim conn As SqlConnection
4 Dim cmd As SqlCommand
5
6 '3 - create connection
7 conn = New SqlConnection
8 conn.ConnectionString = ConfigurationManager.ConnectionStrings("HRMSConnectionString").ConnectionString
9 conn.Open()
10
11 '4 - create command
12 cmd = New SqlCommand
13 cmd.CommandType = CommandType.StoredProcedure
14 cmd.CommandText = "SearchHRMS"
15 '4.2 -Declare a parameter
16 Dim param As SqlParameter
17 param = New SqlParameter("@SearchString", SqlDbType.VarChar, 20)
18 param.Direction = ParameterDirection.Input
19 param.Value = "john"
20
21 '5-Link command to connection
22 cmd.Connection = conn
23
24 'EmployeeListDataList.DataSource = cmd.ExecuteReader()
25 'EmployeeListDataList.DataBind()
26 'GridView1.DataSource = cmd.ExecuteReader()
27 'GridView1.DataBind()
28 conn.Close()
29 End If
  

View Replies !

---

Expects Parameter......parameterized Query
 Hi all,  I am using the below parameterized query and get an error while executing it....can anyone please spot the error. Any help will be appreciated. I have gone cross-eyed now looking at it all day. The error I get it isParameterized Query '(@Re_UK_Eligible nvarchar(4000),@Re_Aus_Eligible nvarchar(33),@R' expects parameter @Re_JobType_Temp, which was not supplied. sqlStmt = "UPDATE Re_Users SET Re_UK_Eligible=@Re_UK_Eligible,Re_Aus_Eligible=@Re_Aus_Eligible,Re_Can_Eligible=@Re_Can_Eligible,Re_USA_Eligible=@Re_USA_Eligible,Re_Address1=@Re_Address1,Re_Address2=@Re_Address2,Re_Address3=@Re_Address3,Re_City=@Re_City,Re_Postcode=@Re_Postcode,Re_Country=@Re_Country,Re_Homephone=@Re_Homephone,Re_Mobile=@Re_Mobile,Re_JobType_Per=@Re_JobType_Per,Re_JobType_Temp=@Re_JobType_Temp,Re_JobType_Con=@Re_JobType_Con,Re_Hours_Full=@Re_Hours_Full,Re_Hours_Part=@Re_Hours_Part,Re_Sector=@Re_Sector,Re_StepTwoDone=1 WHERE Re_UserCount=" + Session["ReUserIdentity"];
cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ReConnectionString"].ConnectionString);
cmd = new SqlCommand(sqlStmt, cn);
cmd.CommandType = CommandType.Text;

//Insert UK
if (chkUK.Checked == false)
{
cmd.Parameters.Add(new SqlParameter("@Re_UK_Eligible", DBNull.Value));
}
if ((chkUK.Checked == true) && (UKRadioButtonList.SelectedIndex > -1))
{
cmd.Parameters.Add(new SqlParameter("@Re_UK_Eligible", UKRadioButtonList.SelectedItem.Text));
}

//Insert AUS
if (chkAUS.Checked == false)
{
cmd.Parameters.Add(new SqlParameter("@Re_Aus_Eligible", DBNull.Value));
}
if ((chkAUS.Checked == true) && (AUSRadioButtonList.SelectedIndex > -1))
{
cmd.Parameters.Add(new SqlParameter("@Re_Aus_Eligible", AUSRadioButtonList.SelectedItem.Text));
}

//Insert CAN
if ((chkCAN.Checked == false))
{
cmd.Parameters.Add(new SqlParameter("@Re_Can_Eligible", DBNull.Value));
}
if ((chkCAN.Checked == true) && (CANRadioButtonList.SelectedIndex > -1))
{
cmd.Parameters.Add(new SqlParameter("@Re_Can_Eligible", CANRadioButtonList.SelectedItem.Text));
}

//Insert USA
if (chkUSA.Checked == false)
{
cmd.Parameters.Add(new SqlParameter("@Re_USA_Eligible", DBNull.Value));
}
if ((chkUSA.Checked == true) && (USARadioButtonList.SelectedIndex > -1))
{
cmd.Parameters.Add(new SqlParameter("@Re_USA_Eligible", USARadioButtonList.SelectedItem.Text));
}

//Contact Details
cmd.Parameters.Add(new SqlParameter("@Re_Address1", Address1TextBox.Text));

if (Address2TextBox.Text == "")
{
cmd.Parameters.Add(new SqlParameter("@Re_Address2", DBNull.Value));
}
else
{
cmd.Parameters.Add(new SqlParameter("@Re_Address2", Address2TextBox.Text));
}

if (Address3TextBox.Text == "")
{
cmd.Parameters.Add(new SqlParameter("@Re_Address3", DBNull.Value));
}
else
{
cmd.Parameters.Add(new SqlParameter("@Re_Address3", Address3TextBox.Text));
}


cmd.Parameters.Add(new SqlParameter("@Re_City", CityTextBox.Text));
cmd.Parameters.Add(new SqlParameter("@Re_Postcode", PostcodeTextBox.Text));
cmd.Parameters.Add(new SqlParameter("@Re_Country", CountryDropDownList.SelectedItem.Text));

if (HomeTelephoneTextBox.Text == "")
{
cmd.Parameters.Add(new SqlParameter("@Re_Homephone", DBNull.Value));
}
else
{
cmd.Parameters.Add(new SqlParameter("@Re_Homephone", HomeTelephoneTextBox.Text));
}

if (MobileTelephoneTextBox.Text == "")
{
cmd.Parameters.Add(new SqlParameter("@Re_Mobile", DBNull.Value));
}
else
{
cmd.Parameters.Add(new SqlParameter("@Re_Mobile", MobileTelephoneTextBox.Text));
}


//Job Preferences
for (int i = 0; i < JobTypeCheckBoxList.Items.Count; i++)
{
if (JobTypeCheckBoxList.Items[i].Text == "Permanent" && JobTypeCheckBoxList.Items[i].Selected == true)
{
cmd.Parameters.Add(new SqlParameter("@Re_JobType_Per", 1));
}
else if (JobTypeCheckBoxList.Items[i].Text == "Permanent" && JobTypeCheckBoxList.Items[i].Selected == false)
{
cmd.Parameters.Add(new SqlParameter("@Re_JobType_Per", 0));
}

if (JobTypeCheckBoxList.Items[i].Text == "Temporary" && JobTypeCheckBoxList.Items[i].Selected == true)
{
cmd.Parameters.Add(new SqlParameter("@Re_JobType_Temp", 1));
}
else if (JobTypeCheckBoxList.Items[i].Text == "Temporary" && JobTypeCheckBoxList.Items[i].Selected == false)
{
cmd.Parameters.Add(new SqlParameter("@Re_JobType_Temp", 0));
}

if (JobTypeCheckBoxList.Items[i].Text == "Contract" && JobTypeCheckBoxList.Items[i].Selected == true)
{
cmd.Parameters.Add(new SqlParameter("@Re_JobType_Con", 1));
}
else if (JobTypeCheckBoxList.Items[i].Text == "Contract" && JobTypeCheckBoxList.Items[i].Selected == false)
{
cmd.Parameters.Add(new SqlParameter("@Re_JobType_Con", 0));
}
}


//Hours and Sector
for (int i = 0; i < HoursCheckBoxList.Items.Count; i++)
{
if (HoursCheckBoxList.Items[i].Text == "FullTime" && HoursCheckBoxList.Items[i].Selected == true)
{
cmd.Parameters.Add(new SqlParameter("@Re_Hours_Full", 1));
}
else if (HoursCheckBoxList.Items[i].Text == "FullTime" && HoursCheckBoxList.Items[i].Selected == false)
{
cmd.Parameters.Add(new SqlParameter("@Re_Hours_Full", 0));
}

if (HoursCheckBoxList.Items[i].Text == "PartTime" && HoursCheckBoxList.Items[i].Selected == true)
{
cmd.Parameters.Add(new SqlParameter("@Re_Hours_Part", 1));
}
else if (HoursCheckBoxList.Items[i].Text == "PartTime" && HoursCheckBoxList.Items[i].Selected == false)
{
cmd.Parameters.Add(new SqlParameter("@Re_Hours_Part", 0));
}
}
cmd.Parameters.Add(new SqlParameter("@Re_Sector", SectorDropDownList.SelectedItem.Text));

cn.Open();
cmd.ExecuteNonQuery();
  thanks,vijay

View Replies !

---

Procedure Expects Parameter Not Supplied
Hi - Im working with a sign up page in which I'd like to include the date that the member signed up. I keep getting this error and I was wondering if anyone could help. The error is:

Procedure 'spAddCustomer' expects parameter '@MemberSince', which was not supplied.

I have the date in a label on the form and then I am passing to the database with:

cmdAddCustomer.Parameters.Add("@MemberSince", today_date.text).

Can anyone help with what I am doing wrong? Thanks!

View Replies !

---

Procedure 'x' Expects Parameter '@y' Which Was Not Supplied.
I'm trying to update a member's record via stored procedure. The procedure is thus:

*********************************
CREATE PROCEDURE upd_MemberProfile(
@MemberID VarChar(10),
@FirstName VarChar(20),
@LastName VarChar(20),
@Address VarChar(50),
@City VarChar(40),
@StateID Char(2),
@ZipCode Char(5),
@Email VarChar(30),
@PayPalSubscrID VarChar(22)
) AS

UPDATE Members
SET FirstName = @FirstName, LastName = @LastName, Address = @Address, City = @City, StateID = @StateID, ZipCode = @ZipCode, Email = @Email, PayPalSubscrID = @PayPalSubscrID
WHERE MemberID = @MemberID
GO
*****************************

The call from the Webpage is like so:

*****************************
...
Dim Subscr_id As String = "123ABC-HDTV"

Try
cmd.CommandText = dbo() & "upd_MemberProfile"
cmd.CommandType = CommandType.StoredProcedure

With cmd.Parameters
.Add("@MemberID", MemberID)
.Add("@FirstName", FirstName)
.Add("@LastName", LastName)
.Add("@Address", Address)
.Add("@City", City)
.Add("@StateID", StateID)
.Add("@ZipCode", ZipCode)
.Add("@Email", Payer_email)
.Add("@PayPalSubscrID", Subscr_id)
End With


If cnn.State.Open Then
cnn.Close()
End If

cnn.Open()
cmd.ExecuteNonQuery()
cnn.Close()

Catch ex As Exception

'Notify Admin of the Problem
MailUsTheOrder("ERROR on upd_MemberProfile: " & ex.Message)

'Close the SQL Connection
If cnn.State.Open Then
cnn.Close()
End If
End Try
******************************

This was working two weeks ago. I have changed NOTHING and now I get the error:

Procedure 'upd_MemberProfile' expects parameter '@PayPalSubscrID', which was not supplied.

Any Ideas?

View Replies !

---

Procedure Expects Parameter Which Was Not Supplied
 
why is this? I posted this to the Windows forms group but this may be a problem with my spro?
here is the;
1.  sproc in SQL Server Express  (on Vista)
2.  the code ( vb.net VS2008 Beta 2)
3.  the error
http://www.hazzsoftwaresolutions.net/sprocParam_ex.htm
 
Thank you for any help.
Greg

View Replies !

---

Procedure Expects Parameter Which Was Not Supplied...?
I have this SP definition and below that the code to execute it from .Net. But I'm getting this error: Procedure 'GetNewId' expects parameter '@Key', which was not supplied.

Saw an solution on some site which said to set the value of the output param to NULL before calling it, but that didn't do anything.

Anyone have any ideas why this may be happening?? Thanks in advance.

CREATE PROC GetNewId(@TableName varchar(32), @Key bigint OUTPUT) AS
Declare @NextKey bigint
BEGIN TRAN

SELECT @NextKey = NewID from ID where TableName = @TableName
IF (SELECT @NextKey)IS NULL
  BEGIN
     INSERT INTO ID(NewID, TableName) VALUES(0, @TableName)
  END

  UPDATE ID
  SET NewID = NewID + 1 Where TableName = @TableName
 
  SELECT @NextKey = NewID from ID where TableName = @TableName
 
  SET @Key=@NextKey

COMMIT TRAN

  Return @Key

GO

---------------------------------------------------------------------------------------------
_SQLCommand.CommandText = theCommStr;
_SQLCommand.CommandType = CommandType.StoredProcedure;

SqlParameter aInputTblPrm = new SqlParameter("@TableName", "Jack");
aInputTblPrm.Direction = ParameterDirection.Input;
        
SqlParameter aReturnKeyPrm = new SqlParameter("@Key", SqlDbType.BigInt);
aReturnKeyPrm.Direction = ParameterDirection.InputOutput;        

_SQLCommand.Parameters.Add(aInputTblPrm);
_SQLCommand.Parameters.Add(aReturnKeyPrm);

_SQLCommand.ExecuteNonQuery();

View Replies !

---

'column' Argument Cannot Be Null.
Hi i get the above error whenever i try and run my page, what i am trying to do is embed a repeater within a datalist, here is my code;public void Page_Load(object sender, EventArgs e)
{string strID = Request.QueryString["id"];
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);SqlCommand command = new SqlCommand("stream_Users", conn);
command.CommandType = CommandType.StoredProcedure;command.Parameters.Add("@userID", SqlDbType.Int).Value = Request.QueryString["id"];
SqlDataAdapter cmd1 = new SqlDataAdapter(command);
//Create and fill the DataSet.DataSet ds = new DataSet();
cmd1.Fill(ds, "userName");
//Create a second DataAdapter for the Titles table.SqlDataAdapter cmd2 = new SqlDataAdapter("select * from UserSpecialties", conn);
cmd2.Fill(ds, "specialty");
//Create the relation bewtween the Authors and Titles tables.ds.Relations.Add("myrelation",
ds.Tables["userName"].Columns["userID"],ds.Tables["specialtyName"].Columns["userID"]);
//Bind the Authors table to the parent Repeater control, and call DataBind.DataList1.DataSource = ds.Tables["userName"];
Page.DataBind();
//Close the connection.
conn.Close();
}

View Replies !

---

Sqldatasource1.Select([Argument?])
Hi, nice to meet you all. I need to return a parameter value from a store procedure, i already done insert(), update(), delete() to return parameter back. Just only select() can't return parameter and it need argument, I try to write in Sqldatasource1.Select(Argument.empty), it can't work and return null. How should i solve the problem? Or I wirte the wrong argument? Can anyone help me? Thank in advance.

View Replies !

---

Argument Not Specified For Parameter Error
I am trying to extract a value out of a cookie and then use that as a parameter to a SQL Select...Where function but I am getting the Argument not specified for parameter error. I assume the value from the cookie is not in the variable that I created but I could be wrong.

What is the proper method for populating a variable from a cookie and using that value as part of a SQL Select....Where function?

After I get a value out of the Select...Where function, I need to use the dataset results in a DropDownList as well as perform the DataBind().

here's some of the code:



Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)
if Not Page.IsPostBack then
dim Code as String
Code = Server.HtmlEncode(Request.Cookies("UCodeCookie")("Code"))
label1.Text = Code
GetPropertyCodes(Code)
ddlPropertyCode.DataBind()
end if
End Sub

Function GetPropertyCodes(ByVal Code As String) As System.Data.DataSet
Dim connectionString As String = "server='(local)'; trusted_connection=true; database='master'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)

Dim queryString As String = "SELECT [Property_Details_db].[Prop_Code] FROM [Property_Details_db] WHERE ([Property_Details_db].[Code] = @Code)"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_code As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_code.ParameterName = "@Code"
dbParam_code.Value = Code
dbParam_code.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_code)

Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
dataAdapter.SelectCommand = dbCommand
Dim dataSet As System.Data.DataSet = New System.Data.DataSet
dataAdapter.Fill(dataSet)

Return dataSet
End Function



Thanks for your help.

Chris

View Replies !

---

Argument Not Specified For Parameters Error
hello, I am doing some tutorials to learn SQL reporting services. One of the tutorials is using asp to call a web services and blah blah etc...
Basically the report opens with two calenders and an execute button. the user would pick two dates and then hit execute.

the code is this:

Private Sub cmdExecute_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdExecute.Click
Dim report As Byte() = Nothing

'Create an instance of the Reporting Services
'Web Reference
Dim rs As localhost.ReportingService = New localhost.ReportingService
'Create the credentials that will be used when accessing
'Reporting Services. This must be a logon that has rights
'to the Axelburg Invoice-Batch Number Report.
'***Replace "LoginName", "Password", and "Domain" with
'the appropriate values. ***
rs.Credentials = New _
System.Net.NetworkCredential("Administrator", _
"password", "localhost")
rs.PreAuthenticate = True

'The Reporting Services virtual path to the report.
Dim reportPath As String = _
"/Galactic Delivery Services/Axelburg/Invoice-Batch Number"

' The rendering format for the report.
Dim format As String = "html4.0"

'The devInfo string tells the report viewer
'how to display with the report.
Dim devInfo As String = _
"<DeviceInfo>" + _
"<Toolbar>False</Toolbar>" + _
"<Parameters>False</Parameters>" + _
"<DocMap>True</DocMap>" + _
"<Zoom>100</Zoom>" + _
"</DeviceInfo>"

'Create an array of the values for the report parameters
Dim parameters(1) As localhost.ParameterValue
Dim paramValue As localhost.ParameterValue _
= New localhost.ParameterValue
paramValue.Name = "StartDate"
paramValue.Value = calStartDate.SelectedDate
parameters(0) = paramValue
paramValue = New localhost.ParameterValue
paramValue.Name = "EndDate"
paramValue.Value = calEndDate.SelectedDate
parameters(1) = paramValue

'Create variables for the remainder of the parameters
Dim historyID As String = Nothing
Dim Credentials() As localhost.DataSourceCredentials = Nothing
Dim showHideToggle As String = Nothing
Dim encoding As String
Dim mimeType As String
Dim warnings() As localhost.Warning = Nothing
Dim reportHistoryParameters() As _
localhost.ParameterValue = Nothing
Dim StreamIDs() As String = Nothing

Dim sh As localhost.SessionHeader = _
New localhost.SessionHeader
rs.SessionHeaderValue = sh

Try
'Execute the report.
report = rs.Render(reportPath, format, historyID, _
showHideToggle, encoding, mimeType, _
reportHistoryParameters, warnings, _
StreamIDs)

sh.SessionId = rs.SessionHeaderValue.SessionId

'Flush any pending responce.
Response.Clear()

'Set the Http headers for a PDF responce.
HttpContext.Current.Response.ClearHeaders()
HttpContext.Current.Response().ClearContent()
HttpContext.Current.Response.ContentType = "text/html"
' filename is the default filename displayed
'if the user does a save as.
HttpContext.Current.Response.AppendHeader( _
"Content-Disposition", _
"filename=""Invoice-BatchNumber.HTM""")

'Send he byte array containing the report
'as a binary response.

HttpContext.Current.Response.BinaryWrite(report)
HttpContext.Current.Response.End()

Catch ex As Exception
If ex.Message <> "Thread was being aborted." Then
HttpContext.Current.Response.ClearHeaders()
HttpContext.Current.Response.ClearContent()
HttpContext.Current.Response.ContentType = "text/html"
HttpContext.Current.Response.Write( _
"<HTML><BODY><H1>Error</H1><br><br>" & _
ex.Message & "</BODY></HTML>")
HttpContext.Current.Response.End()

End If
End Try


End Sub
End Class


This is the area underlined as failing:
report = rs.Render(reportPath, format, historyID, _
showHideToggle, encoding, mimeType, _
reportHistoryParameters, warnings, _
StreamIDs)

The errors all pretty much go like this:
c:inetpubwwwrootAxelburgFrontEndReportFrontEnd.aspx.vb(95): Argument not specified for parameter 'ParametersUsed' of 'Public Function Render(Report As String, Format As String, HistoryID As String, DeviceInfo As String, Parameters() As localhost.ParameterValue, Credentials() As localhost.DataSourceCredentials, ShowHideToggle As String, ByRef Encoding As String, ByRef MimeType As String, ByRef ParametersUsed() As localhost.ParameterValue, ByRef Warnings() As localhost.Warning, ByRef StreamIds() As String) As Byte()'.

I searched around, but didn't really understand what these errors mean. Any help is greatly appreciated.

View Replies !

---

Procedure X Expects Parameter '@dealer', Which Was Not Supplied.
Hi
In my code I do this
Dim ds As DataSet = SqlHelper.ExecuteDataset(System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionString").ToString(), "usp_SearchCar", dealer, vehicleType, bodyType, make, model, engineType, year, minPrice, maxPrice, transmission)
But I get the error (in the subject)
I check and the value of dealer is an empty string but that's ok right?
ThanksP
 

View Replies !

---