如何在不设置combo.listindex属性的情况下更新外键记录 [英] How do I update foreign key records without setting combo.listindex property

问题描述

 私有  Sub  CmdSave_Click（）
 如果 Val（CmbRNO）=  0  那么 
 RES.ADDNEW 
 结束 如果 
 否则 
 如果 MsgBox（ 想要修改当前记录，vbYesNo + vbInformation， 修改记录）= vbYes 然后 
 SQL =   UPDATE STUDENT_RECORD_DATABASE SET 
 SQL = SQL +   ROLLNO =& ; Val（CmbRNO）&  ， 
 SQL = SQL +    CONTACT =& Val（Text5）&  ， 
 SQL = SQL +    ADDRESS ='&修剪（TxtADDR）&  '， 
 SQL = SQL +    GRADE ='&修剪（COMBO1）&  '， 
 SQL = SQL +    DIVID ='& Val（CmbDIV.ItemData（CmbDIV.ListIndex））&  '， \\Foreign key 
 SQL = SQL +   HID =& Val（CmbHOUSE.ItemData（CmbHOUSE.ListIndex））&  ， \\Foreign key 
 SQL = SQL +   DATE_OF_BIRTH ='& Format（DTPicker1.Value，< span class =code-string>  dd-MMM-yyyy）&  '， 
 SQL = SQL +   SNAME ='&修剪（CmbSTOP）&  '， 
 SQL = SQL +    BUSNO ='&修剪（CmbBUS）&  '， 
 SQL = SQL +    DID =& Val（CmbDRIVER.ItemData（CmbDRIVER.ListIndex））&  ， \\Foreign key 
 SQL = SQL +   AID =& Val（CmbATTEND.ItemData（CmbATTEND.ListIndex））&  ， \\foreign key 
 SQL = SQL +   WHERE ROLLNO =& Val（CmbRNO）&   
 设置 RES = CON.Execute（SQL）
 MsgBox（  RECORD UPDATED）
 结束 如果 
 结束 < span class =code-keyword> Sub  

1.这是我的表student_record_database的代码，用于更新现有记录。



2.运行此代码我修改了几个值并希望更新记录，

它显示错误如：数组索引的无效属性值



3.我知道这是因为combo.listindex属性设置为-1表示未修改的外键，因为不是每一个（4）外来的密钥被修改。



4.但我不想修改每个外键（即使我不想将combo.listindex属性设置为0因为它设定s默认的组合值）我希望你明白我想说的是什么.....



5.请按照我想要的方式修改记录没有将.listindex属性设置为0.



请帮助我........

解决方案

< blockquote>首先，使用参数，参见 http：/ /msdn.microsoft.com/en-us/library/windows/desktop/ms675869(v=vs.85).aspx [ ^ ]。您当前将值直接连接到SQL语句的方式会让您对Sql注入，数据类型转换问题等开放。



关于实际问题。您必须将missing值设置为null。您可以在Sql语句或代码中执行此操作。您可以使用if语句来决定是否将参数设置为null，或者您可以修改语句来执行此操作。



对于语句，类似（如果我记得的话） ADO参数的语法正确）：

 ... 
 SQL = SQL +   DIVID = DECODE（？， -  1，NULL，？）， \\Foreign key 
 ... 

然后在代码中设置相应的参数以包含

 Val（CmbDIV.ItemData（CmbDIV.ListIndex））

Private Sub CmdSave_Click()
    If Val(CmbRNO) = 0 Then
RES.ADDNEW
    End If
Else
If MsgBox("WANT TO MODIFY THE CURRENT RECORD", vbYesNo + vbInformation, "Modify Record") = vbYes Then
SQL = "UPDATE STUDENT_RECORD_DATABASE SET "
SQL = SQL + "ROLLNO= " & Val(CmbRNO) & ","
SQL = SQL + "CONTACT=" & Val(Text5) & ","
SQL = SQL + "ADDRESS= '" & Trim(TxtADDR) & "',"
SQL = SQL + "GRADE='" & Trim(COMBO1) & "',"
SQL = SQL + "DIVID='" & Val(CmbDIV.ItemData(CmbDIV.ListIndex))&"',"  \\Foreign key
SQL = SQL + "HID=" & Val(CmbHOUSE.ItemData(CmbHOUSE.ListIndex))&","  \\Foreign key
SQL = SQL + "DATE_OF_BIRTH='"&Format(DTPicker1.Value,"dd-MMM-yyyy")&"',"
SQL = SQL + "SNAME='" & Trim(CmbSTOP) & "',"
SQL = SQL + "BUSNO='" & Trim(CmbBUS) & "',"
SQL = SQL + "DID="&Val(CmbDRIVER.ItemData(CmbDRIVER.ListIndex))&","  \\Foreign key
SQL = SQL + "AID="&Val(CmbATTEND.ItemData(CmbATTEND.ListIndex))&","  \\foreign key
SQL = SQL + "WHERE ROLLNO= " & Val(CmbRNO) & ""
    Set RES = CON.Execute(SQL)
    MsgBox ("RECORD UPDATED")
   End If
End Sub

1.This is my code for table student_record_database to update existing record.

2.While running this code I modify few values and want to update record,
It shows error like: Invalid property value of array index

3.I know it''s reason because combo.listindex property set to -1 for non modified foreign keys,because not each and every(4) foreign key is modified.

4.But I don''t want to modify every foreign key(even I don''t want to set combo.listindex property to 0 because it sets default combo value)I hope you understand what I am trying to say.....

5.Please suggest me the way I can modify records as I want without setting .listindex property to 0.

Please help me........

解决方案

First of all, use parameters, see http://msdn.microsoft.com/en-us/library/windows/desktop/ms675869(v=vs.85).aspx[^] . Your current way of concatenating values directly to the SQL statement leaves you open to Sql injections, data type conversion problems and so on.

About the actual problem. You have to set the "missing" value to null. You can do this either in the Sql statement or in the code. Either you use if statement to decide whether to set the parameter as null or you can modify the statement to do this.

For the statement, something like (if I remember the syntax for ADO parameters correctly):

...
SQL = SQL + "DIVID=DECODE(?, -1, NULL, ?),"  \\Foreign key
...

And then set the corresponding parameters in your code to contain

Val(CmbDIV.ItemData(CmbDIV.ListIndex))

这篇关于如何在不设置combo.listindex属性的情况下更新外键记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！