VB6 SQL 'WHERE' statement problem

[fairysdad]

Hi

Sorry that this may seem a little spammy for a first post, but I am literally tearing my hear out here (well, I would be were it not for the fact that I'm going very bald already...)

I've done an Internet search, and have seen that this problem is quite a common one, but nowhere does it give an option that seems to show what the fix is.

My query comes with working with databases and SQL. Now, usually I don't bother using databases with VB, and if this case is anything to go by that was quite a wise thing to do. But this is a database I'm tapping into for another reason. I do use SQL when I'm doing web stuff in PHP, but not VB.

Anyhoos.

I have taken some source code from here: http://www.timesheetsmts.com/adotutorial.htm.

The first connection string works which is a simple 'SELECT * FROM table1;' command.

The second one doesn't work which is also a simple 'SELECT * FROM table2 WHERE id BETWEEN 0 AND 29;' command.

I do seem to have put this down to the WHERE statement which is knocking the problem out - error -2147217904 (80040e10): No value given for one or more required parameters.

The error is occuring on the rstRecordSet.Open cmdCommand line, but I know it's not that line that is causing problems fully as I use exactly the same line of code elsewhere (for the first connection string) which works.

I have tried putting the table and 'column' names in square brackets, putting the 'id BETWEEN...' into normal parentheses, taking out the fact that originally the 0 and 29 were put in by VB (the code currently is hardcoded to put those values in), putting 'table2' and 'id' in inverted commas, as with the 0 and 29 (despite them being integers in both the code and the Access database) - all things that my trawl through the Internet has come up with - I've also tried only putting in 'WHERE id = 1' (therefore only returning one result - this is the first time I've attempted to use the BETWEEN statement, probably a not great installation to start with! - and the only thing that stops that error coming up is to truncate the statement before the 'WHERE' - which leads me to think that it is there WHERE that is causing the problem.

I am at my wits end here with this now - all I want to do is get this to work and I cannot think of anything that is causing it not to. The SQL statement is correctly formed and it's just not working... and I'm rather pi... well I won't say that given that it's my first post and don't want to make such an impression on it, but hopefully you understand my angst!!!

Please help me!!! (I don't often say that...)

Thanks in advance

Fairy's Dad

[viperbyte]

Why did you write that this first post of yours may sound a little spammy? I didn't get that impression at all. But it was a little wordy. Cobal style ha ha. The only reason i would have suspected anything in the least bit that the post sounded spammy was that the link you provided has a Make Donation button. But the post didn't sound spammy at all to me. I would say that it more sounded like it didn't came from a visual basic programmer. But hey that's just my impression. If you would post the code I would be happy to take a look at the problem. Cheers.

[vb5prgrmr]

I can hear what you are saying but pulling out your hearing has nothing to do with your hair and going bald...

Yes, please post your code...



Good Luck

[fairysdad]

Quite interesting that you think that I'm not a VB programmer as I have been programming in VB for a few years now (not professionally though), I just avoid using databases as I remember when I was doing it at college (UK college so equivalent to the last couple of years of US High Schools) that it was a pain in the arse so always avoided it, and now I know why! I am quite a wordy person really... (and on reflection, I think I meant to say 'trolly' rather than 'spammy'!)

I can't see why posting the code will help much as the working and not working bits of code are identical except the SQL statement, which I did give above, and are lifted directly from the site I gave (yes, I know, I know, I will reference it and tidy it up but I want to get it working first!).

[code=VB]Private Sub LoadDatabase()

'Define the three objects that we need,
' A Connection Object - connects to our data source
' A Command Object - defines what data to get from the data source
' A RecordSet Object - stores the data we get from our data source

' Dim conConnection As New ADODB.Connection
' Dim cmdCommand As New ADODB.Command
' Dim rstRecordSet As New ADODB.Recordset

' VARIABLE DEFINITION AT TOP OF FORM AS VARS USED IN PROJECT NOT JUST PROCEDUERE

'Defines the connection string for the Connection. Here we have used fields
'Provider, Data Source and Mode to assign values to the properties
' conConnection.Provider and conConnection.Mode

conConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DatabaseName.mdb;Mode=Read|Write" 'Name edited out for these purposes


'Define the location of the cursor engine, in this case we are opening an Access database
'and adUseClient is our only choice.

conConnection.CursorLocation = adUseClient


'Opens our connection using the password "Admin" to access the database. If there was no password
'protection on the database this field could be left out.

conConnection.Open

End Sub

Private Sub LoadPalData()

'Defines our command object

' .ActiveConnection tells the command to use our newly created command object.
' .CommandText tells the command how to get the data, in this case the command
' will evaluate the text as an SQL string and we will return all
' records from a table called tabTestTable
' .CommandType tells the command to evaluate the .CommandText property as an SQL string.

With cmdCommand
.ActiveConnection = conConnection
.CommandText = "SELECT * FROM Pallettes ;" ' THIS SQL DOES WORK
.CommandType = adCmdText
End With

'Defines our RecordSet object.

' .CursorType sets a static cursor, the only choice for a client side cursor
' .CursorLocation sets a client side cursor, the only choice for an Access database
' .LockType sets an optimistic lock type
' .Open executes the cmdCommand object against the data source and stores the
' returned records in our RecordSet object.

With rstRecordSet
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open cmdCommand
End With

'Firstly test to see if any records have been returned, if some have been returned then
'the .EOF property of the RecordSet will be false, if none have been returned then the
'property will be true.

If rstRecordSet.EOF = False Then

'Move to the first record

rstRecordSet.MoveFirst

'Lets move through the records one at a time until we reach the last record
'and print out the values of each field

Dim whatho As Integer

whatho = 1

Do

'Access the field values using the fields collection and print them to a message box.
'In this case I do not know what you might call the columns in your database so this
'is the safest way to do it. If I did know the names of the columns in your table
'and they were called "Column1" and "Column2" I could reference their values using:

' rstRecordSet!Column1
' rstRecordSet!Column2


'MsgBox "Record " & rstRecordSet.AbsolutePosition & " " & _
' rstRecordSet.Fields(0).Name & "=" & rstRecordSet.Fields(0) & " " & _
' rstRecordSet.Fields(1).Name & "=" & rstRecordSet.Fields(1)

' THIS IS MY OWN CODE - SHOW EACH OF THE BANK NAMES FOR 1 - 6 IN THE BUTTONS
' THIS WILL CHANGE TO SHOW USER SPECIFIED BANKS
' WILL ALSO NEED TO PUT CODE IN TO DEAL WITH THE AMERSAND CHARACTER

If Not whatho > cmdBank.UBound Then _
cmdBank(whatho).Caption = rstRecordSet!PalTitle.Value

whatho = whatho + 1

'Move to the next record

rstRecordSet.MoveNext
Loop Until rstRecordSet.EOF = True

'Add a new record

' With rstRecordSet
' .AddNew
' .Fields(0) = "New"
' .Fields(1) = "Record"
' .Update
' End With

'Move back to the first record and delete it

' rstRecordSet.MoveFirst
' rstRecordSet.Delete
' rstRecordSet.Update


'Close the recordset

rstRecordSet.Close
Else
MsgBox "No records were returned using the query " & cmdCommand.CommandText
End If

'Close the connection

' conConnection.Close

'Release your variable references

' Set conConnection = Nothing
' Set cmdCommand = Nothing
' Set rstRecordSet = Nothing
End Sub

Private Sub LoadSongs(Optional ButtonScreen As Integer = 1)
'Defines our command object

' .ActiveConnection tells the command to use our newly created command object.
' .CommandText tells the command how to get the data, in this case the command
' will evaluate the text as an SQL string and we will return all
' records from a table called tabTestTable
' .CommandType tells the command to evaluate the .CommandText property as an SQL string.

Dim ButtonMax As Integer
Dim ButtonMin As Integer

Dim SQLStatementWoo As String

ButtonMin = ((ButtonScreen - 1) * 30)
ButtonMax = ButtonMin + 29

With cmdCommand
.ActiveConnection = conConnection
' .CommandText =
SQLStatementWoo = "SELECT * FROM PalleteData WHERE (ButtonPosit BETWEEN 0 AND 29);" '" & ButtonMin & " AND " & ButtonMax & ";" ' THIS SQL DOESN'T WORK
.CommandText = SQLStatementWoo
MsgBox SQLStatementWoo
.CommandType = adCmdText
End With

'Defines our RecordSet object.

' .CursorType sets a static cursor, the only choice for a client side cursor
' .CursorLocation sets a client side cursor, the only choice for an Access database
' .LockType sets an optimistic lock type
' .Open executes the cmdCommand object against the data source and stores the
' returned records in our RecordSet object.

With rstRecordSet
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open cmdCommand
End With

'Firstly test to see if any records have been returned, if some have been returned then
'the .EOF property of the RecordSet will be false, if none have been returned then the
'property will be true.

If rstRecordSet.EOF = False Then

'Move to the first record

rstRecordSet.MoveFirst

'Lets move through the records one at a time until we reach the last record
'and print out the values of each field

Dim whatho As Integer

whatho = 1

Do

'Access the field values using the fields collection and print them to a message box.
'In this case I do not know what you might call the columns in your database so this
'is the safest way to do it. If I did know the names of the columns in your table
'and they were called "Column1" and "Column2" I could reference their values using:

' rstRecordSet!Column1
' rstRecordSet!Column2


MsgBox "Record " & rstRecordSet.AbsolutePosition & " " & _
rstRecordSet.Fields(0).Name & "=" & rstRecordSet.Fields(0) & " " & _
rstRecordSet.Fields(1).Name & "=" & rstRecordSet.Fields(1)

' THIS IS MY OWN CODE - SHOW EACH OF THE BANK NAMES FOR 1 - 6 IN THE BUTTONS
' THIS WILL CHANGE TO SHOW USER SPECIFIED BANKS
' WILL ALSO NEED TO PUT CODE IN TO DEAL WITH THE AMERSAND CHARACTER

'If Not whatho > cmdBank.UBound Then _
'cmdBank(whatho).Caption = rstRecordSet!PalTitle.Value

' whatho = whatho + 1

'Move to the next record

rstRecordSet.MoveNext
Loop Until rstRecordSet.EOF = True

'Add a new record

' With rstRecordSet
' .AddNew
' .Fields(0) = "New"
' .Fields(1) = "Record"
' .Update
' End With

'Move back to the first record and delete it

' rstRecordSet.MoveFirst
' rstRecordSet.Delete
' rstRecordSet.Update


'Close the recordset

rstRecordSet.Close
Else
MsgBox "No records were returned using the query " & cmdCommand.CommandText
End If

'Close the connection

conConnection.Close

'Release your variable references

Set conConnection = Nothing
Set cmdCommand = Nothing
Set rstRecordSet = Nothing
End Sub

[viperbyte]

I didn't say I didn't think you weren't a vb programmer. I said you sounded like you weren't a vb programmer. Mostly because all the vb people I've met who use vb use it with databases hand and hand. Sorry for undermining you or anything like that. I have to step out now I just wanted to clear that up now. I took a quick glance at the code. It's alot of stuff to wad through. Hopefully someone will have had a chance to look at it by the time i get back. Later

[viperbyte]

If the intended sql statement was a fragment from the following

SQLStatementWoo = "SELECT * FROM PalleteData WHERE (ButtonPosit BETWEEN 0 AND 29);" '" & ButtonMin & " AND " & ButtonMax & ";" ' THIS SQL DOESN'T WORK

ending at the semicolon right after the closing parenthesis after 29. Then ButtonPosit would have to be a column in the table. And that column would have data in which you are trying to select only the ones which are between 0 and 29. Is ButtonPosit a column? Or is it vb command button? If it is a vb command button then i wonder if you were trying to incorportate that into the sql statement some how. And if that was true I wonder if ButtonMin and ButtonMax are also vb command buttons and also part of the sql attempt. So that's a start on our trying to understand the scenario.

[dglienna]

Download the code, and read the article that I wrote (here).

It includes a sample program, with a database that loads into a grid without binding to the data.

Also, quote this, and you'll see how I add CODE TAGS

' Like this!

[vb5prgrmr]

Let me add to the discussion above and say that access thinks you are trying to execute multiple SQL statement because of the ; in the middle of the string. Either remove it, or the rest of the string...



Good Luck

[fairysdad]

If the intended sql statement was a fragment from the following

SQLStatementWoo = "SELECT * FROM PalleteData WHERE (ButtonPosit BETWEEN 0 AND 29);" '" & ButtonMin & " AND " & ButtonMax & ";" ' THIS SQL DOESN'T WORK

ending at the semicolon right after the closing parenthesis after 29. Then ButtonPosit would have to be a column in the table. And that column would have data in which you are trying to select only the ones which are between 0 and 29. Is ButtonPosit a column? Or is it vb command button? If it is a vb command button then i wonder if you were trying to incorportate that into the sql statement some how. And if that was true I wonder if ButtonMin and ButtonMax are also vb command buttons and also part of the sql attempt. So that's a start on our trying to understand the scenario.

Yes, 'ButtonPosit' is a column in the table (it's not my DB! I'd have named it something more obscure...!) ButtonMin and ButtonMax are variables declared in VB, hence their placement outside the quoted string and preceeded and followed by ampersands to complete the string. For the first time around, ButtonMin is 0 and ButtonMax is 29; I changed it from getting from the variables to the values themselves to try and narrow down the problem.

Let me add to the discussion above and say that access thinks you are trying to execute multiple SQL statement because of the ; in the middle of the string. Either remove it, or the rest of the string...

If you look at the code, you will see that after the first semi-colon the rest of that line is commented out (see my above note).

I have narrowed down the problem already to being the WHERE statement in the SQL code. When I finish the code so it is just 'SELECT * FROM PalletteData' the code runs with no problems. When I add the WHERE it fouls up. Even if it is just 'SELECT * FROM PalletteData WHERE id = 1'. That is where VB is having problems. It is not in my construction of the SQL statement. I have already narrowed it down to where the problem is occurring, and it is at this point. I do realise that the code I have sent in is messy (not just because I didn't use the forum's BB code correctly*) but at the moment my priority is getting this to work rather than how my code looks.

I will have a look at the link dglienna posted above when I'm back on my computer and can test it (I'm using somebody else's mac at the moment...). Also, please know that my little rant above is by no means indicative of me being ungrateful for your help here, I just felt that I needed to clarify things slightly.

*As an aside, and apologies if it has been mentioned before or elsewhere, but as I 'dived straight in' as it were, I wasn't aware of how to put code in (I guessed slightly based on my experience with other forums, alas I was wrong!) and although I imagine that there is a forum FAQ somewhere that explains it, people like me don't always look for those first. Would it not be good to have a quick guide to the BB Codes (or, as other forums do, clickable buttons to give selected text certain formatting) on the post page? Like I said, apologies if this has been mentioned before, or I'm treading on toes by asking this with my current meagre post count!

[viperbyte]

Try it this way:

SQLStatementWoo = "SELECT * FROM PalleteData WHERE (ButtonPosit BETWEEN '0' AND '29');

I just put single quotes around 0 and 29. This should do it.

[dglienna]

alas I was wrong!) and although I imagine that there is a forum FAQ somewhere that explains it, people like me don't always look for those first. Would it not be good to have a quick guide to the BB Codes (or, as other forums do, clickable buttons to give selected text certain formatting) on the post page? Like I said, apologies if this has been mentioned before, or I'm treading on toes by asking this with my current meagre post count!

That'd be in the AUP (links to FAQ) that you agreed to when you joined....

[fairysdad]

Try it this way:

SQLStatementWoo = "SELECT * FROM PalleteData WHERE (ButtonPosit BETWEEN '0' AND '29');

I just put single quotes around 0 and 29. This should do it.

That has been tried, as well as putting in the ` quotemarks which sometimes is needed in PHP (although as the numbers are stored as long integers in the DB they shouldn't be required anyway) but to no avail.

That'd be in the AUP (links to FAQ) that you agreed to when you joined....

Just checked to see/find and there isn't such an AUP shown on the register page... but I shall look at the FAQ page linked to above

[viperbyte]

Did you try it without the semicolon at the end of the string?

[stuckindcmetro]

Just out of curiousity have you checked the spelling of your table name...Pallete? Just saying that is not a word...though it may have been shortened...Pallette is a word I think even Palette is a word...and Pallet is a word...You just might want to check...often the easiest answer is the correct one...especially since your syntax is correct for a between statement!

[Leon Kennedy]

Just a thought, but have you verified that ButtonPosit is a column in the table you are going against? And also that you have it spelled the same way in both the table as well as your SQL statement?