Programming Help

Web Development



What's Here?

  • Members: 136,576
  • Replies: 479,705
  • Topics: 74,760
  • Snippets: 2,567
  • Tutorials: 677
  • Total Online: 1,879
  • Members: 90
  • Guests: 1,789

Welcome to Dream.In.Code
Getting VB Help is Easy!

Join 136,576 VB Programmers for FREE! Get instant access to thousands of VB experts, tutorials, code snippets, and more! There are 1,879 people online right now. Registration is fast and FREE... Join Now!




Passing 2 Variables in a Select statement (VB6)

2 Pages V  1 2 >  
 Reply to this topicStart new topic

Passing 2 Variables in a Select statement (VB6)
Sicle
3 Oct, 2008 - 08:09 PM
Post #1

New D.I.C Head
*

Joined: 3 Oct, 2008
Posts: 5

Private Sub Command1_Click()

Hi can someone please take a look at my code below- I'm trying to pass two parameters to an Access database through the SQL statement below, but I'm getting the runtime error 13 -mis match type. I've never wrtten a select statement using AND with two variables before. I'm sure I'm just screwing up the " 's.

Thx, B




Dim cnntest As New ADODB.Connection
Dim rstest As New ADODB.Recordset


strquarter = cboquarter.Text

intyear = cboyear.Text


cnntest.Open "DSN=ADJUST"



Set rstest = cnntest.Execute("SELECT * FROM txtquotafile WHERE numyear =" & intyear And txtquarter = strquarter)



Set frmfind.MSHFlexGrid2.DataSource = rstest
frmfind.Show

End Sub
User is offlineProfile CardPM
+Quote Post

akhileshbc
RE: Passing 2 Variables In A Select Statement (VB6)
3 Oct, 2008 - 08:24 PM
Post #2

D.I.C Head
Group Icon

Joined: 26 Sep, 2008
Posts: 177



Thanked: 3 times
Dream Kudos: 50
My Contributions
Will you try this..??? smile.gif

Set rstest = cnntest.Execute("SELECT * FROM txtquotafile WHERE numyear = " & intyear & " And txtquarter = " & strquarter & " ")


Also try these lines of code:

dim strquarter, intyear as integer
strquarter = cint(cboquarter.Text)

intyear = cint(cboyear.Text)
User is offlineProfile CardPM
+Quote Post

Sicle
RE: Passing 2 Variables In A Select Statement (VB6)
3 Oct, 2008 - 08:26 PM
Post #3

New D.I.C Head
*

Joined: 3 Oct, 2008
Posts: 5
QUOTE(akhileshbc @ 3 Oct, 2008 - 09:19 PM) *

Will you try this..??? smile.gif

Set rstest = cnntest.Execute("SELECT * FROM txtquotafile WHERE numyear = " & intyear & " And txtquarter = " & strquarter & " ")



I inserted your statement and received the Access ODBC driver error "to few parameters expected 1"


User is offlineProfile CardPM
+Quote Post

akhileshbc
RE: Passing 2 Variables In A Select Statement (VB6)
3 Oct, 2008 - 08:45 PM
Post #4

D.I.C Head
Group Icon

Joined: 26 Sep, 2008
Posts: 177



Thanked: 3 times
Dream Kudos: 50
My Contributions
Try this:

