Passing Parameters To Extended Stored Procedures

Jul 20, 2005

I'm trying to pass parameters to an extended stored procedure, to no
avail. I would like to pass two integers to the dll and I have the
following three snippets:

1. The C++ portion of the dll:
....
declspec(dllexport) int myAddNumbers(int m, int n)
....

2. The creation of the extended stored procedure:
EXEC sp_addextendedproc myAddNumbers , 'foodll.dll';

3. The usage:
create function TestFunction()
returns integer
as
begin
declare @rc integer
exec @rc = myAddNumbers
return (@rc)
end

How do any of the above three things need to be modified in order to
make this work?

Thanks!!!

View 1 Replies


ADVERTISEMENT

Problem Passing Variables As Parameters To Extended Stored Procedure

Jun 20, 2007

Hello all,



I have written an XP for SQL Server 2000 SP2. It performs as expected if I call the XP with literal values for the parameters, however when I wrap the XP call into a regular stored procedure, only the first character of each input string is seen by the XP! Here are the relevant code snippets:



C++ Extended Stored Procedure:

(Basically all this code is doing is retrieving the parameters and printing them back out)



srv_paraminfo(srvproc, 1, &bType, &uMaxLen, &uLen, NULL, &bNull);

param1 = new BYTE[uLen + 1];

srv_paraminfo(srvproc, 1, &bType, &uMaxLen, &uLen, param1, &bNull);

param1[uLen] = '';



srv_paraminfo(srvproc, 2, &bType, &uMaxLen, &uLen, NULL, &bNull);

param2 = new BYTE[uLen + 1];

srv_paraminfo(srvproc, 2, &bType, &uMaxLen, &uLen, param2, &bNull);

param2[uLen] = '';



srv_paraminfo(srvproc, 3, &bType, &uMaxLen, &uLen, NULL, &bNull);

param3 = new BYTE[uLen + 1];

srv_paraminfo(srvproc, 3, &bType, &uMaxLen, &uLen, param3, &bNull);

param3[uLen] = '';



sprintf(msgText, "Params received by xp: %s, %s, %s", param1, param2, param3);

srv_sendmsg( srvproc, SRV_MSG_ERROR, 0,(DBTINYINT)0, (DBTINYINT)0,NULL,0,0,msgText,SRV_NULLTERM);

srv_senddone(srvproc, SRV_DONE_ERROR, (DBUSMALLINT)0, (DBINT)0);





Calling the XP with literal values:



EXEC xp_mytest 'one','two','three'



Output:

Params received by xp: one, two, three





Calling XP via a stored procedure:



create procedure sp_mytest

(

@myvar1 nvarchar(200),

@myvar2 nvarchar(50),

@myvar3 nvarchar(50)

)

as BEGIN

PRINT @myvar1

PRINT @myvar2

PRINT @myvar3

EXEC xp_mytest @myvar1, @myvar2, @myvar3

END



EXEC sp_mytest 'one','two','three'



Output:

one

two

three

Params received by xp: o,t,t





Any insight or assistance is greatly appreciated!!!

View 1 Replies View Related

Passing Parameters To Stored Procedures

Feb 17, 2005

Hello,

I seached around for an answer to this question but didn't have much luck. Hopefully someone can help.

I am passing two parameters from a web page to a stored procedure. The first paramater @Field is the name of the field in the database I want to search, the second @Value is the value to seach for. The @Value works fine but the SP does not seem to recongnize the field parameter. I'm not sure if what I am attemping is not supported or wheather I just need to format the @Field in a different manner. The code and stored procedure is below.

Thanks for your help, Gary

Here is the web code:


Dim conMSS As New SqlConnection(ConfigurationSettings.AppSettings("dsnMSS"))
Dim cmdItems As New SqlCommand("DS-SPRS.dbo.s_ItemLookUp", conMSS)

cmdItems.CommandType = CommandType.StoredProcedure
cmdItems.Parameters.Add(New SqlParameter("@Field", SqlDbType.VarChar, 50))
cmdItems.Parameters.Add(New SqlParameter("@Value", SqlDbType.VarChar, 50))

cmdItems.Parameters("@Value").Value = txtValue.Text & "%"
cmdItems.Parameters("@Field").Value = lstField.SelectedValue

