need a add a fixed value to gridview column

Question

User186897 posted

Hello Friendz

I have a query, I have the below code for my webform and the database layout is shown below, now I want to enter @Type column value as AB

Id(primary key) Type  Label     Room ----4 columns in the database....now I have a grid with label and room columns, now when I enter add,update in the gridview footer I want the values id(will be automatically incremented), label(textbox value will be taken) Room(another textbox value will be taken) and Type should take as 'AB' how do I write a query for that

The below is the sqldatasource I have......I want to add TYpe value as AB

 <asp:SqlDataSource ID="SqlDataSourcepabx" runat="server" ConnectionString="<%$ ConnectionStrings:constr %>" DeleteCommand="DELETE FROM [TEquipment] WHERE [Id] = @Id" InsertCommand="INSERT INTO [TEquip] ([ @Label, @Room)" SelectCommand="SELECT  [Label], [Room], [Id] FROM [TEquip]" UpdateCommand="UPDATE [TEquip] SET [Label] = @Label,  [Room] = @Room WHERE [Id] = @Id">
                     <DeleteParameters>
                         <asp:Parameter Name="Id" Type="Int32" />
                     </DeleteParameters>
                     <InsertParameters>
                        <%-- <asp:Parameter Name="Type" Type="String" />--%>-----------?????
                        
                         <asp:Parameter Name="Label" Type="String" />
                         
                         <asp:Parameter Name="Room" Type="String" />
                     </InsertParameters>
                     <UpdateParameters>
                         <%--<asp:Parameter Name="Type" Type="String" />--%>------------------?????
                        
                         
                         <asp:Parameter Name="Label" Type="String" />
                        
                         <asp:Parameter Name="Room" Type="String" />
                         <asp:Parameter Name="Id" Type="Int32" />
                     </UpdateParameters>
                 </asp:SqlDataSource>

Answer 1

User3690988 posted

You could set the Type's DefaultValue.  Then set Type = @Type in your UPDATE command.

<asp:Parameter Name="Type" Type="String" DefaultValue="AB" />

Answer 2

User186897 posted

what id the value is in radiobutton?

i mean if it is radiobutton1 text or radiobutton2 text?how can i defie that?

Answer 3

User3690988 posted

Could you show the GridView code for the Footer, the code behind for the Add/Update button, and your SQL please?

Answer 4

User186897 posted

the sql is the same as i gave the above code

