shorten an SQL statement to work in VB.net

[Guest]

I need to shorten an SQL statement from a mdb query that is to long to be
accepted as a SELECT statement in a Data Access class in VB.net. "SELECT *
FROM tblXXXXX" won't work because I also need to fill combo boxes as well as
text boxes on a form.

 

[John Spencer]

One way to shorten the text of a query statement is to use aliases for table
names. Since you did not post the actual SQL statement you are attempting
to use I doubt if anyone can suggest anything specific to your situation.

How much do you need to shorten the statement?

Another method might be to return ALL the fields from selected tables (if
you are returning most of the fields).

SELECT [This is a really long table name].[long field name]
, [This is a really long table name].[Another long field name]
, [This is a really long table name].[Field3of4 fields]
, [This is another long table name] .FieldA
, [This is another long table name] .FieldB
FROM [This is a really long table name] INNER JOIN [This is another long
table name]
On [This is a really long table name] .PrimaryKey = [This is another long
table name] .ForeignKey

Could shorten to
SELECT A.*
, B.FieldA
, B.FieldB
FROM [This is a really long table name] A INNER JOIN [This is another long
table name] B
On A.PrimaryKey = B.ForeignKey

 

[Guest]

This is the actual SQL statement:

SELECT tblRespondent.RespondentID, tblRespondent.FirstName,
tblRespondent.MiddleInitial, tblRespondent.LastName, tblRespondent.Address,
tblRespondent.City, tblState.StateName, tblRespondent.ZipCode,
tblRespondent.PhoneNumber, tblRespondent.AltPhoneNumber, tblRespondent.Email,
tblSeason.Season, tblSeason_1.Season, tblPosition.Position,
tblPosition_1.Position, tblSport.Sport, tblAttending.Attending,
tblRespondent.Comments
FROM tblSeason INNER JOIN (tblPosition INNER JOIN (tblState INNER JOIN
(tblAttending INNER JOIN (tblSport INNER JOIN ((tblRespondent INNER JOIN
tblPosition AS tblPosition_1 ON tblRespondent.Position1ID =
tblPosition_1.PositionID) INNER JOIN tblSeason AS tblSeason_1 ON
tblRespondent.SeasonEndedID = tblSeason_1.SeasonID) ON tblSport.SportID =
tblRespondent.SportID) ON tblAttending.AttendingID =
tblRespondent.AttendingID) ON tblState.StateID = tblRespondent.StateID) ON
tblPosition.PositionID = tblRespondent.Position2ID) ON tblSeason.SeasonID =
tblRespondent.SeasonBeganID;

does that help?

One way to shorten the text of a query statement is to use aliases for table
names. Since you did not post the actual SQL statement you are attempting
to use I doubt if anyone can suggest anything specific to your situation.

How much do you need to shorten the statement?

Another method might be to return ALL the fields from selected tables (if
you are returning most of the fields).

SELECT [This is a really long table name].[long field name]
, [This is a really long table name].[Another long field name]
, [This is a really long table name].[Field3of4 fields]
, [This is another long table name] .FieldA
, [This is another long table name] .FieldB
FROM [This is a really long table name] INNER JOIN [This is another long
table name]
On [This is a really long table name] .PrimaryKey = [This is another long
table name] .ForeignKey

Could shorten to
SELECT A.*
, B.FieldA
, B.FieldB
FROM [This is a really long table name] A INNER JOIN [This is another long
table name] B
On A.PrimaryKey = B.ForeignKey

I need to shorten an SQL statement from a mdb query that is to long to be
accepted as a SELECT statement in a Data Access class in VB.net. "SELECT *
FROM tblXXXXX" won't work because I also need to fill combo boxes as well
as
text boxes on a form.

 

[John Spencer]

I'm not familar with VB.net requirements, but this seems like a pretty short
SQL statement. Are you sure that there isn't some other problem that is
triggering an inappropriate error message.

Here is a rewrite that goes from 1013 characters to 596 characters

SELECT R.RespondentID, R.FirstName, R.MiddleInitial, R.LastName, R.Address,
R.City, tblState.StateName, R.ZipCode, R.PhoneNumber, R.AltPhoneNumber,
R.Email, Ssn0.Season, Ssn1.Season, P0.Position, P1.Position, S.Sport,
A.Attending, R.Comments
FROM tblSeason Ssn0 INNER JOIN (tblPosition P0 INNER JOIN (tblState INNER
JOIN (tblAttending A INNER JOIN (tblSport S INNER JOIN ((tblRespondent R
INNER JOIN tblPosition AS P1 ON R.Position1ID = P1.PositionID) INNER JOIN
Ssn0 AS Ssn1 ON R.SeasonEndedID = Ssn1.SeasonID) ON S.SportID = R.SportID)
ON A.AttendingID = R.AttendingID) ON tblState.StateID = R.StateID) ON
P0.PositionID = R.Position2ID) ON Ssn0.SeasonID = R.SeasonBeganID;

This is the actual SQL statement:

SELECT tblRespondent.RespondentID, tblRespondent.FirstName,
tblRespondent.MiddleInitial, tblRespondent.LastName,
tblRespondent.Address,
tblRespondent.City, tblState.StateName, tblRespondent.ZipCode,
tblRespondent.PhoneNumber, tblRespondent.AltPhoneNumber,
tblRespondent.Email,
tblSeason.Season, tblSeason_1.Season, tblPosition.Position,
tblPosition_1.Position, tblSport.Sport, tblAttending.Attending,
tblRespondent.Comments
FROM tblSeason INNER JOIN (tblPosition INNER JOIN (tblState INNER JOIN
(tblAttending INNER JOIN (tblSport INNER JOIN ((tblRespondent INNER JOIN
tblPosition AS tblPosition_1 ON tblRespondent.Position1ID =
tblPosition_1.PositionID) INNER JOIN tblSeason AS tblSeason_1 ON
tblRespondent.SeasonEndedID = tblSeason_1.SeasonID) ON tblSport.SportID =
tblRespondent.SportID) ON tblAttending.AttendingID =
tblRespondent.AttendingID) ON tblState.StateID = tblRespondent.StateID) ON
tblPosition.PositionID = tblRespondent.Position2ID) ON tblSeason.SeasonID
=
tblRespondent.SeasonBeganID;

does that help?

One way to shorten the text of a query statement is to use aliases for
table
names. Since you did not post the actual SQL statement you are
attempting
to use I doubt if anyone can suggest anything specific to your situation.

How much do you need to shorten the statement?

Another method might be to return ALL the fields from selected tables (if
you are returning most of the fields).

SELECT [This is a really long table name].[long field name]
, [This is a really long table name].[Another long field name]
, [This is a really long table name].[Field3of4 fields]
, [This is another long table name] .FieldA
, [This is another long table name] .FieldB
FROM [This is a really long table name] INNER JOIN [This is another long
table name]
On [This is a really long table name] .PrimaryKey = [This is another long
table name] .ForeignKey

Could shorten to
SELECT A.*
, B.FieldA
, B.FieldB
FROM [This is a really long table name] A INNER JOIN [This is another
long
table name] B
On A.PrimaryKey = B.ForeignKey

I need to shorten an SQL statement from a mdb query that is to long to
be
accepted as a SELECT statement in a Data Access class in VB.net.
"SELECT *
FROM tblXXXXX" won't work because I also need to fill combo boxes as
well
as
text boxes on a form.