Solved

Parameter not supplied

Posted on 2004-09-08
24
352 Views
Last Modified: 2010-04-23
I am writing records to an SQL database using an array.  I keep getting the following error message.

Prepared statement '(eadmDate,estudentId,eteacherId,.....etc...

expects parameters @ereadingElaItemCorrectRespArray which was not supplied.


I am supplying it as an array variable in my code.  The only thing that I can think of is the array may not be fully populated.  For example, the array may only contain data from 1-14 leaving the other array items blank.  Would this create that error?  If so, how should I trap for blank values in the array???

Here is my code

Try
                'Write ELA Objective Info record
                subject = "Reading ELA"
                Dim objConnection As SqlConnection = New SqlConnection("server=(local);database=FBISD-TAKS;user id=sa;password=pa55word")
                Dim r4 As Int32
                objConnection.Open()
                For r4 = 1 To 51
                    Dim objCommand As SqlCommand = New SqlCommand
                    objCommand.Connection = objConnection
                    objCommand.CommandText = "Insert Into itemAnalysisDetail " & "(admDate, studentId, teacherId, objectiveNumber, itemCorrectResp, studentCorrectResp, subject) " & _
                                "VALUES (@eadmDate, @estudentId,@eteacherId,@ereadingElaobj,@ereadingElaItemCorrectRespArray,@ereadingElaStudentRespArray, @esubject)"
                    objCommand.Parameters.Add("@eadmDate", SqlDbType.DateTime, 8).Value = madmDate
                    objCommand.Parameters.Add("@estudentId", SqlDbType.Int, 4).Value = studentId
                    objCommand.Parameters.Add("@eteacherId", SqlDbType.Int, 4).Value = teacher1
                    objCommand.Parameters.Add("@esubject", SqlDbType.VarChar, 50).Value = subject
                    objCommand.Parameters.Add("@ereadingElaobj", SqlDbType.Int, 4).Value = readingElaobj(r4)
                    objCommand.Parameters.Add("@ereadingElaItemCorrectRespArray", SqlDbType.VarChar, 50).Value = readingElaItemCorrectRespArray(r4)
                    objCommand.Parameters.Add("@ereadingElaStudentRespArray", SqlDbType.VarChar, 50).Value = readingElaStudentRespArray(r4)
                    objCommand.ExecuteNonQuery().ToString()
                Next
                objConnection.Close()
            Catch ex As Exception
                MsgBox(ex.ToString())

            End Try


Thanks
Kenny
0
Comment
Question by:kwh3856
  • 13
  • 9
  • 2
24 Comments
 
LVL 7

Expert Comment

by:natloz
ID: 12011370
What is the TYPE of the array?
You may have to cast the value to a string since that is what your parameter is...

eg.

objCommand.Parameters.Add("@ereadingElaItemCorrectRespArray", SqlDbType.VarChar, 50).Value =
CStr(readingElaItemCorrectRespArray(r4))

0
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 12011551
Or you can use..
                    objCommand.Parameters.Add("@ereadingElaItemCorrectRespArray", SqlDbType.VarChar, 50).Value = readingElaItemCorrectRespArray(r4).ToString()

-Baan
0
 
LVL 18

Expert Comment

by:DotNetLover_Baan
ID: 12011567
Whay you are using .ToString() here...  objCommand.ExecuteNonQuery().ToString()
-Baan
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

by:kwh3856
ID: 12017049
DotNetLover

I was using it previously to help debug my code.

I will try your suggestion and let you know if it works.

Thanks
Kenny
0
 

Author Comment

by:kwh3856
ID: 12017205
DotNetLover

I tried adding the toString() at the end of the line and here is the error message that I get.

System.Null.Reference.Exception:Object reference not set to an instance of an object

Any ideas???

Thanks
Kenny
0
 

Author Comment

by:kwh3856
ID: 12017255
Natloz,
I tried your command and it did not work either.  I got the same error message

Any other ideas?????

Thanks
Kenny
0
 
LVL 7

Expert Comment

by:natloz
ID: 12017429
What are the DATA TYPEs of your ARRAYs? Can you show some examples of where the arrays are defined and what sort of data they contain?
0
 

Author Comment

by:kwh3856
ID: 12017988
natloz,

Here is the basic code for it all.

