|
I have searched hi and low for my answer with no success. The dataadapter design gives you a way to create multiple queries. (Fill, Fillby, Update, etc.) But, I don't want to use the designer. I want to code it all. I have the fill. That was easy. How do I create a query method using code.
Reason:
In one part on my program I am getting data from a database and binding it to a datagrid. At one point I want only certain records to show. At other times I want all records to show.
I want to be able to code like this:
if certainFlag = "true" then
adapter.fill(datatable)
else
adapter.allfill(datatable)
endif
How would I code the allfill query?
|
|
|
|
|
You would normally write this as two seperate methods, each returning their own specific set of columns from the table.
You already know how to do this because you have all the code written in your other post, other than the appropriate SQL SELECT statement.
|
|
|
|
|
So, how do I distinguish between fill and allfill with different sql statements but, the same adapter?
This is how I am defining my adapters.
Dim adapter As OleDbDataAdapter = New OleDbDataAdapter(sql, connectionstr)
So my question is how does my program know the difference between adapter.fill(datatable) gets everything in the database table with a zero in a certain column and adapter.allfill(datatable) that gets all data?
|
|
|
|
|
Cory Kimble wrote: So, how do I distinguish between fill and allfill with different sql statements but, the same adapter?
I don't get where the problem is. You write two methods. One that returns the columns you want based on a condition, and one that returns the columns you want without a condition. There shouldn't be a flag anywhere that tells a single method which statement to execute.
Or, are you asking how to you write the SQL statement to return all the columns based on a condition??
Or are you asking how you're going to know which method to call??
|
|
|
|
|
I am trying to code a query method that will do what the fill method does but I don't understand how to do that. I understand that the fill method is being overridden, right? Using the designer I can change that name (fill) to what ever I want and using code adapter.somename(sometable) I can do a sql statment that I earlier would of set to the adapter like this. dim adapter as oledbadapter = new oledbadapter(select, connection)
So I have one adapter with a sql statement saved to the select variable using the connection saved in the connection variable. If I code adapter.fill(datatable) it will run the sql statement saved in the select variable. I don't understand how I get another method and what I would need. How would it look? I am lost. What I need is help on what the method would look like. I hope you can help.
|
|
|
|
|
All you need to do is fill a table and return it. It sounds like you're making this much harder than it needs to be. You can right-click the dataset in the designer and pick View Code (I think that's what it is!) and you'll get a partial class code window opened up. That's where you can put your code.
I've got an idea. Watch the "Forms over Data Video Series" here[^]. It covers a bunch of what you're asking about, though the titles of each video don't appear to have anything to do with what you're asking. In these videos, you'll actually see how to add queries to datasets.
|
|
|
|
|
Thank you for the link but, I don't want to use the designer. Let me elaborate what my program is doing.
My program has the ability to communicate with a database and transfer data back and forth. I don't want to hard code the connection and database name in my program. I want some using MS Access or SQL server to use my program. The way I have solved this is I created a form that can grab the database, display the tables to choose from, and listed all fields to choose from in order to work properly with my program. Since adapters and connections work differently with MS Access databases and SQLServer databases, I came up with the Idea to code these aspects(adapters and connection) in my code so I can during runtime decide what database to choose, what connection to choose, what sql statements to use depending on the database chosen (Access or SQL). The Knowledge that I am lacking is How to create and make work an adapter with multiple queries using only code and not the designer, since I want the program to create at runtime.
I need examples on how to do the above. I thought creating a command would work, but again I can't find any examples that take me through all step to the end to see if commands is what I need. So...
If I have an adapter named myadapter
Dim myadapterOle as oledbDataAdapter()<br />
dim myadaptersql as sqlDataAdapter()
'Now I want to create Connections 'I am using two different sql statements to show you the different databases I will use.
dim sql = "Some select statement WHERE NAME = "Joe")<br />
dim ole = "Some select statement WHERE NAME = "Joe")<br />
dim sqlALL = "Some select statement)<br />
dim ole = "Some select, update, Insert statement)<br />
dim connection = cmbobxConnection.text 'Got connection from a dropdown box<br />
myadatpersql = new sqlDataAdapter(sql, connection)<br />
myadapterOle = new oledbdataAdapter(sql, connection)
Now I have an adapter that when I do myadptersql.fill(table) It will perform the sql select statement. How do I call the sqlALL statement. Do I have to create a new Adapter each time the sql statements change?
I hope you can help me. The designer might work If I create an adapter without a connection. Then I can fill in that value during runtime. Is that possible?
|
|
|
|
|
I thought you were adding more methods to an existing designer generated dataset.
If you're writing your own data classes from scratch, you've taken on a lot of work. Normally, to implement data layers for different databases, you would create an Interface that spelled out what each version of the class (1 for SQL Server and 1 for Access) would need to implement so your code can use either class without knowing which one it was using. Everything is done through the Interface.
At a basic level, your interface would probably define at least a couple properties, like ConnectionString (get and set) and ConnectionStatus, and a few top level methods, like GetDatabases, GetTables, GetColumns, GetStoredProcs, ...
|
|
|
|
|
I found my answer. I needed commands.
|
|
|
|
|
I am trying to get table names from different types of databases. I have successfully acquired table names from a MS Access database. Now I am writing code to get table names from a SQL server database. I am having trouble and don't know what I am doing wrong. Can someone help me. The only thing I can find online about this is the Select statements and not all that I need.
Code:
Dim databasestruct As Database<br />
Dim connectionstring As String<br />
Public radioDataset As DataSet = New DataSet<br />
Public dt As DataTable = New DataTable("table") <br />
<br />
<br />
connectionstring = "Data Source=.\SQLEXPRESS;AttachDbFilename=" + database + ";Integrated Security=True;Connect Timeout=30;User Instance=True"<br />
<br />
Dim connection As New SqlClient.SqlConnection(connectionstring)<br />
<br />
connection.Open()<br />
<br />
Dim str As String = "SELECT name FROM(dbo.sysobjects)WHERE xtype = 'U'"<br />
Dim adapt As SqlDataAdapter = New SqlDataAdapter(str, connection)<br />
radioDataset.Tables.Add(dt)<br />
adapt.Fill(dt)
When I run this I get the following error on the last line. Incorrect syntax near ')'
What am I doing wrong?
|
|
|
|
|
The select statement should be:
SELECT name FROM dbo.sysobjects WHERE xtype = 'U'
Note the space between FROM and dbo.sysobjects and between dbo.sysobjects and WHERE.
SQL is not forgiving about missing spaces.
|
|
|
|
|
vb.net/visual studio 2003
i put a picturebox into a panel1 with panel1.autoscroll=true.
now, whenever the user moves the scrollbars of this panel1, i want a second panel besides this panel1 to change its location according to panel1's autoscrollposition. how do i do this? there seems to be no event for change of autoscrollposition.
|
|
|
|
|
Your description isn't very clear. You've got TWO panel controls, I'm assuming, side-by-side on the form, and you want when one panel is scrolled, the other should also scroll to the same position?? What does the PictureBox has to do with this?
|
|
|
|
|
in the picturebox i am drawing a bitmap on which i paint my data. the picturebox's size is the same as the bitmap. the picturebox is in panel1 (with autoscroll=true) to allow the user to scroll the picture since it is bigger then the screen. that works all fine.
but:
in the second panel besides the one holding the picturebox i am painting some stuff that is related to the stuff in the picturebox an should therefor always be seen on the same height as the picturebox.
like in excel where you can fix some columns on the left, and when you scroll to the right, those fixed columns will stay in place to show e.g. the rows' name/number/whatever.
but my bitmap is also taller than the screen, and i want the side-panel, on which i paint these rows' names, to change its height (think it is better to repaint the names on the new height) according to panel1.autoscrollposition's y-value.
--> but to repaint the content of this panel on the right position i need an event to trigger this repaint, an event that catches when the user fiddles around with panel1's scrollbar.
so just this event is what i need.
|
|
|
|
|
If I understand you correctly, all you would have to do is handle the Scroll event of the 1st panel and make any changes you need to the values that the 2nd panel's painting code uses to show it's contents.
|
|
|
|
|
yeah, that's what i want to do. but i don't know how to handle the scroll-event. can't find a scroll-event.
|
|
|
|
|
klappklapp wrote: but i don't know how to handle the scroll-event. can't find a scroll-event.
There are two ways to have Visual Studio create the framework of the event handler for you.
The first is to open up the form designer and click on your 1st panel. Then, go to the Properties window and click on the little yellow lightning bolt to show the available events for the panel. Double-click on line that says "Scroll".
The second method is to open up the code editor for the form your panel is on. At the top of the editor window, just under the window tabs, there are two drop down boxes. In the left drop down, click on your 1st panel object. Then in the right drop down, you'll find all the available events for the panel listed. Just click on "Scroll"...
|
|
|
|
|
i know, how to create an event's framework.
but there is no scroll-event for panels in visual studio 2003! or am i dumb or blind or something?
|
|
|
|
|
ok, think i found out myself. 
thanks anyway.
simply created a new class which inherits from panel, create a public event "scroll", override wndproc, catch WM_VSCROLL and WM_HSCROLL, and raise the scroll event.
|
|
|
|
|
klappklapp wrote: but there is no scroll-event for panels in visual studio 2003!
I didn't notice that you said that in your original post. No, the 2003 version of the Panel doesn't have a Scroll event, you what you did was correct. Create your own version of the Panel, adding support to look for Scroll messages.
|
|
|
|
|
I wonder if anyone can help me with the following problem.
I am trying to update a grid element within a array
e.g.grdGridArea.Item(x - 1, 6) = 50
This element was added in another procedure using InsertAt.
The array is populated no problems until I get to this element where I then get the following error;
Run-time exception thrown : system.data.dataexception.
This is driving me nuts so is there anyone that can tell me how this is happening then fix it
It is properly something simple.
|
|
|
|
|
You've given the exception type, but you failed to tell us what the exception's Message is. Without that, it's pretty difficult to tell you anything useful.
|
|
|
|
|
I am getting an err.number = 5
The problem is when I try and assign a double value to the element I get this err number. Works fine with the others but crashes as above.
Hope this help.
|
|
|
|
|
Got the following error message....
An unhandled exception of type 'System.Data.DataException' occurred in system.data.dll
|
|
|
|
|
None of this is the contents of the Message property of the exception you're getting.
Err is the old VB's error object, that's just about useless. You should NOT be using ON ERROR .... in your VB.NET code. You should be using Try/Catch blocks instead. Comment out the ON ERROR .... lines in your code so you can see a proper exception message.
On top of that, how about posting the code snippet that throws the exception.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
