How do I update foreign key records without setting combo.listindex property

Question

0.00/5 (No votes)

See more:

VB

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........

Posted 9-Jan-13 17:52pm

surkhi

Updated 9-Jan-13 18:04pm

Sandeep Mewara

v3

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2013-01-09T18:34:00

Solution 2

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):

VB

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

And then set the corresponding parameters in your code to contain

VB

Val(CmbDIV.ItemData(CmbDIV.ListIndex))

Posted 9-Jan-13 18:34pm

Wendelius

Comments

surkhi 10-Jan-13 0:46am

sub Form_load()
CmbDIV.AddItem RES.Fields("DIV").Value
CmbDIV.ItemData(CmbDIV.NewIndex) = RES.Fields("DIVID").Value
End sub()

I have set the .itemdata value of each combo like this manner....
So,
SQL=SQL+ "DIVISION=DIVID(?,-1,NULL,?),""
Where should I set this statement???
In insert command or Update or Form Load???

Wendelius 10-Jan-13 0:56am

This is part of the update statement you posted, so you should modify the DML statements (update and insert) this way for each foreign key column.

About parameters, I didn't mean how you set the combo box but how you set the values for the SQL statement. Please refer to the link I posted.

surkhi 10-Jan-13 1:03am

thanx

Wendelius 10-Jan-13 2:20am

You're welcome

Sandeep Mewara 10-Jan-13 23:53pm

My 5! Good answer.:)

OT: Don't edit the spam questions. Just flag them. Chris asked not to edit them few days back. We have few fresh attacks, lets kick them out. :)

Wendelius 11-Jan-13 0:29am

Thanks Sandeep :)

Oh, that's good to know; Will just flag them :)

How do I update foreign key records without setting combo.listindex property

1 solution

Solution 2

Add your solution here

Preview 0

How do I update foreign key records without setting combo.listindex property

1 solution

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us