Inserting into a Database from a Dataset - Formula Help VB.Net Easy Question

I have an easy one.

I have a web method set-up that accepts a dataset from client.

 Dim myDA1 As SqlDataAdapter = New SqlDataAdapter

        myDA1.InsertCommand = New SqlCommand("Insert into Rec_Orders (order_Number,  qty, cost, value) Values (@order_Number,  @qty, @cost, (@cost * @qty))", myConn)

        myDA1.InsertCommand.Parameters.Add("@order_Number", SqlDbType.Int, 4, "order_Number")
        myDA1.InsertCommand.Parameters.Add("@qty", SqlDbType.Int, 9, "qty")
        myDA1.InsertCommand.Parameters.Add("@cost", SqlDbType.Int, 9, "cost")
     'right here ...I am talking about
        myDA1.InsertCommand.Parameters.Add("(@cost * @qty)", SqlDbType.Int, 9, "value")


        Dim param As SqlParameter = myDA1.InsertCommand.Parameters.Add("@Identity", SqlDbType.Int, 0, "id")
        param.Direction = ParameterDirection.Output

        myDA1.Update(ds1, "rec_Types")

My question is I am inserting into a table that has 4 fields.

OrderNumber
qty
cost
Value

The Value field is a field that is populated by     qty multipled by cost...ie (@qty * @cost)

Do I have this done right in my Insert Statement  and also have I got it right in the section where I declare my parameters.



Any insight  or help would be great.
nomar2Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
imperial_p79Connect With a Mentor Commented:
1. the formula in the insert statement is the right one
2. you dont have to set the parameter for the value field.
0
 
imperial_p79Commented:
I can see an extra comma in this piece of insert statement

 Values (@order_Number, ,

hope this is your problem!
0
 
Brian CroweConnect With a Mentor Database AdministratorCommented:
> myDA1.InsertCommand.Parameters.Add("(@cost * @qty)", SqlDbType.Int, 9, "value")

you don't need this parameter since @cost and @qty are already defined
0
 
nomar2Author Commented:
myDA1.InsertCommand = New SqlCommand("Insert into Rec_Orders (order_Number,  qty,   cost, value) Values (@order_Number,  @qty, @cost, (@cost * @qty))", myConn)


myDA1.InsertCommand.Parameters.Add("(@cost * @qty)", SqlDbType.Int, 9, "value")

Is the above the right way to handle...

1. the formula in the insert statement
2. setting the parameter for the variable that will fill the VALUE field
0
 
nomar2Author Commented:
Excellent...so I just need this

  myDA1.InsertCommand = New SqlCommand("Insert into Rec_Orders (order_Number,  qty, cost, value) Values (@order_Number,  @qty, @cost, (@cost * @qty))", myConn)

        myDA1.InsertCommand.Parameters.Add("@order_Number", SqlDbType.Int, 4, "order_Number")
        myDA1.InsertCommand.Parameters.Add("@qty", SqlDbType.Int, 9, "qty")
        myDA1.InsertCommand.Parameters.Add("@cost", SqlDbType.Int, 9, "cost")

...rest of the code

Thanks for the quick turnaround
0
All Courses

From novice to tech pro — start learning today.