conMSS.Open()
dgdItems.DataSource = cmdItems.ExecuteReader
dgdItems.DataBind()
conMSS.Close()


Here is the stored procedure:



CREATE PROCEDURE s_ItemLookUp

@Field AS VARCHAR(50),
@Value AS VARCHAR(50)

AS


SELECT DIV_NO, DIV_NM, LN_NO, LN_DS, ITM_NO, PRD_DS, ITM_MFG_NO, VND_HFC_NM
FROM PRODUCT
WHERE @Field LIKE @Value
ORDER BY DIV_NO, LN_NO, ITM_NO
GO

View 4 Replies View Related

Passing Empty Parameters To Stored Procedures

Aug 9, 2000

Hi

I have a stored procedure some thing like this..
When I pass empty strings to both the parameters ..it is
returning all the rows from the table.

IF I pass both empty strings to @LLASTNAME_I Char(21),
@DEPARTMENTCODE_I char(7),it should not select any rows from the table.. can any one suggest me as to how to accomplish this..


CREATE procedure Ceb_Phone_Book
@LLASTNAME_I Char(21),
@DEPARTMENTCODE_I char(7)
AS
Select
EM010132.DEPARTMENTCODE_I,
DEPARTMENTNAME_I,
LLASTNAME_I,
FFIRSTNAME_I,
EM010132.WORKPHONE_I,
EM010132.MSTRING_I_5
FROM
EM010132 INNER JOIN HR2DEP01
ON HR2DEP01.DEPARTMENTCODE_I = EM010132.DEPARTMENTCODE_I
WHERE
INACTIVE = 0 AND
LLASTNAME_I LIKE LTRIM(RTRIM(@LLASTNAME_I)) + '%' AND
EM010132.DEPARTMENTCODE_I LIKE LTRIM(RTRIM(@DEPARTMENTCODE_I)) + '%'
ORDER BY
LLASTNAME_I,FFIRSTNAME_I


Thanks
VENU

View 3 Replies View Related

Only Functions And Extended Stored Procedures Can Be Executed From Within A Function. Sp_executesql Is A Extended Stored Prod

May 15, 2008

i have created the folowing function but keep geting an error.

Only functions and extended stored procedures can be executed from within a function.

Why am i getting this error!

Create Function myDateAdd
(@buildd nvarchar(4), @avdate as nvarchar(25))
Returns nvarchar(25)
as
Begin
declare @ret nvarchar(25)
declare @sqlval as nvarchar(3000)

set @sqlval = 'select ''@ret'' = max(realday) from (
select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'

execute sp_executesql @sqlval
return @ret
end

View 3 Replies View Related

Passing Parameters To Action Stored Procedures Using ADO, In Access Project

Jul 20, 2005

There is a form in an Access Project (.adp, Access front end with SQLServer) for entering data into a table for temporary storing. Then, byclicking a botton, several action stored procedures (update, append) shouldbe activated in order to transfer data to other tables.I tried to avoid any coding in VB, as I am not a professional, but I havefound a statement in an article, that, unlike select queries, form's InputProperty can't be used for action queries. Therefore, parameters can bepassed to action stored procedure only by using ADO through VB.As I'm not very familiar with VB, I had to search in literature.So, this is a solution based on creating Parameter object in ADO and thenappending values to Parameter collection.Please, consider the following procedure I created for passing parametersfrom form's control objects (Text boxes) to a stored procedureDTKB_MB_UPDATE:Private Sub Command73_Click()Dim cmd As ADODB.CommandSet cmd = New ADODB.Commandcmd.ActiveConnection = CurrentProject.Connectioncmd.CommandText = "DTKB_MB_UPDATE"cmd.CommandType = adCmdStoredProcDim par As ADODB.ParameterSet par = cmd.CreateParameter("@DATE", adDBTimeStamp, adParamInput)cmd.Parameters.Append parSet par = cmd.CreateParameter("@BATCH_NUMBER", adVarWChar, adParamInput, 50)cmd.Parameters.Append parSet par = cmd.CreateParameter("@STATUS", adVarWChar, adParamInput, 50)cmd.Parameters.Append parSet par = cmd.CreateParameter("@DEPARTMENT", adVarWChar, adParamInput, 50)cmd.Parameters.Append parSet par = cmd.CreateParameter("@PRODUCTION", adVarWChar, adParamInput, 50)cmd.Parameters.Append parSet par = cmd.CreateParameter("@SAMPLING_TYPE", adVarWChar, adParamInput,50)cmd.Parameters.Append parcmd.Parameters("@DATE") = Me.DATEcmd.Parameters("@BATCH_NUMBER") = Me.BATCH_NUMBERcmd.Parameters("@STATUS") = Me.STATUScmd.Parameters("@DEPARTMENT") = Me.DEPARTMENTcmd.Parameters("@PRODUCTION") = Me.PRODUCTIONcmd.Parameters("@SAMPLING_TYPE") = Me.SAMPLING_TYPEcmd.ExecuteSet cmd = NothingEnd SubUnfortunately, when clicking on the botton, the following error apears:"Run-time error'-2147217913 (80040e07)':Syntax error converting datetimefrom character string."Obviously, there is some problem regarding parameter @DATE. In SQL Server itis datetime, on the form's onbound text box it is short date (dd.mm.yyyy)data type. I have found in literature that in ADO it should beadDBTimeStamp.So, what is the problem ?Greetings,Zlatko

