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

Question

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

Answer 1

我觉得用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

Answer 2

你好，

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 [e-mail].

Answer 3

谢谢两位专家的建议，如果还有问题再向诸位请教。

谢谢

BR,

Han Zhengrong