trying to use var. to tell sql what to do

[westsiderailway]

Hello everyone,

    Dim SI As String = ""
        SI = CheckedListBox1.SelectedItem
        Dim TextcommandSQL As String = ""
        Dim myCOLUMN As String = ""
        myCOLUMN = ComboBox1.SelectedItem


        TextcommandSQL = "SELECT * FROM test_table where " & myCOLUMN & " = " & SI

        MsgBox(TextcommandSQL)

        Dim con As New SqlConnection
        con.ConnectionString = (" Data Source=i7borgmatrix2\sqlexpress;Initial Catalog=TextSQL;Integrated Security=True")

            Dim queryString As String = TextcommandSQL
            Using connection As New SqlConnection(con.ConnectionString)
                Dim command As New SqlCommand(queryString, connection)
                connection.Open()
                Dim reader As SqlDataReader = command.ExecuteReader()
                Try
                    While reader.Read()
                        Console.WriteLine(String.Format("{0}, {1}", _
                            reader(0), reader(1)))
                    End While
                Finally
                    ' Always call Close when done reading.
                    reader.Close()
                End Try
            End Using

i am trying to use var. to tell sql what column and what row to search for in the db.

the user will select a column from a combobox <ie. weekday> and then select the item <ie. Tuesday>

then click the button to send the query to sql.

at the moment i am getting the error that the column "tuesday" or what ever day i select does not exist, when the day should be the day the sql searches for.!

Thank you for reading. :-)

[JerrimePatient]

Add this in your Form_Load

    cbSearchColumn.Items.Add("Test ID")
    cbSearchColumn.Items.Add("Test Name")
    cbSearchColumn.Items.Add("Test Address")
    cbSearchColumn.Items.Add("Test Description")

and add this in txtSearchString_TextChange

If cbSearchColumn.SelectedIndex = 0 Then
    sWhere = " where id = '" & txtSearchString.Text & "'"
ElseIf cbSearchColumn.SelectedIndex = 1 Then
    sWhere = " where name like '" & txtSearchString.Text & "%'"
ElseIf cbSearchColumn.SelectedIndex = 2 Then
    sWhere = " where address like '" & txtSearchString.Text & "%'"
ElseIf cbSearchColumn.SelectedIndex = 3 Then
    sWhere = " where description like '" & txtSearchString.Text & "%'"
End If
q = "select * from test_table" & sWhere; 'q is your query.

[djjeavons]

Hi

What does your SQL statement look like when you output it to a message box? Are you assigning the right values to the right variables?

Finally, if your WHERE criteria is supposed to be Tuesday, then this would suggest that the field is a string, so you will need to enclose the value within apostrophe's, or better yet, use parameterised queries, as in:

TextcommandSQL = "SELECT * FROM test_table where " & myCOLUMN & " = ?"

Dim command As New SqlCommand(TextcommandSQL, connection)
command.Parameters.AddWithValue("?", SI)

'Remainder of your code

HTH

[westsiderailway]

the statement is a correct sql statement in the "msgbox"

this line is so that the column "weekday" = the the day selected by the user.

correct sql statement-> "select * from test_table where weekday='monday'"

[djjeavons]

Sorry, I don't understand. You originally stated that the error was that Tuesday does not exist which suggested to me that maybe you were assigning the values to the wrong variables so that your statement looked something like: select * from test_table where Tuesday=.... but you are now saying that the statement is correct?

Also, my previous post is slightly incorrect as I assumed you were using Access for some reason. Swap ? for @SomeName for the parameterised queries.

[westsiderailway]

before it was saying that the column tuesday does not exist, which is correct. Tuesday is not the name of the column, weekday is the name of the column.

[pritaeas]

WEEKDAY is an SQL function, so if you also have a column with the same name, that might be your issue.

[djjeavons]

WEEKDAY is an SQL function, so if you also have a column with the same name, that might be your issue.

I thought the same but checked the reserved words and tested and this does not cause any issues.

before it was saying that the column tuesday does not exist, which is correct. Tuesday is not the name of the column, weekday is the name of the column.

So what is the problem at the moment?

[westsiderailway]

Hi Jerrime,

what is this....

cbSearchColumn.Items.Add("Test ID")
    cbSearchColumn.Items.Add("Test Name")
    cbSearchColumn.Items.Add("Test Address")
    cbSearchColumn.Items.Add("Test Description")


