"number of query values and destination fields are not the same" error in sql?

[emaduddeen]

"number of query values and destination fields are not the same" error in sql?

Hi Everyone,

Can you check the syntax of this sql statement?

I get an error that says "number of query values and destination fields are not the same"

This started to happen when I added Address1 to the statement and Address1 is really a column in the database table.

Can you tell me how to correct the syntax?

                    ' Check if the parent is already in the database.
                    '------------------------------------------------
                    strSqlStatement = _
                        "SELECT 1 " & _
                          "FROM Parent " & _
                         "WHERE FirstName = " & _
                                "'" & strFirstName & "' " &
                           "AND LastName = " & _
                                "'" & strLastName & "' " &
                           "AND Address1 = " & _
                                "'" & strAddressLine & "' "

Truly,
Emad

 

[jmcilhinney]

First up, don;t use string concatenation to insert values into SQL code. ALWAYS use parameters. Follow the Blog link in my signature and check out my post on the topic for more information. Your query should be:

SELECT COUNT(*) FROM Parent WHERE FirstName = @FirstName AND LastName = @LastName AND Address1 = @Address1

You then add the appropriate parameters to your OleDbCommand and call ExecuteScalar. That will return either zero or non-zero. Zero means no matches and non-zero means one or more matches.

 

[emaduddeen]

Hi,

I like the parameter way better. You don't have to worry about the quote marks and the other stuff like that.

I still get the same error though.

Can you check my syntax?

                    ' Check if the parent is already in the database.
                    '------------------------------------------------
                    strSqlStatement = _
                        "SELECT COUNT(*) " & _
                          "FROM Parent " & _
                         "WHERE FirstName = @parameter1 " & _
                           "AND LastName = @parameter2 " & _
                           "AND Address1 = @parameter3"

                    If objClassDatabaseObjects.GetIntegerScalarValue(strSqlStatement, _
                                                                     strFirstName, _
                                                                     strLastName, _
                                                                     strAddressLine) > 0 Then

Here's the code in objClassDatabaseObjects

    Public Function GetIntegerScalarValue(ByVal pSqlQuery As String,
                                          ByVal pParameter1 As String,
                                          ByVal pParameter2 As String,
                                          ByVal pParameter3 As String) As Integer
        Dim intValueFound = Nothing

        Try

            ' Create the connection object to use an SQL query and open it.
            '--------------------------------------------------------------
            objConnection = GetDataConnection()
            objConnection.Open()

            ' Set up a command to execute the SQL.
            '-------------------------------------
            objCommand = New OleDbCommand(pSqlQuery, objConnection)
            objCommand.CommandType = CommandType.Text

            If pParameter1 <> Nothing Then
                objCommand.Parameters.Add(New OleDbParameter("@parameter1", pParameter1))
            End If

            If pParameter2 <> Nothing Then
                objCommand.Parameters.Add(New OleDbParameter("@parameter2", pParameter2))
            End If

            If pParameter3 <> Nothing Then
                objCommand.Parameters.Add(New OleDbParameter("@parameter3", pParameter3))
            End If

            intValueFound = objCommand.ExecuteScalar

        Catch ex As Exception
            MessageBox.Show(ex.Message)

        Finally
            ' Close the connection if it's currently open.
            '---------------------------------------------
            If objConnection IsNot Nothing Then
                objConnection.Close()
            End If
        End Try

        Return intValueFound
    End Function

Thanks.

Truly,
Emad

 

[jmcilhinney]

Try adding an alias:

"SELECT COUNT(*) AS ParentCount "

 

[emaduddeen]

Hi,

Tried it but still the same message.

I even tried:

                    strSqlStatement = _
                        "SELECT COUNT(*) AS ParentCount " & _
                          "FROM Parent " & _
                         "WHERE FirstName = @parameter1 "

and still the same message.

I'm really confused now.

It worked the other way only if I used the first name and last name columns but chocked up when I used the address1 column but want to really use the parameter way instead.

Truly,
Emad

 

[jmcilhinney]

Are you passing all three values when you execute the query? Your code contains a bug that I noticed from the start but didn't think was related to this issue but I'm starting to think it might be. Call your method and make sure to pass a value for all three parameters and see if it works.

 

[emaduddeen]

Hi,

Wow. I found the error. Just a simple character was the cause.

It's the > 0 in this code:

 If objClassDatabaseObjects.GetIntegerScalarValue(strSqlStatement, _
                                                                     strFirstName, _
                                                                     strLastName, _
                                                                     strAddressLine) > 0 Then

Right past this code in the IF statement is an INSERT sql statement and that also had the incorrect number of value columns just as the error indicated. Also the > 0 needed to be changed to = 0 because I only want to insert if nothing is found otherwise I want to update.

Anyway thanks for being over my shoulder on this one.

I appreciate all the time you took to help.

Truly,
Emad

 

[cjard]

note that if your query contains 3 parameters, you should NOT conditionally add parameters.

Wrong:

If pParameter1 <> Nothing Then
                objCommand.Parameters.Add(New OleDbParameter("@parameter1", pParameter1))
            End If

            If pParameter2 <> Nothing Then
                objCommand.Parameters.Add(New OleDbParameter("@parameter2", pParameter2))
            End If

            If pParameter3 <> Nothing Then
                objCommand.Parameters.Add(New OleDbParameter("@parameter3", pParameter3))
            End If

Right:

If pParameter1 = Nothing OR pParameter2 = Nothing OR pParameter3 = Nothing Then
           Throw New ArgumentNullException("All 3 parameters must be provided because they are used in the query")

Or set some default values for the parameters. NOT adding a parameter to the collection when you have specified it in the command will cause an error

 

[emaduddeen]

Hi Cjard,

Thanks for the code.

I'm adding it now.

Truly,
Emad