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

Check if MSDE Installation is done

Hi everybody,
I already wrote about our job to build an setup installation including:
..net, msde, my application (using WISE InstallBuilder).
As everyone know, the MSDE setup makes a fork process and return the control
to the next step.
The next step is to install the DB, but I've to wait for it until the MSDE
setup is done.
I read on this NG something about it, but we tried with another solution:
After MSDE setup, wise installer makes a loop until he's able to start the
instance service.
When the Service is started up it's possible to work by OSQL tool.
On Windows98 OS we're not able to check if a service is running or not.
Where are we getting wrong ?
Thank in advance, Pierluigi.
Windows Millennium Edition and Windows 98 do not have a component that
corresponds to Window NT or Windows 2000 services. The SQL Server database
engine and SQL Server Agent run as executable programs on Windows Millennium
Edition and Windows 98.
Dylan
"Pierluigi Terzoli" <pierluigi_terzoli@.hotmail.com> wrote in message
news:uie0TRZZEHA.1264@.TK2MSFTNGP11.phx.gbl...
> Hi everybody,
> I already wrote about our job to build an setup installation including:
> .net, msde, my application (using WISE InstallBuilder).
> As everyone know, the MSDE setup makes a fork process and return the
control
> to the next step.
> The next step is to install the DB, but I've to wait for it until the MSDE
> setup is done.
> I read on this NG something about it, but we tried with another solution:
> After MSDE setup, wise installer makes a loop until he's able to start the
> instance service.
> When the Service is started up it's possible to work by OSQL tool.
> On Windows98 OS we're not able to check if a service is running or not.
> Where are we getting wrong ?
> Thank in advance, Pierluigi.
>
|||Hi Dylan,
Thanks, we just realised about it a few minutes ago.
How can I know when the MSDE installation is done on W/98 using batch file
or Wise InstallBuilder ?
Pierluigi.
"Dylan Kruger" <carmellobear1@.hotmail.com> ha scritto nel messaggio
news:e8DgIDaZEHA.1048@.tk2msftngp13.phx.gbl...
> Windows Millennium Edition and Windows 98 do not have a component that
> corresponds to Window NT or Windows 2000 services. The SQL Server database
> engine and SQL Server Agent run as executable programs on Windows
Millennium[vbcol=seagreen]
> Edition and Windows 98.
> Dylan
> "Pierluigi Terzoli" <pierluigi_terzoli@.hotmail.com> wrote in message
> news:uie0TRZZEHA.1264@.TK2MSFTNGP11.phx.gbl...
> control
MSDE[vbcol=seagreen]
solution:[vbcol=seagreen]
the
>
|||Ok,
after trying several times we were able to find a way to do it.
Maybe could be useful for something:
- we launch the MSDE setup with a log file (an error file)
- after launching we loop until the log file is not in use
So, we can mount the database and install out application.
Thanks again for yours suggestions.
Pierluigi.
"Pierluigi Terzoli" <pierluigi_terzoli@.hotmail.com> ha scritto nel messaggio
news:%23jzdAIaZEHA.384@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Hi Dylan,
> Thanks, we just realised about it a few minutes ago.
> How can I know when the MSDE installation is done on W/98 using batch file
> or Wise InstallBuilder ?
> Pierluigi.
> "Dylan Kruger" <carmellobear1@.hotmail.com> ha scritto nel messaggio
> news:e8DgIDaZEHA.1048@.tk2msftngp13.phx.gbl...
database[vbcol=seagreen]
> Millennium
including:[vbcol=seagreen]
> MSDE
> solution:
> the
not.
>
|||You can handle msde2000 setup process if you use original msde setup files.
In documentation evens says that you can handle callback msi return codes.
Type Callback = mydll!mycallbackfunction.
Bu ti don't know how to do it in vb.
Try this one :
"e:\msde2000setup\installdir\SETUP\SQLRUN01.MS I" /settings
"D:\develop\servis\setup.ini" /i /L*v "D:\develop\servis\MSDE_setup.log"
CmdLine = "e:\msde2000setup\installdir\SETUP\SQLRUN01.MS I" /settings
"D:\develop\servis\setup.ini" /i /L*v "D:\develop\servis\MSDE_setup.log"
You can easily get required dll declarations on the net.
Or in vb :
start.cb = Len(start)
ret = CreateProcessA(0&, CmdLine, 0&, 0&, 1&, NORMAL_PRIORITY_CLASS, 0&,
0&, start, Proc)
i = 0
Do
ret = MsgWaitForMultipleObjects(1&, Proc.hProcess, 0&, INFINITE, _
(QS_POSTMESSAGE Or QS_SENDMESSAGE))
If ret = (WAIT_OBJECT_0) Then
Exit Do 'The process ended.
End If
OpenForms = DoEvents()
If i = 99999 Then
CloseHandle (Proc.hProcess)
sErrMsg = "Not responding"
End If
i = i + 1
Loop
ret = CloseHandle(Proc.hProcess)
ExecCmd = True
Regards
|||And the (poorly documented) /wait switch to the setup.exe command line for
the MSDE installation. This will force the process to wait for the MSDE
installation to finish and return the return code of the MSDE installation.
Jim
"Pierluigi Terzoli" <pierluigi_terzoli@.hotmail.com> wrote in message
news:uie0TRZZEHA.1264@.TK2MSFTNGP11.phx.gbl...
> Hi everybody,
> I already wrote about our job to build an setup installation including:
> .net, msde, my application (using WISE InstallBuilder).
> As everyone know, the MSDE setup makes a fork process and return the
control
> to the next step.
> The next step is to install the DB, but I've to wait for it until the MSDE
> setup is done.
> I read on this NG something about it, but we tried with another solution:
> After MSDE setup, wise installer makes a loop until he's able to start the
> instance service.
> When the Service is started up it's possible to work by OSQL tool.
> On Windows98 OS we're not able to check if a service is running or not.
> Where are we getting wrong ?
> Thank in advance, Pierluigi.
>
sql

