SQLexception varchar to int

chi2king

Member
Joined
Sep 28, 2006
Messages
20
Programming Experience
Beginner
I have an error in my code which states:
System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value '(ListBox1.SelectedValue.ToString)' to data type int. at System.Data.SqlClient.SqlConnection
Can anyone help me why this is happening. im using web developer express.
VB.NET:
Protected Sub DeleteButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim DBConn As New Data.SqlClient.SqlConnection("Data Source=XXX;Initial Catalog=XXX;Integrated Security=True")
        Dim DBCmd As New Data.SqlClient.SqlCommand
        Dim DBAdap As New Data.SqlClient.SqlDataAdapter
        Dim DS As New Data.DataSet
        DBConn.Open()
        Try
            DBCmd = New Data.SqlClient.SqlCommand("DELETE FROM family WHERE firstname = '" & TextBox2.Text & "' AND ID = '(ListBox1.SelectedValue.ToString)'", DBConn)
            
            DBCmd.ExecuteNonQuery()
            Response.Write("Your record is deleted")
            DBAdap = New Data.SqlClient.SqlDataAdapter("SELECT * FROM family", DBConn)
            DBAdap.Fill(DS)
            ListBox1.DataBind()
        Catch exp As Exception
            Response.Write(exp)
        End Try
        DBCmd.Dispose()
        DBAdap.Dispose()
        DBConn.Close()
        DBConn = Nothing
       
    End Sub

My purpose is to delete the row when i click the delete button. firstname is displayed on a textbox when i click on the listbox and ID is the value that is not displayed on the listbox.
Also how can i display the remaining details into the textboxes since i can only display one detail into one of the textbox.

VB.NET:
Protected Sub ListBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
        TextBox1.Text = ListBox1.SelectedItem.ToString
    End Sub
 
First of all you've inserted the literal string "ListBox1.SelectedValue.ToString" into your SQL code. That's obviously not what you want. You need to concatenate the value that that returns into the string, like you've done with TextBox2.Text.

Secondly, if the ID column contains numbers then there should not be single quotes around the value. Quotes are for text values only.
 
thank you

There are no more errors when i click the delete button but the data is not deleted in the listbox and in the database. the data is still displayed in the listbox.

Can you also help me with populating the data from listbox to textbox when i click the listbox since i can only display in the textbox one of the details of five details (columns) in the database tables.

By the way thank you for taking your time in helping me.
 
Given that you are using .NET 2.0 you can make your life so much easier by using the Dataset Designer to create your database connectivity code..

Have a google for Data Walkthroughs and read the results from MSDN
 
Correct SQL statement

Change this line:

DBCmd = New Data.SqlClient.SqlCommand("DELETE FROM family WHERE firstname = '" & TextBox2.Text & "' AND ID = '(ListBox1.SelectedValue.ToString)'", DBConn)

To

DBCmd = New Data.SqlClient.SqlCommand("DELETE FROM family WHERE firstname = '" & TextBox2.Text & "' AND ID = " & ListBox1.SelectedValue.ToString & ", DBConn)

Your SQL statement has integer value passed with single quotes. When ID field has datatype as INTEGER then value passed can not be placed in single quotes.
 
Back
Top