Hello all, Want to know whether can update data in a DataTable with condition? (like where clause in Query) eg. in DataTable1 currently like this

Col1 Col2 Col3

007 a car
007 b book
012 a calendar
012 b pencil
012 c cup
014 a mouse
014 b phone
055 a pen

but, in Database '012' is already existed. Thus, I want to update '012' to be '012A' in DataTable and then use SqlBulkCopy to insert data into Database. After update DataTable1 will be as below

Col1 Col2 Col3

007 a car
007 b book
012A a calendar
012A b pencil
012A c cup
014 a mouse
014 b phone
055 a pen

Please suggest how to update this without looping through all rows in DataTable1. Thanks and best regards.

Use substring

SELECT c.Name, co.Name
FROM City C
JOIN Country co ON c.ID = Substring(co.CID,2,3)

Thank you for your reply. Group By doesn't work for my case.

Hi thank you for the reply. When I use Group By POL,POD as your suggested Get error for "Container_Code and Qty is invalid in the select list because it is not contained in either an aggregate function or the Group By clause." When I add these two in Group by Clause

Group by POL,POD,container_code,qty

Got the result as same as without using Group By.

Oh! yes such a simple case :) I didn't get that idea. Thank you. But, Any Idea again? To get this data I had to use the following query, the query is already complicated. Is there any way to Sum up the data as you suggested without dumping this data into temp Table?

NGB CCU 0 0 0 0 0 0 0 0 6
NGB CCU 0 0 0 0 0 0 4 0 0
SHA SIN 0 0 0 0 0 0 0 0 4
SHA SIN 0 0 0 0 0 0 0 1 0
SHA SIN 0 0 0 0 0 0 25 0 0

-- Get Refeer, Open Top/Flatrack and Normal
with myTable(POL,POD,Container_Code,qty) as
(Select Distinct POL,POD,Container_Code,SUM(qty) as 'Qty'
from TableF
Where account_name like '%Freight%' and account_name not like '%FREIGHT REBATE'
Group by POL,POD,Container_Code
)
select Distinct POL,POD,
(case when container_code like '%R%20' Then qty else '' end ) as 'R_D20',
0 as 'R_D40',
(case when container_code like '%HR%40' Then qty else '' end ) as 'R_H40',
(case when container_code ='FL20' OR container_code ='OT20' Then qty else '' end ) as 'OT_D20',
(case when container_code ='FL40' OR container_code ='OT40' Then qty else '' end ) as 'OT_D40',
0 as 'OT_H40',
(case when container_code='D20' Then qty else '' end ) as 'N_D20',
(case when container_code='D40' Then qty else '' end ) as 'N_D40',
(case when container_code='HC40' Then qty else '' end ) as 'N_H40'
From myTable
order by POL,POD

Hi All, Any idea to select this data ?

NGB CCU 0 0 0 0 6
NGB CCU 0 0 4 0 0
SHA SIN 0 0 0 0 4
SHA SIN 0 0 0 1 0
SHA SIN 0 0 25 0 0

To be result as below

NGB CCU 0 0 4 0 6
SHA SIN 0 0 25 1 4

Thanks and best Regards.

Hi all, Please assist, When I try to assign a DataTable to a Table that existed in Dataset I got this error : " Property 'Item' is 'ReadOnly'." The Scenario is: After select from TableB with conditions if have got Data then Insert Into TableA.

Dim dTable As New DataTable
Dim ds As New DataSet
Dim dAdp As New OdbcDataAdapter("Select Top 1 * From TableA Where 1<>1 ", ODBCcon)
dAdp.Fill(ds, "TableA")

sQuery = "Select * From TableB Where ...."
dTable = SSLGetDataTable(sQuery)
If dTable.Rows.Count > 0 Then
ds.Tables("TableA") = dTable
dAdp.Update(ds, "TableA")
End If

Thanks and Best Regards

Dear all, Please kindly advise this, my storeprocedure structure is as below. The purpose is, While Insert in TableA if there is any error occour Then I want to ROLLBACK TRAN of TableB that related with the current Transaction of TableA, if not COMMIT TRAN. When execute it I have got the error

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1159.

DECLARE Cur_1 Cursor LOCAL FOR
Select col1,col2,col3 from table1
OPEN Cur_1
FETCH NEXT FROM Cur_1
INTO @col1,@col2,@col3

WHILE @@FETCH\_STATUS = 0 
BEGIN
		
    DELETE FROM FROM TableA Where match with @col1,@col2,@col3
    DELETE FROM FROM TableB Where match with @col1,@col2,@col3

        DECLARE Cur\_2 LOCAL FOR		
     Select \* from table2 Where match with @col1,@col2,@col3
	 OPEN Cur\_2
	 FETCH NEXT FROM  Cur\_2

	 WHILE @@FETCH\_STATUS = 0 
	 BEGIN

BEGIN TRAN
INSERT INTO TableB (data from Cur_2 & Cur_1)

	    FETCH NEXT FROM  Cur\_2
	 END
	 CLOSE Cur\_2
	 DEALLOCATE Cur\_2					
		
    INSERT INTO TableA (data fromCur\_1)
		
    IF @@ERROR = 0
	COMMIT TRAN
    ELSE
	ROLLBACK TRAN

    FETCH NEXT FROM  Cur\_1
	INTO @col1,@col2,@col3
END

CLOSE	Cur\_1
DEALLOCATE Cur\_1

modified on Friday, September 2, 2011 12:35 AM

Thank you for the reply @@CURSOR_ROWS works for me.

Dear all, Please advise how to get the record count of Cursor ?

DECLARE @vendor_cursor CURSOR FOR
SELECT BusinessEntityID, Name
FROM Purchasing.Vendor
WHERE PreferredVendorStatus = 1
ORDER BY BusinessEntityID;

