Returning A String From A Sproc
Sep 8, 2006
I am having problems returning a string from a sproc. What I want to do is to be able to create an XML Doc and return it. I have tried the following:
select
@error = @@error,
@row_count = @@rowcount
set @outgoingXML = '<return_code><error_code>' + Convert(varchar, @error) + '</error_code><row_count>' + Convert(varchar, @row_count) + '</row_count></return_code>'
return @outgoingXML
where @outgoingXML is an output parameter. And I have tried:
set @serilializedReturnCode = '<return_code><error_code>' + Convert(varchar, @error) + '</error_code><row_count>' + Convert(varchar, @row_count) + '</row_count></return_code>'
return @serilializedReturnCode
where @serilializedReturnCode is locally declared as varchar(250)
This is the error I get:
Syntax error converting the varchar value '<return_code><error_code>0</error_code><row_count>0</row_count></return_code>' to a column of data type int.
View 15 Replies
ADVERTISEMENT
Nov 28, 2006
I'm still having issues with this despite my attempts to resolve. I even
have "with exec as dbo" in my sproc, and and "exec as dbo" in my execution,
but still the encrypted data returns nulls when I exec as a user other than
DBO. Below is precisely what I have done. All ideas are welcomed.
TIA, ChrisR
--If there is no master key, create one now
IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD =
'23987hxJKL95QYV4369#ghf0%94467GRdkjuw54ie5y01478d Dkjdahflkujaslekjg5k3fd117
r$$#1946kcj$n44ncjhdlj'
GO
CREATE CERTIFICATE HumanResources037
WITH SUBJECT = 'Employee Social Security Numbers';
GO
CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE HumanResources037;
GO
USE [AdventureWorks];
GO
-- Create a column in which to store the encrypted data
ALTER TABLE HumanResources.Employee
ADD EncryptedNationalIDNumber varbinary(128);
GO
-- Open the symmetric key with which to encrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
-- Encrypt the value in column NationalIDNumber with symmetric
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
NationalIDNumber);
GO
-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
GO
-- Now list the original ID, the encrypted ID, and the
-- decrypted ciphertext. If the decryption worked, the original
-- and the decrypted ID will match.
alter procedure getDecryptedIDNumber
with exec as owner
as
SELECT NationalIDNumber, EncryptedNationalIDNumber
AS "Encrypted ID Number",
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS "Decrypted ID Number"
FROM HumanResources.Employee;
GO
/*works for me, shows the decrypted data*/
exec getDecryptedIDNumber
USE [master]
GO
CREATE LOGIN [test] WITH PASSWORD=N'test',
DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [AdventureWorks]
GO
CREATE USER [test] FOR LOGIN [test]
GO
use [AdventureWorks]
GO
GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test]
GO
GRANT IMPERSONATE ON USER:: dbo TO test;
GO
/*Now, open up a "file/new/DB Engine Query" and login with the test login*/
exec as user = 'dbo'
exec getDecryptedIDNumber
/*This returns NULL values where it should show the decrypted data*/
View 1 Replies
View Related
Jul 23, 2005
Heres my problem, the first part selects a row from the database, ifthere is no row with the criteria it inserts a row and then returns it,the problem is the IF statement that inserts the row, never returns theselect after it. if there is a row initially in the database, itreturns the right information, I just can't get it to return the rowafter inserting it. Anyone know what the problem could be?Stored procedure:ALTER PROCEDURE dbo.CheckCurrentPayPeriod(@UserID varchar(50))ASBEGIN-- This SP checks to see if the current PayPeriod exists,-- if not it will create the payperiod for them and returnDECLARE @appStartDate DATETIMEDECLARE @dt DATETIMEDECLARE @rows intSET @appStartDate = (SELECT PayPeriodStart FROM PayPeriodStart)SET @dt = GETDATE()SELECTUserIDFROMPayPeriodWHERE(PeriodStart <= CONVERT(varchar(10), @dt, 101)) AND (PeriodEnd >=CONVERT(varchar(10), @dt, 101)) AND (UserID = @UserID)-- Inserts their new PayPeriodDECLARE @PayPeriodID intif (@@ROWCOUNT = 0)BEGINDECLARE @sDate datetimeDECLARE @eDate datetimeSET @sDate = @appStartDateSET @eDate = DATEADD(day, 13, @sDate)INSERT INTOPayPeriod(UserID, PeriodStart, PeriodEnd)VALUES(@UserID, @sDate, @eDate)/*EXEC @PayPeriodID = InsertPayPeriod @UserID, @sDate, @eDate*/SET @PayPeriodID = @@IDENTITYSELECT * FROM PayPeriod WHERE PayPeriodID = @PayPeriodIDRETURNENDelseRETURNEND
View 1 Replies
View Related
Mar 6, 2015
We have a service written in c# that is processing packages of xml that contain up to 100 elements of goods consignment data.
In amongst that element is an identifier for each consignment. This is nvarchar(22) in our table. I have not observed any IDs that are different in length in the XML element.
The service picks up these packages from MSMQ, extracts the data using XPATH and passes the ID into the SPROC in question. This searches for the ID in one of our tables and returns a bool to the service indicating whether it was found or not. If found then we add a new row to another table. If not found then it ignores and continues processing.
The service seems to be dealing with a top end of around 10 messages a minute... so a max of about 1000 calls to the SPROC per minute. Multi-threading has been used to process these packages but as I am assured, sprocs are threadsafe.It is completing the calls without issue but intermittently it will return FALSE. For these IDs I am observing that they exist on the table mostly (there are the odd exceptions where they are legitimately missing).e.g Yesterday I was watching the logs and on seeing a message saying that an ID had not been found I checked the database and could see that the ID had been entered a day earlier according to an Entered Timestamp.
USE [xxxxxxxxxx]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[code]....
So on occasions (about 0.33% of the time) it is failing to get a bit 1 setting in @bFound after the SELECT TOP(1).
change @pIdentifier nvarchar(25) to nvarchar(22)
Trim any potential blanks from either side of both parts of the identifier comparison
Change the SELECT TOP(1) to an EXISTS
The only other thought is the two way parameter direction in the C# for the result OUTPUT. I have been unable to replicate this using a test app and our test databases. Have observed selects failing to find even though the data is there, like this before?
View 1 Replies
View Related
Jul 24, 2004
Hello,
We are creating an app to search through products. On the presentation layer, we allow a user to 'select' categories (up to 10 check boxes). When we get the selected check boxes, we create a concatenated string with the values.
My question is: when I pass the concatenated string to the SPROC, how would I write a select statement that would search through the category field, and find the values in the concatenated string?
Will I have to create Dynamic SQL to do this?...or... can I do something like this...
@ConcatenatedString --eg. 1,2,3,4,5,6,7
SELECT col1, col2, col3 FROM TABLE WHERE CategoryId LIKE @ConcatenatedString
Thanks for your help.
View 2 Replies
View Related
Sep 22, 2005
Hi. I have data which comes as a string like"... Store #61"but sometimes it is"... Store 61"and sometimes it is"... Store 061"For three digits, it might be "... Store 561" or "... Store #561", or"... Store 0561".....The only thing I can be sure of is that the last 2 or 3 (significant)digits of this field represent the StoreNumber.I have to link this table on field StoreNumber with another table wherethe data is ALWAYS like 0061, 0561, etc, so always four digits, paddedwith zeroes.I'd like to use the equivalent of the VB functionFormat(StoreNumber), "0000"), but Format does NOT exist in TSQL.How could I solve this problem ? Please bear with me - I'm a beginnerin SQL...Thank you very muchAlex.
View 5 Replies
View Related
Jul 20, 2005
I want to do something like this in a stored proc:------Create Procedure dbo.GetPatients@PatientIdList varchar(200) -- comma separated list of PatientIDsAsSelect *From PatientsWhere PatientId In (@PatientIdList)------I know the above won't work, but of course what I want is if@PatientIdList = '1,2,3' then I want Patient records with PatientIds1, 2, and 3 returned.It looks like the only way to do this is to build the SQL statement asa string within the stored procedure ... which pretty much defeats theusefulness of using precompiled sprocs as I understand it (better offbuilding a dynamic query against a View in that case).Thoughts?Joel Thornton ~ <groups@joelpt.eml.cc>
View 1 Replies
View Related
Jul 29, 2014
I need to create a TSQL to return MAX(Value) removing the first part and last part.
Example
I have these Varchar Reference code:
1.00001-Q1
2.00100-Q2
3.00005-Q4
I need to cut the string to find the max number excluding (Part1):
1.
2.
3.
And also excluding (Part3):
-Q1
-Q2
-Q4
In this case converting varchar to INT, the correct value that i want is: 00101
The middle part excluding (Part1 and Part3)
Then my final reference could be:
1.00101-Q1
or
2.00101-Q2
or
3.00101-Q4
View 3 Replies
View Related
Jul 23, 2005
I need to write a query that tells me which string values are empty orblank in a table.Is it possible to return the length of the string contained in characterfield?
View 3 Replies
View Related
Jun 19, 2014
i need to cut my string on 3 portion, for exemple my string is :
a) 1.9999-Q1
b) 01.9999-Q11
I need to keep all values before "." (point), and all values after "-" , and also all values between "." and "-", like this:
a) 1
9999
Q1
b) 01
9999
Q11
View 9 Replies
View Related
Apr 24, 2008
I have a field that contains text strings that always follow this syntax "ABC:DE:comment follow". I want to return a new field with only whatever is between the two : (in this case DE but it could be more). It would be perfect if all the field follow that syntax, however, I have variations where users put spaces either before or after the :. Can the spaces be ignored?
Thank you for any help.
View 4 Replies
View Related
Jul 13, 2004
I need to know how to figure out the number of times a character occurs within a string, in SQL.
For example, "happy" concerning "p" would be evaluated to 2.
View 2 Replies
View Related
May 15, 2008
I am using a SQLDataSource with the following Select query. If the "spouse" values are not in the database, I get the HTML non-breaking-space character back, rather than an empty string.
SelectCommand="select applicant_id, (applicant_last_name + ', ' + applicant_first_name) as applicant_name, CONVERT(varchar(10), applicant_dob, 101) as applicant_dob, applicant_ssn, (spouse_last + ', ' + spouse_first) as spouse_name, CONVERT(varchar(10), spouse_dob, 101) as spouse_dob, spouse_ssn from applicant where applicant_last_name like '%'+@last_name+'%' order by applicant_last_name"
Here is the relevant code-behind:
TextBox tb = (TextBox)formView.FindControl("SpouseName");
tb.Text = e.Item.Cells[4].Text;
DatePicker dp = (DatePicker)formView.FindControl("SpouseDateOfBirth");
if (e.Item.Cells[5].Text.Length > 0)
{
try // this is a try/catch because nbsp is not parsed as a date
{
dp.DateValue = DateTime.Parse(e.Item.Cells[5].Text);
}
catch
{
dp.txtDate.Text = "";
}
}
tb = (TextBox)formView.FindControl("SpouseSocialSecurityNumber");
tb.Text = e.Item.Cells.Text;
Thoughts?
Todd
View 4 Replies
View Related
Mar 16, 2007
Hi folks,Can anyone enlighten me here? I'm trying to use a SPROC which, when supplied with an int, looks up the table and returns certain columns from it. I'm using a SqlCommand, here's my codebehind: ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SqlCommand dataSource = new SqlCommand("retrieveData", new SqlConnection(dbConnString)); dataSource .CommandType = CommandType.StoredProcedure; dataSource .Parameters.AddWithValue("id", poid); dataSource .Parameters.AddWithValue("title", title).Direction = ParameterDirection.Output; dataSource .Parameters.AddWithValue("creator", creator).Direction = ParameterDirection.Output; dataSource .Parameters.AddWithValue("assignee", assignee).Direction = ParameterDirection.Output; etc, etc... And the SPROC:------------------------------------------------------------------------------------------------------------------set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[retrieveData] @id int, @title varchar(50) OUTPUT, @creator varchar(50) OUTPUT, @assignee varchar(50) OUTPUT, @contact varchar(50) OUTPUT, @deliveryCost numeric(18,2) OUTPUT, @totalCost numeric(18,2) OUTPUT, @status tinyint OUTPUT, @project smallint OUTPUT, @supplier smallint OUTPUT, @creationDateTime datetime OUTPUT, @amendedDateTime datetime OUTPUT, @locked bit OUTPUT AS /**SET NOCOUNT ON; **/ SELECT [title] AS [@title], [datetime] AS [@creationDateTime], [creator] AS [@creator], [assignee] as [@assignee], [supplier] as [@supplier], [contact] AS [@contact], [delivery_cost] AS [@deliveryCost], [total_cost] AS [@totalCost], [amended_timestamp] AS [@amendedDateTime], [locked] AS [@locked] FROM purchase_orders WHERE [id] = @id; ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ The id being passed in is definately not null, and is set to a value of an item I know exists. The resulting error is:
Exception Details: System.InvalidOperationException: String[1]: the Size property has an invalid size of 0.Line 63: retrievePODetails.Connection.Open();Line 64: retrievePODetails.ExecuteNonQuery();[InvalidOperationException: String[1]: the Size property has an invalid size of 0.] System.Data.SqlClient.SqlParameter.Validate(Int32 index) +717091... ... Can anyone see anything I'm missing? Thanks,Ally
View 1 Replies
View Related
Feb 13, 2007
I have attached the results of checking an Update sproc in the Sql database, within VSS, for a misbehaving SqlDataSource control in an asp.net web application, that keeps telling me that I have too many aurguments in my sproc compared to what's defined for parameters in my SQLdatasource control.....
No rows affected.
(0 row(s) returned)
No rows affected.
(0 row(s) returned)
Running [dbo].[sp_UPD_MESample_ACT_Formdata]
( @ME_Rev_Nbr = 570858
, @A1 = No
, @A2 = No
, @A5 = NA
, @A6 = NA
, @A7 = NA
, @SectionA_Comments = none
, @B1 = No
, @B2 = Yes
, @B3 = NA
, @B4 = NA
, @B5 = Yes
, @B6 = No
, @B7 = Yes
, @SectionB_Comments = none
, @EI_1 = N/A
, @EI_2 = N/A
, @UI_1 = N/A
, @UI_2 = N/A
, @HH_1 = N/A
, @HH_2 = N/A
, @SHEL_1 = 363-030
, @SHEL_2 = N/A
, @SUA_1 = N/A, @SUA_2 = N/A
, @Cert_Period = 10/1/06 - 12/31/06
, @CR_Rev_Completed = Y ).
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[sp_UPD_MESample_ACT_Formdata].
The program 'SQL Debugger: T-SQL' has exited with code 0 (0x0).
And yet every time I try to update the record in the formview online... I get
Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
I have gone through the page code with a fine tooth comb as well as the sproc itself. I have tried everything I can think of, including creating a new page and resetting the fields, in case something got broken that I can't see.
Does anyone have any tips or tricks or info that might help me?
Thanks,
SMA49
View 3 Replies
View Related
Apr 23, 2004
I'm sorta new with using stored procedures and I'm at a loss of how to achieve my desired result.
What I am trying to do is retrieve a value from a table before it is updated and then use this original value to update another table. If I execute the first called sproc in query analyzer it does return the value I'm looking for, but I'm not really sure how to capture the returned value. Also, is there a more direct way to do this?
Thanks,
Peggy
Sproc that is called from ASP.NET:
ALTER PROCEDURE BP_UpdateLedgerEntry
(
@EntryLogID int,
@ProjectID int,
@NewCategoryID int,
@Expended decimal(10,2)
)
AS
DECLARE@OldCategoryID int
EXEC @OldCategoryID = BP_GetLedgerCategory @EntryLogID
UPDATE
BP_EntryLog
SET
ProjectID = @ProjectID,
CategoryID = @NewCategoryID,
Expended = @Expended
WHERE
EntryLogID = @EntryLogID
EXEC BP_UpdateCategories @ProjectID, @NewCategoryID, @Expended, @OldCategoryID
Called Sprocs:
*********************************************
BP_GetLedgerCategory
*********************************************
ALTER PROCEDURE BP_GetLedgerCategory
(
@EntryLogID int
)
AS
SELECT CategoryID
FROM BP_EntryLog
WHERE EntryLogID = @EntryLogID
RETURN
*********************************************
BP_UpdateCategories
*********************************************
ALTER PROCEDURE BP_UpdateCategories
(
@ProjectID int,
@NewCategoryID int,
@Expended decimal(10,2),
@OldCategoryID int
)
AS
UPDATE
BP_Categories
SET CatExpended = CatExpended + @Expended
WHERE
ProjectID = @ProjectID
AND
CategoryID = @NewCategoryID
UPDATE
BP_Categories
SET CatExpended = CatExpended - @Expended
WHERE
ProjectID = @ProjectID
AND
CategoryID = @OldCategoryID
View 2 Replies
View Related
Jan 20, 2004
create procedure dbo.GetZipID( @City varchar(30), @State char(2), @Zip5 char(6))
as
DECLARE @CityID integer
declare @StateID integer
declare @ZipID integer
set @ZipID=2
set @Zip5=lTrim(@Zip5)
if @Zip5<>''
SET @ZIPID = (select Min(lngZipCodeID) AS ZipID from ZipCodes where strZipCode=@Zip5)
if @ZipID is null
set @CityID= EXEC GetCityID(@City);
set @StateID= EXEC GetStateID(@State);
insert into ZipCodes(strZipCode,lngStateID,lngCityID) values(@Zip5,@StateID,@CityID)
if @@ERROR = 0
SET @ZIPID = @@Identity
select @ZIPID
GetCityID and GetStateID are two stored procs, how do I execute those two stored procs
in the above stored proc? I mean what is the syntax??
Tks
View 2 Replies
View Related
Sep 25, 2007
I am opening a simple command against a view which joins 2 tables, so that I can return a column which is defined as a tinyint in one of the tables. The SELECT looks like this:
SELECT TreatmentStatus FROM vwReferralWithAdmissionDischarge WHERE ClientNumber = 138238 AND CaseNumber = 1 AND ProviderNumber = 89
The TreatmentStatus column is a tinyint. When I execute that above SQL SELECT statement in SQL Server Management Studio (I am using SQL Server 2005) I get a value of 2. But when I execute the same SQL SELECT statement as a part of a SqlDataReader and SqlCommand, I get a return data type of integer and a value of 1.
Why?
View 5 Replies
View Related
Jan 10, 2007
I hvae a stored procedure that has this at the end of it:
BEGIN
EXEC @ActionID = ActionInsert '', @PackageID, @AnotherID, 0, ''
END
SET NOCOUNT OFF
SELECT Something
FROM Something
Joins…..
Where Something = Something
now, ActionInsert returns a Value, and has a SELECT @ActionID at the end of the stored procedure.
What's happening, if that 2nd line that I pasted gets called, 2 result sets are being returned. How can I modify this SToredProcedure to stop returning the result set from ActionINsert?
View 2 Replies
View Related
Jan 25, 2008
This sproc seems to be way over my head. First off, let's start with the scenario. I have two tables. tblInventory and tblTempCart. Each contain an ItemID and Quantity. I need an sproc that will loop through the rows in tblTempCart and sum the quantity of each ItemID. Then, it needs to update the quantity in tblInventory based on what has been ordered for that ItemID.
What I have tried thus far:
UPDATE dbo.[4HCamp_tblStoreInventory]SET Quantity = Quantity - (SELECT SUM(dbo.[4HCamp_tblStoreTempCart].Quantity) AS Quantity FROM dbo.[4HCamp_tblStoreTempCart] WHERE dbo.[4HCamp_tblStoreTempCart].ItemID = dbo.[4HCamp_tblStoreInventory].ItemID)
This works other than if the ItemID doesn't exist in tblTempCart, then it updates the quantity in tblInventory to NULL instead of retaining it's current value. I have no experience with looping in sql so any help will be greatly appreciated.
Thanks!
Amanda
View 1 Replies
View Related
May 2, 2008
I am trying to design a stored procedure to list out all of the unique software items that have been approved. There are multiple tables involved: CISSoftware, Software, Manufacturers, SoftwareTypes. Despite putting DISTINCT, I am still receiving rows of records where the software title (the title field) is a duplicate. Why is this query not working? Am I overlooking something?
SELECT DISTINCT CISSoftware.SoftwareID, Software.Title, Manufacturers.ManufacturerID, Manufacturers.ManufacturerName, SoftwareTypes.SoftwareTypeID, SoftwareTypes.Type
FROM CISSoftware, Software, Manufacturers, SoftwareTypes
WHERE CISSoftware.SoftwareID = Software.SoftwareID
AND Software.ManufacturerID = Manufacturers.ManufacturerID
AND Software.SoftwareTypeID = SoftwareTypes.SoftwareTypeID
View 4 Replies
View Related
Apr 10, 2004
hi all,
I'm trying to learn using sproc in ASP.NET, but ran into problems I couldn't solve. Here're the details
My Table (JournalArticle)
ArticleID - int (PK)
ArticleTitle - varchar
ArticleContent - text
I could run a normal sql string against the table itself in ASP.NET and got the results I expect.
but when using a sproc, i couldn't get anything
The sproc
CREATE PROCEDURE dbo.sp_ArticleSearch(@srch text)
AS SELECT ArticleID, ArticleTitle, ArticleContent
FROM dbo.JournalArticle
WHERE (ArticleAbstract LIKE @srch)
GO
After reading some of the threads here, I experimented by changing ArticleContent and @srch to type varchar, still no luck, it's not returning anything.
I think the problem is when i set the value of @srch (being new at this, I could be seriously wrong though), like this:
prmSearch.ParameterName = "@srch"
prmSearch.SqlDbType = SqlDbType.Text
prmSearch.Value = Request.Form("txtSearch")
My original string looks like this
strSQL = "SELECT * FROM JournalArticle WHERE (ArticleContent LIKE '%" & Request.Form("txtSearch") & "%')"
What am I doing wrong?? Thanks in advance for any help.
View 7 Replies
View Related
Dec 2, 2004
I have tried to mix this around every way I can think of but the procedure inserts two rows instead of one. You will notice that I specify two commands/sprocs. I did that as part of my trying everything. when it was one command/sproc it did the same thing... What am I doing wrong? Please Help! :)
___________________
SPROC:
___________________
CREATE PROCEDURE dbo.sp_addMembershipRole
@INCID Int
AS
declare @literal NVarChar (10)
SET @literal = 'RTRListing'
INSERT INTO dbo.RTR_memberPermissions ([memberID], [Role])
VALUES (@INCID, @literal)
GO
___________________
CODE:
___________________
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Security.Cryptography;
using System.Web.Security;
namespace admin
{
/// <summary>
/// Summary description for createMembership.
/// </summary>
public class createMembership : System.Web.UI.Page
{
protected System.Web.UI.WebControls.RequiredFieldValidator Vusername;
protected System.Web.UI.WebControls.RequiredFieldValidator Vpassword;
protected System.Web.UI.WebControls.RequiredFieldValidator Vretype;
protected System.Web.UI.WebControls.TextBox username;
protected System.Web.UI.WebControls.TextBox password;
protected System.Web.UI.WebControls.TextBox retype;
protected System.Web.UI.WebControls.Label lblError;
protected System.Web.UI.WebControls.Label lblDate;
protected System.Web.UI.WebControls.Button btnAdd;
private void Page_Load(object sender, System.EventArgs e)
{
if(! Page.IsPostBack)
{
string StrContactID = Request.QueryString["CID"].ToString();
Session["CID"]= StrContactID;
}
}
#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.btnAdd.Click += new System.EventHandler(this.btnAdd_Click);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
public void btnAdd_Click(object sender, System.EventArgs e)
{
int intContactID;
string StrCID = Session["CID"].ToString();
intContactID= Int32.Parse(StrCID);
if(password.Text != retype.Text)
{
lblError.Text = "Retyping of your desired password did not match. Please try again.";
return;
}
string pwdSalt = CreateSalt(5);
string pwd = password.Text;
string pwdHash = CreatePasswordHash(pwd, pwdSalt);
string connStr= "server=****; uid=****; pwd=****; database=****";
string cmdStr= "sp_addMembershipUandP";
string cmd2Str= "sp_addMembershipRole";
SqlConnection CONN = new SqlConnection(connStr);
CONN.Open();
SqlCommand cmd = new SqlCommand(cmdStr, CONN);
cmd.CommandType = CommandType.StoredProcedure;
SqlCommand cmd2= new SqlCommand(cmd2Str, CONN);
cmd2.CommandType = CommandType.StoredProcedure;
SqlParameter param;
param = cmd.Parameters.Add("@username", SqlDbType.NVarChar, 50);
param.Value = username.Text;
param = cmd.Parameters.Add("@password", SqlDbType.NVarChar, 50);
param.Value = pwdHash;
param = cmd.Parameters.Add("@salt", SqlDbType.NVarChar, 50);
param.Value = pwdSalt;
param = cmd.Parameters.Add("@CID", SqlDbType.Int, 4);
param.Value = intContactID;
SqlParameter param2;
param2 = cmd2.Parameters.Add("@INCID", SqlDbType.Int, 4);
param2.Value = intContactID;
cmd.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
CONN.Close();
return;
}
private static string CreateSalt(int size)
{
RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider();
byte[] buff = new byte[size];
rng.GetBytes(buff);
return Convert.ToBase64String(buff);
}
private static string CreatePasswordHash(string pwd, string salt)
{
string saltAndPwd = String.Concat(pwd, salt);
string hashedPwd = FormsAuthentication.HashPasswordForStoringInConfigFile(saltAndPwd, "SHA1");
return hashedPwd;
}
}
}
View 2 Replies
View Related
Jan 22, 2004
create procedure
GetAddress(@Addr1 varchar(40), @Addr2 varchar(40), @City varchar(30), @State char(2), @Zip5 char(6), @Zip4 smallint)
as
begin
declare @ZipID integer
declare @AddrID integer
set @AddrID=1
if lTrim(@Addr1)<>''
EXEC @ZipID= dbo.GetZipID(@City,@State,@Zip5)
set @AddrID = (select Min(lngAddrID) from dbo.Addrs where lngZipCodeID=@ZipID and Address1=@Addr1 and Address2=@Addr2)
return(@AddrID)
end
GO
In the above sproc I m trying to call another sproc GetZipID . Its giving me an error stating that
"Incorrect syntax near @City. "
Can you help me out? The same syntax works for passing one variable but not for three.
FYI this is the other sproc
CREATE PROCEDURE dbo.GetZipID(@City varchar(30), @State char(2), @Zip5 char(6))
AS
BEGIN
DECLARE @CityID integer
DECLARE @StateID integer
DECLARE @ZipID integer
SET @ZipID=2
set @Zip5=lTrim(@Zip5)
if @Zip5<>''
SET @ZIPID = (select Min(lngZipCodeID) AS ZipID from ZipCodes where strZipCode=@Zip5)
if @ZipID is null
EXEC @CityID = dbo.GetCityID @City
EXEC @StateID = dbo.GetStateID @State
insert into ZipCodes(strZipCode,lngStateID,lngCityID) values(@Zip5,@StateID,@CityID)
if @@ERROR = 0
SET @ZIPID = @@Identity
Return @ZipID
print @ZIPID
END
GO
Thx in advance
View 1 Replies
View Related
May 17, 2004
ha ha ,
havent used sql serv in years, and having trouble wit me sprocet! What devilish deed did i do?
Use Contacts
GO
CREATE PROC MaserIn
@entreddatetime= GETDATE(),
@initialsvarchar(2)=Null,
@Vendorvarchar(50),
@CkNovarchar(20),
@expTypevarchar(15),
@ckDatedatetime,
@ckAmtmoney,
@mIdintOUTPUT
AS
INSERT INTO [Contacts].[dbo].[Master](
[entered], [initials], [Vendor],
[CkNo], [expType], [ckDate], [ckAmt])
VALUES(@entered,
@initials,
@Vendor,
@CkNo,
@expType,
@ckDate,
@ckAmt)
SELECT @mID=@@iDENTITY
View 6 Replies
View Related
Nov 28, 2007
Hi.
I wanted to know if its possible to do this in a sproc.
if you want to hide the column that has no data, I suggest you to handle these works in your data accessing modular. For example, if you check one of your column is empty, just remove the column in your record set, so the column would not show in the report.
If yes how can i do it..
Any help will be appreciated.
Regards
Karen
View 8 Replies
View Related
Dec 19, 2007
Hi, I am trying to Implement Multi parameter...
If i give NULL it works fine but if i give '7,4' I get this error message Msg 102, Level 15, State 1, Line 18 Incorrect syntax near '17'.
This is my sproc
ALTER Procedure [dbo].[usp_GetOrdersByOrderDate]
@ClientId nvarchar(max)= NULL,
@StartDate datetime,
@EndDate datetime
AS
Declare @SQLTEXT nvarchar(max)
If @ClientId IS NULL
Begin
Select
o.OrderId,
o.OrderDate,
o.CreatedByUserId,
c.LoginId,
o.Quantity,
o.RequiredDeliveryDate,
cp.PlanId,
cp.ClientPlanId
FROM
[Order] o
Inner Join ClientPlan cp on o.PlanId = cp.PlanId
Inner Join ClientUser c on o.CreatedByUserId = c.UserId
WHERE
--cp.ClientId = @ClientId
--AND
o.OrderDate BETWEEN @StartDate AND @EndDate
ORDER BY
o.OrderId DESC
END
ELSE
BEGIN
SELECT @SQLTEXT = 'Select
o.OrderId,
o.OrderDate,
o.CreatedByUserId,
c.LoginId,
o.Quantity,
o.RequiredDeliveryDate,
cp.PlanId,
cp.ClientPlanId
FROM
[Order] o
Inner Join ClientPlan cp on o.PlanId = cp.PlanId
Inner Join ClientUser c on o.CreatedByUserId = c.UserId
WHERE
cp.ClientId in (' + @ClientId + ')
AND
o.OrderDate BETWEEN ' + Convert(varchar,@StartDate) + ' AND ' + convert(varchar, @EndDate) + '
ORDER BY
o.OrderId DESC'
execute (@SQLTEXT)
END
any help will be appreciated.
Regards
Karen
View 4 Replies
View Related
Jul 5, 2004
hi all
i need to design a SPROC which will return me top n rows from a table.
like GetTopN 4, will give me top 4 tuples
/**
GetTopN
To list top n rows
Date - 05 July 2004
Yogesh Jangam
*/
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'GetTopN' AND type = 'P')
DROP PROCEDURE GetTopN
GO
/************************ Actual SP Code *********************************/
Create procedure GetTopN (@intN int)
AS
BEGIN
SET NOCOUNT ON
declare
SELECT TOP @intN from Employee -- this part i am not able to write
end
is there a answer?
Thanks
View 2 Replies
View Related
Sep 21, 2005
How can I determine when a sproc or table was last used?
I suspect that I have many obsolete tables and sprocs in my database but how can I find out for sure??
Thanks,
DL
View 5 Replies
View Related
May 14, 2006
I wrote the following SPROC and it works the first time i run it. But if I attempt to run it again I get the following T-SQL Error: "There is not enough memory to complete the task. Close down some operations and try again". Then the app closes. Any ideas?
Here is my complete code:
USE IADATA
IF EXISTS (select * from syscomments where id = object_id ('TestSP'))
DROP PROCEDURE TestSP
GO
CREATE PROCEDURE TestSP
/*Declare Variables*/
@ListStr varchar(100) /*Hold Delimited String*/
AS
Set NoCount On
DECLARE@ListTbl Table (InvUnit varchar(50)) /*Creates Temp Table*/
DECLARE@CP int /*Len of String */
DECLARE @SV varchar(50) /*Holds Result */
While @ListStr<>''
Begin
Set @CP=CharIndex(',',@ListStr) /*Sets length of words - Instr */
If @CP<>0
Begin
Set @SV=Cast(Left(@ListStr,@CP-1) as varchar) /*Copies Portion of String*/
Set @ListStr=Right(@ListStr,Len(@ListStr)-@CP) /*Sets up next portion of string*/
End
Else
Begin
Set @SV=Cast(@ListStr as varchar)
Set @ListStr=''
End
Insert into @ListTbl Values (@SV) /*Inserts variable into Temp Table*/
End
Select InvUnit From @ListTbl LT
INNER Join dbo.Incidents ST on ST.Inv_Unit=LT.InvUnit
and my VB6 Code:
Dim adoConn As ADODB.Connection
Dim adoCmd As ADODB.Command
Dim adoRS As ADODB.Recordset
Dim strLegend As String
Dim strData As String
Set adoConn = New ADODB.Connection
adoConn.Open connString
Set adoRS = New ADODB.Recordset
Set adoCmd = New ADODB.Command
With adoCmd
Set .ActiveConnection = adoConn
.CommandText = "TestSP"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ListStr", adVarChar, adParamInput, 100)
.Parameters("ListStr").Value = "Unit 41,Unit 32,Unit 34,Unit 54"
Set adoRS = .Execute
Do While Not adoRS.EOF
Debug.Print adoRS.Fields(0).Value
adoRS.MoveNext
Loop
End With
Set adoCmd = Nothing
adoRS.Close
Set adoRS = Nothing
Set adoCmd = Nothing
adoConn.Close
Set adoConn = Nothing
End Sub
Any ideas?
Thanks
View 2 Replies
View Related
Sep 23, 2006
I'm optimizing an Access mdb to run in front of a SS 2005 database. My approach is to move as much of the processing as possible out of Access and in to SQL Server.
I have a report uses an Access query as it's source. One field in that report is generated via a series of 4 or 5 sub-queries that are finally joined in to the report's source query.
I have enough knowhow to turn each individual Access query into a veiw inside SQL server, but I'm wondering if this wouldn't be better accomplished using a stored procedure?
Essentially, I'd need the sproc to open up a set of 50-60 records, loop through them until it finds the first record with certain criteria, then return a certain value as it's result. Finally, I need that vallue to be joined to a view that I will point to as the source for my report in Access.
Is it possible to do this with a sproc? Is this the right way to use a sproc?
View 9 Replies
View Related
Mar 11, 2008
Hi SQLersI'm in an unusual position of using a SQL box I'm not an admin of. To make it worse, it is SQL 2k. I haven't used this in anger for over a year. I don't even have the 2k tools installed and I'm just using SSMS.sp_adduser ain't working. I've nosed around in master and the reason why is somebody, for some reason, has created a sproc called (you guessed it) master.dbo.sp_adduser.I can't poke around as I would like and I don't have a 2k instance to bugger about with. Correct me if I am wrong but if there is some user defined master.dbo.sp_adduser sproc then there can't be a system sproc to add users? CREATE USER is SQL 2k5 too right? Users do get created by EM by the team managing the box so what command does this use? Anyone know or (flutters eyelashes) fancy running a profiler trace on EM to see?I want to convert my SQL 2k5 security objects script for SQL 2k. I can create the objects in SSMS so not critical."Overriding" system sprocs is not something I would ever do and I have no equivalent testing environment so any help highly appreciatedCheers
View 12 Replies
View Related
Jan 20, 2004
CREATE proc dbo.sp_address ( @Abbr char(2) )
as
DECLARE @StateID int
SET @Abbr = UPPER(ISNULL( @Abbr, '' ))
SET @StateID = ( SELECT MIN(lngStateID) FROM dbo.States where strAbbr = @Abbr )
set @StateID=53
IF ( @StateID is null )
INSERT into dbo.States( strAbbr, strName ) VALUES( @Abbr, @Abbr )
if @@ERROR = 0
SET @StateID = @@Identity
return(@StateID)
GO
Can I execute the above stored procedure in a function like this:
create function
sf_GetStateID( @Abbr char(2))
returns integer
begin
declare @StateID int
exec sp_address
return(@StateID)
end
I just want to cross check.
Tks
View 1 Replies
View Related
