Solved

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

Posted on 2009-05-12
13
672 Views
Last Modified: 2012-05-06
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
0
Comment
Question by:drews1f
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
13 Comments
 
LVL 15

Expert Comment

by:rob_farley
ID: 24365077
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
 

Author Comment

by:drews1f
ID: 24365123
no its not an identity column :(
0
 
LVL 15

Expert Comment

by:rob_farley
ID: 24365203
What's a "manual value"?

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

Rob
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:drews1f
ID: 24365272
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
 
LVL 15

Expert Comment

by:rob_farley
ID: 24365380
So what is IDone set to?
0
 

Author Comment

by:drews1f
ID: 24365395
an integer. in this particular case it was

74
0
 
LVL 15

Expert Comment

by:rob_farley
ID: 24365530
Put a breakpoint in, grab the command, and run it in SSMS. That might give you some more clues.

Rob
0
 

Author Comment

by:drews1f
ID: 24365589
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
 

Author Comment

by:drews1f
ID: 24365592
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
 

Author Comment

by:drews1f
ID: 24365642
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
 
LVL 2

Expert Comment

by:dzex13
ID: 24367173
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
 
LVL 15

Accepted Solution

by:
rob_farley earned 500 total points
ID: 24371315
Ok... you're missing a comma in the SELECT statement:

debt_client_id2 debt_client_id3

Rob
0
 

Author Comment

by:drews1f
ID: 24372941
hah yeh - missing comma! my bad!
thanks
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question