Sending Null DateTime Value To Stored Procedure From VB
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
ADVERTISEMENT
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
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
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
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
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 6, 2007
Hello. A question please. How can I send a null value from vb.net to a datetime field of Sqlserver ?, This fields allows null values.
Thanks...
View 1 Replies
View Related
Jul 20, 2005
Hi,I have write a stored procedure which makes update in a numeric (int) field.Depending on data in other table, in some case the result of query get aNull value instead a zero value...How can I tell to Update query to NOT update field if the value is Null ?I hope my word clear...here the stored procedure:UPDATE dbo.ANAUTENTISET dist1punti = dist1punti +(SELECT SUM(TEMPIMPORTAZIONEDIST1.qnt * ANAARTICOLI.punti) AS totalepuntiFROM TEMPIMPORTAZIONEDIST1 INNER JOINANAARTICOLI ON TEMPIMPORTAZIONEDIST1.codicearticolo =ANAARTICOLI.codartdist1WHERE (TEMPIMPORTAZIONEDIST1.piva = ANAUTENTI.piva))WHERE (piva IN(SELECT pivaFROM TEMPIMPORTAZIONEDIST1GROUP BY piva))Thanks in advancePieroItaly
View 2 Replies
View Related
Apr 15, 2004
Hi;
I have a stored procedure simply adds userid,logintime and status to db but I have an error when running procedure can you help me please??
Create Procedure AddLog
(
@User char(10),
@DLogon DateTime(8),
@Status bit
)
As
Insert Into Log(UserID,LogInTime,Online)
Values(@User,DLogon,Status)
ERROR:
Server: Msg 128, Level 15, State 1, Procedure AddLog, Line 9
The name 'DLogon' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
View 1 Replies
View Related
Mar 14, 2008
Hi,
Can i compare date = null in stored procedure? Does this work? The syntax works but it never get into my if else statement? The weird thing here is it has been working and just this morning, it stopped. I don't know why? Could you please help? Thanks,
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[UpdateActiveStates1]
@PROVIDERID INT
AS
DECLARE @STARTDATE DATETIME
DECLARE @ENDDATE DATETIME
DECLARE @ACTIVE BIT
SET @ACTIVE = 0
SET @STARTDATE = (SELECT ProhibitionStartDate FROM Providers WHERE ProviderID=@PROVIDERID)
SET @ENDDATE = (SELECT ProhibitionEndDate FROM Providers WHERE ProviderID=@PROVIDERID)
IF ((@STARTDATE = NULL OR @STARTDATE = '1753-01-01') AND (@ENDDATE = NULL OR @ENDDATE = '1753-01-01'))
BEGIN
PRINT 'Setting Inactive due to NULL Start Date and NULL End Date'
SET @ACTIVE=0
END
ELSE IF (@STARTDATE != NULL AND @STARTDATE <= GETDATE())
BEGIN
IF (@ENDDATE = NULL)
BEGIN
PRINT 'Setting Active due to NON-NULL Start Date and NULL End Date'
SET @ACTIVE=1
END
ELSE IF (@ENDDATE >= GETDATE())
BEGIN
PRINT 'Setting Active due to NON-NULL Start Date and NON-EXPIRED End Date'
SET @ACTIVE=1
END
ELSE
BEGIN
PRINT 'Setting Inactive due to NON-NULL Start Date and EXPIRED End Date'
SET @ACTIVE=0
END
END
UPDATE Providers SET Active=@ACTIVE
WHERE ProviderID=@PROVIDERID
UPDATE Actions SET Active=@ACTIVE WHERE ProviderID=@PROVIDERID
View 2 Replies
View Related
Jan 3, 2008
Hello,
I have got an SQL server stored procedure, and I would like to get this stored procedure, dbo.SpDate_Time_Minute_Today below executed against a matching or exact datetime from the database tables, PRODUCT_SALES.
Code Block
CREATE PROCEDURE dbo.SpDate_Time_Minute_Today
AS
SELECT PROD_SAL_DESC FROM PRODUCT_SALES
WHERE (CONVERT(CHAR(11), PROD_SAL_BY_DATE, 103) + ' ' + SUBSTRING(CONVERT(CHAR(17), PROD_SAL_BY_DATE, 100), 13, 19) = CONVERT(CHAR(11), CURRENT_TIMESTAMP, 103)
+ ' ' + SUBSTRING(CONVERT(CHAR(17), CURRENT_TIMESTAMP, 100), 13, 19))
/* SET NOCOUNT ON */
RETURN
PRODUCT_SALES has columns like this:
PROD_SAL_NO
PROD_SAL_BY_DATE
PROD_SAL_DESC
PROD_SAL_MFR
PROD_SAL_DEPT
Please do anyone here know how I can achieve this please. Thanks.
View 6 Replies
View Related
May 16, 2008
When I run the following code I get error "Incorrect syntax near 'MyStoredProcedureName".
Code Snippet
public static string GetWithDate(string date)
{
string connString = System.Configuration.ConfigurationManager.ConnectionStrings["Development"].ToString();
SqlConnection conn = new SqlConnection(connString);
conn.Open();
XmlDocument xmlDoc = new XmlDocument();
SqlCommand cmd = new SqlCommand("usp_SVDO_CNTRL_GetPalletChildWorkExceptions", conn); //sw.WriteLine(count++);
cmd.Parameters.Add(new SqlParameter("@date", date));
try
{
cmd.ExecuteReader();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
SqlDataReader rdr = cmd.ExecuteReader(); //<---Bombs
if (conn != null)
conn.Close();
return xmlDoc.InnerXml;
}
I'm assuming this is because my Date is in the wrong format when .NET passes it. I've tested the stored procedure directly in SQL Server Managent Studio and it works (Format of date is '5/15/2008 9:16:23 PM').
View 3 Replies
View Related
Mar 27, 2008
Hello.
I have a report that can get null as a value for one of his parameters.
I'm running this report in a reportViewer (WinForm application).
How can I send this report the value NULL?
I tried using:
Microsoft.reporting.winforms.reportParameter("p_param",null)
But I got an error that the call is ambiguous becouse it have two overload:
1) Microsoft.reporting.winforms.reportParameter(string,string)
2) Microsoft.reporting.winforms.reportParameter(string,string[])
So how can I send the value NULL?
Thanks.
View 1 Replies
View Related
Mar 27, 2008
Hello.
I have a report that can get null as a value for one of his parameters.
I'm running this report in a reportViewer (WinForm application).
How can I send this report the value NULL?
I tried using:
Microsoft.reporting.winforms.reportParameter("p_param",null)
But I got an error that the call is ambiguous becouse it have two overload:
1) Microsoft.reporting.winforms.reportParameter(string,string)
2) Microsoft.reporting.winforms.reportParameter(string,string[])
So how can I send the value NULL?
Thanks.
View 1 Replies
View Related
Oct 24, 2006
Can anyone tell me why my outstring is always null with this table structure:1 This is my sql table structure
2
3 FK = ID int,
4 Empnaam varchar(200),
5 PK = EmpID int
and with this stored procedure: ALTER PROC [dbo].[ShowHierarchy] @Root int,@outstring VARCHAR(8000) OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE @EmpID int, @EmpNaam varchar(30)
SET @EmpNaam = (SELECT EmpNaam FROM dbo.Emp WHERE EmpID = @Root)
SET @outstring= @outstring+REPLICATE('-', @@NESTLEVEL * 4) + @EmpNaam +'
'
SET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE ID = @Root)
WHILE @EmpID IS NOT NULL BEGIN
EXEC dbo.ShowHierarchy @EmpID,@outstring OUTPUT
SET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE ID = @Root AND EmpID > @EmpID)
END
END I use this code in visual studio:this is my function :1
2 public static List<string> viewtree(int Root)
3 {
4 SqlConnection con = new SqlConnection(mainConnectionString);
5 con.Open();
6 try
7 {
8 List<string> ids = new List<string>();
9 SqlCommand command = new SqlCommand(@"ShowTree", con);
10 command.Parameters.AddWithValue("@Root", Root);
11 command.Parameters.Add(new SqlParameter("@outstring", SqlDbType.VarChar, 8000));
12 command.Parameters["@outstring"].Direction = ParameterDirection.Output;
13 command.CommandType = CommandType.StoredProcedure;
14 SqlDataReader dr = command.ExecuteReader();
15 command.Parameters.Clear();
16 while (dr.Read())
17 {
18
19 ids.Add(dr["@outstring"].ToString());
20
21 }
22
23
24 return ids;
25 }
26 finally
27 {
28 con.Close();
29 }
30 }
It would be awesome if someone could test this and help me out....Thanks in advance
View 28 Replies
View Related
Dec 16, 2013
I'm trying to write a stored procedure for a search criteria. A user will have few controls on ASP.Net webpage to enter/select the search criteria, based on which , it returns the data.
A Textbox( to enter Username), A Textbox (to enter Employee name),
A DropDownList (To select the profile. Its Id is sent as parameter) &
A DropDownList (To select status). This is where I am stuck.
The last dropdown has 3 list items
1. --All-- value is null/empty
2. Active value is 0
3. Blocked value is 1
Though the Database table will have only 0's and 1's for the status column, the user should be able to see all records when -all-- is selected.I also tried excluding the status parameter and its condition, but no luck either way.
View 3 Replies
View Related
Jan 18, 2007
hi this is my stored procedure.i am passing mu column nam and recordname has to be fetched.if run this proceedure i am getting null records only.but i am having records in my table
CREATE PROCEDURE HRUser_spsearch
(
@columnname varchar(50),
@recordname varchar(50)
)
As
if(@columnname !=' ' and @recordname !=' ')
begin
select userid,user_name,password,role_code,expiry_date from usermaster where '+@columnname+' like '+@recordname+"%"'
end
GO
can any one help to solve this please
View 5 Replies
View Related
Dec 14, 2006
How can I format a datetime field in a stored procedure to return just the date in MM/DD/YYYY format?
View 13 Replies
View Related
May 23, 2007
Hello everyone I have a stored procedure that I am trying to save datetime data to at the moment I can create a string that will output 25/05/2007 18:30 Does anyone know the best way to get this into the correct format for my SP parameter
cmdPublish.Parameters.Add(new SqlParameter("@datPublishDate", System.Data.SqlDbType.DateTime));
cmdPublish.Parameters["@datPublishDate"].Value = ????
Thanks
View 3 Replies
View Related
Jan 18, 2008
hello,
I have a stored procedure being called from my class.
All values are ok, except for the DateTime value. What am i doing wrong here ? Am wondering if its the date size of 50 I put there or what ?
command.Parameters.Add(new SqlParameter("@dob", SqlDbType.DateTime,50, dob1));
Error Message
Compiler ErrorMessage: CS1502: The best overloaded method match for 'System.Data.SqlClient.SqlParameter.SqlParameter(string, System.Data.SqlDbType, int, string)' has some invalid arguments
thanks
Ehi
View 3 Replies
View Related
Aug 19, 2004
I have a stored procedure called from ASP code, and when it is executed, the stored procedure takes in a date as an attribute.
But since the stored procedure call is really just a string in the code, it is taking in the value as a string. So in my stored procedure, I want to convert a string value to a date value.
Any idea how this is done?
View 1 Replies
View Related
Sep 9, 2007
Ok, i am using the convert function to get the date format from my datetime column. My problem is when C# try's to pull the column name the column name is not sent in the query. When I run the query in the query analyzer the column name is blank in the result window. How can I get the date from the record with out losing the name of my column in the query. My data binding needs the name of the column to bind the data to the drop down list control. Here is the statement:
SQL statement
SELECT DISTINCT convert(datetime, eventDT, 110) FROM tblRecognition
C# code
ddlDateTo.DataSource = _uiCode.Fill.Date();
ddlDateTo.DataTextField = "eventDT";
ddlDateTo.DataBind();
Thank you,
View 2 Replies
View Related
Feb 5, 2004
Hi I'm new to MS SQL and trying to write a very small dynamic stored procedure which is giving me a headache.
What I have is:
CREATE PROCEDURE busy_report
@TableName varchar(255),
@reporteddate datetime=NULL
AS
if @reporteddate is null
select @reporteddate = CURRENT_TIMESTAMP
-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(255)
SET DATEFORMAT dmy
-- Enter the dynamic SQL statement into the
-- variable @SQLStatement
SELECT @SQLStatement = "SELECT vendor, reporteddate, count(vendor) FROM " +
@TableName + "WHERE reporteddate = ' "
+ @reporteddate + " '"
-- Execute the SQL statement
EXEC(@SQLStatement)
GO
The error I keep getting is:
Server: Msg 8114, Level 16, State 4, Procedure busy_report, Line 0
Error converting data type varchar to datetime.
Any ideas appreciated.
(Edit:)
I've also tried it this way:
CREATE PROCEDURE UK_busy_report
@TableName varchar(255),
@reporteddate datetime=NULL
AS
-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(255)
SELECT @reporteddate=CONVERT(datetime, @reporteddate)
IF @@ERROR <> 0 BEGIN
/* Do some error processing */
PRINT 'Error Occured' END
ELSE
-- Enter the dynamic SQL statement into the
-- variable @SQLStatement
SELECT @SQLStatement = "SELECT vendor, reporteddate, count(vendor) FROM " +
@TableName + "WHERE reporteddate = ' "
+ @reporteddate + " '"
-- Execute the SQL statement
EXEC(@SQLStatement)
GO
Which gives me the same error!
.logic.
View 5 Replies
View Related
Nov 23, 2005
Hi,When I pass a date time parameter the stored procedure takes about 45seconds, when I hard code the parameter it returns in 1 second. How canI rewrite my stored procedure?@createddatelower datetimeWHERE dbo.tblCaseHistory.eventdate > dateadd(d,-7,@createddatelower )AND dbo.tblCaseHistory.eventdate < dateadd(d,-6,@createddatelower ) (45seconds)vs.WHERE dbo.tblCaseHistory.eventdate > dateadd(d,-7,'11/15/05') ANDdbo.tblCaseHistory.eventdate < dateadd(d,-6,'11/15/05') (1 second)thanks for your help,Paul
View 5 Replies
View Related
Nov 19, 2006
Hi,
I'm having a problem with inserting a datetime value into a database using VB.net and a Stored Procedure. Below is my stored procedure code and VB.net code. Could somebody please tell me what I am doing wrong ... I am almost frustrated to tears .
Stored procedure:
ALTER PROCEDURE dbo.SPTest
@testvalue DATETIME
AS
INSERT INTO tbl_Rates VALUES (1.2, 1.3, @testvalue, 'EUR/USD')
RETURN 1
VB.NET code:
Dim RatesTA As New RatesDataSetTableAdapters.RatesTableAdapter
Dim ReturnVal As Object
ReturnVal = RatesTA.SPTest(Now)
Console.WriteLine(CType(ReturnVal, Integer))
When I run this the ReturnVal is 0.
I should also mention that my system uses the dd/mm/yyyy date format (Australian) and I am using VB.NET Express and SQL Server Express.
View 1 Replies
View Related
Apr 26, 2008
I want to store datetime data in data table.
I am facing problem in using datetime data value in insert statement passed via stored procedure.
Any Help !Thanks in advance
View 3 Replies
View Related
Aug 25, 2006
Hi,I have a stored procedure which expects a Null value if a user does not enter something into a TextBox. The query should return all rows if this case.The stored procedure works as expected in Query Analyzer, but my application does not seem to send a Null and no rows are returned. Any ideas?Thanks.ps I am using SqlDataSource and GridView, this is the first time I have tried to connect to a database with asp.net.
View 3 Replies
View Related
