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.

Edited October 19, 2006 by 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 wouldn't exist if you provided your own statements.