1

I am relatively new to SQL Server and am working on a legacy app in Visual Basic 6. How can I use an SQL INSERT command to insert a string of characters that can include any ASCII character between 0 and 255 into a VARCHAR field?

JimmyPena
  • 8,694
  • 6
  • 43
  • 64
Glen Pearce
  • 21
  • 1
  • 5
  • 2
    Make the column VarBinary & pass in your bytes as a byte array paramater. Example: http://stackoverflow.com/questions/2307830/can-i-return-a-byte-array-from-a-sql-server-varbinary-column-using-a-parameteriz – Alex K. Jul 06 '12 at 14:36
  • +1 Alex K. A random sequence of bytes is not a string, and shouldn't be treated as a string. – MarkJ Jul 06 '12 at 20:43
  • Thanks for the byte array suggestion, but I also need to store text in that same field in other records in that table. I eventually solved the problem by changing the encryption routine to prevent Chr(0) and single quote chars (') from being used and I could then store the resulting encrypted string in a VARCHAR field. – Glen Pearce Jul 06 '12 at 20:47

3 Answers3

1

You could encode the string into a sequence of hexadecimal values representing the ASCII codes of the string's characters, put 0x in front of the sequence, then apply CAST(… AS varchar(n)) to the whole thing and use that expression in your DDL statement.

What I mean is, if your string was e.g. #X?!v, you would insert it like this

UPDATE table
SET column = CAST(0x23583F2176 AS varchar(n))
WHERE condition

So, if there was a NUL character somewhere, the resulting sequence would contain 00 at the corresponding position.

I don't really "speak" VB, but if I did, I would perhaps look into creating a function accepting a raw string and returning a string of hex codes, and so, when building the DDL command, my VB instruction would probably look something like this:

cmd.CommandText = "UPDATE table SET column = CAST(0x" & HexEncode(RawString) & " AS varchar(n)) WHERE condition"
Andriy M
  • 76,112
  • 17
  • 94
  • 154
0

Assuming your project has a reference to ADO, it's something like this:

Dim oConn As Connection
Dim oCmd As Command

Set oConn = New Connection

oConn.Open <your connection string goes here>

Set oCmd = New Command

oCmd.ActiveConnection = oConn
oCmd.CommandText = "INSERT INTO TableName (ColumnName) VALUES('string')"
oCmd.Execute
Cambridge Developer
  • 161
  • 1
  • 7
  • That's the command I am using, but, as soon as it encounters a chr(0) embedded in the string, it throws this error: "Unclosed quotation mark after the character string". Is there any way to get it to accept any char between 0 and 255 (inclusive)? – Glen Pearce Jul 06 '12 at 12:13
  • I've just done a little research. It appears that chr(0) is treated as the end of a command. Therefore it's terminating your string at some point. That explains the error message. I'm afraid i don't know of a way around this without treating the data as binary, and changing your column to VARBINARY. – Cambridge Developer Jul 06 '12 at 12:26
  • Thanks for your input. I'm trying to store previously encrypted data into the field. I'll play with converting the column to VARBINARY. I'm still not sure how to put that into an INSERT command. eg: is this right? CONVERT(VARBINARY(256),'^%$76546%$#%^#@$@!*&'^%&^') How do you delimit the string if it can hold any character? – Glen Pearce Jul 06 '12 at 14:03
0

I don't have the ADO parameter syntax at hand, but you should be able to insert general strings (including NULs) into varchar columns using ADO Parameters.

Mark Hurd
  • 10,665
  • 10
  • 68
  • 101