Stored Procedure error - Invalid Object Name

20/11/2008 10:59:39 ... Error: Database.vb/insertPictures: Invalid object name 'Plan Number'.

I am having trouble finding what is causing this issue in my SP, I think it has somehting to do with the prepared statement but not sure what. I would be grateful of any help. :(
METHOD CALL
-----------
doInsert.insertPictures(dsPictures.Tables(0).Rows(i)("PictureName"), dsPictures.Tables(0).Rows(i)("WorkOrderNo"), dsPictures.Tables(0).Rows(i)("IssueNo"), dsPictures.Tables(0).Rows(i)("RoundName"), dsPictures.Tables(0).Rows(i)("OperativeName"), dsPictures.Tables(0).Rows(i)("InChargeDate"), dsPictures.Tables(0).Rows(i)("LocationNo"), dsPictures.Tables(0).Rows(i)("PanelNo"), dsPictures.Tables(0).Rows(i)("WorkDate"), dsPictures.Tables(0).Rows(i)("WorkTime"), dsPictures.Tables(0).Rows(i)("DesignName"), dsPictures.Tables(0).Rows(i)("GPSLatitude"), dsPictures.Tables(0).Rows(i)("GPSLongitude"), dsPictures.Tables(0).Rows(i)("MOMSFeedback"), dsPictures.Tables(0).Rows(i)("PlanNum"), dsPictures.Tables(0).Rows(i)("NameStreet"), dsPictures.Tables(0).Rows(i)("Address1"), dsPictures.Tables(0).Rows(i)("Address2"), dsPictures.Tables(0).Rows(i)("City"), sCountry, Server.MapPath("/logs/" & sCountry & ".txt"))
 
INSERT CLASS
------------
Public Sub insertPictures(ByVal PictureName As String, ByVal WorkOrderNo As String, ByVal IssueNo As String, ByVal RoundName As String, ByVal OperativeName As String, ByVal InChargeDate As String, ByVal LocationNo As String, ByVal PanelNo As String, ByVal WorkDate As String, ByVal WorkTime As String, ByVal DesignName As String, ByVal GPSLatitude As String, ByVal GPSLongitude As String, ByVal Feedback As String, ByVal Country As String, ByVal PlanNum As String, ByVal NameStreet As String, ByVal Address1 As String, ByVal Address2 As String, ByVal City As String, ByVal ApplicationLog As String)
        doGlobal.mySQLCmd = New SqlClient.SqlCommand("AddPicture", doGlobal.mySQLConn)
        doGlobal.mySQLCmd.CommandType = CommandType.StoredProcedure
 
        Dim param1 As New SqlClient.SqlParameter("@PictureName", SqlDbType.NVarChar, 150)
        param1.Direction = ParameterDirection.Input
        param1.Value = Trim(PictureName)
        doGlobal.mySQLCmd.Parameters.Add(param1)
 
        Dim param2 As New SqlClient.SqlParameter("@WorkOrderNo", SqlDbType.NVarChar, 50)
        param2.Direction = ParameterDirection.Input
        param2.Value = WorkOrderNo
        doGlobal.mySQLCmd.Parameters.Add(param2)
 
        Dim param3 As New SqlClient.SqlParameter("@IssueNo", SqlDbType.NVarChar, 50)
        param3.Direction = ParameterDirection.Input
        param3.Value = IssueNo
        doGlobal.mySQLCmd.Parameters.Add(param3)
 
        Dim param4 As New SqlClient.SqlParameter("@RoundName", SqlDbType.NVarChar, 75)
        param4.Direction = ParameterDirection.Input
        param4.Value = Trim(RoundName)
        doGlobal.mySQLCmd.Parameters.Add(param4)
 
        Dim param5 As New SqlClient.SqlParameter("@OperativeName", SqlDbType.NVarChar, 100)
        param5.Direction = ParameterDirection.Input
        param5.Value = Trim(OperativeName)
        doGlobal.mySQLCmd.Parameters.Add(param5)
 
        Dim param6 As New SqlClient.SqlParameter("@InChargeDate", SqlDbType.SmallDateTime)
        param6.Direction = ParameterDirection.Input
        param6.Value = IIf(IsDate(InChargeDate), InChargeDate, DBNull.Value)
        doGlobal.mySQLCmd.Parameters.Add(param6)
 
        Dim param7 As New SqlClient.SqlParameter("@LocationNo", SqlDbType.NVarChar, 50)
        param7.Direction = ParameterDirection.Input
        param7.Value = LocationNo
        doGlobal.mySQLCmd.Parameters.Add(param7)
 
        Dim param8 As New SqlClient.SqlParameter("@PanelNo", SqlDbType.NVarChar, 50)
        param8.Direction = ParameterDirection.Input
        param8.Value = PanelNo
        doGlobal.mySQLCmd.Parameters.Add(param8)
 
        Dim param9 As New SqlClient.SqlParameter("@WorkDate", SqlDbType.SmallDateTime)
        param9.Direction = ParameterDirection.Input
        param9.Value = IIf(IsDate(WorkDate), WorkDate, DBNull.Value)
        doGlobal.mySQLCmd.Parameters.Add(param9)
 
        Dim param10 As New SqlClient.SqlParameter("@WorkTime", SqlDbType.SmallDateTime)
        param10.Direction = ParameterDirection.Input
        param10.Value = IIf(IsDate(WorkTime), WorkTime, DBNull.Value)
        doGlobal.mySQLCmd.Parameters.Add(param10)
 
        Dim param11 As New SqlClient.SqlParameter("@DesignName", SqlDbType.NVarChar, 4000)
        param11.Direction = ParameterDirection.Input
        param11.Value = Trim(DesignName)
        doGlobal.mySQLCmd.Parameters.Add(param11)
 
        Dim param12 As New SqlClient.SqlParameter("@GPSLatitude", SqlDbType.NVarChar, 50)
        param12.Direction = ParameterDirection.Input
        param12.Value = Trim(GPSLatitude)
        doGlobal.mySQLCmd.Parameters.Add(param12)
 
        Dim param13 As New SqlClient.SqlParameter("@GPSLongitude", SqlDbType.NVarChar, 50)
        param13.Direction = ParameterDirection.Input
        param13.Value = Trim(GPSLongitude)
        doGlobal.mySQLCmd.Parameters.Add(param13)
 
        Dim param14 As New SqlClient.SqlParameter("@MOMSFeedback", SqlDbType.NVarChar, 4000)
        param14.Direction = ParameterDirection.Input
        param14.Value = Trim(Feedback)
        doGlobal.mySQLCmd.Parameters.Add(param14)
 
        Dim param15 As New SqlClient.SqlParameter("@Country", SqlDbType.NVarChar, 50)
        param15.Direction = ParameterDirection.Input
        param15.Value = Trim(Country)
        doGlobal.mySQLCmd.Parameters.Add(param15)
 
        Dim param16 As New SqlClient.SqlParameter("@PlanNum", SqlDbType.NVarChar, 50)
        param16.Direction = ParameterDirection.Input
        param16.Value = Trim(PlanNum)
        doGlobal.mySQLCmd.Parameters.Add(param16)
 
        Dim param17 As New SqlClient.SqlParameter("@NameStreet", SqlDbType.NVarChar, 150)
        param17.Direction = ParameterDirection.Input
        param17.Value = Trim(NameStreet)
        doGlobal.mySQLCmd.Parameters.Add(param17)
 
        Dim param18 As New SqlClient.SqlParameter("@Address1", SqlDbType.NVarChar, 150)
        param18.Direction = ParameterDirection.Input
        param18.Value = Trim(Address1)
        doGlobal.mySQLCmd.Parameters.Add(param18)
 
        Dim param19 As New SqlClient.SqlParameter("@Address2", SqlDbType.NVarChar, 150)
        param19.Direction = ParameterDirection.Input
        param19.Value = Trim(Address2)
        doGlobal.mySQLCmd.Parameters.Add(param19)
 
        Dim param20 As New SqlClient.SqlParameter("@City", SqlDbType.NVarChar, 100)
        param20.Direction = ParameterDirection.Input
        param20.Value = Trim(City)
        doGlobal.mySQLCmd.Parameters.Add(param20)
 
        Try
            doGlobal.mySQLConn.Open()
            doGlobal.mySQLCmd.ExecuteNonQuery()
            doGlobal.mySQLConn.Close()
        Catch ex As Exception
            doGlobal.mySQLConn.Close()
            doGlobal.doError("Database.vb/insertPictures: " & ex.Message.ToString, ApplicationLog)
        End Try
 
    End Sub
 
STORED PROCEDURE
----------------
CREATE PROCEDURE AddPicture
@PictureName nvarchar(150), @WorkOrderNo nvarchar(50), @IssueNo nvarchar(50), @RoundName nvarchar(100), @OperativeName nvarchar(100), @InChargeDate smalldatetime, @LocationNo nvarchar(50), @PanelNo nvarchar(50), @WorkDate smalldatetime, @WorkTime smalldatetime, @DesignName nvarchar(4000), @GPSLongitude nvarchar(50), @GPSLatitude nvarchar(50), @MOMSFeedback ntext, @Country nvarchar(50), @PlanNum nvarchar(50), @NameStreet nvarchar(150), @Address1 nvarchar(150), @Address2 nvarchar(150), @City nvarchar(100)
AS
declare @sql nvarchar(4000)
set @sql = 'INSERT INTO [' + @Country + '] (PictureName, WorkOrderNo, IssueNo, RoundName, OperativeName, InChargeDate, LocationNo, PanelNo, WorkDate, WorkTime, DesignName, GPSLatitiude, GPSLongitude, MOMSFeedback, PlanNum, NameStreet, Address1, Address2, City) VALUES (@PictureName, @WorkOrderNo, @IssueNo, @RoundName, @OperativeName, @InChargeDate, @LocationNo, @PanelNo, @workDate, @WorkTime, @DesignName, @GPSLatitude, @GPSLongitude, @MOMSFeedback, @PlanNum, @NameStreet, @Address1, @Address2, @City)' 
exec sp_executesql @sql, N'@PictureName nvarchar(150), @WorkOrderNo nvarchar(50), @IssueNo nvarchar(50), @RoundName nvarchar(100), @OperativeName nvarchar(100), @InChargeDate smalldatetime, @LocationNo nvarchar(50), @PanelNo nvarchar(50), @WorkDate smalldatetime, @WorkTime smalldatetime, @DesignName nvarchar(4000), @GPSLongitude nvarchar(50), @GPSLatitude nvarchar(50), @MOMSFeedback nvarchar(4000), @PlanNum nvarchar(50), @NameStreet nvarchar(150), @Address1 nvarchar(150), @Address2 nvarchar(150), @City nvarchar(100)', @PictureName, @WorkOrderNo, @IssueNo, @RoundName, @OperativeName, @InChargeDate, @LocationNo, @PanelNo, @workDate, @WorkTime, @DesignName, @GPSLatitude, @GPSLongitude, @MOMSFeedback, @PlanNum, @NameStreet, @Address1, @Address2, @City
GO

Open in new window

pictures.txt
clearchannelAsked:
Who is Participating?
 
clearchannelConnect With a Mentor Author Commented:
Error seemed to be coming from there being one too many fields between the class and SP.
Nothing to do with the names or caching.
0
 
DhaestCommented:
Did you change your columnname from "plan number" to "planno" ?
You can try to recreate the stored procedure with another name, alter you code and test to see if goes then
0
 
clearchannelAuthor Commented:
Originally the db field was called "PlanNo" and then I changed it to "PlanNum". 'Plan Number' it refers to is the string being passed in via the xml fiel I attached in my original post.

I have attached a screenshot of the table.
db.jpg
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
clearchannelAuthor Commented:
Ignore that last image as the pic is the wrong file.


db.jpg
0
 
amxCommented:
just a stab i the dark but...

you seem to be passing @MOMSFeedback ntext but use @MOMSFeedback nvarchar(4000) when executing the sql
0
 
DhaestCommented:
Did you try to copy your "AddPicture" into "AddPicture2"  and call the last one to see if the error still occurs
0
 
clearchannelAuthor Commented:
I don't see why the name of the SP should make any difference but I'll give it a go I guess.,
0
 
DhaestCommented:
It can be that the sp is cached by your application, that's why you just need to test it (to make sure that the program is taking the correct sp)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.