none
Query SELECT with WHERE conidition and parameters RRS feed

  • Question

  • Hi all

    I'm trying to read records from DB using the following statments

    #Region "GET BY ID" Public Function TabRegioniGetByID(ByVal pID As Integer) As DSTabRegioni dim ID = pID mHasException = False mHasSqlException = False mHasCurrException = False DAL_DS = New DSTabRegioni Dim QuerySelectById As String = <sql> SELECT [RegioneID] ,[RegioneDes] ,[RegioneNazioneID] FROM Tabel.TabRegioni WHERE [RegioneID]= @ID </sql>.Value Try Using CONN As New SqlConnection With {.ConnectionString = VG.FullCS} Using CMD = New SqlCommand() With {.Connection = CONN, .CommandType = CommandType.Text} Using DAL_DA As New SqlDataAdapter With {.SelectCommand = New SqlCommand With {.Connection = CONN}} CONN.Open() DAL_DA.SelectCommand.CommandText = QuerySelectById CMD.Parameters.AddWithValue("@ID", ID) DAL_DA.Fill(DAL_DS, "TabRegioni")

    CONN.Close() Return DAL_DS End Using End Using End Using Catch sqlex As SqlException mHasSqlException = True mLastSqlException = sqlex Catch ex As Exception mHasException = True mLastException = ex End Try Return Nothing End Function

    It gives me error Dataset is nothing but,

    if I change the @ID in the WHERE condition with a number every thing works well

    So the problem is in the parameter. I check the pID and it is has right value and type (int32) 

    So I cannot find what is wrong.

    Can you help me ?

    Friday, December 7, 2018 9:17 PM

Answers

  • hello,

    can you try this: 

    Try
      Using CONN As New SqlConnection With {.ConnectionString = VG.FullCS}
          Using CMD = New SqlCommand() With {.Connection = CONN,
              .CommandType = CommandType.Text, .CommandText = QuerySelectById}
              ' we prepare our command parameters
              CMD.Parameters.AddWithValue("@ID", ID)
              ' and we use it as a SelectCommand for our DataAdapter
              Using DAL_DA As New SqlDataAdapter With
                {.SelectCommand = CMD}
                  CONN.Open()
    
                  DAL_DA.Fill(DAL_DS, "TabRegioni")
    
                  CONN.Close()
                  Return DAL_DS
              End Using
          End Using
      End Using
    Catch sqlex As SqlException
      mHasSqlException = True
      mLastSqlException = sqlex
    Catch ex As Exception
      mHasException = True
      mLastException = ex
    
    End Try

    here is my remark:

    Using CMD = New SqlCommand() With {.Connection = CONN,
      .CommandType = CommandType.Text}
      
      Using DAL_DA As New SqlDataAdapter With
      {.SelectCommand = New SqlCommand With {.Connection = CONN}}
      ' in the line above your instantiate a new command
      ' you should use this:   {.SelectCommand = CMD, .Connection = CONN}
          CONN.Open()
    
          DAL_DA.SelectCommand.CommandText = QuerySelectById
          CMD.Parameters.AddWithValue("@ID", ID) ' but here you are adding parameters to the CMD command which is not what DAL_DA use
          DAL_DA.Fill(DAL_DS, "TabRegioni")
    
          CONN.Close()
          Return DAL_DS
      End Using
    End Using

    hope it helps ;) 

    Good Coding;


    • Edited by Cherkaoui.Mouad Monday, December 10, 2018 8:39 AM
    • Marked as answer by Claudio111 Monday, December 10, 2018 6:06 PM
    Friday, December 7, 2018 9:41 PM

All replies

  • hello,

    can you try this: 

    Try
      Using CONN As New SqlConnection With {.ConnectionString = VG.FullCS}
          Using CMD = New SqlCommand() With {.Connection = CONN,
              .CommandType = CommandType.Text, .CommandText = QuerySelectById}
              ' we prepare our command parameters
              CMD.Parameters.AddWithValue("@ID", ID)
              ' and we use it as a SelectCommand for our DataAdapter
              Using DAL_DA As New SqlDataAdapter With
                {.SelectCommand = CMD}
                  CONN.Open()
    
                  DAL_DA.Fill(DAL_DS, "TabRegioni")
    
                  CONN.Close()
                  Return DAL_DS
              End Using
          End Using
      End Using
    Catch sqlex As SqlException
      mHasSqlException = True
      mLastSqlException = sqlex
    Catch ex As Exception
      mHasException = True
      mLastException = ex
    
    End Try

    here is my remark:

    Using CMD = New SqlCommand() With {.Connection = CONN,
      .CommandType = CommandType.Text}
      
      Using DAL_DA As New SqlDataAdapter With
      {.SelectCommand = New SqlCommand With {.Connection = CONN}}
      ' in the line above your instantiate a new command
      ' you should use this:   {.SelectCommand = CMD, .Connection = CONN}
          CONN.Open()
    
          DAL_DA.SelectCommand.CommandText = QuerySelectById
          CMD.Parameters.AddWithValue("@ID", ID) ' but here you are adding parameters to the CMD command which is not what DAL_DA use
          DAL_DA.Fill(DAL_DS, "TabRegioni")
    
          CONN.Close()
          Return DAL_DS
      End Using
    End Using

    hope it helps ;) 

    Good Coding;


    • Edited by Cherkaoui.Mouad Monday, December 10, 2018 8:39 AM
    • Marked as answer by Claudio111 Monday, December 10, 2018 6:06 PM
    Friday, December 7, 2018 9:41 PM
  • Hi,

    this works well now,

    Just a little mistake since .connection is not a member of DataAdapter

    Thank you very much

    Friday, December 7, 2018 10:48 PM
  • Welcome :)

    happy to help!

    Good Coding;


    Saturday, December 8, 2018 12:33 AM
  • Hi,

    this works well now,

    Just a little mistake since .connection is not a member of DataAdapter

    Thank you very much

    Hi,

    Please mark the useful replies as answers.

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 10, 2018 7:28 AM