the radiobutton is no in the gridview its outside

 <tr><td >Equipment Survey Type  -  <asp:Label runat="server" Text="AB"  Font-Bold="True" ID="Label50"></asp:Label> </td></tr>
            <tr><td>    Equipment on Site <asp:Radiobutton runat="server" ID="Radiobutton1" Text="Yes" AutoPostBack="true" GroupName="e1" OnCheckedChanged="Radiobutton1_CheckedChanged" /><asp:Radiobutton runat="server" Groupname="e1" autopostback="true" ID="Radiobutton2" Text="No" OnCheckedChanged="Radiobutton2_CheckedChanged"/>
               
          
           </td></tr>
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" visible="false" ShowFooter="True" OnRowCommand="GridView1_RowCommand" DataKeyNames="Id" HeaderStyle-Font-Bold="true"  HeaderStyle-ForeColor="White" HeaderStyle-BackColor="#329bd8" DataSourceID="SqlDataSourceab" AllowPaging="True" AllowSorting="True" >
                     <Columns>           
         
         <asp:TemplateField HeaderText="Labelled Description">
    <ItemTemplate><%# Eval("Label") %></ItemTemplate>
                            
    <EditItemTemplate>
    <asp:TextBox runat="server" ID="Label" Text='<%# Bind("Label")%>' />
    </EditItemTemplate>
    <FooterTemplate>
    <asp:TextBox runat="server" ID="txtlabel" Text='' />
    </FooterTemplate>
    </asp:TemplateField> 
 <asp:TemplateField HeaderText="Room">
    <ItemTemplate><%# Eval("Room") %></ItemTemplate>
                            
    <EditItemTemplate>
    <asp:TextBox runat="server" ID="Room" Text='<%# Bind("Room")%>' />
    </EditItemTemplate>
    <FooterTemplate>
    <asp:TextBox runat="server" ID="txtroom" Text='' />
    </FooterTemplate>
    </asp:TemplateField>

   <asp:TemplateField HeaderText="Action">
    <ItemTemplate>
    <asp:Button runat="server" ID="cmdEdit" Text="Edit" CommandName="Edit" />
    <asp:Button runat="server" ID="cmdDelete" OnClientClick="return confirm('Are you sure to delete')" Text="Delete" CommandName="Delete" />
    </ItemTemplate>
    <EditItemTemplate>
    <asp:Button runat="server" ID="cmdUpdate" Text="Update" CommandName="Update" />
    <asp:Button runat="server" ID="cmdCancel" Text="Cancel" CommandName="Cancel" />
    </EditItemTemplate>
    <FooterTemplate>
    <asp:Button runat="server" ID="cmdAdd" Text="Add " CommandName="New" />
    </FooterTemplate>
    </asp:TemplateField>        

  Protected Sub SqlDataSourceab_Inserting(sender As Object, e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlDataSourceab.Inserting
        e.Command.Parameters.Clear()
 e.Command.Parameters.Add(Label)
      
        e.Command.Parameters.Add(Room)

    Protected Sub GridView1_RowCommand(sender As Object, e As System.Web.UI.WebControls.GridViewCommandEventArgs)
        If e.CommandName = "EmptyNew" Then
  Label.Direction = ParameterDirection.Input
            Label.Value = DirectCast(GridView1.Controls(0).Controls(0).FindControl("txtlabel"), TextBox).Text
          
    Room.Direction = ParameterDirection.Input
      Room.Value = DirectCast(GridView1.Controls(0).Controls(0).FindControl("txtroom"), TextBox).Text

            SqlDataSourceab.Insert()
        End If


        If e.CommandName = "New" Then
 Label.Direction = ParameterDirection.Input
            Label.Value = DirectCast(GridView1.FooterRow.FindControl("txtlabel"), TextBox).Text
         
          Room.Direction = ParameterDirection.Input
            Room.Value = DirectCast(GridView1.FooterRow.FindControl("txtroom"), TextBox).Text

            SqlDataSourceab.Insert()
        End If

    End Sub

 Private Label As New System.Data.SqlClient.SqlParameter("Label", SqlDbType.VarChar, 200)
 Private Room As New System.Data.SqlClient.SqlParameter("CRoom", SqlDbType.VarChar, 200)

Here the radiobutton balue should enter in a column named Estatus how do i do that?

Answer 5

User186897 posted

also I tried to put this @type line which u have given above in the code and it shows the below error

Server Error in '/' Application.

---

Must declare the scalar variable "@Type".

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.SqlClient.SqlException: Must declare the scalar variable "@Type".

Source Error: 

Line 75: CommsRoom.Value = DirectCast(GridView1.FooterRow.FindControl("txtcommsroom"), TextBox).Text Line 76: Line 77: SqlDataSourcepabx.Insert() Line 78: End If Line 79:

Source File:    Line: 77 

Stack Trace: 

[SqlException (0x80131904): Must declare the scalar variable "@Type".] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +2442030 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +5736648 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +628 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +3731 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +225 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest) +2026 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) +375 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) +337 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +280 System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +392

Answer 6

User186897 posted

I really dont understand how do I send this type and equistatus fields to the datatbase on add/update of gridview...can someone plz help me...

Answer 7

User475983607 posted

You need to read the reference documentation.

https://msdn.microsoft.com/en-us/library/z72eefad.aspx

https://msdn.microsoft.com/en-us/library/dz12d98w.aspx

Did you remember to update your SQL statement to include @Type?

Answer 8

User186897 posted

I still cant get it working....it doesnot display error but also doesnot insert value

Answer 9

User-1664007096 posted

Hi Lexi85,

Exception Details: System.Data.SqlClient.SqlException: Must declare the scalar variable "@Type".

According to your description and exception, I suggest that your could reconfigure SqlDataSource control to include Type field.

For more information, click here to refer about Using Parameters with the SqlDataSource Control