Little database help? why isnt this working? 3

[unborn]

Im trying to get a 2 digit number from my database. Database is Clients then Table is Contracts. Im new to db and ive compiled this code with other parts of code that works in my program but isnt working now.

Public Function RevServiceDay(sdCustomer)
Dim mCn As ADODB.Connection
Dim mRs As ADODB.Recordset
Dim Rs As ADODB.Recordset
Dim sSql As String

Set mCn = New ADODB.Connection
Set mRs = New ADODB.Recordset
mCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\my work\Programs\clients.mdb"
mRs.Open "Contracts", mCn, adOpenKeyset, adLockOptimistic, adCmdTable

sSql = "SELECT ServiceDay FROM Contracts WHERE CustomerID LIKE '%" & Format(sdCustomer, "00000000") & "%'"

Set Rs = New ADODB.Recordset
Rs.Open sSql, mCn, adOpenForwardOnly, adLockReadOnly

While Not Rs.EOF And Not Rs.BOF
 testStr = GetString(Rs.Fields("ServiceDay").Value)
 Rs.MoveNext
Wend

RevServiceDay = testStr

Rs.Close
mRs.Close
mCn.Close
End Function

Basically i put label1 = RevServiceDay(txtAcctID) it it pulls the account number from a text box and needs to look up the service date for this client. When i check it the recordcount shows as "-1" so im assuming there is no records found. But i am looking in my database and it does have information in it. Ive altered the code many times trying to get it to work and i feel this is the closest way but i just cant seem to get it. Any help would be appreciated!! Thanks!

Running in circles is what I do best!

 

[SkipVought]

Hi,

Have you taken the sSQL string, after substitutions, and run it in your Access DB sucessfully? You might need terminate the string with a semicolon also.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[shakespeare5677]

If you know that the record is there and you know that the connection is proper, then try using adOpenStatic and adLockOptimistic to open the recordset. Also, not that it makes a difference here, but try using chr(34) with the LIKE statements to make "... LIKE " & chr(34) & "search criteria%" & chr(34) instead of "... LIKE 'search criteria%'". It will help you avoid problems in the future.
-Max

 

[gmmastros]

Rs.Open sSql, mCn, [red]adOpenForwardOnly[/red], adLockReadOnly

When you use a forward only recordset, you will not get a recordcount. It will always show as -1.

While Not Rs.EOF [green]And Not Rs.BOF[/green]
 testStr = GetString(Rs.Fields("ServiceDay").Value)
 Rs.MoveNext
Wend

The green highlighted code is not necessary and may resolve your problem.

Also, looking at your code, I have a couple pointers...

1) You have declared a function, but do not specify the return type.

Public Function RevServiceDay(sdCustomer) [red]As String[/red]

2) You declare an mRS but never 'really' use it. Unless there is more to the code, you could probably remove it completely. I recommend you comment that part of the code out, and then run it. If all appears to work properly without those lines, then remove them completely.

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[unborn]

Here is what i have now and still not working.

Public Function RevServiceDay(sdCustomer) As String
Dim mCn As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim sSql As String

Set mCn = New ADODB.Connection
mCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\my work\Programs\clients.mdb"

sSql = "SELECT ServiceDay FROM Contracts WHERE CustomerID LIKE " & Chr(34) & Format(sdCustomer, "00000000") & Chr(34)

Set Rs = New ADODB.Recordset
Rs.Open sSql, mCn, adOpenStatic, adLockOptimistic

While Not Rs.EOF
 testStr = GetString(Rs.Fields("ServiceDay").Value)
 Rs.MoveNext
Wend

RevServiceDay = testStr

Rs.Close
mCn.Close
End Function

The results are still blank. I have tried everything mentioned above still blank. Now i KNOW the information is in the tables but when i run a query in access it doesnt show anything either.

SELECT Contracts.ServiceDay
FROM Contracts
WHERE (((Contracts.CustomerID) Like '00000001'));

