Update Binary Field in sql server 2000

[jbisono]

Hi, Im new here, im sorry if i am posting in a wrong forum, i have a piece of code that update a binary field in sql server 2000, im keeping this code a simple as possible, so this is working but only save the first character in the string in this case the letter H i want to save the hole string what i am missing thanks.

im rsVen
dim dbCon
dim sCon
dim sSql
dim nVNBR
Dim BytArray(19)
Dim ByteIndex
set dbCon = CreateObject("ADODB.Connection")
set rsVEN = CreateObject("ADODB.Recordset")
sCon = "driver={SQL Server};server=;uid=;pwd=;database=;"
dbCon.ConnectionString = sCon
dbCon.Open()
'Bits is the binary field
sSql = "SELECT BITS, BITS_LENGTH FROM PURC_ORDER_BINARY WHERE PURC_ORDER_ID = '290585'"
rsVEN.Open sSql, dbCon, 1, 3, 1
'this is the piece of string i want to save
nVNBR = "hi how are yodsf sdf"

For ByteIndex = 1 To 18
rsVen("BITS").AppendChunk MID(nVNBR,ByteIndex,1)
Next

rsVen.Fields("BITS_LENGTH").Value = 19

rsVen.Update
dbCon.Close()

[QVeen72]

Hi,

Why do you want to use "AppendChunk" for simple string..?

Try This :

nVNBR = "hi how are yodsf sdf"
rsVen("BITS") =nVNBR
rsVen.Fields("BITS_LENGTH") = 19
' Or
' rsVen.Fields("BITS_LENGTH") =Len(nVNBR)
rsVen.Update

Regards
Veena

[jbisono]

Thanks Veena for your response.

If i do the same you are saying it give me back this error "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.' At Line 24 Position 0" in Line 24 i have this piece of code rsVen("BITS") =nVNBR.

in the other hand the BITS field in the database is type binary, blob that is why i was using AppendChunk.

Thanks again.

[QVeen72]

Hi,

Well, If your field is Binary, then you need to use AppendChunk..
Just One more correction, After opening the Recordset, you need to write this line :

rsVEN.Open sSql, dbCon, 1, 3
rsVen.AddNew

(Rest of the Code Remains same..)

Regards
Veena

[jbisono]

Hi,

Well, If your field is Binary, then you need to use AppendChunk..
Just One more correction, After opening the Recordset, you need to write this line :

rsVEN.Open sSql, dbCon, 1, 3
rsVen.AddNew

(Rest of the Code Remains same..)

Regards
Veena

Thanks Veena I really appreciate your help i did what you said, but still the statement is saving the first character of the string. Check the code how looks like now.

dim rsVen
dim dbCon
dim sCon
dim sSql
dim nVNBR
Dim BytArray(19)
Dim Temp
Dim ByteIndex 
set dbCon = CreateObject("ADODB.Connection")
set rsVEN = CreateObject("ADODB.Recordset")
sCon = "driver={SQL Server};server=;uid=;pwd=;database=;"
dbCon.ConnectionString = sCon
dbCon.Open()
'Bits is the binary field
sSql = "SELECT PURC_ORDER_ID, TYPE, BITS, BITS_LENGTH FROM PURC_ORDER_BINARY"
rsVEN.Open sSql, dbCon, 1, 3
rsVen.AddNew
'this is the piece of string i want to save
nVNBR = "bi how are yodsf sd"

For ByteIndex = 1 To 18
rsVen("BITS").AppendChunk(MID(nVNBR,ByteIndex,1))
Next

rsVen("PURC_ORDER_ID").Value = "290590"
rsVen.Fields("TYPE").Value = "D"
rsVen.Fields("BITS_LENGTH").Value = 19

rsVen.Update
dbCon.Close()

[QVeen72]

Hi,

Well, the Code Looks Perfect,
I tried the same Code, For a "Memo" field in Access Database, and works Correctly.
Not sure, why it dosent work there.. Any ways, Try Declaring the variables like this :

Dim nVNBR As String
Dim ByteIndex As Integer

Regards
Veena

[jbisono]

Thanks again Veena for your time.

Now when I Declare the variables like this
Dim nVNBR As String, it give me back an error saying this
'Expected end of statement', I research what is the solution for this in microsoft website and just says this "To correct this error Remove the extra text. "

any other suggestion?

[jbisono]

Hey Veena I finally get this to work this is the code.

dim rsVen
dim dbCon
dim sCon
dim sSql
dim nVNBR
Dim Temp

set dbCon = CreateObject("ADODB.Connection")
set rsVen = CreateObject("ADODB.Recordset")
sCon = "driver={SQL Server};server=;uid=;pwd=;database=;"
dbCon.ConnectionString = sCon
dbCon.Open()
'Bits is the binary field
sSql = "SELECT PURC_ORDER_ID, TYPE, BITS, BITS_LENGTH FROM PURC_ORDER_BINARY WHERE PURC_ORDER_ID = '290590'"

rsVen.Open sSql, dbCon, 1, 3
rsVen.AddNew

'this is the piece of string i want to save
nVNBR = "Hi how are yodsf sd"

Set Temp = rsVen("BITS")

rsVen.Fields("BITS_LENGTH").Value = 25
Temp.AppendChunk (StringToMB(nVNBR) & ChrB(0)) 
rsVen("PURC_ORDER_ID").Value = "290590"
rsVen.Fields("TYPE").Value = "D"

rsVen.Update
dbCon.Close()

Function StringToMB(S)
  Dim I, B
  For I = 1 To Len(S)
    B = B & ChrB(Asc(Mid(S, I, 1)))
  Next
  StringToMB = B
End Function

Thanks for all your help.