[RESOLVED] Bind data record to a text box.

[Troy Mac]

Ok what is wrong with this code? I am trying to bind a record to a textbox using a Dropdown selected index change. I get the error, any help would be greatly appreciated. if there is a better way to do this I am open to anything at this point as I've spent considerable time on google and other forums looking for a solution. Thanks in advance!

Conversion failed when converting the nvarchar value 'DrFirstName DrLastName,Ph.D.' to data type int. is it because I concatenate the name and prefix of the DR into the Dropdown list like this,

SELECT ID,EmailAddress, DrName + ',' + PreFix AS DrName FROM DrNames

Here is the dropdownlist code that I want to populate the textbox with the emailaddress in the table associated with Dr name and ID.

Private Sub DropDownList1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles DropDownList1.SelectedIndexChanged
        Dim dt As New DataTable()

        If DropDownList1.SelectedItem.Text = "OTHER,..." Then
            txtRx1.Visible = True
            DropDownList1.Visible = False
        End If
        Dim connString As String = ConfigurationManager.ConnectionStrings("TCONNConnectionString").ConnectionString
        Dim con As New SqlConnection(connString)
        Dim cmd As New SqlCommand

       Dim strQuery As String = "SELECT ID, EmailAddress, DrName from DrNames WHERE DrName = @DrName"
        cmd.Parameters.AddWithValue("@DrName", DropDownList1.SelectedItem.Value)

        cmd.CommandType = CommandType.Text
        cmd.CommandText = strQuery
        MsgBox(strQuery)
        cmd.Connection = con
        Try
            con.Open()
            Dim sdr As SqlDataReader = cmd.ExecuteReader()
            While sdr.Read()
                txtEmailAddress.Text = sdr("EmailAddress").ToString()
            End While
        Catch ex As Exception
            Throw ex
        Finally
            con.Close()
            con.Dispose()
        End Try

    End Sub

[Troy Mac]

I found out that indeed the SQL query to fill the Dropdown from the SQLAdapter is the culprit

SELECT ID,EmailAddress, DrName + ',' + PreFix AS DrName FROM DrNames

gives me this FristName LastName,PHD so it loads the dropdown with the Dr's fist name and last name and their title, what I need to do is remove everything after the ,(comma) basically removing the PHD or MD or Psy... what ever their title is. I have been messing with SPlit Right etc... but I just cannot figure out where to put it. the scope of the job is that I need to have the Dr's Title in the dropdown so how do I remove everything after the , using the code above

[jmcilhinney]

Um, if you're trying to convert that data to type 'int' as the error message says then how is removing the title from the end going to help? The rest of the name is not an int either so it's still going to fail.

[Troy Mac]

Thanks for the reply jmcilhinney, I should have explained myself better, I am no longer getting the convert to int error. Now I get nothing it skips right over the While sdr.Read() I noticed if I change my SQLAdapter Query that loads the Dropdown to SELECT * FROM DrNames the binding of the textbox works and the correct Email Address get populated into the the textbox but when I put that query back to what the scope of the job calls for SELECT ID,EmailAddress, DrName + ',' + PreFix AS DrName FROM DrNames I get nothing and it skips over the While sdr.Read() so my thoughts are that the , prefix in the name (text of dropdown) is messing things up.

Even if I change my srtQuery to SELECT ID,EmailAddress, DrName + ',' + PreFix AS DrName FROM DrNames same as the query that loads the drop down I get nothing it skips over the While loop

[jmcilhinney]

Why are you trying to use the name anyway? The name should be for display purposes only. It's the ID that you should be using to identify the record.

[Troy Mac]

I agree but when I use the ID that is when I get the cannot convert to int error. the query is clearly grabbing the ID and DrName and Email, not sure but I think the @ID trys to compare to the name displayed in the drop down. I have tried this

 Dim strQuery As String = "SELECT ID, EmailAddress, DrName from DrNames WHERE ID= @ID"
        cmd.Parameters.AddWithValue("@ID", DropDownList1.SelectedItem.Value)

but I think because the selected value is a name and not the (int)ID it fails.

[wes4dbt]

but I think because the selected value is a name and not the (int)ID it fails

Would just take a minute to test what it is.

Debug.Print DropDownList1.SelectedItem.Value.Tostring

[Troy Mac]

