none
Study: VB 2015 with LINQ/ Issue: Insert new record to Access dataset failed RRS feed

  • 问题

  • Access: Games.accd
    Table: tblGames
    Primary Key: GameID (Auto Number) 
    Foreign Key: Platform (Short text)

    Table: tblPlatform
    Primary Key: PFID (Short text)

    Column: Platform(Short text)

    I want to insert the new records which input at form1's textboxs by user, but system popup error:
    Type"string"can't be convert to“GamesDataset.tblPlatformRow"

    第一次提问有点惶恐,而且不能上传数据网盘链接也不行。我想往access的games表里添加一行记录,但是在textbox输入的字符串外键,无法通过报错了:

    类型"string"的值无法转换为"GamesDataset.tblPlatformRow"

    I put access and code at baidu cloud drive:

    Code:

    Option Explicit On Option Strict On Option Infer On Public Class Form1 Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load 'TODO: 这行代码将数据加载到表“GamesDataSet.tblPlatform”中。您可以根据需要移动或删除它。 Me.TblPlatformTableAdapter.Fill(Me.GamesDataSet.tblPlatform) End Sub Private Sub btnAdd_Click(sender As Object, e As EventArgs) Handles btnAdd.Click Dim intPrice As Integer = CType(txtPrice.Text, Integer) Dim intQty As Integer = CType(txtQty.Text, Integer) GamesDataSet.tblGames.AddtblGamesRow(txtGameName.Text, txtPlatform.Text, txtRating.Text, intPrice, txtStates.Text, intQty) ' txtPlatform.text这一句提示: 类型"string"的值无法转换为"GamesDataset.tblPlatformRow" End Sub End Class

    Thanks in advance

    Best regards

    Han Zhengrong





    • 已编辑 zhengrong.han 2016年11月30日 23:11 添加错误位置注释
    2016年11月30日 14:09

答案

  • 我觉得用OleDb的方案要好一点,参考这里 http://www.cnblogs.com/nsky/p/4488252.html

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data.OleDb; //引入命名空间 需要添加引用 System.Data.dll

    namespace ConsoleApplication2
    {
        class Program
        {
            static void Main(string[] args)
            {
                //连接Access字符串
                string conStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=userInfo.accdb;Persist Security Info=False";

                //插入数据
                string name = "张三";
                int age = 18;

                string sql = "insert into user(name,age)values(@name,@age)";
                OleDbParameter[] para = {
                                            new OleDbParameter("@name",OleDbType.VarChar),
                                            new OleDbParameter("@age",OleDbType.Integer)                                     
                                        };
                para[0].Value = name;
                para[1].Value = age;

                int rows = 0;

                using (OleDbConnection connect = new OleDbConnection(conStr))
                {
                    using (OleDbCommand cmd = new OleDbCommand(sql, connect))
                    {
                        if (para != null && para.Length > 0) cmd.Parameters.AddRange(para);

                        if (connect.State == System.Data.ConnectionState.Closed) connect.Open();

                        rows = cmd.ExecuteNonQuery();
                    }
                }

                if (rows > 0) Console.WriteLine("插入成功");
                Console.ReadLine();
            }
        }
    }


    专注于.NET ERP/CRM开发框架,C/S架构,SQL Server + ORM(LLBL Gen Pro) + Infragistics WinForms

    2016年12月1日 0:16
  • 你好,

    Thank you for posting in MSDN Forum.

    同样建议使用OLE DB连接数据库的方法。首先使用参数,在VB中Perematers有AddWithValue方法,它可以获取你所提供的数据并将其键入正确的类型,通过追加字符串构建SQL语句的方法可能会造成SQL注入。

    以下是我的测试的相关代码,希望对你有所帮助:

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
            Try
                Dim sqlconn As New OleDb.OleDbConnection
                Dim sqlquery As New OleDb.OleDbCommand
                Dim connString As String
                connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Documents\Database1.accdb;Persist Security Info = False;"
                sqlconn.ConnectionString = connString
                sqlquery.Connection = sqlconn
                sqlconn.Open()
                sqlquery.CommandText = "INSERT INTO tblUsers([Username], [Password])VALUES(@Username, @Password)"
                sqlquery.Parameters.AddWithValue("@Username", txtUname.Text)
                sqlquery.Parameters.AddWithValue("@Password", txtPass.Text)
                sqlquery.ExecuteNonQuery()
                sqlconn.Close()
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End Sub

    Best Regards,

    Neda Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    2016年12月1日 7:16
    版主

全部回复

  • 我觉得用OleDb的方案要好一点,参考这里 http://www.cnblogs.com/nsky/p/4488252.html

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data.OleDb; //引入命名空间 需要添加引用 System.Data.dll

    namespace ConsoleApplication2
    {
        class Program
        {
            static void Main(string[] args)
            {
                //连接Access字符串
                string conStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=userInfo.accdb;Persist Security Info=False";

                //插入数据
                string name = "张三";
                int age = 18;

                string sql = "insert into user(name,age)values(@name,@age)";
                OleDbParameter[] para = {
                                            new OleDbParameter("@name",OleDbType.VarChar),
                                            new OleDbParameter("@age",OleDbType.Integer)                                     
                                        };
                para[0].Value = name;
                para[1].Value = age;

                int rows = 0;

                using (OleDbConnection connect = new OleDbConnection(conStr))
                {
                    using (OleDbCommand cmd = new OleDbCommand(sql, connect))
                    {
                        if (para != null && para.Length > 0) cmd.Parameters.AddRange(para);

                        if (connect.State == System.Data.ConnectionState.Closed) connect.Open();

                        rows = cmd.ExecuteNonQuery();
                    }
                }

                if (rows > 0) Console.WriteLine("插入成功");
                Console.ReadLine();
            }
        }
    }


    专注于.NET ERP/CRM开发框架,C/S架构,SQL Server + ORM(LLBL Gen Pro) + Infragistics WinForms

    2016年12月1日 0:16
  • 你好,

    Thank you for posting in MSDN Forum.

    同样建议使用OLE DB连接数据库的方法。首先使用参数,在VB中Perematers有AddWithValue方法,它可以获取你所提供的数据并将其键入正确的类型,通过追加字符串构建SQL语句的方法可能会造成SQL注入。

    以下是我的测试的相关代码,希望对你有所帮助:

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
            Try
                Dim sqlconn As New OleDb.OleDbConnection
                Dim sqlquery As New OleDb.OleDbCommand
                Dim connString As String
                connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Documents\Database1.accdb;Persist Security Info = False;"
                sqlconn.ConnectionString = connString
                sqlquery.Connection = sqlconn
                sqlconn.Open()
                sqlquery.CommandText = "INSERT INTO tblUsers([Username], [Password])VALUES(@Username, @Password)"
                sqlquery.Parameters.AddWithValue("@Username", txtUname.Text)
                sqlquery.Parameters.AddWithValue("@Password", txtPass.Text)
                sqlquery.ExecuteNonQuery()
                sqlconn.Close()
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End Sub

    Best Regards,

    Neda Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    2016年12月1日 7:16
    版主
  • 谢谢两位专家的建议,如果还有问题再向诸位请教。

    谢谢

    BR,

    Han Zhengrong

    2016年12月2日 4:38