Return Code From Exec
Sep 30, 2004
Hi
I have a maintenance proceudre that trims data tables on the live site, but prior to trimming i need to execute procs via linked servers at four seprate locations (subscribers) to ensure all of their data is up to date before the trim happens (the deletes are blocked at the subs). my problem is that if i go
exec @error_id = procname
and an error such as permission being denied or proc missing, the return code
is still 0 - therefor it looks like th proc ran successfully, meanwhile it didnt.
does anyone know how to check for these sorts of erors, so i dont leave this to chance?
thnks
des
View 5 Replies
ADVERTISEMENT
May 19, 2006
I am just trying to capture the return code from a stored proc as follows and if I get a 1 I want the SQL Task to follow a failure(red) constrainst workflow and send a SMTP mail task warning the customer. How do I achieve the Exec SQL Task portion of this, i get a strange error message [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".
Using OLEDB connection, I utilize SQL: EXEC ? = dbo.CheckCatLog
EXEC SQL Task Editer settings:
RESULTSET: Single Row
PARAMETER MAPPING: User::giBatchID
DIRECTION: OUTPUT
DATATYPE: LONG
PARAMETER NAME: 0
PS-Not sure if I need my variable giBatchID which is an INT32 but I thought it is a good idea to feed the output into here just in case there is no way that the EXEC SQL TASK can chose the failure constrainst workflow if I get a 1 returned or success constraint workflow if I get a 0 returned from stored proceedure
CREATE PROCEDURE CheckCatLog
@OutSuccess INT
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
DECLARE @RowCountCAT INT
DECLARE @RowCountLOG INT
---these totals should match
SELECT @RowCountCAT = (SELECT Count(*) FROM mydb_Staging.dbo.S_CAT)
SELECT @RowCountLOG = (SELECT Count(*) FROM mydb_Staging.dbo.S_LOG)
--PRINT @RowCountCAT
--PRINT @RowCountLOG
BEGIN
IF @RowCountCAT <> @RowCountLOG
--PRINT 'Volume of jobs from the CAT file does not match volume of jobs from the LOG file'
--RETURN 1
SET @OutSuccess = 1
END
GO
Thanks in advance
Dave
View 6 Replies
View Related
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.
View 2 Replies
View Related
Aug 29, 2007
Hi, I have an sql query like this :DECLARE @TableName varchar(200), @ColumnName varchar(200), @EmployeeID varchar(200), @Result varchar(200);SET @TableName = 'Customer';SET @ColumnName = 'First_Name';SET @CustomerID = 28;-- This line return ErrorSET @Result = EXEC ('select' + @ColumnName + ' from ' + @TableName + ' where Recid = ' + @CustomerID + '');Print @Result; I am pretty sure the SELECT statement in EXEC will only return 0 or 1 record. But how to capture result from EXEC? Thanks
View 1 Replies
View Related
Jan 12, 2004
OK, I'm fairly new to SQL Server, but I know SQL and databases pretty well. I'm just starting to use the dynamic SQL feature of SQL Server (with EXEC), and am wondering how to return a scalar value from a dynamic SQL expression. I realize I can't use EXEC in a user-defined function, but I want to create a stored procedure with one OUTPUT variable so I can simulate a function. The following code does not work, because EXEC does not return a value:
CREATE PROCEDURE dbo.ExecFunction ( @ScalarSELECTString varchar(250), @@ReturnVal sql_variant )
set @@ReturnVal = ( exec @ScalarSELECTString )
go
So, I was wondering if someone might be able to suggest a way to re-write the above code to achieve the same effect. Thanks in advance.
View 8 Replies
View Related
Oct 19, 2006
ALTER PROCEDURE [dbo].[GD_SP_FACTURA_GLOBAL]
AS
DECLARE @DIR nvarchar(10)
SELECT @DIR = DIR_NOME FROM Direccao
EXECUTE dbo.GD_SP_FACTURA_ValorTotal @DIR
I Have the query above, It's correct, but I want the SUM of all the values returned by EXECUTE function.
Thanks
View 4 Replies
View Related
Aug 16, 2006
Hi,
Does anybody know how to use exec sp command from vb.net code?
Suppose, i've a control that is populated using the following code.
<ie:treenode Text="root" type="tree" Expanded="true"
TreeNodeSrc="http://localhost/mySQLXML?sql=execute+sp_GenMyXML+@pid=1" />
In the above code, how can I execute for TreeNodeSrc="http://localhost/mySQLXML?sql=execute+sp_GenMyXML+@pid=1" ???
What I want is I want to get the xml result from the sql server.
Does any body has any idea?
Any help is appreciated.
View 1 Replies
View Related
Apr 8, 2004
Hi,
Any idea how to write a (T-)SQL Stored Procedure which uses a SubQuery calling
a SELECT query from another SP??
Something like...
CREATE procedure spThisSP(@param varchar(20))
AS
SELECT theColumn FROM theTable WHERE theColumn NOT IN (EXEC spAnotherSP @param)
Cheers
View 8 Replies
View Related
Sep 21, 2007
Hi,
I am trying to find a way to return the result of an EXEC(*sqlstring*) from a function. I can return the tsql but not the result of an execute.
This is my function:
ALTER FUNCTION [dbo].[ReturnPickItemValue]
(
-- Add the parameters for the function here
@TypeID int,
@CaseID int
)
RETURNS varchar(max)
AS
BEGIN
-- Declare the return variable here
DECLARE @RTN varchar(max)
IF(SELECT IncludeDates FROM TBL_LU_PICK WHERE PickTypeID = @TypeID) = 1
BEGIN
SET @RTN = 'SELECT PickItem I +
CASE D.IsStartDateEstimated
WHEN 0 THEN CAST(StartDate as varchar)
ELSE CAST(dbo.ReturnEstimatedDate(D.IsStartDateEstimated, 0) as varchar)
END +
CASE D.IsEndDateEstimated
WHEN 0 THEN CAST(EndDate as varchar)
ELSE CAST(dbo.ReturnEstimatedDate(D.IsEndDateEstimated, 1) as varchar)
END
FROM TBL_LU_PICK L
INNER JOIN TBL_Pick_Items I ON I.PickTypeID = L.PickTypeID
INNER JOIN TBL_PICK P ON P.PickItemID = I.PickItemID
LEFT JOIN TBL_PickDates D ON D.PickID = P.PickID
WHERE L.PickTypeID = ' + CAST(@TypeID as varchar) + '
AND P.CaseID = ' + CAST(@CaseID as varchar)
END
ELSE
BEGIN
SET @RTN=
'SELECT I.PickItem
FROM TBL_LU_PICK L
INNER JOIN TBL_Pick_Items I ON I.PickTypeID = L.PickTypeID
INNER JOIN TBL_Pick P ON P.PickItemID = I.PickItemID
WHERE L.PickTypeID = ' + CAST(@TypeID as varchar) + '
AND CaseID = ' + CAST(@CaseID as varchar)
END
RETURN @RTN
END
Each time I try " RETURN EXEC(@RTN) " or something similar I get an error.
I have tried executing the tsql and assigning the result to a varchar and returning that varchar but i get an error.
Anyone with any ideas?
View 4 Replies
View Related
Oct 6, 2006
hello world how are there
View 1 Replies
View Related
Dec 27, 2007
Hi, i'm new to reporting services, rather new to heavy processing in reporting services. i have scenario
for which i need your help. so here it goes,
i have a method in Code area of the report, i'm passing parameter values to it. the method willl return me a swl query in string format. i need to execute it in data tab area. the codei have used in data tab is as below. please let me know. wat to do to make it right. thx
EXEC ('Code.Main(Parameters!Param1)' +
UNION
+ 'Code.Main(Parameters!Param2) ' +
UNION ALL
+ 'Code.Main(Parameters!Param3)')
Thx again
i need to get this report done really soon, so please, if u have any idea let me know ASAP
View 6 Replies
View Related
Jul 22, 2015
I use new query to execute my store procedure but didnt return any value is that any error for my sql statement??
USE [Pharmacy_posicnet]
GO
/****** Object: StoredProcedure [dbo].[usp_sysconf] Script Date: 22/07/2015 4:01:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[usp_sysconf]
[Code] ....
View 6 Replies
View Related
Sep 19, 2000
How do I use a @variable to hold on value return from an exec ('string command') statement.
Example for:
declare @OldID int
declare @cmd varchar(255)
declare @db varchar(25)
declare @OldOwner varchar(25)
set @db = 'DBNAME'
set @OldOwner = 'USERNAME'
select @cmd = 'select uid from ' + ltrim(rtrim(@db))'..sysusers where name = ' + "'" + ltrim(rtrim(@OldOwner)) + "'"
exec (@cmd)
How can I use @OldID to hold on UID return from this statement.
When I try use:
select @cmd = 'select @OldID = uid from ' + ltrim(rtrim(@db))'..sysusers where name = ' + "'" + ltrim(rtrim(@OldOwner)) + "'"
then I'm getting a error message: @OldID not declare.
Thanks.
View 2 Replies
View Related
Aug 23, 2006
Hello everybody!
As the topic:
Can i get the value "count(*)" from EXEC('select count(*) from xTable')
Any helps will be usefull! Thanks!
View 6 Replies
View Related
Jan 17, 2007
Greeting.
I use OdbcConnection inside clr procedure, for getting data. If I use simple EXEC dbo.clr_proc - all is OK. If I use INSERT...EXEC I recive error message: Distributed transaction enlistment failed.
I set MSDTC security options for No Authentification and Allow inbound and Allow outbound, but it's no use.
Have this problem solution? May be, I must use another method to get my data?
P.S. Linked Servers and OPENQUERY is not applicable. Sybase not describe columns in stored proc result set and one stored proc may return different result set by params.
P.S.S. Sorry for bad english.
View 1 Replies
View Related
Jan 7, 2008
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
public partial class UserDefinedFunctions
{ [Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName="Obj_Row",
IsDeterministic=true,IsPrecise=true,
TableDefinition="ObjID int,OjbDataID int,ObjDataValue nvarchar(400)",DataAccess= DataAccessKind.Read)]public static IEnumerable Obj_IDs(SqlInt32 Data_1, SqlInt32 Data_2, SqlInt32 Data_3)
{using (SqlConnection conn = new SqlConnection("context connection=true"))
{
try
{ if (!Data_2.IsNull)
{string sql = @"Select Obj_ID, Obj_Data_ID, Obj_Data_Value from tbl_Obj_2";
SqlCommand cmd = new SqlCommand(sql, conn);SqlDataAdapter da = new SqlDataAdapter(cmd);DataTable dt = new DataTable();
da.Fill(dt);
conn.Open();return dt.Rows;
}if (!Data_3.IsNull)
{string sql = @"Select Obj_ID, Obj_Data_ID, Obj_Data_Value from tbl_Obj_3";
SqlCommand cmd = new SqlCommand(sql, conn);SqlDataAdapter da = new SqlDataAdapter(cmd);DataTable dt = new DataTable();
da.Fill(dt);
conn.Open();return dt.Rows;
}
}catch (Exception ex)
{
ex.Message.ToString();
}
finally
{
conn.Close();
}
}
}public static void Obj_Row(Object item, out int ObjID, out int ObjDataID, out string ObjDataValue)
{DataRow row = (DataRow)item;
ObjID = Convert.ToInt32(row["Obj_ID"]);ObjDataID = Convert.ToInt32(row["Obj_Data_ID"]);ObjDataValue = row["Obj_Data_Value"].ToString();
}
};
//Error 1 'UserDefinedFunctions.Obj_IDs(System.Data.SqlTypes.SqlInt32, System.Data.SqlTypes.SqlInt32, System.Data.SqlTypes.SqlInt32)': not all code paths return a value
I'm newbie. Please, show me how to correct the problem. Thank you.
View 3 Replies
View Related
Jul 23, 2005
xp_startmail is documented as returning two code: 0 success, 1 failure.It would be nice to check the return code.When mail has already been started it returns a 1. This case appearsindistinguishable from a failure to start. (MSFT should have providedseparate return codes for failure-already started, and failure-cannotstart, but they did not.)Does anyone know how to distinguish these cases. Text is printedindicating that it has already been started but I don't know how toprogrammatically grab this case.It seems the conventional workaround is not to check the return code atall (or to start mail once at server startup and never start it again).Neither is ideal.Suggestions?Thanks,Mark Andersen
View 5 Replies
View Related
Jan 10, 2007
Can anyone help me with below error code i am in getting while runninga C program using sqls.Password: passwordSQL Return Code: -25001BATCH_LOG_PATH:Thank you,D
View 1 Replies
View Related
Dec 20, 2007
I was thinking of using a CTE to return history, have you ever done this?
I know this can be done in other ways but I wanted to wrap this CTE in a view so people could join to it.
Here is an example table:
KEY ASOFDATE
A 1/1/2007
A 3/1/2007
B 1/1/2007
B 2/5/2007
B 3/6/2007
I want the results to look like:
KEY START END
A 1/1/2007 3/1/2007
A 3/1/2007 NULL
B 1/1/2007 2/5/2007
B 2/5/2007 3/6/2007
B 3/6/2007 NULL
This would be even better:
ID KEY ASOFDATE
1 A 1/1/2007
2 A 3/1/2007
3 B 1/1/2007
4 B 2/5/2007
5 B 3/6/2007
I want the results to look like:
ID KEY START END
1 A 1/1/2007 3/1/2007
2 A 3/1/2007 NULL
3 B 1/1/2007 2/5/2007
4 B 2/5/2007 3/6/2007
5 B 3/6/2007 NULL
View 1 Replies
View Related
Feb 2, 2007
Hi. We're experiencing the exact same problem described in this post: http://tinyurl.com/ynqv7o
We've tried the 11 steps described there and it didn't fix the problem -- just as it didn't fix it for that person.
Help!! Thank you.
View 1 Replies
View Related
Feb 26, 2007
Hi,
I've created a stored procedure which inserts values into a table and upon successful execution the RC column gets returned along with the Identity value (I'm using SELECT SCOPE_IDENTITY()), but I don't want to RC column, I only want to get the Id number of the current row. I'm doing this using the Query Analyzer.
Is there a way to suppress the RC column? I have run the same query on different servers and the RC col doesn't show up. I only want the ID value to put into an ASP.NET page.
While testing different query methods from ASP.NET (Output parameter, Return_Value, etc) did I set a flag within SQL Server?
Sincerely,
Fred
View 1 Replies
View Related
Jan 4, 2007
hi all,
i have sql statement in ExecuteSQLTask, connecting to AdventureWorks
BEGIN TRY
insert into person.contacts(contactid) values (1);
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;
i purposely put a table that does not exist in the database to catch
the error. however, the error number that is 208 won't appear, instead
the whole error message will be displayed.
if i change the query in try block to
insert into person.contact(contactid) values (1);
it'll return the error number 544, which is correct.
what did i do wrong?
thanks!
View 5 Replies
View Related
Nov 6, 2007
I am building a stored procedure that changes based on the data that is available to the query. See below.
The query fails on line 24, I have the line highlighted like this.
Can anyone point out any problems with the sql?
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the error...
Msg 8114, Level 16, State 5, Procedure sp_SearchCandidatesAdvanced, Line 24
Error converting data type varchar to numeric.
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the exec point...
EXEC [dbo].[sp_SearchCandidatesAdvanced]
@LicenseType = 4,
@PositionType = 4,
@BeginAvailableDate = '10/10/2006',
@EndAvailableDate = '10/31/2007',
@EmployerLatitude = 29.346675,
@EmployerLongitude = -89.42251,
@Radius = 50
GO
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the STORED PROCEDURE...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_SearchCandidatesAdvanced]
@LicenseType int = 0,
@PositionType int = 0,
@BeginAvailableDate DATETIME = NULL,
@EndAvailableDate DATETIME = NULL,
@EmployerLatitude DECIMAL(10, 6),
@EmployerLongitude DECIMAL(10, 6),
@Radius INT
AS
SET NOCOUNT ON
DECLARE @v_SQL NVARCHAR(2000)
DECLARE @v_RadiusMath NVARCHAR(1000)
DECLARE @earthRadius DECIMAL(10, 6)
SET @earthRadius = 3963.191
-- SET @EmployerLatitude = 29.346675
-- SET @EmployerLongitude = -89.42251
-- SET @radius = 50
SET @v_RadiusMath = 'ACOS((SIN(PI() * ' + @EmployerLatitude + ' / 180 ) * SIN(PI() * p.CurrentLatitude / 180)) + (COS(PI() * ' + @EmployerLatitude + ' / 180) * COS(PI() * p.CurrentLatitude / 180) * COS(PI()* p.CurrentLongitude / 180 - PI() * ' + @EmployerLongitude + ' / 180))) * ' + @earthRadius
SELECT @v_SQL = 'SELECT p.*, p.CurrentLatitude, p.CurrentLongitude, ' +
'Round(' + @v_RadiusMath + ', 0) AS Distance ' +
'FROM ProfileTable_1 p INNER JOIN CandidateSchedule c on p.UserId = c.UserId ' +
'WHERE ' + @v_RadiusMath + ' <= ' + @Radius
IF @LicenseType <> 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND LicenseTypeId = ' + @LicenseType
END
IF @PositionType <> 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND Position = ' + @PositionType
END
IF LEN(@BeginAvailableDate) > 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND Date BETWEEN ' + @BeginAvailableDate + ' AND ' + @EndAvailableDate
END
--SELECT @v_SQL = @v_SQL + 'ORDER BY CandidateSubscriptionEmployerId DESC, CandidateFavoritesEmployerId DESC, Distance'
PRINT(@v_SQL)
EXEC(@v_SQL)
-----------------------------------------------------------------------------------------------------------------
View 4 Replies
View Related
Jun 6, 2008
Hi all, if have problem to display error message in vb.net that comes from a stored procedure. IF ...... then msgbox "ERROR at Update" returnvalue = SUCCES ELSE Msgbox "Successfull Updated" returnvalue = ERROREND IFHow can I return values from Stored Procedure to VB.NET and then give to User a Message that the Update was successful or not.Thanks to all
View 2 Replies
View Related
Apr 12, 2005
Does anyone know how to call a SQL store procedure that return a value to the page?
I've a simple data entry aspx page with several textboxes and a save button. When user fill out the form and click save/submit, it calls a store procedure to insert a row into a SQL table and automatically generate an ID that need to return the the page to display for the user.
Are there a similar article somewhere?
Thank you all!
View 6 Replies
View Related
Aug 9, 2006
I used a function to create dataset as below:
Public Function GetSQLDataSet(ByVal SQL As String) As DataSet
......
MyConnection = New SqlConnection(MyConnectionString)
MyCommand = New SqlCommand(SQL, MyConnection)
MyDataSet = New DataSet
MySQLDataAdapter = New SqlDataAdapter(MyCommand)
MySQLDataAdapter.Fill(MyDataSet)
......
End function
It works fine.
How to code a function to return a dataset in which there are two tables and relationship?
View 1 Replies
View Related
Apr 18, 2007
My stored procedure works and codes is working except I need to capture the return value from the stored procedure and use that value in my code behind page to indicate that a duplicate record entry was attempted. In my code behind file (VB) how would I capture the value "@myERROR" then display in the label I have that a duplicate entry was attempted.
Stored ProcedureCREATE PROCEDURE dbo.usp_InsertNew @IDNumber nvarchar(25), @ID nvarchar(50), @LName varchar(50), @FName varchar(50)
AS
DECLARE @myERROR int -- local @@ERROR , @myRowCount int --local @@rowcountBEGIN -- See if a contact with the same name and zip code exists IF EXISTS (Select * FROM Info WHERE ID = @ID) BEGIN RETURN 1END ELSEBEGIN TRAN INSERT INTO Info(IDNumber, ID, LName, FName) VALUES (@IDNumber, @ID, @LName, @FName) SELECT @myERROR = @@ERROR, @myRowCount = @@ROWCOUNT If @myERROR !=0 GOTO HANDLE_ERROR
COMMIT TRAN RETURN 0 HANDLE_ERROR: ROLLBACK TRAN RETURN @myERROR ENDGO
asp.net page<asp:SqlDataSource ID="ContactDetailDS" runat="server" ConnectionString="<%$ ConnectionStrings:EssPerLisCS %>" SelectCommand="SELECT * FROM TABLE_One" UpdateCommand="UPDATE TABLE_One WHERE ID = @ID" InsertCommand="usp_InsertNew" InsertCommandType="StoredProcedure"> <SelectParameters> <asp:ControlParameter ControlID="GridView1" Name="ID" PropertyName="SelectedValue" /> </SelectParameters> </asp:SqlDataSource>
View 2 Replies
View Related
Jun 2, 2006
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?
View 2 Replies
View Related
Jul 20, 2005
I am running the following OSQL command and capturing the return codefor the error .Whenver i have an error like server not exists or uableto login I get a return code of 1 for the %ERRORLEVEL%.Howeverwhenever I have an errorof a wrong dbcompatibility error the retuncode id 1 even though sql returns an iformation message from OSQL thatthe right copatibilty levels are 60,70 and 80.How can i get OSQL toreturn the right return code whenver a error of this type occurrs frombatch mode sql.The OSQL i am running from the batch isosql -S%SrvName% -U%Username% -P%Userpswd% -n -w 132 -d%DBname%-Q%sqlcmd% -o%Dirrpt%\%DBname%_%SPname%.txtECHO %errorlevel% >> %logbatch%IF %ERRORLEVEL% NEQ 0 Goto SQLErrorsqlcms is exec sp_dbcompatibiltylevel srvrname, dbname 80Thanks in anticipation.Ajay
View 3 Replies
View Related
Jan 18, 2008
Can anyone tell me how to capture the return code of a process launched by an Execute Process Task? I am able to capture the output by using the StandardOutputVariable but can't seem to capture the actual code.
View 4 Replies
View Related
Oct 30, 2005
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
View 7 Replies
View Related
Sep 17, 2012
I am TRYING to write code to combine two tables and then return the maximum value of one table, but SQL Server keeps telling me that the column is not valid.... I have added attached screenshots to show that it IS a valid column, so I cannot figure out what is the retarded issue!!
View 11 Replies
View Related
Apr 18, 2014
We made SSIS package in dev environment in windows 2008R2 and SQL server 2012. Same packages were placed on SAN disks in cluster environment and are invoked with security contexxt of admin user and wih dtexec utility ( we call this using an sp). rarely but procedure completed i see the log text file by SSIS package and find the return code 5 means package is unable to load. i canst found what are the exact reasons for return code 5.
View 2 Replies
View Related
