?
Solved

VB has just gone weird

Posted on 2003-03-06
9
Medium Priority
?
135 Views
Last Modified: 2010-05-01
Hi,

Somehow every local, global and module level string variable in my program (even the Connection String Property of the ADO Connection object!) is getting set to the same SQL Query string.  

The SQL query is built in a function, assigned to a local variable, then passed back to another function which executes it.  If I step through it all works fine, if I try to run through it errors because the check;

If strOrder_By <> "" Then ...(add the order by clause)

Hovering over and debug printing strOrder_By says it equals "" but when VB evaluates this line it runs the Then statement???

The really crazy thing is if I hit End on the error message window, then re-run the program... every string is still set to the last SQL Query, sames as when I stopped the prog.  I have to re-start VB to get it to run again, rebooting PC has no effect.

With the same code this is doing the same thing on both of my machines (VB6 SP5).  Am I going mad or have you guys seen this kind of insanity before?

Please Help if you can,

North
0
Comment
Question by:North
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 7

Expert Comment

by:fluglash
ID: 8078973
try to replace "" with vbNullString
0
 
LVL 53

Expert Comment

by:Ryan Chong
ID: 8078993
In your function, try use the ByVal rather than using default ByRef (VB6).

So, define your function like this:

function generateSQL(byval table As String,byval flds As String,byval condition As String, byval orderby As String, byval orderbyfld As String) As String
  generateSQL = "Select " & flds & " from " & table " & condition & IIF(orderby <> "",orderby & " " & orderbyfld,"")
end function
0
 
LVL 7

Expert Comment

by:fluglash
ID: 8079013
Strings in VB using ByVal by default
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:North
ID: 8079049
Hi,

Yeah I tried that fluglash, but it seems a little stranger than that, for example, when it reloads I set a function parameter to the result of an ini read (my data source cfg file).  Once inside the function, the parameter = the contents of the ini file + the SQL query from the previous execution, crazy isn't it!

ryancys, cheers for that, but it is more or less what it is doing already.  I have module level Private variables and use property lets to set them, you then call an execute method which builds the SQL from the current state of the properties.  It means that all knowledge of the database and SQL queries is handled outside of the main interface code.  

I now have actually solved this by making a new project file and including all components and references then adding all my forms, etc.  The only strange thing I got was a message from each form that uses an OCX of mine, saying the GUID ref was invalid, but it still found and used the controls in the OCX I had included in the new VBP.  I think the old VBP somehow got corrupted and had some weird reference?

The points are still there if anyone can tell me why it happened? Note: When I copy the code from my desktop to my laptop, and vice-versa, I have to recompile the OCX (binary compatability) or the program doesn't recognise the old OCX in the system32 folder, i then have to load the prog, add the recompiled ocx as a component, close and save only the project file, then reopen.  Could this be the cause and is there a better way?

North

0
 
LVL 53

Accepted Solution

by:
Ryan Chong earned 800 total points
ID: 8079344
>>, the parameter = the contents of the ini file + the SQL query from the previous execution, crazy isn't it!

I think maybe you forget to clear its value when assign it with the value in ini file. Try set the variable to "" before re-assign it with an value. Try to break the code (with F9) to debug your code, it do make helps.
0
 
LVL 26

Expert Comment

by:EDDYKT
ID: 8079589
Try this


If format(strOrder_By) <> "" Then
0
 
LVL 26

Expert Comment

by:EDDYKT
ID: 8079602
Sorry forget my comment


8->
0
 
LVL 1

Author Comment

by:North
ID: 8080627
Hi ryancys,

Assigning the variable the value from the ini file is the first thing I do after the program starts, it should be defaulted to "".  I never set that particular variable to the value of the SQL statement, every string variable in the program magically gets that value from nowhere once it has gone weird.  Most importantly, even after stopping and restarting execution.  It must be some strange memory, pointer or reference issue.

The problem is now solved by recreating the VBP file, I was just wondering if repeatedly swapping the code to run on one machine to another one where the dependent OCX has different GUIDs could be the cause?

North
0
 
LVL 1

Author Comment

by:North
ID: 8102646
Hi,

though there was no clear solution to my problem, I hate leaving questions open or asking CS to delete them when people have tried to help.  I have chosen ryancys' comment, on volume and relevance of comments.

Cheers for trying guys,

North
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month15 days, 4 hours left to enroll

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question