why added datarow not showing in server explorer

[Richnl]

I was wondering why the new data is not showing when I do
rightclick -> show table data
also when I rightclick -> execute sql the whole newly added datarows
dissapaers.
Could someone explain this behaviour?

Thanks, Richard

 

[cjard]

Take a quick read of the DNU link in ym signature and ensure this is not the issue youre facing

 

[Richnl]

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 also set @param as smalldatetime = Null

I still can't get it to work
I get "Nullable object must have a value" error??

What 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?

Richard

 

[cjard]

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

Does your RDBMS allow a smalldatetime to be set to null?

I also set @param as smalldatetime = Null

I dont understand how this can be done. AFAIWA VB has no Null keyword

I still can't get it to work
I get "Nullable object must have a value" error??

Seeing the code that throws this error might help

What 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?

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 help

 

[Richnl]

thank you

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

Thats, all

Does your RDBMS allow a smalldatetime to be set to null?

I don't know anymore
In the tag off the editor it does say system nullable(of Date)
But how you can get the ?&%-* Null in there, I don't know

maybe a date always needs a value?

 

[cjard]

I cant reproduce any error, but the thing is, you dont have any way using the commands that the dataset generates for you, of allowing the default value to be used.. You dont get optional parameters with the DS generated methods, you HAVE to specify some value, even if the value given is Nothing

Here's what I did:

Made a stored procedure like yours except:

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

heres what the designer made:

        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

Heres how i called it:

   Dim x As New DBDataSetTableAdapters.QueriesTableAdapter
    Dim i as Integer = 0
    x.MakeNewJob(Nothing, Nothing, Nothing, Nothing, Nothing, [B]Nothing[/B], i)

When the code finished we had i = 1


The bold Nothing was sent to the proc as NULL. The proc didnt use the default (Nul) it was coded for. It cannot and never will

 

[cjard]

note that you make an output param, but your last line is a select, which does a return.. You maybe need to clarify the difference in your mind between an output param and a return value

 

[Richnl]

to clarify:

I don't use this
@DateReceived As smalldatetime = Null
BUT this is good
@DateReceived As smalldatetime

this is different:
Dim x As New DBDataSetTableAdapters.QueriesTableAdapter

I now have the SP added to the JobsTableAdapter
rightclick -> add querie

I 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?

Thanks so much

oh and,
The reason for the select was to return the auto-increment value
I changed it to Return Scope_Identity

 

[cjard]

to clarify:

I don't use this
@DateReceived As smalldatetime = Null
BUT this is good
@DateReceived As smalldatetime

I cant see this would matter, as noted the default value can never be used

this is different:
Dim x As New DBDataSetTableAdapters.QueriesTableAdapter

Not really; it's just a name

I now have the SP added to the JobsTableAdapter
rightclick -> add querie

It wouldnt matter in the slightest; it's just a name

I 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?

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 called

oh and,
The reason for the select was to return the auto-increment value
I changed it to Return Scope_Identity

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 it

 

[Richnl]

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
that was sort off overkill
I will read up on that next time I am going to involve myself again with SP's
I still need to read the other one also (post 2)
While 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.
I was ready to throw in the towel then.

Luckely, you gave the answer

But what is it that I actually learned then?
Is it the use of the New keyword in the statement that makes that the adapter allows Nothing as input?
That's the only thing left, otherwise I would still run into errors
If not, then it requires some more testing to find the issue here

 

[cjard]

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

An SP can have multiple output parameters, useful for "returning" more than 1 value. |The clue was in my code.

If we had an SP that had 3 output parameters and we called it like this:

Dim a,b,c As Integer
'a,b and c are all zero
MyTableAdapter.MyQuery(a, b, c)
'now a, b and c are set to some values

Whereas if it had just one return value we would call it like this:
Dim returned as Integer = MyTableAdapter.MyQuery()


See the difference?

Now, heres the kicker. Because the first way, you have to pass in a variable. .you cannot pass in a constant, because a constant cannot be set to anything:

MyTableAdapter.MyQuery(0, 0, 0) 'wont work!


Think about it.. how will you retrieve the values that the SP has output if you put constants in?

While 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.

I wouldnt take the opinion of the one in the face of the widespread use by the many. In lots of situations, SPs make sense

Is it the use of the New keyword in the statement that makes that the adapter allows Nothing as input?

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 value

See my code.. And ignore naming differences, they are irrelevant

 

[Richnl]

yes, that was it
variable versus constant

I treated the SP like I would treat a function returning a value
I get it