Database Return Issue
Dec 12, 2005Hello all!
I have a stored procedure that I want to return a value to a C# varaiable:
Code:
ADVERTISEMENT
Best Way To Return Data From SQL Database, Without Giving Access To Database
May 8, 2008Hello,
I have a quick question regarding getting data from a SQL database but I am slightly confused as to the best way to handle it.
Basically on of the projects I am working on I need to send data to another company, there are several calls required to the database to bring back various options for changing the questions asked on the front end.
Up to now all that has happened is there has been a mutual agreement between myself and this other company and they have just had access to the database to call a series of stored procs which I have written for them to access the data. Recently however the situation has changed and my client wants me stop them accessing the database however they still need to recieve the information from the database they recieve now.
What will be the best way to handle this? My knowledge of SQL is farily limited presently and I am only ust getting into learning a lot more about it.
Any help and advice would be greatly appreciated.
Kind regards,
Lee
How Can I Return A Database From The DataBase Explorer Of VB 2005 Express To The Object Explorer Of SQL Server Management Studio
Mar 3, 2008Hi all,
I just realized recently that a database "XYZ" in the Object Explorer of my SQL Server Management Studio Express (SSMSE) is put in the Database Explorer of my VB 2005 Express for processing a Stored Procedure in executing the SELECT statements (not by using Input and/or Output Parameters) during the ADO.NET 2.0-VB 2005 Express programming, then the content of the database "XYZ" is not in the SSMSE. How can I return the database "XYZ" from the DataBase Explorer of VB 2005 Express back to the Object Explorer of SQL Server Management Studio Express (SSMSE) safely? Please help and advise.
Thanks in advance,
Scott Chang
Return All Fields Of Database
Feb 16, 2007Hello,
Is it possible to return all the field names of a database. I do not want the data rows. Just a list of fields in the databse.
Thanks
How Can I Return The 5 Records From A Database Table Using Ado.net
Apr 24, 2007I have an incident reporting management application. People are supposed to report incidents by this application and every time some one reports an incident, they also select thier employee#(reqiured field). so how can write an sql statement that returns only the Top 5 incident reporters i.e going by employee number. Iam thinking of applying a COUNT function on the incident_id and grouping by Employee# but then how do i make sure that only the top 5 incident reporters are returned.
View 1 Replies View RelatedReturn Value For One Selected Row From A SQL Database/Dataset Problem
Jan 26, 2008I try to get a value from ArtikkelId from a SQL database into a textbox. Can anyone help me?
My Code-----------------------------------------
Imports System.DataImports System.Data.SqlClient
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim Conn_News_db As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("NEWS_CS1").ToString()Dim SQL_conn As New SqlConnection(Conn_News_db)Dim SQL_cmd1 As New SqlCommand()Dim myDataSet As New DataSetDim Select_ArtId_Max As String = "Select ArtikkelId from Hoved where ArtikkelId=(Select MAX(ArtikkelId) from hoved)"Dim SQL_DA As New SqlDataAdapterSQL_DA.Fill(myDataSet)TextBox1.Text = myDataSet.Tables.Item["ArtikkelId"]
End Sub----------------------------------
Trying To Return MAX(value) Of A Database In Formview Text='&<%# Eval(MAX(ID1)) %&>
Mar 4, 2008I am trying to return the ID of the last record entered into my database so the user will have his Record ID. I'm trying to do this in a from view.
text='<%#eval("ID")%>'
SelectCommand="SELECT MAX(ID) FROM [Webenhancetest]">
If it is done in this manner, it says DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'MAX'.
and if I just use SELECT IDFROM [Webenhancetest it works but only returns the first record which is 1.
How do I grab the last record, or Max(ID)???
Return All User Tables In A Database Where Table Name Is
May 5, 2008
How do I return a list of all user tables in a database where table name starts with EmpID_
for example the table names are:
EmpID_Countries
EmpID_Regions
EmpID_States
EmpID_Colorado
EmpID_Arizona
etc etc...........
I am using SQL Server 2005. Thanks.
Return The Last Identity Value Generated All Tables In A Database
Apr 14, 2008
I need a script that returns the last identity value generated for each table in a database that has an identity column.
I am using SQL Server 2005.
Thanks.
Return A List Of New Tables Or Columns Which Do Not Exist In A Database
Mar 24, 2008
What SQL statement can i run to return a list of new tables or columns that exist in the Sales Database on Server A, which do not exist in the Sales Database in Server B.
Server A and Server B are linked servers.
I am using SQL Server 2005. Thanks.
How Do You Create A Multiple Word Search Box To Return Rows From A Sql Database In Asp.net 2.0
Jun 12, 2007I am using VS2005 to construct a website. I have a sql database with 3 datasets. One of the table adapters is called Proudcts and has the following fields. ProductItem, Description, Price, ProductID, PackSizeI want to be able to see a summary of the products (there are thousands of them) using one or more words (or partial words) in one text box to search in only 3 fields (ProductItem, Description, ProductID, ). This needs to show only records which contain the search criteria in a gridview?This is such a basic requirement for a website, and can be found on many sites, but I haven't found how to do it.Thanks, Bri
View 7 Replies View RelatedReturn Error Code (return Value) From A Stored Procedure Using A Sql Task
Feb 12, 2008
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.
SQL Server 2012 :: Return Only Count Of Database With Backup Older Than 24 Hours
Nov 11, 2014I need only the count of databases that last fullbackup was older then 24 hours or null. and status is online. I have tried
SELECT Count(DISTINCT msdb.dbo.backupset.database_name)
From msdb.dbo.backupset
where datediff(day,backup_finish_date,GETDATE()) > 1 -- or is null
and Database_Name not in ('tempdb','ReportServerTempDB','AdventureWorksDW','AdventureWorks') --online also
group by Database_name, backup_finish_date
Tried using where max(backup_finish_date) < datediff(day,backup_finish_date,GETDATE()) .But get the aggregate in where clause error. get a count of databases with backups older than 24 hours not including the samples, report service, and tempdb. I would also want to put status is online but havent gotten the above to work so havent tried to add that yet.
How To Return SqlDataReader And Return Value (page Count) From SPROC
Jan 2, 2006This 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
'Return' Statement In A Function,Get,or Operator Must Return A Value....Question
Aug 28, 2007
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,
Using Output Clause And Return Timestamp And Return Value
Oct 28, 2006I am trying to bring my stored proc's into the 21st century with try catch and the output clause. In the past I have returned info like the new timestamp, the new identity (if an insert sproc), username with output params and a return value as well. I have checked if error is a concurrency violation(I check if @@rowcount is 0 and if so my return value is a special number.)
I have used the old goto method for trapping errors committing or rolling back the transaction.
Now I want to use the try,catch with transactions. This is easy enough but how do I do what I had done before?
I get an error returning the new timestamp in the Output clause (tstamp is my timestamp field -- so I am using inserted.tstamp).
Plus how do I check for concerrency error. Is it the same as before and if so would the check of @@rowcount be in the catch section?
So how to return timestamp and a return value and how to check for concurrency all in the try/catch.
by the way I read that you could not return an identity in the output clause but I had no problem.
Thanks for help on this
SM haig
Why SP Return SQLException When No Data Return ?
Jul 24, 2006hi, good day,
i have using BCP to output SP return data into txt file, however, when it return nothing , it give SQLException like "no rows affected" , i have try to find out the solution , which include put "SET NOCOUNT ON" command before select statement, but it doesn't help :(
anyone know how to handle the problem when SP return no data ?
thanks in advance
How Can I Return Data From A Database And Fill A Data Grid View?
May 2, 2007Hi ! I have a textbox and a Search button. When the user inputs a value and press the button, i want a datagrid to be filled.
The following code runs:
Dim connect As New Data.SqlClient.SqlConnection( _
"Server=SrvnameSQLEXPRESS;Integrated Security=True; UID= ;password= ; database=dtbsname")
connect.Open()
Dim cmd As New SqlCommand
Dim valor As New SqlParameter("@valor", SqlDbType.VarChar, 50)
cmd.CommandText = "Ver_Contactos_Reducido"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = connect
cmd.Parameters.Add(valor)
cmd.Parameters("@valor").Value = texto
Dim adapter As New SqlDataAdapter(cmd)
Dim ds As New System.Data.DataSet()
adapter.Fill(ds)
GridViewContactos.DataSource = ds
GridViewContactos.DataBind()
connect.Close()
I drag a datagrid on the page and only changed its Id.
Into the SQL database the stored procedure is the following:
ALTER PROCEDURE [dbo].[Ver_Contactos_Reducido]
(@Valor VARCHAR(100))
AS
BEGIN
SET NOCOUNT ON;
SELECT NombreRazonSocial, Nombre, Apellido,TelefonoLaboral,
Interno, TelefonoCelular, Email1, Organizacion
FROM CONTACTOS
WHERE NombreRazonSocial = @Valor OR Nombre = @Valor OR Apellido = @Valor OR TelefonoLaboral = @Valor OR Interno = @Valor OR
TelefonoCelular =@Valor OR Email1 = @Valor OR Organizacion= @Valor
END
When i run the page i can't see the datagrid, and after i enter a text and press the button, nothing happens. What am i doing wrong??
Thks!!
SQL Server 2012 :: Query To Return Top 10 Tables On Each Database On Server?
Nov 21, 2013i need a query to return the top 10 tables in each database on a server. have used EXEC sp_msforeachtable 'sp_spaceused ''?''' which returns what I need for one db but I need it to loop through and gather the info for all dbs on server. maybe need cursor not sure. for reporting reasons i would like to include the name of the server and name of database.
View 3 Replies View RelatedTransact SQL :: Return Set Of Values From SELECT As One Of Return Values From Stored Procedure
Aug 19, 2015I have a stored procedure that selects the unique Name of an item from one table.
SELECT DISTINCT ChainName from Chains
For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.
SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName
Each row of the result set returned by the stored procedure would contain:
ChainName, Array of StoreNames (or comma separated strings or whatever)
How can I code a stored procedure to do this?
Northwind Database In SQL Server Management Studio Express Is Lost Or Used/processed By VB 2005 Express:How To Locate/return It
Dec 3, 2007Hi all,
In the last one and half years, I used the Northwind Database in SQL Server Management Studio Express (SSMSE) to learn the programming of SqlConnections, Data sources, Database Exploere, ADO.NET 2.0, etc. via VB 2005 Express.
The Northwind Database in my SSMSE got lost very often, but I was not aware of it. How can I know where the Northwind Database is used or processed by my VB 2005 Express projects that were the examples of some tutorial books or my trial projects? How can I release the Northwind Database back to my SSMSE from the VB 2005 Express projects? Please help and advise.
Thanks in advance,
Scott Chang
Return Value From SP
Dec 6, 2006I have the following SP
SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE spGetResettedPassword@Email nvarchar(50)ASBEGIN SET NOCOUNT ON; declare @newid uniqueidentifier set @newid=newid() declare @newPass nvarchar(10) set @newPass=convert(nvarchar(100),@newid) set @newPass=substring(@newPass,1,7) UPDATE aspnet_Membership SET Password=@newPass WHERE Email=@Email return @newPassENDGO
When calling it from code like this: MyConnection.Open() cmd = New SqlCommand("spGetResettedPassword", MyConnection) cmd.CommandType = Data.CommandType.StoredProcedure myTrans = MyConnection.BeginTransaction() cmd.Transaction = myTrans fBeginTransCalled = True bSuccess = False cmd.Parameters.Add(New SqlParameter("@Email", Email)) Dim NewPassword As Object = cmd.ExecuteScalar If NewPassword <> Nothing And CType(NewPassword, String) <> "" _ And TypeOf (NewPassword) Is String Then Return CStr(NewPassword) Else Return "ERROR, CONTACT US ON THE SITE" End If bSuccess = True fBeginTransCalled = False
I get the following error:
Conversion failed when converting the nvarchar value '40DC5F3' to data type int.
What is happening here?!?!
Return Value From SQL
Jun 14, 2008I am trying to get the following to work, here is the stored procedureSTORED PROCEDURE (@varA varchar(25),@varb varchar(25))AS IF EXISTS (SELECT column1 FROM table1 WHERE nameA=@NameA)RETURN 01IF EXISTS (SELECT column2 FROM table1 WHERE nameB=@nameB)RETURN 02ELSE BEGIN INSERT INTO table1 (nameA, NameB) VALUES (@nameA, @nameB) RETURN 03 END **************************************************************************** dim cn as New sqlConnection(connectionstring) dim cm as sqlcommand cn.Open() With Cm .CommandType = CommandType.StoredProcedure .CommandText = "STOREDPROCEDURE" .Connection = cn End With 'INSERT Names Cm.Parameters.Add("@nameA", SqlDbType.VarChar, 25).Value = nameA.text Cm.Parameters.Add("@nameB", SqlDbType.VarChar, 25).Value = nameB.text param_returnValue = Cm.Parameters.Add("RETURN_VALUE", SqlDbType.Int) param_returnValue.Direction = ParameterDirection.ReturnValue*******************************************************************************************This is where I get stuck. I need to display certain error messages depending on the return value.******************************************************************************************* dim insertStatus as integer insertStatus = Cm.ExecuteScalar() If insertStatus = 01 Then warningLabel.Text = "NameA AlreadyExists." If insertStatus = 02 Then warningLabel.Text = "NameA AlreadyExists." If insertStatus = 9999 Then Response.Redirect("success.aspx") End If End If End If I am pretty sure the IF statements arent the way to go especially because it is not working but i am curious to how I am to make this work.
View 2 Replies View RelatedReturn
May 18, 2004whats wrong with this SP? I want @id to contain the row identity of the newly created row as a return value.ALTER PROCEDURE setCountry
(
@name varchar( 50 ) = NULL,
@alt varchar( 24 ) = NULL,
@code varchar( 3 ) = NULL,
@id int = null OUT
)
AS
SET NOCOUNT ON
INSERT INTO Countries( CountryName, CountryAltName, CountryCode ) VALUES ( @name, @alt, @code )
@id = @@identity
RETURN
If...Else Return Value
Oct 11, 2006Hi,
Here is my sp:
Quote: CREATE PROCEDURE dbo.usp_ProfQuals_Add_AwardingBody_nr
(
@awardbody varchar(100)
)
AS
BEGIN
declare @message int
if EXISTS(SELECT vchr_awardingbody from tbl_ProfQuals_AwardingBody where vchr_awardingbody = @awardbody)
set @message = 1
else
set @message = 2
insert into tbl_ProfQuals_AwardingBody (vchr_AwardingBody) values (@awardbody)
END
return @message
The problem I am having is that it returns the correct value if there is a record that exists or indeed if it doesn't BUT ... even when a record exists it still carries out the insert!
Any help much appreciated.
Dave
How Could I Return The Next Row(Or 8th Row For Example)
Sep 25, 2005Hi,
I want to return the next row in a select ... order by ... cursor.
I don't want to loop,just SQL,Do you know any solution?
For example: in Oracle we use rownum,is there any equivalent in SQL Server?
-Thanks
Getting The Return Value
Aug 15, 2007Hello everyone -
Please advise on why the output value is an empty
declare
@DynamicSQL3 NVARCHAR(4000
SET @DynamicSQL3 = 'SELECT Security_Level_ID ' +
'FROM ' + @DatabaseName + '.dbo.Security_Level_Master ' +
'WHERE Security_Level_Name = ''Administrator'' '
exec sp_executesql @DynamicSQL3, N'@i int output', @AdministratorSecurityID output
I run the query and see the grid being populated with the proper
security_level_id in the query analyzer,
but when i execute a print @AdministratorSecurityID
there is a space character in the results window
please advise on what i am doing wrong
thanks
tony
Return SUM That Is Not 0?
Oct 15, 2013I have this table that contains transactions for a specific client and some clients, when I total the transactions, have a zero balance.
I want to be able to filter those clients out, only show the ones that have open balances.
Here's an example:
DECLARE @T1 TABLE( CODE VARCHAR(6), ITEM INTEGER, AMOUNT INTEGER)
INSERT INTO @T1
VALUES
('ABC',4001,110),
('ABC', 4001, -110),
('ABC',4002,-5.5),
[code]....
If you look at the table, client ABC has a lot of transactions but if I SUM them the balance is 0, same with client GHI. The only row that show show on my query is client DEF.
I am trying to do something like this:
select *
from @T1
having Code in ( SELECT code
from @T1
group by code
having SUM(amount) <> 0)
but I get an error.
No Return Value
Mar 6, 2007Hi all. How could i trigger the result in select statement? If the query does not return a value, there must be a prompt message to appear.
thanks
-Ron-
Return The Last Row Only
Jul 5, 2007Hello!
I'm new to SQL and i've searched everywhere for the answer to this question but I can't seem to find the anwer anywhere. My question is.....
If there are multiple rows for a specific field, is there a way in SQL to only return the last row based on that specific field.
For example, I wrote a query that returns a list of patients who have a specific lab type. If a patient has had several labs taken, they show up in the result set several times (for each lab date). I only want to return the most recent lab date.
Here is my query........
SELECT id, resulted_test_desc, result_value, units, update_date
FROM dbo.lab_result LEFT OUTER JOIN
dbo.mpi ON dbo.lab_result.blind_key = dbo.mpi.chart LEFT OUTER JOIN
dbo.mpi_xref ON dbo.mpi.chart = dbo.mpi_xref.blind_key
WHERE resulted_test_desc = 'Hemoglobin'
Any help would be greatly appreciated.
Thanks!
Amedeo
How To Get A SP Return Value On Another SP?
Sep 12, 2006I have a SP, which will exec other SP depend on the input.the "other SP" need to return a integer back.How to do this?Thanks for give me a hand.
View 4 Replies View RelatedReturn New Id
Jul 20, 2005Dear All,I try this but it keeps returning zero !Dim newid As LongCurrentProject.Connection.Execute "INSERT INTO [ORDER] (ORD_P_ID,ORD_CREATION_DATE) VALUES ('4004', CONVERT(DATETIME,'" & Year(Date) & "-" &Month(Date) & "-" & Day(Date) & " 00:00:00', 102)); select @@identity as'newID'"Me.RequeryMsgBox newid >> returns 0Msgbox DMax("ORD_ID","[ORDER]") >> returns the new idMy question stays: is it possible to return the newid into a VB-variabledirectly?Filip
View 1 Replies View RelatedReturn One Row
Jul 20, 2005HelloI was wondering if someone could help me out with something.With the following rows:agnt_num supv_num meet_num strt_dt strt_lvl trm_dt trm_lvl1 1 1 10-10-2000 01 10-15-2000 021 1 2 10-09-2000 06 051 2 1 10-08-2000 05 10-20-2000 011 2 1 10-05-2000 01 10-15-2000 03What I need is SQL to get one row per agnt/supv with the following:strt_dt, strt_lvl of earliest strt_dttrm_dt, trm_lvl of the latest trm_dt but only if all trm_dts forthe supv arefilled in, otherwise null for bothagnt_num supv_num strt_dt strt_lvl trm_dt trm_lvl1 1 10-09-2000 06 null null1 2 10-05-2000 01 10-20-2000 01If anyone has any ideas on the most efficient way to accomplish this,I'd appreciate a reply.Thanks!
View 5 Replies View Related