Re: Data Retrieval - Database Discussion Boards

How to Use an Array Variant as a control name?

dpminusa31-Jul-09 2:00

31-Jul-09 2:00

I have a Tab control with 15 tabs. I am using the TabCtl.Value in Select statements to control the ListBoxes, Reports, etc. for each tab. It occurred to me that I could simplify this greatly by defining a Variant Array of the ListBox names and report names. Then I could simply use array(TabCtl.Value) instead of the Select Statement details.

The problem is that I cannot see how to make the array(TabCtl.Value) into a Control name that accepts property or method references. It seems like this should be simple, but I have tried a few things with no success.

I am using Access 2007 on Vista Bus 32. Here is a sample of my code: (I have used the same names for the Queries, ListBoxes, Reports, etc. as a way of maximizing the usefulness of the one array and as an aid to reducing misspellings during coding. I find this less confusing than a lot of similar names.)

'An Array of Query Names to use as the RowSource for A Tab ListBox.
Dim lstFindRptNames As Variant
    
    lstFindRptNames = Array("lstByCusName", _
    "lstByContact1", _
    "lstByContact2", _
    "lstByContact3", _
        ...
    "lstByProspect", _
    "lstByCustomer")


'Populate the ListBox by referencing a Query of the same name as the RowSource
lstFindRptNames(TabCtl0.Value).RowSource = lstFindRptNames(TabCtl0)

This fails because Access does not seem to substitute the String, in context, with the Control of the same name. So I tried to help it out with making a Control with a DIM first, I also tried a Generic Object. Neither of these lead to a solution.

In several other languages, other than VBA, this is a non-brainer. What am I missing?

"Coding for fun and profit ... mostly fun"

Re: How to Use an Array Variant as a control name?

Luc Pattyn31-Jul-09 2:22

Luc Pattyn

31-Jul-09 2:22

Assuming you want a solution for VB.NET, I would hold the references to the ListBoxes in a generic List (or even a Dictionary; not really an array), and most certainly not the names.

You then can use indexing, and even for each

In VBA, which I haven'tused for years, I would hope and use an array of ListBoxes.

BTW: you're in the wrong forum, this isn't a database question.

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.

Re: How to Use an Array Variant as a control name?

dpminusa31-Jul-09 9:53

dpminusa

31-Jul-09 9:53

Thanks for the hint. Not sure which Forum is correct.

I tried to use the References Idea. It does not seem to apply or I am not clear on its use in this case. I see it is a way to get at Tools-> References. Tools-> References includes a reference to my open project. But I can get to these objects directly any way.

Can you offer a snippet?

I am also trying:

TabCtl0.Pages(TabCtl0.Value).Controls but it only has count and item as properties and no methods.

Hmmm...

"Coding for fun and profit ... mostly fun"

Re: How to Use an Array Variant as a control name?

Luc Pattyn31-Jul-09 10:20

Luc Pattyn

31-Jul-09 10:20

Hi,

I still don't know which language you want: VB, VB.NET, VBA.
Anyway they all belong in the "Visual Basic"forum.

I don't have any VBx snippets handy right now (and almost none most of the time, I'm more of a C# kind of guy).

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.

Re: How to Use an Array Variant as a control name?

dpminusa31-Jul-09 10:28

dpminusa

31-Jul-09 10:28

VBA is what Access 2007 uses as default, as far as I know. I am forced to use a lot of different tools and languages in my projects. I am more comfortable with other languages as well.

I will try to more it to the VBx forum. I guess that is a cut and paste unless you are a moderator.

Thanks for the Hints.

PS: If you need help with Perl, maybe I can reciprocate.

"Coding for fun and profit ... mostly fun"

Full Text Search

ps_prakash0230-Jul-09 21:33

ps_prakash02

30-Jul-09 21:33

Hi Guys,

I'm in need of your help.

my problem is, i have two tables namely MASTER and LINECONTENT.

"MASTER" table contains 5,00,000 records,
The columns in MASTER table is
masterid int, masterdesc varchar(1000).

"LINECONTENT" table contains more than 1000 records &
its columns are pageno int, lineno int, content varchar(2000).

I want to compare each row of "MASTER" table, masterdesc column value with the "LINECONTENT" table content column,
If any records matched i want to take the matched master record and store it in another table.

for this i used the below query.

select b.* from linecontent a
join master b on a.content like '%'+b.masterdesc+'%'