View 2 Replies View Related

Extended Stored Procedures

Oct 12, 1999

I'm trying to execute an exetended stored procedure from a trigger. The scenario is this: when an input occurs on a table, my trigger is fired, the extended stopred procedure is to be run, and VB app is to be notified that there is new input to process.

Problem is, I can't seem to get the XP to run. I have written a minimal DLL
with VC++ 6, has nothing but the required __GetXpVersion function and the
trigger function, as:

__declspec(dllexport) SRVRETCODE xp_InputAlarm(SRV_PROC *pSrvProc)
{ return XP_NOERROR; }

__declspec(dllexport) ULONG __GetXpVersion()
{ return ODS_VERSION; }

Pretty simple. I have defined the XP in the master database, set the properties point to the DLL. Believe everything is set up properly. However,
when an input occurs, a run-time error is generated on the XP. Attempting to execute it in Query Analyzer gives this error:

"Cannot find the function xp_InputAlarm in the library D:CustomMedInstaCert SourceInputAlarmInputAlaInputAlarm.dll. Reason: 127(The specified procedure could not be found.)."

Which I think means it can't find the proper entry point (xp_InputAlarm) in the DLL. Any help would be greatly appreciated.

mjo

View 2 Replies View Related

Extended Stored Procedures

Jan 6, 2005

Is there any way I can create an SQL script on any extended stored procedures? I need to see what they do. I looked in books online, but it didn't help. Thanks.

View 3 Replies View Related

Extended Stored Procedures - C/C++ Only?

Jun 13, 2001

I am working with Visual Basic and SQL Server 2000. I am looking to convert some of the VB into extended stored procedures, yet everything I've been reading has said that xp's can only be written in C or C++. Is this true??

Thanks.

Caroline Kaplonski
ckaplonski@buckconsultants.com

View 1 Replies View Related

Extended Stored Procedures

Jan 6, 2005

Howdy,

I'm trying to call XP_CMDSHELL from a stored procedure that's not in the Master db. How do I call XP_CMDSHELL? I've tried "Use Master" but I get an error telling me that I can't use "USE" inside of a stored procedure.

Thanks!

View 2 Replies View Related

Triggers, Extended Stored Procedures, And VB5/6

Sep 30, 1998

I would like to know if the the DLL`s one can build with VB5/6 can be used to construct Extended Stored Procedures in MS SQLServer 6.5? If so, how does one do this. All the reference material I`ve come across is for C

Thanks in Advance - Ralph

View 1 Replies View Related

Triggers, Extended Stored Procedures, And VB5/6

Sep 30, 1998

I would like to know if the the DLL`s one can build with VB5/6 can be used to construct Extended Stored Procedures in MS SQLServer 6.5? If so, how does one do this. All the reference material I`ve come across is for C

Thanks in Advance - Ralph

View 1 Replies View Related

Access To Extended Stored Procedures

Jul 18, 2007

Hi,

How can I remove access to extended (xp_) stored procedures?
Is there any revoke on <stored_procedure_name> ... command? How can I generate a script of all users who have execute privileges for these procedures? Also, is there any way of restricting (instead of removing) access to those procedures?

