Display value in Textbox as per combobox selection

Question

5.00/5 (1 vote)

See more:

I want to dispaly Item Name in Text Box as per the selection of Combo box.I am using following code:

VB

Dim cmd As New SqlCommand()
    Dim con As New SqlConnection
    Dim rd As SqlDataReader
Dim ds As New DataSet()

        ds = GetView(StrQuery) 'GetView function retrive query result from database
        If (ds.Tables(0).Rows.Count > 0) Then
            cmb.DataSource = ds.Tables(0).DefaultView
            cmb.DisplayMember = Mdisp
            cmb.ValueMember = Mvalue
        End If

Private Sub cmbItemName_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbItemName.SelectedIndexChanged

Dim str As String = (" SELECT Item_name FROM Item_Master where Item_no'"=cmbItemName.SelectedValue.ToString()& "'")
        con = obj.SetCon()
        cmd.CommandText = str
        cmd.Connection = con
        rd = cmd.ExecuteReader

        If rd.Read = True Then
            txtItem_code.Text = rd("Item_no")
        End If

        con.Close()

System throws an error at If rd.Read = True Then statement (Error converting data type varchar to numeric.)

I am unable to understand the error.

Please suggest solution.

Thanks in Advance.

Posted 3-Dec-12 6:46am

Yogi ,Pune

Updated 3-Dec-12 6:57am

__TR__

v2

Add a Solution

Comments

__TR__ 3-Dec-12 13:01pm

Looks like the SQL query is not formed properly in your code. Best way to see this would be to debug your code and get the value of the string "str" and check how the query is formed.

6 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

fjdiewornncalwe · Answer 1 · 2012-12-03T10:32:00

Your sql string is messed up. It needs to look more like this, but my second code snippet is how this should be done (as per MSDN[^]). The way you have written it leaves the query extremely vulnerable to sql injection.
" SELECT Item_name FROM Item_Master where Item_no = '" & cmbItemName.SelectedValue.ToString()& "'"

VB

Dim commandText As String = "SELECT Item_name FROM Item_Master where Item_no = '@selectedItemNo'"

Using connection As New SqlConnection(connectionString)
   Dim command As New SqlCommand(commandText, connection)

   ' Add selectedItemNo parameter for WHERE clause.
   command.Parameters.AddWithValue("@selectedItemNo", cmbItemName.SelectedValue.ToString())

   Try
      connection.Open()
      rd = command.ExecuteReader

      If rd.Read = True Then
         txtItem_code.Text = rd("Item_no")
      End If
   Catch ex As Exception
      Console.WriteLine(ex.Message)
   End Try 
End Using

Yogi ,Pune · Answer 2 · 2012-12-04T03:13:00

Thanks.

as per your suggestion I am using following code:

VB

Dim str As String = "SELECT Item_name FROM Item_Master where Item_no = '@selectedItemNo'"
        con = obj.SetCon()
        Dim command As New SqlCommand(str, con)
        command.Parameters.AddWithValue("@selectedItemNo", cmbItemName.SelectedValue.ToString())
Try
            con.Open()
            rd = command.ExecuteReader
If rd.Read = True Then
                txtItem_code.Text = rd("Item_no")
            End If
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try
        con.Close()

I debugging the code after reading rd = command.ExecuteReader statement system directly go to catch statement & show message "The connection was not closed. The connection's current state is open.".
I am unable to find out a problem.

Please help

Yogi ,Pune · Answer 3 · 2012-12-04T03:29:00

Solution 3

Now problem mentioned in above is solved but main problem is still there i.e
System throws an error at If rd.Read = True Then statement (Error converting data type varchar to numeric.)

Posted 4-Dec-12 3:29am

Yogi ,Pune

Comments

Yogi ,Pune 5-Dec-12 10:32am

Please suggest solution for my question

DinoRondelly 5-Dec-12 10:45am

txtItem_code.Text = rd("Item_no").ToString()

Yogi ,Pune 5-Dec-12 11:00am

on rd = command.ExecuteReader statement system directly go to catch statement & show message "The connection was not closed. The connection's current state is open.".
above mentioned issue still exist.

DinoRondelly 5-Dec-12 11:08am

So you are still getting the error - If rd.Read = True Then statement (Error converting data type varchar to numeric.) and you are having an issue with your connection?

Yogi ,Pune 6-Dec-12 8:10am

Yes issue is still there.

Please suggest me a solution

DinoRondelly 6-Dec-12 11:51am

is Item_no a int value in your table?

