Convert SQl Server SELECT Query to a UPDATE query.

[lmalave]

Hi, I am new to SQL Server and what I am trying to do is convert my working SELECT Query to an UPDATE Query to update the data from a vb.net datgridview. I have looked for a tool or examples but cant find any information that can point me to the correct path.

Select AssetNo [ASSET NO], t2.TYPE [TYPE], t3.Status [STATUS],  t13.PURTYPE [PUR TYPE], MAKE [MAKE], t5.Model [MODEL], SerialNO [SERIAL NO], T6.OS [OS], LICENSEKEY [LIC KEY], MEMSIZE [MEM], HDDSIZE  [HDD], Owner [OWNER], SVP [SVP], t12.Company [VENDOR], CONTRNO [CONTR NO], LNAME [USER LNAME], FName [USER FNAME], UserNO [USER NO], t9.Site [USER SITE], T10.BLDG [USER BLDG], t11.Dept [USER DEPT], Loc [USER LOC], CMNT, PO [PO], Cost [COST], purdate [PUR Date], WrrtyStart [WRRTY START], WrrtyEnd [WRRTY EXP], WrrtyEXT [WRRTY EXT], t14.CONTACT, t15.ADDRESS, T16.POBOX, T17.CITY, T24.STATE, T25.COUNTRY, T18.ZIPCODE, T19.TEL, T20.CEL, T21.FAX, T22.EMAIL, T23.WEBPAGE

                                         From ASSETINFO t1 
                                         INNER Join TypeInfo t2 on t1.TYPEID = t2.TYPEID
                                        INNER Join STATUSINFO t3 on t1.STATUSID = t3.STATUSID
                                        INNER Join MAKEINFO t4 on t1.MAKEID = t4.MAKEID
                                        INNER Join MODELINFO t5 on t1.MODELID = t5.MODELID
                                        INNER Join OSINFO t6 on t1.OSID = t6.OSID
                                        INNER Join OWNERINFO t7 on t1.OwnerID = t7.OwnerID
                                        INNER Join SVPINFO t8 on t1.SVPID = t8.SVPID
                                        INNER Join SITEINFO t9 on t1.SITEID = t9.SiteID
                                        INNER Join BLDGINFO t10 on t1.BLDGID = t10.BLDGID
                                        INNER Join DEPTINFO t11 on t1.DEPTID = t11.DEPTID
                                        INNER Join VENDORINFO t12 on t1.VENDORID = t12.VENDORID
                                        INNER Join PURTYPEINFO t13 on t1.PURTYPEID = t13.PURTYPEID  
                                        INNER Join VENDORINFO t14 on t1.VENDORID = t14.VENDORID
                                        INNER Join VENDORINFO t15 on t1.VENDORID = t15.VENDORID
                                        INNER Join VENDORINFO t16 on t1.VENDORID = t16.VENDORID
                                        INNER Join VENDORINFO t17 on t1.VENDORID = t17.VENDORID
                                        INNER Join VENDORINFO t18 on t1.VENDORID = t18.VENDORID
                                        INNER Join VENDORINFO t19 on t1.VENDORID = t19.VENDORID
                                        INNER Join VENDORINFO t20 on t1.VENDORID = t20.VENDORID
                                        INNER Join VENDORINFO t21 on t1.VENDORID = t21.VENDORID
                                        INNER Join VENDORINFO t22 on t1.VENDORID = t22.VENDORID
                                        INNER Join VENDORINFO t23 on t1.VENDORID = t23.VENDORID
                                        INNER Join STATEINFO t24 on t12.STATEID = t24.STATEID 
                                        INNER Join CNTYOINFO t25 on t12.CNTYID = t25.CNTYID

Regards,

[RudyM]

Are all the fields you're trying to update in ASSETINFO ? Which fields are you trying to update? Whether it's SQL Server, Oracle or PostGRE, SQL itself shouldn't matter.

It'll look something like:

UPDATE table_name
SET column1=value1
    , column2=value2 
    , column3=value3...
WHERE some_column = some_value;

[lmalave]

First of all thank you for your time and response.

All Fields are on ASSETINFO but some are Identity columns.

Your code is working and updating columns that are not Identity columns. Error 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ** 'COMP: COPIER'** to data type int.

Please advise.

Regards,

[RudyM]

Ok, identity columns cannot be updated; this would require exporting table data, rebuilding of tables and re-importing the table data. See https://technet.microsoft.com/en-us/library/aa226401(v=sql.80).aspx.

Also for more on SET IDENTITY_INSERT mytable ON: https://msdn.microsoft.com/en-us/library/ms188059.aspx
I hope this helps.

[RudyM]

Should you expect VARCHAR in that column? Looks like you're updating an INT column with a VARCHAR value, so you'll have to use a numeric value for that column. Unless that VARCHAR you have is supposed to translate to a numeric value, which I've seen used with CASE statements.

[lmalave]

Thank you for pointing me to the correct path. I will take it from here and start the research.

Regards,

[lmalave]

(Should you expect VARCHAR in that column? Looks like you're updating an INT column with a VARCHAR value, so you'll have to use a numeric value for that column.)

No, it is a INT. It worked when I put the number for the column. Since these come from comboboxes on my winforms I have to now try to look SET IDENTITY_INSERT table ON to see if I can pass the int instead of the VARCHAR on the query inside of the vb.net code.

[lmalave]

I figured the Query to update Identity Fields from a WINFORMS Update Button. Comboboxes were bind with a datasource. Thanks RudyM for the initial code.

Heres the SQL Server Query to Update from a WINFORM Update button.

 Query_SQL_Update = New OdbcCommand("UPDATE ASSETINFO SET SerialNO = '" & txtSerialNo2.Text & "' , LICENSEKEY = '" & txtOSLic2.Text & "' , TypeID = '" & cboType2.SelectedValue & "', StatusID = '" & cbostatus2.SelectedValue & "' , MakeID = '" & cboMake2.SelectedValue & "' , ModelID = '" & cbomodel2.SelectedValue & "', OSID = '" & cboOS2.SelectedValue & "', OwnerID = '" & cboAssetOwner2.SelectedValue & "', SVPID = '" &  cboSuportProvider2.SelectedValue & "',  SITEID = '" & cboSite2.SelectedValue & "',  BLDGID = '" & cboBLDG2.SelectedValue & "', DEPTID = '" & cboDept2.SelectedValue & "',  PURTYPEID = '" & cboPurType2.SelectedValue & "' , VENDORID = '" & cboVendor2.SelectedValue & "', MEMSIZE = '" & txtMem2.Text & "' , HDDSIZE = '" & txtHDD2.Text & "' , LNAME = '" & txtlname2.Text & "' , FNAME = '" & txtfname2.Text & "' , UserNO = '" & txtUserID2.Text & "' , LOC = '" & txtLOC2.Text & "' , CMNT = '" & txtComment2.Text & "' , PO = '" & txtPO2.Text & "' , Cost = '" & txtCost2.Text & "' , PURDATE = '" & txtDatePUR2.Text & "' , WrrtyStart = '" & txtWRRTYStart2.Text & "' , Wrrtyend = '" & txtWRRTYEnd2.Text & "' , WrrtyEXT = '" & txtWRRTYEXT2.Text & "' WHERE ASSETNO = '" & txtAssetNo2.Text & "'")