How To Use Aliases In Typed Dataset
Jun 27, 2007I have a SELECT query with an alias in it.
The intellisense shows all field except the alias one.
What goes wrong?
ADVERTISEMENT
Parameter For Where In (@Par1) In Typed Dataset
Aug 21, 2007Hi,
Is there any work around to passing a set of strings to a parameter in a Typed Dataset for example I am pasing '4226222172004','4212012182004' which I build on my code the number of items will vary passed on the user selection but since the Typed Dataset uses sp_executesql and the parameters are change to '''4226222172004'',''4212012182004'''
Any ideas how I can format the Parameter I am passing.so that it will end like where in ( '4226222172004','4212012182004') instead of where in ('''4226222172004'',''4212012182004''') I again the number of parameters will very.
Thanks
Julio D
Strongly Typed Dataset To SQL File
Nov 23, 2004I have a strongly-typed that I fill w/in my app, then use the dataset to insert rows into a SQL table.
3 fields look like this:
RegHrs - decimal
OTHrs - decimal
TotHrs - decimal
When I insert the fields using parameters, the params look like this:
paramValues(10) = New SqlParameter("@RegHrs", SqlDbType.Decimal)
paramValues(11) = New SqlParameter("@OTHrs", SqlDbType.Decimal)
paramValues(12) = New SqlParameter("@TotHrs", SqlDbType.Decimal)
The fields in the SQL table are defined as:
RegHrs - decimal (9,2)
OTHrs - decimal (9,2)
TotHrs - decimal (9,2)
Before I insert the row (from w/in the VB code), I stop the code and verify that the values being placed into the params are:
5.85
0
5.85
After the SQL insertion, the values w/in the SQL Table contain:
6
0
6
What am I missing?????
Thanx
JerryK
Typed Dataset Failed To Enable Constraints
Sep 11, 2006Hi, I am trying to use a typed dataset created using the Query builder which returns the data correctly when I use 'Execute Query' in query builder but as soon as I attempt to return a dataset using the GetData method created I get the following error: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints. Which is not very helpful, any ideas where the problem may be? I have tried switching off enforce constraints and setting the NullValue property of all strings to 'Emtpy' bu that has no effect. Thanks in advance
View 2 Replies View RelatedTyped Dataset Schema Throwing Error
Apr 8, 2006Hello,I'm using a typed dataset to access my database (SQL Express); I have a table that returns data, which two fields are null; I'm getting this error:{"Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."}There are no foreign-key or unique key constraints, so it has to be a non-null issue; I changed the two fields in the table to have a null value of Empty instead of Throw Exception, but I don't understand what the problem is coming from... I'm at a loss.Thanks.
View 2 Replies View RelatedUser-defined Types (UDT) In Typed Dataset
Dec 24, 2006Hi!
I'm need to use typed Dataset with CLR UDT, but when I'm trying to create TableAdapter in Dataset designer, I'm gettng error message: User-defined data types are not supported in DataSet designer
Is any way to get it working?
My UDT defined in separate assembly as:
[Serializable]
[XmlRoot(Namespace = NS)]
[StructLayout(LayoutKind.Sequential)]
[SqlUserDefinedType(Format.Native, Name = DataType, IsByteOrdered = true, ValidationMethodName = "Validate")]
public struct Coordinate
: INullable,
IXmlSerializable
{
.....
}
Working environment: Visual Studio 2005 SP1, MS SQL 2005 SP1 + Hotfix.
Insert Unicode Data Into The Database With Typed DataSet
Dec 11, 2006Hi all,I am using a Strongly Typed DataSet (ASP.NET 2.0) to insert new data into a SQL Server 2000 database, types of some fields in db are nvarchar. All thing work fine except I can not insert unicode data(Vietnamese language) into db.I can't find where to put prefix N. Please help me!!!
View 1 Replies View RelatedGenerating Strong Typed DataSet From An SP That Returns Two Tables.
Dec 26, 2007Hi,
I have a little question. I searched google, and could not find good answer for this one.
I have a stored procedure that returns two tables. Usually I generate a dataset out of a stored procedure by dragging it to the dataset.
When I drag this one it creates a DS with only one table, the first one. How can I make it use both tables?
Thank you.
CE 3.5, VS 2008, Typed Dataset: Get The Updated Identity Of Inserted Row
Sep 15, 2007
Hello,
Using VS 2008 Beta 2, SQL CE 3.5, on desktop, and Typed Datasets: The INSERT command of dataset table adapter does not return the updated identity of inserted row. Why?
also every time I want to modify the insert command to return the updated identity of inserted row, i get the error: "Unable to parse query text."
(Should I post this in Orcas forum?!)
Regards,
Parham.
SQL Server CE 3.5, Typed Dataset, Retrieving @@IDENTITY Of Inserts?
Mar 19, 2008I'm at loss how I'm supposed to work with typed datasets and Sql Server Compact 3.5, when inserting records and I need to update my datatables with the primary key of newly inserted rows.
I've tried adding a RowUpdated handler to all tableadapters that look like this:
Code Snippet
void Adapter_RowUpdated(object sender, System.Data.SqlServerCe.SqlCeRowUpdatedEventArgs e) {
if(e.Status == UpdateStatus.Continue && e.StatementType == StatementType.Insert) {
if(e.Row.Table.PrimaryKey.Length > 0) {
SqlCeCommand selectIdentityCommand = new SqlCeCommand("SELECT @@IDENTITY",e.Command.Connection);
if(e.Command.Connection.State == ConnectionState.Open) {
e.Row[e.Row.Table.PrimaryKey[0].Ordinal] = (int)selectIdentityCommand.ExecuteScalar();
}
}
}
I've previously used this type of approach when working with an OleDbDatabase, which works just fine. But it doesn't work with Sql Server CE 3.5, and since it doesn't support stored procedures I can't fix it that way either. And it doesn't support commands in a batch (i.e appending the Insert command of the adapter with ";SELECT @@IDENTITY") so that doesn't work either...
So how are we supposed to use Sql Server CE 3.5? It's impossible together with datasets? Or am I missing something obvious?
Any hints would be greatly appreciated! Thanks!
Cheers!
Uploading A File To A SQL Database Via A Typed Dataset And Using The FileUpload Control.
Oct 2, 2006I am trying to upload a file in ASP.net 2.0 to a SQL database using the FileUpload control. I am doing this by way of a typed dataset.Here is my code.Dim rta As New ResponseTableAdapterDim rdt As ResponseDataTable = rta.GetResponseByID(1)Dim rr As ResponseRow = rdt(0)Dim fs As New FileStream(fu.PostedFile.FileName, FileMode.Open, FileAccess.Read)Dim br As New BinaryReader(fs)Dim image() As Byte = br.ReadBytes(fs.Length)br.Close()fs.Close()rr.UploadFile = imagerr.NameFile = fs.Namerta.Update(rdt)The code runs without an error but afterwards I find that nothing has been stored in the UploadFile cell in the database but the file name has been stored in the NameFile cell in the database. Any ideas?Your help is much appreciated. James
View 1 Replies View RelatedHow To Create An Update Or Delete Method In A Strongly Typed Dataset?
Mar 22, 2007Like the subject says, I'm using strongly typed datasets. I'm using to designer to create the datasets and the methods. I can select and insert, but I can't update or delete. I right click on the adapter bar and select Add Query. I sleect 'Use SQL Statements'I select 'Update' (or 'Delete')I get a sql statement pane containing the word 'Update' ('Delete') and asking 'What data should the table load?'I can click on next, but anything else gives me errors. I'd list them, but I'm clearly doing something wrong and it's probably obvious. Diane
View 5 Replies View RelatedAliases
May 7, 2008Please help...
I need help analyzing this query. I am not sure why the person that wrote this query used the same table for 3 different aliases.
FROM property,
address,
ppi,
code_table state,
code_table country,
code_table prop_role
I tried to get rid of :
code_table country,
code_table prop_role
and put "state" in any line that referenced:
code_table country,
code_table prop_role
to see if the query worked the same, but I different results. Why?
Please help...
--7 seconds to omplete.
--set explain on;
--UNLOAD TO '/export/home/permit/owneraddress.txt'
SELECT ppi.id,
ppi.property_id,
ppi.party_id,
ppi.address_id,
ppi.eff_from_date,
ppi.eff_to_date,
address.line_1,
address.line_2,
address.line_3,
address.city,
state.code_table_cd,
address.zip_postal_code,
country.code_description,
address.line_care_of,
prop_role.code_table_cd
FROM property,
address,
ppi,
code_table state,
code_table country,
code_table prop_role
WHERE property.id = ppi.property_id
AND ppi.address_id = address.id
AND (property.eff_to_date IS NULL OR property.eff_to_date >= getdate())
AND (ppi.eff_to_date IS NULL OR ppi.eff_to_date >= getdate())
AND ppi.eff_from_date <= getdate()
AND state.id = address.province_state_cd
AND country.id = address.country_cd
AND prop_role.id = ppi.prop_role_cd
AND prop_role.code_table_cd = 'OWNER'
AND property.pact_code <> 'PERS'
Table Aliases?
Nov 9, 2004Does anyone know of a neat way of aliasing tables in MS SQL Server 2000? I have a table [X] and I want to refer to it (in TSQL statements, stored procedures, etc.) by two names, say [X] and [Y]. There should be only one copy of the table's data.
I could handle this in my site logic (ie convert [Y] to [X]), but it would be really clean to handle this in the database.
Thanks for your advice.
Matt
Aliases To Get Around Error
Mar 26, 2015trying to write a query from 2 tables and join them only they share a column name of the same name.I can define both aliases seperatrly but get an error when trying both,
select
case_HDR.case_nbr as "test"
Case_DTL.case_nbr as "test2"
From
Case_HDR, case_dtl
I need to join them also but trying to work in stages.
Numeric Expressions And Aliases
Mar 8, 2006I'm in the process of building a site and converting views/tables/queries from an Access database to SQL. I've done this quite a few times, and never had any significant issues I couldn't figure out on my own.
In Enterprise Manager, I've created a view and in the query, I need to create an alias that is similar to below:
SELECT ((monthmult) + ((b2avg*15)-(av2*10)) + (lp1+lp2) + ((b1avg*30)-(av1*20))) as PIndexValue
which is how the formula reads in the Access view.
However, when I got to run the query, SQL strips out all of the parentheses and calculates the value in left to right order:
(monthmult + b2avg*15-av2*10 + lp1+lp2 + b1avg*30-av1*20) as PIndexValue
Which gives me an incorrect value.
Does anyone know why this is happening, or am I just unaware of the right way of doing it?
Thank you,
Derrick
Using Data As Column Aliases
Jan 15, 2008Hi,
I’m working with a really old design migrated to SQL 2005, in which I basically have two tables…
Table 1 contains all the “proper” data, and has columns called: col_1, col_2, col_3
Table 1’s data is something like:
col_1, col_2, col_3
Jack,jack@yahoo.ca,Toronto
Jill,jill@hotmail.com,Montreal
Table 2 contains meta-data for Table 1, specifically, it has two columns: column, meta-data
Table 2’s data is something like:
column,metadata
col_1,name
col_2,email
col_3,city
(Hopefully, my description of the design makes sense….basically; Table 2’s data describes what’s in each column of Table 1).
So, the question, if I want to write a ‘SELECT’ on Table 1, how can I use the data in Table 2 as aliases (or column) headers.
I’m currently going down the path of building dynamic SQL statements in T-SQL….but before I get too far, wanted to vet this idea here (it’s always been a fantastic resource for me)
Thanks in advance!
Multi Aliases With The Same Column?
Jun 17, 2008TABLE1
======================
PriceList
---------
1
2
3
1
2
3
1
2
3
Price
-----
777
888
999
777
888
999
777
888
999
(pretend these columns are side by side)
======================
I need to make a query to:
SELECT PRICE AS 'PRICE1' WHERE PRICELIST = 1
AND SELECT PRICE AS 'PRICE2' WHERE PRICELIST = 2
AND SELECT PRICE AS 'PRICE3' WHERE PRICELIST = 3
the output that i want is:
PRICE1
------
777
777
777
PRICE2
------
888
888
888
PRICE3
-----
999
999
999
(pretend these columns are also side by side)
Typed Endpoints In SQL 2005?
May 18, 2007Is it possible to return typed data in an Endpoint for an ASP.Net Web Reference to Proxy? If so, is there any specific terminology I should be aware of to target my search?
I realize there is a choice between Object (returns a dataset or error) or dataset, but the automatically generated WebReference Proxy in ASP.Net (2.0) is untyped and we can't change the typing there as you have to remove the entire WebReference to pick up new WebMethods (or changes to signitures I'd assume).
I'm able to create my own typed proxy as a psuedo-DAL assembly which takes the WebReference and cast rows/objects into typed rows/objects one at a time, but this seems like a lot of work and probobly not the best practice.
Any help would be greatly appreciated
Numeric Aliases For Columns
May 6, 2008Hi,
is it possible to assign a numeric value as a column alias:
select ... as 1234 from ...
does not work.
Cheers
64 Bit Server Aliases SQL 2005
Feb 15, 2007Does anyone know how to create SQL Server aliases for Itanium servers?
Alternatively does anyone know how to use SMO to create a SQLAliasCollection for a specific server? I can't seem to find how to get that collection.
I've added aliases that work for 32 bit applications but they don't seem to be used for 64 bit applications.
Aliases && Columns Name W/ Spaces
Oct 5, 2006Formatting question. The query below is failing on the columns with spaces in the name. I've tried brackets and single quotes with no luck. How should this be formatted?
----------------------------
SELECT x.trkuniq, s.mstuniq, t.meetuniq,
c.coursec AS Course_Code,
c.descript AS Course_Name,
[q.cactus #] AS Cactus_#
s.sectionn AS Section,
RTRIM(f.lastname) + ', ' + RTRIM(f.firstname) AS Teacher, f.funiq,
t.termc AS Term_Code, zd.cycldayc AS Day,
zp.periodc AS Period, zp.periodn,
p.schoolc AS School
FROM mstmeet t INNER JOIN
mstsched s ON t.mstuniq = s.mstuniq INNER JOIN
trkcrs x ON s.trkcrsuniq = x.trkcrsuniq INNER JOIN
course c ON x.crsuniq = c.crsuniq INNER JOIN
track p ON p.trkuniq = x.trkuniq INNER JOIN
facdemo f ON s.funiq = f.funiq INNER JOIN
courses q ON c.coursec = [q.course number] INNER JOIN
trkper zp ON t.periodn = zp.periodn AND
x.trkuniq = zp.trkuniq INNER JOIN
trkcycle zd ON t.cycldayn = zd.cycldayn AND
x.trkuniq = zd.trkuniq
Reuse Of Field Aliases
Jan 3, 2007I have been working with SQL for quiet a while but think this perhaps is a very basic question that has always escaped me:
At my work I was exposed to both, MS SQL Server 2000 and Sybase Adaptive Server Anywhere/Sybase SQL Anywhere.
Under Sybase I was able to use aliases in other calculations and filters but i have never been able to do the same with SQL.
Example:
In Sybase I can write this:
Select Price * Units as Cost Cost * SalesTax as TotalTaxFrom Invoice Where TotalTax > 3.5
However if i want to do this in MS SQL 2000 i have to go trough
Select
Price * Units as Cost
Price * Units * SalesTax as TotalTax
From Invoice
Where (Price * Units * SalesTax) > 3.5
In the long run this is costing me a lot of code redundancy, not to mention a debugging nightmare. Is there a way to replicate this alias usage in MS SQL Server?
MARS Issue With Typed DataSets?
Aug 2, 2007I have a DAL that uses Typed DataSets (not directly, the DAL references and calls the dataset methods) and I am receiving a "There is already an open DataReader associated with this Command which must be closed first." error when I render an ASP.NET page where two imgs need to retrieve two different versions of an image (a thumbnail and a full sized image).I am using SQL Server 2005, ASP.NET 2.0, and my connection string includes the MARS attribute set to true. This is the method in the SqlServer DAL, _images is a typed table adapter:public override Image SelectImage(Guid? id) { // TODO: Find out why MARS feature isn't working! DataSet.ImageDataTable dt = _images.SelectImageById(id); <-- Exception occurs here if ((dt != null) && dt.Count == 1) { DataSet.ImageRow row = dt[0]; Guid? keyId = null; if (!row.IsKeyIdNull()) { keyId = row.KeyId; } // Success return new Image(row.Id, keyId, row.Path, row.Caption); } return null; } // This is the method that is called for both images, which calls the DALpublic class EntitySearch{ public static Image ImageById(Guid id) { return _dal.SelectImage(id); }}// These are the code-behind methods that the image response strings will come from...imgThumbnail.ImageUrl = String.Format("../Services/ImageBroker.aspx?img={0}&mode=Thumbnail", image.Id);imgFullImage.ImageUrl = String.Format("../Services/ImageBroker.aspx?img={0}&mode=Image", image.Id); // And finally here is the code in the broker class that saves the images to the response streamImage image = EntitySearch.ImageById(imageId);using (Picture picture = (mode == ImageMode.Image) ? image.Picture : image.Thumbnail){ Picture.Save(response.OutputStream, ImageFormat.Jpeg);}So the final snippet of code is being called twice, and it crashes. I have tried closing the connection and reopening it as a workaround but this only yields strange results, though it intermittently does load the images.Can anyone spot the issue?
View 1 Replies View RelatedTyped Data Set Error Message
Feb 14, 2008I am using typed data sets. When a certain line of C# code is reached, I get the following error message:
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
Here is my source code. I altered the table to remove all contraints and primary keys, but I still get the error message. The erroroccurs when the last line of code is reached. (_DeltaTable = myAdaptor.GetData();) The code is listed below. Does anyone know whatis going on?
calculateValuesAndAveragesTableAdapters.FundamentalsTableAdapter myAdaptor = new calculateValuesAndAveragesTableAdapters.FundamentalsTableAdapter();
calculateValuesAndAverages.FundamentalsDataTable _DeltaTable; _DeltaTable = myAdaptor.GetData(); // Error occurs on this line
Strongly Typed Datatables (out Of Resultsets)
Feb 18, 2008I dragged two tables into the dataset designer. And I have a query with a resultset over both of the tables (joined).Is there ANY way I can have a strongly typed datatable out of the resultset?The automatically created dataset adapters won't allow me to create a tableadapter or datatable for the joined resultset but for one single table at a time only.Any solution to that? Theoretically it should be possible since I don't "lose" any type information with a join.. why can't I have a strongly typed datatable over the resultset?The join is not very sophisticated. In fact it couldn't be any more simple.(I'm refering to my previously posted question at http://forums.asp.net/t/1220481.aspx)
View 4 Replies View RelatedDoes Having Strongly Typed XML Speed Up Queries
Feb 2, 2015I'm looking to serialize some NVP data into an XML blob. I plan to put a primary xml index on the column, but my question is, would putting an XSD on the column speed up any queries, or would it just ensure format? I know that with selective xml indices, you can do things like specify the datatype associated with the xpath which can further optimize retrieval.
View 1 Replies View RelatedSQL 2012 :: Multiple Aliases Of Same Table?
Mar 4, 2015TableX
-------
X1
X2
X3
TableY
-------
Y1
Y2
Y3
I need to write a query with the following joins:
TableX.X1 = TableY.Y1
TableX.X2 = TableY.Y2
What is the proper way to do this in SQL? Would it be:
select x.*
from TableX x
join TableY y1 on y1.Y1 = x.X1
join TableY y2 on y2.Y2 = x.X2
Is there a more proper way to do this without creating multiple aliases of the same table?
Aliases And Remote Connection Failure
Nov 14, 2007Just stumbled over the interesting bug -
if you have an SQLExpress server and the "allow remote connection" in SQL Instance is turned ON, it doesn't means that it works if you create some aliases for this instance
If Im trying to connect to SQLExpress via new created alias ('localhost' for example) I got the "SQL Server does not allow remote connections" error. Albeit, connecting to ".SQLExpress" is successful.
I've checked the properties of locahost DB instance in SQL Management Studio - remote connection is allowed.
I've restarted the SQLExpress instance.
There is no SQLServer or other instances on my PC
But it doesn't work
How to fix this and allows to connect to sql instance via aliases?
Refering Aliases In The Same View/ Query
Dec 6, 2007HI,
I have a view where I want to add Rundate and Prev Month Rundate as computed columns to simplify my joins and calculations.
Rundate will be a select from another table that has a list of rundates for each month like this.
SELECT MAX(fm_dateend)
FROM dbo.tbl_FiscalMonth
WHERE fm_dateend <= getdate()) AS smalldatetime) AS CurrRunDate
Now, I want to add Prev Month Rundate on the basis of CurrRunDate but it does not accept CurrRunDate. The query is like this
SELECT MAX(fm_dateend)
FROM dbo.tbl_FiscalMonth
WHERE fm_dateend < CurrRunDate) AS smalldatetime) AS PrevMonthRunDate
Can anyone help me to work around with the alias as I dont like to put a whole bunch of code inplace of CurrRunDate(Alias).
Thanks,
Shariq
Column Aliases In Case Statement In Order By
Jan 18, 2007Hi All,
I have this query :
select col1, col2, col3, col4, col5,..... , (select col99 from tab2) as alias1 from tab1 where <condition>
order by
case @sortby
when 'col1' then col1,
when 'col2' then col2,
when 'col3' then col3,
when 'col99' then col99
end
when i execute the above query it gives me the following error message.
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'col99'.
Thanks in advance.
Thanvi.
Loosely-typed Data Flow Destination
Mar 14, 2007I would like to extact data from a source system even if it has errors. Then I can transform it and handle the errors in the appropriate manner. Are there any loosely-typed Data Flow Destinations?
View 11 Replies View RelatedClustering, Virtual Sql Servers And Client Network Aliases
Oct 31, 2006I have a question regarding the nature of virtual sql servers, specificially what protocol is used to communicate to the server when a request is made by a client.
For example, if a scheduled job is run on the virtual sql server, what determines the protocol used (e.g. TCP/IP, named pipes etc.) by SQL Server agent? Is it the client network alias set up on the virtual server?
I am asking because currently the client aliases on some of our virtual sql servers are using named pipes and I think this is causing a problem with our backups.
Thanks,
Adrian.