is the query string im using in Access (it sort of made it itself. But it still doesnt show anything.

ContractID	ComputerID	CustomerID	Contract	ContractCost	UsesLeft	ContractExpires	ServiceDay	ContractDate
00000001		00000001	Gold	$800.00	25		11	8/23/2005
00000002		00000002	Silver	$600.00	14		12	7/23/2005
00000020		00000001	Silver	$2.00	20		13	8/28/2005
00000021		00000001	Titanium	$2.00	40		14	8/28/2005
00000026		00000001	None	$50.00			21	8/28/2005
00000034		00000001	Silver	$2.00	38		22	8/30/2005
00000035		00000001	Gold	$500.00	34		23	8/30/2005
00000036		00000066	None	$1.00			24	8/30/2005
00000037		00000067	Silver	$1,000.00	43		32	8/31/2005
00000038	10000030	00000030	Gold	$600.00	36	9/12/2005	12	9/12/2005

**the 10000030** is where the computerID is supposed to be the customer ID is in that section on others because i didnt have anything in them**

is what i have in my tables hope that shows up half way decent :/. So it must be my query im using? is the LIKE wrong? or WHERE? -sigh.

BTW Mastro, thanks i also saw the mRs was actually used in other code that i didnt need. I thought it was pulling the full database and then the other recordset was pulling information from it and creating a temp table for it to hold the information i was searching.

I have given you all stars because i have gotten useful information from all of you and thanks! Hope you can help solve this murder mystery :/ Thanks!

Running in circles is what I do best!

 

[unborn]

WOOT!!! I found it, i did a little reading on the query i was doing and found that #'s are not supposed to use " ' " it just uses pure item heres the revised working code!!!

Public Function RevServiceDay(sdCustomer) As String
Dim mCn As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim sSql As String

Set mCn = New ADODB.Connection
mCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\my work\Programs\clients.mdb"

sSql = "SELECT ServiceDay FROM Contracts WHERE CustomerID[b] = " & Format(sdCustomer, "00000000")[/b]

Set Rs = New ADODB.Recordset
Rs.Open sSql, mCn, adOpenStatic, adLockOptimistic

While Not Rs.EOF
 testStr = GetString(Rs.Fields("ServiceDay").Value)
 Rs.MoveNext
Wend

RevServiceDay = testStr

Rs.Close
mCn.Close
End Function

I also learned that the LIKE isnt needed because im not looking for a pattern i want EXACTLY the account number im looking for so i replaced LIKE with = and dropped the ' on it and now it reads it for me!! I posted the answer so if anyone else is having this problem. Thanks again for all of your help!!!!

Running in circles is what I do best!

 

[unborn]

even better completely finished i think? any comments to improve appreciated

Public Function RevServiceDay(sdCustomer) As String
Dim mCn As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim sSql As String

[COLOR=green]'setting database and opening it[/color]
Set mCn = New ADODB.Connection
mCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\my work\Programs\clients.mdb"

[COLOR=green]'sql query to get the serviceday code[/color]
sSql = "SELECT ServiceDay FROM Contracts WHERE CustomerID = " & Format(sdCustomer, "00000000")

[COLOR=green]'opens the recordset to query the database[/color]
Set Rs = New ADODB.Recordset
Rs.Open sSql, mCn, adOpenStatic, adLockOptimistic

[COLOR=green]'sets the function to the serviceday code[/color]
RevServiceDay = GetString(Rs.Fields("ServiceDay").Value)

[COLOR=green]'closes connections[/color]
Rs.Close
mCn.Close
End Function

Running in circles is what I do best!

 

[gmmastros]

I have a couple of minor comments.

1) Parameter
You have...
Public Function RevServiceDay(sdCustomer) As String

When you don't use byref or byval when declaring your parameter, the default (in vb6) is byref. Since you are not changing the value of sdCustomer inside this function, you should specify it as a byval parameter.

You also are not specifying the data type for sdCustomer. It appears to be an integer or a long, so you should specify it as such.

Public Function RevServiceDay([red]ByVal[/red] sdCustomer [red]As Long[/red]) As String

2) Connection String
Your connection string
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\my work\Programs\clients.mdb"
has a hard coded path to the database. In many applications, the path to the database is determined by the path to the executable that you are creating. For example, if your executable is in D:\My Work\Programs then your connection string could be....

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\clients.mdb"

App.Path will return the path of the executable. By using app.path instead of hardcoding the path, you will be able to move the path of the executable and database without having to modify a single line of code.

3. Data
You are opening the recordset and then acting as though there will ALWAYS be a record. Instead, you should check to make sure a record exists.

Rs.Open sSql, mCn, adOpenStatic, adLockOptimistic

[red]If Not RS.Eof then[/red]
'sets the function to the serviceday code
RevServiceDay = GetString(Rs.Fields("ServiceDay").Value)
[red]End If[/red]

4. Error Handling
There can always be unexpected errors in any code you write. Therefore, you should have some error handling in there. There is a free tool called 'm-z tools', that you can download. You can ue this tool to insert error handling in to your code.

