Re: SQL Server Function Dynamic Column - Database Discussion Boards

AJ12317-Jun-06 22:40

17-Jun-06 22:40

Hi There

This has got to be an easy one... I am coming to ADO.NET from an ADO background, so i appologise if it comes across as totally stupid!

All i want to do is insert a new row into a table and pull back the autonumber primary key assigned to it.

What i don't want to have to do is:

1) Fill a Dataset in order to get a DataTable definition.
2) Use stored procedures.

Anybody know the answer off the top of their head? Guess i really need to buy a good ADO.NET book.

Thanks In Advance
Rich

Re: Inserting a Row - Returning the ID

AJ12317-Jun-06 23:09

AJ123

17-Jun-06 23:09

Found my answer :-

There is a good discussion of this at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/manidcrisis.asp[^]

how to give a name to the result table

TheBeginner7717-Jun-06 22:39

TheBeginner77

17-Jun-06 22:39

this is my query
query="SELECT studenti.Nume, studenti.Prenume, cursuri.denumire_curs, note.nota, note.data " +
"FROM studenti INNER JOIN " +
"(profesori INNER JOIN " +
"(cursuri INNER JOIN [note] ON cursuri.id_curs = note.id_curs) " +
"ON profesori.id_profesor = cursuri.id_profesor) ON studenti.id_stud = note.id_stud " +
"WHERE profesori.user_name = '" + user + "'";
i want to give a name to the result table so i can put it in a DataSet
dadapter.Fill(ds,"table name");
Thanks

Re: how to give a name to the result table

Paul Brower19-Jun-06 4:06

Paul Brower

19-Jun-06 4:06

You don't need to know the name to put the results into a dataset. Just look at the overloads available.

using "ntext" (data type in SQL Server)

leanhsang17-Jun-06 18:53

leanhsang

17-Jun-06 18:53

In SQL Server: What is "ntext"? What is it for? How to use?
It is difficult for me to understand. A example will help me better.

For example: I have a Web page. I need display the content of news,include texts that larger than data type of ntext for a column.

What must I do now?

Thanks!

Re: using "ntext" (data type in SQL Server)

Dinuj Nath17-Jun-06 19:59

Dinuj Nath

17-Jun-06 19:59

From bol

ntext: Variable-length Unicode data with a maximum length of 2^(30 - 1)(1,073,741,823) characters. Storage size, in bytes, is two times the number of characters entered. The SQL-92 synonym for ntext is national text.

If your news content will always be less than 8000 characters you can use varchar or nvarchar. Otherwise you will have to use text, ntext or image datatype.

http://msdn2.microsoft.com/en-us/library/ms190258.aspx[^]
http://msdn2.microsoft.com/en-us/library/ms191262.aspx[^]

--
Don't take life seriously because you can't come out of it alive.
-Warren Miller
(From Monty²[^] bio)

SQL Server Function Dynamic Column

smarttom9917-Jun-06 6:25

smarttom99

17-Jun-06 6:25

I am trying to creat a function in SQL Server where it returns a the value of a column in a table where the column is passed as a parameter.

Below is the function where I want to return the value in the user table for column @field. Thanks for your help.

CREATE FUNCTION [dbo].[USER_RETURNCOLUMN] (@userid decimal, @field varchar(200))
RETURNS varchar(2000)
AS
BEGIN

DECLARE @RETURNVALUE varchar(100)

SELECT @RETURNVALUE = ''

SELECT @RETURNVALUE = @field
FROM USER_TABLE
WHERE USER_ID = @userid

RETURN @RETURNVALUE
END

Re: SQL Server Function Dynamic Column

r.stropek20-Jun-06 23:47

r.stropek

20-Jun-06 23:47

If you ask me no chance to do this in a user defined function. You would need dynamic SQL with exec( @stmt ) and that is not allowed in UDFs. Can't you use a stored proc? You can use dynamic SQL there.

Regards,
Rainer.

Rainer Stropek
Visit my blog at http://www.cubido.at/rainers

sorting

militiaware17-Jun-06 5:48

militiaware

17-Jun-06 5:48

i want to sort a table by string column

this column contains data in this format ddd/dddd (ex: 100/2006 or 6/2006)
so when i order the table by this column, it will sort like this:
1/2006
11/2006
111/2006
2/2006
22/2006

but i want to sort like this
1/2006
2/2006
11/2006
22/2006
111/2006

so, is there any why to sort like this on a vb.net application not in the database?

Faris Madi
Nothing Comes Easy (N.C.E.)

Re: sorting [modified]

Colin Angus Mackay17-Jun-06 6:11

Colin Angus Mackay

17-Jun-06 6:11

