#sqlcommand

[simple-logic]

#PollTime Which SQL command do you use most often? 🧐SELECT 🤝JOIN 🔄UPDATE ❓WHERE

Which SQL command is your go-to? 🤔 Let us know in the comments below! 👇

[tccicomputercoaching]

5 Types of SQL Commands You Must Know

It is very important for a user embedded with SQL in database management to have a full-fledged interaction with the database so that the actual work gets done smoothly. Knowing the 5 Types of SQL Commands You Must Know ensures effective data handling, whether you are an amateur developer or an expert. TCCI-Tririd Computer Coaching Institute is one of the leading institutes where expert training and guidance are provided in SQL and Database Management.

These are the five types of SQL commands that one needs to know:

1. Data Definition Language (DDL)- Definition of Database Structure

DDL commands are concerned with defining and changing the structure of the database. Applying these commands has a direct impact on the schema of a database.

CREATE - Used to create tables, databases, index, or views.

ALTER - Modification of an existing database object, which may include the addition of new columns to a table.

DROP- Deletes tables, databases, or other database objects permanently.

TRUNCATE- Deletes all records from a table but keeps the structure for future use.

2. Data Manipulation Language (DML)- Manipulating Data

DML commands allow users to act on the data stored in a database. Retrieval and modification of the data are the primary operations performed by these commands.

INSERT - Inserts new records into a table.

UPDATE - Updates existing records in a table.

DELETE - Deletes a few records from a table.

3. Data Query Language (DQL)- Retrieving Data

The main purpose of DQL is to fetch data from the database.

SELECT - Feeds data from one or many tables based on the stipulated conditions.

4. Data Control Language (DCL)- Controlling Access To Data

DCL commands are meant for the control of users accessing the database.

GRANT - A specific privilege is assigned to users or roles.

REVOKE - Removes previously granted privileges.

5. Transaction Control Language (TCL)- Controlling Transaction

TCL commands are used for the efficient and secure execution of database transactions.

COMMIT- Saves all transaction changes.

ROLLBACK- Undo changes if an error occurs.

SAVEPOINT- Creates intermediate points in a transaction.

Conclusion

If you are someone looking to work with databases, then mastering these five sorts of SQL commands is necessary. To help you understand database management comprehensively, TCCI-Tririd Computer Coaching Institute offers an elaborative SQL training program. Our well-trained instructors guide both beginners and advanced students through practical and real-life applications.

Location: Bopal & Iskon-Ambli Ahmedabad, Gujarat

Call now on +91 9825618292

Get information from: https://tccicomputercoaching.wordpress.com/

FAQs

1. Which is the most important SQL command?

The most important command is SELECT, which helps retrieve data from the database.

2. What are the differences between DDL and DML?

DDL defines the structure of the database, while DML performs actual manipulations and management over data.

3. Can one learn SQL without prior programming experience?

Yes, SQL is easy to learn without coding experience.

4. Why are TCL commands important in SQL?

TCL guarantees that database transactions are carried out well, maintaining the consistency of the data.

Where can I learn SQL professionally? Join TCCI-Tririd Computer Coaching Institute to get expert guidance and hands-on SQL training.

Get started with SQL today and enhance your database management skills with TCCI! 🚀

[edcater1]

A Beginner's Guide to Database Management

SQL, or Structured Query Language, is a powerful tool used in database management and manipulation. It provides a standardized method for accessing and managing databases, enabling users to store, retrieve, update, and delete data efficiently. SQL operates through a variety of commands, such as SELECT, INSERT, UPDATE, DELETE, and JOIN, allowing users to perform complex operations on relational databases. Its simplicity and flexibility make it an essential skill for beginners in data management and analysis. With SQL, users can interact with databases seamlessly, extracting valuable insights and ensuring data integrity across various applications and platforms. For the more information visit our website -

[exhydra]

SQL Query To Find Statistics Of SQL Query

set lines 200 pages 1000 col sql_id for a13 col waitsec for 999999999 col status for a7 col command for a10 SELECT s.sid, s.serial#, s.sql_id, st.command_name command, s.seconds_in_wait waitsec, s.status, ses.value cpu FROM v$session s INNER JOIN v$sesstat ses on (s.sid = ses.sid) INNER JOIN v$statname stn on (ses.statistic# = stn.statistic#) INNER JOIN v$sqlcommand st on (s.command =…

[arshikasingh]

Some of The Most Important SQL Commands

SQL stands for Structured Query Language. It is used for storing and managing data in relational database management system (RDMS). Learn more about it from Javatpoint. Get a certification from the Best Online Institute in Noida. Address: G-13, 2nd Floor, Sec-3 Noida, UP, 201301, India Email: hr@javatpoint.com Contact: (+91) 9599321147, (+91) 9990449935

[tutort-academy]

SQL commands are just that: Instructions. It's used to send and receive data from the database. It can also be used to carry out specific tasks, functions, and data queries📊 👉🏻Here is the video that will dive you to learn about the different types of SQL commands✅ 👉🏻Do follow for more amazing content  @tutortacademy 📍

[pagesvewor]

Vb net best way to encode data

VB NET BEST WAY TO ENCODE DATA UPDATE

VB NET BEST WAY TO ENCODE DATA WINDOWS

Remember that building your own ad hoc queries in Entity Framework is just as susceptible to SQLi as a plain SQL query. Use of the Entity Framework is a very effective SQL injection prevention mechanism.The database user should only be able to access items that make sense for the use case. Apply the principle of least privilege when setting up the Database User in your database of choice.Enum.IsDefined can validate whether the input value is valid within the list of defined constants. NET only validates a successful cast to the underlying data type, integer by default. Enums are still vulnerable to unexpected values because.Use enums, TryParse or lookup values to assure that the data coming from the user is as expected. List allowable values coming from the user.Do not use SqlCommand with a string parameter made up of a concatenated SQL String.Use Parameterized SQL commands for all data access, without exception.It is a nearly ubiquitous library that is strongly named and versioned at the assembly level. NET Framework is the set of APIs that support an advanced type system, data, graphics, network, file handling and most of the rest of what is needed to write enterprise apps in the Microsoft ecosystem. ASP.NET Core & Entity Framework Core Security Announcements.Receive security notifications by selecting the "Watch" button at the following repositories:

VB NET BEST WAY TO ENCODE DATA UPDATE

ELMAH for instance, requires a separate update effort. Remember that third-party libraries have to be updated separately and not all of them use NuGet. As Visual Studio prompts for updates, build it into your lifecycle. Individual frameworks can be kept up to date using NuGet.

VB NET BEST WAY TO ENCODE DATA WINDOWS

Windows Update can be accessed at Windows Update or from the Windows Update program on a Windows computer. Developers do not normally need to run separate updates to the Framework. NET Framework is kept up-to-date by Microsoft with the Windows Update service. It is the supporting API for ASP.NET, Windows Desktop applications, Windows Communication Foundation services, SharePoint, Visual Studio Tools for Office and other technologies. NET Framework is Microsoft's principal platform for enterprise development. This page intends to provide quick basic. Insecure Direct Object Reference PreventionĭotNet Security Cheat Sheet ¶ Introduction ¶ NET Framework with AJAXĪ9 Using Components with Known Vulnerabilities

[social-vifree]

Incorrect syntax near 's'.Unclosed quotation mark after the character string ')'.

Incorrect syntax near ‘s’.Unclosed quotation mark after the character string ‘)’.