i thought that it might be you shortcut for the combobox......?

[westsiderailway]

the problem at the moment is this error..

{"Incorrect syntax near '='."}




TextcommandSQL = "SELECT * FROM test_table where " & myCOLUMN & " = " & SI

  Dim queryString As String = TextcommandSQL
            Using connection As New SqlConnection(con.ConnectionString)
            Dim command As New SqlCommand(queryString, connection)
            command.Parameters.AddWithValue(" = ", SI)

[djjeavons]

As mentioned earlier, if your value is a string then you need to enclose it in apostraphes or use parameters.

Can you try:

TextcommandSQL = "SELECT * FROM test_table where " & myCOLUMN & " = '" & SI & "'"

[westsiderailway]

Thank you very much .... NO Errors. :-)

don't know why i did'nt see that, seeing that i put "&" around the mycolumn. heheeh

now all i goto do is ,figure out how to get the reader to input to the DGV. this is give the old brain a work out...heheeh

[djjeavons]

now all i goto do is ,figure out how to get the reader to input to the DGV. this is give the old brain a work out...heheeh

If you want to show data in a DataGridView then don't use a DataReader. They are specifically used for forward read only operations. What you want to do is use something like a DataTable that can then be bound to the DataGridView.

For example:

Dim connectionString As String = "Server=YourServerAddress;Database=YourDatabase;Trusted_Connection=True;"
Dim selectStatement As String = "SELECT * FROM Test_Table"

Using adapter As New SqlDataAdapter(selectStatement, connectionString)

    Dim table As New DataTable
    adapter.Fill(table)

    DataGridView1.DataSource = table

End Using

Modify the above to suit your needs.

[westsiderailway]

Thank you very much, this is what i did...

      Dim SI As String = ""
        SI = CheckedListBox1.SelectedItem
        Dim TextcommandSQL As String = ""
        Dim myCOLUMN As String = ""
        myCOLUMN = ComboBox1.SelectedItem


        TextcommandSQL = "SELECT * FROM test_table where " & myCOLUMN & " ='" & SI & "'"

        MsgBox(TextcommandSQL)

        Dim con As New SqlConnection
        con.ConnectionString = (" my details")

            Dim queryString As String = TextcommandSQL
            Using connection As New SqlConnection(con.ConnectionString)
            Dim command As New SqlCommand(queryString, connection)
            'command.Parameters.AddWithValue(" = ", SI)
            connection.Open()

            Dim constring As String = "my details"
            Dim selectStatement As String = TextcommandSQL
            Dim DataApp As SqlDataAdapter = New SqlDataAdapter(selectStatement, con)
            Dim DGVTable As New DataTable
            DataApp.Fill(DGVTable)
            DataGridView1.DataSource = DGVTable

        End Using

not sure if i needed 2 connection string....but it all works...

very much apprecated :-)

[djjeavons]

Your welcome.

I took the liberty of tidying your code up a little bit which you are welcome to use. Mainly just removing unnecessary variables etc. I haven't tested it but it should produce the same output:

Dim SI As String =  CheckedListBox1.SelectedItem
Dim myCOLUMN As String = ComboBox1.SelectedItem

Dim queryString As String = "SELECT * FROM test_table where " & myCOLUMN & " ='" & SI & "'"
Dim conString As String = "Data Source=i7borgmatrix2\sqlexpress;Initial Catalog=TextSQL;Integrated Security=True;"

Using DataApp As New SqlDataAdapter(queryString, conString)

    Dim DGVTable As New DataTable

    DataApp.Fill(DGVTable)
    DataGridView1.DataSource = DGVTable

End Using

[westsiderailway]

Once again, Thank You very much. works well. :-)

with this out of the way, i can now work on the date search. :-)

[JerrimePatient]

This is for Combobox items

cbSearchColumn.Items.Add("ID")
cbSearchColumn.Items.Add("Name")
cbSearchColumn.Items.Add("Address")
cbSearchColumn.Items.Add("Description")

This is for your search.

If cbSearchColumn.SelectedIndex = 0 Then
    sWhere = " where id = '" & txtSearchString.Text & "'"
ElseIf cbSearchColumn.SelectedIndex = 1 Then
    sWhere = " where name like '" & txtSearchString.Text & "%'"
ElseIf cbSearchColumn.SelectedIndex = 2 Then
    sWhere = " where address like '" & txtSearchString.Text & "%'"
