Solved

Parameter not supplied

Posted on 2004-09-08
24
340 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
Comment Utility
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
Comment Utility
Or you can use..
                    objCommand.Parameters.Add("@ereadingElaItemCorrectRespArray", SqlDbType.VarChar, 50).Value = readingElaItemCorrectRespArray(r4).ToString()

-Baan
0
 
LVL 18

Expert Comment

by:DotNetLover_Baan
Comment Utility
Whay you are using .ToString() here...  objCommand.ExecuteNonQuery().ToString()
-Baan
0
 

Author Comment

by:kwh3856
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Do any of these succeed?

test = readingElaItemCorrectRespArray(r4)
0
 

Author Comment

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

Thanks
Kenny
0
 
LVL 7

Expert Comment

by:natloz
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:kwh3856
Comment Utility
Nope, still same problem

Thanks
Kenny
0
 
LVL 7

Expert Comment

by:natloz
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Fill it with valid strings.
0
 

Author Comment

by:kwh3856
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now