Conversion from string "SELECT Users.LastName, Users.Mid" to type 'Double' is not val

[Brilho]

Conversion from string "SELECT Users.LastName, Users.Mid" to type 'Double' is not val

Hi!
I'm trying to implement the code below on my program, but when executing the code I'm receiving the error message below and I cannot find where it's. I also checked the .mdb file and cannot find any problem with the fields:

Conversion from string "SELECT Users.LastName, Users.Mid" to type 'Double' is not valid.​

This is de code on my PhoneDB.vb classe:​

Public Function UsersSearch(ByVal FirstName As String, ByVal LastName As String, ByVal Department As Integer, ByVal Location As Integer, ByVal Truncking As String) As DataSet

Dim SqlString As String
Me.FirstName = FirstName
Me.LastName = LastName
Me.Department = Department
Me.Location = Location
Me.Truncking = Truncking

SqlString = "SELECT Users.LastName, Users.MiddleInitials, Users.FirstName, Users.ExtensionNumber, Department.Code_Department, Department.Department_Name, Truncking.Truncking, CallSigns.CallSign, Locations.Location FROM Users, Truncking, Department, Locations, CallSigns, CostCenter WHERE Users.Code_Truncking = Truncking.Code_Truncking AND Users.Code_Depart = Department.Code_Department AND Department.Code_Location = Locations.Code_Location AND Users.Code_CallSign = CallSigns.Code_CallSing"

Dim CmdSelect As OleDbCommand

If Not (IsNullOrEmpty(Me.LastName)) Then
SqlString += " AND Users.LastName = " + " ' " + Me.LastName + " ' "
End If

If Not (IsNullOrEmpty(Me.FirstName)) Then
SqlString += " AND Users.FirstName = " + " ' " + Me.FirstName + " ' "
End If

If Not (IsNullOrEmpty(Me.Department)) Then
SqlString += " AND Department.Code_Department = " + Me.Department
End If

If Not (IsNullOrEmpty(Me.Location)) Then
SqlString += " AND Locations.Code_Location = " + Me.Location
End If

If Not (IsNullOrEmpty(Me.Truncking)) Then
SqlString += " AND Truncking.Code_Truncking = " + Me.Truncking
End If

SqlString += " ORDER BY Department.Department_Name, Users.Last.Name, Users.FirstName ASC"

CmdSelect = New OleDbCommand(SqlString, Conexao)

daUsersList = New OleDbDataAdapter(CmdSelect)

dsUsers = New DataSet()

dsUsers.Clear()

daUsersList.Fill(dsUsers, "Users")

Conexao.Close()

Return dsUsers

End Function

This is de code on my CliPhone.vb class:​

Private Sub btnsearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsearch.Click
Dim drowdep As DataRowView
Dim drowloc As DataRowView

If (txtfirstname.Text <> Nothing Or txtlastname.Text <> Nothing Or cbodepartment.Text <> Nothing Or cbolocation.Text <> Nothing Or txttruncking.Text <> Nothing) Then

Try
Me.StrFirstName = txtfirstname.Text
Me.StrLastName = txtlastname.Text

drowdep = cbodepartment.SelectedItem()
Me.IntDepartment = drowdep.Item("Code_Department")

drowloc = cbolocation.SelectedItem()
Me.IntLocation = drowloc.Item("Code_Location")

Me.IntTruncking = CStr(txttruncking.Text)

'If Not cbodepartment Is Nothing AndAlso cbodepartment.SelectedIndex >= 0 Then
'drowdep = cbodepartment.SelectedItem()
'IntDepartment = drowdep.Item("Code_Department")
' Else

dgrdetails.DataSource = Phn.UsersSearch(Me.StrFirstName, Me.StrLastName, Me.IntDepartment, Me.IntLocation, Me.IntTruncking)
dgrdetails.DataMember = "Users"

mnuPrtPreviewItem.Enabled = True
mnuPrintItem.Enabled = True


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

Else
Dim Msg As String
Msg = "Please fill one or more fields"
MsgBox(Msg, MsgBoxStyle.Information, "Missing Information")
End If

End Sub

 

[jmcilhinney]

Don't use '+' to concatenate strings. Use '&', which is the actual concatenation operator. When both operands are Strings then the '+' operator will perform a concatenation, but in your case one of the operands must be a Double, so it is trying to convert the String to a Double and add them.

 

[Brilho]

Thx for your reply. It helped me a lot and now I think which a refresh is required for my brain in a mean time hehehhe.
However and because nothing is perfect, I still have another problem with my code. In the code below I have a problem, because I need to include an IF statement which allows me to search for one of the Combobox at a time or both at same time. I don't want to select both at the same time. I tried to implement it using IF but there was so many IF statement that I was being crazy. Any idea?

Private Sub btnsearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsearch.Click
        Dim drowdep As DataRowView
        Dim drowloc As DataRowView

        If (txtfirstname.Text <> Nothing Or txtlastname.Text <> Nothing Or cbodepartment.Text <> Nothing Or cbolocation.Text <> Nothing Or txttruncking.Text <> Nothing) Then

            Try
                Me.StrFirstName = txtfirstname.Text
                Me.StrLastName = txtlastname.Text

                drowdep = cbodepartment.SelectedItem()
                Me.IntDepartment = drowdep.Item("Code_Department")

                drowloc = cbolocation.SelectedItem()
                Me.IntLocation = drowloc.Item("Code_Location")

                Me.IntTruncking = CStr(txttruncking.Text)

                'If Not cbodepartment Is Nothing AndAlso cbodepartment.SelectedIndex >= 0 Then
                'drowdep = cbodepartment.SelectedItem()
                'IntDepartment = drowdep.Item("Code_Department")
                '    Else
                
                dgrdetails.DataSource = Phn.UsersSearch(Me.StrFirstName, Me.StrLastName, Me.IntDepartment, Me.IntLocation, Me.IntTruncking)
                dgrdetails.DataMember = "Users"

                mnuPrtPreviewItem.Enabled = True
                mnuPrintItem.Enabled = True
                

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

        Else
            Dim Msg As String
            Msg = "Please fill one or more fields"
            MsgBox(Msg, MsgBoxStyle.Information, "Missing Information")
        End If

    End Sub

 

[cjard]

Once you finish this project, you should start doing your data access a differnt way.. The new way is easier and generates better, more secure, faster code. First, read the PQ link in my signature for more info on the truly horrendous way youre doing your SQLs right now, and why they are so horrendous.. (Sorry, no offence, but that way of writing SQLs is awful, and we have had better ways for 20 years now)
Then read the DW2 link in my sig, starting with "Creating a Simple Data App"

 

[Brilho]

Ok, thx for your advise. I will to try to follow it on my next project. However I still don't have answers for my question. Did anybody knows how I can solve the problem with my Comboboxes?

I'd appreciate a help.

 

[cjard]

For multipart searching, http://www.vbdotnetforums.com/showpost.php?p=67480&postcount=6