Hi,

I'm having problem updating the primary key value in vb run time. However, it works if I update it by using Query Analyzer.

Sub Update_ModuleSetup() On Error GoTo checkerror          updateQuery = "UPDATE D_Module SET ModuleID = '" & frmModuleSetup.txtModuleID(0).Text & "'" & _         ", ModuleName = '" & frmModuleSetup.txtModuleName(0).Text & "'" & _         ", ProgrammeID = '" & frmModuleSetup.cbProgramme.Text & "' WHERE ModuleID = '" & frmModuleSetup.txtModuleID(0).Text & "'"     rs.Open updateQuery, conn, adOpenKeyset, adLockPessimistic          'Unload all forms for refreshing data...     Call Refresh_Form              'Display Save Messagebox...     Call Save_Msg              frmModuleSetup.Show          Exit Sub      checkerror:     Call MsgBox(Err.Description, vbInformation) End Sub

According to the code above, ModuleID is the primary key.

Thank you

Hello. You didn't specify neither the dbms you are using (I guess it's SQL Server, but what is the version?), nor the problems you are experiencing.
Anyway, it seems to me that the ModuleID and ProgrammeID fields should contain two Long numeric values, isn't it? In this case you have not to put the single quotes around their values into the SQL instruction, like following:

updateQuery = "UPDATE D_Module SET ModuleID = " & frmModuleSetup.txtModuleID(0).Text & _         ", ModuleName = '" & frmModuleSetup.txtModuleName(0).Text & "'" & _         ", ProgrammeID = " & frmModuleSetup.cbProgramme.Text & " WHERE ModuleID = " & frmModuleSetup.txtModuleID(0).Text

If this is not the case, paste here the working SQL instruction you use into the Query Analyzer, so we can try to build a same format instruction by VB.
Hope it helps.

____________________________
Real Programmer can count up to 1024 on his fingers

Hi,

Sorry for not specifying it, it's SQL Server 2000 and the main problem is I'm able to update ModuleName and ProgrammeID in VB Form, since these 2 values are not specified as Primary Key but the ModuleID can't be updated in VB Form since it's specified as Primary Key in SQL Server 2000.

Additionally, all the data type for ModuleID, ModuleName, and ProgrammeID are specified as "nvarchar".

In Query Analyzer, I've written the code as: (e.g)

UPDATE V_ModuleReg SET ModuleID = '200', ModuleName = 'Testing', ProgrammeName = 'Testing' WHERE ModuleID = '111'

It works since the ModuleID can be updated as '200' where its original value was '111'. I'm trying to do the same thing when it runs in VB Form, but it unable to update the ModuleID value to '200'.

Hello. If you make a test into the Query Analyzer, setting the primary key to 200, when you run again the same instruction from VB the pk value is already 200: you have set it few seconds ago by the Query Analyzer!
Since it is used to unique tell a record, the primary key field can't accept any duplicated value. This means that if you anyhow have already a value (say, 200) into the field, you can't assign that value neither to another record nor to the same record until you don't remove the existing value.
I suggest you to check this case: make sure the new value doesn't exist, and try executing your query only from VB. It should work.

About the quotes around the values, ignore my previous suggestion to remove them: single quotes are the characters in which you got to enclose all the values for text (char, nchar, varchar, nvarchar) fields, plus some other field data type. It is the correct syntax.
Rather, you should consider that is not wise to use a text datatype for an ID field. A counter - numeric integer - field is the most performant, either automatically assigned by SQL Server or manually assigned by your VB application. If you got to store an integer number, a numeric field can store up to 255 values into one single byte while a text field can store up to 9 values by the same byte. So, in order to store "200" you need three bytes: you could do it by one single byte in an int field. You might consider to change the text datatype to a numeric datatype. If you don't have to store a number, but an alphanumeric value, say 123abc, you must implement a way to prevent duplicate values input into your VB app. And keep in mind your db will be less performant. Remeber also that all hexadecimal values, like "7C 4F 12 0B", are NOT alphanumeric values but real valid integer numbers (the above one means 2085556747, and it fits into an int type field).

Hope it helps

____________________________
Real Programmer can count up to 1024 on his fingers

Hi yronium,

Thanks for the great info, it's still not working although I didn't update it in Query Analyzer.

Wondering what is blocking it from updating the PK value.

Regards,
Gary

For sure, the only check you got to do is if the number you are intended to put into the PK field is really already existing. If so, you got to check and correct the algorithm you use for its creation in the code.
Hope it helps

____________________________
Real Programmer can count up to 1024 on his fingers

Hi yronium,

Thanks for the reply, I've found the mistake where I should update the pk as below:

Let say V_Table is having 2 columns, 1st column name is ID (Primary Key column) and 2nd column name is No. Hence, I should update the pk by issuing the below statement.

"UPDATE V_Table SET ID = '10' WHERE No = '9'" rather than "UPDATE V_Table SET ID = '10' WHERE ID = '9'"