asked on

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>

Ravi Vaddadi

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

bearsgone

ASKER

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

Ravi Vaddadi

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

bearsgone

ASKER

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

Ravi Vaddadi

Yep. I am sorry. Please replace dt with dtDataSource

bearsgone

ASKER

How do I go about:
I am trying to get the value into TB4 instead of datagrid
Thank you

Member_2_4913559

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()

SOLUTION

Ravi Vaddadi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bearsgone

ASKER

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

Member_2_4913559

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.

Member_2_4913559

Oh wait, I read your post again, that's not right, gimme a min...

ASKER CERTIFIED SOLUTION

Member_2_4913559

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial