-
May 27th, 2015, 09:31 PM
#1
Thread Starter
Frenzied Member
SQL Building
Should a string in an sql statement be enclosed in single or double quotes ?
And does that become the only character in the string that needs to be escaped ?
Thanks.
-
May 27th, 2015, 09:41 PM
#2
Re: SQL Building
You should parameterize your SQL command so that you don't have to worry about escaping the literal. To this for example:
Code:
Private Sub InsertUser(ByVal user As String)
Dim connection As SqlConnection = Nothing
Try
'Open the connection
connection = New SqlConnection("My Connection String Here")
'Start the command
Using cmd As SqlCommand = New SqlCommand("INSERT INTO [MyTable] ([UserColumn]) VALUES (@user);", connection)
'Setup the parameters
cmd.Parameters.AddWithValue("@user", user)
'Open up the connection
connection.Open()
'Execute the Command
cmd.ExecuteNonQuery()
'Close the connection
connection.Close()
End Using
Catch ex As Exception
'Display the error
MessageBox.Show(ex.Message)
Finally
'Close the connection if it was left open
If connection IsNot Nothing Then
If connection.State = ConnectionState.Open Then
connection.Close()
End If
connection.Dispose()
End If
End Try
End Sub
In the code I pass the parameter user in the SQL and then add the value user from the parameters of the method. This is so that the SQL query is all one string and I'm not escaping anything. It can also handle values such as O'Hare or User--Day.
-
May 27th, 2015, 10:00 PM
#3
Thread Starter
Frenzied Member
Re: SQL Building
That's thrown me a bit dday. I'm not familiar with 'cmd.Parameters.AddWithValue' I have a Select statement and the criteria is entered by the user.
It's the Where clause I want to construct. Any advice there?
-
May 27th, 2015, 10:21 PM
#4
Re: SQL Building
entered by the user. -- All the more reason for Parameters! Parameters! Parameters!
Example:
Code:
'Start the command
Using cmd As SqlCommand = New SqlCommand("Select Field1, Field2, Field3, Field4 From MyTable Where User = @User", connection)
'Setup the parameters
cmd.Parameters.AddWithValue("@User", UserName.Text) 'Assumption - UserName is a text box...
If you need a reason to use parameters - Take a look at this. It's a bit tongue in cheek... but it's still a very real problem (called SQL Injection).
Just keep in mind, that when you use AddWithValue it uses the type of the VARIABLE or input value as the datatype for the parameter - it does not know or care about what the database says the field really is.
This can lead to issues when adding dates or numbers where the input was a text box, or other means where the type may not actually match. In those case you can either use the .Parameters.Add and explicitly set the datatype... or you convert the value to a variable of the appropriate type, then use that with AddWithValue.
Keep in mind that using parameters does NOT result in simple substitution.... so something like this isn't going to work:
Code:
'Start the command
Using cmd As SqlCommand = New SqlCommand("Select Field1, Field2, Field3, Field4 From @TableName Where ID = @ID", connection)
Dim ID as integer
Integer.TryParse(IDInput.Text, ID) ' Here I convert the text box input to a typed integer variable
'Setup the parameters
cmd.Parameters.AddWithValue("@TableName", TableList.Text) 'TableList is a dropdown list box...
cmd.Parameters.AddWithValue("@ID", ID) 'So here, when I add it, it knows that it is a number and not a string
I know it seems like it should work. It doesn't. Never has. Never will.
-tg
-
May 27th, 2015, 10:43 PM
#5
Re: SQL Building
Originally Posted by AlexanderBB
That's thrown me a bit dday. I'm not familiar with 'cmd.Parameters.AddWithValue' I have a Select statement and the criteria is entered by the user.
It's the Where clause I want to construct. Any advice there?
There's no issue constructing a SQL query using conditional logic and string concatenation but that is still not justification for not using parameters. To learn about using parameters in general, follow the Blog link in my signature below and check out my post on Parameters In ADO.NET. Here are two examples of how you might construct queries with parameters, one using dynamic SQL and one using static:
vb.net Code:
Dim command As New SqlCommand("SELECT * FROM MyTable", connection) Dim criteria As New List(Of String) If givenNameTextBox.TextLength > 0 Then criteria.Add("GivenName = @GivenName") command.Parameters.Add("@GivenName", givenNameTextBox.Text) End If If familyNameTextBox.TextLength > 0 Then criteria.Add("FamilyName = @FamilyName") command.Parameters.Add("@FamilyName", familyNameTextBox.Text) End If If criteria.Count > 0 Then command.CommandText &= " WHERE " & String.Join(" AND ", criteria) End If
vb.net Code:
Dim command As New SqlCommand("SELECT * FROM MyTable WHERE (@GivenName IS NULL OR GivenName = @GivenName) AND (@FamilyName IS NULL OR FamilyName = @FamilyName)", connection) command.Parameters.Add("@GivenName", SqlDbType.VarChar, 50).Value = If(givenNameTextBox.TextLength > 0, CObj(givenNameTextBox.Text), DBNull.Value) command.Parameters.Add("@FamilyName", SqlDbType.VarChar, 50).Value = If(familyNameTextBox.TextLength > 0, CObj(familyNameTextBox.Text), DBNull.Value)
-
May 27th, 2015, 10:52 PM
#6
Thread Starter
Frenzied Member
Re: SQL Building
Hi Techgnome
I massively don't get this ! Sorry, not even your link (Some kind of comic ?)
I did read a bit about SQL injection and parameters. It left me really msytified. As I understand it, you replace what the user enters with something else.
But if you need what they enter, and have no way of knowing what it is, how can you change it to something else that works?
I've never used cmd.Parameters.AddWithValue so will have to figure what that does. But why care about datatypes? If the wrong thing is entered, it probably won't match and they'll get no result.
You guys know so much more than me, that my assumptions must be wrong or flawed, but I don't see where yet !
My main issue is handling the wrong number of single/double quotes correctly.
Please expand a bit ?
-
May 27th, 2015, 11:15 PM
#7
Thread Starter
Frenzied Member
Re: SQL Building
Hi jmcilhinney
Tahnks.. I'm trying to step through your example but have struck a snag with "Connection" on line 1
The Sql connection string here (date Source?) is too long. Could you give me a new line 1 using ole command please
-
May 27th, 2015, 11:27 PM
#8
Re: SQL Building
Originally Posted by AlexanderBB
I did read a bit about SQL injection and parameters. It left me really msytified. As I understand it, you replace what the user enters with something else.
But if you need what they enter, and have no way of knowing what it is, how can you change it to something else that works?
Yes it's a comic... it shows what can happen if you take user input and just use concatenation to stuff it in there... it can lead to someone wiping out your table or worse, your database. For most of us, it's probably not really that big deal... for some of us it's a HUGE deal... imagine wiping out a company's entire accounting data. Scary stuff. So it's generally a good idea to practice the good habits, no matter how trivial. someone once put it "program as if your life depended on it" ... meaning don't take shortcuts that "just work" ... there are some posts on here (fear not, this isn't one of them) where I think that I need to start taking better care of myself, as I don't want some of these posters here programming my pacemaker with code (or cod) they copied from the forums and just paste into the process...
Originally Posted by AlexanderBB
I did read a bit about SQL injection and parameters. It left me really msytified. As I understand it, you replace what the user enters with something else.
But if you need what they enter, and have no way of knowing what it is, how can you change it to something else that works?
There's two ways of dealing with user input... 1) is to sanitize it... doubling up quotes... checking for entered escped characters that could lead to broken SQL ... it's tedious and in the end you'll innevitably miss something. Not to mention that when things do go wrong, it's actually more difficult to debug. Or 2) use parameters... then you don't need to do anything to the data, (personal opinion, the less I have to touch the user's data and manipulate it, the better), you simply pass it through and the database engine will take care of it. You don't change it to something that works... that's part of the point.
Originally Posted by AlexanderBB
I've never used cmd.Parameters.AddWithValue so will have to figure what that does. But why care about datatypes? If the wrong thing is entered, it probably won't match and they'll get no result.
Why does the type matter? Because "1" (the string) isn't really the same as 1 (the number) ... Better example - dates.... 1/1/20015 ... if you pass it as the wrong type, you'll either get a really small number (1 divided by 1, dividend by 2015) or you'll end up with a syntax or an invalid value error in your SQL. Again, back to numbers, what if the user enters "007" ... but the field is numbers (so all it has is 7) ... since "007" isn't the same as 7... you won't get back your data.
Originally Posted by AlexanderBB
You guys know so much more than me, that my assumptions must be wrong or flawed, but I don't see where yet !
My main issue is handling the wrong number of single/double quotes correctly.
Please expand a bit ?
Well, we've all been there at some point... takes time and practice.
As for the wrong number of quotes.... Ahhh see young grasshopper, buy using parameters, one does NOT need to care about quotes...
-tg
-
May 27th, 2015, 11:42 PM
#9
Re: SQL Building
Originally Posted by AlexanderBB
Hi jmcilhinney
Tahnks.. I'm trying to step through your example but have struck a snag with "Connection" on line 1
The Sql connection string here (date Source?) is too long. Could you give me a new line 1 using ole command please
This is basic ADO.NET. You should know by this stage that if you are creating a command object then you pass a SQL statement and a connection object to the constructor. That's not even a relevant part of the example; it's just there for completeness. If you don't know how to create an ADO.NET command then how can you possibly hope to execute one with SQL code constructed dynamically?
If you don't know the basics of ADO.NET then you should learn that first. You might follow the Database FAQ link in my signature below and check out the .NET links there, which includes my own code examples for the most common ADO.NET scenarios.
-
May 27th, 2015, 11:44 PM
#10
Thread Starter
Frenzied Member
Re: SQL Building
All read and digested tg. Thank you. But datatypes.. I'm validating the user input data so a number is a number, a date a data and everything else is a string.
I may have up to 20 "Where" clauses to sort out so was pleased to read from jmcilhinney there's "no issue constructing a SQL query using conditional logic and string concatenation"
My data is not "injection critical' but I'm all for "not caring about quotes" so will plug on figuring out what this magic parameter stuff is. It's a mystery what the "@" is
e.g. "GivenName = @GivenName" deosn't mean a thing. I'm keen to see... from the example in msg 5 it must be a critical but how @GivenName can become injection safe, and avoid any quotes issue....
-
May 27th, 2015, 11:45 PM
#11
Re: SQL Building
Originally Posted by AlexanderBB
Hi jmcilhinney
Tahnks.. I'm trying to step through your example but have struck a snag with "Connection" on line 1
The Sql connection string here (date Source?) is too long. Could you give me a new line 1 using ole command please
Or are you referring to the second example and saying that the SQL statement that you are using in your own code is too long? A SQL statement is not a connection string and if it's too long then it doesn't matter what provider it's for. In that case the obvious option is to use the first example, which results in shorter SQL.
-
May 27th, 2015, 11:48 PM
#12
Re: SQL Building
Originally Posted by AlexanderBB
I may have up to 20 "Where" clauses to sort out so was pleased to read from jmcilhinney there's "no issue constructing a SQL query using conditional logic and string concatenation"
No, you can never have more than one WHERE clause. Perhaps you mean that you may have that many criteria in the WHERE clause.
Originally Posted by AlexanderBB
It's a mystery what the "@" is
e.g. "GivenName = @GivenName" deosn't mean a thing. I'm keen to see... from the example in msg 5 it must be a critical but how @GivenName can become injection safe, and avoid any quotes issue....
If that's the case then you have chosen to ignore the advice I provided:
To learn about using parameters in general, follow the Blog link in my signature below and check out my post on Parameters In ADO.NET.
If you're not going to bother to read information when you're directed to it then I don't see why I should continue to bother volunteering my time to help.
-
May 27th, 2015, 11:48 PM
#13
Thread Starter
Frenzied Member
Re: SQL Building
> That's not even a relevant part of the example; it's just there for completeness.
Yes I know, but I haven't got an example to hand and wanted to step through the rest of and learn something. I'll get one, just mot quickly.
Have got thousands of ADO connection strings but used in Access and almost everything is different in vbNet.
-
May 27th, 2015, 11:51 PM
#14
Thread Starter
Frenzied Member
Re: SQL Building
jmcilhinney it was your blog I referred to in msg #6. I didn't make a lot of sense to me, but I know that's me - not your blog!
-
May 27th, 2015, 11:59 PM
#15
Re: SQL Building
Originally Posted by AlexanderBB
jmcilhinney it was your blog I referred to in msg #6. I didn't make a lot of sense to me, but I know that's me - not your blog!
Post #6 was made 9 minutes after my post suggesting that you read my blog post. I would expect it to take at least 9 minutes to read that blog post, never mind understand it. You're basically saying to us that we should spend more of our time to explain something to you that we've already taken the time to explain elsewhere because you don't want to spend your time reading that original explanation. You'll excuse me if I consider that an unreasonable request on your part. I take the time to write blog posts like that specifically so that I don't have to keep addressing the same questions over and over. Make use of the information available or do without because I won't be contributing further.
-
May 28th, 2015, 12:28 AM
#16
Thread Starter
Frenzied Member
Re: SQL Building
jmcilhinney, I read your blog about a month ago, 2 or 3 times in a row a I remember thinking ' I wish I knew what this guy knows" but really it make little sense to me and there was nothing I could get from it. That may happen further down the track. I defer to your superior knowledge and certainly appreciate everyone's help on the forum (and there's been a lot of it and it's great).
I'll get there but I have to interpret and understand things my way, not your way. Examples that you can step through are my favourite.
Once the connection string sorted I'll see just what your msg 5 is doing. You could have explained that ... I was trying avoid sidetracking to that while keen to see what this "@" is... . I had an sql Connction string a few days ago, but the path was too long, so opted to keep ole connection details instead.
Anyway, cherioo and thanks for your help in the past. No more darkening doorstep etc !
-
May 28th, 2015, 03:00 AM
#17
Hyperactive Member
Re: SQL Building
Originally Posted by AlexanderBB
My data is not "injection critical' but I'm all for "not caring about quotes" so will plug on figuring out what this magic parameter stuff is. It's a mystery what the "@" is
e.g. "GivenName = @GivenName" deosn't mean a thing. I'm keen to see... from the example in msg 5 it must be a critical but how @GivenName can become injection safe, and avoid any quotes issue....
'@GivenName' is simply a placeholder, otherwise known as a parameter. This allows you to provide a value to it for use at execution.
'GivenName' without the @ sign simply refers to a column within the table upon which the query is being executed.
Parameters can be used as a way to pass values safely into your SQL command String.
the Comand.Parameters.AddWithValue method is structured as follows:
Comand.Parameters.AddWithValue(<Parameter-Name>,<Value>)
<Parameter-Name> is the name which you have given your parameter within the SQL string
<Value> is the value you would like that parameter to be replaced with when the query is executed.
For example
"SELECT * FROM PEOPLE WHERE NAME= 'Dave';"
Is the same as
"SELECT * FROM PEOPLE WHERE NAME= @Name;"
Comand.Parameters.AddWithValue("@Name","Dave")
Alternatively, using a textbox
DONT DO THIS
"SELECT * FROM PEOPLE WHERE NAME= '" & TextBox1.Value "';"
Instead Do This
"SELECT * FROM PEOPLE WHERE NAME= @Name;"
Comand.Parameters.AddWithValue("@Name",Textbox1.Value)
I Hope this clears up any confusion
Last edited by Leary222; May 28th, 2015 at 04:20 PM.
Reason: Clairty
-
May 28th, 2015, 04:24 AM
#18
Re: SQL Building
Hi Techgnome
I massively don't get this ! Sorry, not even your link (Some kind of comic ?)
I did read a bit about SQL injection and parameters. It left me really msytified. As I understand it, you replace what the user enters with something else.
But if you need what they enter, and have no way of knowing what it is, how can you change it to something else that works?
I've never used cmd.Parameters.AddWithValue so will have to figure what that does. But why care about datatypes? If the wrong thing is entered, it probably won't match and they'll get no result.
You guys know so much more than me, that my assumptions must be wrong or flawed, but I don't see where yet !
My main issue is handling the wrong number of single/double quotes correctly.
Please expand a bit ?
It sounds like you are suffering form Information overload.
Read Leary222 post just above this one for a nice easy clear explanation on Parameters
Try and get that bit down first forget the rest, just read Leary222's post a few times and try it out!!!
It's a mystery what the "@" is
e.g. "GivenName = @GivenName" deosn't mean a thing.
Don't over think this, @GivenName is just a parameters placeholder for your data, nothing more!!!
When you do this -
Code:
Parameters.AddWithValue("@Name","Dave")
You are saying @Name equals 'Dave', you can then use @Name in your SQL statement instead of the actual string 'Dave'.
Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you
-
May 28th, 2015, 04:30 AM
#19
Thread Starter
Frenzied Member
Re: SQL Building
Thanks Leary, that's a nice rundown . So whatever is entered by the user is enclosed in quotes and thus cannot be interpreted
as an instruction that might be malicious? Is that right ?
The way I'm building up my sql statement is
Code:
If Not txtArtist.Text = String.Empty Then
AddBit = "(Artist ? '~" & txtArtist.Text & "~') And "
If cboArtist.Text = "is" Then
AddBit = Replace(AddBit, "?", "=")
AddBit = Replace(AddBit, "~", "")
ElseIf cboArtist.Text = "contains" Then
AddBit = Replace(AddBit, "?", "Like")
AddBit = Replace(AddBit, "~", "%")
End If
buildstring = buildstring & AddBit
End If
A combo box offers "is" or "contains" which has to be selected before the text box txtArtist is enabled.
That structure is repeated for whatever criteria is input by the user (altered for dates or numbers).
I had a go at changing line 2 to (trial and error at this stage!)
AddBit = "(Artist ? '~@name~') And "
But attempts to apply Parameters.AddWithValue("@Name", txtArtist.Text) gave error 'Parameters is not declared...'
Also if the user enters don"t wouldn't that become "don"t" and (unless I'm wrong) cause an error ?
I've been using single quotes (easier to type) and wondering about replacing them with Chr(34) when the statement is complete.
Either way, does it not mean escaping with double characters is required ?
-
May 28th, 2015, 04:34 AM
#20
Thread Starter
Frenzied Member
Re: SQL Building
Hi NeedSomeAnswers, thanks for input... I'm getting some myself now! Not out of the woods but can see light
Cheers, ABB
-
May 28th, 2015, 04:58 AM
#21
Re: SQL Building
There's still a bit of confusion here. First up, you should probably understand that when you are using a parameterised query, that is not working out how to do the escaping of the string for you or doing any sort of replacement of the SQL in your code. The query is passed as-is to the SQL engine which parses the query, and sees that there is a parameter in it. That is how it can contribute to a defence against SQLi. SQLi is all about escaping from the data context into the query context: there is some text that you intend to only be data, but if it is crafted in a certain way it can trick the SQL parser into thinking it is part of the query and thus alter it. With parameters the user-entered data is never considered by the parser and therefore it can't escape the data context.
So, now we are looking at your specific criteria you want to build up dynamically. for the outer-most part of this, jmc has already shown you one approach that works for conditionally including a criteria in post #5. So you'll want something that looks like:
Code:
Dim criteria As New List(Of String)
If Not txtArtist.Text = String.Empty Then
criteria.Add(*** something ***)
command.Parameters.Add("@ArtistName", txtArtist.Text)
End If
The next issue is that you want two different criteria depending on what the combo box is. Not a problem though, you should be able to apply the inner bit in two different ways:
Code:
Dim criteria As New List(Of String)
If Not txtArtist.Text = String.Empty Then
If cboArtist.Text = "is" Then
criteria.Add(*** something ***)
command.Parameters.Add("@ArtistName", txtArtist.Text)
ElseIf cboArtist.Text = "contains" Then
criteria.Add(*** something ***)
command.Parameters.Add("@ArtistName", txtArtist.Text)
End If
End If
Okay,but what are we doing in each case? The first is easy, it's a normal straight comparison:
Code:
Dim criteria As New List(Of String)
If Not txtArtist.Text = String.Empty Then
If cboArtist.Text = "is" Then
criteria.Add("Artist = @ArtistName")
command.Parameters.Add("@ArtistName", txtArtist.Text)
ElseIf cboArtist.Text = "contains" Then
criteria.Add(*** something ***)
command.Parameters.Add("@ArtistName", txtArtist.Text)
End If
End If
The next needs slightly careful thinking about. Remember that the parameter contains the entire value of data. The wildcards in a LIKE are definitely part of the data, so we can't specify them in the query string, they must come from the parameter, but equally we don't want the user to be typing them. The answer, of course is that the parameter value doesn't need to be exactly what the user entered - we can add the % characters before populating the value:
Code:
Dim criteria As New List(Of String)
If Not txtArtist.Text = String.Empty Then
If cboArtist.Text = "is" Then
criteria.Add("Artist = @ArtistName")
command.Parameters.Add("@ArtistName", txtArtist.Text)
ElseIf cboArtist.Text = "contains" Then
criteria.Add("Artist LIKE @ArtistName")
command.Parameters.Add("@ArtistName", "%" + txtArtist.Text + "%")
End If
End If
Okay, fine? Well, not quite. We've avoided the ability for user input to escape into the query context, but they can still escape into the LIKE context. For this you'd need to escape the input string, but as it's a much more constrained language it's somewhat easier and therefore safer, and if you get it wrong you won't endanger your database in any way, just get some odd results from your query. A quick search yields this page which may help, depending on what your database system is: http://stackoverflow.com/questions/2...ike-expression
-
May 28th, 2015, 05:56 AM
#22
Re: SQL Building
But attempts to apply Parameters.AddWithValue("@Name", txtArtist.Text) gave error 'Parameters is not declared...'
Thats Becasue Parameters are part of your Command Object so you should be adding them like this -
Code:
Command.Parameters.AddWithValue("@Name", txtArtist.Text)
Lets go back to ddays example right back in post 2 (as imho its a nice simple example) - and i will try and annotate it a bit more
Code:
Private Sub InsertUser(ByVal user As String)
Dim connection As SqlConnection = Nothing '******** The Connection Object ***********
Try
'Open the connection
connection = New SqlConnection("My Connection String Here") '******** Set a New Instance of the Connection ***********
'******** Create a Command Object and pass it an SQL statement (substituting your User value for a parameter @user ) ********
Using cmd As SqlCommand = New SqlCommand("INSERT INTO [MyTable] ([UserColumn]) VALUES (@user);", connection)
''******** Add the Value to the Parameter, note we are adding to the Parameters collection of cmd - which is the command) ********
cmd.Parameters.AddWithValue("@user", user)
'Open up the connection
connection.Open()
'Execute the Command
cmd.ExecuteNonQuery()
'Close the connection
connection.Close()
End Using
Catch ex As Exception
'Display the error
MessageBox.Show(ex.Message)
Finally
'Close the connection if it was left open
If connection IsNot Nothing Then
If connection.State = ConnectionState.Open Then
connection.Close()
End If
connection.Dispose()
End If
End Try
End Sub
Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you
-
May 28th, 2015, 04:55 PM
#23
Hyperactive Member
Re: SQL Building
Originally Posted by AlexanderBB
Thanks Leary, that's a nice rundown . So whatever is entered by the user is enclosed in quotes and thus cannot be interpreted
as an instruction that might be malicious? Is that right ?
No, the value passed isn't encapsulated in quotes, as Evil Giraffe said its simply taken out of context; apologies if i was unclear on this.
The issue with SQL injection is that the command which is sent to the database engine could potentially be modified in order to produce undesired results, nothing to do with quotes being wrapped around the data. By using parameters you are separating the values your user puts in from the command string its self and thus not allowing the quotes to affect the command issued to the database.
By using parameters the value which you pass is taken literally as just a value and not part of the command string. Therefore should your user put in a value which contains quotes or any other special characters it would not affect the query.
Originally Posted by AlexanderBB
But attempts to apply Parameters.AddWithValue("@Name", txtArtist.Text) gave error 'Parameters is not declared...'
Apologies this may be my fault for missing the Class name from my example. But as identified by needsomeanswers it should be Command.Parameters.AddWithValue("@Name", txtArtist.Text)
Originally Posted by AlexanderBB
Also if the user enters don"t wouldn't that become "don"t" and (unless I'm wrong) cause an error ?
I've been using single quotes (easier to type) and wondering about replacing them with Chr(34) when the statement is complete.
Either way, does it not mean escaping with double characters is required ?
No when a user enters any special characters and parameters have been used to pass the values to the database engine they are included as part of the data and do not require any single or double quotes.
For Example when using parameters
if txt.Artist.Value = "Don't" then the Value which would be given to the query would be "Don't"
if you didn't use parameters in this situation you would get an SQL syntax error and the associated exception would be raised to your application.
in conclusion, if you use parameters quotes wont be required in your SQL command and if the user enters them they will be passed to your database without any issues and a reduced chance of SQL injection.
-
May 28th, 2015, 08:11 PM
#24
Thread Starter
Frenzied Member
Re: SQL Building
Firstly, thanks very much everyone for going to so much trouble explaining this me. I can see I had lots of misconceptions. Not surprising as I'm self taught (or is it self- not taught?).
I can't explain how I missed the "command" bit as it's blindingly obvious now. Then once it finally sunk in this eliminates any issues with quotes that clinched it as my data is full of them. Evil G said "can escape into the query context, but they can still escape into the LIKE context" so I'll try those two separately. "Like" looks the more complex, especially reading that Stack Overflow link !
I've also got hard copies of everything to more easily refer back. So here we go!
-
May 29th, 2015, 12:31 AM
#25
Thread Starter
Frenzied Member
Re: SQL Building
Much hair loss later... OK. My design was going to be, build the query in one routine and send it to another which opens the connection
and send the result to a dgv control.
But this isn't going to work because the command.parameter thing requires the connection to be already established.
So the connection is established then the string built up? Yes ?
Which must all be in the same subroutine or else the textbox values would be out of scope. I had already set up a connection/dgv thing on it's own so my mindset was locked on doing it how I first envisaged. The code for this is (error handling to come later)
Code:
Public Sub SQLToGrid(ByVal someSQLQuery As String)
Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Backend & ";Persist Security Info=False;")
Dim UserQuery As New OleDbDataAdapter(someSQLQuery, conn)
Dim UserRet As New DataTable
UserQuery.Fill(UserRet)
With frmDGV.DataGridView2
.DataSource = UserRet
End With
frmDGV.DataGridView2.Visible = True
frmDGV.Show()
End Sub
I can move all this into the same place as everything else.
I will need an equivalent of 'Dim command As New SqlCommand("SELECT * FROM MyTable", connection)'
then add command. parameter.... as below .
Although I'm sure the same line needn't be repeated 3 times, one would do (in this example)
Code:
'PEAK
If Not txtPeaked.Text = String.Empty Then
If cboPeaked.Text = "at" Then
criteria.Add("Val(high) = @High")
command.Parameters.AddWithValue("@High",txtPeaked.Text )
ElseIf cboPeaked.Text = "lower than" Then
criteria.Add("Val(high) < @High")
command.Parameters.AddWithValue("@High",txtPeaked.Text )
ElseIf cboPeaked.Text = "higher than" Then
criteria.Add("Val(high) > @High")
command.Parameters.AddWithValue("@High",txtPeaked.Text )
End If
End If
Once I can see the full string after all the processing, these individual bits will be much easier to understand.
-
May 29th, 2015, 12:47 AM
#26
Thread Starter
Frenzied Member
Re: SQL Building
> will need an equivalent of 'Dim command As New SqlCommand("SELECT * FROM MyTable", connection)'
I think it's Dim command As New OleDbCommand(Selectstring, conn) . select string is my half built sql string and i'm getting Parameters from iltellisense
-
May 29th, 2015, 01:08 AM
#27
Thread Starter
Frenzied Member
Re: SQL Building
Well this was unexpected! No exceptions but the resultant string has not got the values, but the "@" text e.g.
WHERE Year = @Year AND Val(high) = @High AND Val(CH) = @CH AND Title = @Title AND Artist = @Artist
That ain't right, is it ?
-
May 29th, 2015, 04:20 AM
#28
Re: SQL Building
That is right. The sql string you pass to SqlServer will have the place holders (e.g. @Year) in it. You will also be passing some parameters which specify the values for those place holders.
SqlServer itself will take those parameters and use those values when it executes the sql string. This happens in the database, not in the client program.
So for the example above you should be passing 5 parameters. One each for @Year, @High, @CH, @Title and @Artist.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
May 29th, 2015, 11:23 PM
#29
Thread Starter
Frenzied Member
Re: SQL Building
Hi FD, thanks for that and I never expected that answer! It kind of helps some of the previous explanations
except that after 2 days I still can't get it to work. And I'm conscious of everyone getting sick and tired
of me and these crazy questions to them. Otherwise I give up and revert to what I know works, but
maybe one last try ?
Essentially are these 2 the same thing ?
"Select Year from tblMyTest WHERE Year = '1961'"
"Select Year from tblMyTest WHERE Year = @Year"
And Year is defined in the code below (with contents of cboYear confirmed as '1961')
If that's all correct then something must be wrong below as the parameter ones results in 'No value given for one or more required parameters.'
and the other is OK.
Code:
Private Sub Test999()
Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Backend & ";Persist Security Info=False;")
Dim Selectstring = "Select Year from tblMyTest "
Dim command As New OleDbCommand(Selectstring, conn)
Dim criteria As New List(Of String)
If Not cboYear.Text = String.Empty Then
criteria.Add("Year = @Year")
command.Parameters.AddWithValue("@High", cboYear.Text)
End If
If criteria.Count > 0 Then
command.CommandText &= " WHERE " & String.Join(" AND ", criteria)
End If
Dim t As String = Replace(command.CommandText, "@Year", "'1961'")
' Dim UserQuery As New OleDbDataAdapter(command.CommandText, conn) '<<< GOES TO ERROR
Dim UserQuery As New OleDbDataAdapter(t, conn) '<<< IS OK
Dim UserRet As New DataTable
UserQuery.Fill(UserRet)
With frmDGV.DataGridView2
.DataSource = UserRet
End With
frmDGV.DataGridView2.Visible = True
frmDGV.Show()
End Sub
-
May 30th, 2015, 01:43 AM
#30
Re: SQL Building
I would look very carefully at this block if I were you:
Code:
If Not cboYear.Text = String.Empty Then
criteria.Add("Year = @Year")
command.Parameters.AddWithValue("@High", cboYear.Text)
End If
-
May 30th, 2015, 03:28 AM
#31
Thread Starter
Frenzied Member
Re: SQL Building
I have '@High' instead of '@Year' ?
That was a silly mistake I made but even with that corrected I'm still getting "No Value Given...'
Is there anything else wrong there ?
Can I ask you about this line in your msg 21 ?
command.Parameters.Add("@ArtistName", "%" + txtArtist.Text + "%")
When i enter that I get a message about the command being obsolete ?
Also, is '+" interchangable with '&' or different altogether?
-
May 30th, 2015, 03:33 AM
#32
Re: SQL Building
'+' was free-coded in the reply box. Whatever VB's string concatenation operator is. (I'm a C#er by default, sorry!)
-
May 30th, 2015, 04:43 AM
#33
Thread Starter
Frenzied Member
Re: SQL Building
Both + and & brought up the same message, which I'm pretty sure was telling me to use "AddWithValue" syntax but I can't quite
test it yet ! (But this darn thing has to work in the end!)
Those examples in your msg 21 esp. about like were great, thanks
I'm just wondering if the problem is oledb connection as almost all of the examples here use sql.. to use sql I need to move VB into
a shorter path as I get an error about data source > 127.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|