• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 197
  • Last Modified:

DB Insert

I am wanting to insert dynamic data into the database.  In Java I could use a PreparedStatement so I could assign the variable values after the statement.  Is there a way to do this in C#?
0
vcgDevelopers
Asked:
vcgDevelopers
  • 6
  • 5
1 Solution
 
WebSpecialsCommented:
yes there is

For example MsSQL:

SqlConneciton con=new SqlConnection(connectionString);

SqlCommand cmd=new SqlCommand("INSERT INTO taTable(id,name) VALUES(@id,@number)",con)
cmd.Parameters.Add("@id",SqlType.Integer).Direction=ParameterDirection.Input;
cmd.Parameters.Add("@number",SqlType.Integer).Direction=ParameterDirection.Input;

cmd.Parameters[0].Value=12;
cmd.Parameters[0].Value=5;

con.Open();
cmd.ExecuteNonQuery();
con.Close();

mfg WebSpecials
0
 
vcgDevelopersAuthor Commented:
I'm getting an error on:

Sqltype

and ParameterDirection

Says namespace cannot be found
0
 
WebSpecialsCommented:
k its SqlDbType
and

and you should use a using directive

using System;
using System.Data;
using System.Data.Sql;

mfg WebSpecial
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
vcgDevelopersAuthor Commented:
that didnt seem to fix the problem
0
 
WebSpecialsCommented:
OK.. here is the code. ive made some mistakes (i typed it in the little answer window ;-) )

using System;
using System.Data;
using System.Data.SqlClient;

public class DataTest
{
     public static void Main()
    {
    string connectionString="";
    SqlConnection con=new SqlConnection(connectionString);

    SqlCommand cmd=new SqlCommand("INSERT INTO taTable(id,name) VALUES(@id,@number)",con);
    cmd.Parameters.Add("@id",SqlDbType.Int).Direction=ParameterDirection.Input;
    cmd.Parameters.Add("@number",SqlDbType.Int).Direction=ParameterDirection.Input;

    cmd.Parameters[0].Value=12;
    cmd.Parameters[0].Value=5;

    con.Open();
    cmd.ExecuteNonQuery();
    con.Close();
   }
}

Ive tested it. You need to set the connection string according to your database.
i hope this solve your problem.

mfg WebSpecials
0
 
vcgDevelopersAuthor Commented:
getting a SQL error - anything jump out?

try
{
SqlCommand cmd=new SqlCommand("INSERT INTO Answers (SiteName, Date, FloorNumber, Direction, Location1, Location2, LocNotes, Contractor, SubContractor, Category, Type, Question, Answer, Repeated, NotAbated) VALUES (@Site, @Date, @FloorNumber, @Direction, @Location1, @Location2, @LocNotes, @Contractor, @SubContractor, @Category, @Type, @Question, @Answer, @Repeat, @NotAbated)",cn);

cmd.Parameters.Add("@Site", SqlDbType.Text).Direction=ParameterDirection.Input;
cmd.Parameters.Add("@Date", SqlDbType.Text).Direction=ParameterDirection.Input;
cmd.Parameters.Add("@FloorNumber", SqlDbType.Text).Direction=ParameterDirection.Input;
cmd.Parameters.Add("@Direction", SqlDbType.Text).Direction=ParameterDirection.Input;
cmd.Parameters.Add("@Location1", SqlDbType.Text).Direction=ParameterDirection.Input;
cmd.Parameters.Add("@Location2", SqlDbType.Text).Direction=ParameterDirection.Input;
cmd.Parameters.Add("@LocNotes", SqlDbType.Text).Direction=ParameterDirection.Input;
cmd.Parameters.Add("@Contractor", SqlDbType.Text).Direction=ParameterDirection.Input;
cmd.Parameters.Add("@SubContractor", SqlDbType.Text).Direction=ParameterDirection.Input;
cmd.Parameters.Add("@Category", SqlDbType.Text).Direction=ParameterDirection.Input;
cmd.Parameters.Add("@Type", SqlDbType.Text).Direction=ParameterDirection.Input;
cmd.Parameters.Add("@Question", SqlDbType.Text).Direction=ParameterDirection.Input;
cmd.Parameters.Add("@Answer", SqlDbType.Text).Direction=ParameterDirection.Input;
cmd.Parameters.Add("@Repeat", SqlDbType.Text).Direction=ParameterDirection.Input;
cmd.Parameters.Add("@NotAbated", SqlDbType.Text).Direction=ParameterDirection.Input;

cmd.Parameters[0].Value= this.ComboBoxValueSite;
cmd.Parameters[1].Value = this.current;
cmd.Parameters[2].Value = this.ComboBoxValueFloor;
cmd.Parameters[3].Value = this.ComboBoxValueDirection;
cmd.Parameters[4].Value = this.ComboBoxValueLoc1;
cmd.Parameters[5].Value = this.ComboBoxValueLoc2;
cmd.Parameters[6].Value = this.ComboBoxValueLocNotes;
cmd.Parameters[7].Value = this.ComboBoxValueContractor;
cmd.Parameters[8].Value = this.ComboBoxValueSubcontractor;
cmd.Parameters[9].Value = this.ComboBoxValueCategory;
cmd.Parameters[10].Value = this.ComboBoxValueType;
cmd.Parameters[11].Value = this.questions.Text;
cmd.Parameters[12].Value = "NO";
cmd.Parameters[13].Value = "No";
cmd.Parameters[14].Value = "No";

                              cmd.ExecuteNonQuery();
                        }

                        catch (Exception excp)
                        {
                              MessageBox.Show("Exception: " + excp.Message);
                              MessageBox.Show("Base Exception: " +
                                    excp.GetBaseException().Message);
                        }
                  }
0
 
WebSpecialsCommented:
hmm.. you i think there is nothing wrong so far...

can you send me the error message, mabey it says more..

mfg WebSpecials
0
 
vcgDevelopersAuthor Commented:
Application or object defined error.

DB Structure
Column Name       Data Type     Length
SiteName              nvchar           255
0
 
WebSpecialsCommented:
hmm... ok thats not a C# Problem anymore. I dont know much about Sql Database (i like Interbase), but i know how to access from .NET plattform to a database.
So your question was how to access.
I cant answer you why there is an error, but if you want to know more about SQL post another Question in SQL Topic

mfg WebSpecials
0
 
vcgDevelopersAuthor Commented:
SqlCommand cmd=new SqlCommand("INSERT INTO Answers(test) VALUES (@test)",cn);

cmd.Parameters.Add("@test", SqlDbType.Int).Direction=ParameterDirection.Input;

cmd.Parameters[0].Value = 14;

it gets to the cmd.Parameters[0].Value=14; and throws a SQL Exception

Test is a column in the Answers table as an int
0
 
vcgDevelopersAuthor Commented:
SqlCommand cmd = cn.CreateCommand();

cmd.CommandText = "INSERT INTO Answers(SiteName) VALUES (?)";
//cmd.Parameters.Add("@SiteName", "CHAD");
cmd.Parameters.Add("@SiteName", SqlDbType.NVarChar, 255, "Chad");
//cmd.Prepare();
cmd.ExecuteNonQuery();


this code was suggested from a C# book.  I still get a SQL Exception.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now