Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Padding a field in SQL

Status
Not open for further replies.

tavie

Technical User
Joined
Nov 23, 2001
Messages
79
Location
US
I am using VB 6 and SQL 7.0. The problem I am having is that one field requires 18 characters for a valid input. If I have a record that is only 9 characters long, I need to pad the record with leading zero's. The problem is that no matter what data type I use in SQL, the zero's are cut off. Do I need to add something to my code to make the insert work ???? (ex. 000000000123456789 shows up as 123456789 in the sql table) Thanks for any help you can offer
 
Have you tried the data type char or varchar?

i.e.

create table test (test_col varchar(18) null)

insert into test
values('000000000001234567')

select * from test


Reults:

test_col
------------------
000000000001234567

Rick.
 
Thanks...Your example works in sql query analyzer but during the ado insert, the zero's are still cut-off. Is there anything I need to specify or declare in my VB code.
 
Make sure you're VB code is treating the number as a String instead of a Long. If VB sees 00000001234 as a number it will automatically shave off the zeros.

If you need the number to perform calculations then there is a conversion function so you can swap between numbers and strings - it's name escapes me but it's in the docs.

I think ADO will implicitly convert the number to a varchar before inserting into SQL Server and this is why you're not getting any errors at the moment.
 
Try

Dim VBField as String * 18

VBfield = format(rst!SQLfield, "000000000000000000")

Should work I think

Wayne
 
Thank you....Your Responses have helped me solve part of my dilema. Now for the new one....I am using the following code (Partial) to determine how many characters are in the input text box. The code works properly the first time I use it.

I use If then statements to determine the actions based on how many characters the user enters.
What happens is that after the first time the code is used, the aupc text box is padded with a blank character "12345678911 " instead of "12345678911"

This throws off my if Then statments because intead of it responding to 11 characters, it is now responding to 12.

What could be causing this problem


Dim strTest As String
Dim intByte As Integer, intChar As Integer

strTest = aupc.Text
intChar = Len(strTest)


MsgBox "Character Length = " & CStr(intChar) & vbCrLf & " ""Byte Length = " & CStr(intByte), , "StringLength rvBasic"
Set rsquery = cnwpm.Execute("Select * from master where upc = '" & aupc.Text & "'")
 
I'm sure why this occurs, but you could try using RTrim() which is a function that wipes any trailing spaces viz.

'aupc.Text = RTrim(aupc.Text)'

Also if you reset the AUPC text box to clear each time it's used make sure your setting it to the empty string ("") instead of " " - this would cause the problem you're having.

Again...The vbCrLf stands for 'Carrige Return/Line Feed'. In Windows these two characters are used to signify the end of a line as opposed to other OS's that will only use one character. Regards,
JJayUK
 
JJayUK You are a life saver When I was clearing the text boxes I was inputing a space and not realizing it. Thanks for the great info. This site has saved me so much time...Thanks again everyone...Tavie
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top