ADO stream concert to binary












1















This is the code that I use to upload file into database using VBA, but this loop can only run once. During the second loop it will give error, the error is




Operation is not allowed in this context




Can anybody suggest me what to do?
The error is in the line adoStream.Type = adTypeBinary



Dim stCon As String 'SQL Connection string
Dim stProcName As String 'Stored Procedure name

Dim strCmd As String

Dim adoStream As Object
Dim adocmd As Object
Dim strFilePath As String
Dim adoCon As Object

Set adoCon = CreateObject("ADODB.Connection")
Set adoStream = CreateObject("ADODB.Stream")
Set adocmd = CreateObject("ADODB.Command")

adoCon.CursorLocation = adUseClient
adoCon.Open "Provider=SQLOLEDB; " & _
"Data Source=#########; " & _
"Initial Catalog=#######;" & _
"User ID=#######; Password=########;"

For Each fl In fld.Files
If fl.name Like Mask Then
strInput = fl.name
strFilePath = fld.path & "" & fl.name

MsgBox (strFilePath)

adoStream.Type = adTypeBinary
adoStream.Open
adoStream.LoadFromFile strFilePath 'It fails if file is open

With adocmd
.CommandText = "INSERT INTO dbo.coc_upload(Data) " & _
"VALUES (?)"
.CommandType = adCmdText
'---adding parameters
.Parameters.Append .CreateParameter("@Data", adVarBinary, adParamInput, adoStream.Size, adoStream.Read)
'---
End With

adocmd.ActiveConnection = adoCon
adocmd.Execute
adoCon.Close

MsgBox ("Done")
End If
Next









share|improve this question

























  • I suspect the error is actually happening on the next line. You haven't closed the adoStream.

    – Jon Vote
    Nov 22 '18 at 0:25











  • When I close the adoStream it gives error, Operation is not allowed when the object is close

    – syahidbudin
    Nov 22 '18 at 0:31











  • Hmmm - ok was hoping that would help...have you tried moving the open statement above the loop?

    – Jon Vote
    Nov 22 '18 at 0:36











  • I finally can get the code run, just have to modfiy this

    – syahidbudin
    Nov 22 '18 at 0:37











  • Have to declare this code inside the loop

    – syahidbudin
    Nov 22 '18 at 0:37
















1















This is the code that I use to upload file into database using VBA, but this loop can only run once. During the second loop it will give error, the error is




Operation is not allowed in this context




Can anybody suggest me what to do?
The error is in the line adoStream.Type = adTypeBinary



Dim stCon As String 'SQL Connection string
Dim stProcName As String 'Stored Procedure name

Dim strCmd As String

Dim adoStream As Object
Dim adocmd As Object
Dim strFilePath As String
Dim adoCon As Object

Set adoCon = CreateObject("ADODB.Connection")
Set adoStream = CreateObject("ADODB.Stream")
Set adocmd = CreateObject("ADODB.Command")

adoCon.CursorLocation = adUseClient
adoCon.Open "Provider=SQLOLEDB; " & _
"Data Source=#########; " & _
"Initial Catalog=#######;" & _
"User ID=#######; Password=########;"

For Each fl In fld.Files
If fl.name Like Mask Then
strInput = fl.name
strFilePath = fld.path & "" & fl.name

MsgBox (strFilePath)

adoStream.Type = adTypeBinary
adoStream.Open
adoStream.LoadFromFile strFilePath 'It fails if file is open

With adocmd
.CommandText = "INSERT INTO dbo.coc_upload(Data) " & _
"VALUES (?)"
.CommandType = adCmdText
'---adding parameters
.Parameters.Append .CreateParameter("@Data", adVarBinary, adParamInput, adoStream.Size, adoStream.Read)
'---
End With

adocmd.ActiveConnection = adoCon
adocmd.Execute
adoCon.Close

MsgBox ("Done")
End If
Next









share|improve this question

























  • I suspect the error is actually happening on the next line. You haven't closed the adoStream.

    – Jon Vote
    Nov 22 '18 at 0:25











  • When I close the adoStream it gives error, Operation is not allowed when the object is close

    – syahidbudin
    Nov 22 '18 at 0:31











  • Hmmm - ok was hoping that would help...have you tried moving the open statement above the loop?

    – Jon Vote
    Nov 22 '18 at 0:36











  • I finally can get the code run, just have to modfiy this

    – syahidbudin
    Nov 22 '18 at 0:37











  • Have to declare this code inside the loop

    – syahidbudin
    Nov 22 '18 at 0:37














1












1








1








This is the code that I use to upload file into database using VBA, but this loop can only run once. During the second loop it will give error, the error is




Operation is not allowed in this context




Can anybody suggest me what to do?
The error is in the line adoStream.Type = adTypeBinary



Dim stCon As String 'SQL Connection string
Dim stProcName As String 'Stored Procedure name

Dim strCmd As String

Dim adoStream As Object
Dim adocmd As Object
Dim strFilePath As String
Dim adoCon As Object

Set adoCon = CreateObject("ADODB.Connection")
Set adoStream = CreateObject("ADODB.Stream")
Set adocmd = CreateObject("ADODB.Command")

adoCon.CursorLocation = adUseClient
adoCon.Open "Provider=SQLOLEDB; " & _
"Data Source=#########; " & _
"Initial Catalog=#######;" & _
"User ID=#######; Password=########;"

