none
SQLJoins in Command Text to fill Dataset

    问题

  • Hi Guys,

     I have a query, when we use SQL Select statement with joins in SQLCommand text, and when i try to fill the dataset, its not returning any values and not filling the details view, and the row collection and evrything is empty, but when i do a select command for a single table its fetching record. and filling the details view.. Kindly advice me on this.

    Below is my code....

      Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            Try
                cname = Request.QueryString("cname")
                con = New SqlConnection()
                con.ConnectionString = ConfigurationManager.ConnectionStrings("Connect_WB0").ConnectionString

                cmd = New SqlCommand()

               cmd.CommandText = "SELECT v_R_System.Name0,AD_Site_Name0,v_GS_COMPUTER_SYSTEM.Description0,Manufacturer0,Model0,Domain0 from v_R_System INNER JOIN v_GS_COMPUTER_SYSTEM on v_R_System.ResourceID=v_GS_COMPUTER_SYSTEM.ResourceID where v_R_System.Name0='" + cname + "'"

                cmd.Connection = con

                da = New SqlDataAdapter(cmd)
                da.Fill(ds, "v_R_System")

                DetailsView1.DataSource = ds.Tables("v_R_System")
                DetailsView1.DataBind()

            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")
            End Try

        End Sub
    End Class

    • 已移动 Mark Liu-lxf 2012年7月6日 2:46 (From:Visual Basic General)
    2012年7月4日 14:40

答案

  • First of all if there will a messagbox shown that will be on the WebServer computer, you cannot use in asp.net a msgBox

    However, if that is not the case then

    There are two possibilities where it goes wrong with this code, either the script is wrong. 

    Use for that the SQLprofiler to see what happens, if it goes wrong this forum is for that SQL transact forum.

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/threads

    if it is not in the join then it is probably somewhere in your DataView so try for that the asp.net forum

    http://forums.asp.net

    Beside that strange messagebox I see not really something in the VB part.

    Be aware that you don't need that command part, there is an overload with which you can direct put the sql string in the constructor of the dataadapter.


    Success
    Cor

    2012年7月5日 5:06
  • Hi Jhansi,

    Could you post the result or message after you run the query in SQL Server management studio?

    If there are some data returns, it should be something wrong with the VB code and suggest you to check your code and you can also post it to VB forum for more support.

    Thanks for your support and understand.


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    2012年7月6日 7:40
    版主

全部回复

  • First of all if there will a messagbox shown that will be on the WebServer computer, you cannot use in asp.net a msgBox

    However, if that is not the case then

    There are two possibilities where it goes wrong with this code, either the script is wrong. 

    Use for that the SQLprofiler to see what happens, if it goes wrong this forum is for that SQL transact forum.

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/threads

    if it is not in the join then it is probably somewhere in your DataView so try for that the asp.net forum

    http://forums.asp.net

    Beside that strange messagebox I see not really something in the VB part.

    Be aware that you don't need that command part, there is an overload with which you can direct put the sql string in the constructor of the dataadapter.


    Success
    Cor

    2012年7月5日 5:06
  • Hi Jhansi,

    Could you post the result or message after you run the query in SQL Server management studio?

    If there are some data returns, it should be something wrong with the VB code and suggest you to check your code and you can also post it to VB forum for more support.

    Thanks for your support and understand.


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    2012年7月6日 7:40
    版主