Any help will be greatly appreciated!!!
Thanks,
Alla

View 2 Replies View Related

Only Functions And Extended Stored Procedures Can

Jun 18, 2008

Hi all,
In one of my UDF I use the following functions:

.....
and len(@int_date) = 4
and isnumeric(substring(@int_date,5,6)) = 1

when I use the function I get

Only functions and extended stored procedures can be executed from within a function.

Yes, when I comment the two lines the function works fine.
Ehm.... why can't I use these functions in my function ?
Thanks: Peter

View 3 Replies View Related

Extended Stored Procedures DB-Lib Alternative

Jul 20, 2005

Since DBlib is no longer the suggested method for connecting back tosql server from an Extended Stored Procedure, has anyone built anyextended stored procedures that use other connection methods likeOLEDB? Has anyone seen links to any sample extended stored proceduresthat use something other than db-lib? In particular I am interestedin something that connects back to the database as the user whoinvoked the extended stored procedure. I haven't had much luckfinding any.Also, is there an alternative for the bcp api that is a little morecurrent and has support for newer datatypes like bigint? We currentlyuse the bcp api from an extended stored procdure written in C++, butnow need to add bigint support which the bcp api doesn't have.Thanks for any advice.

View 3 Replies View Related

Extended Stored Procedures In SQL 2005?

Sep 9, 2007


The Extended Stored Procedures (Written in C++ unmanaged code) are supported in SQL Server 2005????

Thanks

View 5 Replies View Related

Credentials And Extended Stored Procedures

Jun 11, 2007

I have a database script that uses the extended stored procedures sp_OACreate and sp_OCMethod to execute an .exe file. The .exe file is located on the same machine as the SQL Server. At this time it does nothing but log the name of the user calling it.



When I execute the script from Management Studio (logged in as myself) the user being logged as the caller of the .exe is still NT AUTHORITYSYSTEM. I don't know why NT AUTHORITYSYSTEM is the caller, cause the SQL Server service runs under another domain account.



I have tried playing around with EXECUTE AS USER but no matter what, the caller of the .exe is always logged as NT AUTHORITYSYSTEM.



Are there any way I can pass my credentials to the executable that I am calling from the T-SQL script?

View 2 Replies View Related

Creating Extended Stored Procedures In C Sharp ?

Apr 26, 2004

Is it possible to create an extended stored procedure in C Sharp.
This is for Sql Server 2000.

Books online mentions that you have to use c / c++ to create an extended stored procedure.
However have Microsoft added any support so that the same thing can be done through a simpler language like C Sharp.

Thanks,
Alok.

View 1 Replies View Related

Are Clr Sp's And Extended Stored Procedures The Same Thing In SS2005?

Feb 22, 2008

...we understand that perhaps some xp's might come with the product, but for sp's that we author ourselves, are they two different things in 2005, and if so, why would one be chosen over the other?

View 1 Replies View Related

Disabling Extended Stored Procedures For Security

Jun 29, 2007

Our security team wants us to disable access to (or drop) all of the built-in extended stored procedures in MSDE 2000 as they feel it is a vulnerability. Where can I find out which extended procs are safe to disable or how we can disable them during install time? Or, is the security team being too cautious and we should just tell them to leave these intact?

View 5 Replies View Related

How To Get The Results When Executing Extended Stored Procedures.

May 28, 2007

Hi. Does anyone know how to display the results if i execute "xp_fixeddrives, xp_availablemedia and xp_subdirs" commands with VC++ 6.0? I can't obtained the results using Recordset class. Can someone help me? Thank you.



|_N_T_|

View 3 Replies View Related

Create Extended Stored Procedures In Visual Basic

Mar 9, 2001

You know this sample ODS dll project:
$80oolsdevtoolssamplesodsxp_hello

I need to find a template like this in Visual Basic.

On this site I read through the xp_Encrypt project which was developed in VB. I did not see source code or a downloadable project file.

My searching on the internet hasn't yielded any practicle results, all examples are either how to use an extended stored procedure or are a MSVC++ project.

Anyone find VB related resources, anywhere? Books, TV, magazines????

View 1 Replies View Related

Extended Stored Procedures -&> Loading Linked Files

Feb 11, 2004

Hello everybody

