how do i use textbox value from one form on another to access database without releasing the resource(value) - Windows Forms Discussion Boards

I've been pulling my hair out over this for the past week. I finally got the query to work perfectly last night in MS SQL management studio, but when I tried to build it into my VS2008 application it has failed miserable. All day long. For the last 12 hours I've been fighting to make this standard T-SQL syntax work in VS. Here is the code:

SELECT NAME, TIME, AGE,
ROW_NUMBER( ) OVER (ORDER BY TIME) AS RANKING
FROM 'SampleFile.txt'
WHERE AGE<16
UNION
SELECT NAME, TIME, AGE,
ROW_NUMBER( ) OVER (ORDER BY TIME) AS RANKING
FROM 'SampleFile.txt'
WHERE AGE
BETWEEN 16 AND 29
UNION
SELECT NAME, TIME, AGE,
ROW_NUMBER( ) OVER (ORDER BY TIME) AS RANKING
FROM 'SampleFile.txt'
WHERE AGE>29
ORDER BY TIME

What I inevitably get back from the TableAdapter Configuration Wizard is:
Error in SELECT clause: expression near ','.
Missing FROM clause;
Unable to parse query text.

When I click to finish anyway and run the solution I get "OdbcException was unhandled"
ERROR [42000] [ODBC Text Driver] Syntax error (missing operator) in query expression
'ROW_NUMBER () OVER(ORDER BY TIME)'

I have tried every combination of parenthesis, quotes, etc. Nothing has worked.
BTW, I'm using the ODBC text driver, don't know if that makes a difference.
Please help.
Sincerely,
Andrew

Your error messsage says that your using an ODBC Text Driver in your connection string, which will not in any way shape or form work with SQL Server. Post the code that's throwing the error, and your connection string.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007, 2008

I'm sorry if I spoke without precision - the query I posted worked when I tried it with SQL management studio, run against a sample database. The error shown was thrown when I wrote that exact query code into the DataTable Adaptor in Visual Studio 2008 as a query to the ODBC Text Driver Connected text file. "DSN = SampleFile.txt" is the connection string. I could post the whole path, but it would be meaningless, as it is specific to my machine.
My complete effort is detailed here:

Copy the text to a file called "SampleFile.txt" in a selected directory.

Edit the schema.ini file of that directory as follows:

[samplefile.txt]
ColNameHeader=False
Format=FixedLength
MaxScanRows=25
CharacterSet=OEM
Col1=NAME Char Width 8
Col2=TIME Integer Width 8
Col3=AGE Integer Width 8

Under the Data Sources (ODBC) section of the administrative tools file in control panel, set
"SampleFile.txt" to be a Microsoft Text Driver (*.txt; *.csv) System Data Source.

In Visual Studio 2008 create a new project using the windows application template.
Place a datagridview in the form.

Create a Data table, using the following SQL query:

SELECT NAME, TIME, AGE,
ROW_NUMBER() OVER(ORDER BY TIME) As Rank
FROM 'SampleFile.txt'
WHERE AGE < 16
UNION
SELECT NAME, TIME, AGE,
ROW_NUMBER() OVER(ORDER BY TIME) As Rank
FROM 'SampleFile.txt'
WHERE AGE BETWEEN 16 AND 29
UNION
SELECT NAME, TIME, AGE,
ROW_NUMBER() OVER(ORDER BY TIME) As Rank
FROM 'SampleFile.txt'
WHERE AGE > 29
ORDER BY TIME

Here is where it failed. I'm trying to rank 3 age group result sets and then output all three into one datagridview.

I was using the approach wherein I wrote 3 queries into 3 datatables, created a column I called ranking and then set it to auto-increment, seed=1, step=1, but then I couldn't get the 3 resulting datatables to bind back to the 1 datagridview, so I've been battling with this one query linked with the UNION clause.

Thanks for your help,
Andrew

