Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Data in my database always have blank Characteres

Posted on 2010-11-18
13
Medium Priority
?
507 Views
Last Modified: 2012-05-10
Hi Experts,

I created a form with VB for Mycrosoft Dynamics. Basically this form records information from my user and stores the data in my SQL database in its own table.

The form works great, but the problem that I have is everytime I save the data , some empty characters gets filled at the end of my field .
for example: if field A has "hello" my database saves it as "hello____"

so I am not sure why this happens, is it the form that pass the blank characters or is it my SQL that has the wrong definition of the field.
attached is my code, a picture of my form with the trailing blank and also a snapshot of my SQL data.
How can I solve this ??

Thanks
David
'Cylinder TestForm
'Requirements: frm20.dll needs to be register.
'References Microsoft ActiveX Data Object 2.8 library
'           Microsoft Calendar Control 11.0
' the calendar Control needs to be register first by using the MSCAL.OCX
'Table used in SQL is PSHTable1

Option Explicit
Dim cn As New ADODB.connection
Dim Rst As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim binChanged As Boolean
Dim strsql As String


'This is loaded when the form is open first
Private Sub UserForm_Initialize()
    ' set the calendar of the date of today
    Calendar1.Today
    'set the variable to false (no change has been made to the form)
    binChanged = False
    'Retrieve the Work Order # from the WorkOrderEntry window
    lblWorOrd = WorkOrderEntryUpdate.WorkOrderNumber.Value
    'Retrieve an ADO Connection for the current user
    Set cn = UserInfoGet.CreateADOConnection
    'Set the current Database company
    cn.DefaultDatabase = UserInfoGet.IntercompanyID
    cmd.ActiveConnection = cn
    'Check in the database if the record exists in the database
    strsql = " Select count(*) from PSHTable1 where WORKORDNUM ='" & lblWorOrd & "'"
    Set Rst = cn.Execute(strsql)

' If records exists, we load the data to the form
    If Rst.Fields(0).Value = 1 Then
        strsql = " Select * from PSHTable1 where WORKORDNUM ='" & lblWorOrd & "'"
        Set Rst = cn.Execute(strsql)
        txtRepTech = Rst.Fields("REPTECH").Value
        txtTesTech = Rst.Fields("TESTTECH").Value
        txtOilTemp = Rst.Fields("OILTEMP").Value
        txtBrePres = Rst.Fields("BREPRES").Value
        txtRodExt = Rst.Fields("RODEXT").Value
        txtRodRet = Rst.Fields("RODRET").Value
        txtBloPor = Rst.Fields("BLOPOR").Value
        txtNoExt = Rst.Fields("NOEXT").Value
        txtRelA = Rst.Fields("RELA").Value
        txtRelB = Rst.Fields("RELB").Value
        txtDate = Rst.Fields("DATE").Value
        Calendar1.Value = Rst.Fields("DATE").Value
        binChanged = False
    End If
End Sub

' change the binChanged value when a user click on one of  the field. it shows that something has been changed on the form
Private Sub txtBloPor_Change()
    binChanged = True
End Sub

Private Sub txtBrePres_Change()
    binChanged = True
End Sub

Private Sub txtNoExt_Change()
    binChanged = True
End Sub

Private Sub txtOilTemp_Change()
    binChanged = True
End Sub

Private Sub txtRelA_Change()
    binChanged = True
End Sub

Private Sub txtRelB_Change()
    binChanged = True
End Sub

Private Sub txtRepTech_Change()
    binChanged = True
    txtRepTech.Value = UCase(txtRepTech)
End Sub

Private Sub txtRodExt_Change()
    binChanged = True
End Sub

Private Sub txtRodReT_Change()
    binChanged = True
End Sub

Private Sub txtTesTech_Change()
    binChanged = True
End Sub

Private Sub txtDate_Change()
    binChanged = True
End Sub

Private Sub Calendar1_click()
    txtDate.Text = Calendar1.Value
End Sub

