Deleting Record(s)

Results 1 to 3 of 3

Thread: Deleting Record(s)

  1. #1
    Join Date
    Dec 1969

    Default Deleting Record(s)

    Hi,<BR><BR>I am trying to delete a record from my database table, and get the following error:<BR><BR>Microsoft OLE DB Provider for ODBC Drivers error &#039;80004005&#039; <BR><BR>[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression <BR><BR>After connecting to the database, and table, in the same way that I have done for &#039;deletes&#039; on other pages, I have used the following code:<BR><BR>objRS.MoveFirst<BR>Do While Not objRS.EOF<BR> <BR>If ((Cstr(objRS("PrimaryConsultants")) = "na") AND (Cstr(objRS("2ndConsultants")) = "na") AND (Cstr(objRS("3rdConsultants")) = "na") AND (Cstr(objRS("4thConsultants")) = "na") AND (Cstr(objRS("5thConsultants")) = "na")) then <BR> <BR>objRS.Delete<BR> <BR>End If<BR>objRS.MoveNext<BR>Loop <BR><BR>I have no idea why this isnt working, as the code is almost identical to the code I have used before. The only difference is that the If-Statement above contains more conditions!<BR><BR>Any help will be greatly appreciated,<BR>Thanks, Matt<BR>

  2. #2
    Join Date
    Dec 1969

    Default why not just a SQL statement and then DELETE

    set conn = .... you connection here<BR><BR>SQLstmt = " DELETE from your_table where "<BR>SQLstmt = SQLstmt & " PrimaryConsultants = &#039;na&#039; "<BR>SQLstmt = SQLstmt & " and 2ndConsultants = &#039;na&#039; "<BR>SQLstmt = SQLstmt & " and 3ndConsultants = &#039;na&#039; "<BR>SQLstmt = SQLstmt & " and 4ndConsultants = &#039;na&#039; "<BR>SQLstmt = SQLstmt & " and 5ndConsultants = &#039;na&#039; "<BR><BR>conn.execute(SQLstmt)<BR>

  3. #3
    Join Date
    Dec 1969

    Default Bad column-names + some mumbo jumbo

    I agree with the delete statement as suggested. Your method will be paintakingly slow once you have some volumne in your database. <BR><BR>Furthermore, having columnnames that start with a number is just asking for trouble. I would recommend that you rename them. <BR><BR>Also, having 5 columns that essentially holds the same type of information is not a good database design. It is preferable to use a separate table where you have one row for each consultant. This makes it more flexible and would simplify the above query. If you need to distinguish between different consultants, that should be stored as an attribute.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts