trying to use var. to tell sql what to do - Page 2

[westsiderailway]

i am sorry to say,but, it does work,and the Date column data type is date.

well i just tried it in ssms and both version give the same result.

go figure...... :-)

[djjeavons]

I have to agree that it does work on a date type BUT it does not work on a datetime. I wonder if this is possibly due to SQL Server converting the date type to a varchar when using the like operator.

For safety sake though, I would recommend converting the date field to a varchar or using the DatePart function. For example:

select * from Test_Table where convert(varchar, date, 102) like '2015%';

For a list of date and time styles refer to the Transact-SQL documentation on CAST and CONVERT.

Also, as Reverend Jim pointed out, you should avoid using keywords as column names such as date or enclose them in brackets as in [date].

[westsiderailway]

what could go wrong, and why does not ssms scream loud and clear that you are using a reserved word(S).

this is just for my own use only.

where does it say that it converts the date to a vchar. why does it do that, why have a date type at all.

i did read somewhere that you never have a data type of datetime, you split them up into date and time.

[djjeavons]

what could go wrong, and why does not ssms scream loud and clear that you are using a reserved word(S)

SSMS will for certain reserved words however date is not one of them. But as long time programmers you get very wary of using data type names for fields and when you see others do it you always encourage them to enclose them in brackets if they can't rename them. To give you an example of when SSMS will notify you, design your current table and add a column called pivot. As soon as you tab away from the name, note how SSMS has enclosed it in square brackets (or at least it does in SQL Server 2014). Now save the table and write the following query in SSMS:

SELECT pivot FROM Test_Table

You should get an error stating an incorrect syntax near the keyword 'pivot'. Now change the query too:

SELECT [pivot] FROM Test_Table

Now you get data returned (even if they are nulls).

In terms of what could go wrong, probably nothing at this stage. But it is good to be aware of these things. What would you do if no data was returned as is the case with a datetime data type using the like operator. You wouldn't know, whereas now you do and you could say, "oh yeah, I need to convert that to a varchar if I want to use like".

where does it say that it converts the date to a vchar. why does it do that, why have a date type at all.

It doesn't and I struggled to find documentation on this other than a few forum posts, hence the reason I said I wonder if it is possibly due to.. although it does sound like I am making a statement and that wasn't my intention.

[westsiderailway]

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

this works for me, weather it is good code or not does not matter. unless someone can tell me why i should change it, and the reason why. then i might consider it. :-)

hope on one takes offence to what i have type, just my opinion. allways open to another way of doing things if it makes things better. :-)

[djjeavons]

hope on one takes offence to what i have type, just my opinion.

Firstly, no offence.

this works for me, weather it is good code or not does not matter. unless someone can tell me why i should change it, and the reason why. then i might consider it. :-)

Ok, challenge taken :)

Regardless of the argument as to whether LIKE works on a date or not, I would seriously encourage you to change that line of code to use parameterised queries. Why? You are open to serious security issues with that code, specifically SQL Injection attacks. Please read that link and see the damage that can be done to your database or to the information in your database and then look at how easy it is to add some safe guards.

[westsiderailway]

what if i changed the names of my columns to "dates" and "times" that should be ok, yes? :-)

i would proberly have to delete the db and start again, yes.? :-)

[westsiderailway]

ok point taken, but, my server is on my machine and as far as i know sql-server does not have access to the internet. so it is a internal only operation.
not on a cloud somewhere..

do i still have to be worried this.?

[djjeavons]

what if i changed the names of my columns to "dates" and "times" that should be ok, yes? :-)

Yes, that would be ok but personally I would be thinking of what the data represented in those fields. If I was storing logging data then I would call it DateLogged, likewise if it was customer orders I would call it OrderDate and so on. Just like variable names in source code, good naming conventions for databases is just as important.

i would proberly have to delete the db and start again, yes.? :-)

No, you just need to design the table in SSMS. If you get the error that you can't save the table because it is prevented then go to the "Tools" menu and select "Options", then select "Designers" then "Table and Database Designers". There is a check box labelled "Prevent saving changes that require table re-creation", make sure this is unchecked.

[djjeavons]

ok point taken, but, my server is on my machine and as far as i know sql-server does not have access to the internet. so it is a internal only operation.
not on a cloud somewhere..
do i still have to be worried this.?

Yes, you should still be worried about this. Learning and understanding these things early will only make you a better programmer in the future and let's be honest, we are only talking about changing one line of code to use parameterised queries, so it is nothing majorly difficult. Also, there are other benefits to parameterised queries, such as not having to worry about the data types and therefore whether you enclose them in apostraphe's or not.

Finally, your code becomes much more readable as well and with code that is easier to read, it is easier to maintain. So many benefits to you regardless of whether your code is only used by you or by millions.

[westsiderailway]

thanks for this....
No, you just need to design the table in SSMS. If you get the error that you can't save the table because it is prevented then go to the "Tools" menu and select "Options", then select "Designers" then "Table and Database Designers". There is a check box labelled "Prevent saving changes that require table re-creation", make sure this is unchecked.

have changed the 2 columns from date,time to FuelDate,FuelTime.

