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?
 
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
 
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.