hi,how to pass message to front end
select count(*) from emp where empno=1
if(count=0)
i want to pass "no records" to front end(vb.net)
if(count=1)
"records found"
is it possible to do this in sqlserver,if means please help me to do
I am new to SQL Server 7 and get the following error message when I go into the Enterprise Manager, select a specific table, right mouse click, open table, return all rows:
"An unexpected error happened during this operation.
[MS Design Tools] - Query designer encountered a MS Design Tools error: Not Implemented"
Can anyone explain what is causing this error?? I can go into SQL Query Analyzer and do a SELECT against any table and bring back data, but not as stated above.
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?
I hope I'm posting this question in the proper forum, please pardon me if I am not. Here's my situation. I have an application written in VB.Net connecting to a SQL 2005 database. I have a stored procedure that is looking for duplicate entries of a "Company Name" prior to running the stored procedure. If there is no duplicate Company Name want the stored procedure to run, if there is a duplicate I want to return an error message alerting the user to the fact that the Company Name they are attempting to add already exists. I have my if statement but I'm not sure how to return an error message within this context. Any help would be greatly appreciated!
Many Thanks-
IF(SELECT COUNT(CompanyDimID) FROM dbo.CompanyDim WHERE CompanyName = @CompanyDim) = 0
BEGIN
.......................SQL query here................................
ELSE
(I want to return an error message in the ELSE portion to call out that we have a duplicate Company Name)
I use a storeed procedure to execure a dynamic sql string sent from an application. This string often contain a simple update. An update with anyting but one row affected is in this case to be considered an error/problem. As ut use the primary key in the where clause the and I want to store away the update values in anoter table
I want to act on the "(0 row(s) affected)" message. Is there a way to do that or do I have to query the database before I do the update?
Error below was returned from an agent job:
OLE DB provider "SQLNCLI11" for linked server returned message "Query timeout expired". [SQLSTATE 01000]
A linked Server was set up against a remote database to backup a database and it runs perfectly well when executed directly against the remote server but returns the above error when set up through Sql Server Agent. Permissions are good as the step returns success. I reset the query timeout property to zero but error persist. What else should I be looking at to make this work?
In my SQL Server, I see the below message in the Application Event Viewer
"18265 :
Log backed up: Database: HSD, creation date(time): 2007/01/06(05:05:05), first LSN: 1439:495055:1, last LSN: 1439:496675:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'D:MSSQLBACKUPHSDHSD_tlog_200707141300.TRN'})."
When I save the application event viewer and open it in another server, I do not see the above message, instead I get the following message:
" Can't find or load message DLL. Message DLL must be in path or in current directory."
Any thoughts to overcome this problem is appreciated.
Thanks
Santhosh
I run SB between 2 SQL servers. In profiler on an initiator side I see next error: 'This message could not be delivered because its message timestamp has expired or is invalid'. For the conversation we use best practice, i.e. target closes a conversation. Target side succeed to close conversation, but initiator still stay in DO (disconnect_outbound).
What is a reasone for the error? What to do?
I see in profiler this error: "This message could not be delivered because its message timestamp has expired or is invalid"
What is a reason for error?
Hello,
I am having trouble specifying a message body that is valid. I mean for the client to send. If I leave it as null then everything is ok but if I create a memorystream and add a line of text it reports back it did not pass validation. I do not understand this and am not sure what to do. I need to send a message based on a code and text but do not know the format of the body that is allowed. The code I am refering to comes out of HelloWord_CLR because that is what I am formating my sample after. I call it the same way it calls the return message done in ServiceProc. I need to know the message format including body since this does not seem to work. A sample of the call is bellow.
// Create an empty request message
string Msg = "Hello";
MemoryStream body = new MemoryStream(Encoding.ASCII.GetBytes(Msg));
Message request = new Message("Request", body);
Thanks,
Scott Allison...
I have a package that I have been attempting to return a error code after the stored procedure executes, otherwise the package works great.
I call the stored procedure from a Execute SQL Task (execute Marketing_extract_history_load_test ?, ? OUTPUT)
The sql task rowset is set to NONE. It is a OLEB connection.
I have two parameters mapped:
tablename input varchar 0 (this variable is set earlier in a foreach loop) ADO.
returnvalue output long 1
I set the breakpoint and see the values change, but I have a OnFailure conditon set if it returns a failure. The failure is ignored and the package completes. No quite what I wanted.
The first part of the sp is below and I set the value @i and return.
CREATE procedure [dbo].[Marketing_extract_history_load_TEST]
@table_name varchar(200),
@i int output
as
Why is it not capturing and setting the error and execute my OnFailure code? I have tried setting one of my parameter mappings to returnvalue with no success.
Sorry for the lengthy question, but here goes:
I currently have an access database with a FE and BE that resides on each users laptop. Weekly they connect to the network and synch the BE the master on a network drive. There are numerous propblems with this as it has grown quite extensive.
We would like to have this be a server based database with the remote users either having access through the web or something else?
A few of the capabilities of the current database:
1. generate and print reports 2. update project forms. 3. enter time logs 4. enter notes
My question is:
What are my options for a remote FE? I am well versed in VB6 and Access. Would be willing to learn other languages (VB.net, my SQL, etc.) Would I be able to have the same functionality or would I have to have separate software for certain functions?
I have a stored procedure that can take up to 2 hours to run, has anyone any ideas on what front end would be best used to call it
View 3 Replies View RelatedHi,
If I don't have any visual studio design tools etc.
Is there any way I can build a simple form that will allow users to view, insert, update and delete single rows from a SQL 2005 database?
Or is there any free downloads that might help. the table is really simple and has only 4 fields
TIA
Hello,
I am new to SQL Server. I currently have an Access 2007 database and next year my client would like to migrate to SQL Server. Which Front End is the best...Access, VB.net or some other platform altogether?
Thanks,
M. Wilson
This is my function, it returns SQLDataReader to DATALIST control. How
to return page number with the SQLDataReader set ? sql server 2005,
asp.net 2.0
Function get_all_events() As SqlDataReader
Dim myConnection As New
SqlConnection(ConfigurationManager.AppSettings("..........."))
Dim myCommand As New SqlCommand("EVENTS_LIST_BY_REGION_ALL", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
Dim parameterState As New SqlParameter("@State", SqlDbType.VarChar, 2)
parameterState.Value = Request.Params("State")
myCommand.Parameters.Add(parameterState)
Dim parameterPagesize As New SqlParameter("@pagesize", SqlDbType.Int, 4)
parameterPagesize.Value = 20
myCommand.Parameters.Add(parameterPagesize)
Dim parameterPagenum As New SqlParameter("@pageNum", SqlDbType.Int, 4)
parameterPagenum.Value = pn1.SelectedPage
myCommand.Parameters.Add(parameterPagenum)
Dim parameterPageCount As New SqlParameter("@pagecount", SqlDbType.Int, 4)
parameterPageCount.Direction = ParameterDirection.ReturnValue
myCommand.Parameters.Add(parameterPageCount)
myConnection.Open()
'myCommand.ExecuteReader(CommandBehavior.CloseConnection)
'pages = CType(myCommand.Parameters("@pagecount").Value, Integer)
Return myCommand.ExecuteReader(CommandBehavior.CloseConnection)
End Function
Variable Pages is global integer.
This is what i am calling
DataList1.DataSource = get_all_events()
DataList1.DataBind()
How to return records and also the return value of pagecount ? i tried many options, nothing work. Please help !!. I am struck
I'm a Reporting Services New-Be.
I'm trying to create a report that's based on a SQL-2005 Stored Procedure.
I added the Report Designer, a Report dataset ( based on a shared datasource).
When I try to build the project in BIDS, I get an error. The error occurs three times, once for each parameter on the stored procedure.
I'll only reproduce one instance of the error for the sake of brevity.
[rsCompilerErrorInExpression] The Value expression for the query parameter 'UserID' contains an error : [BC30654] 'Return' statement in a Function, Get, or Operator must return a value.
I've searched on this error and it looks like it's a Visual Basic error :
http://msdn2.microsoft.com/en-us/library/8x403818(VS.80).aspx
My guess is that BIDS is creating some VB code behind the scenes for the report.
I can't find any other information that seems to fit this error.
The other reports in the BIDS project built successfully before I added this report, so it must be something specific to the report.
BTW, the Stored Procedure this report is based on a global temp table. The other reports do not use temp tables.
Can anyone help ?
Thanks,
I'm doing a database for employee info and thier payroll.. im using SQL Serevr 2000 and IIS but i'm not too sure what to use as the frontend, a VB would kinda be overkill because each employee just needs to be able to update his own info and addresses, maybe a XML or some other page that uses windows logins for authentication? any ideas, this is my first project like this on on a college Coop
View 1 Replies View Relatedi am almost finished building the front end for an application to manage restaurants. i am developing the front end with access vba, and intend to use sql server as a back end for the service. i just came across a group of people bashing access developers on a different site. i have several restaurants interested in using the service, and believe that the service should be very successfull (i have a very specific customer base that is currently not being targeted). the access component would not be a multiuser application. before i distribute the app i would appreciate any input. am i getting myself into trouble using access vba? should i run scared and switch to a vb.net web service?
thanks in advance for any thoughts.
Below is a post by blindman from my other "mentoring" thread (http://www.dbforums.com/showthread.php?t=1608569). I wanted to respond to it but not in that thread:
Yeah, but if he's going to go unbound he might as well develop in .Net rather than Access. You lose half the convenience of MSAccess as a front end if you use unbound forms and controls.
Besides, he's trying to learn SQL Server, not front-end development. Learning both at the same time may be a bit much. I'd suggest he stick with his idea of converting the Access database to SQL Server back-end.
But, you should definitely convert the Access front-end to a Microsoft Access Data Project, rather than a classic mdb file.
Going unbound would increase your application performance, but probably not noticably unless you have scores of uses, and since you are currently using straight MS Access I'd guess you have no more than five or six simultaneous users, right?
blindman
To start I have decided that ADO is the way to go even though I don't necessarily need the performance boost for my current situation. Keep in mind I am planning for future and more demanding opportunities, hopefully :) And I'd like to learn the "right" way to do things.
I actually use ADO quite a bit in my current DB app and bound controls as well so it really wasn't a new concept. However using strictly ADO produces alot more work and code. And like blindman says it pretty much takes all the ease of Access away and VB.NET for that matter too. I mean all the wizards are useless. So I have two questions:
1. When going to all ADO you are pretty much recreating what Access already does for you right? Coding in ADO is more work right? I mean you have to setup connections and fill fields and listboxes manually in code right? I don't mind the extra work I just want to make sure if I'm going to be doing all this extra coding that it is the right thing to be doing.
2. MDB vs. ADP - From what little I've read Access Data Projects are made for use with SQL Server. It is even more useful if you don't/can't actually work in SQL Server. But again if I'm using ADO it strips all the usefulness of the ADP's doesn't? I don't need ADP's to create views or sprocs. I can do all of that through the management studio. So does it really matter if I use an ADP or MDB as my front end if that is all it is going to be is a front end?
Lastly I wanted to use VB 2005 as the front end but we utilize a ton of reports. As far as I know Crystal Reports or something like it has to be used with VB 2005. Or does VB 2005 have some built in reporting capabilities? I thought I saw some report designer for VB6. Anyway I decided to stick with Access because I am familiar with its reporting capabilities.
I should note that I have VB 2005 Express and SQL Server Express. Right now my company is not putting any money into a higher version.
Let me know what you all think.
Thanks,
GG
Hi
Can anybody tell us what frount end sould i learn for
better result from sql server.
Can also tell us what is price of sql server developer &
enterprise edition
Thanks
ASM
Hi,
I'd like to get some help on this. I'm new to SQL Server but am keen to learn it. I have SQL Server 2000 SP4 running and wonder what kind of front end application is best suited for SQL Server 2000.
Thank you in advance
sometimes a capital N is added in front of a string, for example,
N'xxxx'.
What does it mean?
Is there any front IDE for MS SQL as there is MySQL Front for MySQL. Iam looking for it so that it is easy to quickly add/edit/delete thedata from the existing table. Please prode the Download URL is there isany.Regards.
View 4 Replies View RelatedHi!I'm trying to build an app. for my parents' office. They have a smalloffice (5 PCs), and they currently work with a very old program that'sbased on DOS. The program doesn't work well under XP, so they still useWin 98. They had their network upgraded about a year ago. They hadMicrosoft SBS 2000 installed, it includes SQL Server. I've written afew DB's in Access before, and now I'm just learning how to use SQL.Before I start work on their app., I have a question:I know it's possible to create an interface that can be put on theirnetwork and accessed via a browser. How is this done exactly?It would be very beneficial for me, because it would mean that the newapp. would be platform independent, so that they can upgrade to XPeasily. It would also save us the cost of licensing front-end software(e.g. Access) for each machine.Any comments would be appreciated.Noam Nelke, Israel.
View 2 Replies View RelatedHi all,I have a number like so 778625 and want to make this a more meaningfuldate (UK) so how difficult is it to add two digits in this case 19 tothe front of this number?Many thanksSam*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 3 Replies View RelatedI have created a Database Editor with FrontPage using the Wizzard. I have adatabase in SQL 2000 called Equipment with one table named Equipment.When I bring up the editor, it displays a list of all records with a link tothe edit page. When I click on the link for the first record on the page,The edit page come up correctly. I can edit the page and then submit it.When I click on the link for the second record ( and all the rest of therecords) in the list I get the following message:Database Results ErrorDescription: [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax errorconverting the nvarchar value 'BF176' to a column of data type int.Number: -2147217913 (0x80040E07)Source: Microsoft OLE DB Provider for ODBC DriversWhen I click on the link for record BF176, which is about 20 records downthe page, I get the following message:Database Results ErrorDescription: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid columnname 'BF176'.Number: -2147217900 (0x80040E14)Source: Microsoft OLE DB Provider for ODBC DriversI have gone in the SQL database and messed arounf with changing the columntype from nvchar to nchar and changing the length, but can not figure thisout. I cannot tell whether this is a Frontpage problem or a SQL problem.Any help would be appreciated! Thanks!DarrenMCP
View 1 Replies View Related
Hi
I have a scenario while creating a report where I need to maintain the same number of rows for a column, even if the data grows or shrinks for that column
example
Each Scenario is grouped by ID
Scenario 1
columnA
Row1 X X
Row2 X
Row3 X
Row4 XYZ
Scenario 2
columnA
Row1 XA
Row2 XYZ
In both scenario's I need to control from front end
the number of row should remain 4
I am grouping by row field say Type
This field Type varies for each ID group
Hello all. Yes, this is another question relating to how to make a front-end to a SQL database using ASP.NET. I performed a search in this forum and did not find the exact answer to my questions (one responder to the other threads about this topic even said this is the incorrect sub-forum for this question, but I found no other appropriate place; considering this sub-forum is for asking beginner questions).
Besides the "quickstart" tutorials linked off this site, can you point me to other web resources (tutorials, walk-throughs, etc) or books that would provide the necessary information to develop a SQL front-end? Specifically I need help creating the Update Record and Delete Record pages.
I was hoping a "rapid development" tool could be used. At my disposal I have Visual Studio .NET 2003, Dreamweaver MX 2004, and FrontPage 2003.
Thank you for your time.
I am using Access 97 as a front end to access SQL 7 server on NT 4.0 server.
I've set up security model based on NT authentication only. Users have login right to login to SQL and they have public & dataread & denydatawrite access. They also have SELECT permission on a table object and have no permission to INSERT, DELETE and UPDATE.
When I use Access 97 to access a database, users are still capable of inserting and deleteing records in tables.
Am I doing something wrong?
Thanks, Michael.
Maybe because I have worked mainly with VB as a front end to SQL Server so I am biased, but I now need definite reasons (I am on a committee for potential future directions) for using VB as opposed to Access for front ending SQL/Oracle, etc. I would also like to use ADO as oppose to DAO. Right now we are using Access with DAO.
Any ideas greatly appreciated.
Hi All,
From front end application, I execute a SQL
`exec sp_password oldpassword,newpassword
and I got error message :
"Error code# 15002 :[Visigenics] [ODBC MS
SQL Server 6 Driver] [MS SQL Server]
The procedure `sp_password` cannot be
executed from within a transaction.".
If I logged in directly to SQL 6.5 without going
thru the front-end application then I could
change password successfully.
Has anyone tried to change password FROM
a front-end application? Our shop uses
Omnis 7.3.6 as front end and SQL 6.5 as back
end. TIA.
David Nguyen
Hi,
We have just moved a largish Access database (180mb, 78 tables, largest
tables have about 250k records) to SQL 2k. The original app had an Access
back end (now loaded to SQL), and an Access front end (on each client) which is using some
local temporary tables, about 600 queries, and several thousand lines of
code using ADO and DAO). The Front end was relinked to SQL back end. When
testing everything seemed to run OK, but under load (15-20 users) the new
app just crawls. Routines that used to take seconds now take 10s of minutes.
ODBC timeouts or blocks are common.
Any idea why should the SQL back end be so much slower than Access. both the
Access back end and SQL2k are on the same server (Win 2k Adv. with RAID 5,
dual 600mhz Pent III, 512Mb RAM).
I realize that Access is not the best front end but that is what we have to work with.
Any help would be appreciated, as I am ready to swith back to Access.
Thanks,
Jakub