CODEWITHSUNDEEP
c#sql-server
dsanatomy
dsanatomy

Reputation: 533

Adding a record to the database using C#

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace lab1
{
    public partial class Form1 : Form
{

    DataSet ds = new DataSet(); //holding place in memory
    SqlDataAdapter daParent = new SqlDataAdapter();
    SqlDataAdapter daChild = new SqlDataAdapter();
    SqlConnection cs = new SqlConnection("Data Source=user-PC\\SQLEXPRESS; Initial Catalog=dede;Integrated Security=TRUE");

    BindingSource ParentBS = new BindingSource();
    BindingSource ChildBS = new BindingSource();

    public Form1()
    {
        InitializeComponent();
    }

    private void Form1_Load(object sender, EventArgs e)
    {
        SqlCommand slctParent = new SqlCommand("select * from Developerss", cs); //select statement
        daParent.SelectCommand = slctParent; // attaching sql command into the select command property of the data adapter Parent
        daParent.Fill(ds, "Developerss"); //fullfilling the data set, calling the table Parent

        SqlCommand slctChild = new SqlCommand("select * from Games", cs);
        daParent.SelectCommand = slctChild; 
        daParent.Fill(ds,"Games");

        dgParent.DataSource = ds.Tables["Developerss"];
        dgChild.DataSource = ds.Tables["Games"];
        dgParent.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
        dgChild.SelectionMode = DataGridViewSelectionMode.FullRowSelect;//click anywhere in the table we have the whole row selected

        ParentBS.DataSource = ds.Tables[0];
        ChildBS.DataSource = ds.Tables[1];

        textId.DataBindings.Add(new Binding("Text",ChildBS,"game_id"));
        textName.DataBindings.Add(new Binding("Text", ChildBS, "game_name"));
        textPlatform.DataBindings.Add(new Binding("Text", ChildBS, "game_platform"));
        textDeveloperId.DataBindings.Add(new Binding("Text", ParentBS, "d_id"));


    }

    private void dgParent_SelectionChanged(object sender, EventArgs e)
    {
        ds.Tables["Games"].DefaultView.RowFilter = "developer_id = " + dgParent.CurrentRow.Cells["d_id"].Value;
        //whenever the selection change on the dgparent use the value of the current row that we're on
        dgChild.DataSource = ds.Tables["Games"];
    }

    private void addBtn_Click(object sender, EventArgs e)
    {
        daChild.InsertCommand = new SqlCommand("insert into Games values @id, @name, @platform, @developerId", cs);
        daChild.InsertCommand.Parameters.Add("@id", SqlDbType.Int).Value = textId.Text;
        daChild.InsertCommand.Parameters.Add("@name", SqlDbType.VarChar).Value = textName.Text;
        daChild.InsertCommand.Parameters.Add("@platform", SqlDbType.VarChar).Value = textPlatform.Text;
        daChild.InsertCommand.Parameters.Add("@developerId", SqlDbType.Int).Value = ds.Tables[0].Rows[ParentBS.Position][0];

        cs.Open();
        daChild.InsertCommand.ExecuteNonQuery();
        cs.Close();

    }

Hello there!

I have a problem with the insert command. I get the following error :

Incorrect syntax near 'game_id'.

Yes the column exists in the database. I get the same error for my update function but I have not posted it here since i believe it must come from the same error source. I have 2 data grids, one is parent, the other child. Basically i want to add new records in the child table.

Thank you in advance !

Upvotes: 1

Views: 109

Answers (1)

marc_s
marc_s

Reputation: 754298

Your INSERT syntax is totally wrong - you should use:

INSERT INTO dbo.Games(Co1, Col2, ..., ColN)
VALUES (@id, @name, @platform, @developerId)

First of all: I recommend to always explicitly specify the list of columns you want to insert into.

And secondly: the VALUES need to have parenthesis around the list of values it will insert.

And for a next time - please look up the excellent, freely available SQL Server documentation first! Totally basic stuff like this can be easily found online - e.g. the details about the INSERT syntax can be found here on Technet

That documentation is very thorough, it shows all the details, all the options, all the various ways of using it, and it has plenty of sample code, too - please USE that documentation before asking such basic questions here (using documentation and looking up stuff is part of being a developer, after all!) - thank you.

Upvotes: 2

Related Questions