How to use ado.net to open mdb file in vb.net - Database Discussion Boards

pankajdaga17-Dec-03 7:18

17-Dec-03 7:18

Hi everyone,

Using access, I try to run the following SQL:

SELECT (Select Values from AnotherTable) FROM MyQuery

MyQuery returns a set of data and I want to filter it further by selecting only the records that are returned by the sub-select. However, this does not work. Are such select queries illegal. Is it not possible to tell it the column names for select through another select query? Is there any workaround for it, rather than creating the string in memory and manually concatenating each column name. An SQL solution would be nice.

I would really appreciate some help on this one.

Thanks,
Pankaj

Without struggle, there is no progress

Re: Sql question

Not Active17-Dec-03 8:22

Not Active

17-Dec-03 8:22

Is this what you are trying to

SELECT values FROM Table
WHERE somefield IN (SELECT values FROM AnotherTable)

Search MSDN for subquery

Re: Sql question

pankajdaga17-Dec-03 9:24

pankajdaga

17-Dec-03 9:24

Hi there,

Actually here is a more clear description of what I am trying to do. Sorry to be vague earlier.

In a Select statement you can specify the columns that you want to display.
Example,

Select FirstName, LastName from PeopleTable.

What I am trying to do is this: I have a table (say PeopleTable) that has many columns...say LastName, FirstName, Data Of Birth, Occupation etc.

Now, I have another table that is filled by another process that tells me which columns to display: say, example, LastName, FirstName...

So what I wanted to do was:

SELECT (Select Values from AnotherTable) FROM PeopleTable

Select Values from AnotherTable would return LastName and FirstName and my query would be:

SELECT(LastName, FirstName) from PeopleTable. However, I guess SQL does not do such syntactic substitution.

Does anyone know a way to achieve this. I do not want to use a string and concatenate each of the choices, if there is a way to achieve this with SQL.

Thanks a lot Smile | :)

Sincerely,
Pankaj

Without struggle, there is no progress

Re: Sql question

jeff_martin17-Dec-03 10:23

jeff_martin

17-Dec-03 10:23

Without using a string and concating your query together, No, there is no way to do what you want.

Sorry.

Re: Sql question

pankajdaga17-Dec-03 11:23

pankajdaga

17-Dec-03 11:23

Yeah, that is what I figured after hours of experimenting here. Damn SQL, we need macros here Poke tongue | ;-P

Thanks for replying and making me finally give up so that I can go to bed Smile | :)

Sincerely,
Pankaj

Without struggle, there is no progress

Please help me with Datagrid

roger.nie17-Dec-03 6:24

roger.nie

17-Dec-03 6:24

hi,there

I have a Datagrid bind to Dataview (Columns : Name,type,body,haveread,system......)
show as follow :

<asp:datagrid id="myDataGrid" runat="server" onitemdatabound="myDataGrid_ItemDataBound" border="0"
="" width="100%" allowsorting="True" backcolor="#EEEEEE">
<footerstyle backcolor="#CCCC99">
<columns>
<asp:boundcolumn datafield="Name" sortexpression="MailType" headertext=" ">
<headerstyle width="3%">

<asp:boundcolumn datafield="Name" sortexpression="Name" headertext="Name">
<headerstyle font-bold="True" width="20%">

<asp:boundcolumn datafield="Type" sortexpression="Type" headertext="Type">
<headerstyle font-bold="True" width="57%">

Code behind have Event as follow:

public void myDataGrid_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
{
if ((e.Item.ItemType == ListItemType.Item) || (e.Item.ItemType == ListItemType.AlternatingItem))
{
e.Item.Attributes.Add("onmouseover", "this.style.backgroundColor='#FFFFCC'");
e.Item.Attributes.Add("onmouseout", "this.style.backgroundColor='#FFFFFF'");
}
}

Please help me how to do :

1.If I click on one row, I can in the textBox1---to TextBox6 show detail information such name,type,body,haveread....(though Datagrid just show 2 columns),

2.If I check some row in dataview the item"haveread" is "true", I want Bold Datagrid row related .

3.If I check some row in dataview the item"Syetem " is "true", I want have more column in the Datagrid show a picture "s1.gif" ,otherwise "s2.gif"

I am a new programmer in Datagrid,please show me in detail as possible.

Thank a lot

hoe to get ADO info

xprtguro16-Dec-03 21:15

xprtguro

16-Dec-03 21:15

I want to know the complete information of a Database...
how many tables it have what r there manes...
each table have how many attributes and what r there names......
and want to show it in a treeview...like this
TreeView1.Nodes(0).Nodes.Add(snode)
using OleDbConnection
i execute query like this
select * from emp

Table.Columns.Count
Table.Columns.Item(count).ToString
through this i came to know the column number and name
but want to know full information about a database.....

Nothing ...........................................................

