Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need right Data Type for SQL Server Field (VB6, SQL Server 2K)

    I declared a Type variable that holds a bunch of values that I'm testing against the fields on a form to see if they have been changed. If they have been changed I save those values to a string and append a vbCrLF to the end. The problem is in the SQL Server Database, this field (currently typed as a varchar) seems to be truncating everything after the vbCrLF. What is the proper datatype to use to have it accept even the binary characters (I've tried binary, that doesn't work).

    Below is the long if then conditions used to build the string, if it is helpful:

    With EData

    If .Fname <> txtFirstName.Text Then
    sUpdateStr = "Update FirstName From " & .Fname & " to " & txtFirstName.Text & vbCrLf
    End If

    If .Lname <> txtLastName.Text Then
    sUpdateStr = sUpdateStr & "Update LastName From " & .Lname & " to " & txtLastName.Text & vbCrLf
    End If

    If .Ext <> txtExt.Text Then
    sUpdateStr = sUpdateStr & "Update Ext From " & .Ext & " to " & txtExt.Text & vbCrLf
    End If

    If .Email <> txtEmail.Text Then
    sUpdateStr = sUpdateStr & "Update Email From " & .Email & " to " & txtEmail.Text & vbCrLf
    End If

    If .Epage <> txtEpager.Text Then
    sUpdateStr = sUpdateStr & "Update Epage From " & .Epage & " to " & txtEpager.Text & vbCrLf
    End If

    End With

  2. #2
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need right Data Type for SQL Server Field (VB6, SQL Server 2K)

    The assignment statements you show here don't reveal any relationship to SQL. Can you post the actual SQL syntax that fails? I'm not so sure you need a different datatype (there's only TEXT left if you ask me!)
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need right Data Type for SQL Server Field (VB6, SQL Server 2K)

    Here is the execute statement:

    CN.Execute ("INSERT INTO tblHistory " _
    & "(UserName, TermID, ActionDate, ActionDone) " _
    & "VALUES ('" & OSInfo.UserName & "', " _
    & "'" & OSInfo.ComputerName & "', " _
    & "'" & Now() & "', " _
    & "'Record " & EData.Fname & " " & EData.Lname & " Changed: " & sUpdateStr & "')")

  4. #4
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need right Data Type for SQL Server Field (VB6, SQL Server 2K)

    Nothing drives me to the *answer* here, but what happens if you just use a single chr(13) instead of the dual vbCRLf?
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need right Data Type for SQL Server Field (VB6, SQL Server 2K)

    That worked, Kevin, Thanks!

  6. #6
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need right Data Type for SQL Server Field (VB6, SQL Server 2K)

    Bingo! Rambling thoughts are sometimes meaningful.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

Posting Permissions

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