26.1.12

Read data via SQLdatasource

Hi Everybody,

I would like to explain one of faq within this essay , how we can read data via SQLDatasource without open or close connection from MSSQL Server .

If any question, please feel free to contact me y_turkeli@yahoo.com or 
yenerturkeli@v-basic.net.

1-) We are creating a new ASP.NET Empty Web Site 


2-) Add New Web Form to project


3-) We are designing this windows form in a similar way to the picture below


4-) We are writing this code

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <style type="text/css">
        .style1
        {
            width: 100%;
        }
        .style2
        {
            width: 304px;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
        <table class="style1">
            <tr>
                <td class="style2">
                    Personnel Number</td>
                <td>
                    <asp:TextBox ID="pno_txt" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td class="style2">
                    Full Name</td>
                <td>
                    <asp:Label ID="fullname_lbl" runat="server"></asp:Label>
                </td>
            </tr>
            <tr>
                <td class="style2">
                    Gender</td>
                <td>
                    <asp:Label ID="gender_lbl" runat="server"></asp:Label>
                </td>
            </tr>
            <tr>
                <td class="style2">
                    Age</td>
                <td>
                    <asp:Label ID="age_lbl" runat="server"></asp:Label>
                </td>
            </tr>
            <tr>
                <td class="style2">
                    E-mail</td>
                <td>
                    <asp:Label ID="email_lbl" runat="server"></asp:Label>
                </td>
            </tr>
            <tr>
                <td class="style2">
                    Postcode</td>
                <td>
                    <asp:Label ID="postcode_lbl" runat="server"></asp:Label>
                </td>
            </tr>
            <tr>
                <td class="style2">
                    &nbsp;</td>
                <td>
                    <asp:Button ID="show_btn" runat="server" Text="Show Data" />
                </td>
            </tr>
        </table>
    <asp:SqlDataSource
          id="SqlDataSource1"
          runat="server"
          ConnectionString="<%$ ConnectionStrings:conn_workers %>"
            
            SelectCommand="SELECT [fullname], [age], [gender], [email], [city], [personel_id] , [postcode] FROM [workers] WHERE ([personel_id] = @pid)">
        <SelectParameters>
            <asp:ControlParameter ControlID="pno_txt" Name="pid" PropertyName="Text" 
                Type="String" />
        </SelectParameters>
      </asp:SqlDataSource>

    </div>
    </form>
</body>
</html>




Imports System.Data.Sql
Imports System.Data
Partial Class _Default


    Inherits System.Web.UI.Page




    Protected Sub show_btn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles show_btn.Click


        Dim my_view As DataView = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), DataView)
        For Each my_row As DataRowView In my_view


            fullname_lbl.Text = my_row("fullname").ToString()
            age_lbl.Text = my_row("age").ToString()
            gender_lbl.Text = my_row("gender").ToString()
            email_lbl.Text = my_row("email").ToString()
            postcode_lbl.Text = my_row("postcode").ToString()


        Next
    End Sub
End Class




5-) Lets run the application 


Have a great day 

13.1.12

Fill Dataset via SQL Server View

Hi Everybody, 

I would like to explain one of faq within this essay , how we can fill dataset with MSSQL Server View. 
If any question, please feel free to contact me y_turkeli@yahoo.com or yenerturkeli@v-basic.net.

1-) We are creating a new Windows Application Project 



2-) We are going to use this sample MSSQL Server view



3-) We are designing this windows form in a similar way to the picture below


4-) We are writing this code

Imports System.Data.SqlClient
Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim my_dbConnection As New SqlConnection
        Dim my_Command As New SqlCommand

        Dim my_adapter As New SqlDataAdapter(my_Command)

        Dim my_dataset As New DataSet("Customers")

        my_dbConnection.ConnectionString = "Data Source=csydneydb;Persist Security Info=True;User ID=cussydney;Password=password123"
        my_Command.CommandText = "select id as ID,fullname As [Full Name],cardnumber as [Card Number ],money as Debt ,email as Email from my_view "

        my_Command.Connection = my_dbConnection

        my_adapter.Fill(my_dataset)

        DataGridView1.DataSource = my_dataset.Tables(0)


    End Sub
End Class

4-) Lets run the application



Have a great day

Yener Turkeli


Working with SQL Server View

Hi Everybody,

I would like to explain one of faq within this essay , how we can use MSSQL Server View to establish a relationship and working with data? If any question, please feel free to contact me y_turkeli@yahoo.com or yenerturkeli@v-basic.net.


1-) We are creating a new Windows Application Project





2-) Create a new view






3-) Add SQL Server Tables to establish a relationship between tables






4-) Save new view (my_view)






6-) Choose your view from database object screen





7-) Drag fields into the form







8-) Lets run application









Have a great day


Yener Turkeli
yenerturkeli@v-basic.net