Listbox Contents To Sql Stored Procedure Command Parameter?

Feb 11, 2004

Hello,





Stuck in a spot and hoping someone will nudge me in the right direction....





I'm trying to write to a sql db via a storedprocedure using a parameter. i'm pretty certain the below statement is causing the problem. but i'm not sure how to properly refer to it....








Dim connString As String


connString = "integrated security=false;user id=sa;server=HCENT1;database=LicenseRenewal;persist security info=False"


Dim myConnection As New SqlConnection(connString)


Dim myCommand As New SqlCommand("InsertPage1", myConnection)


myCommand.CommandType = CommandType.StoredProcedure


' .......other (working) parameter statements.........


Dim parameterDates As New SqlParameter("@Dates", SqlDbType.VarChar, 4000)


parameterDates.Value = Session("lstDates")


myCommand.Parameters.Add(parameterDates)





myConnection.Open()


myCommand.ExecuteNonQuery()


myConnection.Close()








the session("lstDates") is the contents of lstDates.Items (listbox) from a previous page.


i'm guessing its not valid to refer to it as a varchar, can someone point me to the proper way to handle this?





tia


andy

View 4 Replies


ADVERTISEMENT

ListBox Web Server Control And Stored Procedure

May 15, 2008

Hi, I've a  ListBox Web Server Control where I select a list of citiesnow I would like to create a stored procedure with the selected values... please note that I use metods like these for execute my Stored procedure public DataSet Getgfdfgh(SqlConnection connection, String IDAttivitaTipo, DateTime DataInizio, DateTime DataFine)        {            ConnectionState currState = connection.State;            if (((connection.State & ConnectionState.Open) != ConnectionState.Open))                connection.Open();            try            {                SqlParameter[] parameters = new SqlParameter[3];                parameters[0] = new SqlParameter("@IDAttivitaTipo", IDAttivitaTipo);                parameters[1] = new SqlParameter("@DataInizio", DataInizio);                parameters[2] = new SqlParameter("@DataFine", DataFine);                SqlCommand cmd = CreateStoreProcedureCommand("Getfhdfh", connection, parameters);                SqlDataAdapter adapter = new SqlDataAdapter(cmd);                DataSet ds = new DataSet();                adapter.Fill(ds);                return ds;            }            finally            {                if ((currState == ConnectionState.Closed))                    connection.Close();            }        }   How Can I manage a list of values ??Thanks for help me!! 

View 1 Replies View Related

Passing Multiple Values From A Listbox Into A Stored Procedure

Dec 9, 2007

hi i have a listbox with selectedmode = multiple, i am currently using this code in my code behind (c#) to call the storedprocedure within the datasource but its not working: Do i have to write specific code in c# to send the mulitple values through?protected void confButton_Click(object sender, EventArgs e)
{
try
{foreach (ListItem item in authorsListBox4.Items)
{if (item.Selected)
{
AddConfSqlDataSource.Insert();
}
}saveStatusLabel.Text = "Save Successfull: The above publication has been saved";
}catch (Exception ex)
{saveStatusLabel.Text = "Save Failed: The above publication failed to save" + ex.Message;
}
}

View 3 Replies View Related

SQL Server 2014 :: Embed Parameter In Name Of Stored Procedure Called From Within Another Stored Procedure?

Jan 29, 2015

I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?

CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],

[Code] ....

View 9 Replies View Related

Sorting Parameter Listbox On Analysis Services Datasource

Feb 26, 2007

Hi all,



How must I change the mdx that is generated for the available values for a user parameter in order to get the content sorted?

Regards,

Henk

BTW the exact mdx query is given below (and the label field of the parameter is set to 'ParameterCaption'), but I would already appreciate an example of a simple mdx.

MEMBER [Measures].[ParameterCaption] AS '[Organisatie].[Level 5 naam].CURRENTMEMBER.MEMBER_CAPTION'

MEMBER [Measures].[Nummer en Naam] AS '[Organisatie].[Kosten Nummer].CURRENTMEMBER.MEMBER_CAPTION +": "+ [Organisatie].[Level 5 naam].CURRENTMEMBER.MEMBER_CAPTION'