SELECT * 
FROM MyTable 
ORDER BY CASE LEN(column) 
         WHEN 6 THEN '00'+column 
         WHEN 7 THEN '0'+column 
         WHEN 8 THEN column 
         ELSE null END

Scottish Developers events:
* .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

-- modified at 12:13 Saturday 17th June, 2006

Re: sorting

Eric Dahlvang19-Jun-06 5:38

Eric Dahlvang

19-Jun-06 5:38

SELECT *<br />
FROM MyTable <br />
ORDER BY REPLICATE('0',8-LEN(mycolumn)) + mycolumn

----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

connect to remote database

militiaware16-Jun-06 22:27

militiaware

16-Jun-06 22:27

i'm using SQL SERVER 2000 with .net 2003

my problem is when i connect to the other computer that has the database
it keep telling me "Login faild on Computername\Guest"

and i've installed the SQL SERVER 2000 on the other computer on mixmode authentication.

Why he want to login to Guest Account... while Guest account is off.

i'm using this connection string:
Me.SQLConnection_Search.ConnectionString = "packet size=4096; Trusted_Connection=true; Persist Security Info=true; SERVER= 192.168.0.1; User ID= sa; Password=sa;initial catalog=aqar; "

so where is the problem. WTF | :WTF:

Faris Madi
Nothing Comes Easy (N.C.E.)

Re: connect to remote database

Colin Angus Mackay17-Jun-06 2:41

Colin Angus Mackay

17-Jun-06 2:41

You have trusted_connection = true and yet you are not wanting to use a trusted connection as you supply a user name and password. As trusted connections are considered more secure it tries to log you on using a trusted connection, but it fails.

SQL Server Reporting Services Excel Export [modified]

Navdeep Bhardwaj16-Jun-06 21:27

Navdeep Bhardwaj

16-Jun-06 21:27

Hi
I need to modify the excel export feature in Reporting Services... The issue is that client needs to have chart in excel format after exporting so that user can manually change the values in excel sheet to check the effect in the chart dynamically. Can anybody tell me how I can do that? I know it would require the creation of chart in BIFF format. But could not find out how?

If someone knows any .NET library that can help me in that please let me know. I don't want to go for SoftArtisian Office Writer as it has certain limitation as it doesn't supports the grouping of data in RS.

Thanks

-- modified at 3:29 Saturday 17th June, 2006

SQLite for small software

yarp16-Jun-06 19:18

yarp

16-Jun-06 19:18

I'm considerind adding database capabilities to my software. First step is selecting the proper database. The database should have 0 maintenance, be light weight. I would also appreciate a single file binary database and not BDF/Paradox like ASCII files.
At the moment I think SQLite could make it.

Once you made a choice - especially with small companies like mine - it is difficult to switch to another one. So since I'm not a database specialist, I would appreciate advices or suggestions on other candidates, and possibly pros and cons.

Thanks,

Yarp
http://www.senosoft.com/

Re: SQLite for small software [modified]

Garth J Lancaster16-Jun-06 20:09

Garth J Lancaster

16-Jun-06 20:09

SQLite is a proprietary format embedded database .. that sort of works against 'switching to another one' unless, you use a database abstraction layer in between your code and the database layer.

The most obvious one I can think of is ODBC, and there is an (?experimental) ODBC driver for Sqlite. You're still not off the hook with ODBC, because of SQL variations (Ie SQLite doesnt support all of the SQL-92 Standard) , but it could make it 'easier' to use ODBC

There is a database abstraction layer (cant remember the name off-hand, will look it up and try and post back), that MAY (memory is a bit dim) let you select between different database 'back-ends' - my guess is you would still have to stick to fairly basic SQL unless its translated somewhere for each potential database back-end.

.. but thats the approach I would use if there was any hint of having to change the database back-end ..

(if it counts for anything, I use SQLite in some projects - I also use SQLite and Oracle 9i in other projects)

hth, 'g'

ps .. your p3DO explorer looks great - wish I knew how to do some of that stuff - not enough time ....

-- modified at 2:10 Saturday 17th June, 2006

Re: SQLite for small software

yarp16-Jun-06 23:32

yarp

16-Jun-06 23:32

Thanks for your detailed answer Garth,
Since my needs are rather basic I will most probably only use SQL basic commands. So I think I can cope with those SQLite limitations.
Also although I will design it for multi-users use it'll mostly be used by single user.
For the driver, yes it is a choice in itself but the thiner layer, the better.

> your p3DO explorer ... - wish I knew how to do some ... - not enough time
Yes it takes a lot of time, fortunately (or not Wink | ;)

I'm single and I love 2D/3D graphics. This gives me the will to go ahead.
Looking for the proper library/component is a hard part of the project (I made severall costly mistakes in the past).

