Check inserted data in a SQL database

Hi im having problems as im new to ASP.NET C#

i have created a button to add details into a SQL database but i want to check the details before i insert the new values from the textboxes

can anyone help...... this is what i have to insert into the database......i just want some help to compare the user name eg... if user name exists a message will appear telling the user to change a different user name

Thanks

private void Button1_Click(object sender, System.EventArgs e)
{
string connectionString = "server=\'(local)\'; trusted_connection=true; database=\'tester\'";
//System.Data.IDbConnection conn = new System.Data.SqlClient.SqlConnection(connectionString);
System.Data.IDbConnection conn = new System.Data.SqlClient.SqlConnection(connectionString);
conn.Open();
string commandString = "INSERT INTO Users (UserName, Password) " + "Values(@.UserName, @.Password)";
//SqlCommand dbCommand = new SqlCommand (commandString, dbconn);
System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand();
//System.Data.SqlClient.SqlCommand myCmd = new System.Data.SqlClient.SqlCommand(queryString, conn);
dbCommand.CommandText = commandString;
dbCommand.Connection = conn;
SqlParameter unParam = new SqlParameter ("@.UserName", SqlDbType.NVarChar, 60);
unParam.Value = txtUser.Text;
dbCommand.Parameters.Add(unParam);
SqlParameter paParam = new SqlParameter ("@.Password", SqlDbType.NVarChar, 60);
paParam.Value = txtPassword.Text;
dbCommand.Parameters.Add(paParam);
dbCommand.ExecuteNonQuery();
conn.Close();
Response.Redirect ("WebForm1.aspx");
}

create a stored procedure, pass in userName and password to it, then make the stored procedure check if user exists and return a value.
eg:

IF NOT EXISTS (SELECT user FROM some_table WHERE <A href="http://links.10026.com/?link=mailto:user=@.userName">user=@.userName</A>)
RETURN 0 /* user doesnt exist */
ELSE
RETURN 1

then

SqlParameter ret = new SqlParameter("@.retVal", SqlDbType.Int, 4);
ret.Direction = ParameterDirection.ReturnValue;

do an ExecuteScalar on your sql command and

