From aspnet_User,Products Join ShoppingCart ON Products.ProductID =ShoppingCart.ProductID
WHERE aspnet_User.Userid=@UserId AND ShoppingCart.CartID = @CartID
EXEC SP_DeleteShoppingCart @CartID
SELECT @OrderID
GO
Does this Stored Procedure Syntax look okay?I'm trying to call it using classic asp with the following code but get an error on calling the stored procedure:set objComm = Server.CreateObject("ADODB.Command")
objComm.ActiveConnection = sstrDsnString 'You can also just specify a connection string here objComm.CommandText = "editorTAmonthlyReport" ' stored procedure name objComm.CommandType = adCmdStoredProc 'Requires the adovbs.inc file or typelib meta tag 'Add Input Parameters to date and from date variables - dtFrom and dtTo objComm.Parameters.Append.CreateParameter("@dtFrom", adDate, adParamInput, 9) objComm.Parameters.Append.CreateParameter("@dtTo", adDate, adParamInput, 9) CREATE PROCEDURE editorTAmonthlyReport @FromDate DateTime, @ToDate DateTime As
Select U.UserID, U.Title, U.FirstName, U.Surname, TAU.UserPosition, U.Email, A.AgencyName, A.AddressLine1, A.AddressLine2, A.Town, A.County, A.Postcode, C.Country, Coalesce(U.Phone,A.Phone) As Phone, TAU.DateOfCreation From [user] U LEFT OUTER JOIN MyTrafalgarUser MTU ON MTU.userID=U.UserID INNER JOIN TravelAgencyUser TAU ON U.UserID=TAU.UserID INNER JOIN Agency A ON TAU.AgencyID=A.AgencyID INNER JOIN Country C ON A.CountryID=C.CountryID Where ( u.userid > 34657 AND DateDiff(d,TAU.DateOfCreation, @FromDate) < 0 ) and datediff(d,TAU.DateOfCreation, @ToDate) > 0 ORDER BY TAU.DateOfCreation GO
Hi All I will like to allow guess users to add items to the shopping cart (using a temp shoppingcartID): ' If user is not authenticated, either fetch (or issue) a new temporary cartID If Not context.Request.Cookies("MMDotNetID") Is Nothing ThenReturn context.Request.Cookies("MMDotNetID").Value.ToString Else ' Generate a new random GUID using System.Guid ClassDim tempCartId As Guid = Guid.NewGuid() ' Send tempCartId back to client as a cookie context.Response.Cookies("MMDotNetID").Value = tempCartId.ToString() context.Response.Cookies("MMDotNetID").Expires = DateTime.Now.AddMonths(1) ' Return tempCartId Return tempCartId.ToString ' If user is authenticated I use the userID to store the items in the Shopping Cart 'If the user logon after adding some items to the cart I need to Migrate the old items. 'But my STORED PROCEDURE is not working properly:ALTER PROCEDURE MM_SP_ShoppingCartMigrate ( @OriginalCartId nvarchar(50), @NewCartId nvarchar(50)) As IF EXISTS(SELECT * FROM MM_SP_ShoppingCart WHERE CartID = @NewCartId) BEGIN
I need help in writing a stored procedure on SQL Server 2000.Basically the stored procedure's primary task is to generate invoicerecords and insert the records in a invoice table. In order togenerate the invoice records, I have an initial table which arebasically Day Records. The task that I would like to complete is toselect all records from that initial table and I guess put them into atemp table. Now that i have my temp table, I would like to loop thruthe table record by record, and do inserts in the invoice table. Icant seem to figure out the syntax since I am somewhat weak in TSQLProgramming. I would appreciate any assistance on this forum or to myemail. Also If you need some pseudocode for the process or the DDL forthe initial table and the invoice table, I can definitely post that inthe forum possibly in the next thread. Please advise, I would need afull written syntax to get me started since i have some otherprocesses that I would need to build using the template. Thanks again.
I'm enhancing a large ASP application by replacing raw SQL statementsin the code with calls to stored procedures. I've seen thisrecommended in many places to increase SQL Server effieicency.Replacing select statements is going fine but I hit a sticking pointwhen trying to replace an update statement.Currently, I use this kind of statement a lot in my ASP:sql = "update"sql = sql & " field1 = value1"sql = sql & ",field2 = value2"If Len(value3) Then sql = sql & ",field3 = value3"sql = sql & " where ...., etc, etcThe important part here is checking if "value3" has a value beforeinserting it into my SQL statement. So how can I put this updatestatement into a stored procedure, pass in values for value1, value2,and value3, and leave off the value3 part of the update if value3comes in as null.Thanks all.
Hi AllIm trying to use the code at the bottom of this message inside my storedprocedure and when i execute the procedure in query analyzer i get thefollowing error:Server: Msg 207, Level 16, State 3, Line 1Invalid column name 'H'.This error happens a few times and im pretty sure it is because the selectstatement needs the ' around the data that is being searched. This is whatthe select statement looks like for me:SELECT * FROM Locations WHERE GroupID = H OR GroupID = HM OR GroupID = HMSOR GroupID = HMSS OR GroupID = HMSSR OR GroupID = HMSSRVAnd im pretty sure it is failing because sql wants the select statement tolook like this:SELECT * FROM Locations WHERE GroupID = 'H' OR GroupID = 'HM' OR GroupID ='HMS' OR GroupID = 'HMSS' OR GroupID = 'HMSSR' OR GroupID = 'HMSSRV'Am i thinking along the right lines with this ? If so does anybody know of away that i can put the ' mark around the the data that is being searched for? Any help is greatly appreciatedThanksCREATE PROCEDURE [dbo].[TestSP]@MachineName VarChar(50),@UserName VarChar(50)ASDECLARE @MachineLength Char(2) /* Local Machine Name Length */DECLARE @SrchInt Char(1) /* Search Loop Integer Counter */DECLARE @SqlStr VarChar(300) /* SQL Select String */DECLARE @CurrMach VarChar(50) /* Local Machine Name Counter */SET @SrchInt = 1SET @MachineLength = Len(@MachineName)SET @SqlStr = 'SELECT * FROM Locations WHERE GroupID = 'WHILE @SrchInt <= @MachineLengthBEGINSET @CurrMach = LEFT(@MachineName,@SrchInt)IF @SrchInt = 1BEGINSET @SqlStr = @SqlStr + LEFT(@MachineName,1)ENDIF @SrchInt > 1BEGINSET @SqlStr = @SqlStr + ' OR GroupID = ' + @CurrMachENDSET @SrchInt = @SrchInt + 1PRINT @SqlStrENDEXEC (@SqlStr)GO
This is probably a simple question but i would appreciate some help. I have a stored procedure that i want want first to check if a customer exists in the customer table and if a customer doesnt exist to insert values. I know that there are other ways to do this but i was wondering if itwas possible to do this only within the stored procedure. I have been using: SELECT COUNT(*)FROM customersWHERE customerID=@customerID IF COUNT(*)=0............ (Insert statement follows) But the new customer values are not being inserted.Can anyone tell me where I am going wrong or suggest a diferent wat of doing this? Many Thanks martin BY the way the rows seem to be counted as the information is inserted if i use If COUNT(*)>0 but the insert is not happening with the code above.
I'm having a weird issue with a stored procedure I wrote. I can run it in Query Analyzer and no problem, but when I run it from the web I get an error.
Heres the stored procedure: CREATE PROCEDURE dbo.UpdateLosses ( @Record int, @PriorCarrier varchar(50), @Year varchar(4), @Losses money ) AS BEGIN TRAN UPDATE Losses SET PriorCarrier = @PriorCarrier, [Year] = @Year, Losses = @Losses WHERE @Record = Record COMMIT TRAN GO
Here's the error: Line 1: Incorrect syntax near 'UpdateLosses'.
Here's how I run it in Query Analyzer: exec UpdateLosses @Record=20, @PriorCarrier='John', @Year='2002', @Losses=234.32
I would be very grateful if someone could help me with a stored procedure syntax problem I want to insert the value "OrderTotal" into databasetable("Newtable") column "OrderTotal"(money (8)). The value can be returned from the page (Dim amount As Decimal = ShoppingCart.GetTotal() totalAmountLabel.Text = String.Format("{0:c}",amount) or returned by the function "ShoppingCart.GetTotal". This is the syntax i have tried, but I get the error message "Invalid column name OrderTotal" CREATE PROCEDURE SP_NewOrder(@CartID char (36),@CustomerID Varchar (50),@OrderTotal decimal(8)) AS INSERT INTO NewTable (FirstName,ProductID,OrderTotal) SELECT Customer.FirstName,Products.ProductID,Ordertotal From Customer,Products Join ShoppingCart ON Products.ProductID =ShoppingCart.ProductIDWHERE Customer.CustomerID=@CustomerID AND ShoppingCart.CartID = @CartIDGO Can anyone tell me where i am going wrong many thanks martin
Dear Forum, I am adding a new column name to my Stored Procedure called HeadlinerID. It is an Int that is 4 characters long. I seem to be putting this in incorrectly in my stored procedure. I have tried it like: @HeadlinerID int(4), and @HeadlinerID int, and both ways I get the error below: Error 170: Line 16: Incorrect Syntax near ‘)’. Line 40: Incorrect syntax near ‘@Opener’. Is there a trick to putting in integers in a stored procedure?
I have the following stored procedure. The good thing is I finally got the syntax to the point where it doesn't blow up, but I was under the impression the "print" statement would print my result. In this case all I get is the statement ' 1 Row Affected ' which by the way is not the answer I was expecting.
Here's the code: ' CREATE PROCEDURE createevents AS
declare @myvariable varchar(700)
declare @myvar varchar(700)
begin Select @myvariable = d.id from table1 d, table2 dc where d.class = dc.class
If @myvariable > 0 begin Select @myvar =dp. class from table3 dp, table2 dc where dp.class = dc.class
If @myvariable >= 1 begin print @myvariable + (',') + @myvar end end
else begin print @myvariable + ('is empty') end end
I have a Stored Procedure that execute several tasks
If I write the SP en the following format :
quote: USE [REPORTES] GO /****** Object: StoredProcedure [dbo].[Astral_sp_AcCpasArtC] Script Date: 06/23/2008 11:29:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTERPROCEDURE [dbo].[Astral_sp_AcCpasArtC] AS BEGIN SELECT ... UPDATE ... INSERT ... END
It saves ok in Stored Procedures folder but doesn't run ok only send the message = Command(s) completed successfully.
Otherwise If I write the SP in the following format :
quote: USE [REPORTES] GO /****** Object: StoredProcedure [dbo].[Astral_sp_AcCpasArtC] Script Date: 06/23/2008 11:29:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTERPROCEDURE [dbo].[Astral_sp_AcCpasArtC] AS GO SELECT ... GO UPDATE ... GO INSERT ... GO
It run ok select/update/insert records but doesn't saves in Stored Procedures folder It only save the next part : quote: USE [REPORTES] GO /****** Object: StoredProcedure [dbo].[Astral_sp_AcCpasArtC] Script Date: 06/23/2008 11:29:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTERPROCEDURE [dbo].[Astral_sp_AcCpasArtC] AS
Also, when the SP has only one task (let's say a Select), it works ok with AS BEGIN - END format
I'm trying to use a couple of variables in a stored procedure. Things workfine when I hard code the data into the variables and also work fine when Iuse the variable in the WHERE clause and hard code data for the othervariable. So, I think I have a syntax problem when trying to use"FrontPage.@FrontpageProduct" as seen in my example code below. I've triedmany variations... and either get syntax errors or end up with a result of"no records." If somebody could assist me with the proper syntax for a"table_name.@variable_name" reference it would be greatly appreciated.The following procedure is called from a VB/.asp page. It's for astorefront front page where product codes listed in the table "FrontPage"are used to pull product data from table "Products."=============================CREATE PROCEDURE dbo.frontpage@FrontpageProduct varchar,@FrontpageDay varcharASSELECT * FROM Products LEFT JOIN FrontPage ON Products.Code =FrontPage.@FrontpageProduct WHERE FrontPage.theDay = @FrontPageDayGO=============================Again, thank you in advance for any help.Dave
How do I pass my stored procedure value into my query? My first example works, in the second example my parameters are contained in the sql string. How do I expose parameter values to the inner sql string?
Thanks for any help.
Kevin
============================================== This works: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[SelectTest] @StartDate datetime, @EndDate datetime AS BEGIN SET NOCOUNT ON; SELECT DownloadDate, [Project Number], [Project Name], [Expenditure Category], [Expenditure Type], [Commitments], RevExp FROM dbo.OracleDownload WHERE DownloadDate BETWEEN @StartDate AND @EndDate END
This does not: INSERT INTO ReportTable (ItemDate, ProjectNo, ProjectName, Category, Type, Amount, RevExp) EXECUTE ('SELECT DownloadDate, [Project Number], [Project Name], [Expenditure Category], [Expenditure Type], [Commitments], RevExp FROM dbo.OracleDownload WHERE [Project Number] > 0 AND DownloadDate BETWEEN @StartDate AND @EndDate ');
CREATE PROCEDURE dbo.IsFirmNickNameUnique @NickName varchar(12), @Found bit output AS IF ((SELECT COUNT(*) FROM Firm WHERE Firm.NickName = @NickName) = 0) BEGIN @Found = 0 END ELSE BEGIN @Found = 1 END
create proc dbo.sp_GetInvitationsHistoryDetails(@ExecID int, @OrgID int) as IF (@OrgID = 0) BEGIN select E.EventID,E.Description as Event,E.EventDate as Date I.Attending as [Att'g],NotAttending as [Not att'g],I.Bootcamp as [Maybe] I.Attended FROM Invitations I INNER JOIN Events E on I.EventID=E.EventID WHERE I.MemberID=@ExecID and NotForStats=0 ORDER BY E.EventDate DESC END ELSE BEGIN select E.EventID,E.Description as Event,E.EventDate as Date count(*) as Invited,SUM(CONVERT(smallint,I.Attended)) AS Attended FROM Invitations I INNER JOIN Events E on I.EventID=E.EventID WHERE I.MemberID IN (select ID FROM Executives WHERE OrganisationID=OrgID GROUP BY E.EventID,E.Description,E.EventDate ORDER BY E.EventDate DESC END
and i'm getting the following syntax errors when i check it -
Msg 102, Level 15, State 1, Procedure sp_GetInvitationsHistoryDetails, Line 10
Incorrect syntax near 'I'.
Msg 102, Level 15, State 1, Procedure sp_GetInvitationsHistoryDetails, Line 19
Incorrect syntax near 'count'.
Msg 156, Level 15, State 1, Procedure sp_GetInvitationsHistoryDetails, Line 23
Incorrect syntax near the keyword 'ORDER'.
Originally i just ran this sql from C# and it worked, obviously added the @ to the variables but it's basically the same.
I have 2 tables, table A and B. Table A has the following fields; Phone (nvchar), Fname (nvchar), Lname (nvchar), DNC (bit). Table B has one field, PhoneNo (nvchar). I would like to update the field DNC in Table A to True(1) if the values Phone in Table A = PhoneNo in Table B.
I tried this syntax but it never updated:
UPDATE dbo.A SET DNC = 1 WHERE (Phone = 'SELECT MAX dbo.B.PhoneNo FROM dbo.B')
These tables have over 100K records and I would like to make sure it runs through and checks every single record and not just the first 10000.
This is probably a very simple question but i would appreciate some helpwith the correct syntax for and update stored procedureI have created user form that allows the user to update the name and address fields in a datatable called customers based on the input value customer ID = ( datatable/Customers)customerIDI have got this far and then got lost:Create SP_UpdateCustomer(@customerID, @name, @address)As Update customers ( name, address)Where customerID = @customerID GOCould anyone tell me what the correct sntax should be.many thanksMartin
Im new to this forum and new also to SQL SERVER Edition Express.
Im trying to creat stored procedure. My main problem is that I need to display an alias consisting of 2 fields in a combobox (VB.Net) using also an innerjoin. Can anyone help me find my mistake please
My code is here and the error is : ----------------------------------------------------------- Msg 156, Level 15, State 1, Procedure LA_suppName, Line 16 Incorrect syntax near the keyword 'INNER'.
Eventually I would need to use tb_LA.LANo and make a query to populate the tb_LABooks in another combobox on selectvaluechanged. Is this possible please???
Can anyone tell me why the line highlighted in blue produces the following error when I try to run this stored proc? I know the parameters are set properly as I can see them when debugging the SP. I'm using this type of approach as my application is using the objectdatasource with paging. I have a similar SP that doesn't have the CategoryId and PersonTypeId parameters and that works fine so it is the addition of these new params that has messed up the building of the WHERE clause The Error is: "Syntax error converting the varchar value ' WHERE CategoryId = ' to a column of data type int." Thanks Neil CREATE PROCEDURE dbo.GetPersonsByCategoryAndTypeByName (@CategoryId int, @PersonTypeId int, @FirstName varchar(50)=NULL, @FamilyName varchar(50)=NULL, @StartRow int, @PageSize int) AS Declare @WhereClause varchar(2000)Declare @OrderByClause varchar(255)Declare @SelectClause varchar(2000) CREATE TABLE #tblPersons ( ID int IDENTITY PRIMARY KEY , PersonId int , TitleId int NULL , FirstName varchar (50) NULL , FamilyName varchar (50) NOT NULL , FullName varchar (120) NOT NULL , AltFamilyName varchar (50) NULL , Sex varchar (6) NULL , DateOfBirth datetime NULL , Age int NULL , DateOfDeath datetime NULL , CauseOfDeathId int NULL , Height int NULL , Weight int NULL , ABO varchar (3) NULL , RhD varchar (8) NULL , Comments varchar (2000) NULL , LocalIdNo varchar (20) NULL , NHSNo varchar (10) NULL , CHINo varchar (10) NULL , HospitalId int NULL , HospitalNo varchar (20) NULL , AltHospitalId int NULL , AltHospitalNo varchar (20) NULL , EthnicGroupId int NULL , CitizenshipId int NULL , NHSEntitlement bit NULL , HomePhoneNo varchar (12) NULL , WorkPhoneNo varchar (12) NULL , MobilePhoneNo varchar (12) NULL , CreatedBy varchar(40) NULL , DateCreated smalldatetime NULL , UpdatedBy varchar(40) NULL , DateLastUpdated smalldatetime NULL, UpdateId int ) SELECT @OrderByClause = ' ORDER BY FamilyName, FirstName' SELECT @WhereClause = ' WHERE CategoryId = ' + @CategoryId + ' AND PersonTypeId = ' + @PersonTypeIdIf NOT @Firstname IS NULLBEGIN SELECT @WhereClause = @WhereClause + ' AND FirstName LIKE ISNULL(''%'+ @FirstName + '%'','''')'ENDIf NOT @FamilyName IS NULLBEGIN SELECT @WhereClause = @WhereClause + ' AND (FamilyName LIKE ISNULL(''%'+ @FamilyName + '%'','''') OR AltFamilyName LIKE ISNULL(''%'+ @FamilyName + '%'',''''))'END Select @SelectClause = 'INSERT INTO #tblPersons( PersonId, TitleId, FirstName, FamilyName , FullName, AltFamilyName, Sex, DateOfBirth, Age, DateOfDeath, CauseOfDeathId, Height, Weight, ABO, RhD, Comments, LocalIdNo, NHSNo, CHINo, HospitalId, HospitalNo, AltHospitalId, AltHospitalNo, EthnicGroupId, CitizenshipId, NHSEntitlement, HomePhoneNo, WorkPhoneNo, MobilePhoneNo, CreatedBy, DateCreated, UpdatedBy, DateLastUpdated, UpdateId) SELECT PersonId, TitleId, FirstName, FamilyName , FullName, AltFamilyName, Sex, DateOfBirth, Age, DateOfDeath, CauseOfDeathId, Height, Weight, ABO, RhD, Comments, LocalIdNo, NHSNo, CHINo, HospitalId, HospitalNo, AltHospitalId, AltHospitalNo, EthnicGroupId, CitizenshipId, NHSEntitlement, HomePhoneNo, WorkPhoneNo, MobilePhoneNo, CreatedBy, DateCreated, UpdatedBy, DateLastUpdated, UpdateId FROM vw_GetPersonsByCategoryAndType ' EXEC (@SelectClause + @WhereClause +@OrderByClause)
hai guys, i have written a stored procedure which creates a table ex: USE PUBS GO IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'RC_STRPROC') DROP PROCEDURE RC_STRPROC GO USE PUBS GO CREATE PROCEDURE RC_STRPROC (@TBLNAME VARCHAR(35), @COLVAL1 VARCHAR(35), @COLVAL2 VARCHAR(35)) AS IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = '@TBLNAME') DROP TABLE @TBLNAME CREATE TABLE @TBLNAME (@COLVAL1, @COLVAL2) GO it gives an syntax error at '@tblname' can u guys tell me the problem
I copied the the following code from a book to the query editor of my SQL Server Management Studio Express (SSMSE): ///--MuCh14spInvTotal3.sql--/// USE AP --AP Database is installed in the SSMSE-- GO CREATE PROC spInvTotal3 @InvTotal money OUTPUT, @DateVar smalldatetime = NULL, @VendorVar varchar(40) = '%' AS
IF @DateVar IS NULL SELECT @DateVar = MIN(InvoiceDate)
SELECT @InvTotal = SUM(InvoiceTotal) FROM Invoices JOIN Vendors WHERE (InvoiceDate >= @DateVar) AND (VendorName LIKE @VendorVar) GO /////////////////////////////////////////////////////////////// Then I executed it and I got the following error: Msg 156, Level 15, State 1, Procedure spInvTotal3, Line 12 Incorrect syntax near the keyword 'WHERE'. I do not know what wrong with it and how to correct this problem.
I have a stored procedure that contains a paramteter of type nvarchar(max). What is the syntax to pass this parameter to the sp from a VB.Net application. Specifically, I need to know what to put in the 3rd parameter below:
Hi,I have a problem with updating a datetime column,When I try to change the Column from VB I get "Incorrect syntax near'942'" returned from [Microsoft][ODBC SQL Server Driver][SQL Server]'942' is the unique key column valueHowever if I update any other column the syntax is fineThe same blanket update query makes the changes no matter what isupdatedThe problem only happens when I set a unique key on the date field inquestionKey is a composite of an ID, and 2 date fieldsIf I allow duplicates in the index it all works perfectlyI am trying to trap 'Duplicate value in index' (which is working onother non-date columns in other tables)This is driving me nutsAny help would be appreciated
i have migrated a DTS package wherein it consists of SQL task.
this has been migrated succesfully. but when i execute the package, i am getting the error with Excute SQL task which consists of Store Procedure excution.
But the SP can executed in the client server. can any body help in this regard.
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?
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)
'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.
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],
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)
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