' this sub reformat the date field to be proper
Private Sub txtDate_AfterUpdate()
    Dim slash As String
    Dim num, num1 As String
    slash = "//"
    'num hold the data from the date field without the //
    num = (Mid(txtDate, 1, 2) & Mid(txtDate, 4, 2) & Mid(txtDate, 7, 4))
    num1 = (Mid(txtDate, 1, 2) & Mid(txtDate, 4, 2) & Mid(txtDate, 7, 2))
    'if 12/12/1212
    If Len(txtDate) = 10 And slash = (Mid(txtDate, 3, 1) & Mid(txtDate, 6, 1)) And IsNumeric(num) = True Then
            MsgBox ("Date good!!!!!")
    'if 12/12/12
    ElseIf Len(txtDate) = 8 And slash = (Mid(txtDate, 3, 1) & Mid(txtDate, 6, 1)) And IsNumeric(num1) = True Then
            txtDate.Text = (Left(txtDate, 2) & "/" & Mid(txtDate, 4, 2) & "/20" & Mid(txtDate, 7, 2))
            MsgBox ("Date good!!!!???!")
    ElseIf IsNumeric(txtDate) Then
            If Len(txtDate) = 6 Then
                txtDate.Text = (Left(txtDate, 2) & "/" & Mid(txtDate, 3, 2) & "/20" & Mid(txtDate, 5, 2))
            ElseIf Len(txtDate) = 8 Then
                txtDate.Text = (Left(txtDate, 2) & "/" & Mid(txtDate, 3, 2) & "/" & Mid(txtDate, 5, 4))
            Else
                MsgBox ("Wrong Date Format")
            End If
    Else
            MsgBox ("wrong Date format")
    End If
End Sub

Private Sub CmdSave_Click()
    Dim dummieString As String
    dummieString = "ok"
    SavingData (dummieString)
End Sub

'The x button on top of the form is clicked to close the form
Private Sub UserForm_QueryClose(cancel As Integer, CloseMode As Integer)
Dim AnswerFromBox As String
AnswerFromBox = ""
'if x is pressed(0) and the form has been modified then call the yesnomessage
    If CloseMode = 0 And binChanged = True Then
        ' changed has been made do we want to save the change?
        AnswerFromBox = YesNoMessageBox()
        If AnswerFromBox = "YesAndSaved" Then
             cancel = 0
        ElseIf AnswerFromBox = "NotSaved" Then
            cancel = 1
        ElseIf AnswerFromBox = "Cancel" Then
            cancel = 1
        ElseIf AnswerFromBox = "NoNeedSave" Then
            cancel = 0
        End If
    Else
        ' nothing changed in the form we can close safely
        cancel = 0
    End If
End Sub


Sub SavingData(ByRef IsSavedGood As String)
' The button Saved is clicked so we save the record
    If ValidateForm Then
        strsql = " Select count(*) from PSHTable1 where WORKORDNUM ='" & lblWorOrd & "'"
        Set Rst = cn.Execute(strsql)
        ' If records exists, we do an update
        If Rst.Fields(0).Value = 1 Then
            Call UpdateTable
        Else
        ' Else we do an insert
            Call InsertTable
        End If
        binChanged = False
        IsSavedGood = "GOOD"
    Else
    IsSavedGood = "BAD"
    End If
    
End Sub


 Function YesNoMessageBox() As String
    Dim Answer As String
    Dim IsSavedGood As String
    IsSavedGood = "123"
    Answer = MsgBox("Do you Want To save your changes?", vbQuestion + vbYesNoCancel, "Save?")
    If Answer = vbYes Then
        Call SavingData(IsSavedGood)
        If IsSavedGood = "GOOD" Then
            YesNoMessageBox = "YesAndSaved"
        Else
            YesNoMessageBox = "NotSaved"
        End If
    ElseIf Answer = vbCancel Then
        YesNoMessageBox = "Cancel"
    ElseIf Answer = vbNo Then
        YesNoMessageBox = "NoNeedSave"
    End If

End Function

