SQL database problem
-
Hi all, I have a problem. I have a database "Users", there are usernames, passwords, and other information, and I'm doing on the registration page - I want to be sure there will be no duplicity of usernames - I searched the old comments but it didn't help me, I have written this code: Dim vilemConn As New SqlClient.SqlConnection vilemConn.ConnectionString = "...." 'Here I set the Select command Dim selectCMD As SqlClient.SqlCommand = New SqlClient.SqlCommand("SELECT * FROM Users", vilemConn) Dim vilemDA As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter vilemDA.SelectCommand = selectCMD Dim cb As SqlClient.SqlCommandBuilder = New SqlClient.SqlCommandBuilder(vilemDA) vilemConn.Open() Dim vilemDS As DataSet = New DataSet vilemDA.Fill(vilemDS) Dim vilemDR As DataRow = vilemDS.Tables(0).NewRow() vilemDR("jmeno") = Server.HtmlEncode(txtJmeno.Text) vilemDR("prijmeni") = Server.HtmlEncode(txtPrijmeni.Text) vilemDR("trida") = Server.HtmlEncode(txtTrida.Text) vilemDR("email") = Server.HtmlEncode(txtEmail.Text) vilemDR("login") = Server.HtmlEncode(txtLogin.Text) vilemDR("heslo") = Server.HtmlEncode(inputHeslo1.Value) vilemDR("skin") = Server.HtmlEncode(dropdownSkin.SelectedValue) vilemDR("image") = Server.HtmlEncode(txtImage.Text) vilemDS.Tables(0).Rows.Add(vilemDR) vilemDA.Update(vilemDS) vilemDS.Reset() vilemDA.Fill(vilemDS) vilemConn.Close() Although I don't know what SQL command I should use (and please send me all the syntax of it, with txtLogin.Text added, because I have problems with this - I am absolute beginner in SQL) So I don't know what SQL command I should use, where should I place it and how to execute it, because I don't want to rewrite the Select command for later adding of the new record. This is my experiment completed with msdn and this forums, either correct it or don't mind it, it doesn't work 'Nastavení příkazu SQL pro vyloučení duplicity Dim NoDuplicityCMD As SqlClient.SqlCommand = New SqlClient.SqlCommand("SELECT COUNT(*) FROM Users WHERE login= " & txtLogin.Text, vilemConn) 'NoDuplicityCMD.CommandText = "SELECT COUNT(*) FROM Users WHERE login=@txtLogin.Text" Dim a As Integer a = NoDuplicityCMD.ExecuteNonQuery() 'is it good place to execute the query and should I write a= noduplicity... or only noduplicitycm
-
Hi all, I have a problem. I have a database "Users", there are usernames, passwords, and other information, and I'm doing on the registration page - I want to be sure there will be no duplicity of usernames - I searched the old comments but it didn't help me, I have written this code: Dim vilemConn As New SqlClient.SqlConnection vilemConn.ConnectionString = "...." 'Here I set the Select command Dim selectCMD As SqlClient.SqlCommand = New SqlClient.SqlCommand("SELECT * FROM Users", vilemConn) Dim vilemDA As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter vilemDA.SelectCommand = selectCMD Dim cb As SqlClient.SqlCommandBuilder = New SqlClient.SqlCommandBuilder(vilemDA) vilemConn.Open() Dim vilemDS As DataSet = New DataSet vilemDA.Fill(vilemDS) Dim vilemDR As DataRow = vilemDS.Tables(0).NewRow() vilemDR("jmeno") = Server.HtmlEncode(txtJmeno.Text) vilemDR("prijmeni") = Server.HtmlEncode(txtPrijmeni.Text) vilemDR("trida") = Server.HtmlEncode(txtTrida.Text) vilemDR("email") = Server.HtmlEncode(txtEmail.Text) vilemDR("login") = Server.HtmlEncode(txtLogin.Text) vilemDR("heslo") = Server.HtmlEncode(inputHeslo1.Value) vilemDR("skin") = Server.HtmlEncode(dropdownSkin.SelectedValue) vilemDR("image") = Server.HtmlEncode(txtImage.Text) vilemDS.Tables(0).Rows.Add(vilemDR) vilemDA.Update(vilemDS) vilemDS.Reset() vilemDA.Fill(vilemDS) vilemConn.Close() Although I don't know what SQL command I should use (and please send me all the syntax of it, with txtLogin.Text added, because I have problems with this - I am absolute beginner in SQL) So I don't know what SQL command I should use, where should I place it and how to execute it, because I don't want to rewrite the Select command for later adding of the new record. This is my experiment completed with msdn and this forums, either correct it or don't mind it, it doesn't work 'Nastavení příkazu SQL pro vyloučení duplicity Dim NoDuplicityCMD As SqlClient.SqlCommand = New SqlClient.SqlCommand("SELECT COUNT(*) FROM Users WHERE login= " & txtLogin.Text, vilemConn) 'NoDuplicityCMD.CommandText = "SELECT COUNT(*) FROM Users WHERE login=@txtLogin.Text" Dim a As Integer a = NoDuplicityCMD.ExecuteNonQuery() 'is it good place to execute the query and should I write a= noduplicity... or only noduplicitycm
First and foremost - For SECURITY. NEVER pass a value directly from a user control in to a SQL Command. This is suseptable to injection attack. What would happen if, in the txtLogin box, I had written
''; shutdown with nowait;
The answer is that your CommandText would fully read:
SELECT COUNT(*) FROM Users WHERE login=''; shutdown with nowait;
This would run your SELECT and then immediately terminate your SQL Server Process - any other queries would fail and no new connections would be permitted. And you (or your DBA) would have to manually restart the SQL Server. Okay - now that you know more about security I'll get on with your question... I would always interact with the database using stored procedures. You add a layer of security there if you only permit access to the stored procedures, then all you can ever do is what the stored procudures can do.
CREATE PROCEDURE RegisterUser(
@UserName varchar(64),
@Password carchar(64)
)
ASIF EXISTS(SELECT * FROM Users WHERE login=@UserName)
BEGIN
RAISERROR('The user '+@UserName+' already exists', 16, 1);
RETURN;
ENDINSERT INTO Users (login, password)
VALUES(@UserName, @Password)
GOThen from your .NET application you can do something like this
SqlCommand cmd = new SqlCommand("RegisterUser", vilemConn);
cmd.Parameters.Add(new SqlParameter("@UserName", txtLogin.Text);
cmd.Parameters.Add(new SqlParameter("@Password", txtPassword.Text); // Don't know what this really is.
try
{
cmd.ExecuteNonQuery();
}
catch(SqlException sqlEx)
{
// If the command is duplicated then sqlEx.Message will contain the message defined in the
// RAISERROR in the stored procedure and the sqlEx.Number will be 50000 (for a custom error)
}This SQL and .NET code will register a user if one does not already exist, if one does exist an error is raised which you can then handle as appropriate. Sorry, but I can only read VB.NET code, I don't know enough VB.NET to create the VB code without referring to a lot of books. Hopefully you will see what I am trying to achieve with the C# code - remember it is the Framework and not the language that is important here. You will also need to add as parameters and extend the stored procedure for the other items in your table. Does this help?
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell Can't manage
-
First and foremost - For SECURITY. NEVER pass a value directly from a user control in to a SQL Command. This is suseptable to injection attack. What would happen if, in the txtLogin box, I had written
''; shutdown with nowait;
The answer is that your CommandText would fully read:
SELECT COUNT(*) FROM Users WHERE login=''; shutdown with nowait;
This would run your SELECT and then immediately terminate your SQL Server Process - any other queries would fail and no new connections would be permitted. And you (or your DBA) would have to manually restart the SQL Server. Okay - now that you know more about security I'll get on with your question... I would always interact with the database using stored procedures. You add a layer of security there if you only permit access to the stored procedures, then all you can ever do is what the stored procudures can do.
CREATE PROCEDURE RegisterUser(
@UserName varchar(64),
@Password carchar(64)
)
ASIF EXISTS(SELECT * FROM Users WHERE login=@UserName)
BEGIN
RAISERROR('The user '+@UserName+' already exists', 16, 1);
RETURN;
ENDINSERT INTO Users (login, password)
VALUES(@UserName, @Password)
GOThen from your .NET application you can do something like this
SqlCommand cmd = new SqlCommand("RegisterUser", vilemConn);
cmd.Parameters.Add(new SqlParameter("@UserName", txtLogin.Text);
cmd.Parameters.Add(new SqlParameter("@Password", txtPassword.Text); // Don't know what this really is.
try
{
cmd.ExecuteNonQuery();
}
catch(SqlException sqlEx)
{
// If the command is duplicated then sqlEx.Message will contain the message defined in the
// RAISERROR in the stored procedure and the sqlEx.Number will be 50000 (for a custom error)
}This SQL and .NET code will register a user if one does not already exist, if one does exist an error is raised which you can then handle as appropriate. Sorry, but I can only read VB.NET code, I don't know enough VB.NET to create the VB code without referring to a lot of books. Hopefully you will see what I am trying to achieve with the C# code - remember it is the Framework and not the language that is important here. You will also need to add as parameters and extend the stored procedure for the other items in your table. Does this help?
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell Can't manage
Hi, thank you for your reply, it helped a lot. However, I wasn't able to run it. It says this error: Line 1: Incorrect syntax near 'RegisterUser'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'RegisterUser'. Source Error: Line 82: cmd.Connection.Open() Line 83: cmd.Parameters.Add("@Password", inputHeslo1.Value) Line 84: cmd.ExecuteNonQuery() Line 85: ' Line 86: 'Dim vilemDA As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter Source File: c:\inetpub\wwwroot\VilemWeb\Register.aspx.vb Line: 84 Here's a code:
Dim vilemConn As New SqlClient.SqlConnection vilemConn.ConnectionString = "...." Dim cmd As New SqlClient.SqlCommand("RegisterUser", vilemConn) cmd.Connection.Open() cmd.Parameters.Add("@UserName", txtLogin.Text) cmd.Parameters.Add("@Password", inputHeslo1.Value) cmd.ExecuteNonQuery() cmd.Connection.Closeand the stored procedure is here:ALTER PROCEDURE RegisterUser ( @UserName varchar(64), @Password varchar(64) ) AS IF EXISTS(SELECT * FROM Users WHERE login=@UserName) BEGIN RAISERROR('The user **CurrentUser** already exists', 16, 1); RETURN END INSERT INTO Users (login, heslo) VALUES(@UserName, @Password) RETURNBTW: It said an error when I had written in RAISEERROR '+@UserName+' instead of CurrentUser, don't you know why? -
Hi, thank you for your reply, it helped a lot. However, I wasn't able to run it. It says this error: Line 1: Incorrect syntax near 'RegisterUser'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'RegisterUser'. Source Error: Line 82: cmd.Connection.Open() Line 83: cmd.Parameters.Add("@Password", inputHeslo1.Value) Line 84: cmd.ExecuteNonQuery() Line 85: ' Line 86: 'Dim vilemDA As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter Source File: c:\inetpub\wwwroot\VilemWeb\Register.aspx.vb Line: 84 Here's a code:
Dim vilemConn As New SqlClient.SqlConnection vilemConn.ConnectionString = "...." Dim cmd As New SqlClient.SqlCommand("RegisterUser", vilemConn) cmd.Connection.Open() cmd.Parameters.Add("@UserName", txtLogin.Text) cmd.Parameters.Add("@Password", inputHeslo1.Value) cmd.ExecuteNonQuery() cmd.Connection.Closeand the stored procedure is here:ALTER PROCEDURE RegisterUser ( @UserName varchar(64), @Password varchar(64) ) AS IF EXISTS(SELECT * FROM Users WHERE login=@UserName) BEGIN RAISERROR('The user **CurrentUser** already exists', 16, 1); RETURN END INSERT INTO Users (login, heslo) VALUES(@UserName, @Password) RETURNBTW: It said an error when I had written in RAISEERROR '+@UserName+' instead of CurrentUser, don't you know why?For the first problem it looks like I missed the line:
cmd.CommandType = CommandType.StoredProcedure
You need to place this between creating the
cmdobject and thecmd.ExecuteNonQuery. chodicimrkev wrote: It said an error when I had written in RAISEERROR '+@UserName+' instead of CurrentUser, don't you know why? Ah! Just realised that RAISERROR doesn't like strings being concatenated inside it. Weird!?! Anyway, tryDECLARE @error_message varchar(256);
SET @error_message='The user '+@UserName+' already exists.';
RAISERROR(@error_message, 16, 1);Hopefully it should work out now.
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
-
For the first problem it looks like I missed the line:
cmd.CommandType = CommandType.StoredProcedure
You need to place this between creating the
cmdobject and thecmd.ExecuteNonQuery. chodicimrkev wrote: It said an error when I had written in RAISEERROR '+@UserName+' instead of CurrentUser, don't you know why? Ah! Just realised that RAISERROR doesn't like strings being concatenated inside it. Weird!?! Anyway, tryDECLARE @error_message varchar(256);
SET @error_message='The user '+@UserName+' already exists.';
RAISERROR(@error_message, 16, 1);Hopefully it should work out now.
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
I take my hat off to you!!! Your better help than MSDN. Thanks a lot, it works definitely. I am going to change all in my app to stored procedures, it's more clear than I did before. Thanks!:)
-
For the first problem it looks like I missed the line:
cmd.CommandType = CommandType.StoredProcedure
You need to place this between creating the
cmdobject and thecmd.ExecuteNonQuery. chodicimrkev wrote: It said an error when I had written in RAISEERROR '+@UserName+' instead of CurrentUser, don't you know why? Ah! Just realised that RAISERROR doesn't like strings being concatenated inside it. Weird!?! Anyway, tryDECLARE @error_message varchar(256);
SET @error_message='The user '+@UserName+' already exists.';
RAISERROR(@error_message, 16, 1);Hopefully it should work out now.
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
-
I take my hat off to you!!! Your better help than MSDN. Thanks a lot, it works definitely. I am going to change all in my app to stored procedures, it's more clear than I did before. Thanks!:)
chodicimrkev wrote: I take my hat off to you!!! Your better help than MSDN. I'm not sure about that, I have to admit I've had 8 years to get used to the style. But thanks for the compliment, it is very welcome. Have a great weekend. :-D
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
-
Steven Campbell wrote: Another programmer converted to using stored procedures... you're an evil genius! :laugh: Mwwaaa haaa haa haa haaaaaaa!! :laugh:
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
-
First and foremost - For SECURITY. NEVER pass a value directly from a user control in to a SQL Command. This is suseptable to injection attack. What would happen if, in the txtLogin box, I had written
''; shutdown with nowait;
The answer is that your CommandText would fully read:
SELECT COUNT(*) FROM Users WHERE login=''; shutdown with nowait;
This would run your SELECT and then immediately terminate your SQL Server Process - any other queries would fail and no new connections would be permitted. And you (or your DBA) would have to manually restart the SQL Server. Okay - now that you know more about security I'll get on with your question... I would always interact with the database using stored procedures. You add a layer of security there if you only permit access to the stored procedures, then all you can ever do is what the stored procudures can do.
CREATE PROCEDURE RegisterUser(
@UserName varchar(64),
@Password carchar(64)
)
ASIF EXISTS(SELECT * FROM Users WHERE login=@UserName)
BEGIN
RAISERROR('The user '+@UserName+' already exists', 16, 1);
RETURN;
ENDINSERT INTO Users (login, password)
VALUES(@UserName, @Password)
GOThen from your .NET application you can do something like this
SqlCommand cmd = new SqlCommand("RegisterUser", vilemConn);
cmd.Parameters.Add(new SqlParameter("@UserName", txtLogin.Text);
cmd.Parameters.Add(new SqlParameter("@Password", txtPassword.Text); // Don't know what this really is.
try
{
cmd.ExecuteNonQuery();
}
catch(SqlException sqlEx)
{
// If the command is duplicated then sqlEx.Message will contain the message defined in the
// RAISERROR in the stored procedure and the sqlEx.Number will be 50000 (for a custom error)
}This SQL and .NET code will register a user if one does not already exist, if one does exist an error is raised which you can then handle as appropriate. Sorry, but I can only read VB.NET code, I don't know enough VB.NET to create the VB code without referring to a lot of books. Hopefully you will see what I am trying to achieve with the C# code - remember it is the Framework and not the language that is important here. You will also need to add as parameters and extend the stored procedure for the other items in your table. Does this help?
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell Can't manage
Just wanted to say that I thought your reply here was really good. I agree - I would never build a database that didn't work through stored procedures. I was shocked to find out that MySQL does not support them, I immediately stopped considering it when I found out. Christian I have drunk the cool-aid and found it wan and bitter. - Chris Maunder
-
Just wanted to say that I thought your reply here was really good. I agree - I would never build a database that didn't work through stored procedures. I was shocked to find out that MySQL does not support them, I immediately stopped considering it when I found out. Christian I have drunk the cool-aid and found it wan and bitter. - Chris Maunder
Christian Graus wrote: I thought your reply here was really good Thanks. Christian Graus wrote: I was shocked to find out that MySQL does not support them Really! I heard also that it doesn't support transactions either (although there is supposed to be some sort of plug in that gives this functionality). That is the biggest showstopper for me.
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!