Well, it doesn't work because the Text Driver doesn't support full SQL. It supports a small subset of SQL only and, of course, no UNIONs.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007, 2008

No, that's wrong. UNION works fine, it's the ROW_NUMBER function that isn't working. That's why I'm appealing to the greater developer community - to find a work around. I thought maybe a "Microsoft MVP" might be able to help.

Your only work around that I can think of would be to load the data into a DataTable or other contain and use LINQ to get the results.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007, 2008

I figured it out. I had to use the archaic SQL 2000 "COUNT" syntax to achieve the rank functionality. Thanks for your response though.

Hello there!
My code is highlighted below.I have five tabpages(two is shown here!) on a form called vwpatient that runs each time a tabpage is selected.The code below works but my problem is this: there is a textbox called sachPatient.patID from another form called sachPatient, the code pulls data from the database on clicking one or two pages, but if more than two is clicked, an exception fires saying "Make sure you have not released a resource before attempting to use it". I have tried endlessly to fix this problem to no avail. And am still trying!!.
I would appreciate if someone can help me.
Thanks in advance!!!

Private Sub TabControl1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles TabControl1.SelectedIndexChanged

For Each tabcont As Control In Me.Controls
If TypeOf tabcont Is TabControl Then
For Each tp As Control In tabcont.Controls
If TypeOf tp Is TabPage Then
Select Case tp.Name
Case "vwProfTabPg"

Try

LoadData()
' Configure and execute the command.
cmd = New System.Data.SqlClient.SqlCommand("SELECT * FROM Patient_profile where patient_Id = @Pat_ID")
cmd.Connection = cn
Dim paraPatId As SqlParameter = cmd.Parameters.Add("@Pat_ID", sachPatient.patID.Text)
' Load the DataTable.
datadapt = New System.Data.SqlClient.SqlDataAdapter(cmd)
datadapt.Fill(ds)
ds.Tables.Add(dt)

' Bind the DataGrid.

vwProfDG.DataSource = ds.Tables(0)
vwProfDG.Visible = True

Catch ex As SqlException
MsgBox(ex.Message.ToString())
Finally
cn.Close()
End Try

Case "vwLabTabPg"
Try

LoadData()

' Configure and execute the command.
cmd = New System.Data.SqlClient.SqlCommand("SELECT * FROM lab where patient_Id = @PatLa_ID")
cmd.Connection = cn
Dim paraPatId As SqlParameter = cmd.Parameters.Add("@PatLa_ID", sachPatient.patID.Text)
' Load the DataTable.
datadapt = New System.Data.SqlClient.SqlDataAdapter(cmd)
datadapt.Fill(ds)
ds.Tables.Add(dt)

' Bind the DataGrid.

vwLabDatGrid.DataSource = ds.Tables(0)
vwLabDatGrid.Visible = True

Catch ex As SqlException
MsgBox(ex.Message.ToString())
Finally
cn.Close()
End Try
End Select
End If
Next
End If
Next

Hi,

< PIV 2.8 mhz, Win XP SP3, VS 2010, 2Gigs of Ram >

I notice a "one time cost" (on the order of 10 seconds) at a WinForm's application startup where the PropertyGrid control is not responsive to any selection of properties.

I assume this is because the PropertyGrid is using reflection to enumerate all the current controls on the Form, and recursively iterating on any Container controls which have some Controls inside them.

Just wondered if anyone else had experienced this, and had observed a delay, or has another hypothesis about the delay.

thanks, Bill

"Many : not conversant with mathematical studies, imagine that because it [the Analytical Engine] is to give results in numerical notation, its processes must consequently be arithmetical, numerical, rather than algebraical and analytical. This is an error. The engine can arrange and combine numerical quantities as if they were letters or any other general symbols; and it fact it might bring out its results in algebraical notation, were provisions made accordingly." Ada, Countess Lovelace, 1844