' this function validate the form to make sure everything is ok in the data
Private Function ValidateForm() As Boolean
    Dim Errors As String
    Dim NumericCheck As Boolean
    Dim EmptyCheck As Boolean
    'if the field contains something different than a number then
    If IsNumeric(txtOilTemp) = False And Trim(txtOilTemp) <> "" Then
        NumericCheck = True
        Errors = Errors & "-Oil Temp is wrong" & vbCrLf
    End If
    If IsNumeric(txtBrePres) = False And Trim(txtBrePres) <> "" Then
        NumericCheck = True
        Errors = Errors & "-Breakway Pressure is wrong" & vbCrLf
    End If
    If IsNumeric(txtRodExt) = False And Trim(txtRodExt) <> "" Then
        NumericCheck = True
        Errors = Errors & "-Bypass Rod Extended is wrong" & vbCrLf
    End If
    If IsNumeric(txtRodRet) = False And Trim(txtRodRet) <> "" Then
        NumericCheck = True
        Errors = Errors & "-Bypass Rod Retracted is wrong" & vbCrLf
    End If
    If IsNumeric(txtBloPor) = False And Trim(txtBloPor) <> "" Then
        NumericCheck = True
        Errors = Errors & "-Bypass Blocked is wrong" & vbCrLf
    End If
    If IsNumeric(txtNoExt) = False And Trim(txtNoExt) <> "" Then
        NumericCheck = True
        Errors = Errors & "-No Extarnal Leaks is wrong" & vbCrLf
    End If
    If IsNumeric(txtRelA) = False And Trim(txtRelA) <> "" Then
        NumericCheck = True
        Errors = Errors & "-Relief A is wrong" & vbCrLf
    End If
    If IsNumeric(txtRelB) = False And Trim(txtRelB) <> "" Then
        NumericCheck = True
        Errors = Errors & "-Relief B is wrong" & vbCrLf
    End If
    If Trim(txtDate) = "" Then
        NumericCheck = True
        Errors = Errors & "-Date Field is empty" & vbCrLf
    End If
    If Trim(Errors) <> "" Then
        MsgBox (Errors)
        ValidateForm = False
    Else
        ValidateForm = True
    End If
End Function

' this sub send the data in the table the record exists so we do an update
Sub UpdateTable()
        If binChanged = True Then
            cmd.CommandText = "UPDATE PSHTable1 set REPTECH='" & txtRepTech & "',TESTTECH='" & txtTesTech & "', " & _
            " OILTEMP ='" & txtOilTemp & "',BREPRES='" & txtBrePres & "', RODEXT = '" & txtRodExt & "', RODRET = '" & txtRodRet & "', " & _
            " BLOPOR = '" & txtBloPor & "', NOEXT = '" & txtNoExt & "', RELA = '" & txtRelA & "', RELB = '" & txtRelB & "' ," & _
            "DATE = '" & txtDate & "' WHERE WORKORDNUM ='" & lblWorOrd & "'"
            cmd.Execute
            binChanged = False
        Else
        End If
End Sub

' this sub send the data in the table the record doesn't exists so we create it
Sub InsertTable()
        If binChanged = True Then
            cmd.CommandText = "insert INTO PSHTable1 (WORKORDNUM,REPTECH,TESTTECH,OILTEMP,BREPRES,RODEXT,RODRET,BLOPOR,NOEXT,RELA,RELB,DATE)" & _
            "VALUES ('" & lblWorOrd & "' ,'" & txtRepTech & "' , '" & txtTesTech & "', '" & txtOilTemp & "', '" & txtBrePres & "', " & _
            " '" & txtRodExt & "', '" & txtRodRet & "', '" & txtBloPor & "', '" & txtNoExt & "', '" & txtRelA & "', '" & txtRelB & "', " & _
            "'" & txtDate & "') "
            cmd.Execute
            binChanged = False
        Else
        End If
End Sub

'this give the focus back to the type field in my WorkOrderEntryUpdate form
Private Sub UserForm_Terminate()
    WorkOrderEntryUpdate.Type.Focus
