Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 574
  • Last Modified:

Update Query


Experts

I am using following Query for Insert record,
Value getting from Array List ( valuelist)

The problem is if record exist then its giving me error cause of primary key in database.
so now how can i update record
using update query

insert query working fine
but for update i am little confuse how it will work and how to get value .....
What update query will be

If valuelist <> "" Then
''''''''INSERTING DATA FROM CSV FILE'''''
    con.Execute "insert into B_Detail (BtyNo,Mode,BDate,TkNo,Code,....etc 58 fields) values (" & valuelist & ")"
    Kill "D:\Crystal\" & BNo & ".csv"
'        valuelist = Empty
End If

Open in new window

0
crystalsoft
Asked:
crystalsoft
  • 6
  • 3
  • 3
2 Solutions
 
rowansmithCommented:
You must first of all check to see if the record exists using a SELECT statement, you only need to count the number of records returned.

If the Count > 0 then you can do an Update to that record.

If the COUNT==0 then you do an insert.

-Rowan
0
 
DhaestCommented:
I should consider first checking if the value already exists or not (based on your primary key )
Do all the fields change in your update or just a few ?

If valuelist <> "" Then
   ' check if record already exist
   Dim myRecCount As New ADODB.Recordset
   Set myRecCount = con.Execute("select * from B_Detail where BtyNo = " & valueList(0))

   If myRecCount.EOF = true and myRecCount.BOF = true  Then
    ' it does not exist
        con.Execute "insert into B_Detail (BtyNo,Mode,BDate,TkNo,Code,....etc 58 fields) values (" & valuelist & ")"
   else
        con.Execute("update  B_Detail set Mode ='" & valuelist(1) & "' where BtyNo = " & valueList(0))
   end if
    Kill "D:\Crystal\" & BNo & ".csv"

End If
0
 
crystalsoftAuthor Commented:

Thanks for quick reply

rowansmith: >> Records already exists, that's why duplicate msg occur from DB.

Dhaest: >> Record already exist, i want to change all the fields where BtyNo = ' & BNo & '
valuelist have all the fields now for update query how can i assign values and fields

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
crystalsoftAuthor Commented:

valuelist code is attached
For i = 0 To UBound(props)

If Not IsNull(props(i)) And props(i) <> "" Then

    If valuelist = "" Then
        If i = 0 Then
            BNo = props(i)
        End If
        valuelist = valuelist & "'" & props(i) & "'"
    Else
        
        If i = 0 Then
            BNo = props(i)
        End If
        
        valuelist = valuelist & ",'" & props(i) & "'"
        
    End If

End If

Next i

Open in new window

0
 
DhaestCommented:
I'm not sure if you can pass a valueList to an updatequery, like you did for the insert-query.
If that's not the case, you'll have to build the sql in code ....
0
 
crystalsoftAuthor Commented:

Dhaest: > You mean to say, i have to put all the 56 fields value in separate 56 variable and then write update query like..

con.Execute "UPDATE B_Detail set Check = " & BD_CHK & ", LsheetNo = " & BD_LSNo & ", Status_Rec = " & BD_Status & ", Ref_Gm_No = '" & BD_RefGmNo & "' where BiltyNo = " & BName3 & ""
0
 
rowansmithCommented:
You can not pass a comma separated list to UPDATE.  You need to do a SET statement for each and every field in the table.

You could also consider deleting the offending record and then inserting a new record as part of a transaction.

So:

con.BeginTrans();
con.Execute("delete from B_Detail where BtyNo = " & valueList(0);");
con.Execute("insert into B_Detail (BtyNo,Mode,BDate,TkNo,Code,....etc 58 fields) values (" & valuelist & ")";");
con.CommitTrans();

0
 
DhaestCommented:
>> You mean to say, i have to put all the 56 fields value in separate 56 variable and then write update query like..

Yes, and confirmed by rowansmith (who gave also a possible workaround)
0
 
crystalsoftAuthor Commented:

Both suggestion can work on my scenario,
Now let me test..

0
 
rowansmithCommented:
Let us know how you get on.
0
 
crystalsoftAuthor Commented:

Its working perfectly,
Instead of use 56 variable, i will go for (rowansmith's Suggestion) delete record first and then insert it,

as usual -------- > TTHHAANNKKSS    (:-D)

0
 
crystalsoftAuthor Commented:

Thanks
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now