Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2009
    Location
    Sweden
    Posts
    19

    Exclamation Unanswered: Stored Procedure to solve this problem !!!

    Hi there,
    i have this code with c#.net, i need to use stored procedure to do the same work!!!

    Code:
    SqlCommand comm1;
    SqlConnection conn1;
    string connectionString1 = ConfigurationManager.ConnectionStrings["DBConnectionString1"].ConnectionString;
    conn1 = new SqlConnection(connectionString1);
    string sqlcomm1 = "INSERT INTO customer (name, firstname, tele, mobil, fax, email, personno)" +
    "SELECT DISTINCT contactperson, Name, Tele, Mobil, Fax, email, PersonNo FROM tem5"+" WHERE (Seid ='" + susid + "')";
    comm1 = new SqlCommand(sqlcomm1, conn1);
    conn1.Open();
    comm1.ExecuteNonQuery();
    conn1.Close();
    what i need how to make the above sql statement with Insert, Select and Where as stored procedure ???

    how i can call the stored procedure from c#.net programming to pass the "susid" ???

    thanks alot in advance ...

  2. #2
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    -- Usage exec usp_customer @Seid = 11
    create procedure usp_customer
    (@Seid int)
    as
    INSERT INTO customer (name, firstname, tele, mobil, fax, email, personno)
    SELECT DISTINCT contactperson, Name, Tele, Mobil, Fax, email, PersonNo FROM tem5 WHERE (Seid = @Seid)

    I think to exec, something like this:

    SqlCommand comm1;
    SqlConnection conn1;
    string connectionString1 = ConfigurationManager.ConnectionStrings["DBConnectionString1"].ConnectionString;
    conn1 = new SqlConnection(connectionString1);
    string sqlcomm1 = "exec usp_customer @Seid = " + susid;
    comm1 = new SqlCommand(sqlcomm1, conn1);
    conn1.Open();
    comm1.ExecuteNonQuery();
    conn1.Close();
    Last edited by PMASchmed; 03-08-10 at 14:16.

  3. #3
    Join Date
    Feb 2009
    Location
    Sweden
    Posts
    19
    Hi PMASchmed,
    i don't know but it doesn't work!!!

    any suggestion !!!

  4. #4
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Name the columns in the select to match the insert.

  5. #5
    Join Date
    Feb 2009
    Location
    Sweden
    Posts
    19
    yes, i'll try that ... thanks

  6. #6
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Quote Originally Posted by Abrahm75 View Post
    Hi PMASchmed,
    i don't know but it doesn't work!!!

    any suggestion !!!
    What do you mean that it doesn't work?

    Can you create the stored procedure without error?

    Did you test the stored procedure within itself?

    Whenever I write a stored procedure, I use the following organization:

    Code:
    create procedure usp_customer
    
    @Seid int
    
    as
    
    set nocount on
    
    /* TEST SECTION
    usp_customer @Seid=11 with recompile
    declare @Seid int
    set @Seid=11
    --*/
    
    INSERT  
    INTO    customer
    SELECT
    DISTINCT
            contactperson name
            ,Name firstname
            ,Tele
            ,Mobil
            ,Fax
            ,email
            ,PersonNo
    FROM    tem5
    WHERE   Seid=@Seid
    If you wipe from the "declare" downward you can test your code and make sure the query itself is working.

    Did you give the stored procedure execute permissions for the user who will be accessing the code?
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The line comment before your section end comment tag is a nifty idea. I'll definitely use that!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •