'Problem with InsertCommand in SqlDataSource. ERROR: System.Data.SqlClient.SqlException: 'You must declare the scalar variable'@EXPEDIENTE'.'
I am a newbie and I have recently started working developing a web application, I do not have much experience so if you are able to solve the problem and explain it in detail I would be much more than grateful. I am trying to insert in Visual Studio through a Gridview a series of data in my database. For this I have several textboxes and a dropdownlist in the FooterRow to obtain a series of data and the other data that is missing in the insert I obtain it by looking at other GridViews/querying the database. At the end, after successfully obtaining all the data I need to insert through the SqlDataSource, I am ready to insert the data with cmd.ExecuteNonQuery(); But when trying to do it I got this error: System.Data.SqlClient.SqlException: 'You must declare the scalar variable '@EXPEDIENTE'.'
My GridView code is :
<asp:GridView ID="GridView3" runat="server" AutoGenerateColumns="False" CellPadding="4" DataSourceID="SqlDataSource3" ForeColor="#333333" GridLines="None" Width="723px" ShowFooter="True" AllowPaging="True" AllowSorting="True" DataKeyNames="EXPEDIENTE,NUMERO_COMENTARIO" OnSelectedIndexChanged="GridView3_SelectedIndexChanged">
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
<Columns>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowSelectButton="True" />
<asp:TemplateField HeaderText="EXPEDIENTE" SortExpression="EXPEDIENTE">
<EditItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Eval("EXPEDIENTE") %>'></asp:Label>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("EXPEDIENTE") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:LinkButton ID="LinkButton2" runat="server" OnClick="Click_addUser" >Añadir Comentario</asp:LinkButton>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="NUMERO_COMENTARIO" SortExpression="NUMERO_COMENTARIO">
<EditItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Eval("NUMERO_COMENTARIO") %>'></asp:Label>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Bind("NUMERO_COMENTARIO") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="FECHA" SortExpression="FECHA">
<EditItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Eval("FECHA") %>'></asp:Label>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Bind("FECHA") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="FECHA" runat="server"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="COMENTARIO" SortExpression="COMENTARIO">
<EditItemTemplate>
<asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("COMENTARIO") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label4" runat="server" Text='<%# Bind("COMENTARIO") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="COMENTARIO" runat="server"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="USUARIO" SortExpression="USUARIO">
<EditItemTemplate>
<asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("USUARIO") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label5" runat="server" Text='<%# Bind("USUARIO") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:DropDownList ID="DropDownList1" runat="server" EnableViewState="false" DataSourceID="SqlDataSource4" DataTextField="NOMBRE_USUARIO"
DataValueField="NOMBRE_USUARIO" AppendDataBoundItems="True" ></asp:DropDownList>
</FooterTemplate>
</asp:TemplateField>
</Columns>
<EditRowStyle BackColor="#999999" />
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#E9E7E2" />
<SortedAscendingHeaderStyle BackColor="#506C8C" />
<SortedDescendingCellStyle BackColor="#FFFDF8" />
<SortedDescendingHeaderStyle BackColor="#6F8DAE" />
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:DESAConnectionString %>"
SelectCommand="SELECT A.ID_EXPE EXPEDIENTE,A.ID_COEX NUMERO_COMENTARIO, convert(varchar, A.FECHA_COMENTARIO, 3 ) FECHA, A.COMENTARIO,E.NOMBRE_USUARIO USUARIO FROM COEX_COMENTARIOS_EXPEDIENTES A , EXPE_EXPEDIENTES B,CLIE_CLIENTES C,EXCL_EXPEDIENTES_CLIENTES D,USUA_USUARIOS E WHERE A.ID_EXPE = B.ID_EXPE AND B.ID_EXPE=D.ID_EXPE AND C.ID_CLIENTE=D.ID_CLIENTE AND E.ID_USUA=A.ID_USUA;"
DeleteCommand="DELETE FROM [COEX_COMENTARIOS_EXPEDIENTES] WHERE ([ID_EXPE] = @EXPEDIENTE) AND ([ID_COEX] = @NUMERO_COMENTARIO)"
InsertCommand="INSERT INTO [COEX_COMENTARIOS_EXPEDIENTES] ([ID_EXPE], [ID_COEX], [FECHA_COMENTARIO], [COMENTARIO], [ID_USUA]) VALUES (@EXPEDIENTE,@NUMERO_COMENTARIO,@FECHA,COMENTARIO,@USUARIO)"
UpdateCommand="UPDATE [COEX_COMENTARIOS_EXPEDIENTES] SET [COMENTARIO] = @COMENTARIO, [ID_USUA] =(SELECT ID_USUA FROM USUA_USUARIOS WHERE NOMBRE_USUARIO=@USUARIO) WHERE [ID_EXPE] = @EXPEDIENTE AND [ID_COEX] = @NUMERO_COMENTARIO">
<DeleteParameters>
<asp:Parameter Name="ID_EXPE" Type="Int32" />
<asp:Parameter Name="ID_COEX" Type="Int32" />
</DeleteParameters>
<InsertParameters>
<asp:Parameter Name="ID_EXPE" Type="Int32" />
<asp:Parameter Name="ID_COEX" Type="Int32" />
<asp:Parameter DbType="Date" Name="FECHA_COMENTARIO" />
<asp:Parameter Name="COMENTARIO" Type="String" />
<asp:Parameter Name="ID_USUA" Type="Int32" />
</InsertParameters>
<UpdateParameters>
<asp:Parameter DbType="Date" Name="FECHA_COMENTARIO" />
<asp:Parameter Name="COMENTARIO" Type="String" />
<asp:Parameter Name="ID_USUA" Type="Int32" />
<asp:Parameter Name="ID_EXPE" Type="Int32" />
<asp:Parameter Name="ID_COEX" Type="Int32" />
</UpdateParameters>
</asp:SqlDataSource>
And the Code of the process that gets executed when i click on the "Añadir comentario" (Add comment in spanish) button is :
protected void Click_addUser(object sender, EventArgs e)
{
bool conected = false;
try
{
conection.Close();
conection.Open();
conected = true;
}
catch (SqlException ex)
{
string errorconection = ex.Message;
}
GridViewRow gvr = (GridViewRow)GridView3.FooterRow;
DropDownList ddl = (DropDownList)gvr.FindControl("DropDownList1");
// String nombre_usua = ((DropDownList)GridView3.FooterRow.FindControl("USUARIO")).Text;
String nombre_usua=ddl.SelectedValue;
String id_usua;
String sSql = "select ID_USUA FROM USUA_USUARIOS WHERE NOMBRE_USUARIO='" + nombre_usua + "'";
SqlDataSource5.SelectCommand = sSql;
DataView dv = (DataView)SqlDataSource5.Select(DataSourceSelectArguments.Empty);
id_usua=dv.Table.Rows[0][0].ToString();
sSql = "select ID_EXPE FROM EXPE_EXPEDIENTES WHERE ID_EXPE="+GridView2.Rows[1].Cells[0].Text+"";
SqlDataSource6.SelectCommand = sSql;
dv = (DataView)SqlDataSource6.Select(DataSourceSelectArguments.Empty);
string id_Expe=dv.Table.Rows[0][0].ToString();
sSql = "select MAX(ID_COEX)+1 from COEX_COMENTARIOS_EXPEDIENTES where ID_EXPE="+id_Expe+"";
SqlDataSource7.SelectCommand = sSql;
dv = (DataView)SqlDataSource7.Select(DataSourceSelectArguments.Empty);
string id_Coex = dv.Table.Rows[0][0].ToString();
SqlCommand cmd = new SqlCommand("INSERT INTO [COEX_COMENTARIOS_EXPEDIENTES] ([ID_EXPE], [ID_COEX], [FECHA_COMENTARIO], [COMENTARIO], [ID_USUA]) VALUES (@EXPEDIENTE, @NUMERO_COMENTARIO, @FECHA, @COMENTARIO,@USUARIO)", conection);
SqlParameter param = new SqlParameter();
param.ParameterName = "@EXPEDIENTE";
param.Value = id_Expe;
cmd.Parameters.Add(param);
SqlParameter param2 = new SqlParameter();
param.ParameterName = "@NUMERO_COMENTARIO";
param.Value = id_Coex;
cmd.Parameters.Add(param2);
SqlParameter param3 = new SqlParameter();
param.ParameterName = "@FECHA";
param.Value = ((TextBox)GridView3.FooterRow.FindControl("FECHA")).Text;
cmd.Parameters.Add(param3);
SqlParameter param4 = new SqlParameter();
param.ParameterName = "@COMENTARIO";
param.Value = ((TextBox)GridView3.FooterRow.FindControl("COMENTARIO")).Text; ;
cmd.Parameters.Add(param4);
SqlParameter param5 = new SqlParameter();
param.ParameterName = "@USUARIO";
param.Value = id_usua;
cmd.Parameters.Add(param5);
cmd.ExecuteNonQuery();}
The error is on the cmd.ExecuteNonQuery();
I am turning crazy trying to find a solution , if anyone could help me i would be so thankful.Thank you very much in advance
Solution 1:[1]
i already found the problem. In the C# code i used the parameter param all the time and i needed to use param2 , param3, param4 etc.... Changed that and everything works perfect.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | AntonSolicitors |