Results 1 to 7 of 7
  1. #1
    Join Date
    May 2002
    Posts
    395

    Unanswered: pass in null/blank value in the date field or declare the field as string and convert

    I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.

    I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits.
    The mfg_start_date is delcared as a string variable

    mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)))

    option 1
    I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.

    With refresh_shipping_sched
    .ActiveConnection = CurrentProject.Connection
    .CommandText = "spRefresh_shipping_sched"
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
    .Parameters.Append .CreateParameter("@option", adInteger, adParamInput, 4, update_option)
    .Parameters.Append .CreateParameter("@mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "")
    .Parameters.Append .CreateParameter("@mfg_start_date", adChar, adParamInput, 10, "")
    Set rs_refresh_shipping_sched = .Execute
    End

    Please help

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The stored procedure will accept null if you define the parameter that way:

    create procedure TESTPROCEDURE (@TestDate datetime = NULL)
    as
    select @TestDate
    go

    exec TESTPROCEDURE '1/1/2003'
    go

    exec TESTPROCEDURE
    go

    blindman

  3. #3
    Join Date
    Dec 2003
    Posts
    15
    I think vbNull also works when passing in a parameter. The code you have below is passing in an empty string which I'm sure I don't have to tell you is not null.

    Try the following:
    .Parameters.Append .CreateParameter("@mfg_start_date", adChar, adParamInput, 10, vbNull)

    I think the stored proc idea is better though, it's safer and better for your data integrity.

    Dan

  4. #4
    Join Date
    May 2002
    Posts
    395

    empty string for the date

    Originally posted by danielacroft
    I think vbNull also works when passing in a parameter. The code you have below is passing in an empty string which I'm sure I don't have to tell you is not null.

    Try the following:
    .Parameters.Append .CreateParameter("@mfg_start_date", adChar, adParamInput, 10, vbNull)

    I think the stored proc idea is better though, it's safer and better for your data integrity.

    Dan
    Hello Dan,

    What I need is an empty string in the date field to pass in in the stored procedure. What is the vb code for that?

    Thanks!

  5. #5
    Join Date
    Dec 2003
    Posts
    15
    The code to pass null (empty string won't work and null will only work if you have allowed nulls on this column in your db design) for a parameter is this:

    Code:
    .Parameters.Append .CreateParameter("@mfg_start_date", adChar, adParamInput, 10, vbNull)
    I modified your existing code. I'm not 100% sure that this will work but it should.

    Dan

  6. #6
    Join Date
    May 2002
    Posts
    395
    Originally posted by danielacroft
    The code to pass null (empty string won't work and null will only work if you have allowed nulls on this column in your db design) for a parameter is this:

    Code:
    .Parameters.Append .CreateParameter("@mfg_start_date", adChar, adParamInput, 10, vbNull)
    I modified your existing code. I'm not 100% sure that this will work but it should.

    Dan
    Thanks for replying so quickly.
    I edited my code as you have it above.
    I'm stilll having trouble getting the date displaying correctly. I need the year to display in four digits. It displays something '12/31/03'
    This is code that I have

    Function get_date(mfg_start_date as string,..)
    mfg_start_date = Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)
    mfg_start_date = CStr(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Year(mfg_start_date))


    mfg_start_date is the textbox I need the date field to display but it will only eight digits of the year and place two empty strings after. I can't understand why. In the db design the mfg_start_date field is a char with length 10 as in the stored procedure.

    Also there must be a better way to write the code that I have above.

    Thank you again.

  7. #7
    Join Date
    Dec 2003
    Posts
    15
    The date format is normally determined by the locale settings ont he server when you're using VB. Can I ask why you're not using a date field in your database?

    Dan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •