Hello, does anyone know how to determine (within the stored procedure)
whether a parameter has been passed explicitly to a stored procedure as
opposed to a parameter that has taken on the default value.
Many thanks
Phil
I'm generating a list of parameters needed by stored procedures, and
I'd like to know which ones have default values assigned to them.
To retrieve the parameter information I use:
sp_sproc_columns @Procedure_Name='InsertUser''
However, the column that is supposed to give the default value,
'COLUMN_DEF' always returns as NULL, even when that column has a
default value assigned to it.
i.e.
CREATE PROCEDURE InsertUser@UserID INT = 10,.....
And then if I do a sp_sproc_columns @Procedure_Name='InsertUser'', the COLUMN_DEF value for the @UserID column is still NULL.
Does anyone know what I'm doing wrong and how I can retrieve the default value?
Thanks
Hi all,
From the "How to Call a Parameterized Stored Procedure by Using ADO.NET and Visual Basic.NET" in http://support.microsft.com/kb/308049, I copied the following code to a project "pubsTestProc1.vb" of my VB 2005 Express Windows Application:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlDbType
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim PubsConn As SqlConnection = New SqlConnection("Data Source=.SQLEXPRESS;integrated security=sspi;" & "initial Catalog=pubs;")
Dim testCMD As SqlCommand = New SqlCommand("TestProcedure", PubsConn)
testCMD.CommandType = CommandType.StoredProcedure
Dim RetValue As SqlParameter = testCMD.Parameters.Add("RetValue", SqlDbType.Int)
RetValue.Direction = ParameterDirection.ReturnValue
Dim auIDIN As SqlParameter = testCMD.Parameters.Add("@au_idIN", SqlDbType.VarChar, 11)
auIDIN.Direction = ParameterDirection.Input
Dim NumTitles As SqlParameter = testCMD.Parameters.Add("@numtitlesout", SqlDbType.Int)
NumTitles.Direction = ParameterDirection.Output
auIDIN.Value = "213-46-8915"
PubsConn.Open()
Dim myReader As SqlDataReader = testCMD.ExecuteReader()
Console.WriteLine("Book Titles for this Author:")
Do While myReader.Read
Console.WriteLine("{0}", myReader.GetString(2))
Loop
myReader.Close()
Console.WriteLine("Return Value: " & (RetValue.Value))
Console.WriteLine("Number of Records: " & (NumTitles.Value))
End Sub
End Class
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
The original article uses the code statements in pink for the Console Applcation of VB.NET. I do not know how to print out the output of ("Book Titles for this Author:"), ("{0}", myReader.GetString(2)), ("Return Value: " & (RetValue.Value)) and ("Number of Records: " & (NumTitles.Value)) in the Windows Application Form1 of my VB 2005 Express. Please help and advise.
Thanks in advance,
Scott Chang
Hi,
I wanted to use a default for yearly instead of monthly for stored procedure, but couldn't figure out how. I usually used stored procedure @start and @end for date.
i.e. this is monthly by default
@start = jul 2007 and @end= aug 2007;
i.e. i want yearly by default
@start = jul 2007 and @end= jul 2008.
Is this too confusing?
Please help!!!!!!!!!
Hello,
I am hoping there is a solution within SQL that work for this instead of making round trips from the front end. I have a stored procedure that is called from the front-end(USP_DistinctBalancePoolByCompanyCurrency) that accepts two parameters and returns one column of data possibly several rows. I have a second stored procedure(USP_BalanceDateByAccountPool) that accepts the previous stored procedures return values. What I would like to do is call the first stored procedure from the front end and return the results from the second stored procedure. Since it's likely I will have more than row of data, can I loop the second passing each value returned from the first?
The Stored Procs are:
CREATE PROCEDURE USP_DistinctBalancePoolByCompanyCurrency
@CID int,
@CY char(3)
AS
SELECT Distinct S.BalancePoolName
FROM SiteRef S
INNER JOIN Account A ON A.PoolId=S.ID
Inner JOIN AccountBalance AB ON A.Id = AB.AccountId
Inner JOIN AccountPool AP On AP.Id=A.PoolId
Where A.CompanyId=@CID And AB.Currency=@CY
CREATE PROCEDURE USP_BalanceDateByAccountPool
@PoolName varchar(50)
AS
Declare @DT datetime
Select @DT=
(Select MAX(AccountBalance.DateX) From Company Company
INNER JOIN Account Account ON Company.Id = Account.CompanyId
INNER JOIN SiteRef SiteRef ON Account.PoolId = SiteRef.ID
Inner JOIN AccountBalance AccountBalance ON Account.Id = AccountBalance.AccountId
WHERE SiteRef.BalancePoolName = @PoolName)
SELECT SiteRef.BalancePoolName, AccountBalance.DateX, AccountBalance.Balance
FROM Company Company
INNER JOIN Account Account ON Company.Id = Account.CompanyId
INNER JOIN SiteRef SiteRef ON Account.PoolId = SiteRef.ID
Inner JOIN AccountBalance AccountBalance ON Account.Id = AccountBalance.AccountId
WHERE SiteRef.BalancePoolName = @PoolName And AccountBalance.DateX = @DT
Order By AccountBalance.DateX DESC
Any assistance would be greatly appreciated.
Thank you,
Dave
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 RelatedIs there a way inside a stored procedure to determine if a parameter defined with a default value was explicity passed the same value as the default vs. the parameter was omitted and you just got the default value.
E.g. create procedure mysp_stuff @parm1 varchar(20)=NULL, @parm1 bit=0 as
if (@parm1 is null)
begin
-- did we get the default or did the caller pass NULL
end
if (@parm2 = 0
begin
-- did we get the default or did the caller pass 0
end
GO
EXEC mysp_stuff
go
EXEC mysp_stuff @parm1=NULL, @parm2=0
go
Can someone explain to me why the following doesn't work?declare @oname sysnameselect @oname=name from sysobjects where name like"df__mytable__mycol%"alter table mytable drop constraint @oname
View 4 Replies View RelatedWhen I call this function and the database field 'Login' is null, then I get an error message "Conversion from type 'DBNull' to type 'String' is not valid." I've supplied a default value in my stored procedure and I've also provided a default value in the result value in the function. How do I get around this? Thanks
lblLastUserLogin.Text = GetLastUserLogin().ToString()
Private Function GetLastUserLogin() As String Dim result As String = "" Dim con As New SqlConnection("server=x.x.x.x;database=database;uid=x;password=x") Dim cmd As New SqlCommand("GetLastUserLogin", con) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = lblUserID.Text cmd.Parameters.Add("@ReturnVal", SqlDbType.SmallDateTime).Direction = ParameterDirection.Output Using con con.Open() cmd.ExecuteNonQuery() result = CType(cmd.Parameters("@ReturnVal").Value, String) End Using Return resultEnd Function
CREATE PROCEDURE [dbo].[GetLastUserLogin] -- OUTPUT parameter to hold the count. @UserID int, @ReturnVal smalldatetime = ' ' OUTPUT AS -- This will return the last date returned by the SELECT query. Set @ReturnVal = (SELECT MAX(Login) FROM TUserLogs WHERE UserID=@UserID)
Hello
I've written a stored procedure with 4 parameters
create procedure dummy
( @a int, @b int, @c varchar(50), @d varchar(50))
now from front end(I'm using c#.net)
I want to send the values according to some criteria
So in the process...I've only values for @a & @c ...
so In order to reduce the code of sending Null values explicitly to other parameters...
can't I set like default values for it so that If I don't send values to certain parameters it will have the default value or Null value.
like I want something like this:
create procedure dummy
( @a int NULL, @b int NULL, @c varchar(50) NULL, @d varchar(50) NULL)
Hi everyone,
I have created a stored procedure in sql server with parameters for my c# application. Wanted to know is there anyway to set the default value for @searchpostcode to select all the records?
Right now it brings the records based on the postcode specified .(I have dropdownlist in my c# application that passes the parameters for postcode)
My stored procedure:
CREATE PROCEDURE sp_accepting_practice (@searchpostcode as nvarchar(100)) AS
SELECT dbo.tbdentists.Title, dbo.tbdentists.FirstName, dbo.tbdentists.Surname, dbo.tbpractices.PracticeName, dbo.tbpractices.PracticeAddress1, dbo.tbpractices.PracticeAddress2, dbo.tbpractices.Town, dbo.tbpractices.Postcode, dbo.tbpractices.Phone, dbo.tbdentistspractices.ListNo, dbo.tbtreatment.treatmentNatureFROM dbo.tbdentists INNER JOIN dbo.tbdentistspractices ON dbo.tbdentists.DentistId = dbo.tbdentistspractices.DentistId INNER JOIN dbo.tbpractices ON dbo.tbdentistspractices.PracticeId = dbo.tbpractices.PracticeId AND dbo.tbdentistspractices.PracticeId = dbo.tbpractices.PracticeId INNER JOIN dbo.tbtreatment ON dbo.tbdentistspractices.TreatmentId = dbo.tbtreatment.treatmentIdWHERE dbo.tbpractices.Postcode LIKE '%' + @searchpostcode + '%'ORDER BY dbo.tbpractices.PracticeId
EXECUTE sp_accepting_practice G4GO
I greatly appreciate your help. Thanks in Advance
Regards
Shini
Hi,I have a problem with a call to stored procedure on SQL server. I created stored procedure for inserting a row to a table.In my application I read data from ASP.NET form and prepare parameters for calling my stored procedure. Like: SqlCommand sc = new SqlCommand("AddToMyTable"); try { sc.CommandType = CommandType.StoredProcedure; sc.Connection = new SqlConnection("myconnectionstringhere"); SqlParameter param; param = sc.Parameters.Add("@MyFirstParam", SqlDbType.NVarChar,50); param.Value = "something"; ..... here I repeate "add" and value assignment for all my parameters }...When I call ExecuteNonQuery(); I get exception that one of the parameters for stored procedure is expected but not supplied. However, I can find this very same parameter in my source code to be added to list of parameters. I think my problem is caused by large number of parameters. I have 55 parameters, half of them are of type nvarchar(50) and half of them of type bit. My reasoning is based on the fact that if I remove parameter that is reported to be missing from my stored procedure and from my application, I get the same error with next parameter in the list.Where should I increase maximum number of parameters that are allowed? Do I have some buffer problem? Where to specify that I need more space for parameters? Nenad
There is a form in my project which users can add their emails through it. The maximum numbers of emails are 60. I put a textbox and a button for adding email. But I don’t know which of the following solutions is suitable:
1. After each click by user , the insert stored procedure will be called
2. All the emails entered by users will be saved and then, the insert stored procedure will be called. This SP must have a loop but I am not sure about the right code for this SP because I have to define 60 input parameters for this SP.
Hi
what's the difference between using parameters in these two ways:
SELECT
@PortalName = Portals.PortalName ...
and
SELECT
Portals.PortalName...
first one will NOT be a result set, and second one will
Whats the basic difference between that? I need to use a NextResult method within a .cs filebut it is not working
thanks
I am creating a advanced search page.in that I have 11 fields.i wrote a stored procedure which having all 11 parameters.
If I don’t enter any one of the value I am not getting the result and it will goes on exception handling.
Now what I want is even I enter one value this must be excute………….i think some validations should be done in
Stored procedure's 'where' clause.but I don’t know how
i would be very happy if someone guides me with sample code for this.
I know there ar in parameters and out parameters, but are there any other kinds? Are there in/out parameters? If so, how would you declare one in the stored procedure?
Thanks.
Hi, I am using isqlw from SQL Server 2000. Is it possible to create T-SQL script that can receive input parameter from outside?
Example, my stored procedure is:
create my_sp @inputvar varchar(12)
as
select @inputvar
go
then I will create an .sql (such as mysql.sql) file that contains line:
exec my_sp parameter
next, I would like to call this .sql file using:
isqlw.... -i mysql.sql parameter?? -o output.txt
is it possible to do that way? Or is there any other way to do so?
Thanks a bunch,
nicoart.
I am creating a advanced search page.in that I have 11 fields.i wrote a stored procedure which has all 11 parameters.
If I don’t enter any one of the value I didn’t get the result and it is raising exceptions.
Parameters are passed to Stored Procedure from VB.NET Application like
Dim param As SqlParameter
param = New SqlParameter("@name",DbType.String)
param.Direction = ParameterDirection.Input
param.Value = "ABC"
comand.Parameters.Add(param)
But is there any way to specify parameters without specifying the
parameter name and type like
Dim param As SqlParameter
param = New SqlParameter
param.Direction = ParameterDirection.Input
param.Value = "ABC"
comand.Parameters.Add(param)
But when i try like this i am getting an error saying that "Parameter1 is not a parameter in the procedure"...
Can anybody tell me how to solve this .
Thanx in advance
If a sqldatasource is programed to send textbox1.text to a stored procedure, and the .text property is left empty, and there is no default value set for the parameter, what exactly is the stored procedure receiving?I would like to run a IF BEGIN statement on the value of the parameter in the stored procedure but the following does not work:IF @Parameter IS NULL BEGINor IF @Parameter = '' BEGINThe only way I've gotten it to work is if I set the default value of the parameter being sent to a specific alphanumeric value. Then do something like:IF @Parameter = '99' BEGIN<Code Here>END
View 4 Replies View RelatedHi All,
I have created a stored procedure (in SQL Server 2005 - Developer) with input and output parameters. Please somebody let me know how I can call this store procedure from code behind using TableAdapter i.e. through XSD.
Thanks,
Long Live Microsoft ;)
Hello everyone,
I am having problem with a program that gets some input from a webform and inserts to a stored procedure, I am getting the two error Error messages below, can somebdoy have a look my code below and put me in the right direction. thanks in advance
Errors
'System.Data.SqlClient.SqlCommand' does not contain a definition for 'InsertCommandType'
'System.Data.SqlClient.SqlCommand' does not contain a definition for 'InsertCommand'
protected void Button1_Click(object sender, EventArgs e)
{
/* These two variables get the values of the textbox (i.e user input) and assign two local
* variables, This is also a good strategy against any Sql Injection Attacks.
*
*/
string Interview1 = TextBox1.Text;
string Interview2 = TextBox2.Text;
string Interview3 = TextBox3.Text;
string ProdMentioned = TextBox4.Text;
string ProdSeen = TextBox5.Text;
string Summary = TextBox6.Text;
string Compere = TextBox7.Text;
string Duration = TextBox8.Text;
//Create Sql connection variable that call the connection string
SqlConnection SqlConnection = new SqlConnection(GetConnectionString());
//Create a sql command to excute SQL statement against SQL server
SqlCommand Command = new SqlCommand();
// Set the command type as one that calls a Stored Procedure.
Command.InsertCommandType = CommandType.StoredProcedure;
//Call the stored procedure so we can pass it on the user input to retrieve user details
Command.InsertCommand = "Summaries";
//open the command connection with the connection string
Command.Connection = SqlConnection;
// Pass the user input to the Stored Procedure to check if user exists in our system.
Command.InsertParameters.Add("interview1", interview1);
Command.InsertParameters.Add("interview2", interview2);
Command.InsertParameters.Add("interview3", interview3);
Command.InsertParameters.Add("ProdMentioned", ProdMentioned);
Command.InsertParameters.Add("ProdSeen", ProdSeen);
Command.InsertParameters.Add("Compere", Compere);
Command.InsertParameters.Add("Duration", Duration);
int rowsAffected = 0;
try
{
rowsAffected = Command.Insert();
}
catch (Exception ex)
{
Resonse.Redirect("InsertSuccessfull.aspx");
}
// open the connection with the command
//Command.Connection.Open();
}
private static string GetConnectionString()
{
return ConfigurationManager.ConnectionStrings["BroadcastTestConnectionString1"].ConnectionString;
}
}
Hi Y'all,
On how many ways can i pass parameters IN a stored procedure? I want to boost my performance and get rid of 4 for loops in my page load.
SO how can I create a resultset (grid) with the same result as looping?
I hope i made myself clear....
Thanks in advance
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>
How do I pass values from my ASP.NET page code into my Stored Procedure, to become parameters to be used in my Stored Proc?
Much thanks
How to write stored procedure with two parametershow to check those variables containing values or empty in SP
if those two variables contains values they shld be included in Where criteiriea in folowing Query with AND condition, if only one contains value one shld be include not other one
Select * from orders plz write this SP for me thanks
I want to know how to set parameters for Update Stored Procedure in SQLDataSource
i built a stored procedure with inserting in to customers table.
i have one column with identity.
so want to take that identity column value in the same stored procedure.
so how can i write that procedure with insert in to statements in that stored procedures.
can any one tell me.
also how to get that value in ado.net 2.0.
friends please tell me.
Hi i'll get the following error
The SqlParameter with ParameterName 'xxxxx' is already contained by another SqlParameterCollection.
I'am trying to create an dataset in which data of the different connectionpoints are separated into different tables. Herefor i'm using an stored procedure.
Below you'll find part of the main function and the full function which will execute the stored procedure.
kind regards
main function
Dim Parameters As SqlParameter() = { _
New SqlParameter("@ConnectionPointID", SqlDbType.NVarChar, 255)}
For x = 0 To myds.Tables("ConnectionPoints").Rows.Count - 1
connectionpointID = myds.Tables("connectionpoints").Rows(x).Item("Eancode")
Parameters(0).Value = connectionpointID
mydb.doStoredProcedure("SP_EDS_Dyomes_XML", Parameters,connectionpointID.ToString, myds)
Next
Public Overloads Function doStoredProcedure( _
ByRef Mycommand As SqlCommand, _
ByRef myds As DataSet, _
ByVal Table As String)
Dim myda As New SqlDataAdapter
Mycommand.CommandTimeout = 180
If Mycommand.Connection Is Nothing Then
Dim mydb As New Database
Mycommand.Connection = mydb.generateconnection
End If
If Mycommand.Connection.State = ConnectionState.Closed Then
Mycommand.Connection.Open()
End If
CheckParameters(Mycommand)
myda.SelectCommand = Mycommand
Try
myda.Fill(myds, Table)
Catch ex As Exception
Debug.Write(ex.Message)
End Try
myda.Dispose()
Mycommand.Dispose()
End Function
Hi
I've an existing SQL 2000 Stored Procedure that return data in many (~20) output parameters.
I'm starting to use it in a .Net c# application and it seems to insist that I setup all the output parameters:
SqlParameter param = cmd.Parameters.Add("@BackgroundColour",SqlDbType.TinyInt);
param.Direction=ParameterDirection.Output;
even if I only need the value of a single one.
Is this right? Is there a way to avoid coding every one every time?
Hello everyone,
I've been trying to use a stored procedure to return the names of some temporary tables that i put in the tempdb table in SQL Server.
--I've been getting the following error in visual basic 6 when i try to call this:
run-time error '-2147217900 (80040e14)': syntax access violation
--This is the error you get when you try to just run the code in query analyzer:
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation
--what code i was trying to use (in query analyzer):
{call EXEC CreateTempTables (@RQSodfil = 'a', @BulkRan = 'a', @BulkFor = 'a', @BulkJit = 'a', @ID = '0', @RQSodfilFlag = '1', @BulkRanFlag = '0', @BulkForFlag = '0', @BulkJitFlag = '0', @DeleteFlag = '0', @ErrorNum = '0')}
--code that i was trying to use in vb 6:
Public Sub TemporaryTables( _
ByVal bytRQSodfilFlag As Byte, _
ByVal bytBulkRanFlag As Byte, _
ByVal bytBulkForFlag As Byte, _
ByVal bytBulkJitFlag As Byte, _
ByVal bytDeleteFlag As Byte, _
ByVal cnPlant As String)
Dim objConn As ADODB.Connection
Dim objCmd As ADODB.Command
Dim objRQSodfil As Parameter
Dim objBulkRan As Parameter
Dim objBulkFor As Parameter
Dim objBulkJit As Parameter
Dim objParamID As Parameter
Dim objRQSodfilFlag As Parameter
Dim objBulkRanFlag As Parameter
Dim objBulkForFlag As Parameter
Dim objBulkJitFlag As Parameter
Dim objDeleteFlag As Parameter
Dim objErrorNum As Parameter
Dim intErrorNum As Integer
' setup command variable
Set objCmd = New ADODB.Command
Set objConn = New ADODB.Connection
objConn.Open cnPlant
objCmd.CommandText = "EXEC CreateTempTables"
objCmd.CommandType = adCmdStoredProc
objCmd.ActiveConnection = objConn
' setup parameters
Set objRQSodfil = objCmd.CreateParameter("@RQSodfil", adVarChar, adParamInputOutput, 20, "a")
objCmd.Parameters.Append objRQSodfil
Set objBulkRan = objCmd.CreateParameter("@BulkRan", adVarChar, adParamInputOutput, 20, "a")
objCmd.Parameters.Append objBulkRan
Set objBulkFor = objCmd.CreateParameter("@BulkFor", adVarChar, adParamInputOutput, 20, "a")
objCmd.Parameters.Append objBulkFor
Set objBulkJit = objCmd.CreateParameter("@BulkJit", adVarChar, adParamInputOutput, 20, "a")
objCmd.Parameters.Append objBulkJit
Set objParamID = objCmd.CreateParameter("@ID", adChar, adParamInputOutput, 2, 0)
objCmd.Parameters.Append objParamID
Set objRQSodfilFlag = objCmd.CreateParameter("@RQSodfilFlag", adTinyInt, adParamInput, , bytRQSodfilFlag)
objCmd.Parameters.Append objRQSodfilFlag
Set objBulkRanFlag = objCmd.CreateParameter("@BulkRanFlag", adTinyInt, adParamInput, , bytBulkRanFlag)
objCmd.Parameters.Append objBulkRanFlag
Set objBulkForFlag = objCmd.CreateParameter("@BulkForFlag", adTinyInt, adParamInput, , bytBulkForFlag)
objCmd.Parameters.Append objBulkForFlag
Set objBulkJitFlag = objCmd.CreateParameter("@BulkJitFlag", adTinyInt, adParamInput, , bytBulkJitFlag)
objCmd.Parameters.Append objBulkJitFlag
Set objDeleteFlag = objCmd.CreateParameter("@DeleteFlag", adTinyInt, adParamInput, , bytDeleteFlag)
objCmd.Parameters.Append objDeleteFlag
Set objErrorNum = objCmd.CreateParameter("@ErrorNum", adInteger, adParamInputOutput, , 0)
objCmd.Parameters.Append objErrorNum
' execute command
Set rsTableInfo = objCmd.Execute(, , adExecuteRecord)
' find returned parameters
gstrRQSodfilName = rsTableInfo.Fields("@RQSodfil")
gstrBulkRanName = rsTableInfo.Fields("@BulkRan")
gstrBulkForName = rsTableInfo.Fields("@BulkFor")
gstrBulkJitName = rsTableInfo.Fields("@BulkJit")
gstrID = rsTableInfo.Fields("@ID")
intErrorNum = rsTableInfo.Fields("@ErrorNum")
End Sub
any help would be appreciated
EXEC('SELECT COUNT(docid) AS Total FROM docs WHERE ' + @QueryFilter)I want to get the cound as an output parameter.I can get output parameters to work only when I dont use EXEC. I need to use EXEC for this case since @QueryFilter gets generated in the stored procedure based on some some other data.How can I get that count using ouput parameter?
View 2 Replies View RelatedI've got a sp that originally took two params, but has been modified to to use three. It worked fine before, but now seems to give me the error that it's expecting a certain param but that it's not being supplied. I know for a fact that all three params are declared correctly and are all being initialized with the correct values, but the sp still doesn't seem to recognize that one of them is there. I don't know if this is relevant but right before I changed the sp we cleared the database and added a few tables but that's all that's changed.
Here's the code I use for creating the parameters. Like I said before, this worked earlier when only two parameters were being used, but it doesn't seem to work with two params now when I change it back to test it. Code:
'Call stored procedure for creating Tracking ID Number and adding Route Users to Tracking List
cmd.ActiveConnection = conn
cmd.CommandText = "getNextTrackingNumber"
'Add necessary parameters
Set paramRouteID = cmd.CreateParameter("@RouteID", adInteger, adParamInput, , routeid)
cmd.Parameters.Append paramRouteID
Set paramUser = cmd.CreateParameter("@CurrentUser", adVarChar, adParamInput, 255, UCase(Trim(CurrentUser)))
cmd.Parameters.Append paramUser
Set paramRouteType = cmd.CreateParameter("@RouteType", adVarChar, adParamInput, 255, UCase(Trim(ROUTE_APPROVAL_SHEET)))
cmd.Parameters.Append paramRouteType
I've debugged it and I'm 100% positive that there are values for these parameters after they've been created, so I'm at a complete loss as to what the problem here is. Any help would be appreciated.
Thanks,
Josh