[SqlException (0x80131904): Incorrect syntax near 's'. Unclosed quotation mark after the character string ')'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +212 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +245 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler,…

[aspdotnettutorial]

CRUD Operation Using Ajax

SQl Scripts

create table Employee (EmployeeID int primary key identity,Name nvarchar(50),Age int,State nvarchar(50),Country nvarchar(50))

ALTER TABLE Employee

ADD FOREIGN KEY (DesignationID) REFERENCES tbl_Designation(Id);

--Insert and Update Employee  

ALTER Procedure [dbo].[InsertUpdateEmployee]    

(    

@Id integer,    

@Name nvarchar(50),    

@Age integer,    

@State nvarchar(50),    

@Country nvarchar(50),    

@Action varchar(10),

@DesignationID int    

)    

As    

Begin    

if @Action='Insert'    

Begin    

Insert into Employee(Name,Age,[State],Country,DesignationID) values(@Name,@Age,@State,@Country,@DesignationID);    

End    

if @Action='Update'    

Begin    

Update Employee set Name=@Name,Age=@Age,[State]=@State,Country=@Country,DesignationID=@DesignationID where EmployeeID=@Id;    

End      

End

----

--Select Employees  

ALTER Procedure [dbo].[SelectEmployee]    

as    

Begin  

SELECT EmployeeID,Name,Age,State,Country,DesignationName

FROM Employee

INNER JOIN tbl_Designation

ON Employee.DesignationID = tbl_Designation.Id;  

End

---

--Delete Employee  

Create Procedure DeleteEmployee    

(    

@Id integer    

)    

as    

Begin    

Delete Employee where EmployeeID=@Id;    

End  

-------------------------

Models class

Employee.cs public class Employee    {        public int EmployeeID { get; set; }

       public string Name { get; set; }

       public int Age { get; set; }

       public string State { get; set; }

       public string Country { get; set; }

       public Nullable<bool> IsDeleted { get; set; }        public Nullable<int> DesignationId { get; set; }        public string DesignationName { get; set; }    }*****EmployeeDb.cs

using System;using System.Collections.Generic;using System.Configuration;using System.Data;using System.Data.SqlClient;using System.Linq;using System.Web;

namespace EmployeeCrudOperationUsingAjax.Models{    public class EmployeeDB    {        //declare connection string          string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

       //Return list of all Employees          public List<Employee> ListAll()        {            List<Employee> lst = new List<Employee>();            using (SqlConnection con = new SqlConnection(cs))            {                con.Open();                SqlCommand com = new SqlCommand("SelectEmployee", con);                com.CommandType = CommandType.StoredProcedure;                SqlDataReader rdr = com.ExecuteReader();                while (rdr.Read())                {                    lst.Add(new Employee                    {                        EmployeeID = Convert.ToInt32(rdr["EmployeeId"]),                        Name = rdr["Name"].ToString(),                        Age = Convert.ToInt32(rdr["Age"]),                        State = rdr["State"].ToString(),                        Country = rdr["Country"].ToString(),                       // DesignationId= int.Parse(rdr["DesignationId"].ToString()),                        DesignationName = rdr["DesignationName"].ToString()                    });                }                return lst;            }        }

       //Method for Adding an Employee          public int Add(Employee emp)        {            int i;            using (SqlConnection con = new SqlConnection(cs))            {                con.Open();                SqlCommand com = new SqlCommand("InsertUpdateEmployee", con);                com.CommandType = CommandType.StoredProcedure;                com.Parameters.AddWithValue("@Id", emp.EmployeeID);                com.Parameters.AddWithValue("@Name", emp.Name);                com.Parameters.AddWithValue("@Age", emp.Age);                com.Parameters.AddWithValue("@State", emp.State);                com.Parameters.AddWithValue("@Country", emp.Country);                com.Parameters.AddWithValue("@DesignationID", emp.DesignationId);                com.Parameters.AddWithValue("@Action", "Insert");                i = com.ExecuteNonQuery();            }            return i;        }

       //Method for Updating Employee record          public int Update(Employee emp)        {            int i;            using (SqlConnection con = new SqlConnection(cs))            {                con.Open();                SqlCommand com = new SqlCommand("InsertUpdateEmployee", con);                com.CommandType = CommandType.StoredProcedure;                com.Parameters.AddWithValue("@Id", emp.EmployeeID);                com.Parameters.AddWithValue("@Name", emp.Name);                com.Parameters.AddWithValue("@Age", emp.Age);                com.Parameters.AddWithValue("@State", emp.State);                com.Parameters.AddWithValue("@Country", emp.Country);                com.Parameters.AddWithValue("@DesignationID", emp.DesignationId);                com.Parameters.AddWithValue("@Action", "Update");                i = com.ExecuteNonQuery();            }            return i;        }

       //Method for Deleting an Employee          public int Delete(int ID)        {            int i;            using (SqlConnection con = new SqlConnection(cs))            {                con.Open();                SqlCommand com = new SqlCommand("DeleteEmployee", con);                com.CommandType = CommandType.StoredProcedure;                com.Parameters.AddWithValue("@Id", ID);                i = com.ExecuteNonQuery();            }            return i;        }    }}

***********************

HomeController.cs

using EmployeeCrudOperationUsingAjax.Models;using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.Mvc;using System.Web.Services;

namespace EmployeeCrudOperationUsingAjax.Controllers{    public class HomeController : Controller    {        EmployeeDB empDB = new EmployeeDB();        EmployeeDBEntities Entities = new EmployeeDBEntities();        // GET: Home          public ActionResult Index()        {            //Pass All Department List Using ViewBag            List<tbl_Designation> DesigList = Entities.tbl_Designation.ToList();            ViewBag.ListOfDesignation = new SelectList(DesigList, "Id","DesignationName");            return View();        }        public JsonResult List()        {            return Json(empDB.ListAll(), JsonRequestBehavior.AllowGet);        }

       public JsonResult Add(Employee emp)        {            return Json(empDB.Add(emp), JsonRequestBehavior.AllowGet);        }

       public JsonResult GetbyID(int ID)        {            var Employee = empDB.ListAll().Find(x => x.EmployeeID.Equals(ID));            return Json(Employee, JsonRequestBehavior.AllowGet);        }

       public JsonResult Update(Employee emp)        {            return Json(empDB.Update(emp), JsonRequestBehavior.AllowGet);        }

       public JsonResult Delete(int ID)        {            return Json(empDB.Delete(ID), JsonRequestBehavior.AllowGet);        }

   }}

*************Index.chtml

@model EmployeeCrudOperationUsingAjax.Models.Employee@{    Layout = null;}

<!DOCTYPE html>

<html><head>    <meta name="viewport" content="width=device-width" />    <title>Index</title>    <!-- Required meta tags -->    <meta charset="utf-8">    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">

   <!-- Bootstrap CSS -->    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">

   <!-- Optional JavaScript -->    <!-- jQuery first, then Popper.js, then Bootstrap JS -->    <script src="https://code.jquery.com/jquery-3.2.1.slim.min.js" integrity="sha384-KJ3o2DKtIkvYIK3UENzmM7KCkRr/rE9/Qpg6aAZGJwFDMVNA/GpGFF93hXpG5KkN" crossorigin="anonymous"></script>    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.9/umd/popper.min.js" integrity="sha384-ApNbgh9B+Y1QKtv3Rn7W3mgPxhU9K/ScQsAP7hUibX39j7fakFPskvXusvfa0b4Q" crossorigin="anonymous"></script>    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js" integrity="sha384-JZR6Spejh4U02d8jOt6vLEHfe/JQGiRRSQQxSfFWpi1MquVdAyjUar5+76PVCmYl" crossorigin="anonymous"></script>    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>

   <script src="~/scripts/jquery-1.12.4.js"></script>    <script src="~/scripts/bootstrap.js"></script>

   <script src="~/scripts/employee.js"></script>   </head><body>    <div class="container">        <h2>Employees Record</h2>          <button type="button" class="btn btn-primary" data-toggle="modal" data-target="#myModal" onclick="clearTextBox();">Add New Employee</button><br /><br />        <table class="table table-bordered table-hover">            <thead>                <tr>                    <th>                        ID                    </th>                    <th>                        Name                    </th>                    <th>                        Age                    </th>                    <th>                        State                    </th>                    <th>                        Country                    </th>                    <th>                       Designation                    </th>                    <th>                        Action                    </th>                </tr>            </thead>            <tbody class="tbody"></tbody>        </table>    </div>    <div class="modal fade" id="myModal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">        <div class="modal-dialog">            <div class="modal-content">                <div class="modal-header">                    <button type="button" class="close" data-dismiss="modal">×</button>                    <h4 class="modal-title" id="myModalLabel">Add Employee</h4>                </div>                <div class="modal-body">                    <form>                        <div class="form-group">                            <label for="EmployeeId">ID</label>                            <input type="text" class="form-control" id="EmployeeID" placeholder="Id" disabled="disabled" />                        </div>                        <div class="form-group">                            <label for="Name">Name</label>                            <input type="text" class="form-control" id="Name" placeholder="Name" />                        </div>                        <div class="form-group">                            <label for="Age">Age</label>                            <input type="text" class="form-control" id="Age" placeholder="Age" />                        </div>                        <div class="form-group">                            @Html.DropDownListFor(m => m.DesignationId, ViewBag.ListOfDesignation as SelectList, "--Select Designation--", new { @id = "DesignationID", @class = "form-control" })                        </div> <div class="form-group">                                <label for="State">State</label>                                <input type="text" class="form-control" id="State" placeholder="State" />                            </div>                            <div class="form-group">                                <label for="Country">Country</label>                                <input type="text" class="form-control" id="Country" placeholder="Country" />                            </div></form>                </div>                <div class="modal-footer">                    <button type="button" class="btn btn-primary" id="btnAdd" onclick="return Add();">Add</button>                    <button type="button" class="btn btn-primary" id="btnUpdate" style="display:none;" onclick="Update();">Update</button>                    <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>                </div>            </div>        </div>    </div>  </body></html>

********

Create Employee.js in Scripts Folder

//Load Data in Table when documents is ready  $(document).ready(function () {    loadData();    alert('Hi')});

//Load Data function  function loadData() {    $.ajax({        url: "/Home/List",        type: "GET",        contentType: "application/json;charset=utf-8",        dataType: "json",        success: function (result) {            var html = '';            $.each(result, function (key, item) {                html += '<tr>';                html += '<td>' + item.EmployeeID + '</td>';                html += '<td>' + item.Name + '</td>';                html += '<td>' + item.Age + '</td>';                html += '<td>' + item.State + '</td>';                html += '<td>' + item.Country + '</td>';                html += '<td>' + item.DesignationName + '</td>';                html += '<td><a href="#" onclick="return getbyID(' + item.EmployeeID + ')">Edit</a> | <a href="#" onclick="Delele(' + item.EmployeeID + ')">Delete</a></td>';                html += '</tr>';            });            $('.tbody').html(html);        },        error: function (errormessage) {            alert(errormessage.responseText);        }    });}

//Add Data Function   function Add() {    var res = validate();    if (res == false) {        return false;    }    var empObj = {        EmployeeID: $('#EmployeeID').val(),        Name: $('#Name').val(),        Age: $('#Age').val(),        State: $('#State').val(),        Country: $('#Country').val(),        DesignationID: $('#DesignationID').val()    };    $.ajax({        url: "/Home/Add",        data: JSON.stringify(empObj),        type: "POST",        contentType: "application/json;charset=utf-8",        dataType: "json",        success: function (result) {            loadData();            $('#myModal').modal('hide');        },        error: function (errormessage) {            alert(errormessage.responseText);        }    });}

//Function for getting the Data Based upon Employee ID  function getbyID(EmpID) {    $('#Name').css('border-color', 'lightgrey');    $('#Age').css('border-color', 'lightgrey');    $('#State').css('border-color', 'lightgrey');    $('#Country').css('border-color', 'lightgrey');    $.ajax({        url: "/Home/getbyID/" + EmpID,        typr: "GET",        contentType: "application/json;charset=UTF-8",        dataType: "json",        success: function (result) {            $('#EmployeeID').val(result.EmployeeID);            $('#Name').val(result.Name);            $('#Age').val(result.Age);            $('#State').val(result.State);            $('#Country').val(result.Country);

           $('#myModal').modal('show');            $('#btnUpdate').show();            $('#btnAdd').hide();        },        error: function (errormessage) {            alert(errormessage.responseText);        }    });    return false;}