OPEN @vendor_cursor;

FETCH NEXT FROM @vendor_cursor
INTO @vendor_id, @vendor_name;

Thanks and best regards

Arun Jacob wrote:

I hope AutoPostBack is false for CheckBox.

Sure, AutoPostBack is false.

ddl.disabled = "disabled";

this doesn't work too :(

Thanks :)

Arun Jacob wrote:

please check the Id of the dropdown.

ID is correct and I got ddl(not null), the value is "[object HTMLSelectElement]"

Arun Jacob wrote:

where you are calling this javascript method Disable() from?

The calling is at Checkbox onClick, I put this code in Page_load event:

Dim chk As CheckBox = CType(CreateUserWizardStep1.ContentTemplateContainer.FindControl("chkViewAll"), CheckBox)
chk.Attributes.Add("onclick", "Disable()")

Thanks and best regards

Hi thanks for the reply I have already checked ddl, it is not null and the value is "[object HTMLSelectElement]". This is my control

Thanks and best regards

Hi All, Please advise how to enable/disable DropDownList at ClientSide. I have the following function, it does work for textbox and button but not on dropdownlist.

function Disable() {

    var chk = document.getElementById('<%= CType(CreateUserWizardStep1.ContentTemplateContainer.FindControl("chkViewAll"), CheckBox).ClientID %>')
    var btn = document.getElementById('<%= CType(CreateUserWizardStep1.ContentTemplateContainer.FindControl("btnAdd"), Button).ClientID %>')
    var ddl = document.getElementById('<%= CType(CreateUserWizardStep1.ContentTemplateContainer.FindControl("ddList"), DropDownList).ClientID %>')
    var txt = document.getElementById('<%= CType(CreateUserWizardStep1.ContentTemplateContainer.FindControl("txtName"), textbox).ClientID %>')
   
    if (chk.checked == true) {

        btn.disabled = true;
        ddl.disabled = true;
        txt.disabled = true;
    }
    else {
        btn.disabled = false;
        txt.disabled = false;
        ddl.disabled = false;
    }
}

Thanks and best regards

I hope this is a great idea. But to be honest I've never been used function and also not familiar. I'm gonna learn it on my Weekend. Any article or site you want to recommend?:) Thanks again and have a nice weekend.

Thanks again. Like your last example, it's look clear and easy :) But as you have told all selects will execute :(

Many thanks Will try with yours suggestion also.

Absolutely right :thumbsup: I'm at that situation now :((

Thank you for you reply Sorry, I didn't get what you mentioned. This are some of my many different conditions

WHERE Vessel_Code=@Vsl AND Voyage_No=@Voy AND POL=@POL AND (isNull(TDR_D20,0)= 0 And isNull(TDR_D40,0)=0 And isNull(TDR_D45,0)=0 And isNull(TDR_H20,0)=0 And isNull(TDR_H40,0)=0 And isNull(TDR_H45,0)=0) AND (isNull(FL_D20,0) =0 AND isNull(FL_D40,0)=0 AND isNull(FL_D45,0)=0 AND isNull(FL_H20,0)=0 AND isNull(FL_H40,0)=0 AND isNull(FL_H45,0)=0) AND (isNull(CLL_D20,0)=0 AND isNull(CLL_D40,0)=0 AND isNull(CLL_D45,0)=0 AND isNull(CLL_H20,0)=0 AND isNull(CLL_H40,0)=0 AND isNull(CLL_H45,0)=0)

WHERE Vessel_Code=@Vsl AND Voyage_No=@Voy AND POL=@POL AND (isNull(TDR_D20,0)>0 Or isNull(TDR_H20,0)>0 Or isNull(TDR_D40,0)>0 Or isNull(TDR_H40,0)>0 Or isNull(TDR_D45,0)>0 Or isNull(TDR_H45,0)>0) AND (isNull(FL_D20,0) =0 AND isNull(FL_D40,0)=0 AND isNull(FL_D45,0)=0 AND isNull(FL_H20,0)=0 AND isNull(FL_H40,0)=0 AND isNull(FL_H45,0)=0) AND (isNull(CLL_D20,0)=0 AND isNull(CLL_D40,0)=0 AND isNull(CLL_D45,0)=0 AND isNull(CLL_H20,0)=0 AND isNull(CLL_H40,0)=0 AND isNull(CLL_H45,0)=0)

WHERE Vessel_Code=@Vsl AND Voyage_No=@Voy AND POL=@POL AND (isNull(TDR_D20,0)=0 AND isNull(TDR_H20,0)=0 And isNull(TDR_D40,0)=0 And isNull(TDR_H40,0)=0 And isNull(TDR_D45,0)=0 And isNull(TDR_H45,0)=0) AND ((isNull(FL_D20,0)>0 OR isNull(FL_D40,0) >0 OR isNull(FL_D45,0) >0 OR isNull(FL_H20,0) >0 OR isNull(FL_H40,0) >0 OR isNull(FL_H45,0) >0) OR (isNull(CLL_D20,0) > 0 OR isNull(CLL_D40,0) > 0 OR isNull(CLL_D45,0) > 0 OR isNull(CLL_H20,0) > 0 OR isNull(CLL_H40,0) > 0 OR isNull(CLL_H45,0)>0))

Thank you for your reply. So far what I've found CASE are use like below

SELECT Category =
CASE type
WHEN 'popular_comp' THEN 'Popular Computing'
WHEN 'trad_cook' THEN 'Traditional Cooking'
ELSE 'Not yet categorized'
END

UPDATE HumanResources.Employee
SET VacationHours =
( CASE
WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40
ELSE (VacationHours + 20.00)
END
)

But, I have no idea how to use in my query. Could you please give me some samples by using my scenarios? Thanks and best regards