I Broke Access Stored Procedures

[dread]

Hello,

I apparently did something wrong in my modification of the Access Stored Procedures template. I saw the advisory in the comments section of the Template Code to rename the template if I wanted to customize it. I went to the properties slide-out for the Template and changed the name and saved it. It did not seem to be taking effect (I hadn't found the Refresh button yet for the Template Browser), so I tried using the New Guid button, thinking this might be a way to make the system see it as a new template.

My code changes work (I was changing the way it names the procedures) except now all of the column data types are using what appear to be SQL Server types instead of Access types. Here is an example:

Code: Select all

CREATE PROC upd_Area ( [_AreaID] Long, [_Area] Text, [_Active] Bit, [_Created] DateTime) AS
UPDATE [ltblArea]
SET [fstrArea] = [_Area] , [fynActive] = [_Active] , [fdatCreated] = [_Created]
WHERE  ([fintAreaID] = [_AreaID])

Notice how it is using data types such as Long, Text, and Bit.

Background info:

- using the beta 1.1.7.8

- connection set to an Access database

- trying to generate stored procs to save in the database (by the way, saving the procs to the database does not work--I had to create a utility to do this myself)

- the default Access Stored Procedures template was working fine in regard to the data types before I started making the changes described above

- everything looks normal in terms of data types in MyMeta Browser, DbTarget Mappings, and Language Mappings

- the Output Language of the modified Template is still set to \"Jet SQL\"

Where did I go wrong?

I'm going to try next starting with a fresh Access Stored Procedures template if I can figure out a way to get it to pull down a new one from your web site.

Thanks,
Dan

[dread]

Update: I just downloaded a new Access Stored Procedures from the server, and now it is doing the same thing with the data types...

I must have accidentally changed something else that's decoupled from the template.

Dan

[dread]

More update:

I'm very confused by all of this...besides the trouble I continue to have with these data types mapping incorrectly, I'm having trouble figuring out what I'm supposed to do if I want to copy and customize an existing template.

At first, as you might guess from my original post, I had not discovered the little \"Save As\" button, so when I changed the name and guid, it continued to use the original Access_StoredProcs.vbgen file. After I went to the template library on the server and retrieved a fresh copy of the Access Stored Procedures template, I had two templates under the Microsoft Access folder in the Template Browser, but it looked like they were somehow sharing the same Access_StoredProcs.vbgen file.

So I deleted both of the templates using the Template Browser, and confirmed on the disk that the Access_StoredProcs.vbgen file was gone. Then I retrieved a new copy of the template from the server, and confirmed that the Access_StoredProcs.vbgen was back. All looked normal (except that my data types problem persists, even after getting the fresh one from the server). So this time I tried the Save As button for the template. It prompted me to save a new .vbgen file, which I did. However, now I do not see my two templates in the Template Browser, even after refreshing the list and restarting the program. The two .vbgen files are there, but only one template shows up in the browser.

Any advice or ideas are appreciated. I hate that I've burned a whole day struggling with this, but I'm very impressed with this tool, and look forward to getting it working soon. I probably should not have started with the beta, but I wanted the .net 2.0 support.

Thanks,
Dan

[dread]

More info, in case it helps make sense of my problem: the IColumn Properties display has started showing the same incorrect column data types as are showing up in the template output. The connection string I am using, and the database I am opening, is clearly Access, and I'm using the same Microsoft.Jet.OLEDB.4.0 driver that I was using previously when it was working fine--yet in the IColumn Properties all of the columns show up with non-Access data types.

Thinking back over the sequence of events today, the only other thing I can think of that could have triggered this problem is that I tried to use the \"Save\" button on the Default Settings dialog. Ever since about that time, this data types problem has appeared.

I think I'm going to try uninstalling this beta and try the current release instead.

Dan

[dread]

After uninstalling the beta and installing 115, I have the same problem with the data types. I suspect something is cached in the registry since when I uninstalled the beta and installed 115, it still remembered my previous Default Settings. I guess the uninstall does not clear the registry. Perhaps whatever is messing up my data types is installed in the registry too. I'm going to try uninstalling again and this time clear the registry settings manually.

Dan

[dread]

The uninstall leaves behind a ton of component registrations in the registry, particularly related to MyMeta.dll.

I couldn't find anywhere in the registry where the Default settings are stored, so I have no idea how it remembered my settings even after an uninstall...and of course I have no idea whether this has anything to do with my data types problem--I'm just grasping at anything that might work.

Thanks again,
Dan

[dread]

I can see why the uninstall didn't appear to work completely: it left the whole MyGeneration folder under Program Files. I deleted the folder manually. I also rebooted for good measure, then installed 115 fresh again.

The Default Settings were reset, thankfully, but unfortunately MyGeneration is still seeing the Access data types incorrectly (Long, Text, bit, etc.). Ugh.

Maybe something in the OLE-DB middleware is causing this? But why would it be fine one minute, and then not the next...?

Thanks,
Dan

[mgnoonan]

Dan,

You probably have the DbTarget set incorrectly in MyGenerations's Default Settings dialog (on the Edit menu). If you have it set to SqlClient, you probably need to change it to OleDb for Access. That would cause the incorrect datatype problem you are describing.

HTH

[dread]

Hi Matt,

Thanks for the suggestion.

The Database Target Mapping setting has been set to \"OleDb\" the whole time. I have not changed this setting. I don't see any option for \"OleDb for Access,\" however.

I don't think what's going on here is a code generation problem, but rather a problem deeper down in the stack somewhere. I am convinced of this because the data types in the MyMeta Properties window show up incorrectly. It's actually worse now than it was previously: all data types now have a value of \"Long\" for the DataTypeNameComplete property--no matter what the original data type is. This program appears to be very confused...

Thanks,
Dan

[dread]

Referring back to my previous comment:

Now I don't know what to trust from what I'm seeing in the MyMeta Properties window. It appears to have some paint/refresh issues. As you move from column to column in the treeview, the DataTypeNameComplete value does not refresh. The DataTypeName property value changes appropriately, but the DataTypeNameComplete does not change with it. If you click in the DataTypeName property field, it appears to fix the paint/refresh, but then it sticks again when you go to the next property. (This is all in 115.)

This appears to be a separate issue from the fact that the DataTypeNameComplete value is wrong when the code is being generated.

I'm going to try modifying the template again to use the DataTypeName value instead of the DataTypeNameComplete value when generating the stored procedures. If that works, it won't explain why this was working fine earlier today, but even after uninstalls, re-installs, deletes, and reboots (and even testing with a brand new MDB file) it is doing this goofy data type thing...

Dan

[dread]

I figured out the cause of the above described paint/refresh problem: if you put your cursor in the DataTypeNameComplete \"Value\" cell in the property list, and then click back over to the treeview, as you move from column to column, the Value sticks to what it was when you first clicked in it. This actually happens with any of the properties, not just DataTypeNameComplete.

Dan

[dread]

OK, problem solved. For anyone who has followed these insane ramblings, I apologize. It's been a long day. The data type problem turned out to be a combination of my misunderstanding of the Access-OleDb data type mappings caused by a misleading error message from OleDb and a bug I introduced into my customized Access Stored Procedures template. Repeat: the Access-OleDb data types in MyGeneration and the Access Stored Procedures template appear to be fine.

It's funny how various clues seem to point one way but turn out later to be pointing another way entirely...

That said, if anyone from the dev team is reading, please consider my comments above regarding the troubles with copying and modifying a template, uninstalling the program, and the MyMeta Properties paint/refresh issue as still valid.

Best,
Dan

[mike.griffin]

Well dread, I hope you don't dread using MyGeneration, you lost a little time today but just think of the overall time savings you incur when using MyGeneration. We're glad you got it straightened out.

[dread]

Well dread, I hope you don't dread using MyGeneration, you lost a little time today but just think of the overall time savings you incur when using MyGeneration. We're glad you got it straightened out.

Thanks, Mike. I was thinking exactly the same thing: spending a day using MyGeneration was time well spent. I got my crud query layer, which would have taken me most or all of the day anyway, and now I feel like I have a handle on how MyGeneration works. I would also say that the fact that I gave it all day is a compliment overall: if the tool had not clearly had a lot to offer, and had not appeared well built, and had not had a community showing its support, I might have given up very quickly. I'm quite impressed not only with the product and the effort that has clearly gone into it, but also the fact that you're sharing it as freeware and that there is a community of users.

Today will be a day of using the dOOdads templates, and reckoning with the fact that I changed the stored procedure naming convention in my customization of the Access Stored Procedures template. Right at the end of the day yesterday I glanced at the dOOdads templates and realized they depend on the naming convention. I may start a new thread on that topic if things get interesting today. Or if it looks too hairy, I may just revert back and swallow using the default convention.

Best,
Dan

[mike.griffin]

Ya, the code needs to know the names of the procs, that's the beauty of code generation in that it's all predicable. I learned alot of things, one of the big ones was make it as user configurable as possible, in my EntitySpaces architecture we have a template that will allow you to customize alot of this kind of stuff.

This post talks about a beta, we're way beyond that version, but scroll down and look at our customization template. This allows users a certain level of customization without the need to modify (and therefore branch) the templates.

http://www.entityspaces.net/blog/Entity ... lable.aspx

[dread]

Thanks for the additional info, Mike. I plan to take a look at EntitySpaces for future projects. For my current needs (a fairly straightforward C# winforms client/server app with an MDB database), it looks like dOOdads will do the trick.

I was actually able to customize the dOOdads Business Entity template without too much trouble to accommodate the different naming convention I've implemented.

Best,
Dan