End Sub

Open in new window

Form1.JPG
SQLData.JPG
SQLTableProperty.JPG
0
Comment
Question by:taverny
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 2000 total points
ID: 34168953
You have declared the field as NCHAR(10)
Which means that ALL data is padded with spaces (on the right).
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34168966
If you change it to NVarchar(10), that will handle new data, but for old, you still need to run a query to remove the trailing spaces:

update tbl set col = rtrim(col)
0
 

Author Comment

by:taverny
ID: 34174684
Hi Cyberkiwi,
thank you for your prompt response. I am gonna try that now.I don't have any data in the table yet, so I am still in a test environment
just for reference, what should I declared the field for my form in my database. I will have a bunch of fields that will have numbers like ( xxxx.xx), a text fields for comments and a field with 3 letter characters, also a date field with no time , just the date; and a field with plain numbers like (xxxx)

Thanks
David
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:taverny
ID: 34201279
Hi Cyberkiwi,
Changing to NVarchar worked, but can you please advice what I should I use for my other fields posted on my previous post?
Thanks
David
0
 
LVL 58

Assisted Solution

by:cyberkiwi
cyberkiwi earned 2000 total points
ID: 34201476
Hi David,

numbers like ( xxxx.xx)  => decimal(10,2) -- up to 10 digits total, 8 before, 2 after decimal
a text fields for comments => varchar(max)
and a field with 3 letter characters => char(3) if always 3, varchar(3) if variable, or just stick to varchar(3)
also a date field with no time,just the date => datetime (or if sql server 2008/r2, go for "date")
and a field with plain numbers like (xxxx) => int, unless it can go above 2 billion, then bigint

Regards
0
 

Author Comment

by:taverny
ID: 34202591
thank you
0
 

Author Comment

by:taverny
ID: 34207481
sorry to bother you one more time, what about a check box, how should I save it in SQL?
Thanks
David
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34207681
A check box is probably best to store as a bit column.  It only allows 0 or 1.
0
 

Author Comment

by:taverny
ID: 34208982
thanks, I also actually tried the datetime and it does store the date fine but it add the time next to it, is there a seeting to only save the date no time .(xx/xx/xxxx)
Thanks
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34209892
Not prior to SQL Server 2008
    * also a date field with no time,just the date => datetime (or if sql server 2008/r2, go for "date")
It doesn't really matter, since you only get to see the time if you are in SSMS.
In any other tool, you can control the display and formatting - just use a format that does not include time.
0
 

Author Comment

