SQL command builder

[mike55]

I have the following code:

 Try

                Dim sqlCommand As String
                Dim cmdImport As New SqlCommand
                Dim dcPKMembers(1) As DataColumn

                sqlCommand = "SELECT Member_ID, Custom_ID, Surname, Forename, FullName, DOB, Title, MobileNumb, PhoneNumb, Addr1,Addr2, Town, County, " & _
                                        " Role, Guardian1,Guardian1_Phone, Guardian2, Guardian2_Phone, Email,AdditionalInformation,CodePrefix, Country, PostCode," & _
                                        "InternationalNumber, Sex," & _
                                        "ImportReference,Custom1, Custom2, " & _
                                        "Custom3, Custom4, Custom5, Custom6, Custom7, Custom8, Custom9, Custom10, Org_ID, Active " & _
                                        "FROM Members WHERE Org_ID = " & mOrgId & ""

                cmdImport.Connection = conSQL
                cmdImport.CommandType = CommandType.Text
                cmdImport.CommandText = sqlCommand

                adpDatabase = New SqlDataAdapter
                adpDatabase.SelectCommand = cmdImport
retry:
                adpDatabase.Fill(dstDatabase, "Members")

                Sets the primary Key on mobile number and fullname
                dcPKMembers(0) = dstDatabase.Tables("Members").Columns("MobileNumb")
                dcPKMembers(1) = dstDatabase.Tables("Members").Columns("FullName")
                dstDatabase.Tables(0).PrimaryKey = dcPKMembers

                Creates a new command builder
                cmdbDatabase = New SqlCommandBuilder(adpDatabase)

In some cases its has no problem in going off and creating the update and the insert commands with the line:

cmdbDatabase = new SqlCommandBuilder(adpDatabase)

While in other cases it will not create the commands, and gives the following error:

Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.

Any suggestions on how I can ensure that it creates the necessary commands every time?

Mike55.

Ok, according to a forum on VistaDB, my problem relates to the following lines:

 dcPKMembers(0) = dstDatabase.Tables("Members").Columns("MobileNumb")
                dcPKMembers(1) = dstDatabase.Tables("Members").Columns("FullName")
                dstDatabase.Tables(0).PrimaryKey = dcPKMembers

Apparently what I am doing is changing the PRIMARY KEY while Editing. And this is not allowed.

So I have found the source of my problem, I must still get a solution to it.

Mike55.

 

[mike55]

Ok, I have fixed my problem, by simply removing the primary key field (member_ID) from the select statement. This change allows me to add new members without any problem. However if I try to update the members again, I get the following error:

Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.

Mike55.

 

[PlausiblyDamp]

Generally it is better to provide your own Insert / Update / Delete commands rather than rely on the CommandBuilder as you have far more control over exactly how the operations are performed.

The limitation on not modifying / needing to include a primary key wouldnt exist if you provided your own statements.