//function for updating employee's record  function Update() {    var res = validate();    if (res == false) {        return false;    }    var empObj = {        EmployeeID: $('#EmployeeID').val(),        Name: $('#Name').val(),        Age: $('#Age').val(),        State: $('#State').val(),        Country: $('#Country').val(),        DesignationID: $('#DesignationID').val()    };    $.ajax({        url: "/Home/Update",        data: JSON.stringify(empObj),        type: "POST",        contentType: "application/json;charset=utf-8",        dataType: "json",        success: function (result) {            loadData();            $('#myModal').modal('hide');            $('#EmployeeID').val("");            $('#Name').val("");            $('#Age').val("");            $('#State').val("");            $('#Country').val("");        },        error: function (errormessage) {            alert(errormessage.responseText);        }    });}

//function for deleting employee's record  function Delele(ID) {    var ans = confirm("Are you sure you want to delete this Record?");    if (ans) {        $.ajax({            url: "/Home/Delete/" + ID,            type: "POST",            contentType: "application/json;charset=UTF-8",            dataType: "json",            success: function (result) {                loadData();            },            error: function (errormessage) {                alert(errormessage.responseText);            }        });    }}

//Function for clearing the textboxes  function clearTextBox() {    $('#EmployeeID').val("");    $('#Name').val("");    $('#Age').val("");    $('#State').val("");    $('#Country').val("");    $('#btnUpdate').hide();    $('#btnAdd').show();    $('#Name').css('border-color', 'lightgrey');    $('#Age').css('border-color', 'lightgrey');    $('#State').css('border-color', 'lightgrey');    $('#Country').css('border-color', 'lightgrey');}//Valdidation using jquery  function validate() {    var isValid = true;    if ($('#Name').val().trim() == "") {        $('#Name').css('border-color', 'Red');        isValid = false;    }    else {        $('#Name').css('border-color', 'lightgrey');    }    if ($('#Age').val().trim() == "") {        $('#Age').css('border-color', 'Red');        isValid = false;    }    else {        $('#Age').css('border-color', 'lightgrey');    }    if ($('#State').val().trim() == "") {        $('#State').css('border-color', 'Red');        isValid = false;    }    else {        $('#State').css('border-color', 'lightgrey');    }    if ($('#Country').val().trim() == "") {        $('#Country').css('border-color', 'Red');        isValid = false;    }    else {        $('#Country').css('border-color', 'lightgrey');    }    return isValid;}

[globalmediacampaign]

MySQL Connect Dialog

