Assigning a SQL query result to a variable

JohnQuar1

Hello, I have a very simple query SELECT newid(), and I want to take the result of this query and assign it a variable so I can use it as a unique ID and use that again in another query. I am having trouble saving the actual variable. Can anyone help? Here is what I have so far. I am also pretty sure that my ID.Close() is in the wrong location. Let me kno what you think.

        Session\["userName"\] = "JMQ";
        // create a new SqlConnection object with the appropriate connection string 
        SqlConnection sqlConn = new SqlConnection("Data Source=localhost;Initial Catalog=Cost\_Model;Integrated Security=True");

        //Open the connection
        sqlConn.Open();

        String result;

        //Create unique ID
        SqlCommand sqlComm1 = new SqlCommand("SELECT newid()", sqlConn);
        SqlDataReader ID = sqlComm1.ExecuteReader(CommandBehavior.CloseConnection);
        
        //Close the connection
        ID.Close();
        
        sqlConn.Open();
        SqlCommand sqlComm2 = new SqlCommand(
            "SELECT \* INTO ##tempFAR15\_" + Session\["userName"\] + "\_" + ID + " FROM FN\_FAR15(1)",
            sqlConn);
        SqlDataReader r = sqlComm2.ExecuteReader(CommandBehavior.CloseConnection);
       
        r.Close();
        
        Response.Redirect("FAR15.aspx");

I want to take the result from the 1st query which I (probably innapropriately have labeled as ID) and then use ID in teh second query. Thanks ALOT!!

Christian Graus

Use ExecuteScalar when only one piece of data is returned.

Christian Graus Driven to the arms of OSX by Vista. Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.

JohnQuar1

I thought about that but I am not entirely sure how to use it. Can you give me an example?

PIEBALDconsult

Why not just use System.Guid newid = System.Guid.NewGuid() ; ? That's one of the benefits of GUIDs over identities.

JohnQuar1

That was the perfect answer. Did exactly what I needed. Thank you!

PIEBALDconsult

Glad to be of service.