无法使用SQL CRUD将数据添加到数据库? [英] Can't add data to database using SQL CRUD?

问题描述

所以我要建立一个用于电影系统的数据库.我已使用SQL制作了数据库，并使用ado.net在控制台应用程序中显示了来自数据库的信息.我一直在使用SQL CRUD将控制台应用程序中的数据添加到数据库中，但是在将文本添加到数据库时遇到了麻烦.我已经使用参数来执行此操作，但出现错误消息无法将参数值从字符串转换为Int32".我将cm.Parameters ["@ FilmName"].Value更改为cm.Parameters ["@ FilmName"].ToString，因为很明显它无法使用value添加文本，但仍然出现错误.

这是我的代码，代码的SQL插入命令部分是需要更改的部分.

Hi, so i''m making a database which is for a film system. I''ve made the database in SQL and i''ve used ado.net to display information from the database in a console application. I''ve been using SQL CRUD to add data from the console app into the database, but im having trouble adding text to the database. I''ve used the parameters to do this but it comes up with error saying "Failed to convet parameter value from a String to Int32". I changed the cm.Parameters["@FilmName"].Value into cm.Parameters["@FilmName"].ToString since obviously it couldnt add text using value, but it still comes up with an error.

Heres my code, the SQL Insert command part of the code is the part that needs changing.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace Database
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                // set up data connection
                string cs = "Data Source=MASTER\\MASTER;Initial Catalog=FilmDB;Integrated Security=True;Pooling=False";
                SqlConnection cn = new SqlConnection(cs);
                // Set up SQL command
                SqlCommand cm = cn.CreateCommand();
                ///
                ///Sql Select Command (Read table and return data)
                ///
                cm.CommandText = "SELECT FilmName, FilmGenre FROM [dbo].[Film];";
                // Set up adapter manager
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cm;
                //Set dataset for results
                DataSet ds = new DataSet();
                ds.Clear();
                //Open Connection to database
                cn.Open();
                //Ask adapter to transfer data from table to dataset
                da.Fill(ds, "Film");
                //Set up data table
                DataTable dt = ds.Tables["Film"];
                //Read data from table rows
                foreach (DataRow dr in dt.Rows)
                {
                    Console.WriteLine(dr["FilmName"].ToString() + "\t\t\t\t" + dr["FilmGenre"].ToString());
                }
                ///
                /// SQL INSERT command
                /// 
                cs = "INSERT INTO [dbo].[Film] ( FilmID, FilmName, FilmGenre ) VALUES ( @FilmID, @FilmName, @FilmGenre );";
                cm.CommandText = cs;
                cm.Parameters.Add("@FilmID", SqlDbType.Int);
                cm.Parameters.Add("@FilmName", SqlDbType.Text);
                cm.Parameters.Add("@FilmGenre", SqlDbType.Text);
                cm.Parameters["@FilmID"].Value = 34;
                cm.Parameters["@FilmName"].Value = "Inception";
                cm.Parameters["@FilmID"].Value = "Action/Thriller";
                
                cm.ExecuteNonQuery();
                cn.Close();

            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
            finally
            {
                //cn.close();
            }
        }
    }
}

推荐答案

Failed to convert parameter value from a String to Int32

这意味着您将字符串传递给SQL，但是数据库中的字段是Int32.

实际上，这很简单:

This implies that you handing the string to SQL, but the field in the database is an Int32.

In fact, it''s simple:

cm.Parameters["@FilmID"].Value = 34;
cm.Parameters["@FilmName"].Value = "Inception";
cm.Parameters["@FilmID"].Value = "Action/Thriller";

更改最后一行:

Change the last line:

cm.Parameters["@FilmID"].Value = 34;
cm.Parameters["@FilmName"].Value = "Inception";
cm.Parameters["@FilmGenre"].Value = "Action/Thriller";

这篇关于无法使用SQL CRUD将数据添加到数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！