Check box in a Combo Box

Hi,
Iam working on a window application using .NET framework 2.0.
I want a check box + some text in a combobox.
User can select multiple items in combo box by using the check
box.
My requirement is : I have a report page. User selects some data
and click on 'Show Report'. Iam showing (I must show) the report also
in the same page. When user selects some other data, and click on 'Show
Report' button new data will be populated.
On this page I have an "Items" Combo box. User may select more than one
Item. Since multi selection property is not there for combo box, Iam
forced to go for ListBox. But Listbox is occupying more space and the
look is bad.
I have seen a CheckedComboBox article here:
http://www.codeproject.com/combobox...&forumid=114...
Its in VC++ I think. I need the same kind of functionality in .NET.
I hope its possible. But dont know how to start with it.
I tried creating a class that is inherited from ComboBox. The class
contains a CheckBox, ItemText Properties. But whats next? Iam unable to
proceed. Could you please help..........
Any ideas or code snippet........ Please............
Regards,
Bharathi Kumar.Please post this to a .Net programming group.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Bharathi Kumar" <bharathidotnet@.gmail.com> wrote in message
news:1149768936.113891.259960@.u72g2000cwu.googlegroups.com...
Hi,
Iam working on a window application using .NET framework 2.0.
I want a check box + some text in a combobox.
User can select multiple items in combo box by using the check
box.
My requirement is : I have a report page. User selects some data
and click on 'Show Report'. Iam showing (I must show) the report also
in the same page. When user selects some other data, and click on 'Show
Report' button new data will be populated.
On this page I have an "Items" Combo box. User may select more than one
Item. Since multi selection property is not there for combo box, Iam
forced to go for ListBox. But Listbox is occupying more space and the
look is bad.
I have seen a CheckedComboBox article here:
http://www.codeproject.com/combobox...&forumid=114...
Its in VC++ I think. I need the same kind of functionality in .NET.
I hope its possible. But dont know how to start with it.
I tried creating a class that is inherited from ComboBox. The class
contains a CheckBox, ItemText Properties. But whats next? Iam unable to
proceed. Could you please help..........
Any ideas or code snippet........ Please............
Regards,
Bharathi Kumar.

Cheapest way to purchase SQL Reporting Services

Within my department we have 1 copy of of vb studio.net standard and 2 copies
of vb studio.net enterprise and run sql server 2000.
One of the guys is writing a system which I have to report on using SQL
Reporting Services. As I don't have any .net product, do I have to purchase a
.net product to then get a development edition of SQL Reporting Services to
write any reports etc.
Looking for the cheapest way to be able to use SQL Reporting Services as I
work in the NHS (lucky me).
Any help will be much appreciated.
Thanks, MarkYour SQL license gives you RS. All you need to develop report is a copy of
VS. The cheapest way to aquire it is VB.net standard.
=?Utf-8?B?bWFya19tZW56aWVz?= <mark_menzies@.discussions.microsoft.com>
wrote in news:DC6ABF54-CFC9-417F-A898-1DB7FCB21549@.microsoft.com:
> Within my department we have 1 copy of of vb studio.net standard and 2
> copies of vb studio.net enterprise and run sql server 2000.
> One of the guys is writing a system which I have to report on using
> SQL Reporting Services. As I don't have any .net product, do I have to
> purchase a .net product to then get a development edition of SQL
> Reporting Services to write any reports etc.
> Looking for the cheapest way to be able to use SQL Reporting Services
> as I work in the NHS (lucky me).
> Any help will be much appreciated.
> Thanks, Mark
>|||Also, RS 2005 comes with a version of VS so no extra purchase is required.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Asher_N" <compguy666@.hotmail.com> wrote in message
news:Xns970F5808B7AFDcompguy666hotmailcom@.207.46.248.16...
> Your SQL license gives you RS. All you need to develop report is a copy of
> VS. The cheapest way to aquire it is VB.net standard.
> =?Utf-8?B?bWFya19tZW56aWVz?= <mark_menzies@.discussions.microsoft.com>
> wrote in news:DC6ABF54-CFC9-417F-A898-1DB7FCB21549@.microsoft.com:
>> Within my department we have 1 copy of of vb studio.net standard and 2
>> copies of vb studio.net enterprise and run sql server 2000.
>> One of the guys is writing a system which I have to report on using
>> SQL Reporting Services. As I don't have any .net product, do I have to
>> purchase a .net product to then get a development edition of SQL
>> Reporting Services to write any reports etc.
>> Looking for the cheapest way to be able to use SQL Reporting Services
>> as I work in the NHS (lucky me).
>> Any help will be much appreciated.
>> Thanks, Mark
>>
>