but this query is taking much.
so we are going full text search option.
i enabled fulltext search MASTER table as well as LINECONTENT table.

but i dont know how to link these tables in freetext search.
IS THERE ANY OPTIONS LIKE THE BELOW ONE.

SELECT b.* FROM LineContent A
JOIN Master b ON FREETEXT(a.Content,b.MasterDesc)

for this scenario which method i have to follow to make the process faster.

please help me

thanks & regards
P.Prakash

Data Retrieval

mgr_2k730-Jul-09 20:25

mgr_2k7

30-Jul-09 20:25

I have a problem of trying to find the query for finding invalid dates in my table which does not follow the following formats. Given are the valid formats:

1. 10/1/2007
2. Thu Sep 20 06:32:10 GMT-0400 (EDT) 2007
3. 10/1/2007 5:00

Can anybody please help me regarding this?
Thanks in advance.

Smile | :)

Re: Data Retrieval

Blue_Boy30-Jul-09 21:37

Blue_Boy

30-Jul-09 21:37

try this

select convert(varchar(10),cast(datevalue as datetime) ,120) from dateformats<br />
where convert(varchar(10),cast(datevalue as datetime) ,120) ='2007-10-01'

Date Thu Sep 20 06:32:10 GMT-0400 (EDT) 2007 will not work for this query because it will cause convertion error exception.

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

www.aktualiteti.com

sql server 2005 installation

Spurple30-Jul-09 18:14

Spurple

30-Jul-09 18:14

Hi,
I have install sql server 2005 on server. i can access server from client machine.
but the problem is when i try to access client from server,its not connecting.
am i missing some setting during installation? what could be the reason for this?
plz guide!

Re: sql server 2005 installation

Mycroft Holmes30-Jul-09 20:24

Mycroft Holmes

30-Jul-09 20:24

Spurple wrote:
the problem is when i try to access client from server

This statement is wrong, the server does not know what/where the client is.

Never underestimate the power of human stupidity
RAH

Re: sql server 2005 installation

Spurple30-Jul-09 23:48

Spurple

30-Jul-09 23:48

ok,
thank u for replying sir!
could u plz tell me any article to know more abt this!

Re: sql server 2005 installation

Eddy Vluggen31-Jul-09 6:14

Eddy Vluggen

31-Jul-09 6:14

Spurple wrote:
,its not connecting.

Did you get an error? What did it say?

Spurple wrote:
what could be the reason for this?

Did you allow remote connections[^] over TCP/IP?

SQL Lingua Venusta

Re: sql server 2005 installation

Jerry Hammond2-Aug-09 3:55

Jerry Hammond

2-Aug-09 3:55

Do you mean the SQL Server or "the server" cannot see the client/work station?

"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering

Re: sql server 2005 installation

Robin_Roy3-Aug-09 0:16

Robin_Roy

3-Aug-09 0:16

What do you mean by access client from the SQL Server 2005....
Please put in some addl. details... usually, from client, the database server is connected...

MS SQL 2005 - merge new rows and tables

Member 469202330-Jul-09 8:59

Member 4692023

30-Jul-09 8:59

I have a development database and production database.

The developers have added new tables and new rows to existing tables.

I need to merge these changes from the dev db to the prod db and am unable to construct a search to find an appropriate article.

Can someone help point me in the right direction?

Re: MS SQL 2005 - merge new rows and tables

Robin_Roy30-Jul-09 16:40

Robin_Roy

30-Jul-09 16:40

Hey, these is a common thing when there are changes in the database schema.
In such a case, your developer must have documented the changes that they have made.
Create Alter scripts on the table with the changes on the schema and run the same on the Production environment to update the schema on the production.
But ensure you backup the production database before executing the alter scripts.

Re: MS SQL 2005 - merge new rows and tables

Mycroft Holmes30-Jul-09 16:54

Mycroft Holmes

30-Jul-09 16:54

You need to look at the Red-Gate products. While you can create the scripts yourself it is tedious and time consuming. I have been using SQL Compare for years and would not be without it. Seriously it will totally eliminate this headache.

Never underestimate the power of human stupidity
RAH

.NET DataGridView question

Art Frank30-Jul-09 7:56

Art Frank

30-Jul-09 7:56

A contrived example that demonstrates what I'm trying to accomplish:

Four tables, like so:

main_table:
persons_name
car
color

car_lookup:
car_id
car_description

color_lookup:
color_id
color_description

car_color:
car_id
color_id

