Solved

Update Query

Posted on 2010-09-23
12
564 Views
Last Modified: 2012-05-10

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
Comment
Question by:crystalsoft
  • 6
  • 3
  • 3
12 Comments
 
LVL 11

Expert Comment

by:rowansmith
ID: 33742667
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
 
LVL 53

Expert Comment

by:Dhaest
ID: 33742690
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
 
LVL 1

Author Comment

by:crystalsoft
ID: 33742851

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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 1

Author Comment

by:crystalsoft
ID: 33742858

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
 
LVL 53

Expert Comment

by:Dhaest
ID: 33742947
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
 
LVL 1

Author Comment

by:crystalsoft
ID: 33743315

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
 
LVL 11

Accepted Solution

by:
rowansmith earned 125 total points
ID: 33743351
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
 
LVL 53

Assisted Solution

by:Dhaest
Dhaest earned 125 total points
ID: 33743378
>> 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
 
LVL 1

Author Comment

by:crystalsoft
ID: 33743923

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

0
 
LVL 11

Expert Comment

by:rowansmith
ID: 33744022
Let us know how you get on.
0
 
LVL 1

Author Comment

by:crystalsoft
ID: 33744223

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
 
LVL 1

Author Closing Comment

by:crystalsoft
ID: 33744336

Thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Get the latest status 8 32
SQL Error - Query 6 26
SQL Server: Unable to remove duplicate sets in Header/Detail 6 25
Using Third Party DLL with Access VBA 14 18
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question