about sql database

Discussion in 'Questions (Windows Mobile)' started by gjoisa, May 12, 2008.

  1. gjoisa

    gjoisa Active Member Licensed User

    I have one sql database in my program. My database contains 9 fields . My database is about latitude longitude of places . Ex : Mumbai , 18 , 58 , N , 72 , 50 , E , 5 , 30 (Placename,lat degree,lat min, lat direction,long degree,long min,long direction,timezone hour,timezone minute) . The problem is , Database accepts the same data if it already exists in database . My requirement is , it should not accept the field1 data if it already exists .:sign0085:
     
  2. glook

    glook Member Licensed User

    If field1 is set as the PRIMARY KEY or a UNIQUE column, then it will not accept duplicate values. Personally I think it is best practice to test for duplicates before attempting an insert, so that you can nicely control the action. This is easily done with a small select:

    cmd.CommandText = "SELECT ROWID from My_table WHERE Name = '" & txtName.Text & "'"
    reader.Value = cmd.ExecuteReader
    havedup = reader.ReadNextRow
    reader.Close
    If havedup Then
    Msgbox(txtName.Text & " already exists!" & crlf & "Must be unique.",,cMsgBoxHand)
    Return
    End If


    Geoff.
     
  3. gjoisa

    gjoisa Active Member Licensed User

    I couldnot understand completely . can you please explain more with fully functioning example ?
     
  4. glook

    glook Member Licensed User

    I'll try to explain (sorry about the delay - been busy with other things).

    A column may be defined as either the PRIMARY KEY or as UNIQUE. This is usually done when you create the table, either by an SQL command from B4ppc or with an external utility.

    This column will then not accept duplicate values. If you try to insert record where the column value already exist, an "error" message will be produced - something like "Abort due to constraint violation column field1 is not unique".

    In my opinion, it is best to handle this event before the error occurs, by testing for the value before attempting any insert - as in the sample code. Your program can then take the appropriate action. This test could be done regardless of how the column is defined.

    Hope that helps,
    Geoff.
     
  5. giannimaione

    giannimaione Well-Known Member Licensed User

    try it

    a little example
     

    Attached Files:

    • sql.sbp
      File size:
      2.3 KB
      Views:
      76
  6. glook

    glook Member Licensed User

    Thanks Gianni, an excellent example.
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice