Does your RDBMS allow a smalldatetime to be set to null?thanks, I will read that
I still have a question about inserting a dbnull value
The sproc has a param as smalldatetime
I have put the sproc in the tableadapter, and
in the procedure I type Nothing for the param
I dont understand how this can be done. AFAIWA VB has no Null keywordI also set @param as smalldatetime = Null
Seeing the code that throws this error might helpI still can't get it to work
I get "Nullable object must have a value" error??
I dont quite know what youre asking. Seeing the SProc code, and the related vb code (all of it, including that which the dataset designer created for you) might helpWhat is the correct way to do this
How can I get the @param to take the default value when I call the SP from a procedure?
ALTER PROCEDURE dbo.MakeNewJob
@ID as int,
@someDate as smalldatetime,
@someMoney As Dec,
@ID2 As int,
@someMoney2 As dec,
@DateReceived As smalldatetime = Null,
@NewJobID as int output
As
begin
Insert Into Jobs (ID, someDate ,someMoney)
Values (@ID,@someDate , @someMoney)
set NewEntryPoint =SCOPE_IDENTITY()
end
if @DateReceived is Null
begin
Insert Into PayOff (ID,JobID,someMoney)
Values (@ID2,@NewJobID ,@someMoney)
select SCOPE_IDENTITY()
end
else
begin
Insert Into PayOff (ID,JobID,someMoney,DateReceived)
Values (@ID2,@NewJobID ,@someMoney,@DateReceived)
select SCOPE_IDENTITY()
end
sub CallingSP
'// a, b, etc represent textbox values
'// Nothing is for the DateReceived param to indicate that I want to put "nothing" in the database(Null, no value)
'// the last param is the output param value (if it asks for a param, give it a param)
Dim i As Integer = Me.daJobs.MakeNewJob(a, b, c, d, e, Nothing, 1)
end sub
I don't know anymoreDoes your RDBMS allow a smalldatetime to be set to null?
ALTER PROCEDURE dbo.MakeNewJob
@ID as int,
@someDate as smalldatetime,
@someMoney As Dec,
@ID2 As int,
@someMoney2 As dec,
@DateReceived As smalldatetime = Null,
@NewJobID as int output
As
begin
set @NewJobID = 1;
end
Public Overloads Overridable Function MakeNewJob(ByVal ID As System.Nullable(Of Integer), ByVal someDate As System.Nullable(Of Date), ByVal someMoney As System.Nullable(Of Decimal), ByVal ID2 As System.Nullable(Of Integer), ByVal someMoney2 As System.Nullable(Of Decimal), ByVal DateReceived As System.Nullable(Of Date), ByRef NewJobID As System.Nullable(Of Integer)) As Integer
Dim command As System.Data.SqlClient.SqlCommand = CType(Me.CommandCollection(0),System.Data.SqlClient.SqlCommand)
If (ID.HasValue = true) Then
command.Parameters(1).Value = CType(ID.Value,Integer)
Else
command.Parameters(1).Value = System.DBNull.Value
End If
If (someDate.HasValue = true) Then
command.Parameters(2).Value = CType(someDate.Value,Date)
Else
command.Parameters(2).Value = System.DBNull.Value
End If
If (someMoney.HasValue = true) Then
command.Parameters(3).Value = CType(someMoney.Value,Decimal)
Else
command.Parameters(3).Value = System.DBNull.Value
End If
If (ID2.HasValue = true) Then
command.Parameters(4).Value = CType(ID2.Value,Integer)
Else
command.Parameters(4).Value = System.DBNull.Value
End If
If (someMoney2.HasValue = true) Then
command.Parameters(5).Value = CType(someMoney2.Value,Decimal)
Else
command.Parameters(5).Value = System.DBNull.Value
End If
If (DateReceived.HasValue = true) Then
command.Parameters(6).Value = CType(DateReceived.Value,Date)
Else
command.Parameters(6).Value = System.DBNull.Value
End If
If (NewJobID.HasValue = true) Then
command.Parameters(7).Value = CType(NewJobID.Value,Integer)
Else
command.Parameters(7).Value = System.DBNull.Value
End If
Dim previousConnectionState As System.Data.ConnectionState = command.Connection.State
If ((command.Connection.State And System.Data.ConnectionState.Open) _
<> System.Data.ConnectionState.Open) Then
command.Connection.Open
End If
Dim returnValue As Integer
Try
returnValue = command.ExecuteNonQuery
Finally
If (previousConnectionState = System.Data.ConnectionState.Closed) Then
command.Connection.Close
End If
End Try
If ((command.Parameters(7).Value Is Nothing) _
OrElse (command.Parameters(7).Value.GetType Is GetType(System.DBNull))) Then
NewJobID = New System.Nullable(Of Integer)
Else
NewJobID = New System.Nullable(Of Integer)(CType(command.Parameters(7).Value,Integer))
End If
Return returnValue
End Function
Dim x As New DBDataSetTableAdapters.QueriesTableAdapter
Dim i as Integer = 0
x.MakeNewJob(Nothing, Nothing, Nothing, Nothing, Nothing, [B]Nothing[/B], i)
I cant see this would matter, as noted the default value can never be usedto clarify:
I don't use this
@DateReceived As smalldatetime = Null
BUT this is good
@DateReceived As smalldatetime
Not really; it's just a namethis is different:
Dim x As New DBDataSetTableAdapters.QueriesTableAdapter
It wouldnt matter in the slightest; it's just a nameI now have the SP added to the JobsTableAdapter
rightclick -> add querie
I only created that because I added your SP to one of my projects and there was no other tableadapter. It doesnt matter what your tableadapter is calledI changed it by:
rightclick -> add querie in the designer so that it will create a QueriesTableAdapter
I am going to use this to store all my SP's in?
Yep, okay but still, it sounds like you dont know the difference between an out param and a return value. Have a read up on itoh and,
The reason for the select was to return the auto-increment value
I changed it to Return Scope_Identity
I was coming here to edit my response
no, I don't yet now exactly the difference
but I know I diddn't need the return OR select
I wouldnt take the opinion of the one in the face of the widespread use by the many. In lots of situations, SPs make senseWhile searching for answers I also came across some programmers comment about stored procedures and preaching how people should never, never, ever use them and should be avoided like the plague.
Probably not. New just creates an instance. It is more, likely your error was caused by trying to pass in a constant to an output parameter, not knowing the difference between and OUT and a return valueIs it the use of the New keyword in the statement that makes that the adapter allows Nothing as input?