Programming Help

Web Development



What's Here?

  • Members: 131,700
  • Replies: 469,508
  • Topics: 72,770
  • Snippets: 2,534
  • Tutorials: 664
  • Total Online: 2,498
  • Members: 86
  • Guests: 2,412

Who's Online?

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

Join 131,700 VB Programmers for FREE! Get instant access to thousands of VB experts, tutorials, code snippets, and more! There are 2,498 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
post 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

Go to the top of the page

akhileshbc
post 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

Go to the top of the page

Sicle
post 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

Go to the top of the page

akhileshbc
post 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

Go to the top of the page

thava
post 3 Oct, 2008 - 08:51 PM
Post #5


D.I.C Regular

Group Icon
Joined: 17 Apr, 2007
Posts: 423



Thanked 17 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

Go to the top of the page

Sicle
post 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

Go to the top of the page

thava
post 3 Oct, 2008 - 08:54 PM
Post #7


D.I.C Regular

Group Icon
Joined: 17 Apr, 2007
Posts: 423



Thanked 17 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

Go to the top of the page

Sicle
post 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

Go to the top of the page

Sicle
post 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

Go to the top of the page

thava
post 3 Oct, 2008 - 09:23 PM
Post #10


D.I.C Regular

Group Icon
Joined: 17 Apr, 2007
Posts: 423



Thanked 17 times

Dream Kudos: 50
My Contributions
if you want to thank just click the this post was helpful
User is offlineProfile CardPM

Go to the top of the page

akhileshbc
post 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

Go to the top of the page

bantay
post 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

Go to the top of the page

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

Live VB Help!

VB Tutorials

Reference Sheets

VB Snippets

Bye Bye Ads

Free DIC T-Shirt

T-Shirt Example

Related Sites

Monthly Drawing

Thumb Drive

Partners

Top Contributors

Top 10 Kudos This Month