Problem Sending Date Parameters To Stored Procedure
Jun 30, 2006
Can anyone help,
I have a problem with calling my stored procedure in vb and passing in its parameters. the system is supposed to then use the store the procedures recordset and use this to pass into a crystal report.
I am getting the following message:
Procedure 'TestReport' expects parameters '@StartDate', which was not supplied.
This happens on the objCom.Execute line.
This is my code:
Dim CRXApplication As New CRAXDRT.Application
Dim CRXReport As CRAXDRT.Report
Dim CRXDatabase As CRAXDRT.Database
Set CRXReport = CRXApplication.OpenReport(APPConst.DatabasePath & stReport & ".rpt")
Dim objCom As adodb.Command
Set objCom = New adodb.Command
Dim prm_one As adodb.Parameter
Dim prm_two As adodb.Parameter
Dim prm_three As adodb.Parameter
Dim fdate, tdate As Date
Call CRXReport.Database.Tables(1).SetLogOnInfo("dell330", "Rota", "RotaAdmin", "dadcahadfu")
Set CRXDatabase = CRXReport.Database
fdate = cboFrom.Value
tdate = cboTo.Value
With objCom
.CommandText = "TestReport"
.CommandType = adCmdStoredProc
.ActiveConnection = cn.ConnectionString
Set prm_one = .CreateParameter("@StartDate", adDBTimeStamp, adParamInput, 8, fdate)
.Parameters.Append prm_one
Set prm_two = .CreateParameter("@EndDate", adDBTimeStamp, adParamInput, 8, tdate)
.Parameters.Append prm_two
Set prm_three = .CreateParameter("@Team", adInteger, adParamInput, 4, 2)
.Parameters.Append prm_three
.Parameters.Refresh
End With
Set SQLRecs = objCom.Execute
CRXReport.DiscardSavedData
CRXDatabase.SetDataSource SQLRecs, 3, 1
This is my Stored Procedure:
CREATE PROCEDURE [dbo].[TestReport] @StartDate datetime, @EndDate datetime, @Team int AS
SELECT dbo._Staff.UniqueName, 2 * DATEDIFF(day, @StartDate, dbo._StaffUnavailable.[Date]) + dbo._StaffUnavailable.TimeCode AS Expr1,
COUNT(dbo._StaffUnavailable.ID) AS CountOfID, dbo._StaffUnavailable.[Date], dbo._Team.Description AS Team, dbo._Staff.Team_ID
FROM dbo._Staff INNER JOIN
dbo._StaffUnavailable ON dbo._Staff.Staff_ID = dbo._StaffUnavailable.Staff_ID INNER JOIN
dbo._Team ON dbo._Staff.Team_ID = dbo._Team.ID
GROUP BY dbo._Staff.Staff_ID, dbo._Staff.UniqueName, 2 * DATEDIFF(day, @StartDate, dbo._StaffUnavailable.[Date]) + dbo._StaffUnavailable.TimeCode,
dbo._StaffUnavailable.[Date], dbo._Team.Description, dbo._Staff.Team_ID
Having Team_ID = @Team AND ([Date] >= @StartDate AND [Date] <= @EndDate)
GO
Thanks in advance
Ian.
View 4 Replies
ADVERTISEMENT
Aug 4, 2015
I am working on an Excel VBA report which is linked to an SQL Server database. The front end is Excel VBA routine, the backend is SQL Server stored procedure.
The VBA routine passes 2 dates to the stored procedure but it seems that it doesn't accept them.
The 2 date parameters in the stored procedure are @OrderDateRangeStart and @OrderDateRangeEnd.
Here is a portion of the stored procedure:
alter proc uspSalesCommission
...
...
@IncludeOrderDateAsCriterion int,
@OrderDateRangeStart date,
@OrderDateRangeEnd date
as
BEGIN
...
...
WHERE (@IncludeSalesPersonsAsCriterion=0 or Staff.name in (@Salespersons)) and
(@IncludeOrderDateAsCriterion=0 or SALESORD_HDR.ORDERDATE between @OrderDateRangeStart and @OrderDateRangeEnd)
In Excel VBA, the code passing the parameters are:
cmd1.parameters("@OrderDateRangeStart").value = cdate(me.startDate)
cmd1.parameters("@OrderDateRangeEnd").value = cdate(me.EndDate)
where cmd1 is a command object, me.startdate is start date field in Excel me.enddate is the end date field in Excel.
The Excel VBA routine works only when the day and month are both 1,(e.g. 1/1/2015), when they are other values (e.g. 31/5/2014) , it failed.
View 5 Replies
View Related
Mar 28, 2008
Hi,
I have created a stored procedure for sending mail. While executing that i am getting the following error message:
SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.
Can anybody explain me how to resolve it?
Expecting the solution at the earliest
View 2 Replies
View Related
Feb 23, 2005
Hello
How can I send a list(s) of data into a stored procedure for processing?
For instance, I have a table: GroupContacts(groupname, userid, contactid).
At times I will be inserting X amount of records into it, X depends on how many contacts need to be added to a group by a user.
How can I send a list of (groupname, userid, contactid)'s into a stored procedure and then use some kind of for-loop to iterate through the list and insert the records?
Thanks
jenn
View 3 Replies
View Related
Mar 1, 2006
Dear All,
I need to create a functionality in SQL server 2000 that would send out data from a table to users within our company.
Is there some built in funcitonality wherby i can mail out the selected data from the table from a stored procedure? Then i could schedule the stored procedure to be run every morning at 7 am.
Never done this, can some one let me know if that would work and give me some pointers on what commands to use in the stored procedure? Thanks in advance!
View 7 Replies
View Related
Jul 14, 2000
I have created a DTS package. I want to know how to call/run this package from a stored procedure.
Please let me know how to call this DTS from a Stored Procedure.
Thanks.
-Rajesh
View 1 Replies
View Related
Nov 3, 2005
In a VB.NET script, I am adding the dbnull value to a parameter that will populate a smalldatetime column:
cmd.Parameters.Add("@unitHdApprove", System.DBNull.Value.ToString)
The stored procedure then defines the input as smalldatetime:
@unitHdApprove smalldatetime,
However, the result is that the record is inserted with 1/1/1900 as the date value, instead of <NULL>.
I'm guessing that this occurs because the conversion of a '' to date will return 1/1/1900, and VB requries the parameter value to be a string (at least with this syntax), so System.DBNull.Value.ToString really equals ''.
I've rewritten the proc to accept the date as a string instead, and then for each date, cast it to a smalldatetime or set it to null as is appropriate. But this is a really bulky way to do things with more than a few dates! Is there any way to change what is in my VB code so that the procedure will insert the actual null value?
Thanks,
Sarah
View 2 Replies
View Related
Feb 23, 2005
Can someone post some code that shows a Stored Procedure receiving a cursor that it can process - lets say a group of order detail records are received that must be saved along with the single Order header record.
And, in another example, a SP returns a result set to the calling program. - For example, a particular sale receipt is pulled up on the screen and the order detail is needed.
Thanks for help on this,
Peter
View 14 Replies
View Related
Jul 23, 2005
Hi all..I'm trying to send multiple INT values to a Stored Procedure that willbe handled in an IN statement.ASP Code:strSQL = "SP_Get_Selections '29, 32'where 29 and 32 are 2 integer valuesNow, in my stored procedure... I would like to look these values up inan IN statement likeCREATE Procedure SY_GET_SELECTIONS@authorid varchar(20)SELECT * FROM Authors WHERE AuthorID IN (@authorid)But when I use this, I get [Microsoft][ODBC SQL Server Driver][SQLServer]Syntax error converting the varchar value '29, 32' to a columnof data type int.Any help would be great.Thanks
View 1 Replies
View Related
Nov 10, 2007
I have some long running stored procedures that I invoke from ADO.NET (using Typed Datasets).
Inside the stored procedure, I like to keep track of the execution by using the PRINT command.
Is there a way of extracting and displaying this PRINT information in .NET during the stored procedure execution?
View 4 Replies
View Related
Jun 28, 2004
if i send the string
2,3,4,5 to a stored procedure...
is there a way i could split those values for input into the database? no, right? i would need a seperate stored procedure that would take each value one at a time...correct?
View 7 Replies
View Related
Oct 24, 2007
hi there,
the subject line says it all. is there a way to send nicely formatted html email from within a stored procedure that returns a dataset?
thanks!
chris
View 2 Replies
View Related
Mar 12, 2008
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
View 29 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
May 16, 2008
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
View 6 Replies
View Related
Feb 15, 2012
I need to set date parameters within Stored Procedures using a sql 2008 R2, with an access 2007 front end. The procedure needs to allow me to set parameters for a start date and an end date.
View 1 Replies
View Related
Jul 20, 2005
Hi,I have a problem with updating a datetime column,When I try to change the Column from VB I get "Incorrect syntax near'942'" returned from [Microsoft][ODBC SQL Server Driver][SQL Server]'942' is the unique key column valueHowever if I update any other column the syntax is fineThe same blanket update query makes the changes no matter what isupdatedThe problem only happens when I set a unique key on the date field inquestionKey is a composite of an ID, and 2 date fieldsIf I allow duplicates in the index it all works perfectlyI am trying to trap 'Duplicate value in index' (which is working onother non-date columns in other tables)This is driving me nutsAny help would be appreciated
View 5 Replies
View Related
Oct 3, 2006
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
View 10 Replies
View Related
Jan 1, 2008
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.
View 3 Replies
View Related
Mar 31, 2005
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
View 1 Replies
View Related
Jun 29, 2007
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.
View 7 Replies
View Related
Jun 16, 2008
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.
View 8 Replies
View Related
Dec 6, 2006
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.
View 8 Replies
View Related
Jun 29, 2007
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.
View 3 Replies
View Related
Feb 14, 2008
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
View 5 Replies
View Related
Dec 9, 2006
Hi 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 ;)
View 4 Replies
View Related
Feb 4, 2007
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;
}
}
View 1 Replies
View Related
Mar 7, 2007
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
View 1 Replies
View Related
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
Aug 11, 2007
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
View 2 Replies
View Related
Aug 23, 2007
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
View 3 Replies
View Related
Jan 19, 2008
I want to know how to set parameters for Update Stored Procedure in SQLDataSource
View 5 Replies
View Related
Feb 11, 2008
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.
View 3 Replies
View Related