SQL Server Performance

tranfer data from one table to another that are not joined

Discussion in 'SQL Server 2005 General Developer Questions' started by mikehaddad, Mar 5, 2010.

  1. mikehaddad New Member

    hi i'm new to sql server 2005 and to database in general i have 2 tables one is customers that has IP,Fname,Lname,Telephone,Email,Location,TypeofConnection,StartDate and the second table is stopped and has IP,Fname,Lname,Telephone,Email,Location,TypeofConnection,StartDate,StoppedDate.
    i want to copy data from customer table to stopped table where IP and stoppedDate are taken from textboxes. i worte this Insert into statement but i'm getting error this is the statement that i wrote:

    "INSERT INTO stopped (IP, Fname, Lname, Telephone,Email, Location, TypeofConnection, StartDate, StoppedDate) SELECT IP, Fname, Lname, Telephone,Email, Location, TypeofConnection, StartDate from customers where IP = '" & TextBox1.Text & "' AND StoppedDate = '" & TextBox2.Text & "';"


    how can i solve this problem.
  2. FrankKalis Moderator

    Welcome to the forum!
    You're not supplying values for StoppedDate in your SELECT statement. Either remove StoppedDate for the list in your INSERT or add the StoppedDate to your SELECT list.
    Btw, you might want to validate the TextBox input first before you use it in your statement. Otherwise you're vulnerable to SQL injection.
  3. mikehaddad New Member

    sorry but i need to add the StoppedDate at the same time and to the same row.is there any way to do that?
  4. Adriaan New Member

    Yes. Like I said earlier, the error message is very clear - you're not supplying any value for the StoppedDate column in your query.
  5. mikehaddad New Member

    yes i am supplying StoppedDate with value from textbox. and that is my problem.how to enter StoppedDate manually to each row i want to stop?
  6. Adriaan New Member

    Your query has two parts, INSERT INTO (target) and SELECT (source).
    For each column on the target, your source must provide a value.
    You can't ask SQL Server to insert a value into a column, and not provide the value.
  7. FrankKalis Moderator

    You are missing the bold green bit in your statement.
  8. mikehaddad New Member

    but StoppedDate is not a column in the customers table it is in the stopped table and i want to add it via textbox to the same row i selected from customer table and i tried a lot but i didn't get what i want so i will be thankful if u can help me.
  9. Madhivanan Moderator

    INSERT INTO stopped (IP, Fname, Lname, Telephone,Email, Location, TypeofConnection, StartDate, StoppedDate) SELECT IP, Fname, Lname, Telephone,Email, Location, TypeofConnection, StartDate, '" & stopped_date_text_box.text &"' from customers where IP = '" & TextBox1.Text & "' AND StoppedDate = '" & TextBox2.Text & "';"
  10. mikehaddad New Member

    sorry but still doesn't work.Can i insert the stoppeddate alone?
  11. Adriaan New Member

    Show us how you've written the code now. And if you say "it's not working", then tell us how you know - is there an error message?
    Just remember we can't see what you're looking at ...
  12. Adriaan New Member

    Please read the error message carefully, it tells you exactly which issue you need to address.
  13. Adriaan New Member

    Frank - validating the input? That's setting him on the road to hell, which as we all know is paved with code samples you can find on www.thedailywtf.com .
    The correct way is of course to use sp_ExecuteSQL with proper parameters, instead of concatenating string values into the query statement.
  14. FrankKalis Moderator

    [quote user="Adriaan"]
    Frank - validating the input? That's setting him on the road to hell, which as we all know is paved with code samples you can find on www.thedailywtf.com .
    The correct way is of course to use sp_ExecuteSQL with proper parameters, instead of concatenating string values into the query statement.
    [/quote]
    True enough. [:)]
  15. moh_hassan20 New Member

    can you capture the dynamic string and run it in SSMS. Does it work ?
    I doubt.!, for simple reason:
    you enter date as date format in textbox (e.g. mm/dd/yyyy) , and and the datetime in database is datetime format (e.g mm/dd/yyyy hh:mm:ss) and where condition will not be valid

    so modify the query and use between date1,date2 with date format as mm/dd/yyyy hh:mm:ss

Share This Page