Yarp
http://www.senosoft.com/

Re: SQLite for small software

Dinuj Nath17-Jun-06 20:03

Dinuj Nath

17-Jun-06 20:03

You can try out Firebird. It can be used both as standalone or as a full server. I think you can convert a standalone database to a server very easily. For more check http://www.firebirdsql.org/[^]

--
Don't take life seriously because you can't come out of it alive.
-Warren Miller
(From Monty²[^] bio)

Re: SQLite for small software

yarp18-Jun-06 7:13

yarp

18-Jun-06 7:13

Thank you for your advice. I already used Interbase in the past, it is a good database but not light enough for what I intend to do.
I would most probably choose it for professional clients running on a network (I've read SQLite is not good for transactionnal network access), but my needs are too basic even for Interbase.
Note - I don't say for FireBird since I don't know it.

Yarp
http://www.senosoft.com/

TCP/IP and Pipes for and ODBC Connection [modified]

FlorianS16-Jun-06 7:43

FlorianS

16-Jun-06 7:43

Hi,

I have a problem with and app that is connected to an SQL 2000 database
The user got a new server dual CPU 3.2 GHz Pentium 4 with 2G memory and the app is slower compare with the old machine a dual CPU 755 MHz Pentium 3 with 512 memory.

If you use the TCP/IP connection in ODBC driver, the new machine is very slow.
If you use Name Pipe in ODBC, both machine a doing well.
The old machine it is working well with both TCP/IP and Pipes.

Does anybody have any idea why it is such a big difference between TCP/IP and pipes for the new machine? The different is from 1 minute to 10 minutes for an operation.

Any thoughts are welcome

Thanks,

Florian

-- modified at 15:01 Friday 16th June, 2006

Re: TCP/IP and Pipes for and ODBC Connection

Garth J Lancaster16-Jun-06 20:14

Garth J Lancaster

16-Jun-06 20:14

my guess is Named-Pipes and ODBC are better at buffering, while TCP/IP works to a point then TCP/IP becomes saturated and network performance decreases.

(but there's a lot of variables here)

'g'

Selecting rows by earliest date

Stefan R16-Jun-06 3:37

Stefan R

16-Jun-06 3:37

I have a table "tbl_order" with to the fields "customer_number" and "date". there can be multiple rows with the same customer_number, but the date fields are allways unique.

these or potential rows in my table:

cust_no | date | item
---------------------------
21 2006-01-06 shovel
21 2006-03-05 axe
21 2006-05-05 widget
45 2006-05-01 fish tank
45 2006-05-02 ...

For every cust_no i want to retreive only the row with the earliest date. How can this be done?

what i want is this:
------------------------
21 2006-01-06 shovel
45 2006-05-01 fish tank

Any help would be greatly appreciated

/ Stefan

Re: Selecting rows by earliest date

Colin Angus Mackay16-Jun-06 4:06

Colin Angus Mackay

16-Jun-06 4:06

SELECT q.cust_no, earliest_date, item
FROM tbl_order as t
INNER JOIN 
(
    SELECT cust_no, MIN(date) as earliest_date
    FROM tbl_order
    GROUP BY cust_no
) AS q ON t.cust_no = q.cust_no AND t.date = q.earliest_date

Update database with multiple rows of dataset

amittinku16-Jun-06 2:56

amittinku

16-Jun-06 2:56

//---------Below is code:----------------------------------------------------

string conn_str="Provider=MSDAORA.1;User ID=navuser2;Data Source=orcl;Password=navuser2";
OleDbConnection conn=new OleDbConnection(conn_str);
conn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.CommandText = "select * from test";
cmd.Connection = conn;
OleDbDataAdapter adap=new OleDbDataAdapter(cmd);
DataSet dtset=new DataSet();
adap.Fill(dtset,"test");
string ins= "insert into TEST values(30,'900')";
DataTable tab1;
tab1=dtset.Tables["test"];
DataRow r1;
r1=tab1.NewRow();
adap.InsertCommand=new OleDbCommand(ins,conn);
tab1.Rows.Add(r1);
adap.Update(dtset,"test");
conn.Close();
//------------------------------------------------------------------------

1.The above code is working fine for insert statements. How to use the above code in case if we are trying to update database with more than 1 updated row of dataset. e.g. instead of using "insert into TEST values(30,'900')";, use "update test set emp=100". Here we donot know how many rows will be updated in dataset.

SQL Server 2005 Expiration Date

Brendan Vogt15-Jun-06 23:51

Brendan Vogt

15-Jun-06 23:51

Hi,

I'm running SQL Server evaluation version, and it sould ne close to the 6 month expiry date. How do I check when it expires?

Regards,
ma se

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.