Input string was not in a correct format.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Input string was not in a correct format.

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-03-09 : 07:02:05

Here's my sp to which I am passing a where clause. The strings all work fine but when I try to pass an integer it doesn't get as far as the sp.I get the message above.

CREATE PROCEDURE sp_RMUProcessRequestFilter (@WHEREClause varchar(1000))
AS

-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(1000)

-- Enter the dynamic SQL statement into the
-- variable @SQLStatement
SELECT @SQLStatement = "SELECT * FROM tblFileRequests WHERE "
+ @WHEREClause

-- Execute the SQL statement
EXEC(@SQLStatement)
GO

Here's the line I'm passing that it errors out on.

ReqNo = Me.txtReqNo.Text
SQLstr = SQLstr + "MovementId = '" + CInt(ReqNo) + "'"

I realise that as an integer it shouldn't have quotes around it, but I have also tried
SQLstr = SQLstr + "MovementId = " + CInt(ReqNo)

which doesn't work either - same error

Any help - TIA

Kristen
Test

22859 Posts

Posted - 2005-03-09 : 07:30:27

What happens when you type

EXEC sp_RMUProcessRequestFilter 'MovementId = 1234'

in Query Analyser? (Substitute a more sensible value for MovementID maybe!)

The Quotes [that you mentioned] won't matter - SQL will make an implicit conversion from String to Integer (assuming you don't try to use 'ABC', obviously)

I presume that the "MovementID" column is actually defined as an INT in tblFileRequests?

Note that you ought NOT to us a "sp_" prefix for your SProc names. "sp_" prefix is reserved for SProcs that exist in the MASTER database. SQL Server will attempt to execute any SProc in master BEFORE looking for one of that name in your database. Even if you do not have such an SProc in MASTER there is still considerable "effort" in looking in MASTER each time the SProc is executed.

Whilst I'm banging on my Drum

best not to use SELECT * - hopefully the code above is a WorkInProgress. SELECT * will return all columns defined in the table; it requires SQL Server to work out what "all columns" means every time; and means that SQL will return All Columns in the future too - even after you add, say, a bunch of TEXT columns that this particular process doesn't require -and which will likely take considerable effort to transmit from Server to Client. Much better to explicitly name the columns you need - even though its a pain in the ar$e

Kristen

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-03-09 : 09:36:34

Thanks for your detailed reply Kristen - you can tell I am self taught and have not formal training in SQL ! I will take note of your suggestions in future as I didn't know about your suggestions.

If I substitute an actual MovmentId it works fine. Like this -

SQLstr = SQLstr + "MovementId = 28775"

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-03-09 : 09:48:39

I have changed it to

SQLstr = "MovementId = " + Me.TxtReqNo.Text

and it works......

Kristen
Test

22859 Posts

Posted - 2005-03-09 : 23:48:50

"you can tell I am self taught and have not formal training in SQL"

We were all beginners at some point! And after all that's what SQLTeam is here for ...

Maybe it was a VB error, rather than SQL Server? The concatenation might need to be:

SQLstr = SQLstr + "MovementId = " + CStr(CInt(ReqNo))

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources