ExecuteNonQuery ExecuteScalar ExecuteReader Example In Asp.Net

SqlCommand ExecuteNonQuery, ExecuteScalar, ExecuteReader and ExecuteXmlReader Method Examples in Asp.Net C# VB.

1. SqlCommand ExecuteNonQuery Example

---

How and when to use ExecuteNonQuery method of SqlCommand in asp.net using C# and VB.NET.

2. ExecuteScalar

---

This example explains use of ExecuteScalar .

3. ExecuteReader

---

How to use ExecuteReader Method using C# and VB.NET.

4. ExecuteXmlReader

---

ExecuteXmlReader method of SqlCommand in asp.net.

Read more >>

ExecuteXmlReader Example In Asp.Net C# VB.Net

This example explains how to use ExecuteXmlReader Method In Asp.Net Using C# And VB

You may also read how to use NonQuery, Reader and ExecuteScalar Methods of SqlCommand.

This method executes the command and builds XmlReader, for it to work with fetching database records we need to use FOR XML AUTO clause in sql query.

C# CODE

---

string strConnection = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        string strSelect = "SELECT Username,Password FROM Users FOR XML AUTO";
        
        SqlConnection con = new SqlConnection(strConnection);
        SqlCommand cmd = new SqlCommand(strSelect, con);
        
        con.Open();
        XmlReader reader = cmd.ExecuteXmlReader();
        reader.Read();
        XmlDocument doc = new XmlDocument();
        doc.Load(reader);
        doc.Save("test.xml");
        reader.Close();
        con.Close();

VB.NET

---

Dim strConnection As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
Dim strSelect As String = "SELECT Username,Password FROM Users FOR XML AUTO"

Dim con As New SqlConnection(strConnection)
Dim cmd As New SqlCommand(strSelect, con)

con.Open()
Dim reader As XmlReader = cmd.ExecuteXmlReader()
reader.Read()
Dim doc As New XmlDocument()
doc.Load(reader)
doc.Save("test.xml")
reader.Close()
con.Close()

Read more >>

ExecuteScalar Example In Asp.Net C# VB

This example describes when and how to use SqlCommand ExecuteScalar Method in Asp.Net Using C# and VB

Other Methods like ExecuteNonQuery , Reader and XmlReader are also explained.

ExecuteScalar Executes the query and returns the first column of first row in result set returned by query, other columns and rows are ignored by this method.

It is best used for retrieving a single value eg record count, max,min,sum or getting the identity values of inserted record from database.

C# CODE

---

string strConnection = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        string strSelect = "SELECT COUNT(*) FROM Users WHERE Username = @Username AND Password = @Password";
        
        SqlConnection con = new SqlConnection(strConnection);
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = strSelect;

        SqlParameter username = new SqlParameter("@Username", SqlDbType.VarChar, 50);
        username.Value = txtUserName.Text.Trim().ToString();
        cmd.Parameters.Add(username);

        SqlParameter password = new SqlParameter("@Password", SqlDbType.VarChar, 50);
        password.Value = txtPassword.Text.Trim().ToString();
        cmd.Parameters.Add(password);

        con.Open();
        int result = (Int32)cmd.ExecuteScalar();
        con.Close();

        if (result >= 1)
            Response.Redirect("Default.aspx");
        else
            lblMsg.Text = "Incorrect Username or Password";

VB.NET

---

Dim strConnection As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
Dim strSelect As String = "SELECT COUNT(*) FROM Users WHERE Username = @Username AND Password = @Password"

Dim con As New SqlConnection(strConnection)
Dim cmd As New SqlCommand()
cmd.Connection = con
cmd.CommandType = CommandType.Text
cmd.CommandText = strSelect

Dim username As New SqlParameter("@Username", SqlDbType.VarChar, 50)
username.Value = txtUserName.Text.Trim().ToString()
cmd.Parameters.Add(username)

Dim password As New SqlParameter("@Password", SqlDbType.VarChar, 50)
password.Value = txtPassword.Text.Trim().ToString()
cmd.Parameters.Add(password)

con.Open()
Dim result As Integer = DirectCast(cmd.ExecuteScalar(), Int32)
con.Close()

If result >= 1 Then
 Response.Redirect("Default.aspx")
Else
 lblMsg.Text = "Incorrect Username or Password"
End If

To retrieve Identity of inserted record we can write code like mentined below

string strConnection = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        string strInsert = "INSERT INTO Users (Username,Password) VALUES (@Username,@Password);"
                          + "SELECT CAST(scope_identity() AS int)";
        SqlConnection con = new SqlConnection(strConnection);
        SqlCommand cmd = new SqlCommand(strInsert, con);
        
        SqlParameter username = new SqlParameter("@Username", SqlDbType.VarChar, 50);
        username.Value = txtUserName.Text.Trim().ToString();
        cmd.Parameters.Add(username);

        SqlParameter password = new SqlParameter("@Password", SqlDbType.VarChar, 50);
        password.Value = txtPassword.Text.Trim().ToString();
        cmd.Parameters.Add(password);

        con.Open();
        int identity = (Int32)cmd.ExecuteScalar();
        con.Close();