int status = (int)sqlCmd.Parameters["@.retVal"].Value;
if (status == 0) { // user created }
else { // failed to create user; user exists }
sql

Character String Query Doesnt Fill Dataset

I'm working in a ASP.NET 2.0 application with a SQL Server 2000 database on the back end. I have a strongly typed dataset in the application that calls a stored procedure for the select. I'm having trouble filling the dataset at runtime though.

I am trying to use a character string query because I setup different columns to be pulled from a table each time and in a different order so my T-SQL looks like this:

set @.FullQuery = 'Select ' + @.FieldsinOrder + ' from tblExample'
exec (@.FullQuery)

This works fine in query analyzer. The results return and display correctly. However, when I run the application, the dataset does not get filled. It is like the results do not output to the application.

If I change the query to be a normal select it works. For example:

select * from tblEmample

That works fine. What is it about a select query setup as a character string and then executed that ASP.NET doesn't like?

try to build your command in ASP and pass it to SQL command command object

like:

Dim

yourCommandAs SqlClient.SqlCommand =New SqlClient.SqlCommand(" 'Select '" + FieldsinOrder + '" from tblExample"', yourconnection)
yourCommand.CommandType = CommandType.Text

Maybe it will work.

When your use Exec to execute query inside query is possible that result from this exec is not visible to ASP.NET code as valid result.

Thanks

char vs. varchar

Greetings,

I have a question. I work on some SQL2k/ASP.NET apps at work. My
predacessor, who created the databases/tables seemed to have liked to
use 'char' for all text fields. Is there a reason why he would have
done this over using varchar? It's a minor annoyance to always have to
RTRIM data and it makes directly making changes to the database more
annoying (with all the pointless trailing spaces)?

I usually use char for fixed string lengths, like state abbreviations
or something, and varchar for strings of unknown length.

Is it a performance issue? Our database doesn't do much traffic, for
the most part.It's not a performance issue unless you're using varchar(1) and the
overhead that incurs and have millions of records and higher traffic
than you probably have.

As a matter of fact, for larger char() fields, they can be slower than
varchar(), because it has to physically store more data pages than if
you used varchar(). If there are more data pages for the same number
of records, things get slower.

Make life easy on yourself and use varchar(). Don't use varchar(1)
though. I have seen people use it.|||The difference between char and varchar are in both storage and performance:

1. Storage wise: char columns have fixed length. If the user supplied value
for the column is less than the fixed length defined in the schema, the
column is padded with 0 at end to make the total length fixed. varchar
doesn't have a fixed length thus no padding is needed. But as the result
varchar columns have to store the size of the data together with the column
data, which takes an extra 2 bytes per varchar column.

2. Performance wise locating char is a little faster than varchar. Since
char columns have fixed length, they are stored in fixed location in a row.
This means locating a char column can directly jump to the fixed location in
a row to read. For varchar column since the size of the data is variable,
they can't be stored in fixed location in a row and rather there is soem
kind of lookup table in the row format to store the location of each varchar
column. This means locating a varchar column has to lookup the location of
the column in the lookup table stored in the row first before jumping to the
location to read. Referencing the lokup table introduces some perofrmance
overhead, especially ifthe lookup table reference causes cache line miss.

In summary, it is a matter of trade-off between padding+faster locate and
2-bytes-overhead-per-column+slower locate when choosing char v.s. varchar.

--
Gang He
Software Design Engineer
Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.
<dmhendricks@.despammed.com> wrote in message
news:1105723409.312275.186390@.f14g2000cwb.googlegr oups.com...
> Greetings,
> I have a question. I work on some SQL2k/ASP.NET apps at work. My
> predacessor, who created the databases/tables seemed to have liked to
> use 'char' for all text fields. Is there a reason why he would have
> done this over using varchar? It's a minor annoyance to always have to
> RTRIM data and it makes directly making changes to the database more
> annoying (with all the pointless trailing spaces)?
> I usually use char for fixed string lengths, like state abbreviations
> or something, and varchar for strings of unknown length.
> Is it a performance issue? Our database doesn't do much traffic, for
> the most part.|||You would never see a practical performance advantage in using char
over varchar, unless you had an extremely high transaction application.
The varchar offset lookup is optimized in-memory. The real bottleneck
is disk I/O, not a few extra CPU cycles from looking up varchar
offsets.

If I have an app that uses a char(80), versus an app that uses a
varchar(80), with an average width of data of 40, the char(80) data is
going to use approximately twice as many data pages to store the data.
That means twice as much disk I/O to read the table, which is where the
real bottleneck is.

I don't believe there is any tradeoff here.|||Gary, see inline

Gary wrote:
> You would never see a practical performance advantage in using char
> over varchar, unless you had an extremely high transaction application.
> The varchar offset lookup is optimized in-memory.

I agree that you won't see any performance degradation here.

> The real bottleneck is disk I/O, not a few extra CPU cycles from looking up varchar
> offsets.

Again, I agree

> If I have an app that uses a char(80), versus an app that uses a
> varchar(80), with an average width of data of 40, the char(80) data is
> going to use approximately twice as many data pages to store the data.
> That means twice as much disk I/O to read the table, which is where the
> real bottleneck is.

This is only true in a perfect world scenario. If there is insufficient
free space to accomodate changes in the varchar data, then change
changes in the varchar(80) data will lead to fragmentation. Changes in
the char(80) data will not lead to fragementation, because any
replacement can be done in-place (assuming columns not part of a
clustered index).

So depending on the fill-factor, number of data changes, etc.
fragmentation will be a little or much greater for varchar compared to
char. This fragmentation is (as you probably know) especially expensive,
because it needs random I/O which is slower than sequential I/O.

I you reindex regularly, and have a sufficient fill factor, then
varchar(80) should always perform better if the average length is only
40.

> I don't believe there is any tradeoff here.

Personally, I don't see a good reason why one would ever choose a
varchar over char when the maximum size is 4 characters or less. For
sizes over 10 characters I tend to choose varchar almost automatically.
For anything between 4 and 10 I really think about the situation before
deciding char or varchar.

Gert-Jan|||Gert-Jan -

I totally agree with you. I simplified the situation quite a bit, but
with all other things being equal, yours is a good "guesstimate".

I ran a test "perfect world" scenario of char(80) vs. varchar(80) (40
char avg len), and both the CPU time and disk I/O were about 40% higher
with the char(80) scenario with 10000 records.

Gary

Char compare to String

hi
I using C# to write a ASP.NET page
and I need to compare a string variable to a Char field in the SQL table in
SQL Server 2000
how can I write a SQL statement to do that?\
I try to use "SELECT Name from Employee Where pass="+password
it shows me an "Invalid column name error"
How can I fix that ?
ThanksYou have to put the value between apostrophes.
"SELECT Name from Employee Where pass = '" + password + "'"
in sql is should look like:
select [name] from employee where pass = '@.#WE$%'
AMB
"Lam" wrote:

> hi
> I using C# to write a ASP.NET page
> and I need to compare a string variable to a Char field in the SQL table i
n
> SQL Server 2000
> how can I write a SQL statement to do that?\
> I try to use "SELECT Name from Employee Where pass="+password
> it shows me an "Invalid column name error"
> How can I fix that ?
> Thanks
>
>|||Hi
name is a reserved SQL keyword, so you have put put it in brackets like
Alejandro indicated.
Regards
Mike
"Alejandro Mesa" wrote:
> You have to put the value between apostrophes.
> "SELECT Name from Employee Where pass = '" + password + "'"
> in sql is should look like:
> select [name] from employee where pass = '@.#WE$%'
>
> AMB
>
> "Lam" wrote:
>|||Thanks Mike. I thought the error was related to the comparison, but also was
trying to indicate what you said (my fault, I did it in t-sql, not in
english).
AMB
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> name is a reserved SQL keyword, so you have put put it in brackets like
> Alejandro indicated.
> Regards
> Mike
> "Alejandro Mesa" wrote:
>