CODEWITHSUNDEEP
oracle-databasevb6
Cool_Guy
Cool_Guy

Reputation: 73

How to show text in combo box but referencing id field?

I wrote code for insert into and update table student_record_database having many foreign keys of other tables.

While running this code it is not accepting text data selected from combo box and giving error invalid number,integrity constraint,and DTpicker also not accepting given date though I have set custom format.

Here's my code below.I am having problem to show text in combo box.

<code>
Private Sub save_Click()
If Val(COMBO4) = 0 Then
SQL = "INSERT INTO    STUDENT_RECORD_DATABASE(ROLLNO,FIRST_NAME,MIDDLE_NAME,LAST_NAME,CONTACT,CONTACT1,CONTACT2,ADDRESS,GRADE,DIVID,BLOOD_GROUP,HID,DATE_OF_BIRTH,TRANSPORT,SNAME,MEAL,BUSNO,RUTNO,DID,AID,CARD_TYPE,CARD_NO)"

  SQL = SQL + "VALUES(" & Val(COMBO4) & ",'" & Trim(Text2) & "', '" & Trim(Text3) & "', '" & Trim(Text4) & "', " & Val(Text5) & ", " & Val(Text6) & ", " & Val(Text7) & ", '" & Trim(Text8) & "', '" & Trim(Combo1) & "','" & Val(Combo2) & "',"
SQL = SQL + " '" & Trim(Combo3) & "' ,'" & Val(Combo9) & "','" & DTPicker1.Value & "',"
SQL = SQL + " '" & Trim(Combo10) & "' ,'" & Combo5.Text & "' ,'" & Trim(Combo11) & "' ,'" & Combo6.Text & "' ,'" & Val(Combo12) & "' ,'" & Val(Combo7) & "','" & Val(Combo8) & "',"

 SQL = SQL + " '" & Trim(Combo13) & "' ," & Text11.Text & " ) "

  Set RES = CON.Execute(SQL)
MsgBox ("RECORD INSERTED")
Else
SQL = "UPDATE STUDENT_RECORD_DATABASE SET "
SQL = SQL + "ROLLNO= " & Val(COMBO4) & ","
SQL = SQL + "FIRST_NAME= '" & Trim(Text2) & "',"
SQL = SQL + "MIDDLE_NAME= '" & Trim(Text3) & "',"
SQL = SQL + "LAST_NAME= '" & Trim(Text4) & "',"
SQL = SQL + "CONTACT=" & Val(Text5) & ","
SQL = SQL + "CONTACT1=" & Val(Text6) & ","
SQL = SQL + "CONTACT2=" & Val(Text7) & ","
SQL = SQL + "ADDRESS= '" & Trim(Text8) & "',"
SQL = SQL + "GRADE='" & Trim(Combo1) & "',"
SQL = SQL + "DIVID='" & Val(Combo2) & "',"
SQL = SQL + "BLOOD_GROUP='" & Trim(Combo3) & "',"
SQL = SQL + "HID='" & Val(Combo9) & "',"
SQL = SQL + "DATE_OF_BIRTH=(' " & DateValue(DTPicker1.Value) & " ' ),"
SQL = SQL + "TRANSPORT='" & Trim(Combo10) & "',"
SQL = SQL + "SNAME='" & Trim(Combo5) & "',"
SQL = SQL + "MEAL='" & Trim(Combo11) & "',"
SQL = SQL + "BUSNO='" & Trim(Combo6) & "',"
SQL = SQL + "RUTNO=" & Val(Combo12) & ","
SQL = SQL + "DID='" & Val(Combo7) & "',"
SQL = SQL + "AID='" & Val(Combo8) & "',"
SQL = SQL + "CARD_TYPE='" & Trim(Combo13) & "',"
SQL = SQL + "CARD_NO=" & Val(Text11) & ""
SQL = SQL + "WHERE ROLLNO= " & Val(COMBO4) & ""
MsgBox ("RECORD UPDATED")
End If
End Sub
</code>
Error occurred is invalid month,integrity constraint-parent key not found,invalid  number.

Upvotes: 0

Views: 288

Answers (2)

C-Pound Guru
C-Pound Guru

Reputation: 16358

You need to use the combo box .ItemData property to store your value's ID field:

Do While Not myRecordset.EOF
   myCombo.AddItem myRecordset("MyFieldString").Value
   myCombo.ItemData(myCombo.NewIndex) = myRecordset("MyFieldID").Value
   myRecordset.MoveNext
Loop

To refer to the selected combo value's .ItemData property:

"CARD_TYPE='" & Trim(Combo13.ItemData(Combo13.ListIndex)) & "',"

As for your data formatting issue, not sure about Oracle, but for SQL Server, you have to format the date value as such:

"DATE_OF_BIRTH=(' " & Format$(DTPicker1.Value, "yyyy-mm-dd") & " ' ),"

Upvotes: 0

Ciar&#225;n
Ciar&#225;n

Reputation: 3057

It's kinda hard to answer you question with no idea of the structure of STUDENT_RECORD_DATABASE and what you're updating but there are some things you could look at.

First, when inserting dates into Oracle like this you should use the TO_DATE() function on your date columns. I.e. DATE_OF_BIRTH

Next make sure that all foreign table key constraints are satisfied by your update.

There is no need to update ROLLNO if it is your primary key.

Finally make sure that none of the data you are trying to update is bigger that the size of string (E.g. VARCHAR2) columns. Also, some of these must surely be string columns and they are not bounded by quotes. E.g. CONTACT CONTACT1 CONTACT2 and CARD_NO

Upvotes: 1

Related Questions