Question Syntax error

[ReSpade]

Im building an application and I keep getting a syntax error. Can anyone spot it?

Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
ds = New DataSet
da = New MySqlDataAdapter("SELECT from cost where item='" & txtSItem.Text & "', world='" & txtSWorld.Text & "')'", sqlcon)
da.Fill(ds, "cost")
Call showData()
End Sub

 

[jmcilhinney]

You don't use commas between conditions in a WHERE clause. You use AND and OR operators. Also, don't insert values into SQL code using string concatenation. To learn why and how to use parameters, follow the Blog link in my signature and check out my post on Parameters In ADO.NET.

 

[ReSpade]

You don't use commas between conditions in a WHERE clause. You use AND and OR operators. Also, don't insert values into SQL code using string concatenation. To learn why and how to use parameters, follow the Blog link in my signature and check out my post on Parameters In ADO.NET.

Thanks for the answer. Do you think you can show me how it is done right (answer with correct code) I'm afraid I do not understand your answer completely. I'm still a beginner at this. If you can that would be greatly appreciated. I defiantly read your blog post on that matter because I want to learn VB.NET as soon as possible.

 

[jmcilhinney]

You don't really need any programming experience. You just need to be able to read and have a little common sense. I said that you don't use commas so, given that there's only one comma in your SQL code, it should be obvious what to remove. I said that you use AND and OR operators. As you're taking out one comma it should be fairly obvious where to put the AND or OR and if you can speak English then you know the difference between AND and OR so you know which one to use. Programming doesn't exist in a vacuum. Don;t just ignore everything you already know about life, the universe and everything because you have to write some code.

 

[ReSpade]

You don't really need any programming experience. You just need to be able to read and have a little common sense. I said that you don't use commas so, given that there's only one comma in your SQL code, it should be obvious what to remove. I said that you use AND and OR operators. As you're taking out one comma it should be fairly obvious where to put the AND or OR and if you can speak English then you know the difference between AND and OR so you know which one to use. Programming doesn't exist in a vacuum. Don;t just ignore everything you already know about life, the universe and everything because you have to write some code.

I did what you told me. This is what the code looks like now. Although it no longer gives me the syntax error it doesn't do anything now. Is something wrong with this part of he code or the logic of my overall code?

    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        sqlcon.Open()
        ds = New DataSet
        da = New MySqlDataAdapter("SELECT from cost where item='" & txtSItem.Text & "' and world='" & txtSWorld.Text & "')'", sqlcon)
        da.Fill(ds, "cost")
        Call showData()
    End Sub

 

[jmcilhinney]

If that code is executed and no exception is thrown then it's going to populate a DataTable named "cost" in that DataSet with the data that matches those criteria. That's not nothing. Presumably that 'showData' method is supposed to be showing that data but there's no way that it could be because it doesn't have to that DataSet. The code is doing something alright. You're just not using the result properly.

 

[ReSpade]

If that code is executed and no exception is thrown then it's going to populate a DataTable named "cost" in that DataSet with the data that matches those criteria. That's not nothing. Presumably that 'showData' method is supposed to be showing that data but there's no way that it could be because it doesn't have to that DataSet. The code is doing something alright. You're just not using the result properly.

I get what you mean. I looked over at my showData() function and it looks alright. I'm already using it to show my datatable. Here is my showData() function code...

    Sub showData()


        ds = New DataSet
        da = New MySqlDataAdapter("Select * from cost", sqlcon)
        da.Fill(ds, "cost")


        lvDisplay.Items.Clear()
        If ds.Tables("cost").Rows.Count > 0 Then
            For i As Integer = 0 To ds.Tables("cost").Rows.Count - 1
                With lvDisplay.Items.Add(ds.Tables("cost").Rows(i).Item(0).ToString)
                    .SubItems.Add(ds.Tables("cost").Rows(i).Item(1).ToString)
                    .SubItems.Add(ds.Tables("cost").Rows(i).Item(2).ToString)
                    .SubItems.Add(ds.Tables("cost").Rows(i).Item(3).ToString)
                    .SubItems.Add(ds.Tables("cost").Rows(i).Item(4).ToString)
                    .SubItems.Add(ds.Tables("cost").Rows(i).Item(5).ToString)
                    .SubItems.Add(ds.Tables("cost").Rows(i).Item(6).ToString)
                End With
            Next
        End If


        txtLevel.Text = ""
        txtItem.Text = ""
        txtCost.Text = ""
        txtWorld.Text = ""
        txtClass.Text = ""
        txtType.Text = ""
        txtSubType.Text = ""


        sqlcon.Close()


    End Sub

 

[jmcilhinney]

The code from post #5 is creating a DataSet and populating a DataTable in it. It then calls 'showData', which completely ignores the DataSet that was previously created and creates and populates a new one. If you want to show the first set of data then don't create a new set. Get rid of the code in 'showData' that creates and populates the DataSet and instead pass a DataSet into the method via a parameter

 

[ReSpade]

The code from post #5 is creating a DataSet and populating a DataTable in it. It then calls 'showData', which completely ignores the DataSet that was previously created and creates and populates a new one. If you want to show the first set of data then don't create a new set. Get rid of the code in 'showData' that creates and populates the DataSet and instead pass a DataSet into the method via a parameter

Thank you for all the help. For the time being I think I have figured it out. I scraped the old code and recoded it a different way. Instead of the button_click search function I used a txtSItem_change function which automatically updates the table with the matching search string(no idea if that made any sense at all) Here is my new code that works.

Private Sub txtSItem_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtSItem.TextChanged


        strSQL = "SELECT * from cost where item like'%" & txtSItem.Text & "%'"
        sqlcon.Open()


        cmd = New MySqlCommand(strSQL, sqlcon)
        dr = cmd.ExecuteReader()


        lvDisplay.Items.Clear()


        Do While dr.Read()


            a = (dr.Item("level").ToString())
            b = (dr.Item("item").ToString())
            c = (dr.Item("cost").ToString())
            d = (dr.Item("world").ToString())
            f = (dr.Item("class").ToString())
            g = (dr.Item("type").ToString())
            h = (dr.Item("subtype").ToString())


            Dim lv As ListViewItem = lvDisplay.Items.Add(a)
            lv.SubItems.Add(b)
            lv.SubItems.Add(c)
            lv.SubItems.Add(d)
            lv.SubItems.Add(f)
            lv.SubItems.Add(g)
            lv.SubItems.Add(h)
        Loop
        dr.Close()
        sqlcon.Close()
        cmd.Dispose()
    End Sub