Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
Hello,
 
I am trying to fix this bug. Please help.
 
I am getting below error and no data inserted :
"Must declare the scalar variable "@bytImage"."
 
    Public Sub InsertFile()
        Dim sMainFolder As String = "D:\Project"
        Dim conAccess As Data.OleDb.OleDbConnection
        Dim commSQL As Data.OleDb.OleDbCommand = New Data.OleDb.OleDbCommand()
        Dim bytImage As Byte()
        Dim QueryParameter As New Data.OleDb.OleDbParameter
        QueryParameter.DbType = Data.DbType.Binary
 
        Try
            If Me.fulFileUpload.HasFile = True Then
                bytImage = Me.fulFileUpload.FileBytes
                QueryParameter.Value = bytImage
 
                conAccess = New Data.OleDb.OleDbConnection
                conAccess.ConnectionString = "File Name=" & sMainFolder & "\" & "Project.udl"
                conAccess.Open()
                commSQL.Connection = conAccess
                commSQL.CommandText = "INSERT INTO [PICS] ([IMAGE]) VALUES (@bytImage))"
 
                ' ''QueryParameter = commSQL.Parameters.Add("@bytImage", Data.OleDb.OleDbType.Binary)
                ' ''QueryParameter.Direction = Data.ParameterDirection.Input
                ' ''commSQL.Parameters.Add(QueryParameter)
                commSQL.Parameters.AddWithValue("@bytImage", bytImage)
 
                commSQL.ExecuteNonQuery()
                conAccess.Close()
                commSQL.Dispose()
            End If
        Catch ex As Exception
            'ex.Message = "Must declare the scalar variable "@bytImage"."
        End Try
    End Sub
 
Thank you in advance,
~ Goutam
Posted 3-Oct-12 20:04pm
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

When using OleDb the parameters in the SQL are to be set to ? and not @bytImage.
try this
commSQL.CommandText = "INSERT INTO [PICS] ([IMAGE]) VALUES (?)"
  Permalink  
v2
Comments
ggoutam7 at 4-Oct-12 1:40am
   
Thank you.
I removed that extra bracket and still have the error.
Santhosh Kumar J at 4-Oct-12 1:44am
   
updated the solution.Check now
ggoutam7 at 4-Oct-12 2:17am
   
Hello,
 
Thank you. It can work now.
Modified code as below :
 
<pre lang="vb"> Public Sub InsertFile()
Dim sMainFolder As String = "D:\Project"
Dim conAccess As Data.OleDb.OleDbConnection
Dim commSQL As Data.OleDb.OleDbCommand = New Data.OleDb.OleDbCommand()
Dim bytImage As Byte()
Dim QueryParameter As New Data.OleDb.OleDbParameter
QueryParameter.DbType = Data.DbType.Binary
 
Try
If Me.fulFileUpload.HasFile = True Then
bytImage = Me.fulFileUpload.FileBytes
QueryParameter.Value = bytImage
 
conAccess = New Data.OleDb.OleDbConnection
conAccess.ConnectionString = "File Name=" & sMainFolder & "\" & "Project.udl"
conAccess.Open()
commSQL.Connection = conAccess
commSQL.CommandText = "INSERT INTO [PICS] ([IMAGE]) VALUES (?)"
 
QueryParameter = commSQL.Parameters.Add("@bytImage", Data.OleDb.OleDbType.Binary)
 
commSQL.ExecuteNonQuery()
conAccess.Close()
commSQL.Dispose()
End If
Catch ex As Exception
 
End Try
End Sub
</pre>
 
Thank you,
~ Goutam
Santhosh Kumar J at 4-Oct-12 2:18am
   
is that working?
ggoutam7 at 4-Oct-12 2:59am
   
Yes. Thank you.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Hi ,
 
I am facing same issue while executing stored procedure,please help me to solve this,
 
'MUST DECLARE SCALAR VARIABLE @FILENAME
 
USE [Pro4SBCR]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
 
Alter PROCEDURE [dbo].[usp_ICLExtract_GetFile]
 
@FileName Varchar(50),
@Image_Path Varchar(50) Output,
@FIleNameList varchar(4096) OUTPUT
AS
BEGIN

SET NOCOUNT ON
DECLARE @strProcName varchar(255)
SET @strProcName = 'usp_ICLExtract_GetFile'
DECLARE @strSQL1 varchar(1024), @strSQL2 varchar(1024)
DECLARE @strFileName Varchar(50)
DECLARE @intErrorReturn int
SET @intErrorReturn = 0
SET @FileNameList = ''
SET @Image_Path = ""
--DECLARE @FileName Varchar(50)

SET @strSQL1 = N'SELECT tbl_ICLExtCashLetter.Image_Path from tbl_ICLExtCashLetter INNER JOIN tbl_ICLExtFile on [tbl_ICLExtCashLetter].ICLExtFileUID = [tbl_ICLExtFile].ICLExtFileUID where tbl_ICLExtFile.FileName = @FileName' 'THIS LINE CAUSING ERROR
/*==============================================================================
* Run the query'
*==============================================================================*/
SET @strSQL2 = 'DECLARE curCategory INSENSITIVE SCROLL CURSOR FOR ' + @strSQL1
EXEC(@strSQL2)
SELECT @intErrorReturn = @@ERROR
IF (@intErrorReturn <> 0) GOTO usp_ICLExtract_GetFile_Error
OPEN curCategory
SELECT @intErrorReturn = @@ERROR
IF (@intErrorReturn <> 0) GOTO usp_ICLExtract_GetFile_Error
FETCH NEXT FROM curCategory INTO @strFileName
WHILE (@@FETCH_STATUS <> -1)
BEGIN
SET @FileNameList = @FileNameList + @strFileName + ';'
SET @Image_Path = @Image_Path + 1
FETCH NEXT FROM curCategory INTO @strFileName
END
CLOSE curCategory
DEALLOCATE curCategory
RETURN(0)
usp_ICLExtract_GetFile_Error:
RETURN(@intErrorReturn)
END
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 406
1 /\jmot 180
2 Suraj Sahoo | Coding Passion 170
3 Afzaal Ahmad Zeeshan 159
4 BillWoodruff 154
0 OriginalGriff 8,344
1 Sergey Alexandrovich Kryukov 7,407
2 DamithSL 5,634
3 Maciej Los 5,024
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web02 | 2.8.1411023.1 | Last Updated 30 Sep 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100