Thanks
Kenny


        Dim readingElaObjScores As String
        Dim readingElaRawScore As String
        Dim readingElaScaleScore As String
        Dim readingElaMetStandard As String
        Dim readingElaCommended As String
        Dim readingElaThcb As String
        Dim readingElaObjNumbers As String
        Dim readingElaItemCorrectResp As String
        Dim readingElaStudentResp As String

 objStreamReader = New StreamReader("C:\fbisd\text.dat")
        strLine = objStreamReader.ReadLine
        Do While Not strLine Is Nothing         





            readingElaObjScores = Mid(strLine, 261, 8)
            readingElaRawScore = Mid(strLine, 269, 2)
            readingElaScaleScore = Mid(strLine, 271, 4)
            readingElaMetStandard = Mid(strLine, 275, 1)
            readingElaCommended = Mid(strLine, 276, 1)
            readingElaThcb = Mid(strLine, 277, 1)
            readingElaObjNumbers = Mid(strLine, 287, 102)
            readingElaItemCorrectResp = Mid(strLine, 389, 51).... Example data abddbeecba
            readingElaStudentResp = Mid(strLine, 440, 51).... Example data ++CJ++D++

"The + indicates a correct response, the other letters indicate a code for the student
response"



'Get Reading ELA Objective Info

            Dim r As Int32
            Dim ridx As Integer = 1
            Dim readingElaobj(51) As String
            For r = 1 To 101 Step 2
                readingElaobj(ridx) = Mid(readingElaObjNumbers, r, 2)
                ridx += 1
            Next

            Dim r2 As Int32
            Dim ridx2 As Integer = 1
            Dim readingElaItemCorrectRespArray(51) As String
            For r2 = 1 To 50 Step 2
                readingElaItemCorrectRespArray(ridx2) = Mid(readingElaItemCorrectResp, r2, 1)
                ridx2 += 1
            Next

            Dim r3 As Int32
            Dim ridx3 As Integer = 1
            Dim readingElaStudentRespArray(51) As String
            For r3 = 1 To 50 Step 2
                readingElaStudentRespArray(ridx3) = Mid(readingElaStudentResp, r3, 2)
                ridx3 += 1
            Next

            Try
                'Write ELA Objective Info record
                subject = "Reading ELA"
                Dim objConnection As SqlConnection = New SqlConnection("server=(local);database=FBISD-TAKS;user id=sa;password=pa55word")
                Dim r4 As Int32
                objConnection.Open()
                For r4 = 1 To 51
                    Dim objCommand As SqlCommand = New SqlCommand
                    objCommand.Connection = objConnection
                    objCommand.CommandText = "Insert Into itemAnalysisDetail " & "(admDate, studentId, teacherId, objectiveNumber, itemCorrectResp, studentCorrectResp, subject) " & _
                                "VALUES (@eadmDate, @estudentId,@eteacherId,@ereadingElaobj,@ereadingElaItemCorrectRespArray,@ereadingElaStudentRespArray, @esubject)"
                    objCommand.Parameters.Add("@eadmDate", SqlDbType.DateTime, 8).Value = madmDate
                    objCommand.Parameters.Add("@estudentId", SqlDbType.Int, 4).Value = studentId
                    objCommand.Parameters.Add("@eteacherId", SqlDbType.Int, 4).Value = teacher1
                    objCommand.Parameters.Add("@esubject", SqlDbType.VarChar, 50).Value = subject
                    objCommand.Parameters.Add("@ereadingElaobj", SqlDbType.Int, 4).Value = readingElaobj(r4)
                    Dim test As String
                    test = readingElaItemCorrectRespArray(r4)
                    objCommand.Parameters.Add("@ereadingElaItemCorrectRespArray", SqlDbType.VarChar, 50).Value = test
                    objCommand.Parameters.Add("@ereadingElaStudentRespArray", SqlDbType.VarChar, 50).Value = readingElaStudentRespArray(r4)
                    objCommand.ExecuteNonQuery().ToString()
                Next
                objConnection.Close()
            Catch ex As Exception
                MsgBox(ex.ToString())

            End Try
0
 

Author Comment

by:kwh3856
ID: 12018032
Also, I am not sure if it makes a difference, some of the array variables have data, other variables have the word NOTHING and other variables had "" for the value.


 readingElaItemCorrectRespArray(1)=a
 readingElaItemCorrectRespArray(2)=c
 readingElaItemCorrectRespArray(3)=d
 readingElaItemCorrectRespArray(4)=NOTHING
 readingElaItemCorrectRespArray(r4)=""

