Stored Procedure Parameter Sequence Through JDBC From SAP
Apr 24, 2007
Hi,
I do not know if this is the right place, but do guide me if possible.
I am using JDBC driver to send data from SAP XI to MS SQL 2000.
The data is passed as an XML file, as below
<?xml version="1.0" encoding="UTF-8" ?>
- <ns0:MT_UPDATEPERSONACUSTOMERS xmlns:ns0="urndalesapcustomersupdatetopersona">
- <CallProc>
- <UPDATE_EndCustomerFromSAP action="EXECUTE">
<table>mk_INSERT_ConsultantFromSAP_proc</table>
<SAPID type="CHAR">0000206320</SAPID>
<DateCreated hasQuot="No" type="DATE">2007-04-24</DateCreated>
<FirstName type="CHAR">CAROL J</FirstName>
<LastName type="CHAR">VERITY</LastName>
<AccountGroup type="CHAR">Z001</AccountGroup>
<CustomerStatus type="INTEGER">1</CustomerStatus>
<Active type="INTEGER">1</Active>
<Blocked type="INTEGER">1</Blocked>
<AssocConsultant type="CHAR">NULL</AssocConsultant>
<Street1 type="CHAR">112 EVERGREEN SQUARE SW</Street1>
<Street2 type="CHAR">CHANGED ADDRESS LINE 1</Street2>
<City type="CHAR">Saint John</City>
<District type="CHAR">NULL</District>
<Region type="CHAR">NB</Region>
<PostalCode type="CHAR">E2L 4L1</PostalCode>
<DayPhone type="CHAR">306-781-4923</DayPhone>
<EveningPhone type="CHAR">306-761-2121</EveningPhone>
<Email type="CHAR">test@lllll.com</Email>
<CommunicationMethod type="CHAR">Day Phone</CommunicationMethod>
</UPDATE_EndCustomerFromSAP>
</CallProc>
</ns0:MT_UPDATEPERSONACUSTOMERS>
I know there is a way to pass paramters by name and hence sequence of parameters should not matter. So I was shocked and I still do not believe that when you call a stored procedure through JDBC adapter, the SEQUENCE of parameters have to be the same as defined in the stored procedure. I do not know if this is true for Oracle as well as MS SQL Server 2000, but I faced this problem on MS SQL 2k.
example - if stored proc 1 has params a, b, c and stored proc 2 has param d, c, a . My destination data type looks like a, b, c, d. Now if i decide to call proc 2, the JDBC adapter in SAP generates XML like a,c,d. I did a trace on SQL server and found that the JDBC driver uses sp_execute command, which my DBA told uses positional mapping and not named mapping.
I believe that SAP or the MS JDBC driver should support this.
So does anyone know, if there is an additional parameter which will make the JDBC driver to work with name and not position of paramters.
View 1 Replies
ADVERTISEMENT
Feb 13, 2007
My server is MS Sql Server 2005. I'm using com.microsoft.sqlserver.jdbc.SQLServerDriver as the driver class. I've established a connection to the database.
I'm trying to invoke a stored procedure using JDBC and a callable statement. The stored procedure has a parameter @CurOut CURSOR VARYING OUTPUT. How do I setup the callable statement so the output parameter is accepted by the driver?
I'm not really trying to pass a cursor up to the database Server but I'm wanting a cursor back from the stored procedure that is other than the result set or other value the stored procedure returns.
First problem: What java.sql.Types (or SQL Server specific) value do I specify for the out parameter I'm registering on the CallableStatement?
Second problem: What do I set the value of the parameter to?
The code looks like:
CallableStatement cstmt = myConnection.prepareCall(sQuery);
cstmt.registerOutParameter(1, Types.OTHER); // What is the right type?
cstmt.setNull(1, Types.OTHER); // What is the right type?
if (cstmt.execute()) {
ResultSet rs = cstmt.getResultSet();
}
Execution results in a NullPointerException from the driver.
What am I doing wrong?
Thanks for your assistance.
Jon Weaver
View 3 Replies
View Related
Oct 26, 2006
I have a procedure that uses varchar(max) as output parameter, when I tried to retrieve
the result of calling the procedure, the result is truncated to 4000 character. Is this a driver bug?
Any workaround?
Here is the pseudo code:
create Procedure foo(@output varchar(max))
{
set @foo = 'string that has more than 4000 characters...';
return;
}
Java code:
CallableStatement cs = connection.prepareCall("{call foo ?}");
cs.registerOutputParameter(1, Types.longvarchar); // also tried Types.CLOB.
cs.execute();
String result = cs.getString(1); // The result is truncated to 4000 char.
-- Also tried
CLOB clob = cs.getClob(1);
long len = clob.length(); // The result is 4000.
Thanks,
Eric Wang
View 3 Replies
View Related
Mar 9, 2008
I have a Column called SaleID in some tables in a Database.The SaleID column has the int datatype. I need a stored procedure that returns the next value in sequence for the SaleID column.
For Example,
If the last value inserted into the SaleID column was 1022 the stored procedure should return 1023
If the last value inserted into the SaleID column was 1023 the stored procedure should return 1024.
If the last value inserted into the SaleID column was 1024 the stored procedure should return 1025.
Also an exclusive lock should be maintained while the the stored procedure is running.
I am using SQL Server 2005.
View 4 Replies
View Related
Dec 6, 2007
When running a stored procedure, how can i retrieve the warnings that are issued within the stored procedure?
the code used is below,
the jdbc is connecting fine, it is running the stored procedure, but when an error is raised in the stored procedure, it is not coming back into s.getwarnings()
warning raised in stored proc with
Code Block
RAISERROR ('Error', 16, 1)with nowait;
there are no results for the stored procedure, I am just wanting to get the warnings
Code Block
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection("jdbc:sqlserver://localh...........);
CallableStatement s = con.prepareCall("{call procedure_name}");
s.execute();
//running in seprate thread
SQLWarning warn = s.getWarnings();
while (m.running) {
if(warn == null) {
warn = s.getWarnings();
}
safeOut(warn);
if(warn != null)
warn = warn.getNextWarning();
m.wait(100);
}the code is not complete, but should show what is happening
it is continually outputting null for the warning, though the strored proc is definately raising errors, which is proven by running it in a query window in sql server.
it is retreiving warning if the statement is a raiseerror instead of a call to the proc
Code Block
CallableStatement s = con.prepareCall("RAISERROR ('Error', 1, 1)with nowait;");
this thread is quite related, but doesnt offer a working solution
Getting messages sent while JDBC Driver calls stored procedure
any help much appreciated,
thankyou
Simon
View 1 Replies
View Related
Oct 9, 2006
Hi.
How can I get the messages sent by the server while I'm executing a stored procedure via the JDBC driver?
I need to get my own debug messages (done through the print() function)
and also standard server messages (such as "x row(s) affected" or
results from SET STATISTICS TIME ON). Is this possible?
Many thanks.
Carlos
View 4 Replies
View Related
Jul 23, 2005
Using JDBC, is there a way to call a stored procedure with multiplereturn values? Thanks.
View 4 Replies
View Related
Oct 4, 2015
When using JDBC is it possible to ignore input parameters in a stored procedure if they have default values?
I can create a string and execute a PreparedStatement but I'd like to use a Callable Statement.
View 7 Replies
View Related
Jun 25, 2007
I am unable to send null values through the Microsoft JDBC 1.1 driver to a stored procedure. Please look at the thread already started on the SQL Server Transact SQL Forum at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1772215&SiteID=1
View 1 Replies
View Related
Jan 29, 2015
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
[Code] ....
View 9 Replies
View Related
Feb 19, 2007
Hello,
since a couple of days I'm fighting with RS 2005 and the Stored Procedure.
I have to display the result of a parameterized query and I created a SP that based in the parameter does something:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE [schema].[spCreateReportTest]
@Name nvarchar(20)= ''
AS
BEGIN
declare @slqSelectQuery nvarchar(MAX);
SET NOCOUNT ON
set @slqSelectQuery = N'SELECT field1,field2,field3 from table'
if (@Name <> '')
begin
set @slqSelectQuery = @slqSelectQuery + ' where field2=''' + @Name + ''''
end
EXEC sp_executesql @slqSelectQuery
end
Inside my business Intelligence Project I created:
-the shared data source with the connection String
- a data set :
CommandType = Stored Procedure
Query String = schema.spCreateReportTest
When I run the Query by mean of the "!" icon, the parameter is Prompted and based on the value I provide the proper result set is displayed.
Now I move to "Layout" and my undertanding is that I have to create a report Paramater which values is passed to the SP's parameter...
So inside"Layout" tab, I added the parameter: Name
allow blank value is checked and is non-queried
the problem is that when I move to Preview -> I set the value into the parameter field automatically created but when I click on "View Report" nothing has been generated!!
What is wrong? What I forgot??
Thankx for any help!
Marina B.
View 3 Replies
View Related
Jul 2, 2007
I had thought that this was possible but I can't seem to figure out the syntax. Essentially I have a report where one of the parameters is populated by a stored procedure.
Right now this is easily accomplished by using "exec <storedprocname>" as the query string for the report parameter. However I am not clear if it is possible to now incorporate User!UserID as parameter to the stored procedure. Is it? Thanks
View 1 Replies
View Related
Mar 26, 2007
Has anyone encountered this before?
Procedure or Function 'stored procedure name' expects parameter '@parameter', which was not supplied.
It seems that my code is not passing the parameter to the stored procedure.
When I click this hyperlink:
<asp:HyperLink
ID="HyperLink1"
Runat="server"
NavigateUrl='<%# "../Division.aspx?CountryID=" + Eval("CountryID")%>'
Text='<%# Eval("Name") %>'
ToolTip='<%# Eval("Description") %>'
CssClass='<%# Eval("CountryID").ToString() == Request.QueryString["CountryID"] ? "CountrySelected" : "CountryUnselected" %>'>
</asp:HyperLink>
it is suppose to get the country name and description, based on the country id.
I am passing the country id like this.
protected void Page_Load(object sender, EventArgs e)
{
PopulateControls();
}
private void PopulateControls()
{
string countryId = Request.QueryString["CountryID"];
if (countryId != null)
{
CountryDetails cd = DivisionAccess.GetCountryDetails(countryId);
divisionNameLabel.Text = cd.Name;
divisionDescriptionLabel.Text = cd.Description;
}
}
To my app code like this:
public struct CountryDetails
{
public string Name;
public string Description;
}
public static class DivisionAccess
{
static DivisionAccess()
public static DataTable GetCountry()
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetCountry";
return GenericDataAccess.ExecuteSelectCommand(comm);
}
public static CountryDetails GetCountryDetails(string cId)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetCountryDetails";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@CountryID";
param.Value = 2;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
CountryDetails details = new CountryDetails();
if (table.Rows.Count > 0)
{
details.Name = table.Rows[0]["Name"].ToString();
details.Description = table.Rows[0]["Description"].ToString();
}
return details;
}
As you can see I have two stored procedures I am calling, one does not have a parameter and the other does. The getcountry stored procedure returns the list of countries in a menu that I can click to see the details of that country. That is where my problem is when I click the country name I get
Procedure or Function 'GetCountryDetails' expects parameter '@CountryID', which was not supplied
Someone please help!
Thanks Nickdel68
View 5 Replies
View Related
Feb 12, 2008
I have this update procedure that updates and encrypts a credit card column. I added the pass phrase to a table and now I'm trying to use that pass phrase in my update procedure.
When I run the update from the app the error is that the procedure is not getting the @Phrase parameter.
Can anyone see why this might be?ALTER PROCEDURE [dbo].[UpdatePayment]
@paymentID int,
@PaymentDate datetime,
@TenderTypeID int,
@PaymentAmount money,
@CreditCardType int,
@ExpirationDate datetime,
@ccNumber nvarchar(50),
@Phrase nvarchar(25)
AS
BEGIN
SET NOCOUNT ON;
SET @Phrase = (SELECT Phrase FROM tblSpecialStuff)
UPDATE tblReceipts SET
PaymentDate=@PaymentDate,
TenderTypeID=@TenderTypeID,
AmountPaid=@PaymentAmount,
CreditCardType=@CreditCardType,
ExpirationDate=@ExpirationDate,
ccNumber = (CASE
WHEN @CreditCardType > 0 THEN
EncryptByPassPhrase(@Phrase,@ccNumber)
ELSE @ccNumber
END)
END
View 1 Replies
View Related
Dec 23, 2003
hi,
i need to run a stored procedure from an asp page that will import data into a temporary table.
i am having trouble with passing the parameter from the Exec command (currently developing in QA). It works ok where i want to use a value such as 'PC', or 'printer' or 'laptop' where the import sql has a where clause of '... = <<parameter>>...'.
I want however to be able to pass it a value such as ['printer','laptop','pc']so that the procedure will collect the data into the temp table with an '...in <<parameter>>'
this falls over and retrieves zero rows. the parameter is passed to the sp correctly as i have used a print command to display it, it comes back as 'pc',printer','laptop'. It appears to be the ' in ' that is not parsing the parameter.
can anyone help please?
TIA
View 3 Replies
View Related
Sep 3, 1999
Hello!
I have a problem when i want to construct a stored procedure. I want to pass a parameter with customerid's. The problem is that I donīt know how many customerId's I send to the stored procedure, because the user select the customerId's from a selectbox. The SQL string I want to excute looks like this
SELECT CustomerName FROM Customer WHERE CustomerId IN (199, 301, 408... )
How should I create the stored procedure so I put the customerId values in a parameter, like the procedure below? (I know that this not will work)
CREATE PROCEDURE rpt_PM2000_test(@SCustomerId varChar)
AS
BEGIN
SELECT Customer
FROM Customer
WHERE CustomerId IN (@sCustomerId)
END
/Fredrik
View 3 Replies
View Related
Apr 22, 2007
In the Create portion of a stored procedure, does the line in bold mean
that if the parameter @RegoDate is not provided, then use the current datetime?
CREATE PROCEDURE spGetRegoDetail
@Owner nvarchar(20),
@RegoDate datetime = getdate
AS ...
Thanks in advance,
G11DB
View 2 Replies
View Related
Mar 4, 2004
Hi,
I have an Access front end/MSDE2000 backend system. There is a form that has a subform which is based on the results of a stored procedure. Both my subform and mainform contain intOrderID ( which is the field i want to link both of them with ).
I would like to have the subform's data updated when changing records in the mainform. In Access, this was simple ( link child and master fields ), but not with MSDE2000 as a back end ( ADO connection thing...)
I think i have to create something to filter the results of the stored procedure, but i dont like the fact the the entire dataset needs to be transmitted over the network ( most of the time, users will only be creating new records, or reviewing recently created ones )
I'm really not sure as to how to accomplish this... Any ideas?
thanks!
View 10 Replies
View Related
Jun 16, 2007
HiI'm trying to alter my stored procedure to take a parameter for theDatabase Name, but as usual the syntax is killing me.Thanks for any helpDennis'--------------------------------------------------------------------------*--------------------------------Before - This Works without a paramater'--------------------------------------------------------------------------*--------------------------------set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[sp_CreateNewClientDb]ASCREATE DATABASE [MyClientDatabase] ON PRIMARY( NAME = N'MyClientDatabase',FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDATAMyClientDatabase.mdf' ,SIZE = 11264KB ,MAXSIZE = UNLIMITED,FILEGROWTH = 1024KB )LOG ON( NAME = N'MyClientDatabase_log',FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDATAMyClientDatabase_log.ldf' ,SIZE = 1024KB ,MAXSIZE = 2048GB ,FILEGROWTH = 10%)COLLATE SQL_Latin1_General_CP1_CI_AS'--------------------------------------------------------------------------*--------------------------------After - This Doesn't work with a parameter'--------------------------------------------------------------------------*--------------------------------set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[sp_CreateNewClientDb]ASCREATE DATABASE @ClientDBName ON PRIMARY( NAME = N@ClientDBName,FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDATA@ClientDBName' + '.mdf' ,SIZE = 11264KB ,MAXSIZE = UNLIMITED,FILEGROWTH = 1024KB )LOG ON( NAME = N'@ClientDBName' + '_log',FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDATA@ClientDBName' + '_log.ldf' ,SIZE = 1024KB ,MAXSIZE = 2048GB ,FILEGROWTH = 10%)COLLATE SQL_Latin1_General_CP1_CI_ASMsg 102, Level 15, State 1, Procedure sp_CreateNewClientDb, Line 4Incorrect syntax near '@ClientDBName'.
View 5 Replies
View Related
Dec 7, 2007
Hi all,
I'm trying to create a SP with a parameter for server.
CREATE PROCEDURE dbo.sp_Testing
@server ???????
AS
SELECT * from @server
GO
Does anyone know how to do what i'm trying here..???
I don't know the type of the parameter...is it just simply server? or is there another specific name for it???
thanks,
View 6 Replies
View Related
Apr 27, 2007
I'm attempting to return a value from my stored procedure. Here is my button click event that runs the stored procedure. protected void btn_Move_Click(object sender, EventArgs e)
{
/*variables need for the update of the old staff record
and the creation of the new staff record.
*/
BMSUser bmsUser = (BMSUser)Session["bmsUser"];
int staffid = Convert.ToInt32(bmsUser.CmsStaffID);
int oldStaffProfileID = Convert.ToInt32(Request.QueryString["profileid"]);
string newManager = Convert.ToString(RadComboBox_MangersbyOrg.Text);
int newMangerProfileID = Convert.ToInt32(RadComboBox_MangersbyOrg.Value);
SqlConnection conn = new SqlConnection(connect);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_BMS_MoveStaffMember";
cmd.Parameters.Add("@OldStaffProfileID", SqlDbType.Int);
cmd.Parameters["@OldStaffProfileID"].Precision = 9;
cmd.Parameters["@OldStaffProfileID"].Scale = 0;
cmd.Parameters["@OldStaffProfileID"].Value = oldStaffProfileID;
cmd.Parameters.Add("@NewManagerProfileID", SqlDbType.Int);
cmd.Parameters["@NewManagerProfileID"].Precision = 9;
cmd.Parameters["@NewManagerProfileID"].Scale = 0;
cmd.Parameters["@NewManagerProfileID"].Value = newMangerProfileID;
cmd.Parameters.Add("@LastModifiedBy", SqlDbType.Int);
cmd.Parameters["@LastModifiedBy"].Precision = 9;
cmd.Parameters["@LastModifiedBy"].Scale = 0;
cmd.Parameters["@LastModifiedBy"].Value = staffid;
SqlParameter sp = new SqlParameter("@OLDManagerReturn", SqlDbType.Int);
sp.Direction = ParameterDirection.Output;
cmd.Parameters.Add(sp);
lbl_ERROR.Text = Convert.ToString(sp);
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
conn.Close();
My parameter sp is coming back null for some reason. Am I not setting the parameter correctly? I set a break point and it says the value is null.
Here is the parts of my stored proc returning the value...
DECLARE @OLDManagerReturn numeric(9)
SELECT @OLDManagerReturn = ManagerProfileID FROM tblBMS_Staff_rel_Staff WHERE StaffProfileID = @OldStaffProfileID AND Active=1 AND BudgetYear = @BudgetYear
RETURN @OLDManagerReturn
View 5 Replies
View Related
Jun 19, 2007
In the SqlDbType enumeration there is no value for the new (max) types, only varchar. If Im passing a large string, it will get cut off at 8K. So how do I specify my varchar parameter as being of the max type ?
View 1 Replies
View Related
Jul 20, 2007
Hi,This works:CREATE PROCEDURE MyProc @Date smalldatetime = '2005-01-01'AS... But this does not CREATE PROCEDURE MyProc @Date smalldatetime = GETDATE()AS... I'm talking about sql2005. Can anyone help how to overcome this?
View 3 Replies
View Related
Aug 2, 2007
How do I assign a temp value to a parameter in a stored procedure?
I am trying like so:
Alter PROCEDURE ap_Insert_Pricing_ListPrice @partNumber varchar = "CQ2"AS SELECT PartNumber FROM Pricing WHERE (PartNumber = @partNumber)GO
What is the correct way to assign a temp value for testing purposes in SQL Server 2000 Query Analyzer?
When I try:
Alter PROCEDURE ap_Insert_Pricing_ListPrice @partNumber varchar
AS
Set @partNumber = "10-AF40-N04B-JZ"SELECT PartNumber FROM Pricing WHERE (PartNumber = @partNumber)GO
I get error:
Invalid column name '10-AF40-N04B-JZ'.
View 2 Replies
View Related
Aug 19, 2007
I have two stored procedures one generates an output parameter that I then use in the second stored procedure.
1 Try2 Dim myCommand As New SqlCommand("JP_GetChildren", myConn)3 myCommand.CommandType = Data.CommandType.StoredProcedure4
5 myCommand.CommandType = Data.CommandType.StoredProcedure6 myCommand.Parameters.Add(New SqlParameter("@ParentRule", Data.SqlDbType.NVarChar))7 myCommand.Parameters.Add(New SqlParameter("@PlantID", Data.SqlDbType.NVarChar))8 myCommand.Parameters.Add(New SqlParameter("@New_ReleasingRulePrefix", Data.SqlDbType.NVarChar))9 myCommand.Parameters.Add(New SqlParameter("@New_ReleasingRuleSuffix", Data.SqlDbType.NVarChar))10 myCommand.Parameters.Add(New SqlParameter("@New_PlantID", Data.SqlDbType.NVarChar))11 myCommand.Parameters.Add(New SqlParameter("@New_RuleSetID", Data.SqlDbType.NVarChar))12 myCommand.Parameters.Add(New SqlParameter("@Count", Data.SqlDbType.Int))13 myCommand.Parameters.Add(New SqlParameter("@IDField", Data.SqlDbType.NVarChar))14
15 Dim OParam As New SqlParameter()16 OParam.ParameterName = "@IDFieldOut"
17 OParam.Direction = ParameterDirection.Output18 OParam.SqlDbType = SqlDbType.NVarChar19 myCommand.Parameters.Add(OParam)20
21
22 myCommand.Parameters("@ParentRule").Value = txtParentRule.Text23 myCommand.Parameters("@PlantID").Value = txtStartingPlantID.Text24 myCommand.Parameters("@New_ReleasingRulePrefix").Value = txtReleaseRuleFromPrefix.Text25 myCommand.Parameters("@New_ReleasingRuleSuffix").Value = txtReleaseRuleFromSuffix.Text26 myCommand.Parameters("@New_PlantID").Value = txtEndingPlantID.Text27 myCommand.Parameters("@New_RuleSetID").Value = txtEndingRuleSetID.Text28 myCommand.Parameters("@Count").Value = 129 myCommand.Parameters("@IDField").Value = " "
30 myCommand.Parameters("@IDFieldOut").Value = 031
32 myCommand.ExecuteNonQuery()33
34 Dim IDField As String = myCommand.Parameters("@IDFieldOut").Value35
If i run this stored procedure in sql it does return my parameter. But when i run this code IDField comes back null. Any ideas
View 6 Replies
View Related
Feb 3, 2008
I have a stored procedure that inserts a new record, and returns the ID value for the newly inserted record. The procedure works fine, but I'm unable to get that return value in my code. Here's what I have:
IF OBJECT_ID ( 'dbo.dbEvent', 'P') IS NOT NULL
DROP PROCEDURE dbEvent
GO
CREATE PROCEDURE
@Name varchar(200)
,@Location varchar(200)
AS
INSERT INTO Event
(
NAME
, LOCATION
)
VALUES
(
NAME
, @LOCATION
)
SELECT SCOPE_IDENTITY()
And my code behind: public Int64 Insert(SqlConnection Conn)
{
try
{
using (SqlCommand Command = new SqlCommand("dbo.dbEvent", Conn))
{
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Add("@ID", ID).Direction = ParameterDirection.Output;
Command.Parameters.Add("@NAME", SqlDbType.VarChar, 200).Value = PermitName;
Command.Parameters.Add("@LOCATION", SqlDbType.VarChar, 200).Value = Location;
if (Conn.State != ConnectionState.Open) Conn.Open();
Command.ExecuteNonQuery();
Int64 _requestId = Convert.ToInt64(Command.Parameters.Add("@ID", SqlDbType.BigInt).Value.ToString());
return _requestId;
}
I'm getting the error that I have "Too many arguments specified" in my Insert() method. When I test the procedure in Query Analyzer, it works fine and returns the correct value.
Any suggestions? Don't I need to declare that return value in my .NET code as an output parameter?
View 2 Replies
View Related
Dec 9, 2003
This works:
WHERE ltrim(str((DATEPART(yyyy, dbo.Media_Tracking_Ad_History.ADDATE))) IN ('2003','2004','2005'))
This doesn't:
WHERE
WHERE ltrim(str((DATEPART(yyyy, dbo.Media_Tracking_Ad_History.ADDATE))) IN (@strYears))
@strYears will work if I only pass a single value such as 2003. I've tried every combination of single and double quotes I can think of to pass multiple values but nothing works. Any suggestions?
View 4 Replies
View Related
Mar 31, 2004
Hey huys, I got this stored procedure. First of all: could this work?
--start :)
CREATE PROCEDURE USP_MactchUser
@domainUserID NVARCHAR(50) ,
@EmployeeID NVARCHAR(50) ,
@loginType bit = '0'
AS
INSERT INTO T_Login
(employeeID, loginType, domainUserID)
Values
(@EmployeeID, @loginType, @domainUserID)
GO
--end :)
then I got this VB.Net code in my ASP.net page....
---begin :)
Private Sub matchUser()
Dim insertMatchedUser As SqlClient.SqlCommand
Dim daMatchedUser As SqlClient.SqlDataAdapter
SqlConnection1.Open()
'conn openned
daMatchedUser = New SqlClient.SqlDataAdapter("USP_MatchUser", SqlConnection1)
daMatchedUser.SelectCommand.CommandType = CommandType.StoredProcedure
daMatchedUser.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@EmployeeID", SqlDbType.NVarChar, 50))
daMatchedUser.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@domainUserID", SqlDbType.NVarChar, 50))
daMatchedUser.SelectCommand.Parameters("@EmployeeID").Value = Trim(lblEmployeeID.Text)
daMatchedUser.SelectCommand.Parameters("@domainUserID").Value = Trim(lblDomainuserID.Text)
daMatchedUser.SelectCommand.Parameters("@EmployeeID").Direction = ParameterDirection.Output
daMatchedUser.SelectCommand.Parameters("@domainUserID").Direction = ParameterDirection.Output
SqlConnection1.Close()
'conn closed
End Sub
---
If I try this it doesn't work (maybe that's normal :) ) Am I doing it wrong. The thing is, in both label.text properties a values is stored that i want to as a parameter to my stored procedure. What am I doing wrong?
View 2 Replies
View Related
Apr 19, 2004
I am trying to supply a bit type parameter to a stored procedure. This is used to update a Bit type field in a table. The field is called PDI
The syntax I am trying to use is:
MyStoredProcedure.Parameters.Add(New SqlParameter("@Pdi",SqlDbtype.bit))
MyStoredProcedure.Parameters("@pdi").value = -1
When I do my ExecuteNonQuery I get error 8114
What am I doing wrong?
View 9 Replies
View Related
Jun 22, 2005
Hi all,
this is an easy one for those who know.
Why do you use two '@' symbols in front of a variable in a stored procedure?
Thanks
Tryst
View 8 Replies
View Related
Aug 1, 2005
What i want is to add by parameter a Where clause and i can not find how to do it!CREATE PROCEDURE [ProcNavigate]( @id as int, @whereClause as char(100))ASSelect field1, field2 from table1 Where fieldId = @id /*and @WhereClause */GOany suggestion?
View 1 Replies
View Related
Sep 19, 2005
Hi I have an if clause in my code to add the final parameter value to send to the database.
If Page.User.IsInRole("MICMS") Then
cmdCheckUser.Parameters.Add("@C_ID", 0)
Else
cmdCheckUser.Parameters.Add("@C_ID", Session("C_ID"))
End If
If the user is in the role, the error is triggered saying that @C_ID
is expected by the stored procedure. If i then change the value from 0
to 10, the stored procedure works fine.Is there any reason that the stored procedure is failing when the value 0 is used and not when any other value is used?Thanking you in advance.
View 1 Replies
View Related
Mar 9, 2006
How can I pass a parameter to a stored procedure using Visual Web Developer 2005? I have created a SQLDataSource that calls the SP.
Thanks
--R
View 1 Replies
View Related
