|
Can anyone tell me how to insert a clob into oracle. I need to upload a document and insert it into a clob column
Thanks
|
|
|
|
|
Hello guys!
In my database I need to be able to update my data. Funny huh?
Well I thought if it was possible to create a stored procedure, that only updates the data supplied by the program. So any data not supplied, would simply stay untouched.
An example. We have a table "Persons". The columns are: "PersonId", "FirstName", "LastName", "Age".
Now little Lisa has a birthday, and we need to change her age. So instead of supplying the SP with all the data of Lisa. I would like to be able to only supply the new value of "Age", and of course the "PersonId" for identification. All other values should remain the same.
But some day little Lisa gets married, and we need to change her last name. At that time, I would like to supply the same SP with only the new value "LastName", and all the other data should remain the same.
Is this possible? Or is it just not the way to do things?
Best regards
Soeren
|
|
|
|
|
Why not just create two stored procedures? say ChangeAge and ChangeLastName
You could create some dynamic SQL in a stored procedure and execute a varchar, but you are getting into the territory of opening yourself up to security risks (like SQL Injection Attacks). It is better, if you can, to use a solution that does not involve dynamic SQL. If you do have to use it then you need to implement extra safe guards to prevent an attack.
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
Hi
I also think Colin's idea is good.
well-done Colin.
regards,
chandana
|
|
|
|
|
Hi,
I am using MS SQL SERVER and trying to insert data into a table in which i have a column of varchar type and size is set to 7000, but my problem arises when i have data of length greater than 7000. It gives me an error thereby not inserting records into the database.
Can someone please suggest on how to go about this problem? Can i break data and store it in multiple rows?
Thanks.
|
|
|
|
|
smadan wrote: I am using MS SQL SERVER and trying to insert data into a table in which i have a column of varchar type and size is set to 7000, but my problem arises when i have data of length greater than 7000. It gives me an error thereby not inserting records into the database.
Of course, SQL Server has been told to expect a maximum of 7000 characters.
smadan wrote: Can someone please suggest on how to go about this problem?
You could use a text column type, it allows up to 2Gb of characters.
smadan wrote: Can i break data and store it in multiple rows?
Yes, but I'd recommend you use the text column type. If you are using SQL Server 2005 you can also use varchar(max)
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
Colin Angus Mackay wrote: Of course, SQL Server has been told to expect a maximum of 7000 characters.
I think it is 8000
|
|
|
|
|
Dinuj Nath wrote: Colin Angus Mackay wrote:
Of course, SQL Server has been told to expect a maximum of 7000 characters.
I think it is 8000
No. The original posted said explicitly he'd set it to a varchar(7000) - Therefore the maximum SQL Server will expect is 7000 characters. The maximum that SQL Server 2000 can be told to expect is varchar(8000) but that was not what was specified at part of the original problem.
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
Sorry. I should read before posting.
|
|
|
|
|
Hi,
Thanks for ur reply.
I tried using the text field , but i still get an error that reads "The size given to the column 'Result' exceeds the maximum allowed for any data type (8000)."
Pls suggest how to proceed.
Thanks.
|
|
|
|
|
If u use text u shouldn't get the same error again since text takes as much data as we place
One thing to avoid this is just fire a validation at client side if the data exceeds more than maximum length
"Every morning I check Forbes 40 richest Indians list , if i'm not there I go to work."
|
|
|
|
|
Thanks for ur reply. I used the text field in my stored procedure and it worked.
I was going wrong in the way i was using this datatype.
-- modified at 6:43 Wednesday 8th March, 2006
|
|
|
|
|
Hi!
I have some questions about datasets in VS2005 and SQL Server 2005. I have added a dataset to my project and added all interesting tables in my DB using the 'data source configuration wizard', all works like a charm. But now to the interesting bit, i have changed the design of some of the tables using 'Microsoft SQL Server Mangament studio express'(for example changed one column from varchar(max) to varchar(100)) and now i want the design changes to appear in VS to.
Only way I have mangaged to do just that is to remove the table from the dataset and add it again but then i lose all functionality I have added to the TableAdapter. Is it possible just to update the dataset without losing any added functionality?
//Jesper Miller
-- modified at 5:16 Monday 6th March, 2006
|
|
|
|
|
hello every body,
I am tryin to develop import export utility like that is provided with SQL Server 2000,it would be useful when there will only MSDE is installed.
I cant use SQLDMO`s Back up and Restore functionality as it is for local Instances. I tryin to use link server way but struggling use with all databases.
I am not able to import data from database to given database.
What r other possible ways of doing the above?
regards
tak
|
|
|
|
|
hi,
tusha wrote: develop import export
if you are familier with .Net
Why dont you develop a .Net Windows application that will recieve excel files to import or export excel files from or to the database,or Develop an app to that will display a table or results from a query in a listview and export it to Excel or let the user input the data so the App will save it in the table.
and if you are not may be we can help.
|
|
|
|
|
Hi experts..im new to sql and i wrote this stored procedure for number to character conversion..
intially i have taken one numeric parameter and i have converted into varchar to display in words..
But i want to do one more modifications to this code..if i entered 500 then it shuld be treated
as 500 dollors and should be multplied with 45 and the result should be = 500 * 45.
But im returning varchar value..how to multiply with '45'. im getting data type conversion error..
Here im sending my code upto ten thousand only.. and return statement..
pls solve the problem..and thanks in advance..
@p_Number numeric(18,2) --> Parameter...
begin
declare @Num varchar(20)
declare @Dec varchar(3)
declare @Return varchar(2000)
set @Return = convert (varchar(2000),@p_Number)
set @Dec = substring(convert(varchar(20),@p_Number),len(convert(varchar(20),@p_Number))-2,3)
set @Num = substring(convert(varchar(20),@p_Number),1,len(convert(varchar(20),@p_Number))-3)
declare @Hundred Char(8)
declare @HundredAnd Char(12)
declare @Thousand Char(9)
declare @Lakh Char(5)
declare @Lakhs Char(6)
declare @Crore Char(6)
declare @Crores Char(7)
set @Hundred = 'Hundred '
set @Thousand = 'Thousand '
set @Lakh = 'Lakh '
set @Lakhs = 'Lakhs '
set @Crore = 'Crore '
set @Crores = 'Crores '
set @HundredAnd = 'Hundred and '
if len(@Num) = 1 -- One
begin
set @Return = testuser.fn_GetTextValue_arif(@Num)
end
else
if len(@Num) = 2 -- Ten
begin
set @Return = testuser.fn_GetTextValue_arif(@Num)
end
else
if len(@Num) = 3 -- Hundred
begin
set @Return = testuser.fn_GetTextValue_arif(substring(@Num,1,1)) + @Hundred
if substring(@Num,2,2) <> '00'
set @Return = @Return + 'And '
set @Return = @Return + testuser.fn_GetTextValue_arif(substring(@Num,2,2))
if substring(@Num,2,2) = '00'
set @Return = testuser.fn_GetTextValue_arif(substring(@Num,1,1)) + @Hundred
end
else
if len(@Num) = 4 -- thousand
begin
set @Return = testuser.fn_GetTextValue_arif(substring(@Num,1,1)) + @Thousand
if substring(@Num,2,1) <> '0'
set @Return = @Return + testuser.fn_GetTextValue_arif(substring(@Num,2,1)) + @Hundred
if substring(@num,3,2) <> '00'
set @Return = @Return + 'And '
set @Return = @Return + testuser.fn_GetTextValue_arif(substring(@Num,3,2))
if substring(@num,2,1) = '0' and substring(@num,3,2) = '00'
set @Return = testuser.fn_GetTextValue_arif(substring(@Num,1,1)) + @Thousand
end
else
if len(@Num) = 5 -- Ten Thousand
begin
set @Return = testuser.fn_GetTextValue_arif(substring(@Num,1,2)) + @Thousand
if substring(@Num,3,1) <> '0'
set @Return = @Return + testuser.fn_GetTextValue_arif(substring(@Num,3,1)) + @Hundred
if substring(@num,4,2) <> '00'
set @Return = @Return + 'And '
set @Return = @Return + testuser.fn_GetTextValue_arif(substring(@Num,4,2))
if substring(@num,3,1) = '0' and substring(@num,4,2) = '00'
set @Return = testuser.fn_GetTextValue_arif(substring(@Num,1,2)) + @Thousand
end
if @Dec <> '.00'
set @Return = @Return + 'And ' + testuser.fn_GetTextValue_arif(substring(@Dec,2,2)) + 'Paise '
select '$' + @Return + 'Only' as 'MoneyInWords' ---> Retrun value...
|
|
|
|
|
GBal wrote: But i want to do one more modifications to this code..if i entered 500 then it shuld be treated
as 500 dollors and should be multplied with 45 and the result should be = 500 * 45.
But im returning varchar value..how to multiply with '45'. im getting data type conversion error..
Have you considered performing the multiplication before you convert the number into words?
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
Hi,
I need A command in SQL 2000 so that i can retrive data by partitions just like in MySql with the limit cmd.
I've tried TOP or TOP Percent or Set Rowcount but i need a command that will give me just a portion of the data each time i call it just like this
//first call
select *<br />
from any_table<br />
Limit 0,30
//second call
select *<br />
from any_table<br />
Limit 31,60
Thanks in adv
|
|
|
|
|
select top 30 * from any_table where sort_field > whatever_value order by sort_field
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
Good day everyone,
need help again. on my database i have 5 different record but they have same Venue field name...
i need to get the first record that was inputed to the database and display on the screen.
next, on how to display the 4 remaining records. using sql 2000
|
|
|
|
|
welbert wrote: on my database i have 5 different record but they have same Venue field name...
Do you mean that in a table you have 5 rows with the same Venue column value? I'm trying to clarify because SQL Server 2000 will not permit you to have the same column name 5 times in one table.
welbert wrote: i need to get the first record that was inputed to the database and display on the screen.
Do you have any columns that indicate the order that the row was inserted into the table? e.g. a column representing an incrementing id, or a datetime column that represents when the data was inserted?
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
Good day to all,
Need help on how to display one record with same data to the other record but different reference id... using sql 2000
more power and thanks
-- modified at 20:42 Sunday 5th March, 2006
|
|
|
|
|
welbert wrote: Need help on how to display one record with same data to the other record but different reference id... using sql 2000
You are going to have to explain better because I don't know what you mean.
Do you mean you want to know what rows in a table contain duplicate data but where the primary keys are different?
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
Hai
I've installed SQlServer 2000 and SQLServer 2005 side by side. Instances named as SQL2000 and SQL2005 on installtion time. Now i have to make SQL server 2005 as default instance. So that it will work with connection string
"Server=(local); database=MyDB; uid=sa; pwd="
Now i need to keep my connection string as
"Server=.\SQL2005; database=MyDB; uid=sa; pwd="
How can i solve this problem.
|
|
|
|
|
Hi all,
I hope someone can help with this relatively simple problem.
I am building a timesheet application using ASP.NET C# with Visual
Studio 2003.As it is only a protoype application, my database has been
made in MSDE.
I have a 'View Resources' page, the purpose of which is to show all
employees, taken from that table in the database. This works fine, up
to a point. I have a DataGrid, which is done with a DataSet and a
DataAdapter. I now want the user to be able to 'filter' the DataGrid,
using a text box for input, and a 'search' button. I have tried
different ways of doing this but can't get it to work. I imagine that
it is achieved by running some kind of different SQL statement (with a
% LIKE % operator) in the btnSearch_Click method, and re-binding the
Data Grid after the filter has finished.
So for example the user could enter 'John' in the 'First Name' text box
and click 'search', and the Data Grid would refresh accordingly with
all records LIKE 'John'.
My Page_Load code looks like this currently
-------------------------------------------------------------------------
this.sqlDataAdapter1.Fill(this.dsResources1);
DataGrid1.DataBind();
sqlConnection2.Close();
this.DataGrid1.PageIndexChanged += new
System.Web.UI.WebControls.DataGridPageChangedEventHandler(this.GridPageIndexChanged);
--------------------------------------------------------------------------------------------
Hope someone can help, I'm sure the btnSearch_Click method is where I
need to put the code.
Thanks in advance,
Al
|
|
|
|
|