none
Retrieve a single element from a database RRS feed

  • Question

  •  

    I am relatively new to web application programming and am coming along pretty well with all the online videos and such available.  I understand how to query a database, even specific down to a single row and can display all the data using visual studio gridview and datalist structures.  But, given a query which returns a single row of my database, how would I extract a single element and store it as a viariable.  I.E.  in the single row my query returns, there is an integer value as its own column, I wish to extract that single value and use is as a variable in executable code I am writing.   Using c#.  Thanks for any help
    Monday, October 1, 2007 2:59 PM

Answers

  •  

    Thanks very much for the help.  I ended up finding an online tutorial about datasets, datatables and table adaptors.  Added them to my project, found the right querystrings, made the appropriate functions and all is well. 
    Tuesday, October 2, 2007 8:14 PM

All replies

  •  

    Code Block

    string myConnectString = "Data Source=myserver;Initial Catalog=Northwind;Persist Security Info=True;User ID=myuserid;Password=mypassword";

    Int32 iManagerId = 0;

     

    // ==================================

    // Strategy 1

    // This is more or less what you are looking for.

    // Get a value from a single column from a single row.

    // ==================================

    iManagerId = 0;

    using (SqlConnection myConnection = new SqlConnection(myConnectString))

    {

    using (SqlCommand myCommand = new SqlCommand())

    {

    myCommand.Connection = myConnection;

    myCommand.CommandType = CommandType.Text;

    myCommand.CommandText = "SELECT EmployeeId, ReportsTo, LastName FROM Employees WHERE EmployeeId = 1";

    using(DataTable myDataTable = new DataTable())

    {

    myCommand.Connection.Open();

    myDataTable.Load(myCommand.ExecuteReader());

    iManagerId = (Int32)myDataTable.Rows[0]["ReportsTo"];

    }

    }

    }

    // ==================================

     

    // ==================================

    // Strategy 2

    // If you know that you want a scalar return from your query

    // Why not modify the query and use ExecuteScalar()

    // ==================================

    iManagerId = 0;

    using (SqlConnection myConnection = new SqlConnection(myConnectString))

    {

    using (SqlCommand myCommand = new SqlCommand())

    {

    myCommand.Connection = myConnection;

    myCommand.CommandType = CommandType.Text;

    myCommand.CommandText = "SELECT ReportsTo FROM Employees WHERE EmployeeId = 1";

     

    myCommand.Connection.Open();

    iManagerId = (Int32)myCommand.ExecuteScalar();

    }

    }

    // ==================================

     

     

    Monday, October 1, 2007 3:55 PM
  • Thanks much for your input, however, in using MS visual developer express 2005 ... SqlConnection, SqlCommand, and DataTable are not valid objects or types, at least not that I see or intellisense comes back with.  I am using the built in datasources.


    Code Block

            <asp:SqlDataSource ID="subjectDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>">
            </asp:SqlDataSource>
            <asp:SqlDataSource ID="categoryDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>">
            </asp:SqlDataSource>
    <% for (int x = 1; x <= 4; x++)
       {
           int height;
           categoryDataSource.SelectCommand = "SELECT * FROM [Category] WHERE CategoryID=" + x.ToString();
           subjectDataSource.SelectCommand = "SELECT * FROM [Subject] WHERE CategoryLocation=" + x.ToString();
     //above works correcly and returns the correct information from the query, which is only one row from Category and several rows from Subject that all have the same location      
         


             height = //here is where I want to set height to NumSubjects column from the datasource categoryDataSource ... it only has one row returned.     
    %>

     
            <div style="z-index: 100; width: 600px; height: 130px" class="Fblock">
            <img src="data/leftT.jpg" style="z-index: 104; left: 0px; position: absolute; top: 0px" />
            <img src="data/rightT.jpg" style="z-index: 105; left: 478px; position: absolute;top: 0px" />

            <asp:GridView ID="categoryGridView" runat="server" AutoGenerateColumns="False" BorderWidth="0px"
                DataSourceID="categoryDataSource" GridLines="None" ShowHeader="False" Style="z-index: 100;
                left: 40px; position: absolute; top: 30px" CssClass="Fcategory">
                <Columns>
                    <asp:BoundField DataField="Name" SortExpression="Name" />
                </Columns>
            </asp:GridView>

            <asp:GridView ID="subjectGridView" runat="server" AutoGenerateColumns="False" BorderWidth="0px" DataSourceID="subjectDataSource" GridLines="None" ShowHeader="False"
                Style="z-index: 101; left: 80px; position: absolute; top: 50px" CssClass="Fsubject">
                <Columns>
                    <asp:BoundField DataField="Name" SortExpression="Name" />
                </Columns>
            </asp:GridView>


                <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CssClass="Fsubject"
                    DataSourceID="subjectDataSource" Style="z-index: 102; left: 529px; position: absolute;
                    top: 50px" BorderWidth="0px" GridLines="None" ShowHeader="False">
                    <Columns>
                        <asp:BoundField DataField="NumTopics" HeaderText="Number of Topics" />
                    </Columns>
                </asp:GridView>

            </div>
    <%}%>
       



    this works, and correctly creates the layer 4 times and displays the correct information in each of the tables, I just now need to dynamically modify the size of the layer based on the height I am trying to get.
    Monday, October 1, 2007 5:00 PM
  • Code Block

    using System.Data.SqlClient;

     

     

     

    should help
    Monday, October 1, 2007 5:03 PM
  •  

    Thanks very much for the help.  I ended up finding an online tutorial about datasets, datatables and table adaptors.  Added them to my project, found the right querystrings, made the appropriate functions and all is well. 
    Tuesday, October 2, 2007 8:14 PM