Hi guys,

I am a newbie in asp and i have a problem.

I have a webpage that allow user to input their info in the webpage.

I am using stored procedure as the insert command in this webpage.

When a button is click, the data should go to the database but not.

This is the webapge code:

<asp:Content ID="Content1" ContentPlaceHolderID="StaffMainPageCPH" Runat="Server">
    <asp:ScriptManager ID="ScriptManager1" runat="server">
    </asp:ScriptManager>
        <table id="AppointmentTable" runat="server">
            <tr>
                <td colspan="3">
                    Appointment</td>
            </tr>
            <tr>
                <td>
                    Order No:</td>
                <td>
                    <asp:TextBox ID="OrderNo_TextBox" runat="server"></asp:TextBox>
                    <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
                    <asp:Label ID="Label2" runat="server" Text="Label"></asp:Label>
                </td>
                <td>
                    <asp:Button ID="Button2" runat="server" Text="Button" />
                </td>
            </tr>
            <tr>
                <td>
                    Type of staff:</td>
                <td colspan="2">
                    <asp:CheckBoxList ID="CheckBoxList4" runat="server">
                        <asp:ListItem>Photographer</asp:ListItem>
                        <asp:ListItem>Designer</asp:ListItem>
                        <asp:ListItem>Stylist</asp:ListItem>
                    </asp:CheckBoxList>
                </td>
            </tr>
            <tr>
                <td>
                    Search Availability:</td>
                <td>
                    <asp:TextBox ID="SearchTimeAvalability_TextBox" runat="server"></asp:TextBox>
                    <cc1:CalendarExtender ID="SearchTimeAvalability_TextBox_CalendarExtender"
                        runat="server" Enabled="True"
                        TargetControlID="SearchTimeAvalability_TextBox" Format="dd/M/yyyy">
                    </cc1:CalendarExtender>
                    <asp:Label ID="Label3" runat="server" Text="Label"></asp:Label>
                </td>
                <td>
                        <asp:Button ID="SearchAvailability_Button" runat="server" Text="Search" />
                </td>
            </tr>
            <tr>
                <td>
                    Staff Avalability List:</td>
                <td colspan="2">
                    <asp:GridView ID="GridView1" runat="server"
                        DataSourceID="StaffAvailabilitySqlDataSource">
                        <Columns>
                            <asp:TemplateField>
                            <ItemTemplate>
                                <asp:CheckBox runat="server" ID="StaffSelector" />
                            </ItemTemplate>
                            </asp:TemplateField>
                        </Columns>
                    </asp:GridView>
                    <asp:SqlDataSource ID="StaffAvailabilitySqlDataSource" runat="server"
                        ConnectionString="<%$ ConnectionStrings:MilanDatabaseConnectionString1 %>"
                        SelectCommand="SearchStaffAvailability" SelectCommandType="StoredProcedure">
                        <SelectParameters>
                            <asp:ControlParameter ControlID="SearchTimeAvalability_TextBox" DbType="DateTime"
                                Name="AvailabilityDate" PropertyName="Text" />
                            <asp:ControlParameter ControlID="CheckBoxList4" Name="RolesName"
                                PropertyName="SelectedValue" Type="String" />
                        </SelectParameters>
                    </asp:SqlDataSource>
                            </td>
            </tr>
            <tr>
                <td>
                    Appointment Time:</td>
                <td colspan="2">
                    <asp:DropDownList ID="Hour_DropDownList" runat="server">
                        <asp:ListItem>1</asp:ListItem>
                        <asp:ListItem>2</asp:ListItem>
                        <asp:ListItem>3</asp:ListItem>
                        <asp:ListItem>4</asp:ListItem>
                        <asp:ListItem>5</asp:ListItem>
                        <asp:ListItem>6</asp:ListItem>
                        <asp:ListItem>7</asp:ListItem>
                        <asp:ListItem>8</asp:ListItem>
                        <asp:ListItem>9</asp:ListItem>
                        <asp:ListItem>10</asp:ListItem>
                        <asp:ListItem>11</asp:ListItem>
                        <asp:ListItem>12</asp:ListItem>
                    </asp:DropDownList>
                    :<asp:DropDownList ID="Minute_DropDownList" runat="server">
                        <asp:ListItem>00</asp:ListItem>
                        <asp:ListItem>15</asp:ListItem>
                        <asp:ListItem>30</asp:ListItem>
                        <asp:ListItem>45</asp:ListItem>
                    </asp:DropDownList>
&nbsp;&nbsp;
                    <asp:DropDownList ID="AMPM_DropDownList" runat="server">
                        <asp:ListItem Selected="True">AM</asp:ListItem>
                        <asp:ListItem>PM</asp:ListItem>
                    </asp:DropDownList>
                            </td>
            </tr>
            <tr>
                <td>
                    Appointment Type:</td>
                <td colspan="2">
                    <asp:DropDownList ID="AppointmentType_DropDownList" runat="server">
                        <asp:ListItem>Select One</asp:ListItem>
                        <asp:ListItem>Indoor</asp:ListItem>
                        <asp:ListItem>Outdoor</asp:ListItem>
                    </asp:DropDownList>
                </td>
            </tr>
            <tr>
                <td>
                    Venue:</td>
                <td colspan="2">
                    <asp:TextBox ID="Venue_TextBox" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td colspan="3">
                    <center><asp:Button ID="NewCustSubmitForm_Button" runat="server" Text="Submit" /></center>
                </td>
            </tr>
        </table>