I actually wrote a stored procedure (in xp_wrapper.dll) that is using a dll (original.dll) which uses a license file (no file extension).... clear? :)

Anyway.

All the required files are placed in the BINN dir of the server.

The problem is now, that original.dll can't find it's license file. It seems, that this file was not load by SQL Server.

How can I load this file into SQL Server's heap?

Yours
Mike

View 1 Replies View Related

Performance Of Extended Stored Procedures In SQL Server 2000

Jul 23, 2005

What is the overhead of using extended stored procedures?I created a table with 500,000 rows.1) I ran a select on two columns and it runs in about 5 seconds.2) I ran a select on one column and called an UDF (it returns aconstant string) and it takes 10 seconds.3) I ran a select on one column and called a UDF that calls an extendedstored procedure that returns a string and it takes 65 seconds.I also tried running test 3 with 4 concurrent clients and each clienttakes about 120 seconds.

View 1 Replies View Related

Only Functions And Extended Stored Procedures Can Be Executed From Within A Function.

Jun 7, 2006

Hi, l've created an function [GSM].[KPIAging], and test it in studio by substitule declare value, i.e.
DECLARE @sCellName VARCHAR(8)
DECLARE @dDate DATETIME
SET @sCellName = "CELL1M_1"
SET @dDate = CAST('06/Jun/2006' AS DATETIME)

EXEC GSM.KPIAging @sCellName, 'CSSR', @dDate

It work fine and return the desired result, but when l used this function in SQL,
SELECT DATEKEY, CELLREGIONKEY, CELL_NAME, CELL_ID, CSSR, GSM.KPIAging(Cell_Name, 'CSSR', @dDate)
FROM GSM.GSMCellDaily_vw
WHERE CSSR BETWEEN 0 AND 85
AND FULLDATE = @dDate
AND CM_SERV > 30
AND (TCH_TRAFFIC > 2 AND TCH_SEIZURES_ATTS > 30)

I got the following error, i.e.
Msg 557, Level 16, State 2, Line 19Only functions and extended stored procedures can be executed from within a function.
Does anyone have any idea on this, and what's the workaround for this?

Thanks you!

View 15 Replies View Related

Only Functions And Extended Stored Procedures Can Be Executed From Within A Function.

May 7, 2008

Hi mister, I have this script sql but I get this error:



Mens. 557, Nivel 16, Estado 2, LĂ­nea 1

Only functions and extended stored procedures can be executed from within a function.

DROP FUNCTION ObtenerTablaPorNombre2

GO

CREATE FUNCTION ObtenerTablaPorNombre2 (@ParamNombreTabla VARCHAR(100))

RETURNS @T Table ( Descripcion VARCHAR(20) NOT NULL, CIF VARCHAR(8) NULL )

AS

BEGIN

DECLARE @cmd nvarchar(max)

DECLARE @params nvarchar(max)

DECLARE @NombreTabla VARCHAR(MAX)

DECLARE @Descripcion VARCHAR(MAX)

DECLARE @CIF VARCHAR(MAX)

SELECT @NombreTabla = [CD_NOMBRE_TABLA], @Descripcion = [DS_CAMPO_DESCRIPCION] , @CIF = [DS_CAMPO_CIF]

FROM [TABLA_MAESTRA] WHERE [CD_NOMBRE_TABLA] = @ParamNombreTabla

SET @cmd = 'SELECT ' + @Descripcion + ',' + @CIF + ' FROM ' + @NombreTabla

--EXEC (@cmd)

SET @cmd = 'SELECT @pDescripcion, @pCIF FROM @pNombreTabla'

SET @params = N'@pDescripcion varchar(100), @pCIF varchar(100), @pNombreTabla varchar(100) '

EXEC sp_executesql @cmd, @params, @pDescripcion = @Descripcion, @pCIF = @CIF, @pNombreTabla = @NombreTabla

RETURN

END

GO

SELECT * FROM [dbo].ObtenerTablaPorNombre2 ('tabla2')
-- Only functions and extended stored procedures can be executed from within a function

View 2 Replies View Related

Using EXECUTE Statements Calling An Extended Stored Procedures From Function..

Apr 29, 2004

Hi, all
I'm using Sql server 2000
I want to make select statement dynamically and return table using function.
in sp, I've done this but, in function I don't know how to do so.
(I have to create as function since our existing API..)

