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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dirk HaestProject managerCommented:
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
clearchannelAuthor Commented:
Ignore that last image as the pic is the wrong file.


db.jpg
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

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
Dirk HaestProject managerCommented:
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
Dirk HaestProject managerCommented:
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
clearchannelAuthor 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.