ElseIf cbSearchColumn.SelectedIndex = 3 Then
    sWhere = " where description like '" & txtSearchString.Text & "%'"
End If
q = "select * from test_table" & sWhere;
Dim t As New DataTable
da.SelectCommand = new SqlCommand(q, SqlConnecor)
da.(Fill)
dataGridView1.Datasource = t

[westsiderailway]

ok thanks,
the name "cbSearchColumn" had me fooled for a while. :-)
seeing it here in your latest post , it just dawned on me, what it was....

[westsiderailway]

hi again folks,
in trying to get the date search working, have come across this error.

  Dim SI As String = CheckedListBox1.SelectedItem
        Dim myCOLUMN As String = ComboBox1.SelectedItem
        Dim myDate As String = DateTimePicker1.Value
        'Conversion failed when converting date and/or time from character string.
        MessageBox.Show(myDate)
        Dim myTime As String = DateTimePicker1.Text
        MessageBox.Show(Me.DateTimePicker1.Text)
        Dim queryString As String = "SELECT * FROM test_table where " & myCOLUMN & " ='" & myDate & "'"
        Dim conString As String = "Data Source=i7borgmatrix2\sqlexpress;Initial Catalog=TextSQL;Integrated Security=True;"
        Using DataApp As New SqlDataAdapter(queryString, conString)
            Dim DGVTable As New DataTable
            DataApp.Fill(DGVTable)
            DataGridView1.DataSource = DGVTable
        End Using

Conversion failed when converting date and/or time from character string.
only happens when trying to search for dates.

[westsiderailway]

ok folks have found out how to do it.
been doing some reading about the quarks of SQL date format.

am now using textbox instead of datetimepicker.
tell the user to input the date as "yyyy/mm/dd"

and it works. :-)

now i will see if i can do the "and / or" functions...hehehe or < / > .

[westsiderailway]

ok i am trying to do this..
"select * from test_table where date like '2012%' "
this is my query string

Dim queryString As String = "SELECT * FROM test_table where " & myCOLUMN & " like '" & myDate & "'"

it works as far as does not get any errors, but, all i get is a blank DGV row with headers.

[Reverend Jim]

You can't use LIKE on numeric values. You can use DATEPART to extract the year portion. Your query string should be

qry = "SELECT * FROM test_table" &
      " WHERE DATEPART(YY,myColumn) = " & myDate

[westsiderailway]

thanks for the reply,but did not work...got this error.

: "yy" is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90.

Dim QueryString As String = "select *  from test_table" & "where datepart(YY,myCOLUMN) = " & myDate

[Reverend Jim]

I just ran that query as a test against an MS SQL database and it returned the desired results. What database engine are you using?

My exact query was (using sample NorthWind DB)

SELECT * FROM Orders
 WHERE DATEPART(YY,OrderDate) = 1997

[JerrimePatient]

just set the datetimepicker when you want to save it.

like this. datetimepicker.Value.ToString("yyyy-MM-dd HH:mm:dd")

myDate = datetimepicker.Value.ToString("yyyy-MM-dd HH:mm:dd")
qry = "SELECT * FROM test_table" & _
    " WHERE DATEPART(YY,myColumn) = '" & myDate & "'"

or

qry = "SELECT * FROM test_table" & _
    " WHERE DATEPART(YY,myColumn) = '" & _
    datetimepicker.Value.ToString("yyyy-MM-dd HH:mm:dd") & "'"

[westsiderailway]

hi there,
i am using ms-sql2012 server, and it does allow numbers in the like statement
below is the line i tested it with...

select * from Test_Table where date like '2012%';

[Reverend Jim]

I'm also using MS SQL 2012 and using LIKE with dates returns nothing. It works with numerics, but not with dates.

 select @@VERSION
 Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 
Feb 10 2012 19:39:15 
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

[westsiderailway]

Microsoft SQL Server 2008 (SP3) - 10.0.5520.0 (X64) 
    Jul 11 2014 16:11:50 
    Copyright (c) 1988-2008 Microsoft Corporation
    Express Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

[westsiderailway]

select * from Test_Table where date like '2015-04-29%';

this works fine in ssms.?

[Reverend Jim]

But

select * from Test_Table where date like '2015%'

does not, at least if stored as a date. Actually it should be

select * from Test_Table where [date] like '2015%'