Click here to Skip to main content
15,906,625 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hey guys, im trying to write to a table through a stored proc, i keep getting an error:

Procedure or function ParseTheString has too many arguments specified.

here is the simple code behind:

it is a text box and a button.
C#
private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = "Data Source=Matthew-PC\\SQLEXPRESS;Initial Catalog=SureViewDB;Integrated Security=True";
            con.Open();
            SqlCommand insertcmd = new SqlCommand("ParseTheString", con);
            insertcmd.Parameters.Add("@AlarmID", SqlDbType.VarChar).Value = textBox1.Text;
            insertcmd.Parameters.Add("@ServerNumber", SqlDbType.VarChar).Value = textBox1.Text;
            insertcmd.CommandType = CommandType.StoredProcedure;
            insertcmd.ExecuteNonQuery();
            con.Close();
        }


And here is the stored procedure running against data entered into text box:


SQL
CREATE PROCEDURE [dbo].[ParseTheString]

@Message VARCHAR(1000)

--@message examples

--'The alarm id from server number 1 is 6'

--'Alarm ID 6 has been received from video server number 1'

AS

 

BEGIN

DECLARE

         @str1 VARCHAR(1),@str2 VARCHAR(250),@str3 VARCHAR(250),@str4 VARCHAR(250),

         @str5 VARCHAR(250),@num1 INT,@num2 INT,@loop1 INT, @loop2 INT,@MType INT,

         @str6 VARCHAR(25),@numstart int,@numend int,@now DATETIME

 

 

--Check if the table that stores the data exists, if not create it.

IF (NOT EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

                          WHERE TABLE_NAME = 'LoggedMessages'  

                            AND TABLE_SCHEMA = 'dbo')) 

        BEGIN

        CREATE TABLE dbo.LoggedMessages (AlarmUnit INT Identity (1,1),ServerNo INT, AlarmID INT, GeneratedOn DATETIME, MessageType INT)      

        END

 

---------------ALTER THE CODE HERE TO SUIT THE STARTING WORD OF THE MESSAGES THAT WILL BE RECIEVED.....................

  IF LOWER(LEFT(LTRIM(@Message),5)) = 'alarm'  --convert to lowercase in case there is a capital letter at the start of the word

  BEGIN                                        --and also remove any whitespace from beginning of string

    SET @MType = 1

  END      

   ELSE

     BEGIN

       SET @MType = 2

     END

--------------------------

 

 

 

SET @loop1 = 1

  SET @str1 = (SUBSTRING(@Message,@loop1,1))

    WHILE ISNUMERIC(@str1)=0  --this condition looks for characters in a string.

      BEGIN

        --scroll through the rest of the string until we get a number instead of a character.

        SET @loop1 = @loop1 + 1

         SET @str1 = (SUBSTRING(@Message,@loop1,1))

         PRINT '@loop1a position = '+CAST(@loop1 AS VARCHAR(20))

      END

SET @numstart = @loop1     

--we now have the starting position of the first set of numbers.

 

--numbers start at point @loop1

  SET @str1 = (SUBSTRING(@Message,@loop1,1))

    WHILE ISNUMERIC(@str1)=1  --this condition looks for numbers in a string.

      BEGIN

        --scroll through the rest of the string until we get a character instead of a number.

        SET @loop1 = @loop1 + 1   --increment the position we are looking at.

         SET @str1 = (SUBSTRING(@Message,@loop1,1))  --check the next character to see if it is a number.

         PRINT '@loop1 position = '+CAST(@loop1 AS VARCHAR(20))  --this is to check the position of the numbers is correct.

      END

SET @numend = @loop1

--now get the number out of the string.

SET @num1 = (SELECT SUBSTRING(@Message,@numstart,(@numend-@numstart)))

PRINT @num1

 

--now get the next set of numbers.

 

  SET @str1 = (SUBSTRING(@Message,@loop1,1))

    WHILE ISNUMERIC(@str1)=0  --this condition looks for characters in a string.

      BEGIN

        --scroll through the rest of the string until we get a number instead of a character.

        SET @loop1 = @loop1 + 1

         SET @str1 = (SUBSTRING(@Message,@loop1,1))

         PRINT '@loop1a position = '+CAST(@loop1 AS VARCHAR(20))

      END

SET @numstart = @loop1         