--MEMBER [Measures].[ParameterValue] AS '[Organisatie].[Level 5 naam].CURRENTMEMBER.UNIQUENAME'

MEMBER [Measures].[ParameterLevel] AS '[Organisatie].[Level 5 naam].CURRENTMEMBER.LEVEL.ORDINAL'

SELECT {[Measures].[ParameterCaption],

[Measures].[Nummer en Naam]

, [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS

, filter ([Organisatie].[Level 5 naam].MEMBERS,[Measures]) ON 1 ,

[Organisatie].[Kosten Nummer] on 2

FROM ( SELECT ( STRTOSET(@OrganisatieLevelnaam, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@KalenderFactuurPeriode, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@KalenderFactuurJaarNummerLang, CONSTRAINED) ) ON COLUMNS FROM [FMR DWH Afgenomen Dienst])))

View 6 Replies View Related

Can I Set Each Item In A Listbox As A Parameter When Updating Access Database?

Nov 20, 2007

Hello everyone. I am using C#, and posted this in the C# forum, but was told to try here, as for some reason I just can't get this to work. Everyone was very helpful, but for some reason I keep getting a message that a value is not being given for one or more required parameters. This doesn't make any sense though, as I am specifying a value for all parameters...

Basically, I have a listBox that has several items in it. I want to update the database for each item that appears in the listBox. I can't seem to get this to work though, no matter what I try, and it is driving me nuts... lol

Here is a link to my original thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2349891&SiteID=1

Here is my code:





Code Block

string whereClause = "";
for (int i = 0; i < Panel1ListView1.Items.Count; i++)
{
if (i == 0)
{
whereClause = "ID = " + Panel1ListView1.Items[0].Text;
}
else
{
whereClause += " OR ID = " + Panel1ListView1.Items[i].Text;
}
}

