SQL SELECT statement (in vb.net) returning empty and/or weird rows

Question

I am using VS Express 2012, SQL Server 2012. I have one table (computers) and two rows with 10 columns (HaHu_ID, Serial_Number, Brand, Model, OS, Processor, HDD, Memory, ST, Speed).

I've been on this site reading as many posts as I can trying to create a vb.net interface to a SQL Server. I succeed at connecting them and returning the values to a DataGridView table with a

SELECT * FROM computers

BUT when I tried other commands like

SELECT * FROM computers 
WHERE 'HaHu_ID' = '101'

it returns an empty DataGridView row! Tried changing up 'HaHu_ID' to something random, but it still gave me empty rows. The correct column names are displayed but the rows are all empty.

When I tried another command "SELECT 'HaHu_ID', 'Brand' FROM computers" and it returned:

Column1---|--Column2

-HaHu_ID--|--Brand
-HaHu_ID--|--Brand

The column names have been changed to "Column1 and column2" and as you can see the rows are filled with what should be the column names! Frustrated I tried "SELECT 'HaHu', 'Brand_ID' FROM computers" (non existing columns in my database) and it returned with:

Column1---|--Column2

---HaHu----|--Brand_ID
----HaHu---|--Brand_ID

I went back to the original SELECT * FROM computers and it works again!

Here is my code:

Dim cs as New SqlConnection...
cs.Open()
'Dim da2 as New SqlDataAdapter("SELECT * FROM computers WHERE 'HaHu_ID' = '101'", cs)
Dim da2 as New SqlDataAdapter("SELECT * FROM computers", cs)
Dim table as New DataTable()
table.Locale = System.Globalization.CultureInfo.InvariantCulture
da2.Fill(table)
BindingSource1.DataSource = table
dataGrid1.DataSource = BindingSource1
cs.Close()

(I couldn't copy and paste it because I'm on another computer now). So the select * from computers statement works perfectly but as soon as I try something else it doesn't work.

Why is that? Thanks.

Melanie · Accepted Answer · 2013-04-05 19:52:48Z

1

Don't put single quotes around your column names. WHERE HaHu_ID = '101' is correct.

answered Apr 5, 2013 at 19:52

Melanie

3,1316 gold badges40 silver badges62 bronze badges

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

1 Comment

Banzai Ybk Over a year ago

Oh wow thanks...two days of madness because of a single quote? I almost gave up on it! Thank you very much!

CM Kanode · Accepted Answer · 2013-04-05 20:01:19Z

1

The single quotes are for designating characters or strings. The following SQL statement is selecting the string 'HaHu_ID' and the string 'Brand', and is returning for every record that you have in the computers table. And since no alias is assigned, the server returns defaults of Column1, Column2, etc.

SELECT 'HaHu_ID', 'Brand' FROM computers

To escape column names, use the square brackets as such:

SELECT [HaHu_ID], [Brand] FROM [computers]

answered Apr 5, 2013 at 20:01

CM Kanode

1,4261 gold badge11 silver badges14 bronze badges

Comments

Cortright · Accepted Answer · 2013-04-05 20:13:25Z

0

Right, you're comparing the two strings:

SELECT * FROM computers  WHERE 'HaHu_ID' = '101'

try:

SELECT 'foo' FROM computers WHERE HaHu_ID = '101'

(assuming it's a character type column and not numeric)

answered Apr 5, 2013 at 20:13

Cortright

1,1746 silver badges20 bronze badges

3 Comments

George Over a year ago

Why are you returning a string 'foo'? that will break his logic.

Cortright Over a year ago

Looks like he's just filling a generic DataTable in the code he pasted above, so it shouldn't break that. :) Also, asker: Try to avoid using select *, use select column names instead. You can get into trouble in several ways if you are querying with * and just accepting what it gives you.

Banzai Ybk Over a year ago

I had to search what the 'foo' part meant but after few minutes of googling, seems like it'll save me lots of typing. Didn't mean to Submit my post yet... Anyways, if '*' means "ALL", wouldn't it always return all the columns in that particular table? Thanks.

Metaphor · Accepted Answer · 2013-04-05 21:42:56Z

0

Is this what you're trying to do?

SELECT * FROM computers 
WHERE col1 = 'HaHu_ID' and col2 = '101'

or

SELECT * FROM computers 
WHERE col1 like '%HaHu_ID%' and col2 like '%101%'

if not, what does "SELECT * FROM computers" return for you?

answered Apr 5, 2013 at 21:42

Metaphor

6,42512 gold badges58 silver badges81 bronze badges

1 Comment

Banzai Ybk Over a year ago

No, I was simply trying to do: Select * FROM [computers] WHERE [HaHu_ID] = '101' Thankfully, Melanie and CM Kanode pointed me in the right direction and now it works fine.

lee · Accepted Answer · 2017-07-18 08:50:55Z

Private Sub TxtID_TextChanged(sender As Object, e As EventArgs) Handles TxtID.TextChanged

Dim ds As New My_Ticket_MachineDataSet

'connection settings

Dim con As New SqlConnection(" Data Source=LEES-PC;Initial Catalog=My Ticket Machine;Integrated Security=True")

'which row to select

Dim com As New SqlCommand("select * from FareChartTest where TicketPricesID = 3", con)

com.Parameters.Add("@TicketPricesID", SqlDbType.Int).Value = TxtID.Text

Dim adapter As New SqlDataAdapter(com)

    Dim table As New DataTable()

adapter.Fill(table)

    If table.Rows.Count() > 0 Then
        'which column to select
        TextBox4.Text = table.Rows(0)(0).ToString()
        ' return only 1 row
        'which column to select
        TextBox3.Text = table.Rows(0)(1).ToString()
    Else

        MessageBox.Show("NO Data Found")

    End If
End Sub

Collectives™ on Stack Overflow

SQL SELECT statement (in vb.net) returning empty and/or weird rows

5 Answers 5

1 Comment

Comments

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related