Pass Blank Date Parameters From Vb6 to SQL 2k

[C0PP3R]

I am trying to pass a set of paremters to a sql stored procedure, various ints, varchars and dates. Now m yprog save say 20 fields, but only 10 are required so the function must be able to run with the bare bones data,

now this should not be a problem, except, I use three dates in the stored proc, two of them are required, one is not, and I get method parameter failed for object dbcommand when i try adn pass a "" txtDateclosed, cod eis vbelow, any help on this woudl be greatly appreciated.

Set dbcommand = New ADODB.Command
dbcommand.ActiveConnection = dbconn


dbcommand.CommandType = adCmdStoredProc
dbcommand.CommandText = "spAddUpdateWarrantyOEM"

'add the paremters

With dbcommand
.Parameters.Append .CreateParameter("P1", adVarChar, adParamInput, 50, txtWarFileNo.Text)

.Parameters.Append .CreateParameter("P2", adVarChar, adParamInput, 50, txtWONo.Text)

.Parameters.Append .CreateParameter("P3", adVarChar, adParamInput, 50, txtPreviousWO.Text)

.Parameters.Append .CreateParameter("P4", adVarChar, adParamInput, 50, txtPartNumber.Text)

.Parameters.Append .CreateParameter("P5", adInteger, adParamInput)
.Parameters("P5").Value = cboWarrantyCause.ItemData(cboWarrantyCause.ListIndex)

.Parameters.Append .CreateParameter("P6", adInteger, adParamInput)
.Parameters("P6").Value = cboWarrantyType.ItemData(cboWarrantyType.ListIndex)

.Parameters.Append .CreateParameter("P7", adInteger, adParamInput)
.Parameters("P7").Value = cboOEMDepartment.ItemData(cboOEMDepartment.ListIndex)

.Parameters.Append .CreateParameter("P8", adDBDate, adParamInput, 8, txtDateOpened.Text)
this is the line where i get the error, so I know the date field that has a value is working, but it won't let me pass a blank date

("P9", adDBDate, adParamInput, 8, txtDateClosed.Text)

as the above field is not required it msut be allowed to be blank

Just a note - i searched all ove rthis siote to find a solution b4 postin gthis.

Thanks all!

 

[Swi]

Try setting it to a NULL

("P9", adDBDate, adParamInput, 8, IIf(IsDate(txtDateClosed.Text), txtDateClosed.Text, Null))

Swi

 

[fredericofonseca]

replace
.Parameters.Append .CreateParameter("P8", adDBDate, adParamInput, 8, txtDateOpened.Text)
with
if txtdateopened.text = "" then
.Parameters.Append .CreateParameter("P8", adDBDate, adParamInput, 8, null)
else
.Parameters.Append .CreateParameter("P8", adDBDate, adParamInput, 8, txtDateOpened.Text)
endif

A date or time related field can either have a valid value or be NULL. Nothing else is possible.

Bear in mind that adDBDate is not supported by some DB, SQL Server being one of them, so it may be advisable to use another definition such as adDBDatetime.

And is this code going to be executed many times on the life of the application, or is a "execute once" and leave program?

If the first, then you should not be doing it like that, but more like the following.

If sUpdateSql = "" Then
Set adoUpdateComm = New ADODB.Command
adoUpdateComm.ActiveConnection = DBConn
sUpdateSql = "sp_update_tablex"
adoUpdateComm.CommandType = adCmdStoredProc
adoUpdateComm.CommandText = sUpdateSql
adoUpdateComm.Prepared = True
adoUpdateComm.Parameters.Append adoUpdateComm.CreateParameter("VARX", adInteger, adParamInput, 4)
' more parameter definitions
End If
adoUpdateComm.Parameters("VARX").Value = 173
adoUpdateComm.Execute , , adExecuteNoRecords

and then keep the adoUpdateComm active until the end of session.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[HezMac]

Hi

I'm trying to call a function and am not able to pass the NULL dates. Any suggestions how to get around that?

I call the function from the click below, and get an error at the click saying: "Invalid Use of Null"

Private Sub cmdTestCount_Click()

Dim SelectedIndateBef As Date
Dim SelectedIndateAft As Date

Call CB_Test_Count(txtBalGreater.Text, txtBalLess.Text, _
dtpIndateBefore.Value, dtpIndateAfter.Value)


End Sub

 

[C0PP3R]

Awesome, thats perfect!

Thanks allot!

GO TEK TIPS!