Retrieve data from SQLDataSource

[jtok]

Background:
I am using Visual Studio 2005 Standard SP1 to create an ASP.NET website that accesses an SQL 2005 database. I am using vb.net as well.
I am passing an ID in a query string from one page to the next, where I retrieve it using an SQLDataSource. The data will only be one row; it is returning customer information.

Problem:
I need to be able to get specific fields from the SQLDataSource and populate some textboxes. Honestly, I'm not really sure where to begin. For the sake of argument, let's say that I am working with this:
'My Textbox
Dim LastName As Textbox

'My SQLDataSource (filtered by query string using the customer's ID to only get 1 customer's information at a time)
SQLData

Question:
So, how would I go about retrieving the lastname field from SQLData and inserting it into the textbox LastName?

Thanks,
J'Tok

[SheSaidImaPregy]

Here's some info on commands:

cmdSelect.ExecuteNonQuery()
-- This executes queries that don't return rows, like deletes, updates, inserts, etc.
-- Returns a number of records updated, if you set it to a variable:
-- Dim intRecsUpdated As Integer = cmdSelect.ExecuteNonQuery()

cmdSelect.ExecuteScalar()
-- Retrieves only one record/column/entry, and always the first one it reaches.
-- Good for retrieving only one value.
-- Dim strLastName As String = cmdSelect.ExecuteScalar()

cmdSelect.ExecuteReader()
-- Used to read as many values as you wish. This is what you will need to use.
-- Need to declare a reader as SqlDataReader.
-- Use "while readername.Read()" for more than 1 row returned.

For your operation, since you are retrieving more than one value (a full row), you will need a datareader.

You first declare your datasource, which you already have. You do not need to declare a textbox if it is already built on the page, so if it is on your page already (and not nested within another control), then you do not need to declare it. Here is the process of a datareader in VB.NET:

<script language="vb" runat="server">
'Choose a sub, Page_Load is more known.

Sub Page_Load(ByVal S As Object, ByVal E As EventArgs)
  Dim SQLData As New SqlConnection( connectionstringhere )
  Dim cmdSelect As New SqlCommand( "SELECT TOP 1 * FROM Customers WHERE CustomerID=@CustomerID", SQLData )
  cmdSelect.Parameters.AddWithValue( "@CustomerID", putcustomeridhere )
  SQLData.Open()
  Dim dtrReader As SqlDataReader = cmdSelect.ExecuteReader()
  
  if dtrReader.HasRows then
    'if you retrieve more than one row, you need the next line. If only one row, you can leave it out.
    'I will comment it out because you are returning only one row, especially with "TOP 1"
    'while dtrReader.Read()
      tbLastName.Text = dtrReader("LastName")
    'end while
  else
    response.write("No customer found for the supplied ID.")
  end if
  
  dtrReader.Close()
  SQLData.Close()
End Sub
</script>

[jtok]

Thanks for the help, but I am still having a little trouble. I'm not quite sure how to get the customer ID from the query string into the cmdSelect.Parameters. Based on your suggestion, I've done this:

Dim FirstName As TextBox = AddCustomer.ContentTemplateContainer.FindControl("txtFirstName")
        Dim LastName As TextBox = AddCustomer.ContentTemplateContainer.FindControl("txtLastName")
        Dim SQLData As New System.Data.SqlClient.SqlConnection("my connection string - this works")
        Dim cmdSelect As New System.Data.SqlClient.SqlCommand("SELECT TOP 1 * FROM Customer WHERE Customer.ID=@CustomerID", SQLData)
        cmdSelect.Parameters.AddWithValue("@CustomerID", putcustomeridhere)       
        SQLData.Open()
        Dim dtrReader As System.Data.SqlClient.SqlDataReader = cmdSelect.ExecuteReader()
        If dtrReader.HasRows Then
            FirstName.Text = dtrReader("FirstName")
            LastName.Text = dtrReader("LastName")
        Else
            Response.Write("No customer found for the supplied ID.")
        End If
        
        dtrReader.Close()
        SQLData.Close()

- J'Tok

[SheSaidImaPregy]