Read more >>

ExecuteReader Example In Asp.Net C# VB.Net

SqlCommand ExecuteReader Method Example in Asp.Net Using C# And Vb

This method Execute the command and builds or populate the SqlDataReader object. It is used for accessing data when query returns a set of records for display or navigation purpose.

It provides a forward only, read only connected recordset which means it needs open Sql Connection untill DataReader is open.

Common usage of ExecuteReader Method can be populating a dropdownlist or listbox or retrieving binary files from database.

I have also explained NonQuery , Scalar and XmlReader methods.

C# CODE

---

protected void Page_Load(object sender, EventArgs e)
    {
        string strConnection = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        string strSelect = "SELECT Username FROM Users";

        //Create SQL Connection And SQLCommand
        SqlConnection con = new SqlConnection(strConnection);
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = strSelect;

        con.Open();
        SqlDataReader dReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            while (dReader.Read())
            {
                ddlUsers.Items.Add(dReader["Username"].ToString());
            }
        dReader.Close();
    }

VB.NET

---

Protected Sub Page_Load(sender As Object, e As EventArgs)
 Dim strConnection As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
 Dim strSelect As String = "SELECT Username FROM Users"

 'Create SQL Connection And SQLCommand
 Dim con As New SqlConnection(strConnection)
 Dim cmd As New SqlCommand()
 cmd.Connection = con
 cmd.CommandType = CommandType.Text
 cmd.CommandText = strSelect

 con.Open()
 Dim dReader As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
 While dReader.Read()
  ddlUsers.Items.Add(dReader("Username").ToString())
 End While
 dReader.Close()
End Sub

Hope this helps.

Read more >>

SqlCommand ExecuteNonQuery Example C# VB Asp.Net

This is Example of how to use ExecuteNonQuery Method Of SqlCommand In Asp.Net Using C# And VB.

ExecuteNonQuery() Executes a Transact-SQL statement, It returns -1 for SELECT statements and Number of Rows affected for all INSERT DELETE AND UPDATE statements.

I have also explained Other methods of SqlCommand.

C# CODE

---

protected void btnSignIn_Click(object sender, EventArgs e)
    {
        string strConnection = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        string strInsert = "INSERT INTO Users (Username,Password) VALUES(@Username,@Password)";
        SqlConnection con = new SqlConnection(strConnection);
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = strInsert;

        SqlParameter username = new SqlParameter("@Username", SqlDbType.VarChar, 50);
        username.Value = txtUserName.Text.Trim().ToString();
        cmd.Parameters.Add(username);

        SqlParameter password = new SqlParameter("@Password", SqlDbType.VarChar, 50);
        password.Value = txtPassword.Text.Trim().ToString();
        cmd.Parameters.Add(password);

        con.Open();
        int result = cmd.ExecuteNonQuery();
       
        con.Close();
    }

VB.NET

---

Protected Sub btnSignIn_Click(sender As Object, e As EventArgs)
 Dim strConnection As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
 Dim strInsert As String = "INSERT INTO Users (Username,Password) VALUES(@Username,@Password)"
 Dim con As New SqlConnection(strConnection)
 Dim cmd As New SqlCommand()
 cmd.Connection = con
 cmd.CommandType = CommandType.Text
 cmd.CommandText = strInsert

 Dim username As New SqlParameter("@Username", SqlDbType.VarChar, 50)
 username.Value = txtUserName.Text.Trim().ToString()
 cmd.Parameters.Add(username)

 Dim password As New SqlParameter("@Password", SqlDbType.VarChar, 50)
 password.Value = txtPassword.Text.Trim().ToString()
 cmd.Parameters.Add(password)

 con.Open()
 Dim result As Integer = cmd.ExecuteNonQuery()

 con.Close()
End Sub

Hope this helps

Read more >>

Visual Studio Keyboard Shortcuts

Visual studio keyboard shortcuts.

Below is the list of Commonly used visual studio keyboard shortcuts.

Navigation

---

Ctrl + ]	Moves the cursor to the matching Closing or Opening brace
Ctrl + hyphen	Moves cursor to its previous position
Shift + F7	Switch between the Design View and Source View
Ctrl + Tab	Displays the all open files in VS
Ctrl + Shift + F	Find
F3	Search Again or continue search

Editing

---

Ctrl + k + c	Comment a selected line or all selected lines of code
Ctrl + k + u	Uncomment a selected line or all selected lines of code
Ctrl + x, Ctrl + c, Ctrl + v	Cut, Copy, Paste

