none
SQL Query RRS feed

  • Question

  •  

    Hi,

     

    I'm having a problem with a litlle lookup SQL, i'm trying to return the cost of an item from an Equipment table on the basis of a partcode.

    Please see the code


    Code Snippet

    Dim SQLtext, a As String
            a = Me.PurchaseDetailsTableAdapter.Connection.ConnectionString.Clone()
            Dim myCon As New SqlClient.SqlConnection(a)
            SQLtext = "SET @CostPrice=(SELECT CostPrice FROM EquipmentID WHERE (EquipmentID=@EquipmentID))"

            Dim myCom As New SqlClient.SqlCommand(SQLtext, myCon)

            Dim EquipmenID = "PA-01-01"
            Dim i As Integer = 0

            myCom.Parameters.Add(New SqlClient.SqlParameter("@EquipmentID", SqlDbType.VarChar)).Value = EquipmentID
            i = myCom.Parameters.Add(New SqlClient.SqlParameter("@CostPrice", SqlDbType.Money)).Direction = ParameterDirection.Output

            myCon.Open()
            myCom.ExecuteNonQuery()
            myCon.Close()
            MessageBox.Show(i)

     

     

     

    However, when i run it i get this error message: -

     

    "The parameterized query '(@EquipmentID varchar(8),@CostPrice money)SET @CostPrice=(SELECT' expects the parameter '@CostPrice', which was not supplied."

     

    Why do i need to supply a value for something i want the value returned on?

    What am i doing wrong?

     

    Thanks

    Thursday, August 2, 2007 9:05 AM

Answers

  • You need to use ExecuteScalar without CostPrice parameter in this case. Your code should look like

     

     

    Dim SQLtext, a As String
            a = Me.PurchaseDetailsTableAdapter.Connection.ConnectionString.Clone()
            Dim myCon As New SqlClient.SqlConnection(a)
            SQLtext = "SELECT CostPrice FROM EquipmentID WHERE (EquipmentID=@EquipmentID)"

            Dim myCom As New SqlClient.SqlCommand(SQLtext, myCon)

            Dim EquipmenID = "PA-01-01"
            Dim i As Integer = 0

            myCom.Parameters.Add(New SqlClient.SqlParameter("@EquipmentID", SqlDbType.VarChar)).Value = EquipmentID
            myCon.Open()
            i=myCom.ExecuteScalar()
            myCon.Close()
            MessageBox.Show(i)

    Thursday, August 2, 2007 9:56 AM
    Moderator
  • The SET statement isn't necessary. The SELECT alone should be sufficient if I understand correctly what you are doing. Then you should be able to use ExecuteScalar since only one column is being returned in your result.

     

    Thursday, August 2, 2007 11:48 AM
  •  Jon1s wrote:

    I though the ExecuteScalar() only returned the vaule of Column(0) of the inserted row? can you use it to return values as well?

     

    the costprice would be in colum(3).

     

    thanks for your help!

     

     

    Hi,

     

    To further explain why this is happening, when you use "SELECT CostPrice FROM Equipment ..." as a select statement. It means that you are ONLY retrieving the CostPrice field. Even if your CostPrice is column(3) in your table structure, based on the result of your select statement it will be column(0) since you only requested for a single field.

    Thus, using ExecuteScalar() would get the job done.

     

     

    cheers,

     

    Paul June A. Domag

    Thursday, August 2, 2007 3:22 PM
  • You can either use an SQLDataReader or a DataSet/DataTable.

     

    Friday, August 3, 2007 12:46 PM

All replies

  • You need to use ExecuteScalar without CostPrice parameter in this case. Your code should look like

     

     

    Dim SQLtext, a As String
            a = Me.PurchaseDetailsTableAdapter.Connection.ConnectionString.Clone()
            Dim myCon As New SqlClient.SqlConnection(a)
            SQLtext = "SELECT CostPrice FROM EquipmentID WHERE (EquipmentID=@EquipmentID)"

            Dim myCom As New SqlClient.SqlCommand(SQLtext, myCon)

            Dim EquipmenID = "PA-01-01"
            Dim i As Integer = 0

            myCom.Parameters.Add(New SqlClient.SqlParameter("@EquipmentID", SqlDbType.VarChar)).Value = EquipmentID
            myCon.Open()
            i=myCom.ExecuteScalar()
            myCon.Close()
            MessageBox.Show(i)

    Thursday, August 2, 2007 9:56 AM
    Moderator
  • I though the ExecuteScalar() only returned the vaule of Column(0) of the inserted row? can you use it to return values as well?

     

    the costprice would be in colum(3).

     

    thanks for your help!

     

    Thursday, August 2, 2007 10:18 AM
  • The SET statement isn't necessary. The SELECT alone should be sufficient if I understand correctly what you are doing. Then you should be able to use ExecuteScalar since only one column is being returned in your result.

     

    Thursday, August 2, 2007 11:48 AM
  •  Jon1s wrote:

    I though the ExecuteScalar() only returned the vaule of Column(0) of the inserted row? can you use it to return values as well?

     

    the costprice would be in colum(3).

     

    thanks for your help!

     

     

    Hi,

     

    To further explain why this is happening, when you use "SELECT CostPrice FROM Equipment ..." as a select statement. It means that you are ONLY retrieving the CostPrice field. Even if your CostPrice is column(3) in your table structure, based on the result of your select statement it will be column(0) since you only requested for a single field.

    Thus, using ExecuteScalar() would get the job done.

     

     

    cheers,

     

    Paul June A. Domag

    Thursday, August 2, 2007 3:22 PM
  • cool thanks for the explaination!

    Thursday, August 2, 2007 4:27 PM
  • hi,

     

    so how do i output more than one value what execute command do i have to use and how can i assign those values to variables?

    Friday, August 3, 2007 7:40 AM
  • You can either use an SQLDataReader or a DataSet/DataTable.

     

    Friday, August 3, 2007 12:46 PM