Set rstest = cnntest.Execute("SELECT * FROM txtquotafile WHERE numyear = " & intyear & " And txtquarter = " & strquarter

Do you have that field names numyear and txtquarter ?
What data type are you using for that fields in your database. Is it number or text.?

I havent used this Execute method. I usually uses the .open method smile.gif
User is offlineProfile CardPM
+Quote Post

thava
RE: Passing 2 Variables In A Select Statement (VB6)
3 Oct, 2008 - 08:51 PM
Post #5

D.I.C Regular
Group Icon

Joined: 17 Apr, 2007
Posts: 450



Thanked: 18 times
Dream Kudos: 50
My Contributions
try this
vb

Set rstest = cnntest.Execute("SELECT * FROM txtquotafile WHERE numyear = " & intyear & " And txtquarter = '" & strquarter & "' ")
User is offlineProfile CardPM
+Quote Post

Sicle
RE: Passing 2 Variables In A Select Statement (VB6)
3 Oct, 2008 - 08:52 PM
Post #6

New D.I.C Head
*

Joined: 3 Oct, 2008
Posts: 5
QUOTE(akhileshbc @ 3 Oct, 2008 - 09:45 PM) *

Try this:

Set rstest = cnntest.Execute("SELECT * FROM txtquotafile WHERE numyear = " & intyear & " And txtquarter = " & strquarter

Do you have that field names numyear and txtquarter ?
What data type are you using for that fields in your database. Is it number or text.?

I havent used this Execute method. I usually uses the .open method smile.gif



Same error: send me how you would write it using the open method, and maybe I'll just change it.

Txtquarter (database text field)
strquarter (variable as string)
intyear (variable as integer)
numyear (database number field)

I can get the intyear & numyear to work - its the txtquarter & strquarter that don't seem to work
User is offlineProfile CardPM
+Quote Post

thava
RE: Passing 2 Variables In A Select Statement (VB6)
3 Oct, 2008 - 08:54 PM
Post #7

D.I.C Regular
Group Icon

Joined: 17 Apr, 2007
Posts: 450



Thanked: 18 times
Dream Kudos: 50
My Contributions
did you use that???
is it work or not

This post has been edited by thava: 3 Oct, 2008 - 09:07 PM
User is offlineProfile CardPM
+Quote Post

Sicle
RE: Passing 2 Variables In A Select Statement (VB6)
3 Oct, 2008 - 09:11 PM
Post #8

New D.I.C Head
*

Joined: 3 Oct, 2008
Posts: 5
QUOTE(thava @ 3 Oct, 2008 - 09:54 PM) *

did you use that???
is it work or not



No still not working
User is offlineProfile CardPM
+Quote Post

Sicle
RE: Passing 2 Variables In A Select Statement (VB6)
3 Oct, 2008 - 09:19 PM
Post #9

New D.I.C Head
*

Joined: 3 Oct, 2008
Posts: 5
QUOTE(Sicle @ 3 Oct, 2008 - 10:11 PM) *

QUOTE(thava @ 3 Oct, 2008 - 09:54 PM) *

did you use that???
is it work or not



No still not working



It worked!!!

this code worked...thank you all for your help!!

Set rstest = cnntest.Execute("SELECT * FROM txtquotafile WHERE numyear = " & intyear & " And txtquarter = '" & strquarter & "' ")

User is offlineProfile CardPM
+Quote Post

thava
RE: Passing 2 Variables In A Select Statement (VB6)
3 Oct, 2008 - 09:23 PM
Post #10

D.I.C Regular
Group Icon

Joined: 17 Apr, 2007
Posts: 450



Thanked: 18 times
Dream Kudos: 50
My Contributions
if you want to thank just click the this post was helpful
User is offlineProfile CardPM
+Quote Post

akhileshbc
RE: Passing 2 Variables In A Select Statement (VB6)
3 Oct, 2008 - 11:28 PM
Post #11

D.I.C Head
Group Icon

Joined: 26 Sep, 2008
Posts: 177



Thanked: 3 times
Dream Kudos: 50
My Contributions
"Txtquarter (database text field)
strquarter (variable as string)
intyear (variable as integer)
numyear (database number field)"

That's why I asked you which datatype you are using. Because for numbers, you can just pass them. For text, you have to include the single quotes smile.gif
User is offlineProfile CardPM
+Quote Post

bantay
RE: Passing 2 Variables In A Select Statement (VB6)
12 Oct, 2008 - 12:55 PM
Post #12

New D.I.C Head
*

Joined: 12 Oct, 2008
Posts: 5
i have the same problem as his

here's my code

CODE
adofilter.recordsource = "Select * from BookLoan where BorrowerID = '" & Trim(txtSearch.Text) & "' and Position = '" & lblPosition.Caption & "' ) ORDER BY DateBorrowed "


i am planning to have this as the connection of my datagrid..

but all i get is an adodc error
User is offlineProfile CardPM
+Quote Post

2 Pages V  1 2 >
Fast ReplyReply to this topicStart new topic
Time is now: 12/3/08 12:11AM
Invision Power Board v2.1.7 © 2008  IPS, Inc.
Licensed to: MediaGroup1

Live VB Help!

VB Tutorials

Reference Sheets

VB Snippets

DIC Chatroom

Bye Bye Ads

Monthly Drawing

Thumb Drive

Top Contributors

Top 10 Kudos This Month