[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 183
  • Last Modified:

Easy one for someone!!SQL Commands

Hi, I have the following code that does not work....

Dim cmd As SqlCommand = SqlConnection1.CreateCommand
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "SELECT OrderId, Employees.Firstname " & _
                          "FROM Orders INNER JOIN Employees " & _
                          "FROM Orders INNER JOIN Employees " & _
                          "ON Orders.EmployeeID = Employees.EmployeeID"


        Dim da As New SqlDataAdapter(cmd)
        Dim ds As New DataSet
        SqlConnection1.Open()
        da.Fill(ds, "Results")
        SqlConnection1.Close()

Is it possible to get the datalike this and I'm missing something or do you have to set up a view or stored procedure on the database??

Thanks in advance

Dave
0
Dave091277
Asked:
Dave091277
  • 2
2 Solutions
 
Carl TawnSystems and Integration DeveloperCommented:
You have the CommandType set to StoredProcedure, but you are not using a stored procedure you're using a text query.

Change:

     cmd.CommandType = CommandType.StoredProcedure

To:

    cmd.CommandType = CommandType.Text
0
 
Dave091277Author Commented:
Oops, Thats from me messing around with it before i cut and pasted the bit of code, sorry. Its the....

da.Fill(ds, "Results")


....line that won't work for me.  I get a system error. It seems to me that to collect data from multiple tables I have to set up a view or stored procedure, Is it possible to fill the dataset something like the above??
0
 
Éric MoreauSenior .Net ConsultantCommented:
Hi Dave091277,

In addition to the modification to the CommandType line (as reported by carl_tawn), your SQL query is not valid:

>         cmd.CommandText = "SELECT OrderId, Employees.Firstname " & _
>                           "FROM Orders INNER JOIN Employees " & _
>                           "FROM Orders INNER JOIN Employees " & _
>                           "ON Orders.EmployeeID = Employees.EmployeeID"


should be:

         cmd.CommandText = "SELECT OrderId, Employees.Firstname " & _
                           "FROM Orders INNER JOIN Employees " & _
                           "ON Orders.EmployeeID = Employees.EmployeeID"

Cheers!
0
 
Dave091277Author Commented:
I can not believe I did that and didn't notice!! what a fool!!! Sorry to have wasted your time! I'll split the points. Thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now