0

I have a problem adding data oracle database,it's show me this message(" https://ufile.io/lzpuj ") Run-time ORA-00904:"EMPCODE": invalid identifier. This is Cody:

Dim connEmp As ADODB.Connection
Dim rsEmp As ADODB.Recordset

Private Sub Command1_Click()
   Set rsEmp = New ADODB.Recordset
   rsEmp.Open "select * from tablebooks where empcode = '" & Text1.Text & "'", 
   connEmp, adOpenKeyset, adLockReadOnly, adCmdText
   If rsEmp.RecordCount <> 0 Then
      MsgBox " ! åÐÇ ÇáßÊÇÈ ãæÌæÏ ÈÇáÝÚá "
      rsEmp.Close
      Set rsEmp = Nothing
      Exit Sub
   Else
      Set rsEmp = New ADODB.Recordset
      rsEmp.Open "select * from tablebooks where empcode = '" & Text1.Text & "'", 
      connEmp, adOpenKeyset, adLockPessimistic, adCmdText
      rsEmp.AddNew

      rsEmp!Book_no = Val(Trim(Text1.Text))
      rsEmp!Book_name = Trim(Text2.Text)
      rsEmp!Author_name = Trim(Text10.Text)
      rsEmp!Edition_no = Val(Trim(Text3.Text))
      rsEmp!Publisher_place = Trim(Text11.Text)
      rsEmp!Part_no = Val(Trim(Text5.Text))
      rsEmp!Book_cost = Trim(Text6.Text)
      rsEmp!Place_book = Trim(Text7.Text)
      rsEmp!Note = Trim(Text9.Text)

      rsEmp!Date_publishing = DTPicker1.Value
      rsEmp!Subject = Trim(Combo4.Text)
      rsEmp!State = Trim(Combo4.Text)
      rsEmp.Update
      connEmp.Execute "commit"
      rsEmp.Close
      Set rsEmp = Nothing
      Label11.Visible = True
      Label11 = " ! ÊãÊ ÇáÅÖÇÝÉ ÈäÌÇÍ "
   End If
End Sub
Improve this question
4
  • 1
    Looks like tablebooks doesn't have a column called empcode. (Or, it was created with double-quoted column names as something like "empcode" or "empCode" - if so, you have to refer to it exactly the same way including case and double quotes.) Commented Feb 1, 2018 at 16:16
  • Yeah, I believe your table it's looking at doesn't have it. Also as some friendly advice, this code is very subject to SQL injection hacks. I recommend using the parameters collection to provide the value of Text1.Text in case someone gets sneaky. Commented Feb 1, 2018 at 16:18
  • 1
    This will be crazy-vulnerable to sql injection attacks. Commented Feb 1, 2018 at 16:35
  • Besides, link ufile.io/lzpuj doesn't work. Commented Feb 1, 2018 at 16:41

1 Answer 1

Reset to default
1

First make sure empcode is exactly the right column name.

Then fix your code. You have two big issues:

  1. It's crazy-vulnerable to Sql Injection attacks.
  2. It tries to re-open the same command on the same connection in the ELSE block for no reason.

The exact fix for #1 depends on which provider you are using (Ole vs Odbc), but this link might help:

Call a parameterized Oracle query from ADODB in Classic ASP

For #2, this is somewhat better:

Dim connEmp As ADODB.Connection
Dim rsEmp As ADODB.Recordset

Private Sub Command1_Click()
   Set rsEmp = New ADODB.Recordset
   'TODO: Use parameterized query here!
   rsEmp.Open "select * from tablebooks where empcode = @empcode '" & Text1.Text & "'", 
   connEmp, adOpenKeyset, adLockReadOnly, adCmdText
   If rsEmp.RecordCount <> 0 Then
      MsgBox " ! åÐÇ ÇáßÊÇÈ ãæÌæÏ ÈÇáÝÚá "
      rsEmp.Close
      Set rsEmp = Nothing
      Exit Sub
   End If

   rsEmp.AddNew

   rsEmp!Book_no = Val(Trim(Text1.Text))
   rsEmp!Book_name = Trim(Text2.Text)
   rsEmp!Author_name = Trim(Text10.Text)
   rsEmp!Edition_no = Val(Trim(Text3.Text))
   rsEmp!Publisher_place = Trim(Text11.Text)
   rsEmp!Part_no = Val(Trim(Text5.Text))
   rsEmp!Book_cost = Trim(Text6.Text)
   rsEmp!Place_book = Trim(Text7.Text)
   rsEmp!Note = Trim(Text9.Text)

   rsEmp!Date_publishing = DTPicker1.Value
   rsEmp!Subject = Trim(Combo4.Text)
   rsEmp!State = Trim(Combo4.Text)

   rsEmp.Update
   connEmp.Execute "commit"
   rsEmp.Close
   Set rsEmp = Nothing

   Label11.Visible = True
   Label11 = " ! ÊãÊ ÇáÅÖÇÝÉ ÈäÌÇÍ "
End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.