About a month ago, I published how you can connect to MySQL with a small form. One suggestion, or lets promote it to a request, from that post was: “Nice, but how do you create a reusable library for the MySQL Connection Dialog box?” That was a good question but I couldn’t get back until now to write a new blog post. This reusable MySQL connection dialog lets you remove MySQL connection data from the command-line history. This post also shows you how to create and test a Powershell Module. The first step to create a module requires that you set the proper %PSModulePath% environment variable. If you fail to do that, you can put it into a default PowerShell module location but that’s not too effective for testing. You launch the System Properties dialog and click the Environment Variables button: Then, you edit the PSModulePath environment variable in the bottom list of environment variables and add a new path to the PSModulePath. My development path in this example is: C:Datacit225mysqlpsmod I named the file the same as the function Get-Credentials.psm1 consistent with the Microsoft instructions for creating a PowerShell module and their instructions for Pascal case name with an approved verb and singular noun. Below is the code for the Get-Credentials.psm1 file: function Get-Credentials { # Add libraries for form components. Add-Type -AssemblyName System.Windows.Forms Add-Type -AssemblyName System.Drawing # Define a user credential form. $form = New-Object System.Windows.Forms.Form $form.Text = 'User Credential Form' $form.Size = New-Object System.Drawing.Size(300,240) $form.StartPosition = 'CenterScreen' # Define a button and assign it and its controls to a form. $loginButton = New-Object System.Windows.Forms.Button $loginButton.Location = New-Object System.Drawing.Point(60,160) $loginButton.Size = New-Object System.Drawing.Size(75,23) $loginButton.Text = 'Login' $loginButton.DialogResult = [System.Windows.Forms.DialogResult]::OK $form.AcceptButton = $loginButton $form.Controls.Add($loginButton) # Define a button and assign it and its controls to a form. $cancelButton = New-Object System.Windows.Forms.Button $cancelButton.Location = New-Object System.Drawing.Point(155,160) $cancelButton.Size = New-Object System.Drawing.Size(75,23) $cancelButton.Text = 'Cancel' $cancelButton.DialogResult = [System.Windows.Forms.DialogResult]::Cancel $form.CancelButton = $cancelButton $form.Controls.Add($cancelButton) # Define a label and assign it and its controls to a form. $userLabel = New-Object System.Windows.Forms.Label $userLabel.Location = New-Object System.Drawing.Point(30,15) $userLabel.Size = New-Object System.Drawing.Size(100,20) $userLabel.Text = 'Enter User Name:' $form.Controls.Add($userLabel) # Define a TextBox and assign it and its controls to a form. $userTextBox = New-Object System.Windows.Forms.TextBox $userTextBox.Location = New-Object System.Drawing.Point(140,15) $userTextBox.Size = New-Object System.Drawing.Size(100,20) $form.Controls.Add($userTextBox) # Define a label and assign it and its controls to a form. $pwdLabel = New-Object System.Windows.Forms.Label $pwdLabel.Location = New-Object System.Drawing.Point(30,40) $pwdLabel.Size = New-Object System.Drawing.Size(100,20) $pwdLabel.Text = 'Enter Password:' $form.Controls.Add($pwdLabel) # Define a TextBox and assign it and its controls to a form. $pwdTextBox = New-Object System.Windows.Forms.TextBox $pwdTextBox.Location = New-Object System.Drawing.Point(140,40) $pwdTextBox.Size = New-Object System.Drawing.Size(100,20) $pwdTextBox.PasswordChar = "*" $form.Controls.Add($pwdTextBox) # Define a label and assign it and its controls to a form. $hostLabel = New-Object System.Windows.Forms.Label $hostLabel.Location = New-Object System.Drawing.Point(30,65) $hostLabel.Size = New-Object System.Drawing.Size(100,20) $hostLabel.Text = 'Enter Hostname:' $form.Controls.Add($hostLabel) # Define a TextBox and assign it and its controls to a form. $hostTextBox = New-Object System.Windows.Forms.TextBox $hostTextBox.Location = New-Object System.Drawing.Point(140,65) $hostTextBox.Size = New-Object System.Drawing.Size(100,20) $form.Controls.Add($hostTextBox) # Define a label and assign it and its controls to a form. $portLabel = New-Object System.Windows.Forms.Label $portLabel.Location = New-Object System.Drawing.Point(30,90) $portLabel.Size = New-Object System.Drawing.Size(100,20) $portLabel.Text = 'Enter Port #:' $form.Controls.Add($portLabel) # Define a TextBox and assign it and its controls to a form. $portTextBox = New-Object System.Windows.Forms.TextBox $portTextBox.Location = New-Object System.Drawing.Point(140,90) $portTextBox.Size = New-Object System.Drawing.Size(100,20) $form.Controls.Add($portTextBox) # Define a label and assign it and its controls to a form. $dbLabel = New-Object System.Windows.Forms.Label $dbLabel.Location = New-Object System.Drawing.Point(30,115) $dbLabel.Size = New-Object System.Drawing.Size(100,20) $dbLabel.Text = 'Enter DB Name:' $form.Controls.Add($dbLabel) # Define a TextBox and assign it and its controls to a form. $dbTextBox = New-Object System.Windows.Forms.TextBox $dbTextBox.Location = New-Object System.Drawing.Point(140,115) $dbTextBox.Size = New-Object System.Drawing.Size(100,20) $form.Controls.Add($dbTextBox) $form.Topmost = $true $form.Add_Shown({$userTextBox.Select()}) $result = $form.ShowDialog() if ($result -eq [System.Windows.Forms.DialogResult]::OK) { # Assign inputs to connection variables. $uid = $userTextBox.Text $pwd = $pwdTextBox.Text $server = $hostTextBox.Text $port= $portTextBox.Text $dbName = $dbTextBox.Text # Declare connection string. $credentials = 'server=' + $server + ';port=' + $port + ';uid=' + $uid + ';pwd=' + $pwd + ';database=' + $dbName } else { $credentials = $null } return $credentials } You must create a Get-Connection directory in your C:Datacit225mysqlpsmod directory that you added to the PSModulePath. Then, you must put your module code in the Get-Connection subdirectory as the Get-Connection.psm1 module file. The test.ps1 script imports the Get-Credentials.psm1 PowerShell module, launches the MySQL Connection Dialog form and returns the connection string. The test.ps1 code is: # Import your custom module. Import-Module Get-Credentials # Test the Get-Credentials function. if (($credentials = Get-Credentials) -ne $undefinedVariable) { Write-Host($credentials) } You can test it from the local any directory with the following command-line: powershell .test.ps1 It should print something like this to the console: server=localhost;port=3306;uid=student;pwd=student;database=studentdb If you got this far, that’s great! You’re ready to test a connection to the MySQL database. Before you do that, you should create the same avenger table I used in the initial post and insert the same or some additional data. Connect to the any of your test databases and rung the following code to create the avenger table and nine rows of data. -- Create the avenger table. CREATE TABLE db_connect ( db_connect_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , version VARCHAR(10) , user VARCHAR(24) , db_name VARCHAR(10)); -- Seed the avenger table with data. INSERT INTO avenger ( first_name, last_name, avenger ) VALUES ('Anthony', 'Stark', 'Iron Man') ,('Thor', 'Odinson', 'God of Thunder') ,('Steven', 'Rogers', 'Captain America') ,('Bruce', 'Banner', 'Hulk') ,('Clinton', 'Barton', 'Hawkeye') ,('Natasha', 'Romanoff', 'Black Widow') ,('Peter', 'Parker', 'Spiderman') ,('Steven', 'Strange', 'Dr. Strange') ,('Scott', 'Lange', 'Ant-man'); Now, let’s promote our use-case test.ps1 script to a testQuery.ps1 script, like: # Import your custom module. Import-Module Get-Credentials # Test the Get-Credentials function. if (($credentials = Get-Credentials) -ne $undefinedVariable) { # Connect to the libaray MySQL.Data.dll Add-Type -Path 'C:Program Files (x86)MySQLConnector NET 8.0Assembliesv4.5.2MySql.Data.dll' # Create a MySQL Database connection variable that qualifies: # [Driver]@ConnectionString # ============================================================ # You can assign the connection string before using it or # while using it, which is what we do below by assigning # literal values for the following names: # - server= or 127.0.0.1 for localhost # - uid= # - pwd= # - port= or 3306 for default port # - database= # ============================================================ $Connection = [MySql.Data.MySqlClient.MySqlConnection]@{ConnectionString=$credentials} $Connection.Open() # Define a MySQL Command Object for a non-query. $sqlCommand = New-Object MySql.Data.MySqlClient.MySqlCommand $sqlDataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter $sqlDataSet = New-Object System.Data.DataSet # Assign the connection and command text to the MySQL command object. $sqlCommand.Connection = $Connection $sqlCommand.CommandText = 'SELECT CONCAT(first_name," ",last_name) AS full_name ' + ', avenger ' + 'FROM avenger' # Assign the connection and command text to the query method of # the data adapter object. $sqlDataAdapter.SelectCommand=$sqlCommand # Assign the tuples of data to a data set and return the number of rows fetched. $rowsFetched=$sqlDataAdapter.Fill($sqlDataSet, "data") # Print to console the data returned from the query. foreach($row in $sqlDataSet.tables[0]) { write-host "Avenger:" $row.avenger "is" $row.full_name } # Close the MySQL connection. $Connection.Close() } It should give you the MySQL Connection Dialog and with the correct credentials print the following to your console: Avenger: Iron Man is Anthony Stark Avenger: God of Thunder is Thor Odinson Avenger: Captain America is Steven Rogers Avenger: Hulk is Bruce Banner Avenger: Hawkeye is Clinton Barton Avenger: Black Widow is Natasha Romanoff Avenger: Spiderman is Peter Parker Avenger: Dr. Strange is Steven Strange Avenger: Ant-man is Scott Lange As always, I hope this helps those looking to exploit technology. https://blog.mclaughlinsoftware.com/2021/05/21/mysql-connect-dialog/

[exhydra]

SQL Query To Find Any SQL Command Type Oracle

SQL> col COMMAND_NAME for a20 SQL> set lines 200 pages 1000 SELECT s.sid, s.serial#, s.sql_id, st.command_name, s.seconds_in_wait, s.status FROM v$session s INNER JOIN v$sqlcommand st on (s.command = st.command_type) AND sql_id is not null;

[xinchaocode]

Hướng dẫn INSERT hình ảnh vào SQL Server

BaĐể chèn vào và truy xuất hình ảnh từ cơ sở dữ liệu máy chủ SQL mà không cần sử dụng các thủ tục được lưu trữ và cũng để thực hiện các thao tác chèn, tìm kiếm, cập nhật và xóa và điều hướng các bản ghi.

Khi chúng ta muốn chèn hình ảnh vào cơ sở dữ liệu, trước tiên chúng ta phải tạo một bảng trong cơ sở dữ liệu, chúng ta có thể sử dụng kiểu dữ liệu 'image' hoặc 'binary' để lưu trữ hình ảnh.

create table student(sno int primary key,sname varchar(50),course varchar(50),fee money,photo image)

Truy vấn để tạo bảng trong ứng dụng của chúng ta

Thiết kế giao diện

Thiết kế biểu mẫu như trên với 1 control PictureBox, 1 control OpenFileDialog, 4 Labels, 4  control TextBox và 11  control Buttons.

Thuộc tính PictureBox1

BorderStyle = Cố định3D; SizeMode = StrechImage

Lưu ý rằng control OpenFileDialog xuất hiện bên dưới biểu mẫu (không phải trên biểu mẫu), có thể được sử dụng để duyệt hình ảnh.

Bắt đầu lập trình

using thư viện

using System.Data.SqlClient  

Trong ứng dụng này, chúng ta sẽ tìm kiếm một bản ghi bằng cách lấy đầu vào từ InputBox. Đối với điều này, chúng tôi phải thêm tham chiếu đến Microsoft.VisualBasic.

Thêm tham chiếu vào 'Microsoft.VisualBasic'

Goto Project Menu -> Add Reference -> chọn 'Microsoft.VisualBasic' từ tab .NET.

Để sử dụng tham chiếu này, chúng tôi phải bao gồm không gian tên:

using Microsoft.VisualBasic  

Chuyển đổi hình ảnh thành dữ liệu nhị phân

Chúng ta không thể lưu trữ hình ảnh trực tiếp vào cơ sở dữ liệu. Đối với điều này, chúng tôi có hai giải pháp: Để lưu trữ vị trí của hình ảnh trong cơ sở dữ liệu

Chuyển đổi hình ảnh thành dữ liệu nhị phân và chèn dữ liệu nhị phân đó vào cơ sở dữ liệu và chuyển đổi dữ liệu đó trở lại hình ảnh trong khi truy xuất các bản ghi. Nếu chúng ta lưu trữ vị trí của một hình ảnh trong cơ sở dữ liệu và giả sử nếu hình ảnh đó bị xóa hoặc di chuyển khỏi vị trí đó, chúng tôi sẽ gặp phải vấn đề khi truy xuất các bản ghi. Vì vậy, tốt hơn là chuyển đổi hình ảnh thành dữ liệu nhị phân và chèn dữ liệu nhị phân đó vào cơ sở dữ liệu và chuyển đổi lại thành hình ảnh trong khi truy xuất bản ghi.

Chúng ta có thể chuyển đổi một hình ảnh thành dữ liệu nhị phân bằng cách sử dụng

FileStream

MemoryStream

1. FileStream sử dụng vị trí tệp để chuyển đổi hình ảnh thành dữ liệu nhị phân mà chúng ta có thể / không cung cấp trong khi cập nhật bản ghi.

FileStream fs = new FileStream(openFileDialog1.FileName, FileMode.Open, FileAccess.Read);   byte[] photo_aray = new byte[fs.Length];   fs.Read(photo_aray, 0, photo_aray.Length);

2. Vì vậy, tốt hơn là sử dụng MemoryStream sử dụng hình ảnh trong PictureBox để chuyển đổi hình ảnh thành dữ liệu nhị phân.

MemoryStream ms = new MemoryStream();   pictureBox1.Image.Save(ms, ImageFormat.Jpeg);   byte[] photo_aray = new byte[ms.Length];   ms.Position = 0;   ms.Read(photo_aray, 0, photo_aray.Length);  

Để sử dụng FileStream hoặc MemoryStream, chúng ta phải using thư viên:

using System.IO

OpenFileDialog Control Chúng ta sử dụng điều khiển OpenFileDialog để duyệt các hình ảnh (ảnh) để chèn vào bản ghi

Tải chi tiết ràng buộc vào dataTable

Trong ứng dụng này. chúng ta sử dụng phương thức Find () để tìm kiếm một bản ghi, yêu cầu chi tiết về cột khóa chính, có thể được cung cấp bằng câu lệnh:

adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

Trỏ tới bản ghi hiện tại trong dataTable

Sau khi tìm kiếm một bản ghi, chúng ta phải lấy chỉ mục của bản ghi đó để có thể điều hướng các bản ghi tiếp theo và trước đó.

rno= ds.Tables[0].Rows.IndexOf(drow);

code tham khảo

using System;   using System.Windows.Forms;   using System.Data;   using System.Data.SqlClient;   using System.Drawing;   using System.Drawing.Imaging;   using System.IO;   using Microsoft.VisualBasic;   namespace inserting_imgs   {      public partial class Form1 : Form      {          public Form1()          {              InitializeComponent();          }          SqlConnection con;          SqlCommand cmd;          SqlDataAdapter adapter;          DataSet ds; int rno = 0;          MemoryStream ms;          byte[] photo_aray;            private void Form1_Load(object sender, EventArgs e)          {              con = new SqlConnection("user id=sa;password=123;database=prash");              loaddata();              showdata();          }          void loaddata()          {              adapter = new SqlDataAdapter("select sno,sname,course,fee,photo from student", con);              adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;              ds = new DataSet(); adapter.Fill(ds, "student");          }          void showdata()          {              if (ds.Tables[0].Rows.Count > 0)              {                  textBox1.Text = ds.Tables[0].Rows[rno][0].ToString();                  textBox2.Text = ds.Tables[0].Rows[rno][1].ToString();                  textBox3.Text = ds.Tables[0].Rows[rno][2].ToString();                  textBox4.Text = ds.Tables[0].Rows[rno][3].ToString();                  pictureBox1.Image = null;                  if (ds.Tables[0].Rows[rno][4] != System.DBNull.Value)                  {                      photo_aray = (byte[])ds.Tables[0].Rows[rno][4];                      MemoryStream ms = new MemoryStream(photo_aray);                      pictureBox1.Image = Image.FromStream(ms);                  }              }              else                  MessageBox.Show("No Records");          }            private void browse_Click(object sender, EventArgs e)          {              openFileDialog1.Filter = "jpeg|*.jpg|bmp|*.bmp|all files|*.*";              DialogResult res = openFileDialog1.ShowDialog();              if (res == DialogResult.OK)              {                  pictureBox1.Image = Image.FromFile(openFileDialog1.FileName);              }          }            private void newbtn_Click(object sender, EventArgs e)          {              cmd = new SqlCommand("select max(sno)+10 from student", con);              con.Open();              textBox1.Text = cmd.ExecuteScalar().ToString();              con.Close();              textBox2.Text = textBox3.Text = textBox4.Text = "";              pictureBox1.Image = null;          }            private void insert_Click(object sender, EventArgs e)          {              cmd = new SqlCommand("insert into student(sno,sname,course,fee,photo) values(" + textBox1.Text + ",'" +   textBox2.TabIndex + "','" + textBox3.Text + "'," + textBox4.Text + ",@photo)", con);              conv_photo();              con.Open();              int n = cmd.ExecuteNonQuery();              con.Close();              if (n > 0)              {                  MessageBox.Show("record inserted");                  loaddata();              }              else                  MessageBox.Show("insertion failed");          }          void conv_photo()          {              //converting photo to binary data              if (pictureBox1.Image != null)              {                  //using FileStream:(will not work while updating, if image is not changed)                  //FileStream fs = new FileStream(openFileDialog1.FileName, FileMode.Open, FileAccess.Read);                  //byte[] photo_aray = new byte[fs.Length];                  //fs.Read(photo_aray, 0, photo_aray.Length);                      //using MemoryStream:                  ms = new MemoryStream();                  pictureBox1.Image.Save(ms, ImageFormat.Jpeg);                  byte[] photo_aray = new byte[ms.Length];                  ms.Position = 0;                  ms.Read(photo_aray, 0, photo_aray.Length);                  cmd.Parameters.AddWithValue("@photo", photo_aray);              }          }            private void search_Click(object sender, EventArgs e)          {              try              {                  int n = Convert.ToInt32(Interaction.InputBox("Enter sno:", "Search", "20", 100, 100));                  DataRow drow;                  drow = ds.Tables[0].Rows.Find(n);                  if (drow != null)                  {                      rno = ds.Tables[0].Rows.IndexOf(drow);                      textBox1.Text = drow[0].ToString();                      textBox2.Text = drow[1].ToString();                      textBox3.Text = drow[2].ToString();                      textBox4.Text = drow[3].ToString();                      pictureBox1.Image = null;                      if (drow[4] != System.DBNull.Value)                      {                          photo_aray = (byte[])drow[4];                          MemoryStream ms = new MemoryStream(photo_aray);                          pictureBox1.Image = Image.FromStream(ms);                      }                  }                  else                      MessageBox.Show("Record Not Found");              }              catch              {                  MessageBox.Show("Invalid Input");              }          }            private void update_Click(object sender, EventArgs e)          {              cmd = new SqlCommand("update student set sname='" + textBox2.Text + "', course='" + textBox3.Text + "', fee='" + textBox4.Text + "', photo=@photo where sno=" + textBox1.Text, con);              conv_photo();              con.Open();              int n = cmd.ExecuteNonQuery();              con.Close();              if (n > 0)              {                  MessageBox.Show("Record Updated");                  loaddata();              }              else                  MessageBox.Show("Updation Failed");          }            private void delete_Click(object sender, EventArgs e)          {              cmd = new SqlCommand("delete from student where sno=" + textBox1.Text, con);              con.Open();              int n = cmd.ExecuteNonQuery();              con.Close();              if (n > 0)              {                  MessageBox.Show("Record Deleted");                  loaddata();                  rno = 0;                  showdata();              }              else                  MessageBox.Show("Deletion Failed");          }            private void first_Click(object sender, EventArgs e)          {              rno = 0; showdata();              MessageBox.Show("First record");          }            private void previous_Click(object sender, EventArgs e)          {                if (rno > 0)              {                  rno--; showdata();              }              else                  MessageBox.Show("First record");          }            private void next_Click(object sender, EventArgs e)          {              if (rno < ds.Tables[0].Rows.Count - 1)              {                  rno++; showdata();              }              else                  MessageBox.Show("Last record");          }            private void last_Click(object sender, EventArgs e)          {              rno = ds.Tables[0].Rows.Count - 1;              showdata(); MessageBox.Show("Last record");          }            private void exit_Click(object sender, EventArgs e)          {              this.Close();          }         }   }

Bài viết gốc: https://www.c-sharpcorner.com/UploadFile/e628d9/inserting-retrieving-images-from-sql-server-database-without-using-stored-procedures/

[social-vifree]

Incorrect syntax near 's'.Unclosed quotation mark after the character string ')'.

Incorrect syntax near ‘s’.Unclosed quotation mark after the character string ‘)’.

[SqlException (0x80131904): Incorrect syntax near 's'. Unclosed quotation mark after the character string ')'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +212 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +245 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler,…

[sagar-jaybhay]

How to Perform Edit Operation In Asp.Net MVC

New Post has been published on https://is.gd/3ly1Dm

How to Perform Edit Operation In Asp.Net MVC

In this article we will understand How to Perform Edit Operation in Asp.Net MVC By Sagar Jaybhay.

How to Perform Edit Operation In Asp.Net MVC

Created Edit method in EmployeeController which is shown below and strongly typed EditView for this.

[HttpGet] public ActionResult Edit(string EmpID) var Employye = new BusinessLogic.Business().GetEmployee(EmpID); return View(Employye);

Edit Model In Asp.net MVC

Now after creating a view and click on edit link you will find below the window

Edit Employee In Asp.Net MVC

But if you see above image gender will display in textbox but we want dropdown list so we need to modify this as per our requirement. We modify our view, but in this, you should rename DropDownList Name to EmpGender as our model property is EmpGender so it will bind value directly.

@model WebApplication1.Models.Employee @ ViewBag.Title = "Edit"; <h2>Edit</h2> @using (Html.BeginForm()) @Html.AntiForgeryToken() <div class="form-horizontal"> <h4>Employee</h4> <hr /> @Html.ValidationSummary(true, "", new @class = "text-danger" ) <div class="form-group"> @Html.LabelFor(model => model.EmpID, htmlAttributes: new @class = "control-label col-md-2" ) <div class="col-md-10"> @Html.EditorFor(model => model.EmpID, new htmlAttributes = new @class = "form-control" ) @Html.ValidationMessageFor(model => model.EmpID, "", new @class = "text-danger" ) </div> </div> <div class="form-group"> @Html.LabelFor(model => model.EmpName, htmlAttributes: new @class = "control-label col-md-2" ) <div class="col-md-10"> @Html.EditorFor(model => model.EmpName, new htmlAttributes = new @class = "form-control" ) @Html.ValidationMessageFor(model => model.EmpName, "", new @class = "text-danger" ) </div> </div> <div class="form-group"> @Html.LabelFor(model => model.EmpSalary, htmlAttributes: new @class = "control-label col-md-2" ) <div class="col-md-10"> @Html.EditorFor(model => model.EmpSalary, new htmlAttributes = new @class = "form-control" ) @Html.ValidationMessageFor(model => model.EmpSalary, "", new @class = "text-danger" ) </div> </div> <div class="form-group"> @Html.LabelFor(model => model.EmpGender, htmlAttributes: new @class = "control-label col-md-2" ) <div class="col-md-10"> @Html.DropDownList("EmpGender", new List<SelectListItem> new SelectListItem Text="Male" ,Value="Male", new SelectListItem Text="Female" ,Value="female", "Select Gender") @Html.ValidationMessageFor(model => model.EmpGender, "", new @class = "text-danger" ) </div> </div> <div class="form-group"> @Html.LabelFor(model => model.EmpCity, htmlAttributes: new @class = "control-label col-md-2" ) <div class="col-md-10"> @Html.EditorFor(model => model.EmpCity, new htmlAttributes = new @class = "form-control" ) @Html.ValidationMessageFor(model => model.EmpCity, "", new @class = "text-danger" ) </div> </div> <div class="form-group"> @Html.LabelFor(model => model.EmpEmail, htmlAttributes: new @class = "control-label col-md-2" ) <div class="col-md-10"> @Html.EditorFor(model => model.EmpEmail, new htmlAttributes = new @class = "form-control" ) @Html.ValidationMessageFor(model => model.EmpEmail, "", new @class = "text-danger" ) </div> </div> <div class="form-group"> @Html.LabelFor(model => model.DepartmentID, htmlAttributes: new @class = "control-label col-md-2" ) <div class="col-md-10"> @Html.EditorFor(model => model.DepartmentID, new htmlAttributes = new @class = "form-control" ) @Html.ValidationMessageFor(model => model.DepartmentID, "", new @class = "text-danger" ) </div> </div> <div class="form-group"> <div class="col-md-offset-2 col-md-10"> <input type="submit" value="Save" class="btn btn-default" /> </div> </div> </div> <div> @Html.ActionLink("Back to List", "Index") </div>

Gender Edit Model

But when we click on the save button it will throw an error resource that can not found. Why because EmployeeController class does not have Edit Method which responds to Post a request for Edit. Below is an error is thrown when we click on the Save button.

Server Error in ‘/’ Application.

The resource cannot be found.

Description: HTTP 404. The resource you are looking for (or one of its dependencies) could have been removed, had its name changed, or is temporarily unavailable.  Please review the following URL and make sure that it is spelled correctly. Requested URL: /Employee/Edit

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.8.4075.0

Error in Asp.Net MVC

To Update Employee, we create one stored procedure in our database which is below,

Create Procedure [dbo].[spUpdateEmployee] @emptid int, @name nvarchar(20), @salary float, @gender nvarchar(20), @city nvarchar(20), @email nvarchar(30), @deptid int as begin Update Employee set EmpEmail=@email,EmpName=@name,EmpSalary=@salary,EmpGender=@gender, DepartmentID=@deptid where EmpID=@emptid; End Now we need to call this Procedure in our database class the method is below public void UpdateEmployee(int EmpId, string Name, string Email, string Gender, double salary, int deptid, string city) using (var con = new SqlConnection(this._ConnectionString)) con.Open(); var cmd = new SqlCommand("spUpdateEmployee", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("name", SqlDbType.NVarChar, 20)); cmd.Parameters.Add(new SqlParameter("salary", SqlDbType.Float, 50)); cmd.Parameters.Add(new SqlParameter("gender", SqlDbType.NVarChar, 50)); cmd.Parameters.Add(new SqlParameter("city", SqlDbType.NVarChar, 50)); cmd.Parameters.Add(new SqlParameter("email", SqlDbType.NVarChar, 50)); cmd.Parameters.Add(new SqlParameter("deptid", SqlDbType.Int, 50)); cmd.Parameters.Add(new SqlParameter("emptid", SqlDbType.Int, 50)); cmd.Parameters["name"].Value = Name; cmd.Parameters["salary"].Value = salary; cmd.Parameters["gender"].Value = Gender; cmd.Parameters["city"].Value = city; cmd.Parameters["email"].Value = Email; cmd.Parameters["deptid"].Value = deptid; cmd.Parameters["emptid"].Value = EmpId; cmd.ExecuteNonQuery();

After this, we create a method in business class, which is shown below

public void UpdateEmployee(Employee employee) this.dataAccess.UpdateEmployee(employee.EmpID,employee.EmpName, employee.EmpEmail, employee.EmpGender, employee.EmpSalary, employee.DepartmentID, employee.EmpCity);

Now we create a method in our EmployeeController for handling post request of Edit method which is shown in below

[HttpPost] public ActionResult Edit(Employee employee) if (ModelState.IsValid) new BusinessLogic.Business().UpdateEmployee(employee); return RedirectToAction("DisplayCompleteEmployee"); return View(employee);

First, we see all Employee from our list and click on Edit link for this display all Employee UI looks like below

Edit Link Click In Asp.Net MVC

After clicking on edit we edit details Ui looks like below

Edit In Asp.net MVC

After clicking on Save we return to DisplayEmployee List and our updated values reflected on UI as expected.

Completed Edit in Asp.Net MVC

Now our edit functionality is completed.

GitHub Pages :- https://github.com/Sagar-Jaybhay/AspNetRazor

[phungthaihy]

SQL For Data Science Tutorial | Learn SQL Database For Data Science | Edureka http://ehelpdesk.tk/wp-content/uploads/2020/02/logo-header.png [ad_1] Data Science Master Program: htt... #androiddevelopment #angular #c #css #dataanalysis #datascience #datasciencecourse #datascienceedureka #datascienceusingsql #databasefordatascience #deeplearning #development #docker #edureka #iosdevelopment #java #javascript #learnsql #learnsqldatabasefordatascience #learnsqlfordatascience #machinelearning #mysql #mysqlcommands #node.js #python #react #sqlanddatascience #sqlbasicsfordatascience #sqlcommands #sqldatasciencetutorial #sqldatabaseindatascience #sqledureka #sqlforbeginners #sqlfordatascience #sqlindatascience #sqltutorial #unity #webdevelopment #whatisdatascience #whatismysql #ytccon

[knowledgewiki]

How to add a dropdownlist that’s dynamically populated from database to each row in datatable gridview

So I’ve created a fully functional gridview that has the two last columns as dropdownlists that are populated from the database and they read/write from it. But I needed to implement search unto the gridview so I used the Datatables.net plugin, It works perfectly but as soon as I add the drop down lists, the search is disabled. Unless I add them through javascript and I’m not nearly experienced enough in that field to accomplish that on my own.

Before I switched to the datatable plug in, I used to just append the dropdownlists I needed at the Rowdatabound event of the gridview, How can I achieve something similar through javascript ?

This is the java script code :

var TableContent = "<tr>"; if (!$("#req").length) $("body").append("<table id='req'></table>"); else $('#req').prepend($('<thead>').append($('.add_ADU'))); var colns = [ { "data": "Request_ID", title: "ID", }, { "data": "Student_ID", visible:true }, { "data": "Type", visible:true }, { "data": "Description", visible:true }, { "data": "Language", visible:true }, { "data": "PUC", visible:true }, { "data": "Quantity", title: "Quantity" }, { "data": "Comments", title: "Comments" }, { "data": "Fees", title: "Fees" }, { "data": "Date", title: "Date" }, { "data": "Payment_Status", title: "Payment Status" }, { "data": "Payment_Date", title: "Payment Date" }, { "data": "Student_Name", title: "Student Name", visible:true }, { "data": "Payment_ID",title:"Payment ID", title: "Phone" }, { "data": "Addressed_To",title: "Addressed To", visible:true }, { "data": "username", title:"Assigned To",visible:true }, { "data": "status", title:"Status",visible:true } ]; var table = $('#req').DataTable({ select: true, "columns": colns, "order": [[ 12, "desc" ]], "initComplete": function (settings, json) { reset_table_page_length($(window).height()); }, }); $("#req tr").append( '<select id="assign"' + '<option value="Paid">him</option>' + '<option value="NotPaid">her</option>' + '</select>' ) $("#req_filter").prepend( '<select id="inp-sel-status-fltr" class="table-toolbar-inp">' + '<option value="Paid">Paid</option>' + '<option value="NotPaid">Not Paid</option>'+ '</select>' ) $("#inp-sel-status-fltr").change(function myfunction() { filter_table_by_status($(this).val()); }); function filter_table_by_status(val) { table.column(10).search(val); table.draw(); }

As pathetic as it is, this is how far I’ve gotten to achieving what I’m trying to :

$("#req tr").append( '<select id="assign"' + '<option value="Paid">him</option>' + '<option value="NotPaid">her</option>' + '</select>')

This is the HTML code for the gridview :

<div class="container"> <asp:GridView BorderWidth="0" CssClass="stripe compact order-column row-border" runat="server" AutoGenerateColumns="true" ID="req" OnRowDataBound="req_RowDataBound" ClientIDMode="Static" Style="width: 100% !important"> <HeaderStyle CssClass="add_ADU" /> </asp:GridView>

and in the code behind I would just append the dropdownlists and then query the data I needed and populate them with it using this code:

protected void req_RowDataBound(object sender, GridViewRowEventArgs e) { if (e.Row.RowType == DataControlRowType.DataRow) { DataTable table = new DataTable(); var openCloseddl = e.Row.FindControl("openCloseddl") as DropDownList; var assignddl = e.Row.FindControl("assignToddl") as DropDownList; assignddl.DataMember = "username"; assignddl.DataValueField = "username"; assignddl.DataSource = table; //get the values of the conditions that define which staff var request_id = e.Row.Cells[0].Text; var type = e.Row.Cells[3].Text; var description = e.Row.Cells[4].Text; var puc = e.Row.Cells[6].Text; var quantity = e.Row.Cells[7].Text; //fetch which staff member it's assigned to if exists. SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["studentOrderUpload"].ConnectionString); SqlCommand comm = new SqlCommand("SELECT username from request_status WHERE request_id = @request_id and type = @type and description = @description and puc = @puc and quantity = @quantity", conn); comm.Parameters.AddWithValue("@request_id", request_id); comm.Parameters.AddWithValue("@type", type); comm.Parameters.AddWithValue("@description", description); comm.Parameters.AddWithValue("@puc", puc); comm.Parameters.AddWithValue("@quantity", quantity); try { conn.Open(); object result = comm.ExecuteScalar(); string resultText = (result == null ? "" : result.ToString()); conn.Close(); //if it's not assigned to anyone, show every staff member. if (resultText == "") { if (type.ToLower().Contains("customized")) { SqlCommand cmd1 = new SqlCommand("select username from staff where type = 'customized'", conn); conn.Open(); SqlDataAdapter ad1 = new SqlDataAdapter(cmd1); ad1.Fill(table); conn.Close(); assignddl.DataBind(); assignddl.SelectedValue = "Scholarship"; if (openCloseddl.SelectedValue == "Open") { openCloseddl.Enabled = true; } assignddl.Items.RemoveAt(1); } else { SqlCommand cmd1 = new SqlCommand("select username from staff where type ='standard'", conn); conn.Open(); SqlDataAdapter ad1 = new SqlDataAdapter(cmd1); ad1.Fill(table); conn.Close(); assignddl.DataBind(); } assignddl.Items.Insert(0, new ListItem("Select User")); openCloseddl.Enabled = false; if (openCloseddl.SelectedValue == "Open" && type.ToLower().Contains("customized")) { openCloseddl.Enabled = true; } } //if it's assigned to someone, show only the assigned staff, else { if (type.ToLower().Contains("customized")) { SqlCommand cmd1 = new SqlCommand("select username from staff where type = 'customized'", conn); conn.Open(); SqlDataAdapter ad1 = new SqlDataAdapter(cmd1); ad1.Fill(table); conn.Close(); assignddl.DataBind(); assignddl.SelectedValue = "Scholarship"; if (openCloseddl.SelectedValue == "Open") { openCloseddl.Enabled = true; } assignddl.Items.RemoveAt(1); } else { SqlCommand cmd1 = new SqlCommand("select username from staff where type = 'standard'", conn); conn.Open(); SqlDataAdapter ad1 = new SqlDataAdapter(cmd1); ad1.Fill(table); conn.Close(); assignddl.DataBind(); assignddl.SelectedValue = resultText; } } if (openCloseddl != null) { SqlCommand comm1 = new SqlCommand("SELECT status from request_status WHERE request_id = @request_id and type = @type and description = @description and puc = @puc and quantity = @quantity", conn); comm1.Parameters.AddWithValue("@request_id", request_id); comm1.Parameters.AddWithValue("@type", type); comm1.Parameters.AddWithValue("@description", description); comm1.Parameters.AddWithValue("@puc", puc); comm1.Parameters.AddWithValue("@quantity", quantity); conn.Open(); object result1 = comm1.ExecuteScalar(); conn.Close(); if (result1.ToString() == "Closed") { openCloseddl.SelectedValue = "Closed"; openCloseddl.Enabled = false; assignddl.Enabled = false; if ((HttpContext.Current.User.Identity.Name.Equals("DocRequestAdmin"))) { assignddl.Enabled = true; openCloseddl.Enabled = true; } } } } catch (Exception ee) { var error = ee.Message; conn.Close(); } var paid = e.Row.Cells[12].Text; if (paid.Contains("Not")) { assignddl.SelectedValue = "Select User"; assignddl.Enabled = false; openCloseddl.Enabled = false; } } }

Which is irrelevant at this point I believe, since I’m trying to do this through javascript.

This is a screenshot of what the grid looks like, the dropdown lists I want to add should exist in the last two columns “Assigned To” and “Status”

1 Answer

There is a built in render function in data tables which you can call in your columns objects. The function accepts 3 arguments, the first one is the value of the property which you specified in your “data” property, and the third will return the value of the whole underlying object.

You can read about it here.

For example:

{ "data": "username", title:"Assigned To", visible:true, render: function(data, type, row) { //do sth here } }

In the above render function, the data parameter will return the value of the “username” property, and the row parameter will return the value of all properties of the underlying object.

You can use the data provided by the row parameter to append a value and an id to your dropdown list. If your row parameter returns an array of objects, you can do a for loop to iterate and append every value to the select element.

Archive from: https://stackoverflow.com/questions/59015626/how-to-add-a-dropdownlist-thats-dynamically-populated-from-database-to-each-row

from https://knowledgewiki.org/how-to-add-a-dropdownlist-thats-dynamically-populated-from-database-to-each-row-in-datatable-gridview/