Code Related

---

Ctrl +SpaceBar	Complete word if exist
Tab (Twice)	Inserts Code snippet, eg type while and press Tab twice, or type prop and press Tab twice
Ctrl + Period (.)	Shows SmartTag menu
F5	Start or run the application
F6 or Ctrl + Shift + B	Build the solution
F9	Insert or remove breakpoint
Ctrl + S	Save
Ctrl + Alt + L	Open Solution Explorer

Hope this helps.

Read more >>

Asp.Net Maintain Scroll Position On Postback In Google Chrome

Maintain Scroll Position On Postback In Google Chrome Browser Using Asp.Net.

In one of my previous posts i describe Maintaining Scroll Position After Postback. But this method doesn't work with Google Chrome browser.

So we need to go with any of two methods mentioned below.

1. ASP.NET METHOD

---

Write following code in Page_Load event of page.

protected void Page_Load(object sender, EventArgs e)
    {
        MaintainScrollPositionOnPostBack = true;
    }

Right click on solution explorer > Add New Item

Select Browser File and add it to App_Browsers folder.

Add MaintainScrollPositionOnPostback capability to this browser file as written below.

 <browsers>

   <browser refID="Mozilla">

     <capabilities>

       <capability name="supportsMaintainScrollPositionOnPostback"

                   value="true" />

     </capabilities>

   </browser>

 </browsers>

Build and run the code and it should work with Google chrome.

2. JAVASCRIPT METHOD

---

We can use scrollsaver javascript file to maintain scroll position In Chrome.

Just add this file in root of solution and add reference to it in head section of page.

   1:  <script src="scrollsaver.min.js" type="text/javascript">

   2:  </script>

hope this helps.

Read more >>

Asp.Net Ajax FilteredTextBox Extender Example

This Example explains how to use Asp.Net Ajax FilteredTextbox Extender to filter entries Or create numeric /Letters only textbox

By using it we can restrict user from entering certain special characters in text only or deny character entries in numeric textbox.

I have placed four textbox on the page.

1st will allow UPPERCASE letters

2nd allow lowercase

3rd will allow only Numbers

4th is custom textbox and it only allow "123abc$%" characters.

We can also use JavaScript Or RegularExpression Validator to achieve same functionality.


HTML SOURCE OF PAGE

---

   1:  <asp:ToolkitScriptManager ID="ToolkitScriptManager1" 

   2:                            runat="server">

   3:  </asp:ToolkitScriptManager>

   4:   

   5:  <asp:TextBox ID="txtUpperCase" runat="server"/>

   6:  <asp:FilteredTextBoxExtender ID="UpperCase" 

   7:                 runat="server" 

   8:                 TargetControlID="txtUpperCase" 

   9:                 FilterType="UppercaseLetters">

  10:  </asp:FilteredTextBoxExtender>

  11:              

  12:  <asp:TextBox ID="txtLowerCase" runat="server"/>

  13:  <asp:FilteredTextBoxExtender ID="LowerCase" 

  14:               runat="server" 

  15:               TargetControlID="txtLowerCase" 

  16:               FilterType="LowercaseLetters">

  17:  </asp:FilteredTextBoxExtender>

  18:   

  19:  <asp:TextBox ID="txtNumbers" runat="server"/>

  20:  <asp:FilteredTextBoxExtender ID="Numbers" 

  21:               runat="server" 

  22:               TargetControlID="txtNumbers" 

  23:               FilterType="Numbers">

  24:  </asp:FilteredTextBoxExtender>

  25:   

  26:  <asp:TextBox ID="txtCustom" runat="server"/>

  27:  <asp:FilteredTextBoxExtender ID="Custom" 

  28:               runat="server" 

  29:               TargetControlID="txtCustom" 

  30:               FilterType="Custom" 

  31:               ValidChars="123abc$%">

  32:  </asp:FilteredTextBoxExtender>

To restrict certain or special characters, we can configure as follows.

   1:  <asp:TextBox ID="TextBox1" runat="server"/>

   2:  <asp:FilteredTextBoxExtender ID="Custom" 

   3:               runat="server" 

   4:               TargetControlID="TextBox1" 

   5:               FilterMode="InvalidChars" 

   6:               InvalidChars="!@#$%^&amp;*()~?><|\';:">

   7:  </asp:FilteredTextBoxExtender>

We can change FilteredTextboxExtender properties in code behind if we need to allow or change textbox type on runtime.

using AjaxControlToolkit;
txtUpperCase_FilteredTextBoxExtender.FilterType = FilterTypes.Custom;
txtUpperCase_FilteredTextBoxExtender.FilterMode = FilterModes.ValidChars;
txtUpperCase_FilteredTextBoxExtender.ValidChars = "1234567890";

Hope this helps.

Download Sample Code

Read more >>