943,952 Members | Top Members by Rank

Software Development > Visual Basic 4 / 5 / 6 > Update Binary Field in sql server 2000
Ad:
Permalink
May 6th, 2009
0

Update Binary Field in sql server 2000

Expand Post »
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()
Similar Threads
Reputation Points: 56
Solved Threads: 56
Posting Pro in Training
jbisono is offline Offline
433 posts
since May 2009
Permalink
May 7th, 2009
1

Re: Update Binary Field in sql server 2000

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
Reputation Points: 84
Solved Threads: 140
Posting Shark
QVeen72 is offline Offline
923 posts
since Nov 2006
Permalink
May 7th, 2009
0

Re: Update Binary Field in sql server 2000

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.
Reputation Points: 56
Solved Threads: 56
Posting Pro in Training
jbisono is offline Offline
433 posts
since May 2009
Permalink
May 8th, 2009
0

Re: Update Binary Field in sql server 2000

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
Reputation Points: 84
Solved Threads: 140
Posting Shark
QVeen72 is offline Offline
923 posts
since Nov 2006
Permalink
May 8th, 2009
0

Re: Update Binary Field in sql server 2000

Click to Expand / Collapse  Quote originally posted by 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

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()
Reputation Points: 56
Solved Threads: 56
Posting Pro in Training
jbisono is offline Offline
433 posts
since May 2009
Permalink
May 9th, 2009
0

Re: Update Binary Field in sql server 2000

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
Reputation Points: 84
Solved Threads: 140
Posting Shark
QVeen72 is offline Offline
923 posts
since Nov 2006
Permalink
May 11th, 2009
0

Re: Update Binary Field in sql server 2000

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?
Reputation Points: 56
Solved Threads: 56
Posting Pro in Training
jbisono is offline Offline
433 posts
since May 2009
Permalink
May 11th, 2009
0

Re: Update Binary Field in sql server 2000

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.
Reputation Points: 56
Solved Threads: 56
Posting Pro in Training
jbisono is offline Offline
433 posts
since May 2009

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in Visual Basic 4 / 5 / 6 Forum Timeline: MS Access table connection problem
Next Thread in Visual Basic 4 / 5 / 6 Forum Timeline: Trouble calling VB function from C DLL





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC