What's Wrong With My Stored Procedure?
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
ADVERTISEMENT
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
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
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 NVARCHARASBEGINDECLARE @postedbyid UNIQUEIDENTIFIERSET @postedbyid = (SELECT UserIdFROM aspnet_UsersWHERE (UserName = @username))END###Which returns this### Running [dbo].[GetUserIdForUser] ( @username = jason ).No rows affected.(0 row(s) returned)@RETURN_VALUE = 0Finished 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 NVARCHARASBEGINSELECT UserIdFROM aspnet_UsersWHERE (UserName = @username)END###Which returns this###Running [dbo].[GetUserIdForUser] ( @username = jason ).UserId -------------------------------------- No rows affected.(0 row(s) returned)@RETURN_VALUE = 0Finished running [dbo].[GetUserIdForUser]. Here is a sanity check to show that the data is in fact in the database:ALTER PROCEDURE dbo.GetUserIdForUserASBEGINSELECT UserIdFROM aspnet_UsersWHERE (UserName = 'jason')END Running [dbo].[GetUserIdForUser].UserId -------------------------------------- No rows affected.(1 row(s) returned)@RETURN_VALUE = 0Finished running [dbo].[GetUserIdForUser]. Anyone have any ideas on what I am doing wrong?
View 13 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
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
Jan 15, 2004
Hi,
I have a procedure that inserts a record to a table and return the @@Identity as an output parameter.
This table has a trigger that is triggered when a record is inserted and add a new record to other table.
The problem is that the @@Identity returned is totally wrong.
Any ideas?
Thanx in advance.
View 3 Replies
View Related
Mar 29, 2005
Bellow is the stored procedure. I wanted to do an If / Else statement. I'm getting a syntax error that something is wrong around my Begin / Else statements. If anyone knows what is wrong I would greatly appriciate it.
Thanks in advance as always.
RB
<code>
Create Proc UpdateFundsAndTotals ASIF (Select FundsAndTotals.fundID, FundsAndTotals.TotalPledges, TotalPledges.fundID, TotalPledges.TotalPledges From FundsAndTotals, TotalPledges Where FundsAndTotals.fundID = TotalPledges.fundID AND FundsAndTotals.TotalPledges != TotalPledges.TotalPledges)Begin Update FundsAndTotals Set FundsAndTotals.TotalPledges = TotalPledges.TotalPledgesENDELSE (Select FundsAndTotals.fundID, FundsAndTotals.TotalPledges, TotalPledges.fundID, TotalPledges.TotalPledges From FundsAndTotals, TotalPledges Where FundsAndTotals.fundID = TotalPayments.fundID AND FundsAndTotals.TotalPayments != TotalPayments.TotalPayments)Begin Update FundsAndTotals Set FundsAndTotals.TotalPayments = TotalPayments.TotalPaymentsENDElseEND
</code>
View 7 Replies
View Related
Mar 8, 2005
Can somebody please debug what is wrong with this stored proc.
When I execute this procedure I get an error
Invalid column name 'abcd'.
CREATE PROCEDURE SearchTest
@LName varchar (25) = 'abcd',
@ID varchar(9) = null
AS
Declare @query varchar(100)
Set @query = 'Select * From Document '
IF ( @LName IS NOT NULL)
set @query = @query + ' Where LName = ' + @LName
exec (@query)
GO
Thanks :)
View 4 Replies
View Related
Mar 3, 2008
Hi all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class
///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance,
Scott Chang
More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
View 11 Replies
View Related
Aug 16, 2004
Hi,
I'm getting an error ERROR 137: Must declare the variable '@' when I click the check sysntax button on my store proc window. Any ideas on how to resolve this??
CREATE PROCEDURE CabsSchedule_GetPrevYearMonth
AS
DECLARE @prvYearMonth int
DECLARE @prvYear int
DECLARE @prvMonth int
BEGIN
SET @prvYearMonth = (SELECT MAX(YearMonth) FROM CabsSchedule)
IF ((SELECT COUNT(*) FROM CabsSchedule WHERE YearMonth = @ prvYearMonth) > 15)
ELSE
BEGIN
IF (SUBSTRING(CAST(@prvYearMonth AS VarChar(6)), 5, 2) = '01')
BEGIN
SET @prvMonth = 12
SET @prvYear = CAST(SUBSTRING(CAST(@prvYearMonth AS VARCHAR(6)),1,4) AS INT) - 1
SET @prvYearMonth = CAST(CAST(@prvYear AS VARCHAR(4)) + CAST(@prvMonth AS VARCHAR(2)) AS INT)
END
END
END
GO
Thanks,
View 1 Replies
View Related
Aug 3, 2006
In SQL Server for a field of datatype float(8),
if i insert a value 2.62 , it saves it as 2.6200000000000001
like so for other values also.
But in frontend i can see the right values.
Could any of you help me in fixing up this issue?
Thanks in advance.
~ chaitanya
View 1 Replies
View Related
Nov 14, 2014
I am new to work on Sql server,
I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.
Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.
View 1 Replies
View Related
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
Sep 19, 2006
I have a requirement to execute an Oracle procedure from within an SQL Server procedure and vice versa.
How do I do that? Articles, code samples, etc???
View 1 Replies
View Related
Aug 3, 2006
Hi,
When i try to insert a value in to a field of datatype float(8), it is storing the wrong values(approximate values).
For example, if i try to insert 2.62 , it takes it as 2.6200000000000001
But i can see the correct values displayed in the frontend.
Heard that there is a fix available for this issue.
Could any of you help me in getting the details about that fix?
Thanks in advance.
~ Chaitanya
View 4 Replies
View Related
Dec 28, 2005
I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
View 9 Replies
View Related
Sep 26, 2014
I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
View 3 Replies
View Related
Feb 19, 2006
Im trying to insert a record in my sql server 2005 express database.The following function tries that and without an error returns true.However, no data is inserted into the database...Im not sure whether my insert statement is correct: I saw other example with syntax: insert into table values(@value1,@value2)....so not sure about thatAlso, I havent defined the parameter type (eg varchar) but I reckoned that could not make the difference....Here's my code: Function CreateNewUser(ByVal UserName As String, ByVal Password As String, _ ByVal Email As String, ByVal Gender As Integer, _ ByVal FirstName As String, ByVal LastName As String, _ ByVal CellPhone As String, ByVal Street As String, _ ByVal StreetNumber As String, ByVal StreetAddon As String, _ ByVal Zipcode As String, ByVal City As String, _ ByVal Organization As String _ ) As Boolean 'returns true with success, false with failure Dim MyConnection As SqlConnection = GetConnection() Dim bResult As Boolean Dim MyCommand As New SqlCommand("INSERT INTO tblUsers(UserName,Password,Email,Gender,FirstName,LastName,CellPhone,Street,StreetNumber,StreetAddon,Zipcode,City,Organization) VALUES(@UserName,@Password,@Email,@Gender,@FirstName,@LastName,@CellPhone,@Street,@StreetNumber,@StreetAddon,@Zipcode,@City,@Organization)", MyConnection) MyCommand.Parameters.Add(New SqlParameter("@UserName", SqlDbType.NChar, UserName)) MyCommand.Parameters.Add(New SqlParameter("@Password", Password)) MyCommand.Parameters.Add(New SqlParameter("@Email", Email)) MyCommand.Parameters.Add(New SqlParameter("@Gender", Gender)) MyCommand.Parameters.Add(New SqlParameter("@FirstName", FirstName)) MyCommand.Parameters.Add(New SqlParameter("@LastName", LastName)) MyCommand.Parameters.Add(New SqlParameter("@CellPhone", CellPhone)) MyCommand.Parameters.Add(New SqlParameter("@Street", Street)) MyCommand.Parameters.Add(New SqlParameter("@StreetNumber", StreetNumber)) MyCommand.Parameters.Add(New SqlParameter("@StreetAddon", StreetAddon)) MyCommand.Parameters.Add(New SqlParameter("@Zipcode", Zipcode)) MyCommand.Parameters.Add(New SqlParameter("@City", City)) MyCommand.Parameters.Add(New SqlParameter("@Organization", Organization)) Try MyConnection.Open() MyCommand.ExecuteNonQuery() bResult = True Catch ex As Exception bResult = False Finally MyConnection.Close() End Try Return bResult End FunctionThanks!
View 1 Replies
View Related
Apr 26, 2015
I have an extremely annoying problem when debugging stored procedures in SQL Server 2014 with SSDT or SSMS. When calling a SP thru EXECUTE in Debug mode, 9 out of 10 SPs are traced with a wrong yellow arrow-pointer to the line currently reached.
The offset is between 6 to 15 lines downward. Tracing itself and update of the "Locals"-view works as expected. All SPs contain comments also before the Create Procedure statement. The SP shown when tracing show exactly the same content as the stored SQL in the SSDT project under work incl. Create procedure and all comments.
The picture here show the first line selected after the debugger has traced into the SP. The first line really executed with "Next" will be SET NOCOUNT ON.
If this does not turns out as my fault and some of you would support that, I would like to post this to SQL Connect.
View 4 Replies
View Related
Mar 28, 2007
I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.
How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?
Thanks in advance
View 9 Replies
View Related
Jan 23, 2008
Has anyone encountered cases in which a proc executed by DTS has the following behavior:
1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio
2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio
What could explain this?
Obviously,
All three scenarios are executed against the same database and hit the exact same tables and indices.
Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).
The DTS execution effectively never finishes even after many hours (10+)
The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query)
The Update ad-hoc query will finish in 2 minutes
View 1 Replies
View Related
Sep 13, 2007
Hi all,
I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.
Whenever I tried to right click stored procedure and select step into store procedure> i get following error
"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"
I am not sure what needs to be done on sql server side
We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.
Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?
Please advise..
Thank You
View 3 Replies
View Related
Mar 31, 2008
I have a stored procedure 'ChangeUser' in which there is a call to another stored procedure 'LogChange'. The transaction is started in 'ChangeUser'. and the last statement in the transaction is 'EXEC LogChange @p1, @p2'. My questions is if it would be correct to check in 'LogChange' the following about this transaction: 'IF @@trancount >0 BEGIN Rollback tran' END Else BEGIN Commit END.
Any help on this would be appreciated.
View 1 Replies
View Related
Oct 10, 2006
Hi,I am getting error when I try to call a stored procedure from another. I would appreciate if someone could give some example.My first Stored Procedure has the following input output parameters:ALTER PROCEDURE dbo.FixedCharges @InvoiceNo int,@InvoiceDate smalldatetime,@TotalOut decimal(8,2) outputAS .... I have tried using the following statement to call it from another stored procedure within the same SQLExpress database. It is giving me error near CALL.CALL FixedCharges (@InvoiceNo,@InvoiceDate,@TotalOut )Many thanks in advanceJames
View 16 Replies
View Related
Nov 15, 2006
I have a store procedure (e.g. sp_FetchOpenItems) in which I would like to call an existing stored procedure (e.g. sp_FetchAnalysts). The stored proc, sp_FetchAnalysts returns a resultset of all analysts in the system.
I would like to call sp_FetchAnalysts from within sp_FetchOpenItems and insert the resultset from sp_FetchAnalysts into a local temporary table. Is this possible?
Thanks,
Kevin
View 3 Replies
View Related
May 18, 2007
This is the Stored Procedure below ->
SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GO
/****** Object: Stored Procedure dbo.BPI_SearchArchivedBatches Script Date: 5/18/2007 11:28:41 AM ******/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BPI_SearchArchivedBatches]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[BPI_SearchArchivedBatches]GO
/****** Object: Stored Procedure dbo.BPI_SearchArchivedBatches Script Date: 4/3/2007 4:50:23 PM ******/
/****** Object: Stored Procedure dbo.BPI_SearchArchivedBatches Script Date: 4/2/2007 4:52:19 PM ******/
CREATE PROCEDURE BPI_SearchArchivedBatches( @V_BatchStatus Varchar(30)= NULL, @V_BatchType VARCHAR(50) = NULL, @V_BatchID NUMERIC(9) = NULL, @V_UserID CHAR(8) = NULL, @V_FromDateTime DATETIME = '01/01/1900', @V_ToDateTime DATETIME = '01/01/3000', @SSS varchar(500) = null, @i_WildCardFlag INT)
AS
DECLARE @SQLString NVARCHAR(4000)DECLARE @ParmDefinition NVARCHAR (4000)
IF (@i_WildCardFlag=0)BEGIN
SET @SQLString='SELECT Batch.BatchID, Batch.Created_By, Batch.RequestSuccessfulRecord_Count, Batch.ResponseFailedRecord_Count, Batch.RequestTotalRecord_Count, Batch.Request_Filename, Batch.Response_Filename, Batch.LastUpdated_By, Batch.LastUpdated, Batch.Submitted_By, Batch.Submitted_On, Batch.CheckedOut_By, Batch.Checked_Out_Status, Batch.Batch_Description, Batch.Status_Code, Batch.Created_On, Batch.Source, Batch.Archived_Status, Batch.Archived_By, Batch.Archived_On, Batch.Processing_Mode, Batch.Batch_TemplateID, Batch.WindowID,Batch.WindowDetails, BatchTemplate.Batch_Type, BatchTemplate.Batch_SubType FROM Batch INNER JOIN BatchTemplate ON Batch.Batch_TemplateID = BatchTemplate.Batch_TemplateID WHERE ((@V_BatchID IS NULL) OR (Batch.BatchID = @V_BatchID )) AND ((@V_UserID IS NULL) OR (Batch.Created_By = @V_UserID )) AND ((Batch.Created_On >= @V_FromDateTime ) AND (Batch.Created_On <= @V_ToDateTime )) AND Batch.Archived_Status = 1 '
if (@V_BatchStatus IS not null) begin set @SQLString=@SQLString + ' AND (Batch.Status_Code in ('+@V_BatchStatus+'))' end
if (@V_BatchType IS not null) begin set @SQLString=@SQLString + ' AND (BatchTemplate.Batch_Type in ('+@V_BatchType+'))' end END
ELSEBEGIN SET @SQLString='SELECT Batch.BatchID, Batch.Created_By, Batch.RequestSuccessfulRecord_Count, Batch.ResponseFailedRecord_Count, Batch.RequestTotalRecord_Count, Batch.Request_Filename, Batch.Response_Filename, Batch.LastUpdated_By, Batch.LastUpdated, Batch.Submitted_By, Batch.Submitted_On, Batch.CheckedOut_By, Batch.Checked_Out_Status, Batch.Batch_Description, Batch.Status_Code, Batch.Created_On, Batch.Source, Batch.Archived_Status, Batch.Archived_By, Batch.Archived_On, Batch.Processing_Mode, Batch.Batch_TemplateID, Batch.WindowID,Batch.WindowDetails, BatchTemplate.Batch_Type, BatchTemplate.Batch_SubType FROM Batch INNER JOIN BatchTemplate ON Batch.Batch_TemplateID = BatchTemplate.Batch_TemplateID WHERE ((@V_BatchID IS NULL) OR (isnull (Batch.BatchID, '''') LIKE @SSS )) AND ((@V_UserID IS NULL) OR (isnull (Batch.Created_By , '''') LIKE @V_UserID )) AND ((Batch.Created_On >= @V_FromDateTime ) AND (Batch.Created_On <= @V_ToDateTime )) AND Batch.Archived_Status = 1 '
if (@V_BatchStatus IS not null) begin set @SQLString=@SQLString + ' AND (Batch.Status_Code in ('+@V_BatchStatus+'))' end
if (@V_BatchType IS not null) begin set @SQLString=@SQLString + ' AND (BatchTemplate.Batch_Type in ('+@V_BatchType+'))' end
END
PRINT @SQLString
SET @ParmDefinition = N' @V_BatchStatus Varchar(30), @V_BatchType VARCHAR(50), @V_BatchID NUMERIC(9), @V_UserID CHAR(8), @V_FromDateTime DATETIME , @V_ToDateTime DATETIME, @SSS varchar(500)'
EXECUTE sp_executesql @SQLString, @ParmDefinition, @V_BatchStatus , @V_BatchType , @V_BatchID, @V_UserID , @V_FromDateTime , @V_ToDateTime , @SSS
GO
SET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO
The above stored procedure is related to a search screen where in User is able to search from a variety of fields that include userID (corresponding column Batch.Created_By) and batchID (corresponding column Batch.BatchID). The column UserID is a varchar whereas batchID is a numeric.
REQUIREMENT:
The stored procedure should cater to a typical search where any of the fields can be entered. meanwhile it also should be able to do a partial search on BatchID and UserID.
Please help me regarding the same.
Thanks in advance.
Sandeep Kumar
View 2 Replies
View Related
