Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB.NET
I want to dispaly Item Name in Text Box as per the selection of Combo box.I am using following code:
 
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
Edited 3-Dec-12 6:57am
__TR__28.9K
v2
Comments
__TR__ at 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.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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()& "'"
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 
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Thanks.
 
as per your suggestion I am using following code:
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
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

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.)
  Permalink  
Comments
Yogi ,Pune at 5-Dec-12 10:32am
   
Please suggest solution for my question
DinoRondelly at 5-Dec-12 10:45am
   
txtItem_code.Text = rd("Item_no").ToString()
Yogi ,Pune at 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 at 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 at 6-Dec-12 8:10am
   
Yes issue is still there.
 
Please suggest me a solution
DinoRondelly at 6-Dec-12 11:51am
   
is Item_no a int value in your table?
Yogi ,Pune at 7-Dec-12 10:12am
   
Yes. Item_no is a integrer
DinoRondelly at 7-Dec-12 10:19am
   
trying taking off the ToString() on cmbItemName.SelectedValue.ToString()
Yogi ,Pune at 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 at 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 at 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 at 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 at 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
Rate this: bad
good
Please Sign up or sign in to vote.

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.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 7

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.
 
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'"
 
Dim command As New SqlCommand(str, connection)
            connection.Open()
            command.Parameters.AddWithValue("@selectedItemNo", cmbItemName.SelectedValue.ToString())
Try
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()
        End Using
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

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
  Permalink  

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



Advertise | Privacy | Mobile
Web01 | 2.8.141022.2 | Last Updated 7 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100