|
if I use the DataSet or DataTable through SQL queries and assign the result to gridview.DataSource my program running very good but I want to learn writing Stored Procedures, Stored procedure writing I heard the program will run faster. you did not understand my question
|
|
|
|
|
That's because you asked the wrong question!
What you have created is an SQL Function, not an SQL Stored Procedure, and the two are very different things.
Stored Procedures are designed to be used from inside or outside SQL, while Functions are meant to be internal - it's like the difference between a class and it's private methods.
So delete your function (or there will be problems as the name is in use) and create this as a Stored Procedure instead of a Function:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE LISTSTAFF
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM tblStaff
END
GO And your original code will give no error.
It won't display anything, either, probably - since the Connection will need to be open while the gridview loads from the reader and that's bad practice. I'd use the DataAdapter way if you want a DataSource rather a DataReader.
Those who fail to learn history are doomed to repeat it. --- George Santayana (December 16, 1863 – September 26, 1952)
Those who fail to clear history are doomed to explain it. --- OriginalGriff (February 24, 1959 – ∞)
|
|
|
|
|
I think the procedure can not be used because the procedure does not return a value and assign that value into gridview.DataSource. If you say the procedure is used, you can post your templates or my examples of how you edit ?
|
|
|
|
|
Member 2458467 wrote: procedure does not return a value
Um.
How do I put this? That turns out not to be the case...
A stored procedure is a collection of SQL statements, and so acts just as if you had submitted the statements as your SQL Command. So if you use SELECT, it "returns" the selected rows in just the same was as if you had used the SELECT statement directly instead of executing the stored procedure. Except it accepts parameters better, in that it works with OUTPUT parameters which it can set to specific values are return to your code via the Parameters collection of the SqlCommand in your C# code.
Trust me on this: Stored Procedures are used to "simplify" complicated SQL commands all the time: and that means returning result sets!
Try it: you will see what I mean. (But use a DataAdapter instead of a DataReader: you need the whole information available in order to use a DataSource correctly).
Those who fail to learn history are doomed to repeat it. --- George Santayana (December 16, 1863 – September 26, 1952)
Those who fail to clear history are doomed to explain it. --- OriginalGriff (February 24, 1959 – ∞)
|
|
|
|
|
Thank you for help me, I did it.
|
|
|
|
|
You're welcome!
Those who fail to learn history are doomed to repeat it. --- George Santayana (December 16, 1863 – September 26, 1952)
Those who fail to clear history are doomed to explain it. --- OriginalGriff (February 24, 1959 – ∞)
|
|
|
|
|
I've never called a function from ADO.NET, at least not directly, so I've learned something today! You need to mod what I'm about to say next through my lack of knpowledge:
Anyway when looking into this this SO post[^] seemed relevant - you need to add a parameter for the return value.
I don't know why you are creating a function (CREATE FUNCTION [dbo].[LISTSTAFF]() ) rather than an stored proc, but you'd more than likely need to assign the output parameter required to the data source rather than the result of the execute statement? I actually don't know on this part because as I say I've never really used a function before.
The other thing to do is take a step back and look at what you are doing - using a function in this context is odd, you are selecting a whole table, so I'd assume an stored procedure would be better (or even possibly querying a view depending on your requirements).
|
|
|
|
|
Try executing a "SELECT * FROM [LISTSTAFF]".
Should simply not be wrapped in a function. Shouldn't even be wrapped in a sproc, unless there's a reason to (something that adds value for either you or the enduser).
Member 2458467 wrote: gridview.DataSource =... how assign ? Try the manual[^], you're not the first to try and load data in a list.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
you've not ever written Store procedure.
|
|
|
|
|
follow you write stores procedues how to assign gridview.DataSource ?
|
|
|
|
|
1. Whenever you get an error and you are asking for help, post the error, since there are thousands of possible errors.
2. new SqlCommand("LISTSTAFF", cnn); - this is your problem. It is not a stored procedure. It is a function. You even posted, CREATE FUNCTION...
To select from function you do SELECT field1, field2 FROM dbo.LISTSTAFF()
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
you probably never use stored procedures with writing CREATE FUNCTION [DBO].[LISTSTAFF]()
|
|
|
|
|
You can create a function in a stored procedure, sure, but I don't know why you would want to. However, in this case creating a function in the Stored Procedure does not do anything unless you then call SELECT * FROM dbo.Function().
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
I do not write according to a DataSet or DataTable, this writing style I would use, I heard stores Procedures writing style program will run faster, secure, more... should I try writing style stores Procedures. follow you write Stores Procedures how to assign values to gridview.DataSource ? if you write that you have to answer any of my eye.
|
|
|
|
|
I don't understand what you are asking. However, stored procedures do ten to execute faster because they can already be compiled. But you don't want to create a stored procedure that creates a function. Either use a stored procedure or a function.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
Thank you for help me, I did it. but I cryptichard (don't understand) declare procedures CREATE PROCEDURE return value list, while I declare CREATE FUNCTION don't return list or table.
|
|
|
|
|
Since a WinForm's (in the standard case where the WinForm Parent == null) Bounds are in screen-co-ordinates, I would expect using RectangleToScreen on the Form's Bounds would give the same result as just inspecting the Bounds, but, this is not true:
> ? this.Bounds
{X = 230 Y = 84 Width = 900 Height = 600}
> ? RectangleToScreen(this.Bounds)
{X = 469 Y = 200 Width = 900 Height = 600}
curiously, Bill
“I'm an artist: it's self evident that word implies looking for something all the time without ever finding it in full. It is the opposite of saying : ‘I know all about it. I've already found it.’
As far as I'm concerned, the word means: ‘I am looking. I am hunting for it. I am deeply involved.’” Vincent Van Gogh
|
|
|
|
|
Why Bill?
RectangleToScreen[^] converts Client coordinates to Screen values, and the Bounds[^] property is relative to it's Parent container. So if you try to convert Bounds with no Parent, you are converting Screen Coords to Screen coords, and it all goes agly.
Those who fail to learn history are doomed to repeat it. --- George Santayana (December 16, 1863 – September 26, 1952)
Those who fail to clear history are doomed to explain it. --- OriginalGriff (February 24, 1959 – ∞)
|
|
|
|
|
Brother OriginalGriff, I am consoled by your kind words, but the irritating thought remains, like a spiny-burr in my sandal, that translating screen co-ordinates to screen co-ordinates should return (drum roll) ... screen co-ordinates.
However, mea culpa, I forgot, for a moment, that programming is a form of the Mad Hatter's Tea Party.
cheers, Bill
“I'm an artist: it's self evident that word implies looking for something all the time without ever finding it in full. It is the opposite of saying : ‘I know all about it. I've already found it.’
As far as I'm concerned, the word means: ‘I am looking. I am hunting for it. I am deeply involved.’” Vincent Van Gogh
|
|
|
|
|
Translating screen co-ords to screen co-ords does work - but you aren't.
What you are doing is passing co-ords to a method expecting client co-ords and telling it they are relative to the client (when they are actually relative to it's parent, the desktop) - so it offsets them by the client location in it's parent, as it's supposed to do.
OT: How's Thailand these days? We have nothing on our news due to some silly WendyBall contest in Rio.
Those who fail to learn history are doomed to repeat it. --- George Santayana (December 16, 1863 – September 26, 1952)
Those who fail to clear history are doomed to explain it. --- OriginalGriff (February 24, 1959 – ∞)
|
|
|
|
|
BillWoodruff wrote: translating screen co-ordinates to screen co-ordinates should return (drum roll) ... screen co-ordinates. And how does RectangleToScreen guess that the rectangle you have passed in is actually screen relative and not client relative?
|
|
|
|
|
hi all people
i have a windows form that containes two ComboBoxes, two DataGrids and TextBoxes, i made the necessary to bind all the controls except the second ComboBox (That depends on what is selected in the first ComboBox ), infact i filled the first ComboBox cbCust with the table Customers and i want the second ComboBox cbOrd to be filled with the Orders of the selected Customer. my hole Code is :
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
namespace DataBinding
{
public class MasterDetail : System.Windows.Forms.Form
{
private System.Windows.Forms.DataGrid grdOrders;
private System.Windows.Forms.DataGrid grdOrderDetails;
private System.Windows.Forms.Label label1;
private System.Windows.Forms.Label label2;
private System.Windows.Forms.Label label3;
private System.Windows.Forms.ComboBox cbCust;
private System.Windows.Forms.TextBox txtPhoneNo;
private System.Windows.Forms.TextBox txtFaxNo;
private System.Windows.Forms.Label label4;
private System.Windows.Forms.TextBox txtContact;
private System.ComponentModel.Container components = null;
private System.Windows.Forms.GroupBox groupBox1;
private System.Windows.Forms.GroupBox groupBox2;
private System.Windows.Forms.GroupBox groupBox3;
private System.Windows.Forms.Button btnPrev;
private System.Windows.Forms.Button btnNext;
OleDbConnection cn;
DataSet ds;
private Label label5;
private ComboBox cbOrd;
DataViewManager dsView;
public MasterDetail()
{
InitializeComponent();
cn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\DataBinding\Northwind.accdb;Persist Security Info=False");
ds = new DataSet("CustOrders");
OleDbDataAdapter da1 = new OleDbDataAdapter("SELECT * FROM Customers", cn);
da1.TableMappings.Add("Table","Customers");
da1.Fill(ds);
OleDbDataAdapter da2 = new OleDbDataAdapter("SELECT * FROM Orders", cn);
da2.TableMappings.Add("Table","Orders");
da2.Fill(ds);
OleDbDataAdapter da3 = new OleDbDataAdapter("SELECT * FROM [Order Details]", cn);
da3.TableMappings.Add("Table","OrderDetails");
da3.Fill(ds);
System.Data.DataRelation relCustOrd;
System.Data.DataColumn colMaster1;
System.Data.DataColumn colDetail1;
colMaster1 = ds.Tables["Customers"].Columns["CustomerID"];
colDetail1 = ds.Tables["Orders"].Columns["CustomerID"];
relCustOrd = new System.Data.DataRelation("RelCustOrd",colMaster1,colDetail1);
ds.Relations.Add(relCustOrd);
System.Data.DataRelation relOrdDet;
System.Data.DataColumn colMaster2;
System.Data.DataColumn colDetail2;
colMaster2 = ds.Tables["Orders"].Columns["OrderID"];
colDetail2 = ds.Tables["OrderDetails"].Columns["OrderID"];
relOrdDet = new System.Data.DataRelation("RelOrdDet",colMaster2,colDetail2);
ds.Relations.Add(relOrdDet);
dsView = ds.DefaultViewManager;
grdOrders.DataSource = dsView;
grdOrders.DataMember = "Customers.RelCustOrd";
grdOrderDetails.DataSource = dsView;
grdOrderDetails.DataMember = "Customers.RelCustOrd.RelOrdDet";
cbCust.BindingContext = new BindingContext();
cbCust.DataSource = dsView;
cbCust.DisplayMember = "Customers.CompanyName";
cbCust.ValueMember = "Customers.CustomerID";
cbOrd.DataSource = dsView;
cbOrd.DisplayMember = "Customers.RelCustOrd";
cbOrd.ValueMember = "Orders.OrderID";
txtContact.DataBindings.Add("Text",dsView,"Customers.ContactName");
txtPhoneNo.DataBindings.Add("Text",dsView,"Customers.Phone");
txtFaxNo.DataBindings.Add("Text",dsView,"Customers.Fax");
}
private void btnPrev_Click(object sender, System.EventArgs e)
{
if (this.BindingContext[dsView,"Customers"].Position > 0)
{
this.BindingContext[dsView,"Customers"].Position--;
}
}
private void btnNext_Click(object sender, System.EventArgs e)
{
CurrencyManager cm = (CurrencyManager)this.BindingContext[dsView,"Customers"];
if (cm.Position < cm.Count - 1)
{
cm.Position++;
}
}
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
my question is : How to do to fill the cbOrd ComboBox using the DataBinding?
Thanks in advance.
modified 19-Jun-14 15:31pm.
|
|
|
|
|
sbn1010 wrote: How to do to fill the cbOrd ComboBox using the DataBinding? Bind it when the selection of the other combo changes. There must be some "SelectionChanged" or "SelectedValueChanged" event
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
I want to write a program that I would upload an excel file into and analyze the data. Basically, my framework will be read, extract, and output in form of text or pdf.
Where do I begin?
|
|
|
|
|
If you only need to support the new format files (*.xlsx ), then either EPPlus[^] or ClosedXML[^] should allow you to read the file.
If you need to support the older format (*.xls ) as well, then you can use NPOI[^].
If you're writing a web application, avoid using Office Interop; it's not supported in non-interactive programs[^], and will fail in interesting ways.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|