BillWoodruff wrote:
the PropertyGrid is using reflection to enumerate all the current controls on the Form

Huh? Why would it do that?

BillWoodruff wrote:
Just wondered if anyone else had experienced this

I have not. My wild ass guess would be that it's about how you are using it. That statement is not about you, it means if I encountered that scenario I would assume it was something I had done.

Also, what version of .NET are you using?

and, your sig has now sent me off to do some studying. Wink | ;)

Hi Led Mike,

I am also asking "huh" Smile | :)

And I first assumed it was something I was doing, also. I first was using the ProperyGrid on a separate form as a "floating window" where the form it was on had its Parent property set to the Main Form.

So my first hypothesis was that perhaps the dynamic communication between the two forms was causing some complex interop and marshalling of data back and forth.

So I moved the PropertyGrid directy onto the Main Form : same problem.

So then, assuming the fact I had some controls in the "virtual cells" of tableLayoutPanel, and that accessing perhaps are causing the delay, I ripped out the tableLayoutPanel, just put a few simple controls on the main form, and tested again : still a long delay after the first control is set as the SelectedObject for the PropertyGrid and the PropertyGrid becomes usable.

I have read elsewhere that the PropertyGrid ... at desgn time ... is using reflection to discover the attributes of controls or forms.

Using the principle of Occam's Razor, the simplest hypothesis is that when the control is set, when it's instantiated at run-time : it does a recursive walk through the inner object tree of the app and uses reflection to discover all control objects and their inner list of propertes to edit for each control.

If it didn't parse them first, and then somehow "cache" the clusters of properties, which can vary from control to control, how could it suddenly switch from being non-responsive for over 10 seconds to performing consistently fast as you're editing even such complex properties as the number of columns in a TableLayoutPanel ? And jumping from control to control, it remains fast after that initial load penalty.

But I do note that when used at run-time the Property Grid will omit any special UI Editors for collections and such : I consider that it doesn't break on those to be a "feature."

best, Bill

"Many : not conversant with mathematical studies, imagine that because it [the Analytical Engine] is to give results in numerical notation, its processes must consequently be arithmetical, numerical, rather than algebraical and analytical. This is an error. The engine can arrange and combine numerical quantities as if they were letters or any other general symbols; and it fact it might bring out its results in algebraical notation, were provisions made accordingly." Ada, Countess Lovelace, 1844

BillWoodruff wrote:
I have read elsewhere that the PropertyGrid ... at desgn time ... is using reflection to discover the attributes of controls or forms.

Well Visual Studio is doing that. I have no information to suggest that the PropertyGrid is doing it.

Perhaps I don't understand what you question is. Are you asking about the performance of it in Visual Studio or in your own app, which is what I thought.

I have used PropertyGrid extensively in many applications and have never seen the issue you describe.

Hi Led Mike,

At run-time, in my own app.

Suggest you do this :

1. create a simple WinForms project : add a PropertyGrid

2. throw several simple controls on the Main Form : like buttons, textbxes, checkboxes, labels.

3. in the form Load event loop through the controls of the form and add a click handler that will set the clicked control (except the Property Grid, of course) = the SelectedObject method of the PropertyGrid.

4. run the app : click one of the controls you've wired up with a Click event: the PropertyGrid will load soon enough, but note the long delays before you can select a property and edit it in the Property Grid.

5. Note that once you can select a property to edit in the Property Grid, reponse time for updating the actual controls is very fast.

best, Bill

"Many : not conversant with mathematical studies, imagine that because it [the Analytical Engine] is to give results in numerical notation, its processes must consequently be arithmetical, numerical, rather than algebraical and analytical. This is an error. The engine can arrange and combine numerical quantities as if they were letters or any other general symbols; and it fact it might bring out its results in algebraical notation, were provisions made accordingly." Ada, Countess Lovelace, 1844

I've just tested this on my system and the response feels instant - no noticable delays at all.

