Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2005-03-23
3
Medium Priority
?
283 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
  • 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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

I have developed many web applications with asp & asp.net and to add and use a dropdownlist was always a very simple task, but with the new asp.net, setting the value is a bit tricky and its not similar to the old traditional method. So in this a…
Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month13 days, 9 hours left to enroll

581 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