What I'm trying to do is provide a solution where users can upload an image and a description, to a database, so I'm trying to insert the title and description then return the @@identity for the image upload function which will name the image like this
image_23.jpg (23 being the @@identity) resize it and save it to specified directory
I cant seem to get the identity to return to my script.
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 :
Sorry I'm new in this,Add a record using T-SQL and Connection.ExecuteHow can i insert the identity in a VB-variableDim objConn As ADODB.ConnectionSet objConn = New ADODB.ConnectionobjConn = CurrentProject.ConnectionobjConn.OpenobjConn.Execute "INSERT INTO CONTACT (CNT_PHONE) VALUES ('012345678')SELECT @@IDENTITY as 'NewID'"Msgbox NewID triggers an error !Filip
I would like to use ADO in a new project but I need to find out how to return the identity field value to ADO out of a stored procedure. I have not done alot with ADO or with SQL 7.0 so if anyone has an example of the SQL and the ADO code that I would need I would greatly appriate it.
Hello, I'm using C# to access sql server. When I execute an insert command, I want to get the value of the column ID(ID is an identity column in my table defination) . Is there any method I can use? Thanks.
I have a stored procedure where I am passing in several strings which are concatenated to form an Insert SQL statement. I then return the identity value from the insert - problem is, the identity value is coming back NULL.
FYI, the real problem I am trying to solve is simply to return the identity after an insert. I could write a simple insert sp that returns the identity, but my code is written to handle generic situations and build an INSERT statement as needed. So if there are any other ideas of executing an insert statement and getting the identity, please let me know.
hello, I'm new to store procedure in slq 2000. I want to create a sproc in my sql 2000 sevrer,I have a table named "Supplier", now I want to return my identiy field value when I use insert into sql command (insert into "Supplier") successful. So how to create this store procedure ? much thanks
I have a stored proc that will insert a new row into a table with the values of the parameters you pass in. I need it to return the value of the ID that's generated by an Identity column once the row has been written and that value has been generated. If I just do a SELECT Max(), I could accidentally grab a row written by someone else, right?
My current sp looks like this:
CREATE PROCEDURE sp_SaveNewLabel
-- @LabelID int output @LabelType int , @Logo int , @Field01 char(30)
AS
INSERT INTO tbLabel (LabelType , Logo , Field01)
VALUES (@LabelType , @Logo , @PrintCC , @Field01)
How do I grab the new LabelID (the column is int, Identity) and return it from the stored proc. Any help would be greatly appreciated...
I'm having a problem I do an insert into a table but I want to return the value of the identity field of that insert so I can email a confirmation. For some reason this code doesn't work. Below is the stored procedure I'm calling and below that the code I'm using. What am I doing wrong. The value I have returned is null when it should be a number. Any suggestions. Why does finalMagicNum2 come back null when it should grab the identity field of the inserted record.
' Sequence ID for request magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@theSequence", SqlDbType.NVarChar, 8)) magicCommand.SelectCommand.Parameters("@theSequence").Value = "41833"
' Subject for new Wac Ticket magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@theSubject", SqlDbType.NVarChar, 8)) magicCommand.SelectCommand.Parameters("@theSubject").Value = "1064"
' First Name Field magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@theFirstName", SqlDbType.NVarChar, 50)) magicCommand.SelectCommand.Parameters("@theFirstName").Value = orderFirstName
' Last Name Field magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@theLastName", SqlDbType.NVarChar, 75)) magicCommand.SelectCommand.Parameters("@theLastName").Value = orderLastName
DSMagic = new DataSet() magicCommand.Fill(DSMagic,"employees")
If DSMagic.Tables("_smdba_._telmaste_").Rows.Count > 0 Then finalMagicNum2 = DSMagic.Tables("_smdba_._telmaste_").Rows(0)("finalMagic").toString End If
how do i return the identity from stored procedure to asp.net code behind page? CREATE PROCEDURE [dbo].[myInsert] ( @Name varchar(35), @LastIdentityNumber int output)AS insert into table1 (name) values (@name) DECLARE @IdentityNumber intSET @IdentityNumber = SCOPE_IDENTITY()SELECT @IdentityNumber as LastIdentityNumber code behind: public void _Insert( string _Name, { DbCommand dbCommand = db.GetStoredProcCommand("name_Insert");db.AddInParameter(dbCommand, "name", DbType.String, _userId); db.AddParameter(dbCommand, "@IdentityNumber", DbType.Int32, ParameterDirection.Output, "", DataRowVersion.Current, null); db.ExecuteNonQuery(dbCommand); int a = (int)db.GetParameterValue(dbCommand,"@IdentityNumber"); whats wrong with to the above code?
We're using ASPUpload as a tool to upload files to our server and savethe details to SQLServer. However, I have an application where I needto return the pkID of the just saved file. I'm assuming that I coulduse the @@Identity command but cannot get this to function.Has anyone used this command with ASPUpload with an success, or anyother methods that could be used?Thanks.
Hi.here is my code with my problem described in the syntax.I am using asp.net 1.1 and VB.NETThanks in advance for your help.I am still a beginner and I know that your time is precious. I would really appreciate it if you could "fill" my example function with the right code that returns the new ID of the newly inserted row. Public Function howToReturnID(ByVal aCompany As String, ByVal aName As String) As Integer 'that is the variable for the new id.Dim intNewID As Integer Dim strSQL As String = "INSERT INTO tblAnfragen(aCompany, aName)" & _ "VALUES (@aCompany, @aName); SELECT @NewID = @@identity" Dim dbConnection As SqlConnection = New SqlConnection(connectionString)Dim dbCommand As SqlCommand = New SqlCommand()dbCommand.CommandText = strSQL 'Here is my problem.'What do I have to do in order to add the parameter @NewID and'how do I read and return the value of @NewID within that function howToReturnID'any help is greatly appreciated!'I cannot use SPs in this application - have to do it this way! :-( dbCommand.Parameters.Add("@aFirma", aCompany.Trim)dbCommand.Parameters.Add("@aAnsprAnrede", aName.Trim) dbCommand.Connection = dbConnection TrydbConnection.Open()dbCommand.ExecuteNonQuery() 'here i want to return the new ID!Return intNewID Catch ex As Exception Throw New System.Exception("Error: " & ex.Message.ToString()) Finally dbCommand.Dispose()dbConnection.Close()dbConnection.Dispose() End Try End Function
SELECT emp_id AS emp_num, fname AS first, minit AS middle, lname AS last, IDENTITY(int, @number, 1) AS job_num, job_lvl AS job_level, pub_id, hire_date INTO employees FROM employee
Is there any way i can use variable inside the identity function like in the above example?.
Im a self proclaimed newb and Im stuck on returning a value from a function. I want to get the AttendID that the SQL statement returns and dump it into strAttendID: Response.Redirect("ClassSurvey.aspx?Pupil=" & strAttendID)I cant seem to accomplish this. It returns nothing. Please help.TIA,Stue<code>Function Get_AttendID(ByVal strAttendID As String) As SqlDataReaderDim connString As String = ConfigurationSettings.AppSettings("ClassDB")Dim sqlConn As New SqlConnection(connString)Dim sqlCmd As SqlCommandDim dr As SqlDataReader sqlConn.Open()Dim strSQL As String = "Select AttendID from attendees Where FirstName=@FirstName and LastName=@LastName and classbegdt = @classbegdt and survey = '0'" sqlCmd = New SqlCommand(strSQL, sqlConn) sqlCmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 50)sqlCmd.Parameters("@FirstName").Value = tbFirstName.TextsqlCmd.Parameters.Add("@LastName", SqlDbType.VarChar, 50)sqlCmd.Parameters("@LastName").Value = tbLastName.TextsqlCmd.Parameters.Add("@classbegdt", SqlDbType.DateTime, 8)sqlCmd.Parameters("@classbegdt").Value = calBegDate.SelectedDate.ToShortDateStringdr = sqlCmd.ExecuteReader()dr.Close()sqlConn.Close() Return dr End Function</code>
I want to write a function that returns the physical filepath of the master database for its MDF and LDF files respectively. This information will then be used to create a new database in the same location as the master database for those servers that do not have the MDF and LDF files in the default locations.
Below I have the T-SQL for the function created and a test query I am using to test the results. If I print out the value of @MDF_FILE_PATH within the funtion, I get the result needed. When making a call to the function and printing out the variable, all I get is the first letter of the drive and nothing else.
You may notice that in the function how CHARINDEX is being used. I am not sure why, but if I put a backslash "" as expression1 within the SELECT statement, I do not get the value of the drive. In other words I get "MSSQLData" instead of "D:MSSQLData" I then supply the backslash in the SET statement. I assume that this has something to do with my question.
Any suggestions? Thank you.
HERE IS T-SQL FOR THE FUNCTION IF OBJECT_ID('fn_sqlmgr_get_mdf_filepath') IS NOT NULL BEGIN DROP FUNCTION fn_sqlmgr_get_mdf_filepath END GO
CREATE FUNCTION fn_sqlmgr_get_mdf_filepath ( @MDF_FILE_PATH NVARCHAR(1000)--Variable to hold the path of the MDF File of a database. ) RETURNS NVARCHAR AS
BEGIN
--Extract the file path for the database MDF physical file. SELECT @MDF_FILE_PATH = SUBSTRING(mdf.filename, CHARINDEX('', filename)+1, LEN(filename)) FROM master..sysfiles mdf WHERE mdf.groupid = 1
SET @MDF_FILE_PATH = SUBSTRING(@MDF_FILE_PATH, 1, LEN(@MDF_FILE_PATH) - CHARINDEX('', REVERSE(@MDF_FILE_PATH)))
RETURN @MDF_FILE_PATH
END
HERE IS THE TEST I AM USING AGAINST THE FUNCTION SET NOCOUNT ON
DECLARE @MDF_FILE_PATH NVARCHAR(1000)--Variable to hold the path of the MDF File of a database.
Hii every one When i use the function of (select) from the data bass it return dataset or some thing else But I need it to return string or the data element which in the query not all the query
like
I dont need that _____________ | Id | Name | ----------------- | 1 | Bill | -------------------- I dont need All of that to display But I need to display the name only in Label or textbox like Bill
Hi, I basically do not want to return a null value as a result of using a sum function (using sum against 0 rows). Is there a common way to avoid this? Thanx
Dear all, can i write a table return function like this?
create function my_function( returns @MYTABLE table(NAME VARCHAR(20),SES VARCHAR(20),CNT DECIMAL(10),MYDATE DATATIME) insert into @mytable select col1,col2,col3,col4 from tab1 go select col1,col2,col3,col4 from tab2 go select col1,col2,col3,col4 from tab3 go select col1,col2,col3,col4 from tab4 go return end
am i doing correct? what i'm expecting from this function is, i need all the data from select statements should be inserted into one table. please guide me in this regard
Vinod Even you learn 1%, Learn it with 100% confidence.
return gpa_sem_3; } -------------------------------------------------------------------------------------------------------------------------------------------- //this is the main program(part of it only)
case 1:
system ( "cls" ); gpa_sem_1 = sem_1( );
break; -------------------------------------------------------------------------------------------------------------------------------------------------- // why doesn't the error promp when the user enters an invalid entry? in the codes below.
We have SELECT INTO queries that use the IDENTITY function to assign a sequential row number to a result set based upon a sort order. This has been helpful in SQL Server 2000 for median determination. It appears, however, that in SQL Server 2005, the row numbers are not assigned sequentially, or maybe they are assigned before the sort order is applied.
Can anyone verify whether the IDENTITY function has changed behavior between 2000 and 2005? We would prefer not to have to make changes to existing queries. Thank you.
(BTW, the workaround we found so far is to put the initial SELECT...ORDER BY in a subquery, but then we had to include a phrase like TOP 10000000 to pass syntax check. Is there a better way?)
Hi,I have a sql 2005 function who return a distance from 2 zipcodes. This function is called from a Stored procedure like this :SELECT *, dbo.fn_GetDistance (...) AS DistanceIn this function, i have a Latitude and i want this Latitude to be also returned.It is possible or a function can return only one variable?If it is possible, what's the syntax of it?Thanks in advance
Cannot see where I am going wrong. I always get a value of 0. I know my function works correctly, so it must be the VB.
CREATE FUNCTION [dbo].[getNextProjectID] () RETURNS varchar(10) AS BEGIN '''''''''''''''''''........................... DECLARE @vNextProjectID varchar(10) RETURN @vNextProjectID END
Sub LoadNextProjectNumber() Dim vProjectID As String Dim cmd As New SqlClient.SqlCommand() cmd.Connection = sqlConn cmd.CommandText = "getNextProjectID"
Is there anything new on returning max of two values in SQL 05? There seems to be nothing I have searched everywhere. Only option might be to create my own UDF or do a CASE statement, but I could not believe my eyes there is no such thing?
Hi I need a function which gets 3 parameters and input and returns 4 integers as output. I don't know how to return 4 integers as output any idea is appreciated.