</asp:Content>

This is the stored procedure for the insert command

@OrderNo varchar(10),
    @StaffNo varchar(10),
    @AvailabilityDate datetime,
    @AppointmentType varchar(10),
    @Venue varchar(50)
AS
    /* SET NOCOUNT ON */
    INSERT INTO StaffAvailability (StaffNo, OrderNo, AvailabilityDate, AppointmentType, Venue)
    VALUES (@StaffNo, @OrderNo, @AvailabilityDate, @AppointmentType, @Venue)
    RETURN

And this is the event handler

Protected Sub NewCustSubmitForm_Button_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles NewCustSubmitForm_Button.Click
        Dim selectStaff As New SqlDataSource
        selectStaff.ConnectionString = ConfigurationManager.ConnectionStrings("MilanDatabaseConnectionString1").ToString
        Dim NewCustomerConnection As String = "Server=KAZESENSEI-PC\SQLEXPRESS;Database=MilanDatabase;Trusted_Connection=True;"

        selectStaff.InsertCommandType = SqlDataSourceCommandType.StoredProcedure

        selectStaff.InsertCommand = "AddStaffAvailability"
        Dim iCount As Integer = 0
        Dim dateAndTime As DateTime = Convert.ToDateTime(SearchTimeAvalability_TextBox.Text)
        For Each GVR As GridViewRow In GridView1.Rows
            If CType(GVR.FindControl("StaffSelector"), CheckBox).Checked = True Then
                Dim staffNo As String = GVR.Cells(1).Text
                selectStaff.InsertParameters.Add("StaffNo", staffNo)
                selectStaff.InsertParameters.Add("OrderNo", OrderNo_TextBox.Text)
                selectStaff.InsertParameters.Add("AvailablityDate", dateAndTime)
                selectStaff.InsertParameters.Add("AppointmentType", AppointmentType_DropDownList.SelectedValue)
                selectStaff.InsertParameters.Add("Venue", Venue_TextBox.Text)
                iCount = iCount + 1
            End If
        Next

        Dim rowAffected As Integer = 0

        Try
            rowAffected = selectStaff.Insert()
        Catch ex As Exception
            Label1.Text = "Work More"
        Finally
            selectStaff = Nothing
        End Try

        If rowAffected <> iCount Then
            Label1.Text = "Almost There"
        Else
            Label1.Text = "Success"
        End If

    End Sub

If i execute the stored procedure it works find but when it is inserted from the webpage, it didn't return any error and it still won't insert data into the database. Hope if there are anybody that can point out the problem.

Kind Regard

You are using two SqlDataSource controls, one in the Web Form markup, StaffAvailabilitySqlDataSource, and one in your code, selectStaff. The one in your code has no knowledge of any changes, because that happened in the other SqlDataSource, or rather the GridView to which it was bound. If you want to separate the insert code from the select code in your SqlDataSource control, I suggest you use pure ADO.NET, or add the insert functionality to a single SqlDataSource control.

Sorry but i am still new in this field so i don't really understand of using insert functionality in a single SqlDataSource.

I have try to change all the selectStaff SqlDataSource to StaffAvailabilitySqlDataSource but still cannot insert.

If can, please show me what i should do to solve this problem.

Kind Regards.

Well, the simplest you can do to verify that your markup and code works, is to delete the following lines of code:

Dim selectStaff As New SqlDataSource
selectStaff.ConnectionString = ConfigurationManager.ConnectionStrings("MilanDatabaseConnectionString1").ToString
Dim NewCustomerConnection As String = "Server=KAZESENSEI-PC\SQLEXPRESS;Database=MilanDatabase;Trusted_Connection=True;"

Then you simply replace all instances of selectStaff in the NewCustSubmitForm_Button_Click method with StaffAvailabilitySqlDataSource.

I have change all the instance from selectStaff to StaffAvailabilitySqlDataSource but still no insert.

This is a list of what i have try to to know what data has been insert and can the data be insert into the database

• I have use label to view what the user input value and all are the same with the value that i have use when i execute the stored procedure
• I have put all null in my database table to know which data can be succesfully be send to the database but still no insert
• I try to change all the user input into direct value in coding such as ("StaffNo", "SFN001") except for dateAndTime but still no insert
  

What should i do to solve this problem? And what is ADO.Net?

kaze.sensei:

And what is ADO.Net?

ADO.NET is underlying technology used to facilitate the transport of data to and from the database, see this article, http://msdn.microsoft.com/en-us/library/h43ks021.aspx.

Try changing the code in the NewCustSubmitForm_Button_Click method as follows (replacing the original code):