Dave
BTW, in software, hope and pray is not a viable strategy. (Luc Pattyn)
Visual Basic is not used by normal people so we're not covering it here. (Uncyclopedia)
Why are you using VB6? Do you hate yourself? (Christian Graus)

Ah, now I get what you are doing. Of course it is using Reflection please ignore my previous crazy sounding replies about that. Apparently I was suffering from idiocy there for a while.

As Davey says it runs instantly on my machine which is underpowered compared to yours. (.NET 3 VS2008)

Hi, Friends, thanks for so much for checking this out !

The only hypothesis I can think of at this point is : perhaps it's something to do with the Beta code in VS 2010 : extra levels of object/event checking, internal Form structure ?

About the only thing I haven't done is try instantiating the PropertyGrid at run-time rather than at design-time just to see if there's any difference in modal behavior.

I think I'll go ahead and report this as a "bug" to MS, so I can have the "fun" of getting multiple e-mail messages about how the issue is "closed" and they don't consider it an issue Smile | :)

The last one I reported was a completely incorrect statement in the MSDN documentation saying a certain control had a certain ability : I received about ten e-mails on this, none of which ever acknowledged the error in the documentation, or indicated any plans to edit it.

best, Bill

"Many : not conversant with mathematical studies, imagine that because it [the Analytical Engine] is to give results in numerical notation, its processes must consequently be arithmetical, numerical, rather than algebraical and analytical. This is an error. The engine can arrange and combine numerical quantities as if they were letters or any other general symbols; and it fact it might bring out its results in algebraical notation, were provisions made accordingly." Ada, Countess Lovelace, 1844

It's possible what you are seeing is related to performance concerns in VS2010 but to be more certain you should test the same solution in VS2008 as well.

Based on your other posts, it sounds like you are creating an application containing a property grid and, at runtime, displaying the public properties of a given UI element in the property grid.

No matter what, when you set the SelectedObject property of the property grid to any object the default behavior is for the property grid to reflectively bind to all public properties of that object. The only thing that changes this behavior is if the object implements ICustomTypeDescriptor and provides an implemenation of GetProperties() to return the "public" properties that are available.

Instantiating the property grid at "run-time rather than at design-time" will make absolutely no difference in it's behavior. Likewise, having the property grid in a dialog or on the main form will also have no difference.

I'm interested to know if you are doing this purely as a test project or actually have real-world scenario you are trying to solve with this implementation. We make heavy use of property grids at work, both letting the grid do it's own binding and informing the grid through GetProperties() and have had very few issues. (This is all in VS2008.)

Scott Dorman

Microsoft® MVP - Visual C# | MCPD
President - Tampa Bay IASA

[Blog][Articles][Forum Guidelines]

Hey, hey, hey. Don't be mean. We don't have to be mean because, remember, no matter where you go, there you are. - Buckaroo Banzai

Hi Scott,

Thanks for taking the time to reply !

Scott Dorman wrote:
to be more certain you should test the same solution in VS2008 as well.

As I mentioned in a previous post, right now my machine with VS2008 is "down."

Scott Dorman wrote:
Based on your other posts, it sounds like you are creating an application containing a property grid and, at runtime, displaying the public properties of a given UI element in the property grid.

Not exactly : there is an application with a kind of "virtual property grid" (using a TableLayoutPanel which can have one and one-only control per "virtual cell"). The PropertyGrid is used only to show, and allow editing of, the TableLayoutPanel itself, and any selected control within one of its "virtual cells." I say "virtual cells" because as you probably know the TableLayoutPanel exposes only Row and Column collections, not "cells."

Scott Dorman wrote:
if the object implements ICustomTypeDescriptor and provides an implemenation of GetProperties() to return the "public" properties that are available.

Very timely : I have just been studying the example in Matthew McDonald's Pro Win Forms 2.0 book where he demonstrates using ICustomTypeDescriptor to edit the properties which will appear in the PG.