The idea is that for each type of car in the car_lookup table, there are only a few values in the color_lookup table that are valid. The car_color table has the all of the color_id values that are valid for each car_id (that is, if I select all of the rows for a particluar car_id, I'll retrieve all of the valid color_ids for that car).

Question 1: ok design? Is there a better way to approach this?
Question 2: I'm using a DataGridView to present the main_table to the end-user for editing. Works fine when I'm just presenting the table with the car and color fields just showing ID numbers. How can I present the data so that the DataGridView presents the car_description instead of the car_id that is in the car field (and this one field is not changeable) and for the color field present a drop down of just the valid colors for the given car_id? This would mean that the drop down is different for each row. If car #1 could be only green or blue, whereas car #2 could be blue or red, then the drop down would reflect that.

The database is on a SQL server, the DataGridView is connected to a BindingSource, which is connected to a SqlDataAdapter.

Unsure if this belongs in the .NET section or here. Apologies if this is the wrong place. I'm writing in C#, but any general DataGridView info would be appreciated.

Re: .NET DataGridView question

Robin_Roy30-Jul-09 16:35

Robin_Roy

30-Jul-09 16:35

The database design is fine, as the car and color has many-to-many relationship.
Regarding the database binding, you can have queries in your stored procedure to return multi-recordsets and then you can store them in multi datatables in your dataset with relationship between the datatables...
Then bind the cars and based on the car the colors to the dropdown list by iterating through the rows in the GridView...

Re: .NET DataGridView question

Mycroft Holmes30-Jul-09 16:48

Mycroft Holmes

30-Jul-09 16:48

Your table design seems fine

I never use a SQLDataAdapter as a binding source as the adapter is always in the DAL and never comes out, only a datatable comes out of the DAL.

I would create a stored proc to service the car grid (CarGetForGrid) that joined the 4 tables and displayed the descriptors and the carid only. the result set would be retrieved via the DAL and used to populate the DGV. I then hide col0 from the user in the DGV

I have a major issue with allowing the user to do inline editing of the DGV. This is a personal design issue, I almost never allow inline editing. I force the user to double click the required record and display a dialog nicely layed out for CRUD. This does require rather a lot of dialogs but I have much greater control with a dialog. A list control is dor display purposes only, a dialog is for data entry.

This design eliminates all the crap around combos in grids.

Never underestimate the power of human stupidity
RAH

Re: .NET DataGridView question

Art Frank31-Jul-09 9:18

Art Frank

31-Jul-09 9:18

Thanks for the advice. I think I'll do what you suggest -- force the user to double-click the row to edit, then present a form showing just that record. Since the editing is minimal, I was hoping to get away with just a table, but this is probably better design.

Re: .NET DataGridView question

Art Frank31-Jul-09 9:25

Art Frank

31-Jul-09 9:25

If you don't use a SQLDataAdapter as your binding source, what do you recommend? There appear to be a few different ways to bind the the DataGridView to the database -- I think I just used a method I found in Microsoft's docs. (While I'm a resonably experienced programmer, I'm pretty new to .NET.)

Re: .NET DataGridView question

Mycroft Holmes31-Jul-09 12:42

Mycroft Holmes

31-Jul-09 12:42

Art Frank wrote:
There appear to be a few different ways

This my friend is an understatement, I usually use 2 data sources, a datatable/view or a List<myclass> either bound to a bindingsource and the bindingsource as the datasource to the table. I then hide any columns in the DGV I don't like, I have a base rule that the recordID is ALWAYS in column0 and this is almost always hidden.

On the dblclick, grab the selected row.cell[0].value and pass it to the dialogs constructor. If you are using a List<myclass> you can pass the instance of MyClass.

I have a Utils class that does all the binding and formatting for me so I dont even think about it. It has some requirements like controls are named and prefixed correctly but I have been doing that for so long it is automatic.

Never underestimate the power of human stupidity
RAH

How to protect sqlserver database from unauthorized access

shine_paravur30-Jul-09 0:40

shine_paravur

30-Jul-09 0:40

Hi all
Can you help me to protect sql server 2005 database from unauthorized access.I am using C#.net2005. Please help me to solve this problem

Thanks in advance

regards

shine.paravur@gmail.com

Re: How to protect sqlserver database from unauthorized access

dan!sh 30-Jul-09 0:47

dan!sh

30-Jul-09 0:47

Create user logins and assign them appropriate roles.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.