Yogi ,Pune 7-Dec-12 10:12am

Yes. Item_no is a integrer

DinoRondelly 7-Dec-12 10:19am

trying taking off the ToString() on cmbItemName.SelectedValue.ToString()

Yogi ,Pune 7-Dec-12 10:36am

As per your suggestion when I removed .To string()system throws following error after "rd = command.ExecuteReader" statement.
No mapping exists from object type System.Data.DataRowView to a known managed provider native type.

DinoRondelly 7-Dec-12 10:50am

Try changing If rd.Read = True Then to if rd.HasRows then
if rd.HasRows then
while rd.Read
txtItem_code.Text = rd("Item_no").ToString
End While
End If

Yogi ,Pune 7-Dec-12 12:15pm

I made changes in my code as per your suggestion & debug the code I found that after command.Parameters.AddWithValue("@selectedItemNo", cmbItemName.SelectedValue.ToString()) this statement in my code cursor go to rd = command.ExecuteReader &
If rd.Read = True Then statement after that cursor directly go to Catch statement & shows same error again even "If rd.Read = True Then" at this statement code return True value i.e True=True.

Is there is any thing missing in my code.

DinoRondelly 7-Dec-12 12:34pm

Ok well you dont need the rd.Read = true you can just use if rd.Read then

i dont understand why if Item_no is an int value why you are trying to pull it out with a string cmbItemName.SelectedValue.ToString()

drop the ToString on cmbItemName.

Then check to see if your query is returning anything with rd.hasrows then drop in

'Take off the single quotes around @selectedItemNo
Dim str As String = "SELECT Item_name FROM Item_Master where Item_no = @selectedItemNo"

'Get rid of the .ToString
command.Parameters.AddWithValue("@selectedItemNo", cmbItemName.SelectedValue)

'Check to see if your reader has any rows
if rd.HasRows then
'While there are rows (Should only be one)
while rd.Read
'Add your reader value
txtItem_code.Text = rd("Item_no").ToString
End While
End If

Change your code to that and let me know if you need anymore help.

Yogi ,Pune 11-Dec-12 8:09am

As per your suggestion I made changes,remove single quotes & drop To string from cmbItemName.Selected value.Now system showing following error.
No mapping exists from object type System.Data.DataRowView to a known managed provider native type.
at the time of debugging the code I noticed one thing, after command.Parameters.AddWithValue("@selectedItemNo", cmbItemName.SelectedValue) statement
cursor go to "Try" statement & then "rd = command.ExecuteReader" statement.
which is correct but when cursor go to "rd = command.ExecuteReader" statement system also highlighting catch block.i.e something is wrong in between
command.Parameters.AddWithValue("@selectedItemNo", cmbItemName.SelectedValue)
rd = command.ExecuteReader this two statement

AnkitGoel.com · Answer 4 · 2012-12-06T19:36:00

Solution 6

Please note-

You are returning 'Item_name' from your select query while trying to access 'Item_no' in line 'txtItem_code.Text = rd("Item_no")'. Please verify there is some mistake in your code.

Please, Mark as answer if helepd.

Posted 6-Dec-12 19:36pm

AnkitGoel.com

Yogi ,Pune · Answer 5 · 2012-12-07T04:32:00

Thanks for your suggestion.
I recheck my code as per your suggestion.But unable to find the problem.I used following table.Can you suggest me at which point
my code is wrong.

SQL

Using connection As New SqlConnection("Data Source=RAKESH\SQLEXPRESS;Initial Catalog=PO;Integrated Security =True")
            Dim str As String = "SELECT Item_name FROM Item_Master where Item_no = '@selectedItemNo'"

VB

Dim command As New SqlCommand(str, connection)
            connection.Open()
            command.Parameters.AddWithValue("@selectedItemNo", cmbItemName.SelectedValue.ToString())

VB

Try
rd = command.ExecuteReader

VB

If rd.Read = True Then
                    txtItem_code.Text = rd("Item_no")
                End If
            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try
           con.Close()
        End Using

Yogi ,Pune · Answer 6 · 2012-12-05T05:03:00

Solution 5

after rd = command.ExecuteReader statement system directly go to catch statement & show message "The connection was not closed. The connection's current state is open.".

another error occured is converting data type varchar to numeric after same statement

Posted 5-Dec-12 5:03am

Yogi ,Pune

Display value in Textbox as per combobox selection

6 solutions

Solution 1

Solution 2

Solution 3

Solution 6

Solution 7

Solution 5

Add your solution here

Preview 0