Simple select query not working with vb.net while delete query works fine

Question

I am writing a simple SQL Server query operation through vb.net application. I am having some strange problems.

This line is giving error:

dr = cmd.ExecuteReader()

This is giving me error "Invalid column name abhishek." Here abhishek is the data I am providing in TextBox1.Text. I am not able to think of any mistake by my side as this is a simple query. I am able to run other queries, like delete queries, on same table in a different form, so its not a database problem.

Any clue what's going wrong?

reginfo is the table name. name is one of the fields.

My complete code is below:

Imports System.Data.Sql
Imports System.Data.SqlClient
Public Class Form9
    Dim con As New SqlConnection()
    Dim cmd As New SqlCommand()

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        cmd.CommandText = "select * from reginfo where name=" + (TextBox1.Text) + ""
        Dim dr As SqlDataReader
        con.Open()
        cmd.Connection = con
        dr = cmd.ExecuteReader()    '<<< This line is creating problem
        If dr.Read() Then
            TextBox2.Text = dr(0).ToString()
        End If
        con.Close()
    End Sub

    Private Sub Form8_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        con.ConnectionString = "Data Source=ABHISHEK-PC\SQLEXPRESS;Initial Catalog=locserver;Integrated Security=True;Pooling=False"
    End Sub

    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click

    End Sub

End Class

Steve · Accepted Answer · 2014-04-06 14:15:57Z

2

if the name field is a text field then you need to enclose your textbox in single quotes, but this is bad advice to give. The only good approach to this kind of situations is through a parameterized query

cmd.CommandText = "select * from reginfo where name=@name"
cmd.Parameters.AddWithValue("@name",  TextBox1.Text)
Dim dr As SqlDataReader
con.Open()
cmd.Connection = con
dr = cmd.ExecuteReader()

Also, do not keep global objects like a connection or a command. It is always a good practice to instantiate a connection as late as possible and close it as soon as possible, better inside a Using block

Using con = New SqlConnection(...connection string here....)
    Using cmd = New SqlCommand("select * from reginfo where name=@name", con)
        con.Open()
        cmd.Parameters.AddWithValue("@name",  TextBox1.Text)
        Using dr = cmd.ExecuteReader
            '.... do you reading
        End Using
    End Using

End Using

In this way the connection is kept open for the minimum time possible and, also in case of exceptions is closed and disposed appropriately.

edited Apr 6, 2014 at 14:15

answered Apr 6, 2014 at 14:02

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

KyleMit Over a year ago

In VB.NET you can combine multiple usings together into a single declaration with a comma. Otherwise each using statement should probably have it's own level of indentation.

Collectives™ on Stack Overflow

Simple select query not working with vb.net while delete query works fine

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related