how many chars in varchar record

hello

How could I check how many chars are in record, defined as varchar(8000).
It's obvious that in such defined record could be 1 char to 8000 char. But
what query to SQL database should I post to give information about realy
lenght of this records ?

thanks from advance

AdamSee LEN() and "String Functions" in Books Online.

Simon|||Select len(field)
or
Select DataLength(field)

Madhivanan|||Be careful - the two functions are not the same. LEN() returns the
number of characters after removing trailing blanks, but DATALENGTH()
returns the number of bytes stored. So you can get different results if
trailing blanks are present, if you have varchar vs char, or if you use
Unicode columns - see the code below, and Books Online for more
details.

Simon

create table #t (v varchar(10), nv nvarchar(10))

insert into #t select 'X', 'X'
insert into #t select 'X ', 'X ' -- two trailing blanks

select len(v), datalength(v), len(nv), datalength(nv)
from #t

create table #t1 (c char(10), nc nchar(10))

insert into #t1 select 'X', 'X'
insert into #t1 select 'X ', 'X ' -- two trailing blanks

select len(c), datalength(c), len(nc), datalength(nc)
from #t1|||Thanks Simon

Madhivanan|||thanx for all for valuable information!

best wishes
Adam

How is the Performance of the SQL with .Net?

Hi

I want to insert 1000s of records into SQL Server 2005 Database with some manipulation. So that i put into the For Loop and inserting record.

Inside the loop i am opening the connection and closing after use. The sample code is below

for(int i=0;i<1000;i++)
{

sqlCmd.CommandText = "ProcName";
sqlCmd.Connection = sqlCon;
sqlCmd.Connection.Open():
sqlCmd.ExecuteNonQuery();
sqlCmd.Connection.Close();

}

What my Question is.. How is the Performance of this Code..?? Will is take time to get the Connection and Close the Connection in every itration?

Or Shall I Open the Connection in Begining of the outside loop and close the connection at end of the Loop? will it increase the Performace?

Please clarify me these question.. Thanks in advance.

Hi,

Opening a connection and closing it takes a lot of extra resources, so use following.

1sqlCmd.CommandText ="ProcName";2sqlCmd.Connection = sqlCon;3sqlCmd.Connection.Open():45for(int i=0;i<1000;i++)6{7 sqlCmd.ExecuteNonQuery();8}910sqlCmd.Connection.Close();11

How I can use SqlDataReader?

Hi..

Every time I want to read any record from data base I read it in dataset for example:

SqlConnection con =newSqlConnection(@."Data Source=localhost ;Initial Catalog=university ;Integrated Security=True");

SqlCommand cmd =newSqlCommand("select [User_AuthorityID] from users where [UserID]='" + TextBox1.Text +"' and [UserPassword]='" + TextBox2.Text +"' ", con);

SqlDataAdapter adp =newSqlDataAdapter();

adp.SelectCommand = cmd;

DataSet ds =newDataSet();

adp.Fill(ds,"UserID");

foreach (DataRow drin ds.Tables["UserID"].Rows)

{

user_type = dr[0].ToString();

Session.Add("User_AuthorityID", user_type);

.......

Is there easier way to read data from data base?

How I can use SqlDataReader to do that?

Thanks..

it looks like your returning a single value:

SqlConnection con =newSqlConnection(@."Data Source=localhost ;Initial Catalog=university ;Integrated Security=True");

SqlCommand cmd =newSqlCommand("select [User_AuthorityID] from users where [UserID]='" + TextBox1.Text +"' and [UserPassword]='" + TextBox2.Text +"' ", con);

string value = cmd.ExecuteScalar().ToString();

or

SqlConnection con =newSqlConnection(@."Data Source=localhost ;Initial Catalog=university ;Integrated Security=True");

SqlCommand cmd =newSqlCommand("select [User_AuthorityID] from users where [UserID]='" + TextBox1.Text +"' and [UserPassword]='" + TextBox2.Text +"' ", con);

Session.Add("User_AuthorityID",cmd.ExecuteScalar().ToString(), ;

|||

Example data reader:

SqlConnection con =newSqlConnection(@."Data Source=localhost ;Initial Catalog=university ;Integrated Security=True");

SqlCommand cmd =newSqlCommand("select [User_AuthorityID] from users where [UserID]='" + TextBox1.Text +"' and [UserPassword]='" + TextBox2.Text +"' ", con);

SqlDataReader reader = cmd.ExecuteReader();

string value =string.Empty;

while (reader.Read())

{

value = reader["User_AuthorityID"].ToString();

}

Session.Add("User_AuthorityID", value);

|||

Thanks for that but what should I use if I have more than one value reture from the query?

|||

You can go ahead with the above approach suggested by David.

manal.m.k:

what should I use if I have more than one value reture from the query?

This is pretty straight forward. In the post the while loop goes through all the rows that are returned from the query in your reader. The below example can fetch the column values for each column returned in a row.

while (reader.Read()){ value1 = reader["column1"].ToString(); value2 = reader["column2"].ToString(); . . . valueN = reader["columnN"].ToString();}

How I can pick between 5 - 20 rows in table

Mostly we are using to get 100 or more record with Top operator, but I want to take specific row in between like

10 - 100 or 100 to 200 etc.

How I can pick it. plz give suggestion

See

http://www.aspfaq.com/show.asp?id=2120

|||If you are using SQL 2005, look at ROW_NUMBER function at BOL.

how i can connect to table in SQL?

hi
i working on VB6 + SQL2000
i have table called (tblBuffer)
and want do something in my application everytime table added new record
(its like trigger in SQL)
thats mean if someone add new record to this table, this table call my
application to do some event.
now i made small monitor (working every 15 second) to see if table have new
record to do something, but this idea need network and SQL Resources.
some one have best idea?
--
Best Regards
Tark M. Siala
Development Manager
INTERNATIONAL COMPUTER CENTER (ICC.Networking)
Mobile: +218-91-3125900
E-Mail: tarksiala@.icc-libya.com
Messenger: tarksiala@.hotmail.com
Web Page: http://www.icc-libya.com
Blog: http://spaces.msn.com/tarksiala
======================================In SQL Server 2005 I would recommend the dependency service but in SQL 2000
the only thing I can think of is a trigger that uses sp_OA... commands to
call a COM object you write in VB.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Tark Siala" <tarksiala@.icc-libya.com> wrote in message
news:O5QdxY8YGHA.4760@.TK2MSFTNGP03.phx.gbl...
> hi
> i working on VB6 + SQL2000
> i have table called (tblBuffer)
> and want do something in my application everytime table added new record
> (its like trigger in SQL)
> thats mean if someone add new record to this table, this table call my
> application to do some event.
> now i made small monitor (working every 15 second) to see if table have
> new record to do something, but this idea need network and SQL Resources.
> some one have best idea?
> --
> Best Regards
> Tark M. Siala
> Development Manager
> INTERNATIONAL COMPUTER CENTER (ICC.Networking)
> Mobile: +218-91-3125900
> E-Mail: tarksiala@.icc-libya.com
> Messenger: tarksiala@.hotmail.com
> Web Page: http://www.icc-libya.com
> Blog: http://spaces.msn.com/tarksiala
> ======================================
>