Hi - I'm using .net2, and have a gridview, populated by a SQL Datasource (Edit, Insert, Delete, Select).
Like we all used to do with the datagrid, I've added text boxes into the footer, and a link button, which I'd like to use to fire the Update command.
How do I get the link button to trigger the update command?
Thanks, Mark
in you link button click event handler call
datasourceName.Update();
Hop this help
I want to do something like
if "Product_code" is Null
insert dbo.t_Shopping_cart (Product_code,Name,Price,Product_group,Quantity,Total)
select Product_code,Name,Price,Product_group,Quantity=1,Yhteensa=1
FROM dbo.t_Shopping_cart
WHERE Product_code='AHTU140213'
else
update dbo.t_Shopping_cart
set Quantity=Quantity+1
where Product_code='AHTU140213'
In short: I cant test existence of a record.
Regards
Leif
IF @.Product_codeISNULLBEGININSERT dbo.t_Shopping_cart (Product_code,Name,Price,Product_group,Quantity,Total)SELECT Product_code,Name,Price,Product_group,Quantity=1,Yhteensa=1FROM dbo.t_Shopping_cartWHERE Product_code= @.Product_CodeENDELSEBEGINUPDATE dbo.t_Shopping_cartSET Quantity=Quantity+1WHERE Product_code=@.Product_CodeEND|||
IFNOT EXISTS(SELECT *FROM dbo.t_Shopping_cartWHERE Product_code=@.Product_code)BEGININSERT dbo.t_Shopping_cart (Product_code,Name,Price,Product_group,Quantity,Total)SELECT Product_code,Name,Price,Product_group,Quantity=1,Yhteensa=1FROM dbo.t_Shopping_cartWHERE Product_code= @.Product_CodeENDELSEBEGINUPDATE dbo.t_Shopping_cartSET Quantity=Quantity+1WHERE Product_code=@.Product_CodeEND|||
This looks interesting. I will certainly try it later tonight.
A couple of short questions.
By the way, is it @.Product_code and not Product_code in "IF @.Product_codeISNULL"line.
If it is possible to write Yhteensa=1, which means total=1 by the way, why isn't this possible "Yhteensa=Price*Quantity". I tried it in Management Studio.
Many thanks
Leif
the problem that i faced is, i need to sum up another time according to the year. the above statement will shoe the amount paid. i need to sum up the amount for each year. i've tried to use sum function, but it gives me error. Please help me to solve this problem. Thanks for all advise. I would appreciate it very much.
The simplified query....
(SELECT Convert(varchar,ISNULL(SUM(TC.amount),0)) FROM tb_payment AS TC
WHERE TC.transactionid = TA.TransactionID AND TC.deletedby IS NULL) as AmountPaid
Here you can use join rather subquery, if you post the full query i may help you to tune..
|||FROM tb_pointtransactions AS TA
JOIN tb_transactionsummaries tr on tr.transactionid=TA.transactionid
JOIN tb_memberships m ON TA.membershipid = m.membershipid
JOIN tb_users u ON u.UserID = m.UserID
LEFT JOIN tb_salesstatus s ON s.id = TA.salesstatus
INNER JOIN tb_saletransactions AS TB ON TA.TransactionID = TB.TransactionID AND TA.deletedby IS Null
WHERE membershipid = '1' ORDER BY year
i'm really appreciate your help, ManiD. I'm still a newbie in programming field. With your help, my learning path will be wonderful. Thank you very much.
I want to select the parent_topic_id from the inner query but i am getting an error though....can someone show me how i should structure these queries please?!?! thank you.....Do I not need to include the Order By or is it in the wrong place?
Msg 1033, Level 15, State 1, Line 25
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
Select sti.parent_topic_id
From
(
SELECT sti.parent_topic_id,
st.description_short,
sti.sort_order
FROM sam_topic_items sti
INNER JOIN sam_topic st ON sti.topic_id = st.id
WHERE EXISTS
(
SELECT 1
FROM sam_topic_items sti1
INNER JOIN sam_topic st1 ON sti1.topic_id = st1.id
WHERE st1.type = 'Topic'
AND sti.parent_topic_id = sti1.parent_topic_id
AND sti.sort_order = sti1.sort_order
GROUP BY sti1.parent_topic_id, sti1.sort_order, st1.Type
HAVING COUNT(sti1.sort_order) > 1
)
Order By sti.parent_topic_id
)
Put the [ORDER BY] after the last parenthesis.
|||
I still got "Incorrect Syntax near order"? What am I missing?
Select sti.parent_topic_id
From(
SELECT sti.parent_topic_id,
st.description_short,
sti.sort_order
FROM sam_topic_items sti
INNER JOIN sam_topic st ON sti.topic_id = st.id
WHERE EXISTS
(
SELECT 1
FROM sam_topic_items sti1
INNER JOIN sam_topic st1 ON sti1.topic_id = st1.id
WHERE st1.type = 'Topic'
AND sti.parent_topic_id = sti1.parent_topic_id
AND sti.sort_order = sti1.sort_order
GROUP BY sti1.parent_topic_id, sti1.sort_order, st1.Type
HAVING COUNT(sti1.sort_order) > 1
)
)
Order By sti.parent_topic_id
|||That was a step forward.
Now the next part of the problem is that when you create a derived table, you have to give it a name. (Recall my remarks in your previous posts about using derived tables?) So add a table name after the last parenthesis and before the [ORDER BY]. (A lot of folks use dt for derived table.)
Also, since the column Parent_Topic_ID is a member of the derived table, you will have to preface the outer query to use the derived table name -NOT [sti].
<ShoulderTap>
As a note: In my opinion, you are jumping ahead too fast, and not taking the time to practice, learn, and understand the help that we are giving you. It is very important to completely understand how to properly use derived tables.
</ShoulderTap>
I changed it to that and it works...I forgot all about the derived tables....opps...I am practicing as I learn....I am just trying to find out how to do things within SQL instead of minipulating SQL through VB.net or C#...You what though? Thank for you help and patience..(Did i spell that right)....
Select dt.parent_topic_id
From
(
SELECT st.id,sti.parent_topic_id,
st.description_short,
sti.sort_order
FROM sam_topic_items sti
INNER JOIN sam_topic st ON sti.topic_id = st.id
WHERE EXISTS
(
SELECT 1
FROM sam_topic_items sti1
INNER JOIN sam_topic st1 ON sti1.topic_id = st1.id
WHERE st1.type = 'Topic'
AND sti.parent_topic_id = sti1.parent_topic_id
AND sti.sort_order = sti1.sort_order
GROUP BY sti1.parent_topic_id, sti1.sort_order, st1.Type
HAVING COUNT(sti1.sort_order) > 1
)
) dt
Order By parent_topic_id
Can anyone tell me or point me in the direction of how I can store select query results to a variable in VB.NET? Im using the SqlDataSource control with dropdowns and textboxes for searching. I want to store the search results in a variable on the button click event.
Dim dv As DataView = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), DataView)
Dim dr As DataRow
dr = dv.Table.Rows(0)
session("variable1") = dr("ColumnName1").ToString()
Dim strVar2 as string = dr("ColumnName2").ToString()
etc.
|||Depending on what you are trying to store, there are several options. First, gather the data in a single variable. This can be a collection, array, some user-defined object or even a dataset.
Once you have the variable, you may do several things with it:
Session - If the resultsets will be different for each user, you may want to use session. Session variables exist for a certain amount of time, then disappear when that time expires. If the session expires, the variable will become NULL (Nothing in VB.NET).
eg: Session["DataSet"] = dataSet;
Cache - If the resultsets will be the same for each user, you may want to use cache. The Page.Cache object can store data for as long as you need it, only refreshing the data after an amount of time has elapsed or if an event triggers the cache to be emptied.
eg: Page.Cache.Add("DataSet", dataSet, cacheDependency,DateTime.Now.AddHours(5));
ViewState - If the resultsets will not be used for long, but need to be used across a postback, you may want to use viewstate. Viewstate is used to maintain state at the page level. That being said, it does not exist on another page, but does exist when you postback to the same page. Use this if you only need the data for a limited amount of time, but you plan on spending awhile on the same page.
eg: ViewState.Add("DataSet", dataSet);
Hope this helps.
Try this:
select @.Data=imageCol from Table1 where id=3
mbanavige wrote:
Try this:
select @.Data=imageCol from Table1 where id=3
Hey guys...i've got a problem wif my stored procedures...in which my page keep repeating the same data...so to counter this problem i use SELECT DISTINCT instead of just SELECT..but the problem is when i change SELECT into SELECT DISTINCT...the page will not be display,page error...for your information my stored procures was auto generate from some security sofware...so can guys help me out with my code...![Sad [:(]](http://pics.10026.com/?src=/emoticons/emotion-6.gif)
SELECT DISTINCT t.TargetID,
'V' RecordType,
t.TargetDNSName [Target DNS Name],
t.TargetIPAddress [Target IP],
t.TargetIPDisplay [Target IP Display],
t.TargetOSName,
t.TargetOSRevisionLevel,
v.SecChkID,
v.Severity,
sc.TagName [Tag Name],
sc.ChkBriefDesc [Tag Brief Desc],
sc.ChkName [Tag Chk Name],
CONVERT(NVARCHAR(4000),sc.ChkDetailDesc) [Tag Detail Desc],
CONVERT(NVARCHAR(4000),r.RemedyDesc) [Remedy],
o.ObjectID,
o.ObjectTypeDesc [Object Type Desc],
o.ObjectName [Object Name],
s.SensorDataID,
a.AttributeName,
a.AttributeValue,
NULL [Port],NULL [Service Name],NULL [Protocol]
FROM #Vulns v
INNER JOIN TargetHost t (NOLOCK)
ON v.TargetID = t.TargetID
INNER JOIN (SecurityChecks sc (NOLOCK)
LEFT OUTER JOIN Remedies r (NOLOCK)
ON sc.SecChkID = r.SecChkID)
ON v.SecChkID = sc.SecChkID
INNER JOIN ObjectView o (NOLOCK)
ON v.ObjectID = o.ObjectID
LEFT OUTER JOIN SensorData1 s WITH (NOLOCK, INDEX(SensorData1_AK3))
ON v.ObservanceID = s.ObservanceID
AND s.Cleared = 'n'
LEFT OUTER JOIN SensorDataAVP a (NOLOCK)
ON s.SensorDataID = a.SensorDataID
AND a.AttributeValue IS NOT NULL
AND a.AttributeValue != ''
UNION ALL
|||
Thanks Alvin, i get u...but if u see it clearly...it's was already on at the fourth lines
INNER JOIN(SecurityChecks sc (NOLOCK)
LEFT OUTER JOIN Remedies r (NOLOCK)
ON sc.SecChkID = r.SecChkID)
ON v.SecChkID = sc.SecChkID
Thanks Alvin, i get u...but if u see it clearly...it's was already on at the fourth lines
INNER JOIN(SecurityChecks sc (NOLOCK)
LEFT OUTER JOIN Remedies r (NOLOCK)
ON sc.SecChkID = r.SecChkID)
ON v.SecChkID = sc.SecChkID
i check in sql analyzer...but there was no error...just simply it won't display anythings,but when i delete DISTINCT...it will be fully functioning with data redundancy
|||Well, that makes no sense. The removal of the DISTINCT keyword inand of itself would not cause a query to stop returning results. My guess is that more changed than the removal of the DISTINCT keyword.|||As far as I am aware the query isn't correct either.
INNER JOIN(SecurityChecks sc (NOLOCK)
LEFT OUTER JOIN Remedies r (NOLOCK)
ON sc.SecChkID = r.SecChkID)
ON v.SecChkID = sc.SecChkID
should be
INNER JOIN(SELECT * FROMSecurityChecks sc (NOLOCK)
LEFT OUTER JOIN Remedies r (NOLOCK)
ON sc.SecChkID = r.SecChkID) somename
ON v.SecChkID = sc.SecChkID
I know what UNION ALL does, but it isn't syntatically correct as the last thing in a query. UNION ALL *MUST* be followed by a second query in which case it does caddre said. It combines the output of both queries and doesn't remove the duplicates.
Toss this in Query Analyzer:
SELECT '1'
UNION ALL
You get a syntax error.
|||You get syntax error because there is no UNION in you query but the poster's UNION ALL is at the end of 5 JOIN operations and 3 AND operators so SQL Server maybe ignoring some of the JOINS because the CONVERT sometimes makes tables UNION compatible and running UNION ALL instead. I am not saying the query cannot be modified but it works now so replace the UNION ALL with AND operators to start.|||3rd syntax error:
INNER JOIN TargetHost t (NOLOCK)
should be
INNER JOIN TargetHost t WITH (NOLOCK)
Infact that's done a lot in the query, and they are all wrong. The query as posted won't run period, and should toss up an error. If it is not, it's only because with so many syntax errors you have thoroughly confused the T-SQL parser.
|||The poster said the code runs, you are running DML(data manipulation language) without DDL(data definition language). That is the reason SQL forums always ask for DDLs so when the person post some DDL you can run it in RedGate or BMC. I have TOAD here because I run Oracle.