That is an example when I look at the locals during the debug

Thanks
Kenny



0
 
LVL 7

Expert Comment

by:natloz
ID: 12018241
Do any of these succeed?

test = readingElaItemCorrectRespArray(r4)
0
 

Author Comment

by:kwh3856
ID: 12018291
No, I tried that as a test and it did not work either.

Thanks
Kenny
0
 
LVL 7

Expert Comment

by:natloz
ID: 12018351
objCommand.Parameters.Add(New SqlParameter("@eadmDate", SqlDbType.DateTime, 8)).Value = madmDate
objCommand.Parameters.Add(New SqlParameter("@estudentId", SqlDbType.Int, 4)).Value = studentId
objCommand.Parameters.Add(New SqlParameter("@eteacherId", SqlDbType.Int, 4)).Value = teacher1
objCommand.Parameters.Add(New SqlParameter("@esubject", SqlDbType.VarChar, 50)).Value = subject
objCommand.Parameters.Add(New SqlParameter("@ereadingElaobj", SqlDbType.Int, 4)).Value = readingElaobj(r4)
objCommand.Parameters.Add(New SqlParameter("@ereadingElaItemCorrectRespArray", SqlDbType.VarChar, 50)).Value =
readingElaItemCorrectRespArray(r4)
objCommand.Parameters.Add(New SqlParameter("@ereadingElaStudentRespArray", SqlDbType.VarChar, 50).Value = readingElaStudentRespArray(r4)

Not sure if this is important....but the Add(New SqlParameter( portion may be needed...
0
 

Author Comment

by:kwh3856
ID: 12018409
Nope, still same problem

Thanks
Kenny
0
 
LVL 7

Expert Comment

by:natloz
ID: 12018500
Do a test where you hardcode the value...

objCommand.Parameters.Add(New SqlParameter("@eadmDate", SqlDbType.DateTime, 8)).Value = madmDate
objCommand.Parameters.Add(New SqlParameter("@estudentId", SqlDbType.Int, 4)).Value = studentId
objCommand.Parameters.Add(New SqlParameter("@eteacherId", SqlDbType.Int, 4)).Value = teacher1
objCommand.Parameters.Add(New SqlParameter("@esubject", SqlDbType.VarChar, 50)).Value = subject
objCommand.Parameters.Add(New SqlParameter("@ereadingElaobj", SqlDbType.Int, 4)).Value = readingElaobj(r4)
objCommand.Parameters.Add(New SqlParameter("@ereadingElaItemCorrectRespArray", SqlDbType.VarChar, 50)).Value =
"TESTVALUE1"
objCommand.Parameters.Add(New SqlParameter("@ereadingElaStudentRespArray", SqlDbType.VarChar, 50).Value = "TESTVALUE2"


0
 

Author Comment

by:kwh3856
ID: 12018583
Same problem occured.  Do you think the problem could be in the line of code

objCommand.CommandText = "Insert Into itemAnalysisDetail " & "(admDate, studentId, teacherId, subject, objectiveNumber, itemCorrectResp, studentCorrectResp) " & _
                                "VALUES (@eadmDate, @estudentId,@eteacherId,@esubject,@ereadingElaobj,@ereadingElaItemCorrectRespArray,@ereadingElaStudentRespArray)"


I thought the order might have something to do with it so I put the @esubject respective to the Add statements.

Thanks
Kenny

0
 

Author Comment

by:kwh3856
ID: 12018607
Strike that previous remark.....It did work...now I am having the same problem with the next line.  Let me try hardcoding a value for it and see what happens.


Thanks
Kenny
0
 

Author Comment

by:kwh3856
ID: 12018637
Yes....when I hard code the value.....the problem goes away.....does this command not support arrays????

Thanks
Kenny
0
 
LVL 7

Expert Comment

by:natloz
ID: 12018652
Try an insert with just the necessitys for the table such as perhaps.......

objCommand.CommandText = "Insert Into itemAnalysisDetail " & "(admDate, studentId, teacherId, subject, objectiveNumber, itemCorrectResp, studentCorrectResp) " & _
                                "VALUES (@eadmDate, @estudentId,@eteacherId,@esubject)"


objCommand.Parameters.Add(New SqlParameter("@eadmDate", SqlDbType.DateTime, 8)).Value = madmDate
objCommand.Parameters.Add(New SqlParameter("@estudentId", SqlDbType.Int, 4)).Value = studentId
objCommand.Parameters.Add(New SqlParameter("@eteacherId", SqlDbType.Int, 4)).Value = teacher1
objCommand.Parameters.Add(New SqlParameter("@esubject", SqlDbType.VarChar, 50)).Value = subject



0
 

Author Comment

by:kwh3856
ID: 12018662
Back again....The error message is now

Input string not in correct format.....do I have a data type mismatch somehow????

Thanks
Kenny
0
 
LVL 7

Expert Comment

by:natloz
ID: 12018671
Ok...strike my last remark..we are getting somewhere...

Create a simple array that has no NOTHINGS, BLANKS or NULLS and test this...in place of one of your values
0
 
LVL 7

Accepted Solution

by:
natloz earned 500 total points
ID: 12018685
Fill it with valid strings.
0
 

Author Comment

by:kwh3856
ID: 12019408
natloz,

The error appears when the value is equal to NOTHING.

How do I trap for NOTHING???

Thanks
Kenny
0
 
LVL 7

Expert Comment

by:natloz
ID: 12019556
What you should do is have temp strings that check for nothing and if it is nothing then give a value before passing...

Dim strTemp1 as string

if readingElaItemCorrectRespArray(r4) = Nothing then
  strTemp1 = "N/A"
else
  strTemp1 = readingElaItemCorrectRespArray(r4)
endif

objCommand.Parameters.Add(New SqlParameter("@ereadingElaItemCorrectRespArray", SqlDbType.VarChar, 50)).Value =
strTemp1
0
 

Author Comment

by:kwh3856
ID: 12019571
natloz,

Here is the final code that works.  Thank you very much for all your help...I learned alot about debugging on this one.

Thanks
Kenny


Try
                'Write ELA Objective Info record
                subject = "Reading ELA"
                Dim objConnection As SqlConnection = New SqlConnection("server=(local);database=FBISD-TAKS;user id=sa;password=pa55word")
                Dim r4 As Int32
                objConnection.Open()
                For r4 = 1 To 51
                    Dim objCommand As SqlCommand = New SqlCommand
                    objCommand.Connection = objConnection
                    objCommand.CommandText = "Insert Into itemAnalysisDetail " & "(admDate, studentId, teacherId, subject, objectiveNumber, itemCorrectResp, studentCorrectResp) " & _
                                "VALUES (@eadmDate, @estudentId,@eteacherId,@esubject,@ereadingElaObjNumbers,@ereadingElaItemCorrectRespArray,@ereadingElaStudentRespArray)"
                    objCommand.Parameters.Add(New SqlParameter("@eadmDate", SqlDbType.DateTime, 8)).Value = madmDate
                    objCommand.Parameters.Add(New SqlParameter("@estudentId", SqlDbType.Int, 4)).Value = studentId
                    objCommand.Parameters.Add(New SqlParameter("@eteacherId", SqlDbType.Int, 4)).Value = teacher1
                    objCommand.Parameters.Add(New SqlParameter("@esubject", SqlDbType.VarChar, 50)).Value = subject
                    objCommand.Parameters.Add(New SqlParameter("@ereadingElaObjNumbers", SqlDbType.Int, 4)).Value = Val(readingElaobj(r4))
                    If readingElaItemCorrectRespArray(r4) = Nothing Then
                        readingElaItemCorrectRespArray(r4) = ""
                    End If
                    objCommand.Parameters.Add(New SqlParameter("@ereadingElaItemCorrectRespArray", SqlDbType.VarChar, 1)).Value = readingElaItemCorrectRespArray(r4)
                    If readingElaStudentRespArray(r4) = Nothing Then
                        readingElaStudentRespArray(r4) = ""
                    End If
                    objCommand.Parameters.Add(New SqlParameter("@ereadingElaStudentRespArray", SqlDbType.VarChar, 1)).Value = readingElaStudentRespArray(r4)
                    objCommand.ExecuteNonQuery().ToString()
                Next
                objConnection.Close()
            Catch ex As Exception
                MsgBox(ex.ToString())

            End Try
0

Featured Post

ScreenConnect 6.0 Free Trial

At ScreenConnect, partner feedback doesn't fall on deaf ears. We collected partner suggestions off of their virtual wish list and transformed them into one game-changing release: ScreenConnect 6.0. Explore all of the extras and enhancements for yourself!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question