Use of Stored Procedures for Insert

Can someone give me some advice on using Stored Procedures for inserting data into a db. I have a .net form that has 8 fields. Of those fields 6 of them are linked to other db's for field population. When the form is submitted I have an On_Click call to a Stored Procedure to handle the insert.

My problem is from within the collection script and in particular the use of parameters to gather data. The data being posted is not from the form but some kind of default values. I get no errors on either the build or from the browser however the data is inaccurate.

I am new to this environment so bear with me if you please.

Here is the script for the stored procedure:


ALTER Procedure dbo.lief_sproc
(
@ddl_name nvarchar(50),
@ddl_wed nvarchar(50),
@ddl_lineitem1 nvarchar(50),
@ddl_Component1 nvarchar(50),
@ddl_Activity1 nvarchar(50),
@Hours1 nvarchar(50),
@Comments nvarchar(50)
)

AS

INSERT INTO LIEF_tbl
(
Emp,
Wed,
LineItem,
Component,
Activity,
Hours,
Comments
)
Values
(
@ddl_name,
@ddl_wed,
@ddl_lineItem1,
@ddl_component1,
@ddl_activity1,
@hours1,
@comments
)


Here is the script for calling the str_procedure and field gathering:
[color=sea-green]
<Script Language="VB" Runat="Server">

Public Sub liefOn_Click(ByVal Sender As Object, ByVal E As EventArgs)
Dim myConnection As New SqlConnection("server=ntiris;database=plan;uid=***;pwd=***;")
Dim myCommand As New SqlCommand("LIEF_Sproc",myConnection)
myCommand.CommandType=CommandType.StoredProcedure

Dim parameterddl_name As SqlParameter = New SqlParameter("@ddl_name", SqlDbType.int, 4)
parameterddl_name.Value = ParameterDirection.OUTPUT
myCommand.Parameters.Add(parameterddl_name)

Dim parameterddl_wed As SqlParameter = New SqlParameter("@ddl_wed", SqlDbType.nvarchar, 50)
parameterddl_wed.Value = ParameterDirection.OUTPUT
myCommand.Parameters.Add(parameterddl_wed)

Dim parameterddl_lineitem1 As SqlParameter = New SqlParameter("@ddl_lineitem1", SqlDbType.int, 4)
parameterddl_lineitem1.Value = ParameterDirection.OUTPUT
myCommand.Parameters.Add(parameterddl_lineitem1)

Dim parameterddl_component1 As SqlParameter = New SqlParameter("@ddl_component1", SqlDbType.int, 4)
parameterddl_component1.Value = ParameterDirection.OUTPUT
myCommand.Parameters.Add(parameterddl_component1)

Dim parameterddl_activity1 As SqlParameter = New SqlParameter("@ddl_activity1", SqlDbType.int, 4)
parameterddl_activity1.Value = ParameterDirection.OUTPUT
myCommand.Parameters.Add(parameterddl_activity1)

Dim parameterhours1 As SqlParameter = New SqlParameter("@hours1", SqlDbType.real, 4)
parameterhours1.Value = ParameterDirection.OUTPUT
myCommand.Parameters.Add(parameterhours1)

Dim parametercomments As SqlParameter = New SqlParameter("@comments", SqlDbType.nvarchar, 50)
parametercomments.Value = ParameterDirection.OUTPUT
myCommand.Parameters.Add(parametercomments)

MyCommand.Connection.Open()
MyCommand.ExecuteNonQuery()
MyCommand.Connection.Close
End Sub
</Script>
[/color]

I can probably guess that the problem lies in the mis-use of the parametersDirection.Ouput argument.

Any help would be appreciated.

Thank you.

Sincerely,

Tim

hey


you are doing somethign weird there...

for every param u have this

parameterddl_name.Value = ParameterDirection.OUTPUT

umm...it should be

parameterddl_name.Value = yourvaluevariable

so

int i =4
parameterddl_name.Value = i

you use ParameterDirection.Output when u want output from the sps

James,

You say I'm doing something wierd here? That is an extremely kind way of putting it and I appreciate your reserve in stating the obvious... that I don't know what I'm doing.

However, to my own avail I discovered the solution to my woes.

Apparently the code I had used was not retrieveing the values submited by the form.

Dim parameterddl_name As SqlParameter = New SqlParameter("@ddl_name", SqlDbType.nvarchar, 50)
parameterddl_name.Value = ddl_name.ParameterDirection.Output
myCommand.Parameters.Add(parameterddl_name)

Once I properly declared the correct syntax for extracting the data I was ok:

Dim parameterddl_name As SqlParameter = New SqlParameter("@ddl_name", SqlDbType.nvarchar, 50)
parameterddl_name.Value = ddl_name.SelectedItem.Value
myCommand.Parameters.Add(parameterddl_name)


