Problem mapping a VB data type to an ADO datatype (and then SQL 2000) 1

[nakkii]

I have a stored procedure on a SQL 2000 server that inserts a new record and
some of the fields are allowed to be NULL.

In VB6 using ADO I am appending/creating a parameter to be passed to the SQL
Server, but if the user doesn't put a value in, it needs to be set to "NULL"
explicitly.

So I am "Dim"ing a var:
DIM CellPhone As Variant

Checking to see if it is null, if so then I am setting the value of the var
to "NULL" otherwise it gets the value the user input:
CellPhone = IIf((Trim$(txt_ph_cell.Text)) = "", "Null", txt_ph_cell.Text)

This var is then used in the ADO command creation:
.Parameters.Append AddProspect.CreateParameter ("@pros_ph_cell", adBigInt,
adParamInput, 8, CellPhone)

Here is where I am getting the error:
"Application uses a wrong valueof the wrong type for the current operation."

According to

http://www.able-consulting.com/ADODataTypeEnum.htm

I should be
using the VB data type of "Variant", the ADO datatype of "adBigInt" and the
SQL datatype of BigInt.

The other two var I am checking work fine (MiddleInitial and Address2), but
CellPhone is getting the error. I have tried various combinations of
datatypes but can't seem to find the right one. If anyone could shed some
light on what I am doing wrong or what I am missing I would greatly
appreciate it.

Here is the code:
**********************************************************
Private Sub cmdSave_Click()
On Error GoTo SaveErr

Dim AddProspect As New ADODB.Command
Dim adoItemRS As New ADODB.Recordset
Dim MiddleInitial As String
Dim Address2 As String
Dim CellPhone As Variant

cn.CursorLocation = adUseClient
cn.Open "PROVIDER=SQLOLEDB;Data Source=ROBL\HR_DB;Integrated
Security=SSPI;Initial Catalog=HR_DB;"

Set AddProspect.ActiveConnection = cn

' The ADO command creation will not allow NULL values.
' If Null is needed must be explicitly set.
MiddleInitial = IIf(Len(Trim$(txt_nm_mi.Text)) = 0, "Null",
txt_nm_mi.Text)
Address2 = IIf(Len(Trim$(txt_addr.Text)) = 0, "Null", txt_addr.Text)
CellPhone = IIf((Trim$(txt_ph_cell.Text)) = "", "Null",
txt_ph_cell.Text)

' Create command and parameters
With AddProspect
.CommandType = adCmdStoredProc
.CommandText = "ap_newprospect"

.Parameters.Append AddProspect.CreateParameter (, adInteger,
adParamReturnValue)

.Parameters.Append AddProspect.CreateParameter ("@pros_dtl_id",
adInteger, adParamOutput)

.Parameters.Append AddProspect.CreateParameter ("@pros_id",
adInteger, adParamOutput)

.Parameters.Append AddProspect.CreateParameter ("@pros_ssn",
adInteger, adParamInput, 4, txt_req(2).Text)

.Parameters.Append AddProspect.CreateParameter ("@pros_nm_last",
adVarChar, adParamInput, 25, txt_req(0).Text)

.Parameters.Append AddProspect.CreateParameter
("@pros_nm_first", adVarChar, adParamInput, 20, txt_req(1).Text)

.Parameters.Append AddProspect.CreateParameter ("@pros_nm_mi",
adVarChar, adParamInput, 4, MiddleInitial)

.Parameters.Append AddProspect.CreateParameter ("@pros_addr1",
adVarChar, adParamInput, 50, txt_req(4).Text)

.Parameters.Append AddProspect.CreateParameter ("@pros_addr2",
adVarChar, adParamInput, 50, Address2)

.Parameters.Append AddProspect.CreateParameter ("@pros_city",
adVarChar, adParamInput, 25, txt_req(5).Text)

.Parameters.Append AddProspect.CreateParameter ("@pros_state",
adVarChar, adParamInput, 4, txt_req(6).Text)

.Parameters.Append AddProspect.CreateParameter ("@pros_zip",
adInteger, adParamInput, 4, txt_req(7).Text)

.Parameters.Append AddProspect.CreateParameter ("@pros_ph_home",
adBigInt, adParamInput, 8, txt_req(3).Text)

.Parameters.Append AddProspect.CreateParameter ("@pros_ph_cell",
adBigInt, adParamInput, 8, CellPhone)

.Execute
End With

 

[ClydeDoggie]

I believe the reason some are working and one isn't is because you are trying to put the string 'null' instead of the value Null into the parameter. Some of the parameters accept strings so it appears they are taking the Null value, but I bet if you check the record you will see the word 'null'. Try using the VB keyword vbNull with no quotes. I haven't tried it so I can't guarantee it will work but I don't think what you have will work in any case.

 

[lovell811]

I agree with ClydeDoggie, you need to use either vbNull or IsNull() in your if statements. ----------------
Joe

 

[nakkii]

Thanks. That worked. I changed all three "NULL" to vbNull and that took care of it.