使用c＃.net在存储过程中的数据库中插入数据 [英] Inserting data in database in store procedure using c#.net

问题描述

我创建了数据库并创建了表，之后我创建了存储过程。

和c＃.net中的代码。编译期间我没有收到错误但是在运行时我收到错误转换varchar值时转换失败'@id'数据类型int

c＃.net中的代码： -

I have created database and created table,after that I have created store procedure.
and the code in c#.net .During compilation I am not getting an error but at run time I am getting error "conversion failed when converting the varchar value '@id' data type int"
code in c#.net:-

private void button1_Click(object sender, EventArgs e)
       {
           SqlConnection cn = new SqlConnection("server=DESKTOP-7QODVGC\\SQLEXPRESS;uid=sa;database=employee;password=cdac");
           cn.Open();
           SqlCommand cmd = new SqlCommand("spinsert_3", cn);
           cmd.CommandType = CommandType.StoredProcedure;
           cmd.Parameters.AddWithValue("Id",textBox1.Text);
           cmd.Parameters.AddWithValue("Name", textBox2.Text);
           try
           {
               cmd.ExecuteNonQuery();
               cn.Close();
               MessageBox.Show("Created");

           }
           catch (Exception ex)
           {
               MessageBox.Show(ex.Message);
           }
       }

我的尝试：



创建存储过程，用c＃.net编写代码

What I have tried:

created store procedure,written the code in c#.net

推荐答案

无论textBox1.Text是什么都无法转换到一个int。在使用之前，您应始终验证用户的输入，并且在添加参数时也应使用@。

Whatever is in textBox1.Text can't be converted to an int. You should always validate inputs from the user before using them, and you should also use the "@" when adding the parameter.

int id = 0;
if (!int.TryParse(textBox1.Text, out id))
{
    // data isn't valid, do something to handle this
    return;
}

cmd.Parameters.AddWithValue("@Id", id);

好的，所以你不能直接打开sql注入，但我绝不会建议直接使用textbox.text。你应该先处理错误。举个例子：

Ok, so your not open to sql injection directly, but I would never advise using a textbox.text directly. You should handle errors first. Take this example:

private void button1_Click(object sender, EventArgs e)
       {
           int id;
           if(!int.TryParse(textBox1.Text,out id) || id <= 0){
               //Alert the user.  if all is ok then you now have id as an int 
           }
           string name = textBox2.Text.trim();
           if(string.isNullOrEmpty(name)){          
              //Alert the user.  if all is ok then you have a clean 'name'
           }

           SqlConnection cn = new SqlConnection("server=DESKTOP-7QODVGC\\SQLEXPRESS;uid=sa;database=employee;password=cdac");
           cn.Open();
           SqlCommand cmd = new SqlCommand("spinsert_3", cn);
           cmd.CommandType = CommandType.StoredProcedure;
           
           //Also you can express the parameter as a type like this
           cmd.Parameters.Add("Id",SqlDbType.Int){Value=id};
           cmd.Parameters.Add("Name",SqlDbType.VarChar){Value=name};
           try
           {
               cmd.ExecuteNonQuery();
               cn.Close();
               MessageBox.Show("Created");
 
           }
           catch (Exception ex)
           {
               MessageBox.Show(ex.Message);
           }
       }

希望至少可以解决问题



让我知道^ _ ^

Andy

That will hopefully at least catch the issue

let me know ^_^
Andy

这篇关于使用c＃.net在存储过程中的数据库中插入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！