CODEWITHSUNDEEP
sql-servervb.netado.net
Banzai Ybk
Banzai Ybk

Reputation: 13

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

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.

Upvotes: 1

Views: 4438

Answers (5)

lee
lee

Reputation: 1

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

Upvotes: 0

Metaphor
Metaphor

Reputation: 6405

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?

Upvotes: 0

Cortright
Cortright

Reputation: 1174

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)

Upvotes: 0

CM Kanode
CM Kanode

Reputation: 1436

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]

Upvotes: 1

Melanie
Melanie

Reputation: 3111

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

Upvotes: 1

Related Questions