Protected Sub NewCustSubmitForm_Button_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles NewCustSubmitForm_Button.Click
    Dim selectStaffConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("MilanDatabaseConnectionString1").ToString)
    Dim selectStaffCommand As New SqlCommand("AddStaffAvailability", selectStaffConnection)
    selectStaffCommand.CommandType = SqlDataSourceCommandType.StoredProcedure
    
    Dim iCount As Integer = 0
    Dim dateAndTime As DateTime = Convert.ToDateTime(SearchTimeAvalability_TextBox.Text)

    For Each GVR As GridViewRow In GridView1.Rows
        If CType(GVR.FindControl("StaffSelector"), CheckBox).Checked = True Then
            Dim staffNo As String = GVR.Cells(1).Text
            selectStaffCommand.Parameters.AddWithValue("StaffNo", staffNo)
            selectStaffCommand.Parameters.AddWithValue("OrderNo", OrderNo_TextBox.Text)
            selectStaffCommand.Parameters.AddWithValue("AvailablityDate", dateAndTime)
            selectStaffCommand.Parameters.AddWithValue("AppointmentType", AppointmentType_DropDownList.SelectedValue)
            selectStaffCommand.Parameters.AddWithValue("Venue", Venue_TextBox.Text)

            iCount = iCount + 1
        End If
    Next

    Dim rowAffected As Integer = 0

    Try
        selectStaffConnection.Open()
        rowAffected = selectStaffCommand.ExecuteNonQuery()
    Catch ex As Exception
        Label1.Text = "Work More<br/>" & ex.Message
    Finally
        selectStaffConnection.Close()
    End Try

    If rowAffected <> iCount Then
        Label1.Text = "Almost There"
    Else
        Label1.Text = "Success"
    End If
End Sub

You also need to add these Import statements at the top of the code file:

Imports System.Data
Imports System.Data.SqlClient

Remember, this code compiles here, but I haven't been able to check it, due to the fact that I don't have the database. Let me know what errors you get, if any.

I have try your code but it still cannot insert.

And there are no error.

Just want to show the stored procedure that i used to view the data into the gridview.

SearchStaffAvailability SP

ALTER PROCEDURE SearchStaffAvailability
    @AvailabilityDate datetime,
    @RolesName varchar(80)
AS
    /* SET NOCOUNT ON */
    SET @RolesName = '%' +@RolesName+ '%'
    SELECT StaffProfile.StaffNo, StaffProfile.FirstName,StaffProfile.LastName, StaffProfile.RolesName, StaffAvailability.AvailabilityDate FROM StaffAvailability
    INNER JOIN StaffProfile ON StaffAvailability.StaffNo = StaffProfile.StaffNo
    WHERE StaffProfile.RolesName LIKE @RolesName AND StaffAvailability.AvailabilityDate NOT IN (@AvailabilityDate)
RETURN

Could this be the problem? Or the timedate variable that is used to insert to the database?

Because from what i discover is that .NET webform will send different format of date to SQLdatabase that may prevent the data be inserted to the database. The dateAndTime datatype in my database are DateTime.

Well, the parameters being added to the SqlCommand object doesn't match the names and values used in the stored procedure, and they should. 

Sorry to interupt but i have another question.

Can i build a webform that can be used by two different masterpage?

For example, i have two type of user that will be using this webform, lower and upper. Lower can only add and view but upper can add, edit, view and delete. And some information that will be add by lower and upper will be the same. So lower masterpage for lower and upper masterpage for upper.

Well, you can't use two master pages in one go, i.e. the content page can only work with one master page at a time. However, it is possible switch master pages at run-time, as shown in this thread, http://forums.asp.net/t/1260175.aspx

I understand that by using pre-init, we can identify and change the master page that is selected by the user. But is it possible by using login page.

Is it possible if i create a login page, then it will check for the roles of the user in the database. The roles of the user will identify for which masterpage it will be using in pre-init?

For example, lower will be using "LowerMasterPage" where upper will be using "UpperMasterPage" and the page that is used by them are AddCustomer.aspx.

Kind Regards

Well, you'd still need to change it for each content page as it loads, but if you save the name of the master page chosen in the login page, that should work.

So means that i need to allow the user to choose which master page they will be using in the login page?

Can i just make a login page that contain only Username and Password, then by using coding, it will collect the roles name from the database and each page will identify the roles of the user and make sure that the user will be using the suitable masterpage.

Same example as before, lower will insert its username and password. Then it will redirect lower to "lowerMainPage"+"lowerMasterPage". When lower is using "AddCustomer.aspx", the roles of the user will be identify to make sure that lower will only use "lowerMasterPage".

Kind regards.

Well, if you save the username and password when on the login page, and whatever else you need to retrieve from the database in one or more Session variables, then you can query these variables when loading a content page, to determine which master page you want to use for that particular content page and session.

Sorry but i am still new in this field. About two week ago i start building asp.net in VB.

I know what is session, it will check the session coding everytime the page it is refresh. But i don't know how to pass database variable through session.

I refer my login idea to this website, "http://www.xoc.net/works/tips/forms-authentication.asp". Can it be done by this login method?

Kind Regards