im trying to insert foreign keys into table with DropDownList,
it looks good but i get this error when i press the add button
"mysql.data.mysqlclient.mysqlexception {"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') values (1,1,1,1)' at line 1"}.
the tables have another Variables but now they dont Important for me and they defaults is null.
i dont have enough reputation to add image with the relationships between the tables.
thanks alot!!
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using MySql.Data.MySqlClient;
namespace WebApplication1
{
public partial class usageDisp : System.Web.UI.Page
{
string connectionstring = @"Data Source=localhost; Database=globaldotdb; user ID=root; Password=peleg1708";
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
//check
BindData();
}
}
private void BindData()
{
using (MySqlConnection cn = new MySqlConnection(connectionstring))
{
MySqlDataAdapter adp = new MySqlDataAdapter(("SELECT tblusage.codeUsage,tblcustom.Customer, tblvendor.Vendor, tblusage.dateStart, tblusage.dateEnd, tblregion.Region, tblservice.Service, tblservice.unit, tblusage.isSecure,tblusage.Usage FROM ((((tblvendor INNER JOIN tblusage ON tblvendor.codeVendor = tblusage.codeVendor) INNER JOIN tblservice ON tblusage.codeService = tblservice.codeService) INNER JOIN tblregion ON tblusage.codeRegion = tblregion.codeRegion) INNER JOIN tblcustom ON tblusage.codeCust = tblcustom.codeCust)"), cn);
DataTable dt = new DataTable();
adp.Fill(dt);
if (dt.Rows.Count > 0)
{
gv.DataSource = dt;
gv.DataBind();
}
}
}
protected void gv_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
int codeusage = int.Parse(gv.DataKeys[e.RowIndex].Value.ToString());
deleteusage(codeusage);
BindData();
}
private void deleteusage(int codeusage)
{
using (MySqlConnection cn = new MySqlConnection(connectionstring))
{
string query = "DELETE FROM tblusage WHERE codeUsage=" + codeusage + " ";
MySqlCommand cmd = new MySqlCommand(query, cn);
cn.Open();
cmd.ExecuteNonQuery();
}
}
protected void gv_DataBound(object sender, EventArgs e)
{
DropDownList DDLCu = gv.FooterRow.FindControl("DDLCu") as DropDownList;
DropDownList DDLVe = gv.FooterRow.FindControl("DDLVe") as DropDownList;
DropDownList DDLSe = gv.FooterRow.FindControl("DDLSe") as DropDownList;
DropDownList DDLRe = gv.FooterRow.FindControl("DDLRe") as DropDownList;
using (MySqlConnection cn = new MySqlConnection(connectionstring))
{
MySqlDataAdapter Cadp = new MySqlDataAdapter(("SELECT * from tblcustom"), cn);
DataTable Cdt = new DataTable();
Cadp.Fill(Cdt);
if (Cdt.Rows.Count > 0)
{
DDLCu.DataSource = Cdt;
DDLCu.DataTextField = "Customer";
DDLCu.DataValueField = "codeCust";
DDLCu.DataBind();
}
MySqlDataAdapter Vadp = new MySqlDataAdapter(("SELECT * from tblvendor"), cn);
DataTable Vdt = new DataTable();
Vadp.Fill(Vdt);
if (Vdt.Rows.Count > 0)
{
DDLVe.DataSource = Vdt;
DDLVe.DataTextField = "Vendor";
DDLVe.DataValueField = "codeVendor";
DDLVe.DataBind();
}
MySqlDataAdapter Sadp = new MySqlDataAdapter(("SELECT * from tblservice"), cn);
DataTable Sdt = new DataTable();
Sadp.Fill(Sdt);
if (Sdt.Rows.Count > 0)
{
DDLSe.DataSource = Sdt;
DDLSe.DataTextField = "Service";
DDLSe.DataValueField = "codeService";
DDLSe.DataBind();
}
MySqlDataAdapter Radp = new MySqlDataAdapter(("SELECT * from tblregion"), cn);
DataTable Rdt = new DataTable();
Radp.Fill(Rdt);
if (Rdt.Rows.Count > 0)
{
DDLRe.DataSource = Rdt;
DDLRe.DataTextField = "Region";
DDLRe.DataValueField = "codeRegion";
DDLRe.DataBind();
}
}
}
protected void lnkAdd_Click(object sender, EventArgs e)
{
DropDownList DDLCu = gv.FooterRow.FindControl("DDLCu") as DropDownList;
DropDownList DDLVe = gv.FooterRow.FindControl("DDLVe") as DropDownList;
DropDownList DDLSe = gv.FooterRow.FindControl("DDLSe") as DropDownList;
DropDownList DDLRe = gv.FooterRow.FindControl("DDLRe") as DropDownList;
int cc = int.Parse(DDLCu.SelectedValue);
int cv = int.Parse(DDLVe.SelectedValue);
int cs = int.Parse(DDLSe.SelectedValue);
int cr = int.Parse(DDLRe.SelectedValue);
add(cc, cv, cs, cr );
BindData();
Response.Redirect("http://localhost:56717/usage.aspx");
}
private void add(int cc, int cv, int cs, int cr)
{
using (MySqlConnection cn = new MySqlConnection(connectionstring))
{
string query = "insert into tblusage(codeCust,codeVendor,codeService,codeRegion,) values (" + cc + "," + cv +"," + cs + "," + cr + ") ";
MySqlCommand cmd = new MySqlCommand(query, cn);
cn.Open();
cmd.ExecuteNonQuery();
}
}
}
}
<asp:GridView ID="gv" runat="server"
DataKeyNames="codeUsage"
onrowdeleting="gv_RowDeleting"
AutoGenerateColumns="False" ondatabound="gv_DataBound" ShowFooter="True">
<Columns>
<asp:TemplateField HeaderText="codeusage" Visible="False">
<EditItemTemplate>
<asp:TextBox ID="txtcode" runat="server" Text='<%# Eval("codeUsage") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Eval("codeUsage") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Customer">
<EditItemTemplate>
<asp:TextBox ID="TXTCust" runat="server" Text='<%# Eval("Customer") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:DropDownList ID="DDLCu" runat="server" AutoPostBack="True">
</asp:DropDownList>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Eval("Customer") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Vendor">
<EditItemTemplate>
<asp:TextBox ID="TextBox1" runat="server" Text='<%# Eval("Vendor") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:DropDownList ID="DDLVe" runat="server" AutoPostBack="True">
</asp:DropDownList>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Eval("Vendor") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="dateStart">
<EditItemTemplate>
<asp:TextBox ID="TXTDS" runat="server" Text='<%# Eval("dateStart") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label4" runat="server" Text='<%# Eval("dateStart") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="dateEnd">
<EditItemTemplate>
<asp:TextBox ID="TXTDE" runat="server" Text='<%# Eval("dateEnd") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label5" runat="server" Text='<%# Eval("dateEnd") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="service">
<EditItemTemplate>
<asp:TextBox ID="TXTSe" runat="server" Text='<%# Eval("Service") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:DropDownList ID="DDLSe" runat="server" AutoPostBack="True">
</asp:DropDownList>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label6" runat="server" Text='<%# Eval("Service") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="region">
<EditItemTemplate>
<asp:TextBox ID="TXTRe" runat="server" Text='<%# Eval("Region") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:DropDownList ID="DDLRe" runat="server" AutoPostBack="True">
</asp:DropDownList>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label7" runat="server" Text='<%# Eval("Region") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="isSecure">
<EditItemTemplate>
<asp:TextBox ID="TXTIS" runat="server" Text='<%# Eval("isSecure") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label8" runat="server" Text='<%# Eval("isSecure") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="unit">
<EditItemTemplate>
<asp:TextBox ID="TXTunit" runat="server" Text='<%# Eval("unit") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label9" runat="server" Text='<%# Eval("unit") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="usage">
<EditItemTemplate>
<asp:TextBox ID="TXTusage" runat="server" Text='<%# Eval("Usage") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:LinkButton ID="lnkAdd" runat="server" onclick="lnkAdd_Click">add</asp:LinkButton>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label10" runat="server" Text='<%# Eval("Usage") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:CommandField HeaderText="Operation" ShowDeleteButton="True" />
</Columns>
</asp:GridView>
