使用存储过程写入表？ [英] Writing to a table using a stored procedure?

问题描述

嘿伙计们，我试图通过存储过程写入表，我一直收到错误：



过程或函数ParseTheString指定了太多参数。



这里有简单的代码：



它是一个文本框和一个按钮。 />

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.

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:

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

我只是输入'已从1号视频服务器收到警报ID 6'



为什么会出错？





是因为我正在指定警报ID和servernum？

如果是这样，我如何通过sp添加记录？



任何帮助都是非常感谢。

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.

推荐答案

因为在你的存储过程中只有一个参数

Because in your stored procedure there is only one parameter

@Message VARCHAR(1000)

而从代码中传递两个参数

whereas from code you are passing two parameters

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

仅传递单个参数。喜欢

Pass only single parameter. Like

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

您在代码中添加了两个参数：

You are adding two parameter in your code:

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

..在您的SP中，您刚刚声明了一个：

..and in your SP you just declared one:

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

在SP中为AlaramID或ServerNumber声明一个参数，或者不从代码中传递参数。

Either declare a parameter in you SP for AlaramID or ServerNumber, or don't pass parameter from your code.

你可以只将 @Message VARCHAR（1000）作为参数放入 ParseTheString 。放入实际参数会更加明智，但是这个存储过程的创建者喜欢进行大量的字符串解析，并希望服务器尽可能长时间执行。



您可以考虑创建2个存储过程。一个简单地接受2个参数并改变现有的，所以它解析字符串然后用找到的参数调用新的。



祝你好运！

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!

这篇关于使用存储过程写入表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！