How to use ado.net to open mdb file in vb.net

m_3ryan16-Dec-03 5:29

m_3ryan

16-Dec-03 5:29

Ineed help to know how to open mdb file in vb.net

Re: How to use ado.net to open mdb file in vb.net

Colin Angus Mackay16-Dec-03 5:53

Colin Angus Mackay

16-Dec-03 5:53

use the OleDb classes. You'll find all the information in the online help or at MSDN. You should read up on database connectivity in .NET to get an idea of how it all works. However, OleDBConnection, OleDBCommand are both good search strings to get you started.

--Colin Mackay--

"In the confrontation between the stream and the rock, the stream always wins - not through strength but perseverance." (H. Jackson Brown)

Enumerators in .NET: See how to customise foreach loops with C#

Re: How to use ado.net to open mdb file in vb.net

Mike Ellison17-Dec-03 13:40

Mike Ellison

17-Dec-03 13:40

Another good resource I like is http://www.connectionstrings.com[^]. I always forget what to use for access or SQL connection strings - that site has those plus plenty more.

MsSQL Newbie Questions

MKlucher15-Dec-03 19:03

MKlucher

15-Dec-03 19:03

I've done a couple of ASP/.NET apps that use databases so I know the basics of ADO. My problem is all my development up to this point have been in MySQL and not MsSQL(Or maybe just SQL) anyway, I have absolutly no idea what I need to do when it comes to getting started with what I need.

Here's what I fumbled through so far.

1) Got SQL installed on a dedicated SQL machine. It's already being used by other programs.

So that's it.... Blush | :O

I did use the Enterprise manager to create a new database, but I noticed all this other stuff gets created with it (All those sys tables for one thing) Do I need to keep those around? Adding tables seems pretty strait forward and not too tough. I am a liitle confused on users, as I added a new database user to the database but I can't seem to remove the user dbo. Nor can I seem to have the new user take ownership of everything as I want to have this new user I created to be able to do everything(Cause I will be using this login to do all the updates and querys on the DB)

Anyone know how I should be properly creating this or if there are any quick and dirty guides on quickly setting up a new database. All my books seem to skim right through this stuff.....

Thanks!

Re: MsSQL Newbie Questions

Colin Angus Mackay15-Dec-03 22:28

Colin Angus Mackay

15-Dec-03 22:28

Kluch wrote:
(All those sys tables for one thing) Do I need to keep those around?

Yes, you need the system tables - they contain the metadata that keeps SQL Server running. You wouldn't want to remove the table that defines all the columns in the database would you? Or the table that contains the permissions for who can access what?

Kluch wrote:
I can't seem to remove the user dbo

dbo = DataBase Owner and cannot be removed. IIRC, Any user with admin rights is automatically the dbo. All objects that everyone has access to should be created as dbo because then things get really confused. As a standard many organisations have a rule that the dbo owns everything - this improves efficiency and removes confusion when security come in to play. If you start having lots of users owning lots of different things then security and access rights are hell to manage.

Kluch wrote:
All my books seem to skim right through this stuff.....

I would suggest that only the sa (system administrator) user should be modifying the database at this level. If you take that advice then there isn't much to do - so skim right on through.

--Colin Mackay--

Execute

Anonymous15-Dec-03 7:53

Anonymous

15-Dec-03 7:53

In my DTS package I have (Execute SQL Task ) name test1.
I would like to execute this task and skip other taks in
the same dts. How do I do this?

Baby steps to .net

crderrick11-Dec-03 1:52

crderrick

11-Dec-03 1:52

Our programmer's are using Visual Studio 6, ASP, and ADO accessing Oracle databases - no .net yet. There is a need to use an Oracle feature (Proxy User Authentication) that is available through the Oracle odp.net driver. It will not work through the Microsoft or Oracle oledb drivers.

What is the smallest change that we would have to make to let them use the odp.net driver? Run the .net framework on the web server? Switch to ado.net? Any change needed to development environment?

ADO .NET and MFC

Late11-Dec-03 1:15

Late

11-Dec-03 1:15

Can ADO .NET be used in MFC window?

Create multi-page report!

Anonymous10-Dec-03 17:27

Anonymous

10-Dec-03 17:27

Hi!

In Access,when I make a report, how can I multi-page in screen?

Thanks!

Clear Table in a Dataset Containing Multiple Tables - How?

CharlieMay10-Dec-03 16:31

CharlieMay

10-Dec-03 16:31

I have a dataset with two tables Client (daClient) & Log. (daLog)

I have a combobox bound to the Client table of the dataset filling with the Client column

The FlexGrid is bound to the Log Table and is filled based on the SelectedValue of the combobox. My problem is, to update the Grid based on the selection of the combobox to pull only logs matching the ClientID I need to clear and refill dataset1.Log only. If I just dataset1.clear() I will lose the information in the combobox.

