insert dbnull.value based on userinput

[Richnl]

I have a question
What do I do if I want to assign a dbnull.value to a column
For example:
The database has a column defined as decimal, where nullvalues are excepted
The storedprocedure has a param @Price as dec
I get dim price as string=pricetextbox.text from the user input
I control the userinput with keypress event

IfNot price= vbNullString Then
Dim x As Decimal = Decimal.Parse(price)
EndIf
cmd.Parameters.AddWithValue("@Price", IIf(price= vbNullString, 0, x))

How do I assign a dbnull.value if the user has not input a value?
I send a 0 value back, but somewhere it has to change to dbnull.value(uninizialized column)
I read c# has nullable types, but vb does not.
How is it handled in vb?

thanks in adv., Richard

 

[cjard]

When you make a TableAdapter that uses that sproc, a method stub is generated for you that will accept the passing of Nothing as the parameter you wish to set null..

myTableAdapter.MySProcName("abc", "def", 123, Nothing)

 

[Richnl]

wauw, I diddn't realise it was that simple to call a stored procedure

This MSDN explained some

Only, now it doesn't return the data in the grid.
It supposed to insert into two tables and only one tableadapter has the sproc,
so I cann guess where the problem is coming from, sortoff.(one table-> one tableadapter)
Also a foreignkeyconstraint problem is added, because I turned cascade on.
What is the aproach I should take?
In the sample, the calling of the stored procedure should take place on the second form(creating a new member),
but I put it on the main form

 

[cjard]

I've looked at your app, but it doesnt make much sense to me.

Your sproc wont fill a grid, because it runs insert queries, not select queries. You cannot make a sproc that runs insert queries, the target of a FillBY query and expect it to fill a grid.

We'd best start over by telling me what you hope to achieve.. Currently all we have is two tables, a relation, relevant sql queries for those and a Stored procedure that accepts a bulk of data and inserts it. The stored procedure is in the wrong place, because it is associated with a tableadapter that has nothing to do with its operation

 

[Richnl]

Ofcourse the sample and the data in itself mean nothing

Let's call the two imaginary tables "customers(parent) and customerPrefs(child)" for this purpose

The thing that I am try'ing to achieve is that I want to add (user presses newbutton on mainform and second form appears) a customer to the customers table (parent) and at the same time make an entry for that same customer in another table(child) that holds data about customer preferences and stuff like that, and put data in there also.(so both tables are on the secondform in textboxes)

I want to do that in the most efficient way as possible, the idea was to do that in one procedure(sproc).
Another way could be to split the operation in half and get the scope_identity value first and then do the dataset.table.newtablerow syntax and add a row to the customerPrefs table.

Obviously, I don't quit have it down how the key objects work together.
And what would be appropiate in terms off efficiency

Now, you have seen the sample combined with the summary what I am actually want to achieve
I think it makes it all clear, and you have seen all the things I should\ or should not have done

 

[cjard]

Ofcourse the sample and the data in itself mean nothing

Let's call the two imaginary tables "customers(parent) and customerPrefs(child)" for this purpose

The thing that I am try'ing to achieve is that I want to add (user presses newbutton on mainform and second form appears) a customer to the customers table (parent) and at the same time make an entry for that same customer in another table(child) that holds data about customer preferences and stuff like that, and put data in there also.

I want to do that in the most efficient way as possible, the idea was to do that in one procedure(sproc).
Another way could be to split the operation in half and get the scope_identity value first and then do the dataset.table.newtablerow syntax and add a row to the customerPrefs table.

Obviously, I don't quit have it down how the key objects work together.
And what would be appropiate in terms off efficiency

Now, you have seen the sample combined with the summary what I am actually want to achieve
I think it makes it all clear, and you have seen all the things I should\ or should not have done

OK, well the only problem I forsee with your current code in that case, is that, after you call the stored procedure , you attempt to fill only the Parent table..

When the dataset is enforcing constraints and the table adapter CLearBeforeFill = true, you cannot do this because a Fill will clear the parent table. Clearing the parent leaves orphaned children which is an error..

Your code should look like:

tabladapter.MiekNiewLid(..., ..., ..., ..., ...)
dataset.Clear()
parenttableadapter.fill(parenttable)
childatableadapter.fill(childtable)

 

[cjard]

note, typically when updating related data that is shown on screen, we would do two calls to the database.. its in the DW2 link, about Saving related data

 

[Richnl]

ok, then it is not a really good way to go about it, because now I and up filling the dataset tables after all.

Could I not better split it then without causing errors with nullables and stuff. And only have one insert stored procedure to get the scope_identity value off the parent table.

I added select scope_identity to the end off the sproc
And in the wizard I choose return single value
dim i as integer = tableAdapter.SprocName(..,..,..,)

for the second table I do,dataset.table.newtablerow=.....update(.. ( does this syntax also accept Nothing for nullables)
=IIF(textbox.text=vbnullstring ,Nothing, textbox.text)
ofcourse now I only acomplished that I have to do one instead of two fill

I should problably read your dw2 signature again
I have no time anymore, so I will do that later!

 

[cjard]

ok, then it is not a really good way to go about it, because now I and up filling the dataset tables after all.

Could I not better split it then without causing errors with nullables and stuff. And only have one insert stored procedure to get the scope_identity value off the parent table.

As far as i'm aware, you bind the tables up so that a local column is bound to the identity, the parent table is updated and a new row goes off with some identity it has been using locally, but this identity is ignored (changed) by the database. The change causes a local cascade update of the children, which are then themselves sent off to the database. I dont work with sql server, but this is the behaviour i see with oracle: I assign a temporary id to my tables locally, and it is overwritten with whatever the database puts. THe overwrite causes a local automatic update, and the children are inserted without problem using the new id..

I added select scope_identity to the end off the sproc
And in the wizard I choose return single value
dim i as integer = tableAdapter.SprocName(..,..,..,)

I actually think you provide an inputoutput parameter that the scope ID is placed into, and that causes the local temporary id to be overwritten when the call to update finishes

I should problably read your dw2 signature again
I have no time anymore, so I will do that later!

Yes, take a close read and see if it contains this info; as noted I dont work with SQLS and I only run a SQL Server on my machine to help out people here so I can load projects they send me