[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Stored Procedure error - Invalid Object Name

Posted on 2008-11-20
8
Medium Priority
?
402 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:clearchannel
  • 4
  • 3
8 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 23002720
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
 

Author Comment

by:clearchannel
ID: 23002740
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
 

Author Comment

by:clearchannel
ID: 23002752
Ignore that last image as the pic is the wrong file.


db.jpg
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 9

Expert Comment

by:amx
ID: 23002853
just a stab i the dark but...

you seem to be passing @MOMSFeedback ntext but use @MOMSFeedback nvarchar(4000) when executing the sql
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 23003100
Did you try to copy your "AddPicture" into "AddPicture2"  and call the last one to see if the error still occurs
0
 

Author Comment

by:clearchannel
ID: 23003107
I don't see why the name of the SP should make any difference but I'll give it a go I guess.,
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 23003117
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
 

Accepted Solution

by:
clearchannel earned 0 total points
ID: 23004743
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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

834 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