try
{
using (OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Environment.CurrentDirectory + @"DB.mdb;Jet OLEDB:Database Password=xxx;"))
{
OleDbCommand cmd;
conn.Open();

string command = "UPDATE [Table1] SET [Status] = @P1, [Name] = @P2, [Number] = @P3 WHERE " + whereClause;

cmd = new OleDbCommand(command, conn);

cmd.Parameters.Add("@P1", OleDbType.VarChar).Value = Panel1TextBox3.Text;
cmd.Parameters.Add("@P2", OleDbType.VarChar).Value = Panel1TextBox1.Text;
cmd.Parameters.Add("@P3", OleDbType.VarChar).Value = Panel1TextBox2.Text;

int i = cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
I really appreciate any help anyone can give me. I REALLY want to get this working. I am open to any suggestions and will try anything.

Thank you very much for your help.

View 7 Replies View Related

RS 2005: Stored Procedure With Parameter It Runs In The Data Tab But The Report Parameter Is Not Passed To It

Feb 19, 2007

Hello,
since a couple of days I'm fighting with RS 2005 and the Stored Procedure.

I have to display the result of a parameterized query and I created a SP that based in the parameter does something:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE [schema].[spCreateReportTest]
@Name nvarchar(20)= ''

AS
BEGIN

declare @slqSelectQuery nvarchar(MAX);

SET NOCOUNT ON
set @slqSelectQuery = N'SELECT field1,field2,field3 from table'
if (@Name <> '')
begin
set @slqSelectQuery = @slqSelectQuery + ' where field2=''' + @Name + ''''
end
EXEC sp_executesql @slqSelectQuery
end

Inside my business Intelligence Project I created:
-the shared data source with the connection String
- a data set :
CommandType = Stored Procedure
Query String = schema.spCreateReportTest
When I run the Query by mean of the "!" icon, the parameter is Prompted and based on the value I provide the proper result set is displayed.

Now I move to "Layout" and my undertanding is that I have to create a report Paramater which values is passed to the SP's parameter...
So inside"Layout" tab, I added the parameter: Name
allow blank value is checked and is non-queried

the problem is that when I move to Preview -> I set the value into the parameter field automatically created but when I click on "View Report" nothing has been generated!!

What is wrong? What I forgot??

Thankx for any help!
Marina B.





View 3 Replies View Related

Stored Procedure With User!UserID As Parameter, As Report Parameter?

Jul 2, 2007

I had thought that this was possible but I can't seem to figure out the syntax. Essentially I have a report where one of the parameters is populated by a stored procedure.

Right now this is easily accomplished by using "exec <storedprocname>" as the query string for the report parameter. However I am not clear if it is possible to now incorporate User!UserID as parameter to the stored procedure. Is it? Thanks

View 1 Replies View Related

Stored Procedure Or Command In Sql

Oct 23, 2007

Hi fellows
I am working in a formula about drilling machine. This machine sends the inofrmation to the server basically the name of a blasthole, revolution,pulldown and so on. I have another database with information of the same blasthole, but with different items such as drill diameter,site,area and so on, so i have two tables with information, but both of them have the same blasthole that means that i can relate with a join and identify each blasthole. but teh main point is that i have a specific column con a distance that the machine is drilling for example in a certain moment this machine is drilling 1 feet 3 feet 4 feet until to reach the bottom of a limit. his
My problem is that i want to create a from to for those distances.

table 1
pk diameter bench

dh1 12.5 4050



table 2

blasthole depth
dh1 1.2
dh1 3.5
dh1 4.7
dh1 5.4
dh1 8.3
dh1 15



with a join i can obtain something like this

view 1
blasthole diameter bench depth
dh1 12.5 4050 1.2
dh1 12.5 4050 3.5
dh1 12.5 4050 4.7
dh1 12.5 4050 5.4
dh1 12.5 4050 8.3
dh1 12.5 4050 15

the most difficult part is to include a new colum taht can start with 0 and to obtain teh previus value from depth something like this


view 1-A
blasthole diameter bench from depth
dh1 12.5 4050 0 1.2
dh1 12.5 4050 1.2 3.5
dh1 12.5 4050 3.5 4.7
dh1 12.5 4050 4.7 5.4
dh1 12.5 4050 5.4 8.3
dh1 12.5 4050 8.3 15

I was thinking to create a pk2 in order to know how many values have each blasthole and create a loop and maybe i can organize the column from something like this

pk2 blasthole diameter bench from depth
1 dh1 12.5 4050 0 1.2
2 dh1 12.5 4050 1.2 3.5
3 dh1 12.5 4050 3.5 4.7
4 dh1 12.5 4050 4.7 5.4
5 dh1 12.5 4050 5.4 8.3
6 dh1 12.5 4050 8.3 15


I hope that somebody can understand my question, but i think is a hard one for the forum
cheers
Edwin





View 4 Replies View Related

ORDER BY Command In SQL Stored Procedure

Nov 5, 2003

How do you use a variable in the ORDER BY command in a sql statement.
I currently have:
****************************************
CREATE Procedure SS_POList
(
@CompanyID nvarchar(10),
@PO varchar (20) = '%'
)
As

SELECT
SS_Sendback.EndUserPO,
SS_Sendback.PO,
COUNT(SS_Sendback.EndUserPO) as pocount,
SUM(SS_Sendback.Customerprice) as totcost

FROM
SS_Sendback

WHERE
SS_Sendback.EndusercustomerNumber = @CompanyID
AND
SS_Sendback.EnduserPO Like @PO

GROUP BY
SS_Sendback.EndUserPO,
SS_Sendback.PO

ORDER BY
SS_Sendback.PO
GO
*************************************
I changed it to
*************************************
CREATE Procedure SS_POList
(
@CompanyID nvarchar(10),
@PO varchar (20) = '%',
@Order varchar(20)
)
As

SELECT
SS_Sendback.EndUserPO,
SS_Sendback.PO,
COUNT(SS_Sendback.EndUserPO) as pocount,
SUM(SS_Sendback.Customerprice) as totcost

FROM
SS_Sendback

WHERE
SS_Sendback.EndusercustomerNumber = @CompanyID
AND
SS_Sendback.EnduserPO Like @PO

GROUP BY
SS_Sendback.EndUserPO,
SS_Sendback.PO

ORDER BY
@Order
GO

and I receive the following error..
error1008: the select item identified by the Order By number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.
How does it know @order is a position and not a column name... It's a variable. I'm obviously doing something wrong. Can someone help.
Thanks

View 5 Replies View Related

Can Stored Procedure Execute A Dos Command?

Jul 14, 2001

Is it possible to create a stored procedure to run a custom dos command (eg. c:ProgramName param1 param2)?

Thanks,
Ben

View 1 Replies View Related

BCP Command Hangs Stored Procedure

Sep 30, 2013

I have a problem where bcp command is called and executed successfully sometimes but fails randomly.My stored procedure looks like this:

select @SQL = 'select * from [' + @RepositoryName + '] where ( IsNumeric([' + @PhoneField + ']) = ' + CONVERT(varchar(1), @FALSE) + ' OR LEN([' + @PhoneField + ']) <> ' + CONVERT(varchar(2), @ValidLength) + ')'
execute(@SQL)
set @Count = @@ROWCOUNT
if @Count > 0
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE

[code]....

The above procedure is called 4 times from another procedure by passing appropriate parameters. Most of the time, it executes successfully for 2 times before failing. When I try to debug, it always hangs at EXEC master..xp_cmdshell @CommandSQL. The task manager shows bcp.exe as running at this point. Interestingly, the required rows are exported using the bcp command, but the stored procedure does not move further until bcp.exe is manually stopped from the task manager.

View 2 Replies View Related

ADODB Command (Stored Procedure)

Jun 4, 2007

Hi!I already sent this to the ACCESS newsgroup. But since I do not know reallywhich side is really causing the problem, I have decided to send thisinquiryto this newsgroup also, if I may.Below is the environment of the application:a. MS Access 2003 application running on Windows XPb. SQL Server 2000 - backend running MS Server 2003 OSBelow is the code that is giving me an error:Dim com As ADODB.CommandSet com = New ADODB.CommandWith com.ActiveConnection = "DSN=YES2;DATABASE=YES100SQLC;".CommandText = "sp_Recalculate".CommandType = adCmdStoredProc.Parameters.Refresh.Parameters("@ItemNumber") = ItemNum.Execute ' This is where it hangs up...TotalItems = .Parameters("@TotalInStock")TotalCost = .Parameters("@TotalCost")End WithSet com = Nothingand the store procedure is:CREATE PROCEDURE DBO.sp_Recalculate@ItemNumber nvarchar(50),@TotalInStock int = 0,@TotalCost money = 0ASBEGINSET @TotalInStock = (SELECT Sum([Quantity in Stock])FROM [Inventory Products]WHERE [Item Number] = @ItemNumber)SET @TotalCost = (SELECT Sum([Cost] * [Quantity in Stock])FROM [Inventory Products]WHERE [Item Number] = @ItemNumber)ENDWhen the process goes to the ".Execute" line, it hangs up for a long timethen gives me an error message "Everflow". I have been trying to solvethis issue but do not have an idea for now of the cause.Below is my finding:a. When I run the stored procedure in the SQL analyzer, it works just fine.I placed a SELECT statement to view the result of the stored procedure.It gives the correct values.Can anyone have ideas or similar problems?Thanks.

View 8 Replies View Related

Procedure Or Function 'stored Procedure Name' Expects Parameter Which Was Not Supplied

Mar 26, 2007

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 5 Replies View Related

How To Call A Stored Procedure In An Insert Command

Feb 22, 2006

hi,i had a small doubt , i have a table xxx with two columns (a int,b int) and i have inserted 5 rows my query is to add the two colums using astored procedure and the result has to be displayed in an separatecolumn --this has to be done only stored procedures ---i know how tosolve the problem using computed columns conceptlike thiscreate table yyy(a int ,b int ,total as a+b)insert into yyy values (12,13)select * from yyyi need the answer using stored procedures---is it possiblepls help mesatish

View 1 Replies View Related

OLE DB Command And Stored Procedure That Returns Value And/or Error

May 23, 2006

Guys,

could someone please tell me : am I supposed to use the OLE DB
Command in a dataflow to call a stored procedure to return a value? Or
is it just supposed to be used to call a straightforward insert
statement only?



What I am hoping to do:



I have a table with a few columns and one identity column. In a
dataflow I would like to effect an insert of a record to this table and
retrieve the identity value of the inserted record... and I'd like to
store the returned identity in a user variable.



If I AM supposed to be able to do this... then how on earth do I do it?

I have spent hours fooling around with the OLE DB command trying to call a stored proc and get a return value.



In the Advanced Editor any time I try to add an output column (by
clicking on Add Column) I just get an error dialog that says "the
component does not allow adding columns to this input or output)



So, am getting pretty concussed .. banging my head of the wall like this...

So put me out of my misery someone please.... is the OLE DB Command intended for this or not?



Thanks

PJ

View 4 Replies View Related

Execute Stored Procedure (with Parameters) With An Exec Command

Feb 21, 2004

Hi everybody, I would like to know if it's possible to execute a stored procedure, passing it parameters, using not CommandType.StoredProcedure value of sqlcommand, but CommandType.Text.

I tried to use this:
sqlCmd.CommandType = CommandType.Text
sqlCmd.Parameters.Add(sqlPar)
sqlCmd.ExecuteNonQuery()

With this sql command:
"exec sp ..."

I wasn't able to make it to work, and I don't know if it's possible.

Another question:
if it's not possible, how can I pass a Null value to stored procedure?
This code:
sqlPar = new SqlParameter("@id", SqlDbType.Int)
sqlPar.Direction = ParameterDirection.Output
cmd.Parameters.Add(sqlPar)

sqlPar = new SqlParameter("@parent_id", DBNull)
cmd.Parameters.Add(sqlPar)

doesn't work, 'cause I get this error:
BC30684: 'DBNull' is a type and cannot be used as an expression.

How can I solve this?
Bye and thanks in advance.

P.S. I would prefer first method to call a stored procedure ('cause I could call it with 'exec sp null' sql command, solving the other problem), but obviusly if it's possible...=)

Sorry for grammatical mistakes.

View 9 Replies View Related

How To Capture The ResultSet Of EXEC Command In Stored Procedure

Oct 1, 2007



Hi All,

I have created a dynamic SQL STATEMENT , but the result of the execution of that statement matters to me. within stored procedure.

Note: If run the statement using EXEC() command, the result gets displayed on the SQL Editor.
But I DO NOT KNOW HOW to Capture that value.

Any idea how to capture as I would like capture the result and stored it in a table.

Thank you.
--Israr

View 4 Replies View Related

How To Call A Stored Procedure Just After A TableAdapter's INSERT Command Executes

Feb 29, 2008

All-
Is there a way that I can embedd a call to a stored procedure into an existing INSERT section in a table adapter?
Say my objective is to call a stored procedure called personfill automatically RIGHT AFTER the TableAdapter inserts a row into the person table. One catch is that the stored procedure must be sent the value of unique identifier field person_id, which was created for the new person record automatically by the db. (If this is not possible to do, I might try using a TRIGGER in the person table.)
Below is the INSERT code of the TableAdapter. My guess is that if I could call a procedure, I would want to put the call between lines 12 and 13.
Your comments would be most appreciated!!!
-Kurt1 <InsertCommand>
2 <DbCommand CommandType="Text" ModifiedByUser="false">
3 <CommandText>INSERT INTO [person] ([family_id], [circle_id], [person_type_id], [last], [first], [username], [password]) VALUES (@family_id, @circle_id, @person_type_id, @last, @first, @username, @password)</CommandText>
4 <Parameters>
5 <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="Int16" Direction="Input" ParameterName="@family_id" Precision="0" ProviderType="SmallInt" Scale="0" Size="0" SourceColumn="family_id" SourceColumnNullMapping="false" SourceVersion="Current" />
6 <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="Int16" Direction="Input" ParameterName="@circle_id" Precision="0" ProviderType="SmallInt" Scale="0" Size="0" SourceColumn="circle_id" SourceColumnNullMapping="false" SourceVersion="Current" />
7 <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="Int16" Direction="Input" ParameterName="@person_type_id" Precision="0" ProviderType="SmallInt" Scale="0" Size="0" SourceColumn="person_type_id" SourceColumnNullMapping="false" SourceVersion="Current" />
8 <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@last" Precision="0" ProviderType="VarChar" Scale="0" Size="0" SourceColumn="last" SourceColumnNullMapping="false" SourceVersion="Current" />
9 <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@first" Precision="0" ProviderType="VarChar" Scale="0" Size="0" SourceColumn="first" SourceColumnNullMapping="false" SourceVersion="Current" />
10 <Parameter AllowDbNull="true" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@username" Precision="0" ProviderType="VarChar" Scale="0" Size="0" SourceColumn="username" SourceColumnNullMapping="false" SourceVersion="Current" />
11 <Parameter AllowDbNull="true" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@password" Precision="0" ProviderType="VarChar" Scale="0" Size="0" SourceColumn="password" SourceColumnNullMapping="false" SourceVersion="Current" />
12 </Parameters>
13 </DbCommand>
14 </InsertCommand>
15 <SelectCommand>
16 <DbCommand CommandType="Text" ModifiedByUser="true">
17
 

View 2 Replies View Related

Passing Error Messages From Stored Procedure To Osql Command

Oct 7, 2004

I have a process that is running on the os. This process is picking up FTP files every 5 min, it renames them so not to confuse them with the new files, copies all renamed files into one file to be used by bulk insert, runs the bulk insert to populate a table, and then runs the stored procedure that scrubbing the data and insert it into another table. For every transaction that I do in my stored procedure, I do the error checking as follows:

IF @@error <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN

If my stored procedure encounters an error, return statement will stop it from running. If this happens, I need to stop the process that is running on the os as well.

Questions:

How can that be accomplished?

How to restart the stored procedure ones the error has been corrected?

Thank you for your help.

View 3 Replies View Related

How To Conditionally Or Programmatically Run A Stored Procedure With Command Type Set To Storedproc?

May 21, 2008

I'm using SQL RS 2005 and have a report where we want the report to run a different stored procedure depending on if a condition is true. I've set my 'command type' to stored proc and can type in the name of a stored procedure. If I type in just one stored procedure's name, it runs fine. But if I try to use a =IIF(check condition, if true run stored proc 1, if false run storedproc 2) then the exclamation (run) button is greyed out. Does anyone know how I can do this? Thanks.

View 3 Replies View Related

How To Execute Stored Procedure By Command Line In Client's Computer ?

Nov 28, 2006

I've a S.P. that need to execute in local PC, how can I execute ?

View 1 Replies View Related

Can The OLE DB Command Transformation Support Multiple Values Returned By A Stored Procedure Or Is The Limit One Value Only?

Apr 10, 2007

I have no problem getting OLE DB Command transformations to support single returns by a procedure.

For example, exec name_of_procedure ?,?,? OUTPUT



However, I have a stored procedure which accepts 1 input and returns 5 outputs. This procedure works fine at the command line but when I try to incorporate it into a OLE DB Command I don't get the multiple values returned. There's no problem at all configuring the transform as it recognizes all input and output parameters. For some reason I just don't get values returned.



thanks

John

View 14 Replies View Related

Stored Procedure Not Getting A Parameter

Feb 12, 2008

I have this update procedure that updates and encrypts a credit card column. I added the pass phrase to a table and now I'm trying to use that pass phrase in my update procedure.
When I run the update from the app the error is that the procedure is not getting the @Phrase parameter.
 Can anyone see why this might be?ALTER PROCEDURE [dbo].[UpdatePayment]

@paymentID int,
@PaymentDate datetime,
@TenderTypeID int,
@PaymentAmount money,
@CreditCardType int,
@ExpirationDate datetime,
@ccNumber nvarchar(50),
@Phrase nvarchar(25)

AS
BEGIN
SET NOCOUNT ON;
SET @Phrase = (SELECT Phrase FROM tblSpecialStuff)

UPDATE tblReceipts SET

PaymentDate=@PaymentDate,
TenderTypeID=@TenderTypeID,
AmountPaid=@PaymentAmount,
CreditCardType=@CreditCardType,
ExpirationDate=@ExpirationDate,
ccNumber = (CASE
WHEN @CreditCardType > 0 THEN
EncryptByPassPhrase(@Phrase,@ccNumber)
ELSE @ccNumber
END)
END 

View 1 Replies View Related

Using A Parameter In A Stored Procedure

Dec 23, 2003

hi,

i need to run a stored procedure from an asp page that will import data into a temporary table.

i am having trouble with passing the parameter from the Exec command (currently developing in QA). It works ok where i want to use a value such as 'PC', or 'printer' or 'laptop' where the import sql has a where clause of '... = <<parameter>>...'.

I want however to be able to pass it a value such as ['printer','laptop','pc']so that the procedure will collect the data into the temp table with an '...in <<parameter>>'

this falls over and retrieves zero rows. the parameter is passed to the sp correctly as i have used a print command to display it, it comes back as 'pc',printer','laptop'. It appears to be the ' in ' that is not parsing the parameter.

can anyone help please?

TIA

View 3 Replies View Related

Parameter To Stored Procedure

Sep 3, 1999

Hello!

I have a problem when i want to construct a stored procedure. I want to pass a parameter with customerid's. The problem is that I donīt know how many customerId's I send to the stored procedure, because the user select the customerId's from a selectbox. The SQL string I want to excute looks like this

SELECT CustomerName FROM Customer WHERE CustomerId IN (199, 301, 408... )

How should I create the stored procedure so I put the customerId values in a parameter, like the procedure below? (I know that this not will work)

CREATE PROCEDURE rpt_PM2000_test(@SCustomerId varChar)
AS
BEGIN
SELECT Customer
FROM Customer
WHERE CustomerId IN (@sCustomerId)
END

/Fredrik

View 3 Replies View Related

Stored Procedure Parameter

Apr 22, 2007

In the Create portion of a stored procedure, does the line in bold mean
that if the parameter @RegoDate is not provided, then use the current datetime?

CREATE PROCEDURE spGetRegoDetail
@Owner nvarchar(20),
@RegoDate datetime = getdate
AS ...

Thanks in advance,
G11DB

View 2 Replies View Related

Stored Procedure Parameter

Mar 4, 2004

Hi,

I have an Access front end/MSDE2000 backend system. There is a form that has a subform which is based on the results of a stored procedure. Both my subform and mainform contain intOrderID ( which is the field i want to link both of them with ).

I would like to have the subform's data updated when changing records in the mainform. In Access, this was simple ( link child and master fields ), but not with MSDE2000 as a back end ( ADO connection thing...)

I think i have to create something to filter the results of the stored procedure, but i dont like the fact the the entire dataset needs to be transmitted over the network ( most of the time, users will only be creating new records, or reviewing recently created ones )

I'm really not sure as to how to accomplish this... Any ideas?

thanks!

View 10 Replies View Related

Parameter For Stored Procedure

Jun 16, 2007

HiI'm trying to alter my stored procedure to take a parameter for theDatabase Name, but as usual the syntax is killing me.Thanks for any helpDennis'--------------------------------------------------------------------------*--------------------------------Before - This Works without a paramater'--------------------------------------------------------------------------*--------------------------------set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[sp_CreateNewClientDb]ASCREATE DATABASE [MyClientDatabase] ON PRIMARY( NAME = N'MyClientDatabase',FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDATAMyClientDatabase.mdf' ,SIZE = 11264KB ,MAXSIZE = UNLIMITED,FILEGROWTH = 1024KB )LOG ON( NAME = N'MyClientDatabase_log',FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDATAMyClientDatabase_log.ldf' ,SIZE = 1024KB ,MAXSIZE = 2048GB ,FILEGROWTH = 10%)COLLATE SQL_Latin1_General_CP1_CI_AS'--------------------------------------------------------------------------*--------------------------------After - This Doesn't work with a parameter'--------------------------------------------------------------------------*--------------------------------set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[sp_CreateNewClientDb]ASCREATE DATABASE @ClientDBName ON PRIMARY( NAME = N@ClientDBName,FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDATA@ClientDBName' + '.mdf' ,SIZE = 11264KB ,MAXSIZE = UNLIMITED,FILEGROWTH = 1024KB )LOG ON( NAME = N'@ClientDBName' + '_log',FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDATA@ClientDBName' + '_log.ldf' ,SIZE = 1024KB ,MAXSIZE = 2048GB ,FILEGROWTH = 10%)COLLATE SQL_Latin1_General_CP1_CI_ASMsg 102, Level 15, State 1, Procedure sp_CreateNewClientDb, Line 4Incorrect syntax near '@ClientDBName'.

View 5 Replies View Related

Stored Procedure With A Parameter

Dec 7, 2007

Hi all,

I'm trying to create a SP with a parameter for server.



CREATE PROCEDURE dbo.sp_Testing
@server ???????



AS

SELECT * from @server

GO


Does anyone know how to do what i'm trying here..???
I don't know the type of the parameter...is it just simply server? or is there another specific name for it???
thanks,

View 6 Replies View Related

Return Stored Procedure Parameter

Apr 27, 2007

I'm attempting to return a value from my stored procedure. Here is my button click event that runs the stored procedure. protected void btn_Move_Click(object sender, EventArgs e)
{
/*variables need for the update of the old staff record
and the creation of the new staff record.
*/
BMSUser bmsUser = (BMSUser)Session["bmsUser"];
int staffid = Convert.ToInt32(bmsUser.CmsStaffID);
int oldStaffProfileID = Convert.ToInt32(Request.QueryString["profileid"]);
string newManager = Convert.ToString(RadComboBox_MangersbyOrg.Text);
int newMangerProfileID = Convert.ToInt32(RadComboBox_MangersbyOrg.Value);


SqlConnection conn = new SqlConnection(connect);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_BMS_MoveStaffMember";
cmd.Parameters.Add("@OldStaffProfileID", SqlDbType.Int);
cmd.Parameters["@OldStaffProfileID"].Precision = 9;
cmd.Parameters["@OldStaffProfileID"].Scale = 0;
cmd.Parameters["@OldStaffProfileID"].Value = oldStaffProfileID;
cmd.Parameters.Add("@NewManagerProfileID", SqlDbType.Int);
cmd.Parameters["@NewManagerProfileID"].Precision = 9;
cmd.Parameters["@NewManagerProfileID"].Scale = 0;
cmd.Parameters["@NewManagerProfileID"].Value = newMangerProfileID;
cmd.Parameters.Add("@LastModifiedBy", SqlDbType.Int);
cmd.Parameters["@LastModifiedBy"].Precision = 9;
cmd.Parameters["@LastModifiedBy"].Scale = 0;
cmd.Parameters["@LastModifiedBy"].Value = staffid;

SqlParameter sp = new SqlParameter("@OLDManagerReturn", SqlDbType.Int);
sp.Direction = ParameterDirection.Output;
cmd.Parameters.Add(sp);

lbl_ERROR.Text = Convert.ToString(sp);

conn.Open();
SqlTransaction trans = conn.BeginTransaction();
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
conn.Close();
 My parameter sp is coming back null for some reason. Am I not setting the parameter correctly? I set a break point and it says the value is null.
 Here is the parts of my stored proc returning the value...
DECLARE @OLDManagerReturn numeric(9)
SELECT @OLDManagerReturn =  ManagerProfileID FROM tblBMS_Staff_rel_Staff WHERE StaffProfileID = @OldStaffProfileID AND Active=1 AND BudgetYear = @BudgetYear
RETURN @OLDManagerReturn

View 5 Replies View Related

How Do I Specify Varchar(max) Stored Procedure Parameter ?

Jun 19, 2007

In the SqlDbType enumeration there is no value for the new (max) types, only varchar.  If Im passing a large string, it will get cut off at 8K.  So how do I specify my varchar parameter as being of the max type ?  

View 1 Replies View Related

Default Value Of Stored Procedure Parameter

Jul 20, 2007

 Hi,This works:CREATE PROCEDURE MyProc    @Date smalldatetime = '2005-01-01'AS... But this does not CREATE PROCEDURE MyProc    @Date smalldatetime = GETDATE()AS... I'm talking about sql2005. Can anyone help how to overcome this? 

View 3 Replies View Related







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