Hi lingers,
Check this example. Now please take its reference and correct your code.
SQL
CREATE TABLE orders(
orderid INT,
purchaseorderno VARCHAR(100),
quantity INT,
deliverydate DATETIME,
accepted VARCHAR(100),
waybillno VARCHAR(100),
comment VARCHAR(100),
clicked VARCHAR(100),
)
GO
CREATE TABLE orders_cylinder(
orderid INT,
baseno VARCHAR(100)
)
GO
INSERT INTO orders VALUES(1,'Ord123',5,'2021/07/08','','','','1')
INSERT INTO orders VALUES(2,'Ord567',5,'2021/07/08','','','','1')
HTML
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.24/themes/smoothness/jquery-ui.css" />
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.24/jquery-ui.min.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/tag-it/2.0/css/jquery.tagit.min.css" />
<link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/tag-it/2.0/css/tagit.ui-zendesk.css" />
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/tag-it/2.0/js/tag-it.min.js"></script>
<script type="text/javascript">
$(function () {
$.ajax({
url: "<%=ResolveUrl("Default.aspx/GetCustomers") %>",
data: "{}",
dataType: "json",
type: "POST",
contentType: "application/json; charset=utf-8",
success: function (data) {
var sampleTags = [];
$.each(data.d, function (i, item) {
sampleTags.push(item);
});
$(".myTags").tagit({
availableTags: sampleTags
});
},
error: function (response) {
alert(response.responseText);
},
failure: function (response) {
alert(response.responseText);
}
});
});
function UpdateTags() {
$('[id*=GridView1] tr:has(td)').each(function () {
var ids = [];
$.each($(this).find('.tagit-choice'), function () {
ids.push($(this).find('input[type=hidden]').val());
});
$(this).find('[id*=hfIds]').val(ids.join(','));
});
}
</script>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
<Columns>
<asp:BoundField DataField="orderid" HeaderText="id" />
<asp:BoundField DataField="purchaseorderno" HeaderText="PO NO" />
<asp:BoundField DataField="quantity" HeaderText="Quantity" />
<asp:TemplateField HeaderText="Date">
<ItemTemplate>
<asp:TextBox ID="txtdate" runat="server" Width="100"></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Waybill">
<ItemTemplate>
<asp:TextBox ID="txtwaybill" runat="server" Width="100"></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Comment">
<ItemTemplate>
<asp:TextBox ID="txtcomment" runat="server" Width="100"></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Baseno">
<ItemTemplate>
<ul id="myTags" class="myTags"></ul>
<asp:HiddenField ID="hfIds" runat="server" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<br />
<asp:Button Text="Update" runat="server" OnClick="OnUpdate" OnClientClick="UpdateTags()" />
Namespaces
C#
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;
VB.Net
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Services
Code
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
GridView1.DataSource = GetData();
GridView1.DataBind();
}
}
protected void OnUpdate(object sender, EventArgs e)
{
foreach (GridViewRow row in GridView1.Rows)
{
string orderid = row.Cells[0].Text;
string deliverydate = (row.FindControl("txtdate") as TextBox).Text.Trim();
string waybillno = (row.FindControl("txtwaybill") as TextBox).Text.Trim();
string comment = (row.FindControl("txtcomment") as TextBox).Text.Trim();
string selectedTags = (row.FindControl("hfIds") as HiddenField).Value.Trim().Replace(",", "/");
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
string query = "UPDATE orders SET deliverydate=@deliverydate,accepted=@accepted,waybillno=@waybillno,comment=@comment,clicked ='2' WHERE orderid=@orderid ";
query += " INSERT INTO orders_cylinder (orderid,baseno) VALUES (@orderid,@accepted)";
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = query;
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@orderid", orderid);
cmd.Parameters.AddWithValue("@deliverydate", deliverydate);
cmd.Parameters.AddWithValue("@waybillno", waybillno);
cmd.Parameters.AddWithValue("@comment", comment);
cmd.Parameters.AddWithValue("@accepted", selectedTags);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
Response.Redirect(Request.Url.AbsoluteUri);
}
[WebMethod]
public static string[] GetCustomers()
{
List<string> customers = new List<string>();
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = "SELECT [CustomerID] FROM [Customers]";
cmd.Connection = conn;
conn.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
customers.Add(sdr["CustomerID"].ToString());
}
}
conn.Close();
}
}
return customers.ToArray();
}
private DataTable GetData()
{
string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
string query = "SELECT * FROM orders";
using (SqlConnection con = new SqlConnection(conString))
{
SqlCommand cmd = new SqlCommand(query);
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
return dt;
}
}
}
}
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
GridView1.DataSource = GetData()
GridView1.DataBind()
End If
End Sub
Protected Sub OnUpdate(ByVal sender As Object, ByVal e As EventArgs)
For Each row As GridViewRow In GridView1.Rows
Dim orderid As String = row.Cells(0).Text
Dim deliverydate As String = (TryCast(row.FindControl("txtdate"), TextBox)).Text.Trim()
Dim waybillno As String = (TryCast(row.FindControl("txtwaybill"), TextBox)).Text.Trim()
Dim comment As String = (TryCast(row.FindControl("txtcomment"), TextBox)).Text.Trim()
Dim selectedTags As String = (TryCast(row.FindControl("hfIds"), HiddenField)).Value.Trim().Replace(",", "/")
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(constr)
Dim query As String = "UPDATE orders SET deliverydate=@deliverydate,accepted=@accepted,waybillno=@waybillno,comment=@comment,clicked ='2' WHERE orderid=@orderid "
query += " INSERT INTO orders_cylinder (orderid,baseno) VALUES (@orderid,@accepted)"
Using cmd As SqlCommand = New SqlCommand()
cmd.CommandText = query
cmd.Connection = con
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("@orderid", orderid)
cmd.Parameters.AddWithValue("@deliverydate", deliverydate)
cmd.Parameters.AddWithValue("@waybillno", waybillno)
cmd.Parameters.AddWithValue("@comment", comment)
cmd.Parameters.AddWithValue("@accepted", selectedTags)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using
End Using
Next
Response.Redirect(Request.Url.AbsoluteUri)
End Sub
<WebMethod>
Public Shared Function GetCustomers() As String()
Dim customers As List(Of String) = New List(Of String)()
Using conn As SqlConnection = New SqlConnection()
conn.ConnectionString = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using cmd As SqlCommand = New SqlCommand()
cmd.CommandText = "SELECT [CustomerID] FROM [Customers]"
cmd.Connection = conn
conn.Open()
Using sdr As SqlDataReader = cmd.ExecuteReader()
While sdr.Read()
customers.Add(sdr("CustomerID").ToString())
End While
End Using
conn.Close()
End Using
End Using
Return customers.ToArray()
End Function
Private Function GetData() As DataTable
Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Dim query As String = "SELECT * FROM orders"
Using con As SqlConnection = New SqlConnection(conString)
Dim cmd As SqlCommand = New SqlCommand(query)
Using sda As SqlDataAdapter = New SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
Using dt As DataTable = New DataTable()
sda.Fill(dt)
Return dt
End Using
End Using
End Using
End Function
Screenshots
Record before insert
GridView with inserted data
Record after insert and update