Cheap solution for .Net/sql giving high availability?

Hi
I am looking into finding a cheap solution for a .Net/sql server web
app that gives high availability and hardware redundancy.
Given that performance isn't a great issue, I was wondering if the
following would work:
Two servers running Windows 2003 Standard and clustered using Network
Load Balancing (NLB). Each server running SQL 2005 Standard and
configured so that one server mirrors the other.
Would this configuration mean if either server failed the application
would keep working on the remaining server without any intervention? If
not what other options do I have? Having one active server with another
as a backup would also be fine but I assume this would require some
maintenance to switch the servers over when a failure occurs.
In terms of the actual servers I was looking at the Dell PowerEdge 860
Quad Core Intel Xeon X3210 2.13GHz as these would only need one sql
server licence per server. Storage using RAID 1 for more redundancy and
a UPS for short power outages. I know I could get cheaper servers but
the largest cost would be SQL server 2005 (approx 4100) so getting a
2 server solution would be Ideal.
Any more suggestions on setup or hardware would be appreciated.
Thanks
Henry
If you're using SQL Server 2005, why not investigate Failover Clustering or
Database Mirroring? Both are available on the standard edition (with
limitations compared to enterprise ed).
In what you described below, you have 2 physically independent databases
running (NLB doesn't mean anything to SQL Server). How would you keep them
both in sync? You mentioned configured so that they mirror each other, did
you mean using DB Mirroring or something else? This is not a trivial issue.
With homegrown solution you need to ensure you don't get into situations
where data is out of sync but your application/users are not aware of it.
HP has some nifty cluster-in-a-box solutions with the DL38x and DL58x
(beefier) platforms. They're relatively inexpensive. Haven't worked with
Dell hardware in some time but they probably have similar offerings also.
If you don't want to deal with Failover Clustering, you can get just the 2
servers and implement DB Mirroring.
In your case, I'd recommend DB Mirroring.
joe.
"Henry" <henry.nelson@.gmail.com> wrote in message
news:1169572502.397351.275050@.m58g2000cwm.googlegr oups.com...
Hi
I am looking into finding a cheap solution for a .Net/sql server web
app that gives high availability and hardware redundancy.
Given that performance isn't a great issue, I was wondering if the
following would work:
Two servers running Windows 2003 Standard and clustered using Network
Load Balancing (NLB). Each server running SQL 2005 Standard and
configured so that one server mirrors the other.
Would this configuration mean if either server failed the application
would keep working on the remaining server without any intervention? If
not what other options do I have? Having one active server with another
as a backup would also be fine but I assume this would require some
maintenance to switch the servers over when a failure occurs.
In terms of the actual servers I was looking at the Dell PowerEdge 860
Quad Core Intel Xeon X3210 2.13GHz as these would only need one sql
server licence per server. Storage using RAID 1 for more redundancy and
a UPS for short power outages. I know I could get cheaper servers but
the largest cost would be SQL server 2005 (approx 4100) so getting a
2 server solution would be Ideal.
Any more suggestions on setup or hardware would be appreciated.
Thanks
Henry

Chart Problem

My y axis of bar chart does not show values when there is more data in table. Is there any way out .please help me
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.Right click on the chart control and go to chart
properties , then go to Y Axis tab and remove all values
under scale.
Hope this helps.
>--Original Message--
>My y axis of bar chart does not show values when there is
more data in table. Is there any way out .please help me
>--
>Posted using Wimdows.net NntpNews Component -
>Post Made from http://www.SqlJunkies.com/newsgroups Our
newsgroup engine supports Post Alerts, Ratings, and
Searching.
>.
>

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:
>