For Each fl In fld.Files
If fl.name Like Mask Then
strInput = fl.name
strFilePath = fld.path & "" & fl.name

MsgBox (strFilePath)

adoStream.Type = adTypeBinary
adoStream.Open
adoStream.LoadFromFile strFilePath 'It fails if file is open

With adocmd
.CommandText = "INSERT INTO dbo.coc_upload(Data) " & _
"VALUES (?)"
.CommandType = adCmdText
'---adding parameters
.Parameters.Append .CreateParameter("@Data", adVarBinary, adParamInput, adoStream.Size, adoStream.Read)
'---
End With

adocmd.ActiveConnection = adoCon
adocmd.Execute
adoCon.Close

MsgBox ("Done")
End If
Next









share|improve this question
















This is the code that I use to upload file into database using VBA, but this loop can only run once. During the second loop it will give error, the error is




Operation is not allowed in this context




Can anybody suggest me what to do?
The error is in the line adoStream.Type = adTypeBinary



Dim stCon As String 'SQL Connection string
Dim stProcName As String 'Stored Procedure name

Dim strCmd As String

Dim adoStream As Object
Dim adocmd As Object
Dim strFilePath As String
Dim adoCon As Object

Set adoCon = CreateObject("ADODB.Connection")
Set adoStream = CreateObject("ADODB.Stream")
Set adocmd = CreateObject("ADODB.Command")

adoCon.CursorLocation = adUseClient
adoCon.Open "Provider=SQLOLEDB; " & _
"Data Source=#########; " & _
"Initial Catalog=#######;" & _
"User ID=#######; Password=########;"

For Each fl In fld.Files
If fl.name Like Mask Then
strInput = fl.name
strFilePath = fld.path & "" & fl.name

MsgBox (strFilePath)

adoStream.Type = adTypeBinary
adoStream.Open
adoStream.LoadFromFile strFilePath 'It fails if file is open

With adocmd
.CommandText = "INSERT INTO dbo.coc_upload(Data) " & _
"VALUES (?)"
.CommandType = adCmdText
'---adding parameters
.Parameters.Append .CreateParameter("@Data", adVarBinary, adParamInput, adoStream.Size, adoStream.Read)
'---
End With

adocmd.ActiveConnection = adoCon
adocmd.Execute
adoCon.Close

MsgBox ("Done")
End If
Next






excel vba excel-vba






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 22 '18 at 7:18









Pᴇʜ

20.6k42650




20.6k42650










asked Nov 22 '18 at 0:20









syahidbudinsyahidbudin

63




63













  • I suspect the error is actually happening on the next line. You haven't closed the adoStream.

    – Jon Vote
    Nov 22 '18 at 0:25











  • When I close the adoStream it gives error, Operation is not allowed when the object is close

    – syahidbudin
    Nov 22 '18 at 0:31











  • Hmmm - ok was hoping that would help...have you tried moving the open statement above the loop?

    – Jon Vote
    Nov 22 '18 at 0:36











  • I finally can get the code run, just have to modfiy this

    – syahidbudin
    Nov 22 '18 at 0:37











  • Have to declare this code inside the loop

    – syahidbudin
    Nov 22 '18 at 0:37



















  • I suspect the error is actually happening on the next line. You haven't closed the adoStream.

    – Jon Vote
    Nov 22 '18 at 0:25











  • When I close the adoStream it gives error, Operation is not allowed when the object is close

    – syahidbudin
    Nov 22 '18 at 0:31











  • Hmmm - ok was hoping that would help...have you tried moving the open statement above the loop?

    – Jon Vote
    Nov 22 '18 at 0:36











  • I finally can get the code run, just have to modfiy this

    – syahidbudin
    Nov 22 '18 at 0:37











  • Have to declare this code inside the loop

    – syahidbudin
    Nov 22 '18 at 0:37

















I suspect the error is actually happening on the next line. You haven't closed the adoStream.

– Jon Vote
Nov 22 '18 at 0:25





I suspect the error is actually happening on the next line. You haven't closed the adoStream.

– Jon Vote
Nov 22 '18 at 0:25













When I close the adoStream it gives error, Operation is not allowed when the object is close

– syahidbudin
Nov 22 '18 at 0:31





When I close the adoStream it gives error, Operation is not allowed when the object is close

– syahidbudin
Nov 22 '18 at 0:31













Hmmm - ok was hoping that would help...have you tried moving the open statement above the loop?

– Jon Vote
Nov 22 '18 at 0:36





Hmmm - ok was hoping that would help...have you tried moving the open statement above the loop?

– Jon Vote
Nov 22 '18 at 0:36













I finally can get the code run, just have to modfiy this

– syahidbudin
Nov 22 '18 at 0:37





I finally can get the code run, just have to modfiy this

– syahidbudin
Nov 22 '18 at 0:37













Have to declare this code inside the loop

– syahidbudin
Nov 22 '18 at 0:37





Have to declare this code inside the loop

– syahidbudin
Nov 22 '18 at 0:37












0






active

oldest

votes











Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53422246%2fado-stream-concert-to-binary%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























0






active

oldest

votes








0






active

oldest

votes









active

oldest

votes






active

oldest

votes
















draft saved

draft discarded




















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53422246%2fado-stream-concert-to-binary%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







Popular posts from this blog

404 Error Contact Form 7 ajax form submitting

How to know if a Active Directory user can login interactively

TypeError: fit_transform() missing 1 required positional argument: 'X'