Best approach - databound controls & ADO.NET

[Michael]

Hi Everyone,
I'm currently using VS 2005 and would like to use the databinding of the
controls to help ease up some of the coding I have to do (some of my forms
have over 120 fields). The problem I find is that in the database we are
currently creating the primary key within the stored procedure like so:
Declare @LastValue int
Set @LastValue = (Select ScreeningId from Support)
set @LastValue = @LastValue + 1
Update Support
Set ScreeningId = @LastValue
/THe insert code would follow using LastValue as the primary key/

I found I had problems with inserting new records when I used databound
controls. It would through errors about invalid use as null for the primary
key. I was wondering if anyone else has run across this and how they solved
this problem, or if you found a better solution. The form I'm currently
working on has little over 150 fields and it has taken a while to code each
field for the Insert, Update and setting the data for the field. I hope
someone would have any suggestions. Thanks.
Michael

[Cor Ligthert [MVP]]

Michael,

I hope that the 120 fields are not in one database table. All wizards in
visual Studio do only work for tables with less than 101 fields.

Otherwise generating a strongly typed dataset in a windowform project will
give you all the commands you need. You can than port them to your SP with
copy and paste.

They are created in the dataset.designe r.vb

(If a table is more than 100 you can use only 100 and than add the one which
exceed those).

A command is named in the dataadapter by the commands as there are
updatecommand.c ommandtype = commandtype.sto redprocedure
updatecommand.c ommandtext = (name of your stored procedure)

http://msdn.microsoft.com/library/de...mmandtopic.asp

http://msdn.microsoft.com/library/de...mberstopic.asp

By the way in fact is there a special newsgroup for this kind of questions.

microsoft.publi c.dotnet.framew ork.adonet

I hope this helps,

Cor

[Michael]

Hi Cor,
thanks for the reply. I guess I'm in trouble then. The table I'm currently
working with has the data split into two table (I think I should break it
down more based on what you said about the 101 field limit). On of the tables
have 256 fields and the other has 186 fields. I'm creating a app to replace
medical type forms.
I'm currently using the command object with the stored procs and it works,
it just ALOT of typing (or copy paste). I did write a little app to create
the stored procs and the base Save/Update vb functions based on whats in the
database, but I still have alot to code.
I will try it again, but if I recall, When I did try the bound controls, I
did have it setup with the dataadapter/command objects and setup a datatable
with relations. Maybe that was what caused my problems, can't remember for
sure (been a few months ago). I'll take another look and see if I can get it
to work.
Thanks again for the reply and any suggestion.
Michael

"Cor Ligthert [MVP]" wrote:

Michael,

I hope that the 120 fields are not in one database table. All wizards in
visual Studio do only work for tables with less than 101 fields.

Otherwise generating a strongly typed dataset in a windowform project will
give you all the commands you need. You can than port them to your SP with
copy and paste.

They are created in the dataset.designe r.vb

(If a table is more than 100 you can use only 100 and than add the one which
exceed those).

A command is named in the dataadapter by the commands as there are
updatecommand.c ommandtype = commandtype.sto redprocedure
updatecommand.c ommandtext = (name of your stored procedure)

http://msdn.microsoft.com/library/de...mmandtopic.asp

http://msdn.microsoft.com/library/de...mberstopic.asp

By the way in fact is there a special newsgroup for this kind of questions.

microsoft.publi c.dotnet.framew ork.adonet

I hope this helps,

Cor

[Dennis]

I really feel sorry for the poor blokes who will have to fill out your form
or even read your form with 150 fields!
--
Dennis in Houston
"Michael" wrote:

Hi Cor,
thanks for the reply. I guess I'm in trouble then. The table I'm currently
working with has the data split into two table (I think I should break it
down more based on what you said about the 101 field limit). On of the tables
have 256 fields and the other has 186 fields. I'm creating a app to replace
medical type forms.
I'm currently using the command object with the stored procs and it works,
it just ALOT of typing (or copy paste). I did write a little app to create
the stored procs and the base Save/Update vb functions based on whats in the
database, but I still have alot to code.
I will try it again, but if I recall, When I did try the bound controls, I
did have it setup with the dataadapter/command objects and setup a datatable
with relations. Maybe that was what caused my problems, can't remember for
sure (been a few months ago). I'll take another look and see if I can get it
to work.
Thanks again for the reply and any suggestion.
Michael

"Cor Ligthert [MVP]" wrote:

Michael,

I hope that the 120 fields are not in one database table. All wizards in
visual Studio do only work for tables with less than 101 fields.

Otherwise generating a strongly typed dataset in a windowform project will
give you all the commands you need. You can than port them to your SP with
copy and paste.

They are created in the dataset.designe r.vb

(If a table is more than 100 you can use only 100 and than add the one which
exceed those).

A command is named in the dataadapter by the commands as there are
updatecommand.c ommandtype = commandtype.sto redprocedure
updatecommand.c ommandtext = (name of your stored procedure)

http://msdn.microsoft.com/library/de...mmandtopic.asp

http://msdn.microsoft.com/library/de...mberstopic.asp

By the way in fact is there a special newsgroup for this kind of questions.

microsoft.publi c.dotnet.framew ork.adonet

I hope this helps,

Cor

[Cor Ligthert [MVP]]

Michel,

100 columns limit. Less than 101.

Cor

[Michael]

Hi Cor,
Is there any limits to the SqlCommand object and the addparameters command.
I'm trying to debug a function that calls a stored proc and for some reason I
had one parameter in the proc that VB.NET errors out on. I've made sure that
the paramater type is correct. I've also made sure I have the correct number
of parameters. I get the following error message:
"Procedure 'intake_AddAdmi nNurseAssessmen t2' expects parameter
'@BleedingWho', which was not supplied."
Now, I can remove this parameter from the proc and my vb proc and the
function works just fine. But as soon as I add it back in, I'll get the same
error. Thanks for any info that you can provide.
Michael
"Cor Ligthert [MVP]" wrote:

Michel,

100 columns limit. Less than 101.

Cor

[Cor Ligthert [MVP]]

Michael,

I think that you just has to see in the SP "Procedure
'intake_AddAdmi nNurseAssessmen t2' what '@BleedingWho' is.

Be as well aware that parameternames in OleDB says nothing, there the
sequence of the parameter has to be the same as in the sql procedure or SP.

I hope this helps,

Cor