|
Subject:
|
Extra Spaces Added When Inserting
|
|
Posted By:
|
rsearing
|
Post Date:
|
1/7/2007 4:37:27 PM
|
This one has stumped me. I debug the following code (which is ran during Protected Sub CreateUserWizard1_CreatedUser) and I check the values of all my parameters...there are NO spaces. I put the DEBUG point right at the executenonquery statement. THEN, I quit debugging before the redirect and go to the DB and there are a bunch of spaces added to several columns.
Why?
(NOTE...it seems to always be roleStr & CouncilPosStr that it does it with--I even tried making the insertString without the parameters--coding the variables into the string..same thing)
(Also--here is the string copied DIRECTLY from Debug:
"INSERT INTO [members] (fname, lname, email, home_phone, cell_phone, address, city, state, zip, userName, role, password, councilPosition) VALUES ('Rob', 'Searing', 'email@some.net', 'none', 'none', '1234 Some St', 'City', 'KS', '66213', 'rjsearing', 'No Role', 'password', 'none')"
)
Dim insertString = "INSERT INTO [members] (fname, lname, email, home_phone, cell_phone, address, city, state, zip, userName, role, password, councilPosition) VALUES (@fname, @lname, @email, @home, @cell, @address, @city, @state, @zip, @userNa, @rol, @pass, @councilPosStr)"
Dim knightsDBConn As New SqlConnection(conString)
Dim sqlCmd As New SqlCommand(insertString, knightsDBConn)
sqlCmd.CommandType = CommandType.Text
sqlCmd.Parameters.AddWithValue("@fname", firstNameStr)
sqlCmd.Parameters.AddWithValue("@lname", lastNameStr)
sqlCmd.Parameters.AddWithValue("@email", emailStr)
sqlCmd.Parameters.AddWithValue("@home", phoneStr)
sqlCmd.Parameters.AddWithValue("@cell", cellStr)
sqlCmd.Parameters.AddWithValue("@address", addressStr)
sqlCmd.Parameters.AddWithValue("@city", cityStr)
sqlCmd.Parameters.AddWithValue("@state", stateStr)
sqlCmd.Parameters.AddWithValue("@zip", zipStr)
sqlCmd.Parameters.AddWithValue("@userNa", userNameStr)
sqlCmd.Parameters.AddWithValue("@rol", "roleStr")
sqlCmd.Parameters.AddWithValue("@pass", passwd)
sqlCmd.Parameters.AddWithValue("@councilPosStr", councilPosStr)
knightsDBConn.Open()
sqlCmd.ExecuteNonQuery()
sqlCmd = Nothing
Response.Redirect("~/admin/addmember.aspx")
|
|
Reply By:
|
rsearing
|
Reply Date:
|
1/7/2007 8:10:27 PM
|
Ok..I figured it out..but not sure what I did...or rather, what the problem was.
I assumed it had to be something in the DB--so I looked at what I had typed the fields. Well, I changed them..and not sure what some were..but I changed some that were varchar(25) to nvarchar(25) and nchar to nvarchar...could this have been the problem?
If so...can someone please point me to an article that discusses the different data types?
Thanks,
Rob
|
|
Reply By:
|
peter0480
|
Reply Date:
|
1/8/2007 8:43:52 AM
|
The change from char to varchar (or as the case may be, nchar to nvarchar) will have made the difference. char and nchar datatypes automatically fill up to the indicated length with spaces, so if you put 'hello' in a char(10) field, it'll come out as 'hello '. varchar (and nvarchar) on the other hand do not pad strings that are shorter than the field's max length.
The difference between having the n or not is whether or not the value is stored as Unicode (requiring double space but supporting far more non-standard characters), and won't affect the above. Basically, you'll choose between nvarchar or varchar depending on whether you need Unicode support, and between varchar and char depending on whether you want your strings to be padded out with spaces. I understand there's a slight performance tradeoff (chars being slightly faster) but I've never seen that make a difference in practice, so I've always stuck to varchar myself. Alternatively if you do use char you'll probably have to trim any values before using them.
|
|
Reply By:
|
rsearing
|
Reply Date:
|
1/8/2007 9:56:23 AM
|
Thank you very much--that helps alot!
|
|