Notice the revised statement that now reads SelectedItem.value.

I have a lot to learn with ADO.Net. Plain ADO used to be simple for me although I understand now the performance advantages to ADO.Net, it appears to be a bit more cumbersome to learn.

Thanks for your time in responding.

Sincerely,

Tim

lol

im sorry if that sounded taht way.. by no mean i meant to make you feel bad.

now you made me feel bad..

anyway backto the problem...

yes i must agree that ADO.NET has became more complex than ever but i think it's worth learning all its features..

neway, parameterddl_name.Value property is the property that takes in the value for the sp parameters so you would expect to have it in forms of string or numbers...even dates and boolean types are accepted ...but i didn't think that enumeration was accepted.. only reason the compiler didn't complain was cuz the parameter accepted a "object" object which is everything...

hope this helped...
if you need furthur help, im happy to help on this forum, email or icq..so let me know..

James,

By no means did you make me feel bad. I am humbled by my own limitations and even find humor in it at times.

I do have one additional question for you if you would allow me the pleasure of asking. Now that I have been able to successfully submit data to my db, I want to have a record preview on post back when the page refreshes.

Do you know how I can code to have the db return only those records that were currently entered using a uid filter?

For instance, if I wanted to db to return the current record against the Emp value of the form, how would the code look?

In the old days it used to be the recordset and querystring but I am still looking for the .net method.

Thank you again.

Sincerely,

Tim

are you talking about geting Unique Identity ID for the new record?

For the preview thing, what i would do is display the values in a label and then when user confirms it you store the data, instead of storing and then retriving the result to preview it.

James,

Yes. Let me explain my application. The application is a kind of project time tracking system. Each programmer is responsible for certain line items throughout the course of development for a specific application. This application will monitor and report the use of time for each line item.

So, the user goes into the app and see a form that has pull downs for all relevant fields dealing with selecting line items, action items, componenets etc. and specifies number of hours for each spent during a week.

What I want to do is now that the user has submitted the form, I want them to be able to view all the line items being entered for a specific week(current) as they are entering them. Currently, what I would like to do is have a preview pane just below the form that updates each time a user enters a new line item.

So the preview pane would be filtering data from the db based upon the weekending date, and the Emp_id.

Any thoughts?

Also,

In my form I have 6 drop downs that are currently populated by other tables. Because of this I have 6 SQL-Adapters. Is this right? Or is there a way to only have 1 SQL-Adapter to drive the 6 datasets? It didn't appear to be the case, I could get one to work for all.

Thank you again for your patience with me.

Sincerely,

Tim

Hey

6 Adapters and 6 DataSet will have too much overhead man...

here are some facts taht will make you a happy man.

- you can have a sp returning multiple tables (DataSet is collection of DataTables)
- you can use dataset to retrieve these multiple tables
- you only need one data adapter to run the sp


Now, from what i understand, the "line" is the value that a user has just entered...

There are two choices you have to make here. Do you want to store the data first and then retrieve them to preview and update changes ?? or do you want to preview the data first and make sure everythings right before data is stored??

1st one will be very complicated cuz you need 2 sps one updating and one inserting...and prolly one more if you want a cancel button..

2nd button is the one i would go for...so i wil explain how 2nd can be achieved

what you want is a web control to display the "previewing values"
and 2 buttons. 1 for submitting and and 1 for previewing. Previewing will just display the values to the "previewing values control" and submitting will actually store the data. That's basically it.. to make it a bit fancier, so say you wanna force previewing b4 data is stored, just make the submit buton invisible and make it visible when preview button is clicked..

For some reason i think this isn't what you want... so do you mind posting some code for it and/oor explain it in more detail??

hope this helpeed

Also one more thing

if its a asp.net prog..i recommend u using datareader...which also supports multiple tables...just it is forward only...

I find dataset too feature rich for web apps where performance is real key to success...

but for smart client winform apps dataset isthe way to go!!!

Holy S, if you could have seen me just then. That lightbulb in my head just blew up. You are awesome! Funny how in all 6 books I have, none of them ellude to the same fact you just gave me. I knew there had to be a better way to limit adapters and DataSets.

Ok, so here's what I'll do. I will recode to include a sp to handle the query of all tables used for the dropdowns. Then I will query the dataset for those values. Ok so far?

I want to stop here and get this going so I can start posting code that won't make you throw up.

Hang in there with me James!

Thanks again for your wisdom

Sincerely,

Tim

Im not going newhere man...
LOL
glad i could help

James,

Yes that is correct, this is a web application not a windows app. It will be running on IE 6.0+.

Do you think I should utilize DataReader instead of the SP route?

THanks again.

tim