none
Insert GUID in SQL RRS feed

  • Question

  • I need to read a GUID in some VB.Net code and insert it into another table. Below is a code snippet. How do I format the Insert stmt?

        'ACcmd is As New SqlClient.SqlCommand already opened
        'dr is SqlClient.SqlDataReader
        Dim UserGuid As Guid, InsertStmt As String, Ctr As Integer
        ACCmd.CommandText = "Select UserID, PKID From Users Where UserID = '" & UserID & "'"
        dr = ACCmd.ExecuteReader
        dr.Read()
        UserGuid = dr.GetGuid(1)
        'What should I put in the next line for UserGuid
        ACCmd.CommandText = "Insert Into TranTbl(TranAmt, UserGuid) Values ('100, " & UserGuid & ")"
        Ctr = ACCmd.ExecuteNonQuery


    Price Brattin, SQLServer & SharePoint 2010 MCP, Microsoft Dynamics SL Consultant


    Tuesday, October 2, 2012 11:50 AM

Answers

  • It's easier than I thought. I can use a string in the insert statement and it works correctly. Below is the code I got to work.

        'ACcmd is As New SqlClient.SqlCommand already opened
        'dr is SqlClient.SqlDataReader
        Dim UserGuid As Guid, Ctr As Integer, GuidStr As String
        ACCmd.CommandText = "Select DistinguishedName, PKID From Users Where DistinguishedName = '" & UserID & "'"
        dr = ACCmd.ExecuteReader
        dr.Read()
        UserGuid = dr.GetGuid(1) 'this works and shows the Guid
        dr.Close()
        GuidStr = UserGuid.ToString
        ACCmd.CommandText = "Insert Into xTranTbl(TranAmt, UserGuid) Values (250, '" & GuidStr & "')"
        Ctr = ACCmd.ExecuteNonQuery


    Price Brattin, SQLServer & SharePoint 2010 MCP, Microsoft Dynamics SL Consultant

    • Marked as answer by Alexander Sun Tuesday, October 23, 2012 8:09 AM
    Tuesday, October 2, 2012 9:51 PM

All replies

  • This does not work either:

        'ACcmd is As New SqlClient.SqlCommand already opened
        'dr is SqlClient.SqlDataReader
        Dim UserGuid As Guid, Ctr As Integer, GuidStr As String
        ACCmd.CommandText = "Select DistinguishedName, PKID From Users Where DistinguishedName = '" & UserID & "'"
        dr = ACCmd.ExecuteReader
        dr.Read()
        UserGuid = dr.GetGuid(1)
        dr.Close()
        GuidStr = UserGuid.ToString
        'What should I put in the next line for UserGuid
        'InsertStmt = "Insert Into xTranTbl(TranAmt, UserGuid) Values ('100, " & UserGuid & ")"
        'This does not work either
        ACCmd.CommandText = "Insert Into xTranTbl(TranAmt, UserGuid) Values ('100, Cast('" & GuidStr & "') As UniqueIdentfier))"
        Ctr = ACCmd.ExecuteNonQuery


    Price Brattin, SQLServer & SharePoint 2010 MCP, Microsoft Dynamics SL Consultant

    Tuesday, October 2, 2012 2:49 PM
  • I would recommend using Command Parameters for your SQL statements. Below is an example using a Guid:

        using(SqlConnection conn = new SqlConnection(connectionString)) 
        { 
            conn.Open(); 
            using (SqlCommand cmd = conn.CreateCommand()) 
            { 
                cmd.CommandText = @"INSERT INTO [MYTABLE] ([GuidValue]) VALUE @guidValue;"; 
                cmd.Parameters.AddWithValue("@guidValue", Guid.NewGuid()); 
                cmd.ExecuteNonQuery(); 
            } 
        } 


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, October 2, 2012 2:54 PM
  • Thx for your prompt post.

    Maybe Command Parameters would work but the problem is different. Note that it is not a new Guid that needs to be inserted. Rather it is an existing Guid from one table that has to be the same in the insert stmt for a 2nd table so the tables can be referentially linked. I did not design this DB but I have to make it work.

    How would  the code work to take a Guid read from one table and inserted into another table?


    Price Brattin, SQLServer & SharePoint 2010 MCP, Microsoft Dynamics SL Consultant

    Tuesday, October 2, 2012 3:05 PM
  • Take the variable you assigned the Guid to (UserGuid) and use that as the value (second argument) in the AddWithValue statement.

    I simply used Guid.NewGuid() as an example but you can use a variable containing a Guid value as well.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, October 2, 2012 3:33 PM
  • OK, thanks. I guess I could use parameter in a command if that is the only way I can accomplish this. I have between 100,000 and a million records to create in multiple tables and this program will be run multiple times. If I have to execute the command for each insert it seems that the process will consume a lot of time.

    To make things faster my current code uses a loop to build a large string of 2000 Insert statements at a time and uses it into an ExecNQ for a command. Currently in my real code I am using a stringbulilder to do that and it works great but I have been ignoring the GUID. That being the case, do you know any way in a dotNet program of taking a guid from one table and using it in an insert statement for another table? Is there a way to use Cast or the equivilent of AddWithValue in TSQL?


    Price Brattin, SQLServer & SharePoint 2010 MCP, Microsoft Dynamics SL Consultant

    Tuesday, October 2, 2012 4:23 PM
  • Essentially you've described a batch process and speed isn't really going to be possible if the rows are inserted one at a time. If you were exporting from one table to another you could use a bulk copy mechanism (such as the SQLBulkCopy Class or even straight SQL) but that doesn't sound like what you are doing.

    I do not know what the data source is for the other column values that will be inserted with the GUID. If all data is coming from the same table, or can be generated within the SQL statement, then you can use an INSERT INTO...SELECT FROM SQL statement. Otherwise, the inserts may need to occur one row at a time.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, October 2, 2012 5:21 PM
  • It's easier than I thought. I can use a string in the insert statement and it works correctly. Below is the code I got to work.

        'ACcmd is As New SqlClient.SqlCommand already opened
        'dr is SqlClient.SqlDataReader
        Dim UserGuid As Guid, Ctr As Integer, GuidStr As String
        ACCmd.CommandText = "Select DistinguishedName, PKID From Users Where DistinguishedName = '" & UserID & "'"
        dr = ACCmd.ExecuteReader
        dr.Read()
        UserGuid = dr.GetGuid(1) 'this works and shows the Guid
        dr.Close()
        GuidStr = UserGuid.ToString
        ACCmd.CommandText = "Insert Into xTranTbl(TranAmt, UserGuid) Values (250, '" & GuidStr & "')"
        Ctr = ACCmd.ExecuteNonQuery


    Price Brattin, SQLServer & SharePoint 2010 MCP, Microsoft Dynamics SL Consultant

    • Marked as answer by Alexander Sun Tuesday, October 23, 2012 8:09 AM
    Tuesday, October 2, 2012 9:51 PM
  • Yes, but you should still use Command Parameters instead of inserting variables into a SQL string. There are several reasons for this, most notable is that the use of variables poses a SQL injection vulnerability. Also, if a variable contains a special character, such as a single quote, the SQL statement will fail to execute. Probably won't happen with a GUID, but it's good practice to use Command Parameters instead.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, October 3, 2012 2:52 AM