I tried daLog.Tables("Log").clear but this doesn't work.

Right now, the Grid fills with ClientID 6's rows because that is what is in the combobox when I load the form. I set the selectcommand parameter to get combobox1.SelectedValue on load of the form.

I hope this isn't confusing and if so, I can go into more detail with specific settings to objects. The only thing I can say right now is that when changing the combobox to another client, I get the new information in the grid but the old information remains so I now have 2 client's rows in the grid. If I click on one of the items in the grid, the combobox changes to show that client in the combobox. I don't really need this since I only want to display the selected clients information.

Thanks,
Charlie

Re: :: problem in UPDATE statement ::

Colin Angus Mackay11-Dec-03 15:45

Colin Angus Mackay

11-Dec-03 15:45

I'm not sure that I understand what you want to do.

I'll interpret the SQL you've got and let me know which bits you need different to what you have already.

UPDATE table                     -- The updates are on the table
                                 -- named "table"

SET field = field + intvariable  -- The field named "field" is being updated
                                 -- The new value is the addition of the
                                 -- existing "field" value plus the value in
                                 -- field named "intvariable"

WHERE otherfield = yes           -- Apply this update only on records where
                                 -- the field "otherfield" is yes

From your naming convention here, I am thinking that "intvarible" is something that is not in the database already. Is that right? If yes then:
(1) Is "intvariable" the same value for all instances where "otherfield = yes"?
(2) Is "intvariable" different for each record that you want to update?

--Colin Mackay--

Re: :: problem in UPDATE statement ::

Colin Angus Mackay12-Dec-03 12:06

Colin Angus Mackay

12-Dec-03 12:06

Okay, I've got the files you sent me. So that everyone else understands what is happening I'll explain a little about what was sent.

I received a database with a table called "tsttbl", that table contained a field called "value". The important part of the code I have copied below.

For to help, I've numbered the lines.

1  void Button1_Click(Object sender, EventArgs e)
2  {
3      string str = TextBox1.Text;
4      int num = Convert.ToInt32(str);
5      string strConnect = "Provider=Microsoft.Jet.OLEDB.4.0 ;Data Source="+Server.MapPath(".\\db\\tstdb.mdb");
6      string strupdate = "UPDATE tsttbl SET value = value + str";
7
8      // ... Remainder of method here ...
9  }

In line 6 you define the SQL statement you use to update the database. What this says is: Update all records (because there is no where clause) in tsttble and set the value field to be its current value plus the value of the str field. However there is no str field in the database.

I am guessing, because you've not said, that the str value that you really want is the one defined on line 3. If this is correct change line 6 to:

string strupdate = string.Concat("UPDATE tsttbl SET value = value + ",num.ToString());

I've used num instead of str because num is guaranteed to be a valid integer.

I hope all this helps.

Regards,
Colin.

--Colin Mackay--

Re: :: problem in UPDATE statement ::

Majid al-Sarra13-Dec-03 0:46

Majid al-Sarra

13-Dec-03 0:46

HI
I think the best way is to put the number value in a parameter and add the parameter in the command object then use it in SQL statment it is better than paste it in the string of SQL statment by using var.toString()

Access database - change password?

Rickard Andersson²⁰10-Dec-03 8:40

Rickard Andersson²⁰

10-Dec-03 8:40

Is it possible from my program to change a password for an Access database?

I'm building an application that has to store restricted information in an Access database. In Access, the program itself, you can change password (at least you can remove and re-add a password, not really "change").

Rickard Andersson
Here is my card, contact me later!
UIN: 50302279
Sonork: 37318

Inserting files into database table

tmagoo10-Dec-03 5:45

tmagoo

10-Dec-03 5:45

I have been reading various articles where bitmaps, excel files, pdf files, etc... are being inserting into a column of a database table. Out of curiosity, why are developers doing this and what are the benefits? Big Grin | :-D

Re: Inserting files into database table

10-Dec-03 8:05

:-DIn my understanding, let's say you have an employee database, and you want to store employee pictures as well in the database along with the name and other information. In that case you may want to store it as a bit map.

Also alot of business are still receiving documents as a hard copy, and to keep the paper trail at one place, they scan the document and upload it to the Database.

This is just few of the many reasons why bit map, and files are inserted to DB.

Hope this helps
Ruchi

ADO.NET

sreemurali9-Dec-03 10:59

sreemurali

9-Dec-03 10:59

My windows form consists of a datagrid which is populated with the records from the database. When i select a row using HitTest property the data is displayed in the textboxes that i have on my form.I hava an update button which updates this selected data to the database.User can change the data that is displayed in the textboxes.

My question is : I am not able to update this changed data to the database.

Please help.
My code is in c#

Thanks

ADO.NET

sreemurali9-Dec-03 10:59

sreemurali

9-Dec-03 10:59

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