Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
EricBHK
Starting Member
18 Posts |
 Posted - 2012-08-16 : 04:52:29
|
| Hello,I want to split up following sql statement over two lines in VS 2010 :Sql = "DELETE FROM Keywords10 WHERE Keyword = '" & ComboBox1.Text & "' OR Keyword = '" & ComboBox2.Text & "'after the OR as follows :Sql = "DELETE FROM Keywords10 WHERE Keyword = '" & ComboBox1.Text & "' OR_Keyword = '" & ComboBox2.Text & "'However, the underscore after the OR doesn't seem to work (all examples I have found so far use the underscore and strat next line with " )what is the proper way ? |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-08-16 : 05:01:46
|
| It's inside quotes so is treated as a string. TrySql = "DELETE FROM Keywords10 WHERE Keyword = '" & ComboBox1.Text &_ "' OR Keyword = '" & ComboBox2.Text & "'==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2012-08-16 : 05:03:30
|
This is the line you wantSQL = "DELETE FROM dbo.Keywords10 WHERE Keyword IN ('" & ComboText1.Text & "', '" & Combotext2.Text & "');"You have to know where the scope of SQL is and where the scope of VB is!SQL = "DELETE FROM dbo.Keywords10"_ & " WHERE Keyword IN ('"_ & ComboText1.Text_ & "', '"_ & Combotext2.Text_ & "');"
N 56°04'39.26"E 12°55'05.63" |
|
|
|
EricBHK
Starting Member
18 Posts |
Posted - 2012-08-16 : 05:50:45
|
| SwePeso,Thanks for your answer.Copy + paste did not work. I had to add an additional blank space in front of the underscore sign at the end of each line.Afterwards, works like a charm !You remark as to the scope of SQL vs. VB also pointed me to the origin of my problem and provided me with a better general understanding. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-08-16 : 06:13:48
|
you are going to sanitize those inputs right?Is there anything stopping someone from writing sql code into your combobox?ComboText1.Text If not then you could get strings like this:'; DELETE FROM Sales; --Which could really screw up your day.Transact Charlie[code]Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. http://nosqlsolution.blogspot.co.uk/ |
|
|
|
EricBHK
Starting Member
18 Posts |
Posted - 2012-08-16 : 08:13:27
|
| It seems that may optimism was somewhat premature.Situation = 10 columns in one Access table named 'Bibliothèque' (Keyword1 ... to Keyword10) and 10 Combobox1 ... to Combobox10I currently have the following sql statement (I intended to search only column Keyword1 in the Access table and next do an additonal search with 'OR' for the other columns Keyword2 ... to Keyword10) : Sql = "SELECT * FROM Bibliothèque WHERE Keyword1 IN ('" _ & ComboBox1.Text & "', '" & ComboBox2.Text & "','" & ComboBox3.Text & "','" & ComboBox4.Text & "','" & ComboBox5.Text & "', '" _ & ComboBox6.Text & "', '" & ComboBox7.Text & "','" & ComboBox8.Text & "','" & ComboBox9.Text & "', '" & ComboBox10.Text & "' ) "First, this sql already returning the record I need from my Access table IF AN ONLY IF there is a matching value in each combobox, while only having searched column Keyword1 !Secondly, a problem arises whenever I decide NOT to select an item in all of the 10 comboboxes : I then get a blank row returned in my DatagridView.I have checked the order in which the values in the comboboxes are selected : no issue there (no need to correspond to Access table) as long as all 10 comboboxes get a value selected.Anyone any ideas on how to improve my sql string ?Shouldn't be too hard for someone with some experience, but I just started studying SQL last weekend , so .....Thanks in advance ! |
|
|
|
|
|
|
|
|
long sql statement