copy sqldatareader value into a text box C#

I have this small form that calls a store procedure. I am trying to get a result of  Sub Submit2 to be shown in TB4 test box, so that it can be modified and stores back into database. Problem is that sqldatareader can not be converted into textbox field

<%@ Import Namespace="System.Web.Security" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System" %>

 <Script Runat="Server">

     Sub Submit(ByVal sender As Object, ByVal e As EventArgs)
     
         Dim TechCon As SqlConnection
         Dim cmdSelect As SqlCommand
         TechCon = New SqlConnection("works correctly")
         cmdSelect = New SqlCommand("Tech", TechCon)
         cmdSelect.CommandType = CommandType.StoredProcedure
         cmdSelect.Parameters.AddWithValue("@TeamID", TB1.Text)
         TechCon.Open()
         Jobs.DataSource = cmdSelect.ExecuteReader()
         Jobs.DataBind()
         
         
         TechCon.Close()

     End Sub

     Sub Submit2(ByVal sender As Object, ByVal e As EventArgs)
     
         
         Dim TechCon As SqlConnection
         Dim cmdSelect As SqlCommand
         TechCon = New SqlConnection("works correctly")
         cmdSelect = New SqlCommand("ViewforUpdate", TechCon)
         cmdSelect.CommandType = CommandType.StoredProcedure
         cmdSelect.Parameters.AddWithValue("@TeamID", TB2.Text)
         cmdSelect.Parameters.AddWithValue("@JobID", TB3.Text)
         TechCon.Open()
         'Jobs.DataSource = cmdSelect.ExecuteReader()
         'Jobs.DataBind()
         TB4 = cmdSelect.ExecuteReader()
         TB4 = DataBinder()
         TechCon.Close()

     End Sub
     
</Script>

<html>

<head><title>Tech Jobs</title></head>
<body>
<form runat="server">
<asp:Label ID="LB" runat="server" Text="Tech ID"></asp:Label>
<asp:TextBox ID="TB1" runat="server"></asp:TextBox><br /><br />
<asp:Button OnClick="Submit" Text="View Jobs" runat="server" />
<br />
<br />
<asp:Label ID="LB2" runat="server" Text="Tech ID:"></asp:Label>
<asp:TextBox ID="TB2" runat="server"></asp:TextBox><br /><br />
<asp:Label ID="LB3" runat="server" Text="Enter JobID:"></asp:Label>
<asp:TextBox ID="TB3" runat="server"></asp:TextBox><br /><br />
<asp:Label ID="LB4" runat="server" Text="Notes:"></asp:Label>
<asp:TextBox ID="TB4" runat="server"></asp:TextBox><br /><br />
<asp:Button ID="Button2" OnClick="Submit2" Text="Add/Modify Notes" runat="server" />

</form>

<asp:DataGrid
ID="Jobs"
Runat="Server" />
</body>

</html>
bearsgoneAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SriVaddadiCommented:
You cannot bind with SQLDataReader. If you want specify the datasource property then use SqlDataAdapter and DataTable combination.

you can get the value from SqlDataReader by Calling SqlDataReader.GetString(<index of column>)

check this example

http://dotnetperls.com/sqldataadapter-adonet
0
bearsgoneAuthor Commented:
I am still not sure how to accomplish it in my case. Can I get an idea on how it will work in my case?

Thank you
0
SriVaddadiCommented:
try this
Sub Submit2(ByVal sender As Object, ByVal e As EventArgs)
     
         
         Dim TechCon As SqlConnection
         Dim cmdSelect As SqlCommand
                  Dim dtDataSource as DataTable
         Dim da as SqlDataAdapter

         TechCon = New SqlConnection("works correctly")
         cmdSelect = New SqlCommand("ViewforUpdate", TechCon)
         TechCon.Open()
         cmdSelect.Connection = TechCon
         cmdSelect.CommandType = CommandType.StoredProcedure
         cmdSelect.Parameters.AddWithValue("@TeamID", TB2.Text)
         cmdSelect.Parameters.AddWithValue("@JobID", TB3.Text)
         dt = New DataTable
         da.Fill(dt)
        Jobs.DataSource = dt
        Jobs.DataBind()
         TechCon.Close()

     End Sub

 
0
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

bearsgoneAuthor Commented:
Hello what is dt? is it the same as dtDataSource?

I am trying to get the value into TB4 instead of datagrid
I get run time error that da is used before it has been assigned a  value

Thank you
0
SriVaddadiCommented:
Yep. I  am sorry. Please replace dt with dtDataSource
0
bearsgoneAuthor Commented:
How do I go about:
I am trying to get the value into TB4 instead of datagrid
Thank you
0
ddayx10Commented:
From what you've posted I would change things around a little. Since you are trying to fill a single textbox with a value read from sql then a simple datareader is in order. There is no need for elaborate dataadapters or binding (which will only waste resources).

The trick here is that you are using a StoredProc and this StoredProc depending on how its written and your data is going to return 1 row, 5 rows, 5000 rows; same with columns? I have no way to know since I cant see the underlying SQL.

So you fill the sqldatareader with the return data from the StoredProc. Now you run the datareader through an iteration process to read each row returned. You use the syntax sqldatareader("column_name") OR sqldatareader(indexOfRow) to get the value you want to assign to the text box.

If your data returns one row, then this will be the value put in TB4, but if your StoredProc returns 5 rows then the last row's value will be assigned to the textbox. So mold your data to fit the result you want. Hope that makes sense.

        Dim TechCon As SqlConnection
        Dim cmdSelect As SqlCommand
        TechCon = New SqlConnection("works correctly")
        cmdSelect = New SqlCommand("ViewforUpdate", TechCon)
        cmdSelect.CommandType = CommandType.StoredProcedure
        cmdSelect.Parameters.AddWithValue("@TeamID", TB2.Text)
        cmdSelect.Parameters.AddWithValue("@JobID", TB3.Text)
        TechCon.Open()
        Dim dr As SqlDataReader = cmdSelect.ExecuteReader()
        While dr.Read()
            TB4.Text = dr("NAME_OF_COLUMN_IN_TABLE")
        End While
        dr.Close()
        TechCon.Close()

0
SriVaddadiCommented:
This is how you can bind value into text box assuming the data source has a single row.

<asp:TextBox ID="TB4" runat="server" Text='<%# dtDataSource.DataRow["Your Column Name"] %>'></asp:TextBox>

Depending on your other requirements along with binding requirements, you can either use datareader or dataadapter to fetch data.

If you have a single column and single row you don't need a SqlDataReader. You can use ExecuteScalar which returns First row and First column data in the form of an object.








0
bearsgoneAuthor Commented:
Re: ddayx10:

That worked. However when I have nothing in that column I get an exception error. How can I handle null value in the column? It should not show anything if it is null.

I do get one column/one field response from my store procedure

Thank you
0
ddayx10Commented:
I think I might just do this:

        While dr.Read()
            If Not String.IsNullOrEmpty(dr("NAME_OF_COLUMN_IN_TABLE")) Then
            TB4.Text = dr("NAME_OF_COLUMN_IN_TABLE")
            End If
        End While

There are other ways to check this, but I think one is as good as another.
0
ddayx10Commented:
Oh wait, I read your post again, that's not right, gimme a min...
0
ddayx10Commented:
This should do it:

        While dr.Read()
            If dr("NAME_OF_COLUMN_IN_TABLE") IsNot DBNull.Value Then
                TB4.Text = dr("NAME_OF_COLUMN_IN_TABLE")
            End If
        End While
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.