Data Type Mismatch in SQL code

Teqlump · September 25th, 2004, 10:54 PM

Can anyone tell me why I am getting a data type mismatch in the following SQL code?:

sql = "select * from tblPrintersLumpkin where PrinterID = " & _
Val(Me.txtPrinterIDLumpkin.Value)

I am using this SQL code for a Delete Button in my VBA code for an Access Database.

joefawcett · September 26th, 2004, 04:08 AM

Try without the .value part:

Code:

sql = "select * from tblPrintersLumpkin where PrinterID = " & _
        Val(Me.txtPrinterIDLumpkin)

and maybe use a specific converter:

Code:

sql = "select * from tblPrintersLumpkin where PrinterID = " & _
        CLng(Me.txtPrinterIDLumpkin)

--
Joe

Teqlump · September 26th, 2004, 12:50 PM

Hello All,
I tried changing that converter to CLng and I omitted the Value property like Joe said, but it still says there is a Type mismatch. I have inserted all of the code for the Delete button below. Can someone give me some assistance in trying to debug this problem. I will work on it myself as well, but the problem is not that obvious.

Private Sub cmdDeleteLumpkin_Click()
    Dim sql As String
    Dim rsDelete As New ADODB.Recordset

    'On Error GoTo DbError

    ' Build dynamic SQL statement based on record selected by the user.
    sql = "select * from tblPrintersLumpkin where PrinterID = " & _
        CLng(Me.txtPrinterIDLumpkin)

    ' Assign updatable cursor and lock type properties.
    rsDelete.CursorType = adOpenDynamic
    rsDelete.LockType = adLockOptimistic

    ' Open the Recordset object.
    rsDelete.Open sql, localConnection, , , adCmdText

    ' Don't try to delete the record, if the recordset did not find a row.
    If rsDelete.EOF = False Then

    ' Update the record based on input from the user.
        With rsDelete
            .Delete
            .Update
            .Close
        End With

    End If

    MsgBox "Record deleted.", vbInformation

    ' Close the form-level Recordset object and refresh it to include
    ' the newly updated row.
    rsPrinters.Close
    SetRecordset

    Exit Sub

'DbError:

    'MsgBox "There was an error deleting the record." _
            '& Err.Number & ", " & Err.Description
End Sub

Let me thank joefawcett and anyone else who is willing to help me. I appreciate it greatly.

happygv · September 26th, 2004, 01:31 PM

Hi,

Can you tell us what is the datatype of PrinterId in your table(Hope you are using a TEXT type there) and what is the value that get passed from "Me.txtPrinterIDLumpkin.Value"

Cheers!

_________________________
- Vijay G
Strive for Perfection

Teqlump · September 26th, 2004, 02:12 PM

Thanks Happy,
It is indeed a Text data type, and the value that will be returned by txtPrinterIDLumpkin is a 7-10 character ID that we use for our printers. The ID is in the following format: JD234AD. The ID's start with two text characters which represent what type of device the printer is connected to(ie. JD stands for Jet Direct), they have a 3 digit building number in the middle and end with two text characters which is just a standard ID used for identification. I hope this is what you meant by what type of value is returned by txtPrinterIDLumpkin. Thanks a million for your help.

happygv · September 26th, 2004, 02:48 PM

Then wrap the value with singe quotes around and remove VAL/CLNG function from there.

Code:

sql = "select * from tblPrintersLumpkin where PrinterID = '" & _
        Me.txtPrinterIDLumpkin.Value & "'"

Cheers!

_________________________
- Vijay G
Strive for Perfection

Teqlump · September 26th, 2004, 09:51 PM

Thank you so much happygy, that worked. Now all I have to do is figure out why the single quotes work better than double quotes.

pgtips · September 27th, 2004, 03:03 AM

Can I just say that's a long-winded way of deleting a record. Why don't you just use SQL's DELETE statement?

Anantsharma · September 27th, 2004, 05:44 AM

HI,

Well happy know that ur work is done. SQL Server expects Character datatype values (Varchar,Char etc) and DATE type values enclosed within Single quotes. Keep this thing in mind whlie forming ur query
in code. Well I always prefer to use "+" to form a query string through VB/VB.NET. Its clear to read and data type checking is much clearer.

Dim SqlStr as String
SqlStr = ""
SqlStr = "Select * From Trans_Inv_Voucher Where"
SqlStr =SqlStr +" VDNO = '" + trim(TxtVdNo) +"' AND"
SqlStr =SqlStr +" InvAmount = " + trim(TxtInvAmt) +" AND"
SqlStr =SqlStr +" GLDate = '" + Format(Date,"MM/dd/yyyy") +"'"

Set RsVoucherHeader = ConnManager.Execute(SqlStr)

Cheers.

B. Anant

Teqlump · September 27th, 2004, 10:49 PM

pgtips has a good point. But after I got my Delete button working and I read what he wrote, I noticed there was something I was missing. My database has a many to many relationship. When I delete a printer, that printer could be on 4 different systems. I need to be able to choose which system I want to delete the printer off of. After I choose which system I want to delete the printer off of and I delete the printer, I want the printer information to go to a deleted table so we can keep track of the printers that we delete. I have been thinking about this and it would seem like I should use some SQL code to copy the printer and it's config information to the deleted table first and then delete it. Is there code that does this. I mean I am going to need to delete this information from 2 tables, my junction table and my printers table. Is there anyone that could give me a hint as to how I should go about doing this? I would really appreciate the help. Thanks.

Teqlump