Now, please don't think that I am nit picking. You asked for advice, and I'm offering it. The 4 points that I made should be habit to do. By applying these priciples consistently throughout your code, your application will be better and easier to maintain.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[unborn]

Mastro thanks a bunch! Ill have to look up on byval and byref so i know exactly what they are :/

The db path, i wasnt really thinking about it but that you mention it if im pulling 40 different db connections that would be alot of code to have to recode to make it run so i will deffinately go back and set that up correctly i also need to add a password with it i just havent yet because i want easy access to my fake information.

Checking for the record is a good idea i need to stop having the mentality "oh im just setting the basics up right now" and actually code everything correctly so i dont have to go back and fix stuff. I havent programmed in like 3 years and now that my business needs a program i am relearning and learning more then what i use to know so yes i do need to get my habits back. atleast i still name my controls -laugh.

Error handelling is good idea to i usually just have a pop up show up wiuth the error number and description, should i do more? I will deffinately take a look at that program you mentioned if it will make my life easier im all for it

Thank you Mastro you have been very helpful on this thread and thank you for pushing me in the right direction!!


Running in circles is what I do best!

 

[unborn]

Oooh thanks!! This addin looks sweet i never really messed with any but this looks like it will help alot!! Any other helpful things you can suggest? thanks again!!!

Running in circles is what I do best!

 

[shakespeare5677]

> Ill have to look up on byval and byref so i know exactly what they are :/

ByVal tells the compiler to create a new variable (by copying the value of the argument) in the procedure allowing you to change its contents without affecting the original.

ByRef allows you to use a reference to the original variable in the parent procedure allowing you to change its contents from the procedure. This is often good for large variables and classes.
-Max

 

[unborn]

So easy way to remember when to use would be

ByVal is if i plan on changing the value
ByRef is if its staying the same and im referencing it

Just maken sure i understand, also should i use that for all variables im diming as well?

Dim ByVal postCount as Integer
Dim ByRef baseNum as Integer

So i would dim it like that if i was adding a count to postCount where say its in a loop i would have +1 to it. I would ten perhaps reference the baseNum to compare the value to know when to stop and since its not stoping i would use the ByRef.

This will make the code run smoother as well?

Thanks for the help!!


and Thanks!!

Running in circles is what I do best!

 

[gmmastros]

You can't declare your variables with byval or byref.

The following will give a compile error

Dim ByVal postCount as Integer
Dim ByRef baseNum as Integer

And, you have the definitions completely reversed.

ByVal should be used when you don't want the change made to the parameter and byref should be used when you do want to change the parameter.

Here's an example. Open a new vb project and put a command button on the form. Then copy/paste the code.

Option Explicit

Private Sub Command1_Click()

    Dim strTest As String
    
    strTest = "Hello world"
    
    Call ByValExample(strTest)
    
    Call MsgBox(strTest)
    
    Call ByRefExample(strTest)
    
    Call MsgBox(strTest)

End Sub

Private Sub ByRefExample(ByRef Example As String)
    
    Example = "This is byref example"
    
End Sub

Private Sub ByValExample(ByVal Example As String)
    
    Example = "This is byval example"
    
End Sub

When you run this, the first message box will display "Hellow World" because you used a byval parameter. When you change the value of a byval parameter inside the sub, the changed value does NOT get sent back to the calling subroutine (the command1_click in this example).

The second message box will display "This is a byref example". Since it's a byref parameter, when you change the value inside the subroutine, the changes get sent back to the calling subroutine.

This technique comes in handy when you want to return multiple values back from a function. Since a function can only return 1 value, you can use a subroutine with byref parameters to return multiple changes.

For example....

Public Sub ShowData

Dim ShoeSize as Double
Dim EyeColor as string
Dim PersonId as Long

Call GetInfo(PersonId, ShoeSize, EyeColor)

lblShoeSize.Caption = CStr(ShoeSize)
lblEyeColor.Caption = EyeColor

End Sub

Public Sub GetInfo(ByVal PersonId As Long, ByRef ShoeSize As Double, ByRef EyeColor as string)

ShoeSize = 9.5
EyeColor = 'Brown'

End Sub

Understand now?  If not, post back and I will attempt to clarify.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[shakespeare5677]

ByVal and ByRef are procedure argument modifiers and can be used in procedures only (so no dimming with this). So use ByVal in an argument declaration

Private Sub MyProcedure(ByRef MyArgument as String)
MyArgument = "changed string"
End Sub

