Need to put from vb.net to sql format.

 I would like to put the following in a sql format. If someone can help me that would be awesome.


 Public Function GetTicketDevices(ByVal ticketID As String) As SqlDataReader
        Dim sql As New StringBuilder()
        sql.Append("SELECT  TOP 100 ")
        sql.Append("tbl_index_ticket_devices.TicketSolutionComponentIndexID, ")
        sql.Append("tbl_index_ticket_devices.DeviceID, ")
        sql.Append("tbl_index_ticket_devices.TicketID, ")
        sql.Append("tbl_index_ticket_devices.DateAdded, ")
        sql.Append("tbl_index_ticket_devices.AgentAdded, ")
        sql.Append("tbl_company.Company, ")
        sql.Append("tbl_devices.DeviceID, ")
        sql.Append("tbl_devices.FriendlyName, ")
        sql.Append("tbl_lookup_type_devices.DeviceVendor, ")
        sql.Append("tbl_lookup_type_devices.DeviceModel, ")
        sql.Append("tbl_lookup_type_devices.DeviceDescription ")
        sql.Append("FROM tbl_devices ")
        sql.Append("Join tbl_index_ticket_devices ")
        sql.Append("ON tbl_devices.DeviceID = tbl_index_ticket_devices.DeviceID ")
        sql.Append("Join tbl_company ")
        sql.Append("ON tbl_devices.EnterpriseID = tbl_company.EnterpriseID ")
        sql.Append("Join tbl_lookup_type_devices ")
        sql.Append("ON tbl_devices.DeviceTypeID = tbl_lookup_type_devices.DeviceTypeID ")
        sql.Append("WHERE tbl_index_ticket_devices.TicketID = '" & ticketID & "' ")
        sql.Append("ORDER BY tbl_index_ticket_devices.DateAdded ")
        Dim dr As SqlDataReader
        Dim conn As New SqlConnection(connectionString)
        If conn.State <> ConnectionState.Open Then conn.Open()
        Dim cmd As New SqlCommand(sql.ToString(), conn)
        cmd.CommandTimeout = 900
        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        SqlConnection.ClearPool(conn)
        Return dr
mathieu_cuprykAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

wht1986Commented:
Not quite sure what you mean by sql format, beacause the sql command is there in your code.  Just stripping out the string builder stuff (and adding a few table aliases) you have the below.

If this is not what you  are looking for, please clarify by what you mean as "sql format"
SELECT  TOP 100
  T.TicketSolutionComponentIndexID,
  T.DeviceID,
  T.TicketID,
  T.DateAdded,
  T.AgentAdded,
  C.Company,
  D.DeviceID,
  D.FriendlyName,
  L.DeviceVendor,
  L.DeviceModel,
  L.DeviceDescription,
FROM tbl_devices D
JOIN tbl_index_ticket_devices T  ON D.DeviceID = T.DeviceID
JOIN tbl_company C               ON D.EnterpriseID = C.EnterpriseID
JOIN tbl_lookup_type_devices L   ON D.DeviceTypeID = L.DeviceTypeID
WHERE T.TicketID = 'placeYourTicketIdHere'
ORDER BY T.DateAdded

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ddayx10Commented:
Hi,

I hope this helps. The SQL requires a parameter for ticketID so I added one (@ticketID). Of course you could just manually put in a ticketID at the appropriate location (replace the parameter with '[YOUR ID]'

dday
@ticketID bigint --I don't know the data type to give this
AS
SELECT  TOP 100
        tbl_index_ticket_devices.TicketSolutionComponentIndexID,
        tbl_index_ticket_devices.DeviceID,
        tbl_index_ticket_devices.TicketID,
        tbl_index_ticket_devices.DateAdded,
        tbl_index_ticket_devices.AgentAdded,
        tbl_company.Company,
        tbl_devices.DeviceID,
        tbl_devices.FriendlyName,
        tbl_lookup_type_devices.DeviceVendor,
        tbl_lookup_type_devices.DeviceModel,
        tbl_lookup_type_devices.DeviceDescription
        FROM tbl_devices
        Join tbl_index_ticket_devices
        ON tbl_devices.DeviceID = tbl_index_ticket_devices.DeviceID
        Join tbl_company
        ON tbl_devices.EnterpriseID = tbl_company.EnterpriseID
        Join tbl_lookup_type_devices
        ON tbl_devices.DeviceTypeID = tbl_lookup_type_devices.DeviceTypeID
        WHERE tbl_index_ticket_devices.TicketID = @ticketID
        ORDER BY tbl_index_ticket_devices.DateAdded

Open in new window

0
mathieu_cuprykAuthor Commented:
both of u are on the right track just the ticket ID need to be created,
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.