?
Solved

Problem filling two tables into a dataset - SQL/ASP.NET(vb)

Posted on 2005-03-23
3
Medium Priority
?
282 Views
Last Modified: 2010-08-05
Hey all. I'm trying create a dataset of several tables from my database. The relevant snippet of code is:
-------------------------------------------------------------------
Dim sSql As String
sSql = "SELECT tContacts.* tOrgActivities.tOrgActivity_Name FROM tContacts INNER JOIN tOrgActivities ON tContacts.tContact_OrgActivity_id = tOrgActivities.tOrgActivity_id WHERE tContacts.tContact_id =" & iContact_id
Dim oDa As New SqlDataAdapter(sSql, oConn.Conn)  'oConn is an instance of my SQL connector class (which works fine)
Dim oDsContact As New DataSet("ContactDs")
oDa.Fill(oDsContact, "tContacts")
oDa.Fill(oDsContact, "tOrgActivities")
-------------------------------------------------------------------
The error I get is:
-------------------------------------------------------------------
Line 1: Incorrect syntax near 'tOrgActivities'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'tOrgActivities'.

Source Error:
Line 95:         Dim oDa As New SqlDataAdapter(sSql, oConn.Conn)
Line 96:         Dim oDsContact As New DataSet("ContactDs")
Line 97:         oDa.Fill(oDsContact, "tContacts")                     <<-------(highlighted)
Line 98:         'oDa.Fill(oDsContact, "tCountries")
Line 99:         'oDa.Fill(oDsContact, "tDataSources")
-------------------------------------------------------------------
My DB structure is: http://www.esulin.net/docs/Rel.pdf

I think the problem is in the SQL statement. I have also tried using:
"Select * From vContacts Where tContact_id =" & iContact_id
Here vContacts is a view stored in my sql database that includes the two tables. This doesn't give me the error, but with the above code, it puts the tContacts table into the DataSet twice.

In any case. HELP!

-Esulin

P.S. I am still new to all of this, so I know it's probably something stupid. Don't hate me for being a noob. :P


0
Comment
Question by:Esulin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 18

Accepted Solution

by:
tusharashah earned 2000 total points
ID: 13611677
Your Query should be modify like following ( you are missing a comma )

sSql = "SELECT tContacts.*, tOrgActivities.tOrgActivity_Name FROM tContacts INNER JOIN tOrgActivities ON tContacts.tContact_OrgActivity_id = tOrgActivities.tOrgActivity_id WHERE tContacts.tContact_id =" & iContact_id

-tushar
0
 

Author Comment

by:Esulin
ID: 13613076
Urmmm... Well that worked... But then I'm back to square one with the tContacts table being added to the dateset twice.

I execute the following loop:
-----------------------------------------------
        Dim i, j As Integer
        For i = 0 To oDsContact.Tables.Count - 1
            DevLabel.Text &= oDsContact.Tables(0).TableName & "<br>"
            For j = 0 To oDsContact.Tables(i).Columns.Count - 1
                DevLabel.Text &= "&nbsp;&nbsp;&nbsp;&nbsp;" & oDsContact.Tables(0).Columns(j).ToString & "<br>"
            Next
        Next
-----------------------------------------------
And I get:
-----------------------------------------------
tContacts
    tContact_id
    tContact_Salutation
    tContact_FName
    tContact_LName
    tContact_JobTitle
    tContact_Org
    tContact_OrgActivity_id
    tContact_HomeTel
    tContact_HomeFax
    tContact_OfficeTel
    tContact_OfficeFax
    tContact_MobileTel
    tContact_EMail
    tContact_StreetAddress
    tContact_POBox
    tContact_City
    tContact_State
    tContact_Country_id
    tContact_DataSource_id
    tContact_Category_id
    tContact_ContactGroup_id
    tOrgActivity_Name
tContacts
    tContact_id
    tContact_Salutation
    tContact_FName
    tContact_LName
    tContact_JobTitle
    tContact_Org
    tContact_OrgActivity_id
    tContact_HomeTel
    tContact_HomeFax
    tContact_OfficeTel
    tContact_OfficeFax
    tContact_MobileTel
    tContact_EMail
    tContact_StreetAddress
    tContact_POBox
    tContact_City
    tContact_State
    tContact_Country_id
    tContact_DataSource_id
    tContact_Category_id
    tContact_ContactGroup_id
    tOrgActivity_Name
-----------------------------------------------

Any ideas why the tOrgActivities table isn't being added to the dataset?

-Esulin
0
 

Author Comment

by:Esulin
ID: 13613145
Ohhhh... Waitaminute... hehehe.. I just noticed the tOrgActivity_Name at the bottom of that list...

That works for me....

Thanks for the help.

-Esulin
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
In .NET 2.0, Microsoft introduced the Web Site.  This was the default way to create a web Project in Visual Studio 2005.  In Visual Studio 2008, the Web Application has been restored as the default web Project in Visual Studio/.NET 3.x The Web Si…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question