Following is my tials...
1.
alter Function fnTest
( @fromTime datetime, @toTime datetime)
RETURNS Table
AS

RETURN Exec spTest @from, @to
GO

Yes, it give syntax error..

2. So, I found the following


From Sql Server Books Online, Remark section of CREATE FUNCTION page of Transact-SQL Reference , it says following..

"The following statements are allowed in the body of a multi-statement function. Statements not in this list are not allowed in the body of a function: "
.....
* EXECUTE statements calling an extended stored procedures.

So, I tried.

alter Function fnTest
( @fromTime datetime, @toTime datetime)
RETURNS Table
AS

RETURN Exec master..xp_msver
GO

It doesn't work... syntax err...

Here I have quick question.. How to execute statements calling an extended stored procedures. any examples?


Now, I'm stuck.. how can I create dynamic select statement using function?

I want to know if it's possible or not..

View 13 Replies View Related

Transact SQL :: Only Functions And Some Extended Stored Procedures Can Be Executed From Within A Function

Sep 11, 2013

I have this function called fn_GetTimedKey() when I run it in my SQL2005 environment it works fine.

When I run it in my SQL2008R2 environment I get the error:

Only functions and some extended stored procedures can be executed from within a function.

The SQL2008R2 server is new. What can I look for?

Here's the code for the function:

BEGIN
DECLARE @v_Key CHAR(12)
EXEC master..xp_usg_gettimedkey_VAL @v_Key OUTPUT
RETURN @v_Key
END

View 4 Replies View Related

Passing Arrays To Stored Procedures

Feb 23, 2004

Dear all,

i want to know how i can pass multiple values in the form of arrays to a stored procedures.

the technique by which i pass multiple values to a stored procedure beginning along with declarations are as follows:


Dim configurationAppSettings As System.Configuration.AppSettingsReader = New System.Configuration.AppSettingsReader()
Me.cmdInsSlabHmst = New System.Data.OleDb.OleDbCommand()
Me.OleDbConnection1 = New System.Data.OleDb.OleDbConnection()
Me.cmdInsSlabDMst = New System.Data.OleDb.OleDbCommand()
'
'cmdInsSlabHmst
'
Me.cmdInsSlabHmst.CommandText = "PKGSLABHMST.INSSLABHMST"
Me.cmdInsSlabHmst.CommandType = System.Data.CommandType.StoredProcedure
Me.cmdInsSlabHmst.Connection = Me.OleDbConnection1
Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iSLABDESC", System.Data.OleDb.OleDbType.VarChar, 50))
Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iSLABUNIT", System.Data.OleDb.OleDbType.VarChar, 1))
Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iREMARKS", System.Data.OleDb.OleDbType.VarChar))
Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iSLABFROM", System.Data.OleDb.OleDbType.VarChar))
Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iSLABRATE", System.Data.OleDb.OleDbType.VarChar))
Me.cmdInsSlabHmst.Parameters.Add(New System.Data.OleDb.OleDbParameter("iNOOFRECORDS", System.Data.OleDb.OleDbType.Integer))
'
'OleDbConnection1
'
Me.OleDbConnection1.ConnectionString = CType(configurationAppSettings.GetValue("ConnectionString", GetType(System.String)), String)

'Passing multiple values to the procedure with the help of ~ sign

