|
CREATE PROC up_MyTotal
@id INT
AS
select sum(value1) as value1_total where customer = @id
The call teh stored proc twice
|
|
|
|
|
SELECT customer, sum(value1)<br />
FROM my_table<br />
GROUP BY customer
or
SELECT customer, sum(value1)<br />
FROM my_table<br />
WHERE customer IN ('1111', '9999')<br />
GROUP BY customer
|
|
|
|
|
I use a DataView to sort and filter information. How do I get specfic cell out of the DataView? For example I use the DataRowView to display the entire Row. The row appears like this 1 5 10 7 9. Each number represents a different column. I need to get each number indivdually and pass it to a local variable I have created.
myId = 1.
myOtherId = 5.
Any ideas.
thanks
|
|
|
|
|
I want to select some information from one server and join it to another servers information in a query. Is this Possible?
QMuffs
|
|
|
|
|
|
Worked great. Thank you for your time.
QMuffs
|
|
|
|
|
I need help constructing a query for my database application. Please use notepad to view the text file below for the problem's description. Thanks.
http://www.nghiep.com/sqlhelp.txt[^]
|
|
|
|
|
Just a small modification (in bold) to your second query:
<br />
Select e.Event_ID, e.Event_Date, p.Prod_Name from EVENT e, PRODUCT p, EVENTS_PRODUCTS eps<br />
where e.Event_ID = eps.Event_ID and eps.Prod_ID = p.Prod_ID<br />
order by e.Event_ID p.Prod_Name DESC, e.Event_Date DESC<br />
Then the grouping of data can be done in the code that renders the HTML. (Iterate through the results, and create a "group-seperator" only when Event_ID changes...)
|
|
|
|
|
Thanks for trying help...but I think your example would only sort the Prod_Names WITHIN each Event_ID group. But still, at the highest level, the table is sorted by Event_ID, not Prod_Name. I do not want that. I need the table to be sort by Product names.
|
|
|
|
|
Ah... So I need to actually READ your text...
Sorry, my fault...
|
|
|
|
|
i have a perl script file and i have to convert it into the sqlserver2000.;)
puneet
|
|
|
|
|
Hi, please forgive my absolute stupidity. I have been using Access databases for ages, and started using SQL recently.
What is the best way to join tables. I have one table for user details, one of the fields is a bit. Depending on true or false, this will then use one of two other tables. How do you link these? Would I create a uniqueId in the users table, and then use something like a nvarchar to set up a relationship. Bearing in mind, these additional two tables, could hold multiple records of the same user. However, a user cannot appear in both these additional tables.
If I did it the way mentioned above, should i also have an additional uniqueId specific to a record?
|
|
|
|
|
Here's an example:
create table parent(id int primary key, somecolumnnameA varchar(200))<br />
create table child1(id int primary key, parentid int foreign key references parent(id), somecolumnnameB varchar(200))
Generally, you need to have the same datatypes in both the parent and the child tables if you're going to create relationships between them. The parent will need a primary key, and the child will need a foreign key that references the parent's primary key.
|
|
|
|
|
I have a custom filter with a checked listbox and i need to fill it with all the unique values from a specified column in System.Data.DataTable. What is the most efficent way to do this?
TIA,
Peter
|
|
|
|
|
|
I've tried the "select TABLE_NAME from INFORMATION_SCHEMA.TABLES" and tried the sysobjects route too, but the DSN I am using (ODBC) reports that no such table exists within the given database. The DB itself is a MySQL database. I don't want to use any non-standard queries because the code must work across multiple datasources.
Then I went the catalog route and tried:
SQLTables(hstmt3, NULL, 0, NULL, 0, "Orders", SQL_NTS, NULL, 0);
and I get a compile error about param 6 ("Orders") being of the incorrect type - even though I copied and pasted it directly from MSDN.
What should I do to get a listing of the tables in a given ODBC DSN?
|
|
|
|
|
Hey,
Are there any predefined functions in .NET to encode user input for SQL statement compatablity (ie. change "'" to "\'", etc)?
So: "Sam's cat died." would become "Sam\'s cat died."
Thanks,
Adam
-- Adam
"If you can't beat your computer in chess, try kickboxing"
|
|
|
|
|
You should avoid encoding user input into your SQL queries. This is a major security hole:
SELECT MyColumn FROM MyTable WHERE MyOtherColumn={1}
can become
SELECT MyColumn FROM MyTable WHERE MyOtherColumn=0; DELETE FROM MyTable
A better solution is to use stored procedures and pass user input as their parameters.
Alexandre Kojevnikov
MCAD charter member
Leuven, Belgium
|
|
|
|
|
If you are using SqlCommand's or DataAdpaters, you can use parameters for your values and it will automatically encode them for you. When you specify you DataAdpater query you can put:
SELECT au_id, au_lname, au_fname
from authors
where au_fname = @au_fname
Then in your command:
myCmd.Parameters["@au_fname"].value = "'any'''input";
And all should be well.
Another approach is to simply to a string replace on your input replacing all single quotes with two single quotes.
Rocky Moore <><
|
|
|
|
|
I have a form that queries my database and returns some values on the forms textboxes. On my laptop it returns for instance a value of 82 but it i load it on another machine the value of 82 shows as 82.000000 in that users textbox. what gives?
Chris
|
|
|
|
|
I am writing an ATL component to do some data access using ATO. I want to pass a _RecordsetPtr pointer or a _ConnectionPtr to a ATL function. I use code like this:
<br />
<br />
STDMETHODIMP CMyAtlObject::ReadData(LPDISPATCH pAdoRecordset, BOOL *pbSuccess)<br />
{<br />
_RecordsetPtr pRecordsetPtr;<br />
<br />
if (FAILED(pAdoRecordset->QueryInterface(IID_IDispatch, (void**) &pRecordsetPtr)))<br />
{<br />
return S_FALSE;<br />
}<br />
<br />
<br />
<br />
<br />
return S_OK;<br />
}<br />
<br />
But this doesn't work. The recordset pointer gets closed during the function call. If I pass an _ConnectionPtr as LPDISPATCH, the pointer becomes invalid.
So how can I pass valid ADO _RecordsetPtr's and _ConnectionPtr's to an ATL Object?
|
|
|
|
|
Hi,
I am trying to query some data from a SQL Server 2000 database, then change the connection and batch update that data into another (but identical in schema) SQL Server 2000 database (some records are different, some new), but nothing happens.
I'm sure it's related to the fact that no data has changed in the ADO recordset means it doesn't think a BatchUpdate is worth it.
Is there a way to make it go back to the database anyway?
Thanks,
Simon
|
|
|
|
|
Can anyone tell me how to connect to fox database (dbf file) from C#? I tried to use odbcDataAdapter but I could not access data in my dbf file.
Maybe i did something wrong or is there any other normal way how to retrieve data from dbf file?
thanx a lot...
|
|
|
|
|
Here's how we do it using an OleDB provider for VFP:
<br />
using System.Data.OleDB;<br />
<br />
private void FillDataSet()<br />
{<br />
<ul><br />
OleDbConnection foxConn = new OleDbConnection(<br />
"Provider=VFPOLEDB.1;Data Source=C:\\DataDirectory");<br />
OleDbCommand foxCommand = new OleDbCommand();<br />
OleDbDataAdapter foxDA = new OleDbDataAdapter();<br />
DataSet foxData = new DataSet();<br />
<br />
foxCommand.Connection = foxConn;<br />
foxCommand.CommandType = CommandType.Text;<br />
foxCommand.CommandText = "SELECT * FROM myTable";<br />
<br />
foxDA.SelectCommand = foxCommand;<br />
foxDA.Fill(foxData, "MyTable");<br />
</ul><br />
}<br />
|
|
|
|
|
Hello, I've run into something strange, I have the following SQL parameter (c#):
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@_rate", System.Data.SqlDbType.Float));
This is for a simple stored procedure that updates a float value in a SQL server 2000 (MSDE) table.
The following line of code:
cmd.Parameters["@_rate"].Value=1.33F;
Results in 1.33000004291534 being stored in the record.
It's obviously a binary rounding issue, but I can't seem to pin it down, I honestly don't think it should be doing that.
Is it my parameter definition or...?
------------
Tomorrow is the same day as today was yesterday.
|
|
|
|