Yeah it is definitely the name and not the ID even when I tried this

Dim strQuery As String = "SELECT ID FROM DrNames WHERE ID = @ID"
        cmd.Parameters.AddWithValue("@ID", DropDownList1.SelectedItem.Value)
        MsgBox(DropDownList1.SelectedItem.Value.ToString)

Not sure how I can get the ID from the drop down, as I said prior the SQLAdapter Query is grabbing all of it and it works fine when I don't concatenate the fields in the database arrggg....

I am beginning to wonder if this does anything,

Dim strQuery As String = "SELECT ID FROM DrNames WHERE ID = @ID"

[jmcilhinney]

I agree but when I use the ID that is when I get the cannot convert to int error. the query is clearly grabbing the ID and DrName and Email, not sure but I think the @ID trys to compare to the name displayed in the drop down. I have tried this

 Dim strQuery As String = "SELECT ID, EmailAddress, DrName from DrNames WHERE ID= @ID"
        cmd.Parameters.AddWithValue("@ID", DropDownList1.SelectedItem.Value)

but I think because the selected value is a name and not the (int)ID it fails.

How are you binding the DropDownList? Are you specifying "ID" as the DataValueField?

[Troy Mac]

ok I figured it out some of it, damn I thought of this hours ago but never tested it, I change the DataValueField to ID from DrName

<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource1" DataTextField="DrName" DataValueField="ID" Width="309px" AppendDataBoundItems="true" AutoPostBack="true" TabIndex="6"> <asp:ListItem>  </asp:ListItem>
</asp:DropDownList>

I am now getting the ID but it still will not populate the TextBox.

UPDATED CODE:

 Private Sub DropDownList1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles DropDownList1.SelectedIndexChanged
        'Dim dt As New DataTable()

        If DropDownList1.SelectedItem.Text = "OTHER,..." Then
            txtRx1.Visible = True
            DropDownList1.Visible = False
        End If
        Dim connString As String = ConfigurationManager.ConnectionStrings("TCONNConnectionString").ConnectionString
        Dim con As New SqlConnection(connString)
        Dim cmd As New SqlCommand

        Dim strQuery As String = "SELECT ID, EmailAddress, DrName FROM DrNames WHERE DrName = @DrName"
        cmd.Parameters.AddWithValue("@DrName", DropDownList1.SelectedItem.Value)
        MsgBox(DropDownList1.SelectedItem.Value.ToString)
        cmd.CommandType = CommandType.Text
        cmd.CommandText = strQuery
        cmd.Connection = con

        Try
            con.Open()
            Dim sdr As SqlDataReader = cmd.ExecuteReader()
            While sdr.Read()
                ' MsgBox(sdr("DrName")).ToString()
                txtEmailAddress.Text = sdr("EmailAddress").ToString()

            End While
        Catch ex As Exception
            Throw ex
        Finally
            con.Close()
            con.Dispose()
        End Try

    End Sub

[Troy Mac]

Thanks for the help guys, thanks jmcilhinney I just figured that out then read your post.

 Private Sub DropDownList1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles DropDownList1.SelectedIndexChanged
        'Dim dt As New DataTable()

        If DropDownList1.SelectedItem.Text = "OTHER,..." Then
            txtRx1.Visible = True
            DropDownList1.Visible = False
        End If
        Dim connString As String = ConfigurationManager.ConnectionStrings("TCONNConnectionString").ConnectionString
        Dim con As New SqlConnection(connString)
        Dim cmd As New SqlCommand

        Dim strQuery As String = "SELECT ID, DrName, EmailAddress from DrNames WHERE ID = @ID"
        cmd.Parameters.AddWithValue("@ID", DropDownList1.SelectedItem.Value)
        ' MsgBox(DropDownList1.SelectedItem.Value.ToString)
        cmd.CommandType = CommandType.Text
        cmd.CommandText = strQuery
        cmd.Connection = con

        Try
            con.Open()
            Dim sdr As SqlDataReader = cmd.ExecuteReader()
            While sdr.Read()
                ' MsgBox(sdr("DrName")).ToString()
                txtEmailAddress.Text = sdr("EmailAddress").ToString()

            End While
        Catch ex As Exception
            Throw ex
        Finally
            con.Close()
            con.Dispose()
        End Try

    End Sub