Parameter (ALL) in Reporting Service SQL Server 2008

 This link help me to display ALL in my parameter on Reporting Service, but up to now, I confused why id '-1' can select all data. :) :)


http://www.codeproject.com/Articles/9802/Add-quot-Select-All-quot-to-parameter-lists-in-SQL

Turning a Comma Separated string into individual rows

Berikut adalah isi dari tabel T_Transaksi

Perhatikan id_pertanyaan.
Permasalahan nya disini adalah saya ingin mendapatkan data berapa id_pertanyaan "1" pada id_poli "1" dan i_kategori "1" sehingga dapat saya kalkulasikan.

Dan akhirnya saya menemukan ini http://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows


Namun dengan beberapa modifikasi yang sesuaikan dengan kebutuhan akhirnya seperti ini, :D

;WITH TabelTransaksi(id_poli, id_kategori,pertanyaan,id_pertanyaan)
AS
(
    SELECT id_poli, id_kategori, LEFT(id_pertanyaan, CHARINDEX(',',id_pertanyaan+',')-1),
        STUFF(id_pertanyaan, 1, CHARINDEX(',',id_pertanyaan+','), '')
    FROM T_Transaksi   

UNION ALL
    SELECT id_poli, id_kategori, LEFT(id_pertanyaan, CHARINDEX(',',id_pertanyaan+',')-1),
        STUFF(id_pertanyaan, 1, CHARINDEX(',',id_pertanyaan+','),'')
    FROM TabelTransaksi
    WHERE id_pertanyaan > ''
)

SELECT TabelTransaksi.id_poli AS ID_POLI, M_Divisi.nama_poli AS NAMA_POLI, TabelTransaksi.id_kategori AS ID_KATEGORI, T_Kategori.nama_kategori AS NAMA_KATEGORI, pertanyaan AS ID_PERTANYAAN, M_Pertanyaan.nama_pertanyaa as PERTANYAAN, COUNT(pertanyaan) as JUMLAH_RESPONDEN_PER_PERTANYAAN
FROM TabelTransaksi join M_Divisi
on TabelTransaksi.id_poli = M_Divisi.id_poli join T_Kategori
on TabelTransaksi.id_kategori = T_Kategori.id_kategori join M_Pertanyaan
on TabelTransaksi.pertanyaan = M_Pertanyaan.id_pertanyaan
GROUP BY TabelTransaksi.id_poli, TabelTransaksi.id_kategori, pertanyaan, M_Divisi.nama_poli,M_Pertanyaan.nama_pertanyaa,T_Kategori.nama_kategori
ORDER by TabelTransaksi.id_poli

Dan kira-kira hasil nya seperti ini.

To get a field value from tabel using stored procedure in WebForm ASP.NET

Kalau sebelumnya menggunakan Dataset untuk menampung banyak data dari tabel, nah tadi saya bingung karna harus mendapatkan satu data saja.

Akhir nya setelah di searching di google, ketemula cara berikut ini.
public string GetUserIdByUsernamePassword(string username, string password)
        {
            conn = objDBConnector.GetConn();

            cmd = objDBConnector.GetCommand();

            SqlDataReader rdr = null;
           
            string id_user = "";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "usp_M_User#GetUserIdByUsernamePassword";
            cmd.Parameters.AddWithValue("@User_Name", username);
            cmd.Parameters.AddWithValue("@Password", password);
            try
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                        id_user = rdr["User_ID"].ToString();
                }
                return id_user;
            }
            catch (Exception exp)
            {
                throw (exp);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
          
        }

dari code diatas, terlihat bahwa saya hanya mereturn satu string saja. :) :)

Semoga bermanfaat. :D :D

View Data in GridView using StoredProcedure (WebForm ASP.NET)

There is a simple way to view data in gridview.
We manage it by using datasource and then choose your stored procedure.
But this time, I don't use it.
I make my grid view by drag an drop the gridview from toolbox.
This is my.aspx
<asp:GridView ID="gridviewKelas" runat="server" AutoGenerateColumns="False"  Width="100%" CssClass="table table-bordered table-striped" AllowPaging="true" OnRowUpdating="gridviewDataKaryawan_RowUpdating" DataKeyNames="Class_Id" OnRowEditing="gridviewKelas_RowEditing" OnRowCancelingEdit="gridviewKelas_RowCancelingEdit" OnRowDeleting="gridviewKelas_RowDeleting">
               
                <Columns>
                    <asp:TemplateField HeaderText="ID Kelas" ItemStyle-Width="150">
                        <ItemTemplate>
                            <asp:Label ID="lblClass_Id" runat="server" Text='<%# Eval("Class_Id") %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>

                    <asp:TemplateField HeaderText="Nama Kelas" ItemStyle-Width="150">
                        <ItemTemplate>
                            <asp:Label ID="lblClass_Name" runat="server" Text='<%# Eval("Class_Name") %>'></asp:Label>
                        </ItemTemplate>
                        <EditItemTemplate>
                            <asp:TextBox ID="txtClassName" runat="server" Text='<%# Eval("Class_Name") %>'></asp:TextBox>
                            <asp:RequiredFieldValidator ID="RequiredFieldValidatorAlamat" runat="server" ErrorMessage="Nama kelas tidak boleh kosong" ControlToValidate="txtClassName"></asp:RequiredFieldValidator>
                        </EditItemTemplate>
                    </asp:TemplateField>

                     <asp:TemplateField HeaderText="Status Kelas" ItemStyle-Width="150">
                        <ItemTemplate>
                            <asp:Label ID="lblStatus" runat="server" Text='<%# Eval("Status") %>'></asp:Label>
                        </ItemTemplate>
                        <EditItemTemplate>
                            <asp:TextBox ID="txtStatus" runat="server" Text='<%# Eval("Status") %>'></asp:TextBox>
                            <asp:RequiredFieldValidator ID="RequiredFieldValidatorNomorHP" runat="server" ErrorMessage="Status kelas tidak boleh kosong" ControlToValidate="txtStatus"></asp:RequiredFieldValidator>
                        </EditItemTemplate>
                    </asp:TemplateField>
                   
                   
                    <asp:CommandField ButtonType="Link" ShowEditButton="true" ShowDeleteButton="true" ItemStyle-Width="150"/>
                </Columns>
            </asp:GridView>

Then in my.aspx.cs while I reload the page I call the method that declare the stored procedure

BED_MANAGEMET_APP.Models.M_Class kelas = new Models.M_Class();
        protected void Page_Load(object sender, EventArgs e)
        {
           
            if (!IsPostBack)
            {
                BindData();
            }
        }

        private void BindData()
        {
            gridviewKelas.DataSource = kelas.GetDataAll(); << the method that call stored procedure
            gridviewKelas.DataBind();
        }

-------
:D :D

How to get textbox value in WebForm

Here code to get textbox value in Webform, may we want to insert some data to database.
string id_kelas = Server.HtmlEncode(txtIdKelas.Text);

txtIdKelas is the ID of the textbox

Or may you want to update a single row and you want to get the value
string id_kelas = ((TextBox)gridviewKelas.Rows[e.RowIndex].FindControl("txtIdKelas")).Text;

:D

Adding Validation Control in Web Form ASP.NET

Berikut link untuk menambah validasi pada textbox.
Misal ingin menambahkan validasi suatu textbox itu tidak diperbolehkan kosong gunakan RequiredFieldValidator, atau ingin membandingkan isi dua textbox gunakan RequiredFieldValidator, atau ingin menyimpulkan semua error tersebut dalam satu message error tambahkan

ValidationGroup (masih dicoba2).

Klik Disini