We help IT Professionals succeed at work.

Double insert

QPR
QPR asked
on
331 Views
Last Modified: 2012-05-05
Hi,
I have an aspx web page (asp 2.0) that consists of 2 databound drop downs, 3 txt boxes, 2 calendar controls and a button.
What I am trying to achieve is to have these form values inserted into my DB when the user clicks the button (as an aside should this SQL/ADO be in the page source or the code behind as a sub onClick??)
I'm hoping for the VB page so it is cleaner.

Most of what I have done in asp.net/vs2005 has been wizard/smart tag related and now I'm paying the price :)

I need to grab the form values and put them into an insert statement. Actually it will be 2 insert statements.
dropdownlist1 value and txtBox1 go into tableA
all the others (as well as dropdownlist1 value) go into tableB.

Should it be 2 insert statements or can you combine 2 inserts in the one command?
What I am unsure of is all the surrounding code import this, sqlcommand, sqlparameter, try/catch.


Comment
Watch Question

QPR

Author

Commented:
ok I'll be more specific.

I have 2 tables
Table A
Field 1 Field 2 Field 3
Table B
Field 1 Field 2 Field 3 Field 4

1 aspx form
dropdown1 dropdown2 calendar1 calendar2 txt1
btnSubmit onClick

Insert into tableA (Field2, Field3) values (dropdown1.value, calendar1value)
insert into TableB (Field1,Field2, Field3, Field4) values (***see below, dropdown2.value, calendar2.value,txt1)

what is the syntax for the sub as a whole.

***see below.
Field1 in TableB (FK) needs to the value of Field1 in TableA following the insert. I think this is retrieved using @@identity but I'm not sure how to retrieve it and then use it for the 2nd insert.
Ramesh SrinivasTechnical Consultant
CERTIFIED EXPERT

Commented:
Something like this i think....

Dim cmd as sqlcommand
Dim strSQL = "Insert into tableA (Field2, Field3) values (" & dropdown1.value & ", " & calendar1.value & ")"

cmd = new sqlcommand(strSQL, <connection object>)
cmd.executeNonQuery()

cmd = new sqlcommand("Select @@Identity", <connection object>)
Dim theID = cmd.executeScalar()

strSQL = "Insert into tableA (Field1, Field2, Field3) values (" & theID & ", " & calendar2.value & ", & txt1 & ")"
cmd = new sqlcommand(strSQL, <connection object>)
cmd.executeNonQuery()

cmd.dispose()
Ramesh SrinivasTechnical Consultant
CERTIFIED EXPERT

Commented:
I suppose you could join the first and second command together like so:

Dim cmd as sqlcommand
Dim strSQL = "Insert into tableA (Field2, Field3) values (" & dropdown1.value & ", " & calendar1.value & ") Select @@Identity "

cmd = new sqlcommand(strSQL, <connection object>)
Dim theID = cmd.executeScalar()

strSQL = "Insert into tableA (Field1, Field2, Field3) values (" & theID & ", " & calendar2.value & ", & txt1 & ")"
cmd = new sqlcommand(strSQL, <connection object>)
cmd.executeNonQuery()

cmd.dispose()
QPR

Author

Commented:
Thanks Saleek,
I've decided to create an SP and execute that instead.
So all I need do is stuff vars with form values and pass them to the SP.

So what I'll need is the syntax for the sub e.g. the command bit.
My connection string (in web.config) is called CommIndexConnectionString
The SP is called InsertNewItems
QPR

Author

Commented:
More info!
here are my vars filled and my exec statement....
All I need now is the ADO.NET syntax wrapped around it... please?

Protected Sub btnAddItem_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAddItem.Click
        Dim commID = lstCommittee.SelectedValue
        Dim meetDate = calMeetDate.SelectedDate
        Dim itemDetail = txtItemDetail.Text
        Dim resolution = txtResolution.Text
        Dim DateRequired = calDateReq.SelectedDate
        Dim docPath = txtWordDoc.Text
        'exec InputNewItems commID,meetDate,itemDetail,resolution,DateRequired,DocPath
    End Sub
Ramesh SrinivasTechnical Consultant
CERTIFIED EXPERT

Commented:
Private myConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("CommIndexConnectionString"))
Dim cmd as sqlcommand
with Cmd
  .connection = <connection>
  .commandtype = commandtype.storedprocedure
  .commandtext = "<NameOfStoredProcedure>"
  .parameters.add("<paramName>", <paramValue>) <-------------repeat this for all parameters
  ...............
  ...............
End With

myConn.Open()
cmd.executeNonQuery()
myConn.Close
cmd.dispose()

<paramName> is what you called the parameter inside your storedprocedure.
<paramValue> is the value you want to pass to the stored procedure.

that should be it.

Technical Consultant
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Ramesh SrinivasTechnical Consultant
CERTIFIED EXPERT

Commented:
Sorry, there should be a "New" keyword in the constructor.

Dim cmd as sqlcommand = new sqlcommand
QPR

Author

Commented:
Excellent thanks - I'm getting squigglys under sqlconnection and sqlcommand - should there be a namespace imported?
QPR

Author

Commented:
ok I had to add a namespace (sqclient.sqlconnection)
Had to change a few other things - apparently
parameters.add is oboslete and is now parameters.addwithvalue
configurationSettings.AppSettings is now System.Configuration.ConfigurationManager.AppSettings

All fine now except 1 thing.
 .commandtype = commandtype.storedprocedure
                          ^^^^^^^^^
commantype is not declared
Ramesh SrinivasTechnical Consultant
CERTIFIED EXPERT

Commented:
Are you using asp.net 2.0 ?
Ramesh SrinivasTechnical Consultant
CERTIFIED EXPERT

Commented:
If you are using 2.0 then I dont know! Havent used it as yet.

Anyway, the only namespace you would need is:

Imports System.Data.SqlClient


Unless that has changed too :O
QPR

Author

Commented:
Yes 2.0
I had to specifically add... Imports System.Data also.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.