--we now have the starting position of the first set of numbers.

 

--numbers start at point @loop1a

  SET @str1 = (SUBSTRING(@Message,@loop1,1))

    WHILE ISNUMERIC(@str1)=1  --this condition looks for numbers in a string.

      BEGIN

        --scroll through the rest of the string until we get a character instead of a number.

        SET @loop1 = @loop1 + 1

         SET @str1 = (SUBSTRING(@Message,@loop1,1))

         PRINT '@loop1a position = '+CAST(@loop1 AS VARCHAR(20))

      END

SET @numend = @loop1

--now get the number out of the string.

SET @num2 = (SELECT SUBSTRING(@Message,@numstart,(@numend-@numstart)))  --set the value

PRINT @num2

 

--Now write the data to the table...........

SET @now = GETDATE()  --get the current data and time.

 

      IF @MType = 1  -- the message is in format 1 'The alarm id from server number 1 is 6'

        BEGIN

             INSERT INTO dbo.LoggedMessages VALUES (@num1,@num2,@now,1)

        END

          ELSE   -- the message is in format 2  'Alarm ID 6 has been received from video server number 1'

             BEGIN

             INSERT INTO dbo.LoggedMessages VALUES (@num2,@num1,@now,2)

             END

END




in the text box i am simply typing in 'Alarm ID 6 has been received from video server number 1'

why is it giving the error?


is it because i am specifying the alarm id and servernum?
if so, how do i go about adding records through the sp?

any help is highly appreciated.
Posted
Comments
Rockstar_ 3-Jun-13 7:27am    
Clearly the error message is saying and describing the problem ...

Because in your stored procedure there is only one parameter
SQL
@Message VARCHAR(1000)

whereas from code you are passing two parameters
C#
insertcmd.Parameters.Add("@AlarmID", SqlDbType.VarChar).Value = textBox1.Text;
insertcmd.Parameters.Add("@ServerNumber", SqlDbType.VarChar).Value = textBox1.Text;


Pass only single parameter. Like
C#
insertcmd.Parameters.Add("@Message", SqlDbType.VarChar).Value = textBox1.Text;
 
Share this answer
 
You are adding two parameter in your code:
SQL
insertcmd.Parameters.Add("@AlarmID", SqlDbType.VarChar).Value = textBox1.Text;
insertcmd.Parameters.Add("@ServerNumber", SqlDbType.VarChar).Value = textBox1.Text;


..and in your SP you just declared one:
SQL
CREATE PROCEDURE [dbo].[ParseTheString]
 @Message VARCHAR(1000) 
--@message examples
--'The alarm id from server number 1 is 6'
--'Alarm ID 6 has been received from video server number 1'
AS



Either declare a parameter in you SP for AlaramID or ServerNumber, or don't pass parameter from your code.
 
Share this answer
 
Comments
BBCokeley 3-Jun-13 7:32am    
@Prasad_Kulkarni

yeh just noticed it myself, 3 mins ago.

Thank you for posting :)
You can only put in @Message VARCHAR(1000) as your parameter to ParseTheString. It would be more sensible to put in the actual parameters but the creator of this stored procedure likes to do a lot of string parsing and wants the server to have a hard as possible time executing.

You could consider creating 2 stored procedures. One that simply takes the 2 parameters and change the existing so it parses the string and then calls the new one with the found arguments.

Good luck!
 
Share this answer
 
Comments
BBCokeley 3-Jun-13 7:33am    
Yup i completely missed that, been manually typing in stuf for different programs all day with the parameters, and was so used to declaring id's names etc, that i overlooked the @message in stored procedure...

Thank you.
Solved it myself,

insertcmd.Parameters.Add("@Message", SqlDbType.VarChar).Value = textBox1.Text;

i declared it as @message, not @AlarmID, so it was looking for the wrong entry.

my bad, thank you for vies tho


Kind regards.
MAtt.
 
Share this answer
 
Your stored procedure has exactly one parameter message as varchar(1000), but your passing in two parameters (using the same textbox1.Text as the value). That explains the error you are getting.

Regards,
— Manfred
 
Share this answer
 
v2
Solved, thank you guys.

Regars Matt.
 
Share this answer
 
Comments
CHill60 3-Jun-13 9:19am    
Don't post comments / messages as solutions. If you want to pass a generic thank you out there you could use the Improve question link

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900