All I Want To Do Is Feed A Varchar Into A Stored Procedure And Get An Id Back... What Am I Doing Wrong?
Dec 28, 2006
So I have been building stored procedures and things were working fine until I hit something that I am sure is incredibly simple to do; however, i have having a hell of a time with it. Here is the code:
#############################################
ALTER PROCEDURE dbo.GetUserIdForUser
@username NVARCHAR
AS
BEGIN
DECLARE @postedbyid UNIQUEIDENTIFIER
SET @postedbyid = (SELECT UserId
FROM aspnet_Users
WHERE (UserName = @username))
END
###Which returns this###
Running [dbo].[GetUserIdForUser] ( @username = jason ).
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[GetUserIdForUser].
#############################################
This is part of a much larger stored procedure, but this is the point of failure in the stored procedure I am trying to build. If anyone can tell me what I am doing wrong I would appreciate it. I have tried a few things that have resulted in different failures. If I remove any references to variables (delete SET @postedbyid = and replace @username with 'jason') I can get it to return a result. If I put @username in though it doesn't work. Here are the examples of those:
ALTER PROCEDURE dbo.GetUserIdForUser
@username NVARCHAR
AS
BEGIN
SELECT UserId
FROM aspnet_Users
WHERE (UserName = @username)
END
###Which returns this###
Running [dbo].[GetUserIdForUser] ( @username = jason ).
UserId
--------------------------------------
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[GetUserIdForUser].
Here is a sanity check to show that the data is in fact in the database:
ALTER PROCEDURE dbo.GetUserIdForUser
AS
BEGIN
SELECT UserId
FROM aspnet_Users
WHERE (UserName = 'jason')
END
Running [dbo].[GetUserIdForUser].
UserId
--------------------------------------
No rows affected.
(1 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[GetUserIdForUser].
Anyone have any ideas on what I am doing wrong?
View 13 Replies
ADVERTISEMENT
Jul 20, 2005
I have two SQL Server stored procedures, PROC1 and PROC2. PROC1 hasabout 50 input parameters. PROC2 is the main procedure that does somedata modifications and afterwards calls PROC1 using an EXECUTEstatement.The input parameter values for PROC1 are stored in a table in mydatabase. What I like to do is passing those values to PROC1 using aSELECT statement. Currently, all 50 parameters are read and stored ina variable, and afterwards they are passed to PROC1 using:EXEC spPROC1 @var1, @var2, @var3, ... , @var50Since it is a lot of code declaring and assigning 50 variables, I waswondering if there is a possibility to run a statement like:EXEC spPROC1 (SELECT * FROM myTable WHERE id = 2)Any help on this is greatly appreciated!
View 1 Replies
View Related
Apr 6, 2004
please take a look at this strored proxedure and tell what is wrong with it?
CREATE PROCEDURE sp_Pictures_CreateTextPost
@UserID Int,
@Country varchar (2),
@Description varchar (1000),
@PostID int output
AS
BEGIN TRANSACTION
INSERT INTO Pictures_TextPosts
(UserID, Country, Description)
VALUES
(@UserID, @Country, @Description)
IF @@ERROR <> 0
ROLLBACK TRANSACTION
ELSE
BEGIN
COMMIT TRANSACTION
UPDATE Accounts_Users SET Count_InsertText = Count_InsertText + 1 Where UserID = @UserID
SET @PostID = @@IDENTITY
RETURN 1
END
i run this sp on MS SQL server on my local machine and it works fine. now i move the same sp to the host server and it no longer works. it is the problem with this sp as i run sql query to test it. i use the ASP.NET Enterprise Manager to insert this sp. can someone please help?
one hint: on the host server, if i add 'GO' to the end of the sp, error occured.
View 6 Replies
View Related
Apr 8, 2004
I have written an ASP.net app that iterates through the controls on my page if the are checkboxlist or radiobuttonlist controls, the id and value are serialized into an xml file which is sent to a SQL 2000 stored procedure where the xml data is inserted into a couple of tables.
I captured the xml that is being sent:
<?xml version="1.0"?>
<Schema7Ihsurveyresponse xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" surveyid="1" Userid="14">
<ihsresponsedetail question="Q1" response="0" result="1" />
<ihsresponsedetail question="Q2" response="0" result="1" />
<ihsresponsedetail question="Q3" response="0" result="1" />
<ihsresponsedetail question="Q5" response="0" result="1" />
<ihsresponsedetail question="Q6" response="0" result="1" />
<ihsresponsedetail question="Q7" response="0" result="1" />
<ihsresponsedetail question="Q8" response="0" result="1" />
<ihsresponsedetail question="Q9" response="0" result="1" />
<ihsresponsedetail question="Q10" response="0" result="1" />
<ihsresponsedetail question="Q11" response="0" result="1" />
<ihsresponsedetail question="Q12" response="0" result="1" />
<ihsresponsedetail question="Q13" response="0" result="1" />
<ihsresponsedetail question="Q14" response="0" result="1" />
<ihsresponsedetail question="Q15" response="0" result="1" />
<ihsresponsedetail question="Q17" response="0" result="1" />
<ihsresponsedetail question="Q18" response="0" result="1" />
<ihsresponsedetail question="Q19" response="0" result="1" />
<ihsresponsedetail question="Q20" response="0" result="1" />
<ihsresponsedetail question="Q21" response="0" result="1" />
<ihsresponsedetail question="Q23" response="0" result="1" />
<ihsresponsedetail question="Q24" response="0" result="1" />
<ihsresponsedetail question="Q25" response="0" result="1" />
<ihsresponsedetail question="Q26" response="0" result="1" />
<ihsresponsedetail question="Q27" response="0" result="1" />
<ihsresponsedetail question="Q28" response="0" result="1" />
<ihsresponsedetail question="Q29" response="0" result="1" />
<ihsresponsedetail question="Q30" response="0" result="1" />
<ihsresponsedetail question="Q31" response="0" result="1" />
<ihsresponsedetail question="Q32" response="0" result="1" />
<ihsresponsedetail question="Q33" response="0" result="1" />
<ihsresponsedetail question="Q34" response="0" result="1" />
<ihsresponsedetail question="Q35" response="0" result="1" />
<ihsresponsedetail question="Q36" response="0" result="1" />
<ihsresponsedetail question="Q37" response="0" result="1" />
<ihsresponsedetail question="Q38" response="0" result="1" />
<ihsresponsedetail question="Q39" response="0" result="1" />
<ihsresponsedetail question="Q40" response="0" result="1" />
</Schema7Ihsurveyresponse>
and here is my stored procedure:
CREATE PROCEDURE Insertresponsedetails
@XML TEXT
AS
SET NOCOUNT ON
SET XACT_ABORT ON
-- Access is not restricted.
insert into tempxml ([xml]) values (@xml)
DECLARE @idoc INT, @responseid int
EXECUTE sp_xml_preparedocument @idoc OUTPUT, @XML, '<ns xmlns:a="http://interhealthusa.com/surveyresponse.xsd"/>'
if @@error<>0
begin
select(-1)
return
end
BEGIN TRANSACTION
insert into ihsurveyresponse (userid, Surveyid, Responsedate)
select userid, surveyid, getdate()
from openxml(@idoc, '/schema7ihsurveyresponse', 1)
with (userid int, surveyid int)
if @@error<>0
begin
rollback transaction
select 2
return
end
set @responseid = scope_identity()
INSERT INTO IHSResponsedetail (Question,Result, Response)
SELECT Question, Result, @responseid
FROM OPENXML (@idoc, '/schema7ihSurveyResponse/ihsresponsedetail')
WITH ( [Question] char(10) '@Question', Result int '@Result')
EXECUTE sp_xml_removedocument @idoc
if @@error<>0
begin
rollback transaction
select 3
return
end
COMMIT TRANSACTION
select @responseid
GO
If anyone has any insight I would really appreciate the assistance.
View 1 Replies
View Related
Sep 20, 2001
Hiya, all!
I've got this stored procedure:
CREATE Procedure CheckReOccurences
@PatID INT, @CPT CHAR(7),@DOS DATETIME,@DRecur SMALLINT
AS
DECLARE @DaysElapsed INT
SET @DaysElapsed = (SELECT DATEDIFF(day,ISNULL((SELECT MAX(ServiceDate) FROM VouchersDetail WHERE PatID = @PatID AND ProcCode = @CPT),01/01/1900),@DOS))
IF @DaysElapsed < @DRecur --Patient has had procedure done too recently
BEGIN
PRINT 'Too Recent-Only ' + str(@dayselapsed) + ' days elapsed'
RETURN 0
END
ELSE --More time has elapsed since procedure last performed than is necessary
BEGIN
PRINT 'Not Too Recent- ' + str(@dayselapsed) + ' days elapsed'
RETURN 1
END
And when I run it with the exec command in Query Analyzer, filling in all parameters with proper values, I get this error message:
Server: Msg 8114, Level 16, State 4, Procedure CheckReOccurences, Line 0
Error converting data type nvarchar to datetime.
Please note Line says 0, when no such line exists. I've tried running every single part separately in QA, and all runs well (the functions work, returns expected code, etc.). What's the problem?
Thanks,
Sarah
View 4 Replies
View Related
Jun 11, 2004
I am having trouble getting this procedure to properly enter the data into a table. It works when I do not add the @ToName info and only ask to insert the AccountID and FromName data. When i add the @ToNamevariable, no data is inserted. I think it might have to do with the select statement. I have two TerritoryID, RegionID, DivisionID, and EmpID because I want this information to be entered for both the FromName and the ToName selected. Does anyone know what my problem is? Id appreciate any help.
CREATE PROCEDURE InsertAllThree
@AccountID char(10),
@FromName nvarchar(50),
@ToName nvarchar(50)
AS
insert into accounttransferstestmike
(AccountID, FromTerritoryID, FromRegionID, FromDivisionID, FromEmpID, ToTerritoryID, ToRegionID, ToDivisionID, ToEmpID)
select Accounts.AccountID, Territories.TerritoryID, Regions.RegionID, Divisions.DivisionID, Employees.EmployeeID, Territories.TerritoryID, Regions.RegionID, Divisions.DivisionID, Employees.EmployeeID
From Accounts, EndoscopySqlUser.Territories INNER JOIN
EndoscopySqlUser.Regions ON EndoscopySqlUser.Territories.RegionName = EndoscopySqlUser.Regions.Region INNER JOIN
EndoscopySqlUser.Employees ON EndoscopySqlUser.Territories.TerritoryID = EndoscopySqlUser.Employees.TerritoryID INNER JOIN
EndoscopySqlUser.Divisions ON EndoscopySqlUser.Regions.Division = EndoscopySqlUser.Divisions.DivisionID
Where (Accounts.AccountID = @AccountID) and (EndoscopySqlUser.Employees.DateLeft IS NULL) AND (EndoscopySqlUser.Territories.TerritoryName=@FromN ame) AND (EndoscopySqlUser.Territories.TerritoryName=@ToNam e)
GO
View 4 Replies
View Related
Oct 31, 2006
When I try to create the stored procedure below, I receive the following error. Can someone please let me know what is wrong with the code? The problem seems to be with the default parameter of getdate(). This is because when I comment the default parameter, it works fine. Please note that I need the @BeginDate parameter to be varchar(10). I cannot have it as datetime.
Msg 156, Level 15, State 1, Procedure test, Line 4
Incorrect syntax near the keyword 'convert'.
Msg 137, Level 15, State 2, Procedure test, Line 8
Must declare the scalar variable "@BeginDate".
/* Stored Procedure*/
Create PROCEDURE test --test
@BeginDate varchar(10) = convert(varchar(10),getdate(),101)
as
select convert(datetime,@BeginDate)
Thanks!
View 9 Replies
View Related
Jan 24, 2008
Hi All,
Appreciate any help that you can offer, I have three tables that need data entered when the user completes a web form, in short the tables are
dbo.JBAClient
Which has
JBCID as PK and other data
dbo.JBAEmployee
Which has
JBEID as PK and JBEClientID as a Foreign Key and other data
dbo.JBAdvert
Which has
JBAID as PK, JBAEmployeeID as Foreign Key , JBAClientID as Foreign Key and other data,
The stored procedure that I've tried to write is supposed to create a new client record and enter all of the relevant data into the client table (store the JBCID) then Create a new Employee Record, insert all relevant data including (JBCID into JBEClientID) and (Store both JBCID & JBEID). Finally create a new advert record and store all of the relevant data including (JBCID as JBAClientID and JBEID as JBAEmployeeID)
The stored procedure that I have written works fine until the last section, where instead of inserting JBCID into JBAClientID and JBEID into JBAEmployeeID, it inserts JBEID into JBAClientID and JBEID into JBAEmployeeID
So inserting JBEID twice and ignoring JBEClientID........
the code is below,---
CREATE PROCEDURE PayPalCLient
@siteid int,
@companyname nvarchar(50),
@address nvarchar(500),
@phone nvarchar(50),
@fax nvarchar(50),
@email nvarchar(225),
@url nvarchar(225),
@companytype nvarchar(50),
@billingcontact nvarchar(50),
@name nvarchar(50),
@AccountType nvarchar(50),
@PASSWORD nvarchar(50),
@AccountLive nvarchar(50),
@EmployeeLevel nvarchar(50),
@adverttitle nvarchar(50),
@description nvarchar(500),
@category nvarchar(50),
@location nvarchar(50),
@payrate nvarchar(50),
@employmenttype nvarchar(50),
@reference nvarchar(50),
@startdate nvarchar(50),
@postfor INT,
@invoiced nvarchar(50),
@notified nvarchar(50),
@ppclient nvarchar(50),
@pppaid nvarchar(50)
AS
Declare @NewID INT
INSERT INTO dbo.JBClient(JBCLSiteID, JBCLName, JBCLAddress, JBCLPhone, JBCLFax, JBCLEmail, JBCLCompanyType, JBCLURL, JBCLAccountType, JBCLAccountlive, JBCLBillingContact)
VALUES (@siteid, @companyname, @address, @phone, @fax, @email, @companytype, @url, @AccountType, @AccountLive, @billingcontact)
DECLARE @NewID2 INT
SELECT @NewID = SCOPE_IDENTITY()
INSERT INTO dbo.JBEmployee(JBEClientID, JBESiteID, JBEName, JBELevel, JBEUsername, JBEPassword, JBEAddress, JBEPhone)
VALUES (@NewID, @siteid, @name, @EmployeeLevel, @email, @PASSWORD, @address, @phone)
SELECT @NewID = SCOPE_IDENTITY()
SELECT @NewID2 = SCOPE_IDENTITY()
INSERT INTO dbo.JBAdvert(JBAClientID, JBAEmployeeID, JBASiteID, JBATitle, JBADescription, JBACategory, JBALocation, JBAPayRate, JBAEmplymentType, JBAReference, JBAStartDate, JBAPostFor, JBAInvoiced, JBANotified, JBAPPClient, JBAPPPaid)
VALUES (@NewID, @NewID2, @siteid, @adverttitle, @description, @category, @location, @payrate, @employmenttype, @reference, @startdate, @postfor, @invoiced, @notified, @ppclient, @pppaid)
Appreciate any help that you can offer, I think the problem has something to do with --
SELECT @NewID = SCOPE_IDENTITY()
SELECT @NewID2 = SCOPE_IDENTITY()
Thanks again
View 3 Replies
View Related
Jul 25, 2006
I have the following stored proceduredrop procedure ce_selectCity;set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGO-- =============================================-- Author: <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- =============================================create PROCEDURE ce_selectCity @recordCount int output -- Add the parameters for the stored procedure here --<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2,, 0>AS declare @errNo int -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here select ciId,name from ce_city order by name select @recordCount = @@ROWCOUNT select @errNo = @@ERROR if @errNo <> 0 GOTO HANDLE_ERROR return @errNoHANDLE_ERROR: Rollback transaction return @errNoGoand i was just testing it likeProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load db.connect() Dim reader As SqlDataReader Dim sqlCommand As New SqlCommand("ce_selectCity", db.getConnection) Dim recordCountParam As New SqlParameter("@recordCount", SqlDbType.Int) Dim errNoParam As New SqlParameter("@errNo", SqlDbType.Int) recordCountParam.Direction = ParameterDirection.Output errNoParam.Direction = ParameterDirection.ReturnValue sqlCommand.Parameters.Add(recordCountParam) sqlCommand.Parameters.Add(errNoParam) reader = db.runStoredProcedureGetReader(sqlCommand) If (db.isError = False And reader.HasRows) Then Response.Write("Total::" & Convert.ToInt32(recordCountParam.Value) & "<br />") While (reader.Read()) Response.Write(reader("ciId") & "::" & reader("name") & "<br />") End While End If db.close() End SubIt returns ALL ROWS (5 in the table right now). So, recordCount should be 5. (When i run it inside SQL Server (directly) it does return 5, so i know its working there).BUT, its returning 0.What am i doing wrong??EDIT:Oh, and this is the function i use to execute stored procedure and get the readerPublic Function runStoredProcedureGetReader(ByRef sqlCommand As SqlCommand) As SqlDataReader sqlCommand.CommandType = CommandType.StoredProcedure Return sqlCommand.ExecuteReader End Function
View 5 Replies
View Related
Jun 23, 2004
Hi all.
1. I have a stored procedure, the procedure need create new category for forums, each category have their own place (what going first , what going second etc...), it's importent for me that all categories will be in property for them place, so i must read next place from table, and if not then i set it = 0 . But it's seems not working, i getting error.
Can somebody tell me please what wrong ?
ALTER PROCEDURE dbo.CreateCategory
(
@category_name varchar(100)
)
AS
declare @place int
/* setting place to zero */
SET @place = 0
/* trying to get last place and set value into @place */
SET @place = (Select max(category_place) FROM fo_categories)
/* if got nothing from table then setting value of first category */
IF (@place = 0)
set @place = 1
INSERT fo_categories (category_name, category_place)
VALUES(@category_name, @place)
RETURN
Running dbo."CreateCategory" ( @category_name = Public forums ).
Cannot insert the value NULL into column 'category_place', table 'mg_forum.dbo.fo_categories'; column does not allow nulls. INSERT fails.
2. I also would ask what SET NOCOUNT ON/OFF mean and for what i can use it ?
View 2 Replies
View Related
Nov 4, 2004
Hello everyone,
I've been trying to use a stored procedure to return the names of some temporary tables that i put in the tempdb table in SQL Server.
--I've been getting the following error in visual basic 6 when i try to call this:
run-time error '-2147217900 (80040e14)': syntax access violation
--This is the error you get when you try to just run the code in query analyzer:
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation
--what code i was trying to use (in query analyzer):
{call EXEC CreateTempTables (@RQSodfil = 'a', @BulkRan = 'a', @BulkFor = 'a', @BulkJit = 'a', @ID = '0', @RQSodfilFlag = '1', @BulkRanFlag = '0', @BulkForFlag = '0', @BulkJitFlag = '0', @DeleteFlag = '0', @ErrorNum = '0')}
--code that i was trying to use in vb 6:
Public Sub TemporaryTables( _
ByVal bytRQSodfilFlag As Byte, _
ByVal bytBulkRanFlag As Byte, _
ByVal bytBulkForFlag As Byte, _
ByVal bytBulkJitFlag As Byte, _
ByVal bytDeleteFlag As Byte, _
ByVal cnPlant As String)
Dim objConn As ADODB.Connection
Dim objCmd As ADODB.Command
Dim objRQSodfil As Parameter
Dim objBulkRan As Parameter
Dim objBulkFor As Parameter
Dim objBulkJit As Parameter
Dim objParamID As Parameter
Dim objRQSodfilFlag As Parameter
Dim objBulkRanFlag As Parameter
Dim objBulkForFlag As Parameter
Dim objBulkJitFlag As Parameter
Dim objDeleteFlag As Parameter
Dim objErrorNum As Parameter
Dim intErrorNum As Integer
' setup command variable
Set objCmd = New ADODB.Command
Set objConn = New ADODB.Connection
objConn.Open cnPlant
objCmd.CommandText = "EXEC CreateTempTables"
objCmd.CommandType = adCmdStoredProc
objCmd.ActiveConnection = objConn
' setup parameters
Set objRQSodfil = objCmd.CreateParameter("@RQSodfil", adVarChar, adParamInputOutput, 20, "a")
objCmd.Parameters.Append objRQSodfil
Set objBulkRan = objCmd.CreateParameter("@BulkRan", adVarChar, adParamInputOutput, 20, "a")
objCmd.Parameters.Append objBulkRan
Set objBulkFor = objCmd.CreateParameter("@BulkFor", adVarChar, adParamInputOutput, 20, "a")
objCmd.Parameters.Append objBulkFor
Set objBulkJit = objCmd.CreateParameter("@BulkJit", adVarChar, adParamInputOutput, 20, "a")
objCmd.Parameters.Append objBulkJit
Set objParamID = objCmd.CreateParameter("@ID", adChar, adParamInputOutput, 2, 0)
objCmd.Parameters.Append objParamID
Set objRQSodfilFlag = objCmd.CreateParameter("@RQSodfilFlag", adTinyInt, adParamInput, , bytRQSodfilFlag)
objCmd.Parameters.Append objRQSodfilFlag
Set objBulkRanFlag = objCmd.CreateParameter("@BulkRanFlag", adTinyInt, adParamInput, , bytBulkRanFlag)
objCmd.Parameters.Append objBulkRanFlag
Set objBulkForFlag = objCmd.CreateParameter("@BulkForFlag", adTinyInt, adParamInput, , bytBulkForFlag)
objCmd.Parameters.Append objBulkForFlag
Set objBulkJitFlag = objCmd.CreateParameter("@BulkJitFlag", adTinyInt, adParamInput, , bytBulkJitFlag)
objCmd.Parameters.Append objBulkJitFlag
Set objDeleteFlag = objCmd.CreateParameter("@DeleteFlag", adTinyInt, adParamInput, , bytDeleteFlag)
objCmd.Parameters.Append objDeleteFlag
Set objErrorNum = objCmd.CreateParameter("@ErrorNum", adInteger, adParamInputOutput, , 0)
objCmd.Parameters.Append objErrorNum
' execute command
Set rsTableInfo = objCmd.Execute(, , adExecuteRecord)
' find returned parameters
gstrRQSodfilName = rsTableInfo.Fields("@RQSodfil")
gstrBulkRanName = rsTableInfo.Fields("@BulkRan")
gstrBulkForName = rsTableInfo.Fields("@BulkFor")
gstrBulkJitName = rsTableInfo.Fields("@BulkJit")
gstrID = rsTableInfo.Fields("@ID")
intErrorNum = rsTableInfo.Fields("@ErrorNum")
End Sub
any help would be appreciated
View 1 Replies
View Related
Oct 9, 2006
I am trying to get data back from stored procedure output arguments. I am in a Visual C++ enviroment calling the stored procedure via the ODBC function SQLExecDirect. When the stored procedure has only a single output argument, everything works fine. When there are multiple output arguments, I get an error "Invalid Cursor State' from the SQLFetch. This is telling me there is no result set.
Below is a pseudo code fragment which shows what I am doing.
I want to use SQLGetData to convert the data into C variables. I do not want to use SQLBindParameter or Parameter Markers.
Sql = "DECLARE @outarg1 int
DECLARE @outarg2 datetime
{CALL ProcName('Inarg1','Inarg2',.......@outarg1 OUTPUT,@outarg2 OUTPUT ) }
SELECT @outarg1,@outarg2 " ;
rc = SQLExecDirect(hstmt,Sql,strlen(Sql) )
if( rc == SQL_SUCCESS)
{
rc == SQLFetch(hstmt);
if(rc == SQL_ERROR)
{
SqlGetDiagRec(.........);
}
}
.....
When there is one out parameter, everything works OK.
When there are multiple out parameters, I get a SQL error state 24000 which is "Invalid Cursor State". This tells me there is no result set for the Fetch to act on.
The stored procedure is doing a select for each out parameter.
Any Idea why I get an error when using more than one out parameter
View 6 Replies
View Related
Mar 21, 2008
Hi,
I was wondering if anyone could offer me some advice. I am currently using a stored procedure to insert records into a database. I want to be able to retrieve the ID (primar key) from the item that has just been inserted using the stored procedure. The ID I want to get back is Meter_ID
This is my stored procedure:ALTER PROCEDURE dbo.quote
(@Business_Name nvarchar(50),
@Business_Type nvarchar(50),@Contact_Title nchar(10),
@Contact_First_Name nvarchar(50),@Contact_Last_Name nvarchar(50),
@Address_Line_1 nvarchar(MAX),@Address_Line_2 nvarchar(MAX),
@City nvarchar(MAX),@Postcode nchar(7),
@Tel_No nchar(11),@E_mail_Address nvarchar(50),
@Distributor_ID int,@Profile_Class int,
@Meter_Time_Code int,@Line_Loss_Factor int,
@Unique_Identifier1 int,@Unique_Identifier2 int,
@Check_Digit int,@Tariff nchar(20),
@UnitRate1AnnualUsage nchar(10),@UnitRate2AnnualUsage nchar(10),
@UnitRate1SubTotal money,@UnitRate2SubTotal money,
@QuoteTotal money
)
ASINSERT INTO client_details (Business_Name, Business_Type, Contact_Title, Contact_First_Name, Contact_Last_Name, Address_Line_1, Address_Line_2, City, Postcode, Tel_No, email_Address)VALUES (@Business_Name, @Business_Type,@Contact_Title, @Contact_First_Name, @Contact_Last_Name, @Address_Line_1, @Address_Line_2, @City, @Postcode, @Tel_No, @E_mail_Address)
DECLARE @Client_ID INTSET @Client_ID = scope_identity()
INSERT INTO meter_quote (Client_ID, Tariff, Meter_Distributor_ID, Meter_Profile_Class, Meter_Time_Code, Meter_Line_Loss_Factor, Unique_Identifier1, Unique_Identifier2, Check_Digit, UnitRate1AnnualUsage, UnitRate2AnnualUsage, UnitRate1SubTotal, UnitRate2SubTotal, QuoteTotal)VALUES (@Client_ID, @Tariff, @Distributor_ID, @Profile_Class, @Meter_Time_Code, @Line_Loss_Factor, @Unique_Identifier1, @Unique_Identifier2, @Check_Digit, @UnitRate1AnnualUsage, @UnitRate2AnnualUsage, @UnitRate1SubTotal, @UnitRate2SubTotal, @QuoteTotal)
RETURN
And this is the code I have in my asp page:<asp:SqlDataSource ID="SqlDataSource3" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
InsertCommand="quote" InsertCommandType="StoredProcedure">
<InsertParameters><asp:ControlParameter ControlID="TextBoxBusinessName" DefaultValue=""
Name="Business_Name" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="DropDownBusinessType" Name="Business_Type"
PropertyName="SelectedValue" Type="String" /><asp:ControlParameter ControlID="DropDownListTitle" Name="Contact_Title"
PropertyName="SelectedValue" Type="String" /><asp:ControlParameter ControlID="TextBoxFirstName" Name="Contact_First_Name"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxLastName" Name="Contact_Last_Name"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxAddressLine1" Name="Address_Line_1"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxAddressLine2" Name="Address_Line_2"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxAddressLine3" Name="City"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxPostcode" Name="Postcode"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxTelNo" Name="Tel_No"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxEmail" Name="E_mail_Address"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxDistributorID" Name="Distributor_ID"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxProfileClass" Name="Profile_Class"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxMeterTimeCode" Name="Meter_Time_Code"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxLineLossFactor" Name="Line_Loss_Factor"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxUniqueIdentifier1" Name="Unique_Identifier1"
PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="TextBoxUniqueIdentifier2" Name="Unique_Identifier2"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="TextBoxCheckDigit" Name="Check_Digit"
PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="LabelTariff2" Name="Tariff"
PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="TextBoxUnitRate1Usage" Name="UnitRate1AnnualUsage"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="LabelUnitRate2Usage" Name="UnitRate2AnnualUsage"
PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="LabelUnitRate1Total" Name="UnitRate1SubTotal"
PropertyName="Text" Type="Decimal" /><asp:ControlParameter ControlID="LabelUnitRate2Total" Name="UnitRate2SubTotal"
PropertyName="Text" Type="Decimal" /><asp:ControlParameter ControlID="LabelQuoteTotal" Name="QuoteTotal"
PropertyName="Text" Type="Decimal" />
</InsertParameters>
</asp:SqlDataSource>
And the following in the C# code:
try
{
SqlDataSource3.Insert();//Insert quote details into the database using a stored procedure
}catch (Exception ex)
{LabelInsertException.Text = "Failed" + ex.Message;
}
Any help would be much appreciated
Thanks, Hayley
View 6 Replies
View Related
Apr 18, 2008
Hi,
I have a ms access application with a button. Clicking this button runs a sql server 2000 stored procedure. The stored procedure updates product information. When I click the button it updates the products fine. When the users (with lesser permissions than me) click the button, no error message is sent but the transactions are rolled back. Does anyone know why or how this could happen? Please let me know before I pull out the last strands of hair from my head.
Thank you,
Anand
View 4 Replies
View Related
May 24, 2007
Apologies if this post doesn't belong here.
We are in the process of moving steps from a large SQL job to a web portal. The intent here is to allow the user to manually trigger the jobs to run. The problem I am having is in determining a way to run these job steps in the background, thus allowing the browser to take control while the step runs. These jobs can take enough time that forcing the browser to wait is not an option.The steps have been consolidated into stored procedures so in order to run a step, one would call the sproc. What I would like to do is run this in a separate thread and let that thread end on its own while the server transfers back to the client. Is something like this possible? My understanding is that the thread would be orphaned once the browser takes control again. Thanks in advance for any advice.
View 9 Replies
View Related
Apr 7, 2006
Hi,
How to call a stored procedure on MS Sqlserver from web forms and get back output
Thanx,
Nishu
View 1 Replies
View Related
Feb 25, 2008
I have a table that has 5 values that I want to MSSQL to create a csv file for. After it is created, dump that csv file back into the database as a binary file. Is this possible?
View 1 Replies
View Related
Sep 22, 2005
oConn = New SqlClient.SqlConnection
oConn.ConnectionString = "user id=MyUserID;data source=MyDataSource;persist security info=False;initial catalog=DBname;password=password;"
oCmd = New SqlClient.SqlCommand
oCmd.Connection = oConn
oCmd.CommandType = CommandType.StoredProcedure
oCmd.CommandText = "TestStdInfo"
'parameters block
oParam1 = New SqlClient.SqlParameter("@intSchoolID", Me.ddlSchl.SelectedItem.ToString())
oParam1.Direction = ParameterDirection.Input
oParam1.SqlDbType = SqlDbType.Int
oCmd.Parameters.Add(oParam1)
oParam2 = New SqlClient.SqlParameter("@dob", Convert.ToDateTime(Me.txbBirth.Text))
oParam2.Direction = ParameterDirection.Input
oParam2.SqlDbType = SqlDbType.DateTime
oCmd.Parameters.Add(oParam2)
oParam3 = New SqlClient.SqlParameter("@id", Me.txbID.Text)
oParam3.Direction = ParameterDirection.Input
oParam3.SqlDbType = SqlDbType.VarChar
oCmd.Parameters.Add(oParam3)
oConn.Open()
daStudent = New SqlClient.SqlDataAdapter("TestStdInfo", oConn)
dsStudent = New DataSet
daStudent.Fill(dsStudent) 'This line is highlighted when error happens
oConn.Close()The error I am getting :Exception Details: System.Data.SqlClient.SqlException: Procedure 'TestStdInfo' expects parameter '@intSchoolID', which was not supplied.I am able to see the value during debugging in the autos or command window. Where does it dissapear when it comes to Fill?Could anybody help me with this, if possible fix my code or show the clean code where the procedure called with multiple parameters and dataset filled.Thank you so much for your help.
View 2 Replies
View Related
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
Apr 26, 2007
Hi All,
I have a Stored procedure as below..
create procedure sp_sample
@name varchar(12)
as
begin
set @name = (select name from mytable where id = 1)
select @name
end
how can i return the varchar value from the above Stored procedure?
I want to capture it in SQLFetch ODBC call.
Thanks in advance!!
vishu
Bangalore
View 16 Replies
View Related
Mar 3, 2008
I'm having the following (abbreviated) stored procedure:
Code Snippet
CREATE PROCEDURE proc_SomeSmartName @SomeVariable VARCHAR AS
BEGIN SELECT COUNT(ID) AS SomeLabel, SomeField
FROM SomeTable
GROUP BY SomeField
HAVING SomeField = @SomeVariable
END
Now my problem: It doesn't seem to work if I give the @SomeParameter a string to work with, neither via SqlCommandObject nor directly in the Management Studio. The following returns zero rows:
Code Snippet
DECLARE @return_value int
EXEC @return_value = [dbo].[proc_SomeSmartName]
@SomeVariable = 'MyText'
SELECT 'Return Value' = @return_value
Funny enough, when I have the following query, it works perfectly:
Code Snippet
SELECT COUNT(ID) AS SomeLabel, SomeField
FROM SomeTable
GROUP BY SomeField
HAVING SomeField = 'MyText'
Returning one row as it should. SomeField is an NVarChar field, but I tried casting it to VarChar without any benefit, and I also supplied the parameter as NVarChar to test, both without further success. And 'MyText' does exist in the database, in both cases when I run the stored procedure and when I run the SQL statement directly.
What am I doing wrong?
View 4 Replies
View Related
Aug 16, 2007
I have a stored procedure which returns a count of products and a limited number of rows from a query.
I am using SQL Server 2005 and calling the procedure in asp.net
The procedure is as follows
Code Snippet
GO
ALTER PROCEDURE [dbo].[GetProductsByCategoryId]
@Category VARCHAR(255),
@Range INT,
@PageIndex INT,
@NumRows INT,
@CategoryName nvarchar(255) OUTPUT,
@CategoryProductCount INT OUTPUT
AS
BEGIN
/*
Get product count
*/
SELECT @CategoryProductCount=(SELECT COUNT(*) FROM Products LEFT JOIN tblVar on Products.ProductID = tblVar.prodidvar WHERE Products.Category=@Category AND Products.Range=@Range)
/*set row variables*/
Declare @startRowIndex INT;
set @startRowIndex =(@PageIndex * @NumRows) + 1;
/* get full list of products */
With ProductEntries as (
SELECT ROW_NUMBER() OVER (ORDER BY Products.ProductID, tblVar.idvar ASC) as Row, field1, field2
FROM Products LEFT JOIN tblVar on Products.ProductID=tblVar.prodidvar
WHERE Range=@Range
AND Category = @Category
)
/*get only needed rows */
SELECT field1, field2
FROM ProductEntries
WHERE Row Between
@startRowIndex and @startRowIndex+@NumRows-1
END
The problem seems to be with the line
AND Category = @ Category
in the query to make the ProductEntries
If I take this query and run it in an SQL pane I need to enclose the argument for @Category in single quotes.
If I try to do this in the procedure it simply searchs for @Category as a string rather than the value of @Category.
The query returns and displays results with no problems without this line, and also if it is returning a result set that has no values in tblVar to join to.
Also if I run the query on just the Products table removing the left join it will return results with no problems.
Thanks to anyone who can help!
And I apologise if it is something simple but asp and SQL Server is not my usual coding platform.
View 7 Replies
View Related
Mar 12, 2008
I have this sql statement in a stored procedure
SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' + stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN ' + @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot
in the statement, where @sql is defined as DECLARE @sql varchar(Max). the problem is that this statement produces results that are in excess of 8000 characters and the results are truncated. Is there anyway to avoid this? I know that it's not possible to user ntext/text as a local variable, and if i try to return the result as an ouput paramater, only the first result is returned.
my code is based off of this article http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables
Thanks for any suggestions.
View 4 Replies
View Related
Oct 11, 2007
Hi , I am trying to write a stored procedure (i have given it below).i am basically trying to join 4 strings into one based on some if conditions.But the result gives only the intially assaigned string and rest are not getting concatenated.i have provided teh stored procedure below along with the inputs and result i got.Can anyone Please help me to acheive this set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[TestSearch] @distributorId int, @locationId int, @orderTypeId int, @fromDate Datetime = NULL, @toDate Datetime = NULL, @OrderStatus varchar(500) = NULL, @TaxAuthority varchar(500) = NULL, @TaxStampType varchar(500) = NULLASBEGINDeclare @SQL varchar(8000)Set @SQL ='select * from Orders AS a INNER JOINOrderLines AS b ON a.Id = b.FkOrder INNER JOINTaxStampTypes AS c ON b.FkTaxStampType = c.Id where ''' +CONVERT(VARCHAR(8),@fromDate ,1) + ''' <= CONVERT(VARCHAR(8),a.OrderDate ,1) and ''' +CONVERT(VARCHAR(8),@toDate ,1) + '''>= CONVERT(VARCHAR(8), a.OrderDate,1)and a.fkordertype = '+ convert(varchar(1),@orderTypeId) +'anda.FkDistributor = ('+ convert(varchar(50), @distributorId)+',a.FkDistributor)anda.FkLocation in ('+convert(varchar(10),@locationId)+',a.FkLocation)and'IF(@OrderStatus != null)Beginset @SQL= @SQL + 'a.FkOrderState in ('+ @OrderStatus +') and' EndIF(@TaxAuthority!= null)Beginset @SQL = @SQL +'a.FkTaxAuthority in ('+@TaxAuthority+') and'EndIF(@TaxStampType!= null)Beginset @SQL = @SQL + 'c.id in ('+ @TaxStampType+ ')and'End--Execute (@SQL1)select (@SQL);ENDHere is the Input Given to stored Procedure for executing:DECLARE @return_value intEXEC @return_value = [dbo].[TestSearch] @distributorId = 1002, @locationId = 3, @orderTypeId = 1, @fromDate = N'06/10/07', @toDate = N'10/10/07', @OrderStatus = N'2', @TaxAuthority = N'1000', @TaxStampType = N'1000'SELECT 'Return Value' = @return_valueHere Is The Output i get when I execute the stored procedure: select * from Orders AS a INNER JOIN OrderLines AS b ON a.Id = b.FkOrder INNER JOIN TaxStampTypes AS c ON b.FkTaxStampType = c.Id where '06/10/07' <= CONVERT(VARCHAR(8),a.OrderDate ,1) and '10/10/07'>= CONVERT(VARCHAR(8), a.OrderDate,1) and a.fkordertype = 1 and a.FkDistributor = (1002,a.FkDistributor) and a.FkLocation in (3,a.FkLocation) and--Ajay
View 9 Replies
View Related
Jun 23, 2006
I have 1 files, one is .sql and another is stored procedure. SQL file will call the stored procedure by passing the variables setup in the SQL file. However, everything ran well except at the end, I try to get the return value from SP to my SQL file ... which will send notification email. But, I get the following msg ... I am not sure how to fix this ... help!!! :(
Syntax error converting the varchar value 'ABC' to a column of data type int.
SQL file
======================
DECLARE @S AS VARCHAR(1000)
EXEC @S = PDT.DBO.SP_RPT 'ABC'
SELECT CONTENT = @S -- this is the value I am trying to pass as content of the email
EXEC PRODUCTION.DBO.SENDEMAIL 'xxx@hotmail.com', 'Notification', @S
======================
Stored Procedure
======================
CREATE procedure sp_RPT( @array varchar(1000) ) AS
DECLARE @content AS VARCHAR(2000)
SET @content = 'RPT: ' + @array + ' loaded successfully '
SET @array = 'ABC'RETURN CONVERT(VARCHAR(1000),@array)
GO
View 2 Replies
View Related
Aug 26, 2007
In my .NET app I have a search user control. The search control allows the user to pick a series of different data elements in order to further refine your display results. Typically I store the values select in a VarChar(5000) field in the DB. One of the items I recently incorporated into the search tool is a userID (GUID) of the person handling the customer. When I go to pass the selected value to the stored proc
objUpdCommand.Parameters.Add("@criteria", SqlDbType.VarChar).Value = strCriteria;
I get: Failed to convert parameter value from a String to a Guid.
Ugh! It's a string, dummy!!! I have even tried:
objUpdCommand.Parameters.Add("@criteria", SqlDbType.VarChar).Value = new Guid(strCriteria).ToString();
and
objUpdCommand.Parameters.Add("@criteria", SqlDbType.VarChar).Value = new Guid(strCriteria).ToString("B");
but to no avail. How can I pass this to the stored proc without regard for it being a GUID in my app?
View 1 Replies
View Related
Nov 4, 1999
I am stuck! and need help!
I have a problem I can't seem to find the solution with this aweful limitations on VARCHAR fields of 255.
Within a stored procedure called Store_Check, I need to dynamically build a string (@string) using VARCHAR(255) since the text datatype can't be used in a stored procedure.
So,this string is built according to whether the Store ID is NOT NULL. So if the StoreID is not null, I start building this string 'Exec Update_Store_Address @StoreID1, @address2'. There are 20 StoreID's passed into Store_Check. IF all 20 StoreID's are not NULL, the executed String greatly exceeds 255 because the string winds up looking like this
'Exec Update_Store_Address @StoreID1, @address1 Exec Update_Store_Address @StoreID2, @address2 Exec Update_Store_Address @StoreID3, @address3 Exec Update_Store_Address @StoreID4, @address4 Exec Update_Store_Address @StoreID5, @address5 Exec Update_Store_Address @StoreID6, @address6 etc. etc.'
I am not executing this string within the StoredCheck procedure. It needs to be passed as ONE string to a VB program and it gets executed by the VB program. Even if I create 4 local variables and concatenate them, it stops at the 255th character.
Also, a local varialbe of type TEXT cannot be declared within stored procedure.
What can I do? I am stuck!
Angel
View 1 Replies
View Related
Nov 20, 2014
how to execute this stored procedure to decode a varchar hexadecimal string? My SQL syntax stills have faded with the sands of time... I want to do a select * from the users table and decode the Password field (but not update) in the process.
CREATE PROCEDURE spPasswordDecode (@hex varchar (100), @passwordtext varchar (100) output)
AS
declare @n int,
@len int,
@str nvarchar(500),
@output varchar(100)
[code]....
View 4 Replies
View Related
Aug 28, 2012
Here's the SP
Code:
USE [byrndb]
GO
/****** Object: StoredProcedure [dbo].[sp_GetLikeJobsByJobNumber] Script Date: 08/28/2012 15:17:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ....
Why when I run
Code:
USE [byrndb]
GO
DECLARE@return_value int
EXEC@return_value = [dbo].[sp_GetLikeJobsByJobNumber]
@number = N'23913'
SELECT'Return Value' = @return_value
GO
Am I getting an "Msg 8114, Level 16, State 5, Procedure sp_GetLikeJobsByJobNumber, Line 16
Error converting data type varchar to numeric." error? Line 16 is "@number varchar = null"
View 5 Replies
View Related
Aug 9, 2006
I'm having a hard time to getting back an xml data back from a stored procedure executed by an Execute SQL task.
I'm passing in an XML data as a parameter and getting back resulting XML data as a parameter. The Execute SQL task is using ADO connection to do this job. The two parameters(in/out) are type of "string" and mapped as string.
When I execute the task, I get the following error message.
[Execute SQL Task] Error: Executing the query "dbo.PromissorPLEDataUpload" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 2 ("@LogXML"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I also tried mapping the parameter as XML type, but that didn't work either.
If anyone knows what's going on or how to fix this problem please let me know. All I want to do is save returning XML data in the parameter to a local package variable.
Thanks
View 10 Replies
View Related
May 10, 2007
Hi,I am trying to write a method which needs to call a stored procedure and then needs to get the response of the stored procedure back to the variable i declared in the method. private string GetFromCode(string strWebVersionFromCode, string strWebVersionString) { //call stored procedure } strWebVersionFromCode = GetFromCode(strFromCode, "web_version"); // is the var which will store the response.how should I do this?Please assist.
View 3 Replies
View Related
Jul 23, 2005
I get the following error for this procedure:Server: Msg 156, Level 15, State 1, Procedure Trig_Ins_TrackingNumbers,Line 6Incorrect syntax near the keyword 'IF'.CREATE TRIGGER Trig_Ins_TrackingNumbers ON TrackingNumbersINSTEAD OF INSERTASBEGINIF (SELECT ISNUMERIC(InvoiceNumber) FROM inserted)IF(EXISTS (SELECT I.InvoiceNumberFROM Invoices I, inserted InsWHERE I.InvoiceNumber=CAST(Ins.InvoiceNumber AS INT)))INSERT INTO TrackingNumbers(InvoiceNum,InvoiceNumber,TrackingNumber)SELECT CAST(InvoiceNumber AS INT), InvoiceNumber, TrackingNumberFROM insertedEND
View 4 Replies
View Related
Nov 1, 2007
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
View 1 Replies
View Related