by:taverny
ID: 34640963
Cyberkiwi,
I am sorry to reopen this question. I am still working on tweeking my table to allow the correct data in it. I have modified the table with the correct fields, but it seems that if all my fields are not filled then I have a SQL error . I am doing an insert or an update depending if the data already exists in the table. but most of my fields in my form doesn't have data so when I push it , i guess it push nothing into the table . I think the NULL is not allowed in the table for some of the field. what would be the best way on fixing that? If you feel I should open another question let me know and I will post right away.
Thanks
David
Sub InsertTable()
        If binChanged = True Then
            cmd.CommandText = "insert INTO PSHSVCSHO1 (WORKORDNUM, DATE, TECH, TESTTECH, ROTATION, THEORY, PREMAX, PREMIN, " & _
            "OIL, RPM, IN3, RPMMAX, RPMMIN, OILTEMP, T1G500, T1G1000, T1G1500, T1G2000, T1G2500, T1G3000, T1G3500, " & _
            "T1C500, T1C1000, T1C1500, T1C2000, T1C2500, T1C3000, T1C3500, T2G500, T2G1000, T2G1500, T2G2000, T2G2500, " & _
            "T2G3000, T2G3500, T2C500, T2C1000, T2C1500, T2C2000, T2C2500, T2C3000, T2C3500, T3G500, T3G1000, T3G1500, " & _
            "T3G2000, T3G2500, T3G3000, T3G3500, T3C500, T3C1000, T3C1500, T3C2000, T3C2500, T3C3000, T3C3500, CASE1, " & _
            "COMPENSATOR, RELIEF, PILOT, FLOW, PRESSURE, PSI, COMMENT, USERID )" & _
            "VALUES ('" & lblWorOrd & "' ,'" & txtDate & "',  '" & txtTech & "', '" & txtTTech & "',  '" & ComRotation & "',  '" & txtTheo & "', " & _
            " '" & txtPreMax & "',  '" & txtPreMin & "', '" & txtOil & "',  '" & txtRPM & "',  '" & txtIn3 & "',  '" & txtRPMMax & "', " & _
            " '" & txtRPMMin & "',  '" & txtOilTemp & "',  '" & txtT1G500 & "',  '" & txtT1G1000 & "',  '" & txtT1G1500 & "'," & _
            " '" & txtT1G2000 & "',  '" & txtT1G2500 & "',  '" & txtT1G3000 & "',  '" & txtT1G3500 & "',  '" & txtT1C500 & "', " & _
            " '" & txtT1C1000 & "',  '" & txtT1C1500 & "',  '" & txtT1C2000 & "',  '" & txtT1C2500 & "',  '" & txtT1C3000 & "', " & _
            " '" & txtT1C3500 & "',  '" & txtT2G500 & "',  '" & txtT2G1000 & "',  '" & txtT2G1500 & "',  '" & txtT2G2000 & "', " & _
            " '" & txtT2G2500 & "',  '" & txtT2G3000 & "',  '" & txtT2G3500 & "',  '" & txtT2C500 & "',  '" & txtT2C1000 & "', " & _
            " '" & txtT2C1500 & "',  '" & txtT2C2000 & "',  '" & txtT2C2500 & "',  '" & txtT2C3000 & "',  '" & txtT2C3500 & "',  " & _
            " '" & txtT3G500 & "',  '" & txtT3G1000 & "',  '" & txtT3G1500 & "', '" & txtT3G2000 & "',  '" & txtT3G2500 & "',  " & _
            " '" & txtT3G3000 & "',  '" & txtT3G3500 & "',   '" & txtT3C500 & "',  '" & txtT3C1000 & "',  '" & txtT3C1500 & "', " & _
            " '" & txtT3C2000 & "', '" & txtT3C2500 & "',  '" & txtT3C3000 & "',  '" & txtT3C3500 & "',  '" & txtCase & "',  " & _
            " '" & txtComp & "',  '" & txtRelie & "',  '" & txtPilot & "',  '" & txtChFlow & "',  " & _
            " '" & txtChPres & "',  '" & txtCaseP & "',  '" & txtComm & "', '" & LblSetupTech & "'   ) "

            cmd.Execute

Open in new window

table-fields-name.JPG
table-fields-2.JPG
0
 

Author Comment

by:taverny
ID: 34640972
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34642197
It is normally easier to ask a new question since you have unlimited points and there are plenty of experts who will look at a new question but hardly notice an old question (they did not answer) with 10 comments on it already, let alone closed.  The saying goes, experts (myself included) sleep but EE does not sleep, so you get more eyes on the issue quicker.

To the question, "cannot convert varchar to numeric" is caused by not properly determining if a column has not been given a value as you have suspected.  In a simple case

" '" & txtT3C2000 & "', '" & txtT3C2500 .....

if txtT3C2000 is a number and it was provided, you get

'123', '355' ..

which even though it is in quotes is perfectly valid (the number is extracted).  However, if you leave it blank, it becomes

'', '355' ...

for which the '' is indecipherable as a number.  One tedious option is to test for all the numeric input (that allows blanks) and perform something like

 " " & txtCompValue & ",  '"

Where you notice I stripped the single quotes

txtCompvalue itself is worked out before the

cmd.CommandText = "...

statement.  It would be something like

If txtComp = "" Then txtComp = "NULL"

leaving it as the numeric content otherwise.
0

Featured Post

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Desired Skill Set for Microsoft Dynamics CRM Technical Resources – Part III
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question