could you show me how to change the line of code that is needed to be changed.

Thank you for your time and patience.

[djjeavons]

The following uses a parameter called @FuelDate in the queryString, note that it is not enclosed in apostraphes. Then you add a parameter value to the Parameters collection of the command object before filling your table with the returned data.

Dim connectionString As String = "Server=DJJEAVONS-PC\SQLEXPRESS;Database=Test;Trusted_Connection=True;"
Dim queryString As String = "SELECT * FROM Test_Table WHERE FuelDate LIKE @FuelDate"

Using adapter As New SqlDataAdapter(queryString, connectionString)

    adapter.SelectCommand.Parameters.AddWithValue("@FuelDate", "2015%")

    Dim table As New DataTable
    adapter.Fill(table)

    DataGridView1.DataSource = table

End Using

So now you have done the minimum to safeguard you against SQL Injection attacks, but now you also do not need to worry about data types and also you get the benefit of readable code. For example, can you imagine the following INSERT sql statement:

"INSERT INTO ATable (column1, column2, column3) VALUES ('" & myTextValue & "', '" & anotherTextValue & "', '" & andAnotherTextValue & "')"

As opposed to:

"INSERT INTO ATable (column1, column2, column3) VALUES (@Value1, @Value2, @Value3)"

Command.Parameters.AddWithValue("@Value1", myTextValue)
Command.Parameters.AddWithValue("@Value2", aTextValue)
Command.Parameters.AddWithValue("@Value3", anotherTextValue)

It might be a few more lines of code but it's worth it.

[westsiderailway]

Thank you very much.

as to your "insert" line yes i can imagine that line, as it is exctly like the insert line in my other program.heheeh THAT is going to be changed. :-)

as you might be able to tell, i am making a DB of the amount of fuel my car uses. am keeing all the receipts and inputing them.

so my program will have 8 columns.

[westsiderailway]

another question for you , if you don't mind. :-)

have noticed that when inserting into the DB, it does not mind if you do not have the columns as below code...

Dim sql As String = "INSERT INTO Test_Table VALUES (@Weekday,@FuelDate,@FuelTime)"

is this a good thing or a bad thing.?

Thanks again. :-)

[westsiderailway]

have inserted my own var. into parts your code for me.

Dim queryString As String = "select * from test_table where " & Search_COLUMN & " like @fueldate"

adapter.SelectCommand.Parameters.AddWithValue("@fueldate", Search_Date & "%")

this are the code for my var.

 Dim Search_COLUMN As String = ComboBox1.SelectedItem

  Dim Search_Date As String = txtSearchFullDate.Text

when the program opens the user selects a column form the drop down list and then inputs the date into the textbox and clicks the button.

[westsiderailway]

you know i could have swore that the following line worked before?

 Dim queryString As String = "select * from test_table  where " & Search_COLUMN & "Like @fueldate"

now i am getting an error, Incorrect syntax near the keyword 'Like'.

[djjeavons]

have noticed that when inserting into the DB, it does not mind if you do not have the columns as below code...
Dim sql As String = "INSERT INTO Test_Table VALUES (@Weekday,@FuelDate,@FuelTime)"

This is perfectly valid. Basically, if your table only contains three columns, as suggested above, and you are writing data to all three columns then you do not need to specify the column names. This is also true if you had an Identity column (so four columns) as the Identity column is handled for you by SQL Server.

If, however, you only supplied two values then you would get an error stating that the Column name or number of supplied values does not match the table definition.

now i am getting an error, Incorrect syntax near the keyword 'Like'.

It does work, or at least it does in my tests. Have you verified that the value you are supplying to @fueldate is valid? All my tests have assumed that you were using a value such as '2015%'.

[westsiderailway]

Hi there,
sorry about this, it seems that sql did not like the underscore char. in the var.name.?

have now changed this var. to

Dim SearchCOLUMN As String = ComboBox1.SelectedItem

and it is working again.?

to give you an idea of what my program does....

the user selects from a combobox the column that the users wants to search, which goes into the var.SearchColumn.(if selected fueldate)
then the user is asked to type in a full date or a partial date starting with the year and or month.
this goes into the var.SearchDate.
this 2 vars. are then included in the sql query.

another question, if you don't mind. :-)
have been trying to add another sql command "order by" to the query... so far without any luck.

Have figure out , how to sort the dataBase. Yeah!! :-)
i am doing the sorting in the DGV.

[westsiderailway]

ok i am trying to do this..

select * from Test_Table where FuelTime between '09:00' and '19:00' order by FuelTime asc;

not to fussed about the orderby as i can do that with a sort of the DGV.

this is the main line so far...

Dim queryString As String = "select * from Test_Table where FuelTime between " & "'" & TextBox1.Text & "'" & "and" & "'" & TextBox2.Text & "'"

is there a better way of doing this.?

[westsiderailway]

i have treid using parameters...

  '   adapter.SelectCommand.Parameters.AddWithValue("@ST1", TextBox1.Text)
            '  adapter.SelectCommand.Parameters.AddWithValue("@ST2", TextBox2.Text)

but i get an error, saying that it cannot covert between date/time to string.