Inserting GUID into datatable

[bchaney]

I'm calling a GUID with a system function and trying to insert it into a database as a primary key. i'm using vb 2010 .net framework with sqlce server making my datatables. the column is AutoNumber which i set up to be a uniqueidentifier. the length is not adjustable and set at 16. i got all kinds of not parsing query errors. i've edited the insert statement a bunch trying to figure this out. i've went so far as changing the table schema of AutoNumber to be just a nvarchar with length of 100. it's something to do with how i'm inserting the GUID. in the code below i'm converting it into a string then inserting it into my table. is this wrong?? i've tried finding other ways of doing it but it's a little confusing. everything else in the code works perfectly it's just this part that is keeping me from completion.

Private Sub saveandexitbutton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles saveandexitbutton.Click
        Dim check As Integer
        Dim mycon As SqlCeConnection
        Dim mycmd1 As New SqlCeCommand
        Dim myDA As New SqlCeDataAdapter
        Dim myDS As New DataSet
        Dim myDT As New DataTable
        Dim cline As String
        Dim autonumber As String

        cline = linecbo.SelectedValue
        If shiftstatus <> "1" And shiftstatus <> "2" Then
            MsgBox("Please Select Shift", MsgBoxStyle.OkOnly, "Message")
        ElseIf linecbo.SelectedIndex = -1 Or frm3machinecbo.SelectedIndex = -1 Or possiblecausetxtbox.Text = "" Or descriptiontxtbox.Text = "" _
            Or estimatedtxt.Text = "" Then
            MsgBox("Please Fill in Data Fields", MsgBoxStyle.OkOnly, "Message")
        ElseIf MsgBox("Are you sure you want to save information?", MsgBoxStyle.OkCancel, "Message") = MsgBoxResult.Cancel Then
            'do nothing
        Else
            Try
                mycon = GetConnect()
                mycon.Open()
                autonumber = System.Guid.NewGuid.ToString

                mycmd1 = mycon.CreateCommand
                mycmd1.CommandText = "INSERT INTO baseform(AutoNumber, Date, Shift, MechanicName, LineNumber, Machine, EstimatedTime, RootCause, BriefDescription) '" & _
                    "'VALUES('" & autonumber & "','" & datetxtbox.Text & "','" & shiftstatus & "','" & fullnametxtbox.Text & _
                    "','" & linecbo.SelectedValue & "','" & frm3machinecbo.SelectedValue & "','" & estimatedtxt.Text & _
                    "','" & Trim(possiblecausetxtbox.Text) & "','" & Trim(descriptiontxtbox.Text) & "')"

                check = mycmd1.ExecuteReader.RecordsAffected()

                If check > 0 Then
                    MsgBox("Mechanic with ID " & Trim(fullnametxtbox.Text) & " successfully added", MsgBoxStyle.OkOnly, "Message :")
                Else
                    MsgBox("Mechanic with ID " & Trim(fullnametxtbox.Text) & " failed to add", MsgBoxStyle.OkOnly, "Message :")
                End If

                mycmd1.ExecuteNonQuery()
                DataEntry_Refresh_Form()
                mycon.Close()
                Me.Close()

            Catch ex As Exception
                MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OkOnly, "Connection Error!!")
            End Try
        End If
    End Sub

any help would be appreciated. i'm sure it's just a simple fix but it's escaping me.

Thanks,

[Teme64]

i got all kinds of not parsing query errors.

Keep it simple. First make a simple insert, once it works, add fields one by one and test again. You'll find the offending part.

I used 2008R2, not SQL Server CE, but it doesn't make any difference. I also dropped If-statements from the code because they were irrelevant to SQL-related error. I suggest checking how this code works (with Ce) and make the corrections to your original code.

I added comments to the code, I hope you find them helpful:

Dim check As Integer
Dim mycon As SqlConnection 'SqlCeConnection
Dim mycmd1 As New SqlCommand 'SqlCeCommand
Dim myDA As New SqlDataAdapter 'SqlCeDataAdapter
Dim myDS As New DataSet
Dim myDT As New DataTable
Dim cline As String
Dim autonumber As String

' Let's move "OpenConnection" in a separate Try-Catch block, now we know if connection.open failed or command.execute failed
Try
    'mycon = GetConnect()
    ' DEBUG
    mycon = New SqlConnection("Data Source=<MYDB>; INITIAL CATALOG=<TABLE>; User ID=<NAME>; Password=<PASSWORD>;")
    mycon.Open()
Catch ex As Exception
    MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OkOnly, "Connection Error!!")
    ' Since we can't open a connection, simply return without closing the form and let the user handle the situation
    Exit Sub
End Try

Try
    ' Generate a GUID
    autonumber = System.Guid.NewGuid.ToString

    mycmd1 = mycon.CreateCommand
    'mycmd1.CommandText = "INSERT INTO baseform(AutoNumber, Date, Shift, MechanicName, LineNumber, Machine, EstimatedTime, RootCause, BriefDescription) '" & _
    '    "'VALUES('" & autonumber & "','" & datetxtbox.Text & "','" & shiftstatus & "','" & fullnametxtbox.Text & _
    '    "','" & linecbo.SelectedValue & "','" & frm3machinecbo.SelectedValue & "','" & estimatedtxt.Text & _
    '    "','" & Trim(possiblecausetxtbox.Text) & "','" & Trim(descriptiontxtbox.Text) & "')"

    ' Use a simple insert
    ' Once this works, add fields one by one
    mycmd1.CommandText = "INSERT INTO Test(AutoNumber, Date) " & _
        "VALUES('" & autonumber & "','" & datetxtbox.Text & "')"

    ' This line INSERTS a row in the table:
    check = mycmd1.ExecuteReader.RecordsAffected()

    ' When you insert a row successfully, exactly one row is affected!
    'If check > 0 Then
    If check = 1 Then
        MsgBox("Mechanic with ID successfully added", MsgBoxStyle.OkOnly, "Message :")
    Else
        MsgBox("Mechanic with ID failed to add", MsgBoxStyle.OkOnly, "Message :")
    End If

    ' Duplicates previous INSERT:
    'mycmd1.ExecuteNonQuery()

    ' DEBUG: Commented out a line
    'DataEntry_Refresh_Form()

Catch ex As Exception
    MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OkOnly, "Failed to insert data to DB")
    ' DEBUG: Never dump SQL in production code to user!
    MsgBox("SQL: " & mycmd1.CommandText)
Finally
    ' Dispose command object
    mycmd1 = Nothing
    ' We have a DB-connection open so close it
    mycon.Close()
End Try

' Close the form
Me.Close()

The problem wasn't in the GUID. The main "error point" was that duplicate execution of INSERT and syntax error(s) in the INSERT statement, for example:
...BriefDescription) ''VALUES('... <- That '' before VALUES caused an error.

HTH

[bchaney]

The quotation marks before values was the problem area. Thank you for showing me how to debug the program properly. i had the problem narrowed down to my insert statement but i couldn't figure out what was causing it. i totally looked over the small quotation marks i had before the VALUES statement. the code works and is adding data into the datatable (all data fields) with one exception. for some reason i'm getting "Items collection cannot be modified when the DataSource property is set" msgbox popping up. i looked it up and it said that this comes up when you modify a binded datatable without modifying the dataset first. the table i am modifying isn't bound to anything on the form. i do have data binding but its not with this table or dataset. any ideas?