[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 381
  • Last Modified:

Parameter not supplied

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
kwh3856
Asked:
kwh3856
  • 13
  • 9
  • 2
1 Solution
 
natlozCommented:
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
 
DotNetLover_BaanCommented:
Or you can use..
                    objCommand.Parameters.Add("@ereadingElaItemCorrectRespArray", SqlDbType.VarChar, 50).Value = readingElaItemCorrectRespArray(r4).ToString()

-Baan
0
 
DotNetLover_BaanCommented:
Whay you are using .ToString() here...  objCommand.ExecuteNonQuery().ToString()
-Baan
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
kwh3856Author Commented:
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
 
kwh3856Author Commented:
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
 
kwh3856Author Commented:
Natloz,
I tried your command and it did not work either.  I got the same error message

Any other ideas?????

Thanks
Kenny
0
 
natlozCommented:
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
 
kwh3856Author Commented:
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
 
kwh3856Author Commented:
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
 
natlozCommented:
Do any of these succeed?

test = readingElaItemCorrectRespArray(r4)
0
 
kwh3856Author Commented:
No, I tried that as a test and it did not work either.

Thanks
Kenny
0
 
natlozCommented:
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
 
kwh3856Author Commented:
Nope, still same problem

Thanks
Kenny
0
 
natlozCommented:
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
 
kwh3856Author Commented:
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
 
kwh3856Author Commented:
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
 
kwh3856Author Commented:
Yes....when I hard code the value.....the problem goes away.....does this command not support arrays????

Thanks
Kenny
0
 
natlozCommented:
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
 
kwh3856Author Commented:
Back again....The error message is now

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

Thanks
Kenny
0
 
natlozCommented:
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
 
natlozCommented:
Fill it with valid strings.
0
 
kwh3856Author Commented:
natloz,

The error appears when the value is equal to NOTHING.

How do I trap for NOTHING???

Thanks
Kenny
0
 
natlozCommented:
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
 
kwh3856Author Commented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 13
  • 9
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now