if you want to be able to change the original value of the variable passed as MyArgument. In other words using

Dim x as String
x = "original string"
MyProcedure x
MsgBox x

will display "changed string", meaning that the value in x changed because x was passed by reference. If you used ByVal in MyProcedure's declaration, then x would remain the same. Hope this helps.
-Max

 

[shakespeare5677]

Damn it, George beat me to it

 

[strongm]

Strings are a really bad example to choose ...

 

[shakespeare5677]

LOL, point taken.

 

[unborn]

Ok i got ByVal will not allow it to be changed so the value will stay the same..

ByRef will allow me to change the information...

Ok so i have that now.. now i guess im confused on why i would actually want to use either one.

So i would use the ByRef so when i pass a value to the function it will allow change to it? Like with my function that changes the accountID into the ServiceDay? if i did ByVal then it would have returned the acctid instead (which was the original information passed to it) ?

I guess i would then be confused on why i would ever want to use ByVal then?


I really appreciate you helping me understand this. Got some other things i need to learn reason or whys on using them is there a good site that explains stuff in detail like this. I had programmed for like 4 years before i quit and there even then was alot of stuff i didnt understand.

Youve been a great help and once again i appreciate all your help!!


Running in circles is what I do best!

 

[gmmastros]

unborn

They way you have your function written is just fine. Other than the comments I made, I don't think you need to change it. You could have done this instead....

Public Sub RevServiceDay(byval sdCustomer As Long, Byref ServiceDays As String)
Blah, blah, blah, bunch of code...
ServiceDay = GetString(Rs.Fields("ServiceDay").Value)

End Function

With your original function, the code that calls it would be like this...

Dim strMyServiceDays as String

strMyServiceDays = RevServiceDay(sdCustomer)

With the subroutine, the code would look like...

Call RevServiceDays(sdCustomer, strMyServiceDays)

What's the difference?
In this case, there isn't much difference. It could easily be written either way. However, if you want to retrieve 2 pieces of information from a database you would need to use 2 byref parameters instead because a function can only return 1 value.

Going back to an example in one of my previous posts... Suppose you wanted to get the Shoe Size and the Eye Color for a customer from a database. Also suppose you get this information from several places within your app. The best thing to do would be to create a subroutine with 3 parameters. 1 ByVal parameter for the CustomerId, and 2 ByRef parameters (for Shoe Size and Eye Color).

Public Sub GetInfo([red]ByVal[/red] CustomerId As Long, [green]ByRef[/green] ShoeSize As Double, [green]ByRef[/green] EyeColor as string)

' pretend this is coming from a database
ShoeSize = 9.5
EyeColor = 'Brown'

End Sub

Since we will be using the CustomerId, but not modifying it from within the GetInfo subroutine, we declare it as ByVal. We want to change the ShoeSize and EyeColor in the subroutine, so we declare them as ByRef.

Make sense now?

You can generalize this as... when you want 1 return value, use a function. When you want several return values, use byref parameters in a subroutine.

There are exceptions to almost everything I said here. For now, concentrate on this, and your code will improve.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[unborn]

Ahh! now im confused!! :/

Ok you say a function can only return 1 value, but yet you are doing a call to pull the shoesize and eyecolor. I understand now that the bval for customerid would be that way because it wont be chaning it is the deciding factor on what record to pull from. The shoesize and eyecolor would be byref because it will have a different value for each record that is pulled up so that will be changing constantly. This was actually a great example because now i see where i would need that information. Now that code would work even if i didnt declare it byref/val correct? This just makes it run fast sort of like declaring your variables and their type because it is less calculation correct? (maken sure i understand)

Oh nm so the sub (i was thinking it was a function). But now im confused again hehehe so this sub (from what i can see) would be used..

Call GetInfo(txtacctID, ?, ?)

Where the ?'s are is there where i would say 9.5, Brown and it would then retrieve all records matching that and be available to be placed in an array? Not sure how i would use the information i would get from it.

From my understanding subs were like .. well commands. When i say this im meaning like i supply it with information and it will run a command but i didnt realize i could get any information out of them like

Call FormOnTop(Form1)

Laugh i can do alot of programming stuff pretty good for self taught but i some how feel ive completely missed the basics.. this stuff should be all so simple to me and i cant seem to grasp it. Arrays are scary for me aswell hehe i can do basic ones but eek sometimes i know it would work good but i get so lost in them.

Anyways am i anywhere where i need to be in the above understanding. Im sorry im struggling with this so much.

Running in circles is what I do best!