ms sql insert with select and then update in the same command

Im creating a VB.NET application with a MS SQL database. I am trying to copy an undetermined number of rows to the same table and whilst at the same time change one value.
My code works perfectly as long as i dont try to set a manual value for one of the columns. If i try to do this i get an error saying the select part of the command must have the same amount of columns as the insert part. Is there a way to avoid this?
Possibly by updating each row as soon as its added? baring in mind this statement will usually copy between 2-5 rows.


            'copy core ff Previous Address history to new sale
            Dim objCommandCopyPA As SqlCommand = New SqlCommand()
            objCommandCopyPA.Connection = objConnection
            objCommandCopyPA.CommandText = "INSERT INTO previous_address (pa_sale_id, pa_client_id," & _
            "pa_address1, pa_address2, pa_town, pa_postcode, pa_years, pa_months, pa_start, pa_end) " & _
          "SELECT '" & IDone & "',pa_client_id, pa_address1, pa_address2, pa_town, pa_postcode," & _
          "pa_years, pa_months, pa_start, pa_end " & _
          "FROM previous_address WHERE (pa_sale_id = 0) AND (pa_client_id = '" & app1_id & "' OR " & _
          "pa_client_id = '" & app2_id & "'); " & _
          "Select Scope_Identity()"
            Dim cPAID As Integer

            Try
                cPAID = objCommandCopyPA.ExecuteScalar()
            Catch SqlExceptionErr As Exception
                MessageBox.Show(SqlExceptionErr.Message)
            End Try
drews1fAsked:
Who is Participating?
 
rob_farleyConnect With a Mentor Commented:
Ok... you're missing a comma in the SELECT statement:

debt_client_id2 debt_client_id3

Rob
0
 
rob_farleyCommented:
Are you trying to insert a value into the Identity column?

Also, watch out for SQL Injection issues... for more on this, check out http://xkcd.com/327

Rob
0
 
drews1fAuthor Commented:
no its not an identity column :(
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
rob_farleyCommented:
What's a "manual value"?

Are you having trouble with values that have apostrophes in them?

Rob
0
 
drews1fAuthor Commented:
nope the manual value is a variable which is defined earlier in the form.
If i change the statement to set the pa_sale_id value from the select statement it works fine.
however when i remove pa_sale_id and replace it with '" & IDone & "'

i get the error saying the select and insert statements need to have the same amount of columns :S
0
 
rob_farleyCommented:
So what is IDone set to?
0
 
drews1fAuthor Commented:
an integer. in this particular case it was

74
0
 
rob_farleyCommented:
Put a breakpoint in, grab the command, and run it in SSMS. That might give you some more clues.

Rob
0
 
drews1fAuthor Commented:
error from SSMS:

Msg 120, Level 15, State 1, Line 1
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.
0
 
drews1fAuthor Commented:
command used:

INSERT INTO debt (debt_sale_id, debt_client_id_type,
debt_client_id1, debt_client_id2, debt_client_id3, debt_client_id4, debt_type, debt_company,
debt_add, debt_ceasing, debt_balance, debt_cleared_monthly, debt_repayments, debt_ends,
debt_prop_value, debt_rental, debt_no, debt_prop_type, debt_tenancies, debt_tenancy,
debt_prop_address1, debt_prop_address2, debt_prop_town, debt_prop_postcode)
SELECT '77',debt_client_id_type, debt_client_id1, debt_client_id2 debt_client_id3,
debt_client_id4, debt_type, debt_company, debt_add, debt_ceasing, debt_balance, debt_cleared_monthly,
debt_repayments, debt_ends, debt_prop_value, debt_rental, debt_no, debt_prop_type,
debt_tenancies, debt_tenancy, debt_prop_address1, debt_prop_address2, debt_prop_town, debt_prop_postcode
FROM debt WHERE (debt_sale_id = 0) AND (debt_client_id1 = '835' OR
debt_client_id1 = '2999');
Select Scope_Identity()
0
 
drews1fAuthor Commented:
I think i need to ignore the debt_sale_id column and update it once the new row is created.
If i was just copying one row i could do this fine - but i dont know how i can do this for multiple rows.
0
 
dzex13Commented:
the problem, most likely in quotes - remove quotes surrounding '77':

INSERT INTO debt (debt_sale_id, debt_client_id_type,
debt_client_id1, debt_client_id2, debt_client_id3, debt_client_id4, debt_type, debt_company,
debt_add, debt_ceasing, debt_balance, debt_cleared_monthly, debt_repayments, debt_ends,
debt_prop_value, debt_rental, debt_no, debt_prop_type, debt_tenancies, debt_tenancy,
debt_prop_address1, debt_prop_address2, debt_prop_town, debt_prop_postcode)
SELECT 77,debt_client_id_type, debt_client_id1, debt_client_id2 debt_client_id3,
debt_client_id4, debt_type, debt_company, debt_add, debt_ceasing, debt_balance, debt_cleared_monthly,
debt_repayments, debt_ends, debt_prop_value, debt_rental, debt_no, debt_prop_type,
debt_tenancies, debt_tenancy, debt_prop_address1, debt_prop_address2, debt_prop_town, debt_prop_postcode
FROM debt WHERE (debt_sale_id = 0) AND (debt_client_id1 = '835' OR
debt_client_id1 = '2999');
Select Scope_Identity()
0
 
drews1fAuthor Commented:
hah yeh - missing comma! my bad!
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.