Dim strCode As String
Dim i As Integer
'Dim dblSlabRate As Decimal
'Dim dblSlabFrom As Decimal
Dim strSlabRate As String
Dim strSlabFrom As String
Dim strSlabRateP As String
Dim strSlabFromP As String
Dim intCntr As Integer
'Me.cmdInsSlabHmst.Parameters("iSLABDESC").Value = txtSlabDesc.Text
'Me.cmdInsSlabHmst.Parameters("iSLABUNIT").Value = ddlSalbUnit.SelectedItem.Value
'Me.cmdInsSlabHmst.Parameters("iREMARKS").Value = txtRemarks.Text
'OleDbConnection1.Open()
'strCode = cmdInsSlabHmst.ExecuteScalar
'OleDbConnection1.Close()
For i = 0 To dgSlabDtl.Items.Count - 1
If i = dgSlabDtl.Items.Count - 1 Then
'dblSlabRate = CType(dgSlabDtl.Items(i).FindControl("txtSlabRate"), TextBox).Text
'dblSlabFrom = CType(dgSlabDtl.Items(i).FindControl("txtSlabFrom"), TextBox).Text
strSlabRate = CType(dgSlabDtl.Items(i).FindControl("txtSlabRate"), TextBox).Text
strSlabFrom = CType(dgSlabDtl.Items(i).FindControl("txtSlabFrom"), TextBox).Text
Else
'dblSlabRate = CType(dgSlabDtl.Items(i).FindControl("lblSlabRate"), Label).Text
'dblSlabFrom = CType(dgSlabDtl.Items(i).FindControl("lblSlabFrom"), Label).Text
strSlabRate = CType(dgSlabDtl.Items(i).FindControl("lblSlabRate"), Label).Text
strSlabFrom = CType(dgSlabDtl.Items(i).FindControl("lblSlabFrom"), Label).Text
End If
strSlabRateP += strSlabRate & "~"
strSlabFromP += strSlabFrom & "~"
intCntr += 1
'If dblSlabRate <> "" And dblSlabFrom <> "" Then
'InsDtl(strCode, dblSlabFrom, dblSlabRate)
'End If
Next
If strSlabRateP <> "" And strSlabFrom <> "" Then
With cmdInsSlabHmst
.Parameters("iSLABDESC").Value = UCase(txtSlabDesc.Text)
.Parameters("iSLABUNIT").Value = ddlSalbUnit.SelectedItem.Value
.Parameters("iREMARKS").Value = txtRemarks.Text
.Parameters("iSLABFROM").Value = strSlabFromP
.Parameters("iSLABRATE").Value = strSlabRateP
.Parameters("iNOOFRECORDS").Value = intCntr
End With
OleDbConnection1.Open()
cmdInsSlabHmst.ExecuteNonQuery()
OleDbConnection1.Close()
End If


to the insert procedure i am passing multiple values with the help of ~ sign and in the procedure the individual values are separated by identifying the position of ~ sign and the no. of records which have been passed. For which a complicated stored procedure has been written.

i want to pass multiple values in an array, so that my stored procedure becomes simple and runs faster. So, if someone tells me how to pass arrays to a stored procedure (with code example), it will be of real help.

regards
subhajit

View 1 Replies View Related

Passing An Array To A Stored Procedures

Feb 17, 2005

How to do this ?

==============================
CREATE procedure dbo.AddTb2FromTb1
@Tb1No nvarchar(1000)
as
insert into Tb2 (*)
select * from Tb1
where Tb1 IN (@Tb1No) /* How to Passing an Array to a Stored Procedures ??? */
==============================

dbo.AddTb2FromTb1 'No001' is Work !
dbo.AddTb2FromTb1 'No001,No002,Bo003' is not Work !

View 3 Replies View Related

Passing Arrays To Stored Procedures?

Feb 20, 2003

Someone recently tried to tell me that it is possible to pass an array to a stored procedure.

I have tried creating procedures with the 'table' datatype for the parameters but the attempts fail with a syntax error.

We currently workaround this limitation by passing the equivalent of an array to temporary tables and selecting from those tables as needed within our stored procedure but if we can circumvent this by passing an array, we'd definetly like to try that instead.

Thanks in advance for any advice you may share.

View 4 Replies View Related

Passing Multivalue In Stored Procedures

Jun 7, 2007

Hi guys,

I'm new in SSRS, Im having problems passing multivalue in stored procedures. Can someone help. please.



Thanks in advance.

View 10 Replies View Related

Passing Parameters Into Stored Procedure

Apr 11, 2007

I have two variables in my code behind .cs,
string Zip;int MenuCode;
I am trying to pass them to the stored procedure below and am getting compile errors.  How do I properly construct the parameter object?
error: Cannot create an object of type 'System.TypeCode' from its string representation '"String";' for the 'Type' property. (points to Parameter Name ="Zip" Type="string"
<asp:SqlDataSource ID="LocalPremiumLinks" runat="server"            ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>" SelectCommand="CPLink"            SelectCommandType="StoredProcedure">            <SelectParameters>                        <asp:Parameter Name="Zip" Type="string" />             <asp:Parameter Name="MenuCode" Type="double" />                               </SelectParameters>
 

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved