|
I wasn't aware of this limitation with the NTEXT datatype. So maybe I'm barking up the wrong tree here, but have you tried using the COALESCE(value_which_may_be_null, value_to_return_in_case_of_first_value_being_null) function? There's also the CASE statement, if you want to check for an empty string as opposed to a NULL. I've been coding in Orcle for some months now, so apologies if i've missed the point of your question.
|
|
|
|
|
Hey
Currently I am using SQL Server, and to get the stored procedure's return value I am writing (c++):
VARIANT v;
v.vt = VT_I2;
v.intVal=0;
ptrCom->Parameters->Item[v]->Value;
What I want to know is, if this command will get the return value in Oracle as well (does the return value is 0, just like in SQL Server)?
Thanks in advance!
|
|
|
|
|
sir actually i am using asp.net with c#.
in my web application i take farpoint spreadsheet control.
On that control i want treeview.
-SectorName
Aggriculture
Company name
NagarjunFertilizer
Auto
TataMotors
Honda
IT
Infosys
Wipro
like this Treeview i want on farpoint spreadsheet.
i have 3 tables
1.tSectorMaster
SectorCode(PK)
Description
2.tAssetMaster
AssetCode(PK)
Descriptionlong
3.tAssetSectorMap
AssetSectorCode(PK)
SectorCode(FK)
AssetCode(FK)
and i want
SectorName
Description
Descriptionlong
Description
Descriptionlong
Descriptionlong
Description
Descriptionlong
Descriptionlong
|
|
|
|
|
OK - so you're using ASP.NET, but you're asking in the SQL forum how to use a third party ASP.NET control ? I'd say the people who sold you the control is who you should be asking, and if they have a SQL forum, it's the wrong place to ask, there, too..
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
|
I have some sql Update command error.
I get the following error message when i run my project.
" ERROR [42000] [Microsoft] [ODBC Microsoft Access Driver] Syntax error in UPDATE statement "
some of my code is following....
Dim CW As Integer
Dim RCW As Integer
------------------------------------------------------------------------------------------------------------------------
Dim level As String = CStr(ComboBox1.SelectedItem.ToString)
Dim exercise As String = CStr(ComboBox2.SelectedItem.ToString)
Dim FWds As String = CStr(lblFalseWords.Text)
Dim SpanTime As String = CStr(hour )
"UPDATE tblSave SET Level=' " & level & " ',Exercise=' " & exercise & " ',CWords=" & CW & ",FWords=' " & FWds & " ',WPM=" & RCW & ",SpanTime=' " & SpanTime & " ' WHERE tblSave.UserName=' " & CurrentUser & " ' "
Please tell me some suggestion.....
|
|
|
|
|
Well my first suggestion is to read up on SQL injection attacks, to understand why you should never allow any system to use this code, ever. Next, I would point out that CW and RCW are used without you giving them a value. However, I'd say your core issue is simply that you're doing your own bit of SQL injection, that values in the strings you're passing through are what's causing this SQL to break. Did you go into the debugger to look at the final SQL string and see what the final SQL line looks like ? Perhaps if you copied and pasted it into SQL Server, it would explain it even better, but the solution is to use stored procs and pass your parameters in a sane way, not by string mashing
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
In "tblSave" table, if CWords and WPM columns are of VarChar Datatype then you have to alter update query as below:
'UPDATE tblSave SET Level='" & level & "',Exercise='" & exercise & "',CWords='" & CW & "',FWords='" & FWds & "',WPM='" & RCW & "',SpanTime='" & SpanTime & "' WHERE tblSave.UserName='" & CurrentUser & "'"
And For Level, Excercise, FWords and SpanTime columns ur giving one space after single quote, if the query runs successfully that space will also insert into the database.
|
|
|
|
|
Now i get success by adding [] in Level field.
Now my success code is
"UPDATE tblSave SET [Level]='" & level & "',Exercise='" & exercise & "',CWords=" & CW & ",FWords='" & FWds & "',WPM=' & RCW & ",SpanTime='" & SpanTime & "' WHERE tblSave.UserName='" & CurrentUser & "'"
Plese tell me how do u think for above metter?
|
|
|
|
|
sounds like "Level" is a keyword and has to be put into [] if used as column.
|
|
|
|
|
how to get row values as columns.
Ismail
Software Engineer
|
|
|
|
|
I believe that's called a 'pivot table'. If you google that, you should find the help you need.
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
Add the following code in your stored Procedure
DECLARE @V1 VARCHAR(1000) --Increase the size depending on your row count
DECLARE @V2 VARCHAR(500)
DECLARE @V3 VARCHAR(500)
SET @V1 = ''
DECLARE C1 CURSOR FOR
SELECT country_name FROM t_master_country
OPEN C1
FETCH C1 INTO @V3
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH C1 INTO @V2
IF @@FETCH_STATUS <> 0
PRINT ' '
ELSE
SET @V1 = @V1 + ' ' + @V2
END
SET @V1 = @V3 + ' ' + @V1
CLOSE C1
PRINT @V1 -- Return @v1
|
|
|
|
|
This i know but I want to take row as coulum and next coulum values as row values
Ismail
Software Engineer
|
|
|
|
|
i dint get you...can u give an example
|
|
|
|
|
I a C# app that queries against an accessDB having the following tables:
tblItems<br />
int ItemID string ItemName ...<br />
1 Item1 ...<br />
2 Item2 ...<br />
3 Item3 ...<br />
...<br />
<br />
tblEvents<br />
int EventID string EventName<br />
1 Event1<br />
2 Event2<br />
3 Event3<br />
4 Event4<br />
....<br />
<br />
<br />
lnkItemEvents<br />
int ItemID int EventID DateTime TimeStamp ...<br />
1 1 Jan 1 ...<br />
1 2 Jan 3 ...<br />
1 4 Jan 4 ...<br />
1 1 Jan 8 ...<br />
1 2 Jan 13 ...<br />
2 1 Jan 2 ...<br />
2 4 Jan 3 ...<br />
2 3 Jan 8 ...<br />
...<br />
<br />
<br />
What I need is a query to return something that looks like this.
ItemName EventName TimeStamp ... about 10 more fields from a half dozen tables.<br />
Item1 Event2 Jan 13 ...<br />
Item2 Event3 Jan 8 ...<br />
...<br />
Based on a bit of googling I believe that to make it work I'll need to use sub queries. One ran on lnkItemEvents to get the most recent time stamp for each item. And a second using the first as a sub query and adding the ItemName, EventName, etc fields to create the final result. I have the first working:
SELECT MAX(TimeStamp) AS Expr1, ItemID<br />
FROM lnkItemEvents<br />
GROUP BY ItemID
But when I try and add it to the WHERE clause of the second I get a syntax parsing error at the WHERE and a second error "You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field." If I remove ITemID from the sub query, I only get the single most recent event. Not the most recent event for each item.
To keep it simple while trying to debug, in my truncated main query I haven't added the SQL to use the ID fields to get the corresponding Names, or the other fields of data I need.
<br />
SELECT ItemID, EventID, TimeStamp<br />
FROM lnkItemEvents<br />
WHERE (SELECT MAX(TimeStamp) AS Expr1, ItemID<br />
FROM lnkItemEvents<br />
GROUP BY ItemID)<br />
GROUP BY ItemID, EventID, TimeStamp
If i remove the WHERE clause it does return the selected fields of every row in the table. I do have a otherwise complete, working, version of the main query that gets everything except for the data linked via lnkItemEvents.
Otherwise [Microsoft is] toast in the long term no matter how much money they've got. They would be already if the Linux community didn't have it's head so firmly up it's own command line buffer that it looks like taking 15 years to find the desktop.
-- Matthew Faithfull
|
|
|
|
|
Try this query
SELECT ItemID, EventID, TimeStamp
FROM lnkItemEvents
INNER JOIN tblItems ON tblItems.ItemID = lnkItemEvents.ItemID
INNER JOIN tblEvents ON tblEvents .EventID = lnkItemEvents.EventID
WHERE TimeStamp = (SELECT MAX(TimeStamp)FROM lnkItemEvents)
I guess this will work
|
|
|
|
|
With a few formatting tweaks (below) to make the DB happy it ran, but it only returns a single row with the single most recent event, not the most recent event for each item.
SELECT tblItems.ItemID, tblEvents.EventID, TimeStamp
FROM ((lnkItemEvents
INNER JOIN tblItems ON tblItems.ItemID = lnkItemEvents.ItemID)
INNER JOIN tblEvents ON tblEvents .EventID = lnkItemEvents.EventID)
WHERE TimeStamp = (SELECT MAX(TimeStamp)FROM lnkItemEvents)
Otherwise [Microsoft is] toast in the long term no matter how much money they've got. They would be already if the Linux community didn't have it's head so firmly up it's own command line buffer that it looks like taking 15 years to find the desktop.
-- Matthew Faithfull
|
|
|
|
|
Putting the subquery as it's own entity so that I could access it like a table in the main one let me get what I needed working.
Otherwise [Microsoft is] toast in the long term no matter how much money they've got. They would be already if the Linux community didn't have it's head so firmly up it's own command line buffer that it looks like taking 15 years to find the desktop.
-- Matthew Faithfull
|
|
|
|
|
I am new to C# and I am trying to convert some old VB6 code to the new platform.
I am importing a text file ".txt" and want to put the data into a database.
In VB6 you could create a recordset for the database in ram and store it there while you are using it and then delete it.
I would like to accomplish the same task in C# but I am having some problems.
The code I am using is as follows:
private ADODB.Connection cn = new ADODB.Connection();
private ADODB.Recordset rs = new ADODB.Recordset();
public frmMainAlarm()
{
InitializeComponent();
rs.Fields.Append ("ID", ADODB.DataTypeEnum.adInteger, 4, ADODB.FieldAttributeEnum.adFldUpdatable, -1);
rs.Fields.Append("Alarm", ADODB.DataTypeEnum.adChar, 255, ADODB.FieldAttributeEnum.adFldUpdatable, -1);
rs.Fields.Append("Message", ADODB.DataTypeEnum.adChar, 255, ADODB.FieldAttributeEnum.adFldUpdatable, -1);
//rs.CursorType = ADODB.CursorTypeEnum.adOpenStatic;
//rs.LockType = ADODB.LockTypeEnum.adLockOptimistic;
rs.Open(null, null, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, -1);
}
I get an error at the "rs.Fields.Append" statement saying it has the wrong variables or data types.
Any help would be greatly appreciated.
Keith Mabey 
|
|
|
|
|
If you are using the same code in C# then it will not compile you have to write C# equivalent code to this vb6 code
1 - write code to read from file and then
2 - make a SqlConnection object
3 - make a SqlCommand object
4 - through SalCommand object call the ExecuteNonQuery method
|
|
|
|
|
I am tring to create a virtual database in RAM (Memory).
Fill the database table with data from a text file.
Then manipulate the data and rewrite it back out as an XML file.
|
|
|
|
|
|
write code
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
