Problem with JOIN

I'm learning aspx. I have the following code in my aspx:
SqlConnection connection = new
SqlConnection("server=(local)\\NetSDK;database=pub s;Integrated
Security=SSPI");
String sqlCommand = "SELECT * FROM Titles INNER JOIN MyTable ON
MyTable.title_id=Titles.title_id";
SqlDataAdapter command = new SqlDataAdapter(sqlCommand, connection);
..
..
..
This code returns the correct data set. However, when I use this code:
SqlConnection connection = new
SqlConnection("server=(local)\\NetSDK;database=pub s;Integrated
Security=SSPI");
String sqlCommand = "SELECT Titles.title_id, title, type, pub_id, price,
notes, pubdate FROM Titles INNER JOIN MyTable ON
MyTable.title_id=Titles.title_id";
SqlDataAdapter command = new SqlDataAdapter(sqlCommand, connection);
..
..
..
the returned data set does not contain the MyTable data.
What is Wrong?
"RuffAroundTheEdges" schrieb:
> I'm learning aspx. I have the following code in my aspx:
> SqlConnection connection = new
> SqlConnection("server=(local)\\NetSDK;database=pub s;Integrated
> Security=SSPI");
> String sqlCommand = "SELECT * FROM Titles INNER JOIN MyTable ON
> MyTable.title_id=Titles.title_id";
> SqlDataAdapter command = new SqlDataAdapter(sqlCommand, connection);
> .
> This code returns the correct data set. However, when I use this code:
> SqlConnection connection = new
> SqlConnection("server=(local)\\NetSDK;database=pub s;Integrated
> Security=SSPI");
> String sqlCommand = "SELECT Titles.title_id, title, type, pub_id, price,
> notes, pubdate FROM Titles INNER JOIN MyTable ON
> MyTable.title_id=Titles.title_id";
> SqlDataAdapter command = new SqlDataAdapter(sqlCommand, connection);
> .
> the returned data set does not contain the MyTable data.
> What is Wrong?
You are not asking for data from MyTable. All the fields in the select list
are fields from 'titles', aren't they?
|||If you place the star (*) in the selection list without any prefix for any
table, even all joined tables are used for selecting the columns, If you are
specifying the columns only these are selected.
So in your second example you should go for that statement if you want to
get those columns in addition:

> String sqlCommand = "SELECT Titles.title_id, title, type, pub_id, price,
> notes, pubdate,
myTable.*
FROM Titles INNER JOIN MyTable ON
> MyTable.title_id=Titles.title_id";
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"RuffAroundTheEdges" <RuffAroundTheEdges@.discussions.microsoft.com> wrote in
message news:AC882F99-C17E-43BF-843E-0EF0D0B4ADC1@.microsoft.com...
> I'm learning aspx. I have the following code in my aspx:
> SqlConnection connection = new
> SqlConnection("server=(local)\\NetSDK;database=pub s;Integrated
> Security=SSPI");
> String sqlCommand = "SELECT * FROM Titles INNER JOIN MyTable ON
> MyTable.title_id=Titles.title_id";
> SqlDataAdapter command = new SqlDataAdapter(sqlCommand, connection);
> .
> .
> .
> This code returns the correct data set. However, when I use this code:
> SqlConnection connection = new
> SqlConnection("server=(local)\\NetSDK;database=pub s;Integrated
> Security=SSPI");
> String sqlCommand = "SELECT Titles.title_id, title, type, pub_id, price,
> notes, pubdate FROM Titles INNER JOIN MyTable ON
> MyTable.title_id=Titles.title_id";
> SqlDataAdapter command = new SqlDataAdapter(sqlCommand, connection);
> .
> .
> .
> the returned data set does not contain the MyTable data.
> What is Wrong?
|||The example shown in the FROM document page at;
http://msdn.microsoft.com/library/de..._pubs_2v8l.asp
has:
SELECT ProductID, Suppliers.SupplierID
FROM Suppliers JOIN Products
ON (Suppliers.SupplierID = Products.SupplierID)
|||I had to do more reading and found that the SELECT statement works
differently in a JOIN:
SELECT Titles.title.id, ..., MyTable.stuff FROM Titles JOIN MyTable ON
(MyTable.title_id = Titles.title_id)

Problem With Insert Using SQLDataSource

I have a table with 2 columns - "memberid" is a guid and "Interest" is a string. In an aspx page I want to read in all values of "Interest" for a given "memberid" and display approprite checkboxes as checked. The user can then change which boxes are checked and I want to record the new list of selected items in the table. I created a SQLDataSource (see below) but when I run it, I get this error:

========== Error ===========

Disallowed implicit conversion from data type sql_variant to data type uniqueidentifier, table 'DB_136571.dbo.gs_MemberInterests', column 'memberid'. Use the CONVERT function to run this query.

============================

The only use of this SQLDataSource is to delete all entries for a given user and then insert an entry for each checked checkbox. The code to set the session variables is as follows:

======== Code ============

Dim guidMemberidAs Guid =CType(user.ProviderUserKey, Guid)

Session("currmember") = guidMemberid

....

Session("currinterest") =CType(item.FindControl("CheckBox1"), CheckBox).Text

==========================

Any ideas of what I am doing wrong here?

========= SQLDataSource ==========

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:GoodSamSiteDB %>"

DeleteCommand="DELETE FROM [gs_MemberInterests] WHERE [memberid] = @.memberid"

InsertCommand="INSERT INTO [gs_MemberInterests] ([memberid], [Interest]) VALUES (@.memberid, @.Interest)"

SelectCommand="SELECT memberid, Interest FROM gs_MemberInterests WHERE (memberid = @.memberid) AND (Interest = @.interest)">

<DeleteParameters>

<asp:ParameterName="memberid"Type="Object"/>

</DeleteParameters>

<SelectParameters>

<asp:SessionParameterName="memberid"SessionField="currmember"Type="Object"/>

<asp:SessionParameterName="interest"SessionField="currinterest"/>

</SelectParameters>

<InsertParameters>

<asp:ParameterName="memberid"Type="Object"/>

<asp:ParameterName="Interest"Type="String"/>

</InsertParameters>

</asp:SqlDataSource>

=============================

Type="Object"|||

Motley wrote:

Type="Object"

Thanks. I assume you are referring to the fact that the "Type='Object'" has to be removed. I just found a reference to that bug. That cleared the problem.

sql

Problem with dates

I am having a problem inserting dates into SQL server 2000. I have an aspx page which contains a simple form.

This form data is inserted into the database on submission. On submission to SQL Server the date field is changed to 01/01/1900

This is the concatenated INSERT statement which is generated:

INSERT INTO Applicant (appTitle, appFname, appSname, appDOB, appEmail, appTel, appTelB, appAdd1, appAdd2, appAdd3, appAdd4, appPostcode, appPassword) VALUES ('Mr', 'John', 'Smith', 01/04/78, 'j.smith@.hello.com', '01258 698523', '', '1 The Smithy', 'Old Town', 'Oldville', '', 'OS6 8IU', 'Smith3969')

I have tried wrapping the date in ' ', but this doesn't work either.

Any help would be greatly appreciated

ThanksIt will not work without the quotes. Absent that, you are inserting 1/4 (0.25) /78 (a really small number).

I woulduse parameters instead, which isolates you from the strangeness with handling dates otherwise.|||Thank you for this.

I have almost got it working now using parameters. However I have noticed that dates submitted in the format DD/MM/YYYY or DD/MM/YY are being flipped in SQL Server to the incorrect format MM/DD/YYYY.

This is creating incorrect dates and in some cases is generating the following error:
"Arithmetic overflow error converting expression to data type datetime. The statement has been terminated."

Any ideas?|||Inside SQL Server, the dates (if DateTime type) are binary types, without any formatting applied to them. You can explicitly format the datetime retrieved from SQL Server to a useful representation using DateTime.ToString() (one of the overloads allows a format string that specifies the display format).|||Thats ok, so I can format them correctly in my application.

But what is causing the error:
"Arithmetic overflow error converting expression to data type datetime. The statement has been terminated."

It only occurs on some dates and not on others. This is really confusing me. I may have to resort to storing the dates as text, and then perform any calculations I require by changing them back to dates in my application.

If you have some ideas on this they would be greatfully received.

Thanks

Charlie|||You are likely sending a date to SQL Server (as a string) in a format it does not expect (DD/MM/YYYY rather than MM/DD/YYYY).

Show the code around when the error is occuring. On SQL Server, I would use SQL Profiler and see what is being sent to SQL Server.