33503

Count from SQL Rows into C# textbox

Question:

Hi there its the first time to use stackoverflow so hi every one L)

i'm a beginner into C# forms i take it as a fun hobby.

SqlCommand comm = new SqlCommand("SELECT COUNT(*) FROM Members where sponser = " +textbox1.text+"'", connection); Int32 count = (Int32)comm.ExecuteScalar(); textbox2.Text ="Found "+ count+" Members;

well its just a mix between 2 codes i have got from google xD how ever the error appear here <strong><em>textbox2.Text ="Found "+ count+" Members;</em></strong>

Answer1:

protected void Page_Load(object sender, EventArgs e) { lb1.Text = GetRecordCount(textbox2.Text).ToString(); } private int GetRecordCount(string myParameter) { string connectionString = ConfigurationManager.ConnectionStrings["DBConnection"].ToString(); Int32 count = 0; string sql = "SELECT COUNT(*) FROM members WHERE sponsor = @Sponsor"; using (SqlConnection conn = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.Add("@Sponsor", SqlDbType.VarChar); cmd.Parameters["@Sponsor"].Value = myParameter; try { conn.Open(); count = (Int32)cmd.ExecuteScalar(); } catch (Exception ex) { } } return (int)count; }

Answer2:

There are a couple of things wrong with this line of code:

textbox2.Text ="Found "+ count+" Members;

First of all, there's a syntax error. You never close the second set of quotes. You'd do so like this:

textbox2.Text ="Found "+ count+" Members";

However, string concatenation like this is still a little messy. You have two literal strings and you're trying to add them to an integer, which isn't entirely intuitive (and probably slower than it needs to be). Instead, consider using a formatting string:

textbox2.Text = string.Format("Found {0} Members", count);

This will take the value from count (which is an integer) and, internally to the string.Format() function, discern its string representation and insert it into the placeholder in the formatted string.

<strong>UPDATE:</strong> That takes care of the compile-time errors. Now you're going to get a run-time error from this:

SqlCommand comm = new SqlCommand("SELECT COUNT(*) FROM Members where sponser = " +textbox1.text+"'", connection);

As soon as you try to execute that SQL statement you're going to get an error from the database because the resulting query has a syntax error:

SELECT COUNT(*) FROM Members where sponser = some text'

You're missing the opening single-quote for the parameter. Something like this:

SqlCommand comm = new SqlCommand("SELECT COUNT(*) FROM Members where sponser = '" +textbox1.text+"'", connection);

<strong>However</strong>, and this is <strong>important</strong>, you're still not done. This line of code is <em>wide open</em> to a very common and easily exploitable vulnerability called <a href="http://en.wikipedia.org/wiki/SQL_injection" rel="nofollow">SQL Injection</a>. You'll want to move away from direct string concatenation and use parameters for your SQL queries. Something like this:

SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM Members where sponser = @sponser"); cmd.Parameters.Add("@sponser", textbox1.text); Int32 count = (Int32)comm.ExecuteScalar();

Know that there is still a lot more you can do to improve this, which is all worth learning over time. Things you can look into are:

<ul><li>Checking and validating user input (textbox1.text) before you even try to use it in a SQL query.</li> <li>Checking the output of comm.ExecuteScalar() before trying to directly cast it to an Int32 (this would give you a runtime error if it returns anything other than an integer for some reason).</li> <li>Consider using something like Linq to Sql in place of ADO.NET components as it does a lot more for you with less code on your part.</li> </ul>

Answer3:

You are missing a closing " at the end:

textbox2.Text ="Found "+ count+" Members";

Answer4:

You code is vulnerable to <a href="http://msdn.microsoft.com/en-us/magazine/cc163917.aspx" rel="nofollow">SQL Injections</a>. Please consider using <a href="http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.aspx" rel="nofollow">Parameters</a>.

private int GetMemberCount(string connectionString, string sponsor) { using(var connection = new SqlConnection(connectionString)) using(var command = connection.CreateCommand()) { command.CommandText = "SELECT COUNT(*) FROM members WHERE sponsor = @Sponsor"; command.Parameters.AddWithValue("@Sponsor", sponsor); return Convert.ToInt32(command.ExecuteScalar()); } } //Usage var sponsor = textbox1.text; var count = GetMemberCount(connectionString, sponsor); textbox2.Text = string.Format("Found {0} Members", count);

Recommend

  • How can I disable the color of an image using css?
  • generic extension method
  • Like buttons and like box vanished
  • Processing certain form elements to build objects
  • What does the maxJsonLength property refer to?
  • “Error converting data type nvarchar to int” when executing Stored Procedure and reading return valu
  • Datatable class in asp.net core
  • Servicestack ORMLite/Massive managing multiple DataTables with Expandos / Dynamic?
  • How can I fix CA2100 Review SQL queries for security vulnerabilities issue
  • Parse returned C# list in AJAX success function
  • Execute Success but num_rows return 0 [PHP-MySQL]
  • How to export MS Access table into a csv file in Python using e.g. pypyodbc
  • MS SQL Server 2008 :Getting start date and end date of the week to next 8 weeks
  • Displaying Data From Multiple MySQL Tables
  • How to find data from last week in MySQL
  • limited threads in soapUI free version
  • HikariPool-1 - Unusual system clock change detected, soft-evicting connections from pool
  • Does for loop open and close a database connection on each iteration?
  • Can't connect Entity Framework to local SQL Server Express
  • View Paypal shopping cart contents on my site
  • Run script file on remote server
  • Timeout a query
  • SqlCommand back up Database
  • Identifier too long in Oracle
  • Converting query results into DataFrame in python
  • VSCode change debug shell to bash on windows
  • Error processing multiple files
  • Eliminate partial duplicate rows from result set
  • NetLogo BehaviorSpace - Measure runs using reporters
  • Spring security and special characters
  • How to redirect a user to a different server and include HTTP basic authentication credentials?
  • Can I make an Android app that runs a web view in Chrome 39?
  • JSON with duplicate key names losing information when parsed
  • C# - Getting references of reference
  • Angular 2 constructor injection vs direct access
  • Java static initializers and reflection
  • Android Google Maps API OnLocationChanged only called once
  • LevelDB C iterator
  • Linking SubReports Without LinkChild/LinkMaster
  • UserPrincipal.Current returns apppool on IIS