I'm creating a simple stored procedure:
create procedure spzipcode
(
@TableName nvarchar(10)
)
as
select distinct customer_code
from @TableName
GO
When running the code above, I get the following error message:
Server: Msg 137, Level 15, State 2, Procedure spzipcode, Line 8
Must declare the variable '@TableName'.
I can't figure out what I'm doing wrong.
Does anyone have an answer:
Hi
I have trouble to get this stored procedure running and I tried to figure out where I did a mistake but I'm just lost, so if somebody could help me... Thanks!
The Error Message is 102, Level 15, State 1
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE InsertNewDocumentTypeAndAddSystemFields
@varDocumentTypeName nvarchar(254),
@varNEWDocumentTypeID integer = 0,
@varTempID integer
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.Document_Type_LookUP
VALUES(@varDocumentTypeName);
@varNEWDocumentTypeID = IDENT_CURRENT('dbo.Document_Type_LookUP')
-- Name or Title
INSERT INTO dbo.Document_Field
SELECT 'Name', SQLDataTypeID FROM SQLDataType WHERE SQLDataTypeName = 'string'
@varTempID = IDENT_CURRENT('dbo.Document_Field')
INSERT INTO dbo.Document_Type_Field_Link
VALUES(@varNEWDocumentTypeID,@varTempID)
-- Filename
INSERT INTO dbo.Document_Field
SELECT 'Filename', SQLDataTypeID FROM SQLDataType WHERE SQLDataTypeName = 'string'
@varTempID = IDENT_CURRENT('dbo.Document_Field')
INSERT INTO dbo.Document_Type_Field_Link
VALUES(@varNEWDocumentTypeID,@varTempID)
END
GO
Thanks in advance!
I have read many error message articles on the web but still cannot get this to work. I need to return the description of the error that is produced to a output variable (@ErrMsg).
In my stored procedure, I assign @SQLCode to @@Error. @SQLCode is also an output variable. I got the @SQLCode to return no problem, just the description is wrong.
Code SnippetIF @SQLCode <> 0
BEGIN
SELECT description = @ErrMsg
FROM master.dbo.sysmessages
WHERE error = @SQLCode
END
Can anyone shed some light on this? I have heard I have to assign the error information to another table and then pull the info from that table, but I don't know how to do that either. Help is greatly appreciated.
Thanks
Greetings,
I am creating a package that has many SQL tasks. Each task executes a stored procedure. I need to capture any error messages returned by the stored procedures. Eventually, the error messages will be logged so that we can audit the package and know if individual tasks succeeded or failed.
I'm not sure where or how I can access a stored procedure message. What is the best way?
Thanks,
BCB
Hi All,I am having an error message when running the program below, the message says " Could not find stored procedure 'dbo.U_Login ". I have tried a inline Sql statement to match if user exists and then redirects to another page but when using a Stored Procedure what I get is the above mentioned error. I am logged onto my PC as Administrator but I don't know why this error appaears. Can anyone help me with this ? I am using SQL Server Express with Visual Studio. here the application I am trying to run:1 using System;
2 using System.Data.SqlClient;
3 using System.Data;
4 using System.Configuration;
5 using System.Web;
6 using System.Web.Security;
7 using System.Web.UI;
8 using System.Web.UI.WebControls;
9 using System.Web.UI.WebControls.WebParts;
10 using System.Web.UI.HtmlControls;
11
12
13 public partial class _Default : System.Web.UI.Page
14 {
15 protected void Page_Load(object sender, EventArgs e)
16 {
17
18 }
19 protected void btnSubmit_Click(object sender, EventArgs e)
20 {
21
22 //SqlDataSource LoginDataSource = new SqlDataSource();
23 //LoginDataSource.ConnectionString =
24 // ConfigurationManager.ConnectionStrings["LoginConnectionString"].ConnectionString;
25 ////ConfigurationManager.ConnectionStrings["LoginConnectionString"].ToString();
26 ////// if (LoginDataSource != null)
27 //// // Response.Redirect("DebugPage.aspx");
28
29 //Sql connection = LoginDataSource.ConnectionString;
30
31 string UserName = txtUserName.Text;
32 string Password = txtPassword.Text;
33
34 // string query = "SELECT * FROM Users WHERE userName = @UserName " + "AND Password = @Password";
35 //SqlCommand Command = new SqlCommand(query, new SqlConnection(GetConnectionString()));
36
37 SqlCommand Command = new SqlCommand("dbo.U_Login", new SqlConnection(GetConnectionString()));
38
39
40 //SqlConnection SqlConnection1 = new SqlConnection(GetConnectionString()); //added
41 //SqlCommand Command = new SqlCommand(); //added
42 //Command.Connection = SqlConnection1; //added
43 // Command.Connection = new SqlConnection(GetConnectionString());
44 //SqlConnection_1.Open();//added
45
46 Command.CommandType = CommandType.StoredProcedure; //added
47 //Command.CommandText = "dbo.U_Login"; //added
48
49
50
51
52 Command.Parameters.AddWithValue("@UserName", UserName);
53
54 Command.Parameters.AddWithValue("@Password", Password);
55
56 Command.Connection.Open();
57
58 SqlDataReader reader;
59
60
61 //reader = Command.ExecuteReader(CommandBehavior.CloseConnection);11
62
63 reader = Command.ExecuteReader();
64
65
66 if (reader.Read())
67
68 Response.Redirect("DebugPage.aspx");
69 else
70 Response.Write("user doesn't exist");
71
72
73 //SqlConnection_1.Close();//added
74
75
76 }
77 private static string GetConnectionString()
78 {
79
80 return ConfigurationManager.ConnectionStrings["LoginConnectionString"].ConnectionString;
81
82 }
83
84
85 }
86
87
88
89
Thanks in advance
Client/Server machine: Windows Xp Pro (SP2) (latest patches)
Office Software: Access 2003 (latest patches)
Database S/W: SQL Server 2005 (latest patches)
The following error message is displayed when trying to modify a stored procedure.
This version of Microsoft Access doesn't support design changes to the
version of Microsoft SQL Server your project is connected to. See the
Microsoft Office Update Web site for the latest information and downloads
(on the Help menu, click Office on the Web). Your design changes will not be
saved.
However, if you save, close and re-open the stored procedure having made the required changes, the changes have been saved.
Is there any way to suppress the error message / hotfix available from microsoft since the error message appears to be completely erroneous ?
Have I provided enough detail as this is my first post ?
Philip
I am working with SQL Server 2005 Express and have written a query which works well in when run it in the managment console as a select query.
When I try to create a stored procedure out of it, I get the following error:
Code:
Msg 102, Level 15, State 1, Procedure sp_SegmentationList_ByorderID, Line 20 Incorrect syntax near ';'.
Procedure is below:
Code:
USE [ARC_Test]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_SegmentationList_ByorderID]
@OrderID int
[code].....
Hi
I was transferring objects of my existing database from remote to local and I recieved this error during the DTS process. It was on the transfer of the following stored procedure. I tried to create this SP manually but its giving me the same error and doesn't create it. I don't know how to fix it.
I wonder if somebody can help me! Thanks in Advance
[450] Code page translations are not supported for the text data type. From: 1252 To: 1256.
CREATE PROCEDURE dbo.Novasoft_DL_UpdateDownloadsRating @RatingID int, @DownloadID int, @UserID int, @Rating int, @Comment text, @ReviewDate datetime, @CommentName nvarchar(50) ASUPDATE dbo.Novasoft_DL_DownloadsRating SET [DownloadID] = @DownloadID, [UserID] = @UserID, [Rating] = @Rating, [Comment] = @Comment, [ReviewDate] = @ReviewDate, [CommentName] = @CommentNameWHERE [RatingID] = @RatingID
GO
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
thanks
hiss
Hi all,
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.
Please help and advise.
Thanks,
Scott Chang
Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'Donation' that match the referencing column list in the foreign key 'fk_Branch_bloodType'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors. The table name affected is Purchase ,below are the tables the table Donation is created but purchase can't
create table Donation
(
Donation_Code varchar (5) primary key,
Blood_Type varchar (4) not null,
Date_Recd date not null,
Date_Of_Expiry date not null,
Purpose varchar (30),
[code]....
Hi
I created database using SQL server and runs under Cassini. The creation of database is ok but I have a problem when I am creating the table in database. Whenever I execute the code to create the tables, it shows the error message like 'Server not found' or just hang there. Does anybody know why it hang while I am creating the table? Is it because of the code or it's the time out error. Pls help as I am very new to this area.
MZ
I copied my .mdf database from a file and pasted it into the visual studio project App_Data folder.I can see the tables and the columns etc when creating my TableAdapter, and create my sql query etc, but then when I hit the finish button, I get this error "an Unexpected Error has OccuredError Message: Access Denied". Anyone know why I can't create my TableAdapter?
View 7 Replies View RelatedHi all SQL Replication experts :)
I have created my production server as a distributor and a publication on that server. On my backup server I have created a pull subscription. After that I have generated SQL scripts on my backup server so I can create the pull subscription anytime.
To test my script I used sp_removedbreplication 'dbname' to remove replication on the backup server. I then used the generated SQL script to create the pull subscription again. I got this error message
Job 'ProdServName_DBName-BackServName-DBName401A48AE-D8DC-4F29-A610-13916370CD0B' started successfully.
Server: Msg 208, Level 16, State 1, Procedure sp_addsubscription, Line 135
Invalid object name 'syspublications'.
What does this Error message mean and what can I do about it?
Grateful for all answers
Best,
/M
I have SQL Server 2008 Standard and whenever I right click on Maintenance Plans and select New Maintenance Plan and I name the plan and click Ok. I get an error message that says Key cannot be null. Parameter name: key (mscorlib)
When I press OK on this message, I see an error message that says Microsoft SQL Server Management Studio is unable to load this document: Could not load type "Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSPackageSigning 100'from assembly 'Microsoft.SqlServer.DTSRuntimeWrap.
Version=10.0.0.0. Culture=neutral. PublicKeyToken=89845dcd8080cc91'.
What does this mean? What do I need to do to fix this?Â
I have a stored procedure which checks to see if a user's email address exists before it inserts a new record. If it does it should return a message that notifies the user they are already subscribed. If they are not a different message should be returned stating that a new subscription has been created. This procedure works and is shown below.
The problem I am having is with the first SELECT statement. How can I get the procedure to show just one message and not the results from the first SELECT statement too?
CREATE PROCEDURE sp_InsertSubscription
@SubscriberFirstName VARCHAR(50),
@SubscriberLastName VARCHAR(50),
@SubscriberEmailAddress VARCHAR(50),
@SubscriberZipCode INT,
@IsActive BIT,
@SubscriberOptIn BIT,
@AdditionalOffersOptIn BIT,
@Msg VARCHAR(50) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
-- Check to see if email address exists first
SELECT @@ROWCOUNT FROM Subscriber WHERE SubscriberEmailAddress = @SubscriberEmailAddress
IF @@ROWCOUNT > 0
BEGIN
SET @Msg = 'This email address is already subscribed'
SELECT @Msg AS 'User'
RETURN
END
-- Insert statements for procedure here
INSERT INTO Subscriber (SubscriberFirstName,
SubscriberLastName,
SubscriberEmailAddress,
SubscriberZipCode,
IsActive,
SubscriberOptIn,
AdditionalOffersOptIn,
SubscriberSignUpDate)
VALUES (@SubscriberFirstName,
@SubscriberLastName,
@SubscriberEmailAddress,
@SubscriberZipCode,
@IsActive,
@SubscriberOptIn,
@AdditionalOffersOptIn,
GETDATE())
IF @@ROWCOUNT > 0
BEGIN
SET @Msg = 'New user subscription created'
END
SELECT @Msg AS 'User'
END
GO
If the user's email address does not exist I get
(No column name)
User
New user subscription created
If the user's email address does exist I get
(No column name)
0
User
This email address is already subscribed
I would like for the (No column name) to go away - I know this is coming from the first SELECT statement. How do I suppress that statement from being output, yet still get the @@ROWCOUNT variable set?
Hello everybody,
I've encountered a strange thing using a CLR Stored procedure:
The procedure throws an exception with no message inside... value = {" "}
Basically the procedure has a string as argument which contains a SQL statement that changes according to the users selections...
The results of the query are saved into a dataset for further processing.
Those resultsets can sometimes be very big (ex: 15000 records...). (For the record the procedure works fine for smaller datasets ex 6000 records and running the same query on the application server returns the expected resultset )
By Debugging the procedure I could determine that the failing point was when the dataset is filled...
Anyone having any idea??
The only information I have from this exception is the stacktrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.CloseInternal(Boolean closeReader)
at System.Data.SqlClient.SqlDataReader.Close()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at DataAccess.runScalar(String strSQL, Boolean isStoredProcedure) in c:InetpubwwwrootStatistixApp_CodeDataAccess.cs:line 114
Thanks in advance
Alaindlk
Hi all,
I got an error message 156, when I executed the following code:
////--SQLQueryParent&Child.sql---////////
Use newDB
GO
----Creating dbo.Person as a Parent Table----
CREATE TABLE dbo.Person
(PersonID int PRIMARY KEY NOT NULL,
FirstName varchar(25) NOT NULL,
LastName varchar(25) NOT NULL,
City varchar(25) NOT NULL,
State varchar(25) NOT NULL,
Phone varchar(25) NOT NULL)
INSERT dbo.Person (PersonID, FirstName, LastName, City, State, Phone)
SELECT 1, "George", "Washington", "Washington", "DC", "1-000-1234567"
UNION ALL
SELECT 2, "Abe", "Lincoln", "Chicago", "IL", "1-111-2223333"
UNION ALL
SELECT 3, "Thomas", "Jefferson", "Charlottesville", "VA", "1-222-4445555"
GO
----Creating dbo.Book as a Child table----
CREATE TABLE dbo.Book
(BookID int PRIMARY KEY NOT NULL,
BookTitle varchar(25) NOT NULL,
AuthorID int FOREIGN KEY NOT NULL)
INSERT dbo.Book (BookID, BookTitle, AuthorID)
SELECT 1, "How to Chop a Cherry Tree", 1
UNION ALL
SELECT 2, "Valley Forge Snow Angels", 1
UNION ALL
SELECT 3, "Marsha and ME", 1
UNION ALL
SELECT 4, "Summer Job Surveying Viginia", 1
UNION ALL
SELECT 5, "Log Chopping in Illinois", 2
UNION ALL
SELECT 6, "Registry of Visitors to the White House", 2
UNION ALL
SELECT 7, "My Favorite Inventions", 3
UNION ALL
SELECT 8, "More Favorite Inventions", 3
UNION ALL
SELECT 9, "Inventions for Which the World is Not Ready", 3
UNION ALL
SELECT 10, "The Path to the White House", 2
UNION ALL
SELECT 11, "Why I Do not Believe in Polls", 2
UNION ALL
SELECT 12, "Doing the Right Thing is Hard", 2
GO
---Try to obtain the LEFT OUTER JOIN Results for the Parent-Child Table
SELECT * FROM Person AS I LEFT OUTER JOIN Book ON I.ID=P.ID
GO
////---Results---//////
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'NOT'.
////////////////////////////////////////////////////
(1) Where did I do wrong and cause the Error Message 156?
(2) I try to get a Parent-Child table by using the LEFT OUTER JOIN via the following code statement:
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'NOT'.
Can I get a Parent-Child table after the error 156 is resolved?
Please help and advise.
Thanks,
Scott Chang
Does anyone have any ideas or sample code for firing a stored procedure using an MSMQ message?
I have to assume XML is in there somewhere.
(ie. create XML formatted message with proc call as element?)
Just getting started on project, any help appreciated.
www.beyonder422.com
Hello,
I have a bundling package that runs about 20 other packages. It has been working fine for a while but a couple of days ago it fail with the following message,
Error 0x800706BE while loading package file "D:PackagesToradSales.dtsx". The remote procedure call failed.
I´m running the SSIS packages in an 64-bit environment.
Thankful for help with this!
//Patrick
Hello,
is message exchange between a .NET Webservice and a SQL stored procedure possible?
And if, could you please explain me how? Or give me a tip where i can get more informations
and maybe samples?
thanks for your help
regards
pamelia
create procedure Pr_addsupportor(
@supportornumber varchar(10),
@company varchar(100),
@title char(10),
@firstname char(30),
@surname char(30),
@addressline1 varchar(1000),
@addressline2 varchar(1000),
@addressline3 varchar(1000),
@postcode varchar(20),
@town char(100),
@county char(100),
@country varchar(100),
@phonenumber varchar(15),
@faxnumber varchar(15),
@email varchar(100),
@paymenttitle varchar(100),
@barcode varchar(2000),
@collector varchar(3),
@status varchar(10),
@registerdate datetime)
INSERT into
supportor(
[supportor number],
company,
title,
[first name],
surname,
[address line1],
[address line2],
[addess line3],
[post code],
town,
county,
country,
[phone number],
[fax number],
[e-mail],
[payment title],
barcode,
collector,
status,
[register date])
values
(
@supportornumber,
@company,
@title,
@firstname,
@surname,
@addressline1,
@addressline2,
@addressline3,
@postcode,
@town,
@county,
@country,
@phonenumber,
@faxnumber,
@email,
@paymenttitle,
@barcode,
@collector,
@status,
@registerdate);
Server: Msg 156, Level 15, State 1, Procedure Pr_addsupportor, Line 22
Incorrect syntax near the keyword 'INSERT'.
plz help me to find the error
Is it possible to create a stored procedure that execute a delete command from a table whose name is specified as a parameter of the stored procedure?Thank you
View 3 Replies View RelatedALTER PROCEDURE companyinsert@companyid INT IDENTITY(1,1) NOT NULL,@companyname varchar(20),@address1 varchar(30)ASINSERT INTO companymaster ( companyname, address1)VALUES (@companyname,@address1)i don't want the companyname having the same names are recorded again with the different company id..Can anyone help me and modify my code according it's giving error...in the @companyid.It is being done in sql server 2005 with asp.net C# 2005
View 1 Replies View RelatedI have some script for creating the table i want but i need some help creating a Stored Procedure. That will Insert the UserId from the aspnet_Users Table (UserId) into the UserId Column in my table. So when a user creates an account it will put that users id into my table. The data will be retrieved by a FormView and the user can Update their info threw the FormView control..
I just need to associate the UserId from the aspnet_User Table with my table, so the data is stored per UserId in a new row for each new user..create table dbo.custom_Profile (
UserId uniqueidentifier not null Primary Key,
IamWeAre nvarchar(50) null,
InterestedIn nvarchar(256) null,
IntroTitle nvarchar(100) null,
TellOthers nvarchar(MAX)null,
MaritalStatus nvarchar(20) null,
BodyType nvarchar(50) null,
Race nvarchar(20) null,
Smoking nvarchar(20) null,
Drinking nvarchar(20) null,
Drugs nvarchar(20) null,
Education nvarchar(256) null)
go
I have a table (displayed in a gridview) of services we provide. I have another table (the logfile) that displays the current status of those services. This GridView displays the service and current status.When a new service is created there is obviously no status yet. This causes a problem because my stored procedure (below) does not display that new service in my GridView. How can I ensure EVERY service is included in my dataset regardless of whether or there is a status? (and how can I get away from having to group by all the time?)
select s.servicename, s.opr, c.commentid,c.comment, c.etic, t.statusfrom svc_service sinner join svc_comment c on c.serviceid = s.serviceidinner join svc_status t on t.statusid = c.statusidgroup by s.servicename, s.opr, c.commentid, c.comment, c.etic, t.status
TIA,
Jon
I have an inline sql query which i want to convert to a stored procedure.
The query is simplified as follows:
string sql = "SELECT * FROM property_property WHERE Location = " + location;
if(state != null) sql += " AND State = " + state;
if(cost !=null) sql += " AND Cost = " + cost;
The query is created depending on variable states; there are 7 in the real one which together creates the required query to query the database. Im not sure how/if it can be converted in to a stored proc
I know some of the basics and managed to convert every other query except this.
Any help would be appreciated
I already know how you create a stored procedure to add information to a database or retrieve a value for one record. But I don't know how to create a stored procedure that will retrieve many records for a certain querystring value.
Here's my simple stored procedure to show one record:
CREATE PROCEDURE DisplayCity(@CityID int)AS
SELECT City From City where CityID = @CityIDGO
My code for displaying the City:
Sub ShowCity()
Dim strConnect As String
Dim objConnect As SqlConnection
Dim objCommand As New SqlCommand
Dim strCityID As String
Dim City As String
'Get connection string from Web.Config
strConnect = ConfigurationSettings.AppSettings("ConnectionString")
objConnect = New SqlConnection(strConnect)
objConnect.Open()
'Get incoming City ID
strCityID = request.params("CityID")
objCommand.Connection = objConnect
objCommand.CommandType = CommandType.StoredProcedure
objCommand.CommandText = "DisplayCity"
objCommand.Parameters.Add("@CityID", CInt(strCityID))
'Display SubCategory
City = "" & objcommand.ExecuteScalar().ToString()
lblCity.Text = City
lblChosenCity.Text = City
objConnect.Close()
End Sub
Here's the code I'd like to get help with changing into a stored procedure:
Sub BindDataList()
Dim strConnect As String
Dim objConnect As New System.Data.SqlClient.SQLConnection
Dim objCommand As New System.Data.SqlClient.SQLCommand
Dim strSQL As String
Dim dtaAdvertiser As New System.Data.SqlClient.SQLDataAdapter()
Dim dtsAdvertiser As New DataSet()
Dim strCatID As String
Dim strCityID As String
Dim SubCategory As String
Dim SubCategoryID As String
Dim BusinessName As String
Dim City As String
'Get connection string from Web.Config
strConnect = ConfigurationSettings.AppSettings("ConnectionString")
objConnect = New System.Data.SqlClient.SQLConnection(strConnect)
objConnect.Open()
'Get incoming querystring values
strCatID = request.params("CatID")
strCityID = request.params("CityID")
'Start SQL statement
strSQL = "select * from Advertiser,AdvertiserSubCategory, Categories, SubCategories, County, City"
strSQL = strSQL & " where Advertiser.CategoryID=Categories.CategoryID"
strSQL = strSQL & " and Advertiser.AdvertiserID=AdvertiserSubCategory.AdvertiserID"
strSQL = strSQL & " and AdvertiserSubCategory.SubCategoryID=SubCategories.SubCategoryID"
strSQL = strSQL & " and Advertiser.CountyID=County.CountyID"
strSQL = strSQL & " and Advertiser.CityID=City.CityID"
strSQL = strSQL & " and AdvertiserSubCategory.SubCategoryID = '" & strCatID & "'"
strSQL = strSQL & " and Advertiser.CityID = '" & strCityID & "'"
strSQL = strSQL & " and Approve=1"
strSQL = strSQL & " Order By ListingType, BusinessName,City"
'Set the Command Object properties
objCommand.Connection = objConnect
objCommand.CommandType = CommandType.Text
objCommand.CommandText = strSQL
'Create a new DataAdapter object
dtaAdvertiser.SelectCommand = objCommand
'Get the data from the database and
'put it into a DataTable object named dttAdvertiser in the DataSet object
dtaAdvertiser.Fill(dtsAdvertiser, "dttAdvertiser")
'If no records were found in the category,
'display that message and don't bind the DataGrid
if dtsAdvertiser.Tables("dttAdvertiser").Rows.Count = 0 then
lblNoItemsFound.Visible = True
lblNoItemsFound.Text = "Sorry, no listings were found!"
else
'Set the DataSource property of the DataGrid
dtlAdvertiser.DataSource = dtsAdvertiser
'Set module level variable for page title display
BusinessName = dtsAdvertiser.Tables(0).Rows(0).Item("BusinessName")
SubCategory = dtsAdvertiser.Tables(0).Rows(0).Item("SubCategory")
SubCategoryID = dtsAdvertiser.Tables(0).Rows(0).Item("SubCategoryID")
City = dtsAdvertiser.Tables(0).Rows(0).Item("City")
'Bind all the controls on the page
dtlAdvertiser.DataBind()
end if
objCommand.ExecuteNonQuery()
'this is the way to close commands
objCommand.Connection.Close()
objConnect.Close()
End Sub
How can I create a Stored Procedure name within stored procedure ?
Example:
CREATE PROCEDURE A_1000
AS
..............
..............
..............
create procedure B_2000
..............
..............
..............
Is that possible ?
I'm trying to create a SP within the SP.
Thankx for ur time
Thankx a lot
I need to add up the number of people who joined this month and compare that number to the number of people who joined last month and display the results in a report and I have never done a stored procedure and I was wondering if it would be ease to do?
FirstName..LastName.....StartedDate
Randy......Simpson......5/4/2007 10:00:00 PM
Steve......Rowe.........5/2/2007 10:00:00 PM
Eric.......Dickerson....5/4/2007 10:00:00 PM
Gloria.....Sanches......5/1/2007 12:00:29 AM
Andres.....Marcelino....5/1/2007 12:06:31 AM
katie......ryan.........6/4/2007 12:08:35 AM
Denise.....River........6/4/2007 12:27:14 AM
Kellog.....Stover.......6/5/2007 12:37:20 AM
Glenn......Sanders......6/1/2007 12:42:40 AM
Im trying to create a stored procedure that selects everything from afunction name that im passing in through a parameter..create procedure SP_selectall(@functionname varchar(25))asselect * from @functionamegoI keep getting this error:Server: Msg 137, Level 15, State 2, Procedure SP_selectall, Line 5Must declare the variable '@functioname'.Whats the issue?
View 1 Replies View RelatedWant help in creating the stored procedure of company where id is the PrimaryKey in the table companymaster which is created in sql server 2005.1 ALTER PROCEDURE companyinsert
2
3 @companyid int,
4 @companyname varchar(20),
5 @address1 varchar(30)
6
7 AS
8
9 INSERT INTO companymaster
10 ( companyname, address1)
11 VALUES (@companyname,@address1) Procedure or Function 'companyinsert' expects parameter '@companyid', which
was not supplied.
The id is to be created autogenerate in the sequence number.There should be no duplicated companyname with different ids in same table.Apart from the above error can anyone pls give me or tell me the code or modify the stored procedure according to the above..thanxs....
I am trying to create stored procedure i Query analyzer in visual studio 2005. I am havingdifficulty though. Whenever I press the execute button, here is the error message I get:
Msg 102, Level 15, State 1, Procedure MarketCreate, Line 21Incorrect syntax near 'MarketName'.
Here is the stored procedure. Note that the very first column in named "MarketId" but I did notinclude it in the stored procedure since it should be auto generated.
USE [StockWatch]GO/****** Object: StoredProcedure [dbo].[MarketCreate] Script Date: 08/28/2007 15:49:26 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO
CREATE PROCEDURE [dbo].[MarketCreate]
( @MarketCode nvarchar(20), @MarketName nvarchar(100), @LastUpdateDate nvarchar(2), @MarketDescription nvarchar(100))
ASINSERT INTO Market( MarketCode MarketName LastUpdateDate MarketDescription)VALUES( @MarketCode @MarketName @LastUpdateUser @MarketDescription)