Scott Dorman wrote:
Instantiating the property grid at "run-time rather than at design-time" will make absolutely no difference in it's behavior. Likewise, having the property grid in a dialog or on the main form will also have no difference.

Exactly the reason why this anomaly confounds me Smile | :)

Scott Dorman wrote:
I'm interested to know if you are doing this purely as a test project or actually have real-world scenario you are trying to solve with this implementation.

Both.

This is a one-off non-public little application whose job is to produce a translation into a subset of HTML (required as input for a 3rd. party control) of a Table of controls and content. It is much easier for me to build a "mock table" at design-time taking full advantage of the ability to set Dock and Anchor properties of controls and content within a "virtual cell" of a TableLayoutPanel, and then parse that into the required subset of HTML, than it is to "switch my head" into HTML and generate a complex table in which the values of many properties will have to be escape-quoted.

It's also typical of the way I study programming in that I pose problems to myself which require me to get hands-on with controls like the PropertyGrid and TableLayoutPanel, and write small, hopefully re-usable, utilities.

best, Bill

"Many : not conversant with mathematical studies, imagine that because it [the Analytical Engine] is to give results in numerical notation, its processes must consequently be arithmetical, numerical, rather than algebraical and analytical. This is an error. The engine can arrange and combine numerical quantities as if they were letters or any other general symbols; and it fact it might bring out its results in algebraical notation, were provisions made accordingly." Ada, Countess Lovelace, 1844

With VB 2008, I'm writing a text editor that resembles a word processor - 8.5 inch wide RichTextBox centered in the main window, etc. What I haven't figured out is how to place a vertical scrollbar on the right side of the window (instead of using the RTB's built-in scrollbar) and having it behave like the RTB's scrollbar.

Since the RTB's dock property is set to Fill (it's in a panel control), I can't really use the panel's autoscroll. I'd appreciate any pointers on how to make the VScrollBar, docked in the panel, function as the RTB's scrollbar.

Thanks!
AB

This article[^] does something similar although the GetScrollPos will fail with ERROR_NO_SCROLLBARS because your RichTextBox has ScrollBars set to none. The solution of your problem is probably by using Win32 API functions like GetScrollInfo[^], SetScrollInfo[^] ...
I never did something like this nor used these function before and I don't know if they will work or not. I'm not sure if you can achieve it using WPF but it may be easier.

Eslam Afifi

Hi I need to get Operating System name using c#
I am using vista OS.
In my app i used
System.Environment.OSVersion
but it shows Microsoft Windows NT 6.0.6001 Service Pack 1

plz help.

Thanks in advance....

Hi,

you can find a descriptive string in the registry at
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\ProductName

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

DISCLAIMER: this message may have been modified by others; it may no longer reflect what I intended, and may contain bad advice; use at your own risk and with extreme care.

myms.net wrote:
6.0.6001

This holds everything. There is a page in MSDN which reveals the meaning of the number. (I dont have the link right now but it does exists).

जय हिंद

d@nish's reply above prompted me to have a search for a way to do this without using the registry (nasty on vista especially). Came up with this quickie which could do with some work, but does the job.

public static string GetOSFriendlyName()
{
    string result = string.Empty;
    ManagementObjectSearcher searcher = new ManagementObjectSearcher("SELECT Caption FROM Win32_OperatingSystem");
    foreach (ManagementObject os in searcher.Get())
    {
        result = os["Caption"].ToString();
        break;
    }
    return result;
}

[Edit] reguires a reference to System.Management and the same adding to your usings [/Edit]
[Edit2] Useful reference MSDN[^] [/Edit2]

Dave
BTW, in software, hope and pray is not a viable strategy. (Luc Pattyn)
Visual Basic is not used by normal people so we're not covering it here. (Uncyclopedia)
Why are you using VB6? Do you hate yourself? (Christian Graus)

This[^] is what I was speaking of.

जय हिंद