Solved

Data in my database always have blank Characteres

Posted on 2010-11-18
13
496 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
  • 7
  • 6
13 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:taverny
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
thank you
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:taverny
Comment Utility
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
Comment Utility
A check box is probably best to store as a bit column.  It only allows 0 or 1.
0
 

Author Comment

by:taverny
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
0
 
LVL 58

Expert Comment

by:cyberkiwi
Comment Utility
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now