This depends on how you are getting the customer id anyway. If it is done through a login, then you can grab his info from there. What values do you have about the customer? When are you using this sub?

If a customer is using the sub/function, then when the customer logs in, put his customerid in a session variable. Then set the session to a different variable on the page, like "Dim intCustomerID As Long = Session("CustomerID")" and then reference in "intCustomerID" where "putcustomeridhere" is.

If you are grabbing his id, you should have a way of collecting some information about him. Then you can do a subquery like this:

"SELECT TOP 1 * FROM Customer WHERE CustomerID=(SELECT TOP 1 CustomerID FROM Customer WHERE LastName=@LastName)"

Or if this is just an way to practice, you can do this which pulls a random record:

"SELECT TOP 1 * FROM Customer ORDER BY NewID()"

[jtok]

Sorry, I think I've been using the wrong term. I get the customer ID from a DataURLFormatString passed from another page.

Page.aspx?ID={0}

I'm not sure how to retrieve that ID.

[SheSaidImaPregy]

that's a querystring, replace "putcustomeridhere" with:

Trim(Request.QueryString("ID"))

Oh and make sure you change the "Customer.ID" in your SQL Query, cause that will cause an error.

[jtok]

I would like to start by saying thank you for all of the help. (can you feel the catch coming?)
When I execute the code, it says:
"InvalidOperationException was unhandled by user code"
"Invalid attempt to read when no data is present"
It does this in relation to - FirstName.Text = dtrReader("FirstName")

Here's what I have now:

Dim FirstName As TextBox = AddCustomer.ContentTemplateContainer.FindControl("txtFirstName")
        Dim LastName As TextBox = AddCustomer.ContentTemplateContainer.FindControl("txtLastName")
        Dim SQLData As New System.Data.SqlClient.SqlConnection("my working connection string")
        Dim cmdSelect As New System.Data.SqlClient.SqlCommand("SELECT TOP 1 * FROM Customer WHERE ID=@CustomerID", SQLData)
        cmdSelect.Parameters.AddWithValue("@CustomerID", Trim(Request.QueryString("ID")))       
        SQLData.Open()
        Dim dtrReader As System.Data.SqlClient.SqlDataReader = cmdSelect.ExecuteReader()
        If dtrReader.HasRows Then
            FirstName.Text = dtrReader("FirstName")
            LastName.Text = dtrReader("LastName")
        Else
            Response.Write("No customer found for the supplied ID.")
        End If
        
        dtrReader.Close()
        SQLData.Close()

Thanks again,
J'Tok

[jtok]

Okay, I figured it out. I had to add the line you suggested before.
While dtrReader.Read()
and it started working!

- J'Tok

[puseletso.michelle]

protected void btndelete_Click(object sender, EventArgs e)
        {
            DataSet dataset = new DataSet();
            SqlConnection myConnection = new SqlConnection(@"user id=sa;password=Computer1;server=(local)\sql2008;database=MyLoginDatabase; ");
            myConnection.Open();
            SqlCommand objcommand = new SqlCommand("SELECT * FROM [MyLoginDatabase].[dbo].[Table_1UserLogin] WHERE Username ='" + txtUsername.Text + "' AND Password ='" + txtPassword.Text + "'", myConnection);
            SqlDataReader objdatareader = objcommand.ExecuteReader();
            objdatareader.Read();

            if (txtUsername.ToString() == objdatareader["Username"].ToString())

                if (txtPassword.ToString() == objdatareader["Password"].ToString())

                    objcommand = new SqlCommand("DELETE FROM dbo.Table_1UserLogin WHERE (Username = '" + txtUsername + "') AND (Password = '" + txtPassword +  "')", myConnection);
                    objcommand.ExecuteNonQuery();

            Console.WriteLine("Deleted successful!");

            myConnection.Close();

        }

pls help am gettin an error that say"invalid attempt to read when no data is present"am VS 2010 .net framework 4 pls not am still a learner!

[G_Waddell]

This thread is five years old....

Anyway you should check the reader returns something first:

if (objdatareader.HasRows)
    objdatareader.Read();