Insert into VALUES() question

[Roy Gourgi]

Hi,

How can I insert variables instead of values in the VALUES() part of the
insert into command, i.e.

var1=10;
var2=20;
var3=30

insert into emp_test (emp_no1, emp_no2, emp_no3) VALUES (var1, var2, var3)";

The above does not work.

TIA
Roy

 

[Robbe Morris [C# MVP]]

var1=10;
var2=20;
var3=30

"insert into emp_test (emp_no1, emp_no2, emp_no3) VALUES (" + var1 + "," +
var2 + "," + var3 + ")";

dynamic sql like this is wide open to sql injection attacks. Be careful...

 

[Roy Gourgi]

Hi Jon,

I am trying to use parameters as you suggested but I am getting error
messages. What am I doing wrong in this code below?

using System;

using System.Data;

using System.Data.OleDb;

using System.Xml.Serialization;

public class MainClass

{

public static void Main ()

{

test.xxx();


}

}



public class test

{

public static void xxx()

{

int var1=10, var2=20, var3=30;

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=D:\\dbAccess\\db1.mdb";

OleDbConnection myConnection = new OleDbConnection( connectionString );

myConnection.Open();

OleDbCommand myCommand = new OleDbCommand("insert into emp_test (emp_code,
emp_name, emp_ext) VALUES (@var1, @var2 ,@var3)", myConnection);

myCommand.Parameters.Add(New SqlParameter("@var1", _SqlDbType.VarChar, 50));

myCommand.Parameters("@var1").Value = var1.Value;

myCommand.Parameters.Add(New SqlParameter("@var2", _SqlDbType.VarChar, 50));

myCommand.Parameters("@var2").Value = var2.Value;

myCommand.Parameters.Add(New SqlParameter("@var1", _SqlDbType.VarChar, 50));

myCommand.Parameters("@var3").Value = var3.Value;

myCommand.ExecuteNonQuery();

myConnection.Close();


}

}

Thanks

Roy

 

[Jon Skeet [C# MVP]]

var1=10;
var2=20;
var3=30

"insert into emp_test (emp_no1, emp_no2, emp_no3) VALUES (" + var1 + "," +
var2 + "," + var3 + ")";

dynamic sql like this is wide open to sql injection attacks. Be careful...

Which is why you should use SQL parameters instead. If you put the SQL
parameter names into the VALUES clause, then set the parameters
themselves, you don't need to include any user-provided data in the SQL
statement.

 

[Ron Allen]

Roy,
For the Jet engine you need to use ? as the parameter placeholder. You
must declare the parameters in the colledtion in the order that they appear
in the SQL text as well. The @parm syntax only works for SQL Server.

Ron Allen

Hi Jon,

I am trying to use parameters as you suggested but I am getting error
messages. What am I doing wrong in this code below?

using System;

using System.Data;

using System.Data.OleDb;

using System.Xml.Serialization;

public class MainClass

{

public static void Main ()

{

test.xxx();


}

}



public class test

{

public static void xxx()

{

int var1=10, var2=20, var3=30;

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=D:\\dbAccess\\db1.mdb";

OleDbConnection myConnection = new OleDbConnection( connectionString );

myConnection.Open();

OleDbCommand myCommand = new OleDbCommand("insert into emp_test (emp_code,
emp_name, emp_ext) VALUES (@var1, @var2 ,@var3)", myConnection);

myCommand.Parameters.Add(New SqlParameter("@var1", _SqlDbType.VarChar,
50));

myCommand.Parameters("@var1").Value = var1.Value;

myCommand.Parameters.Add(New SqlParameter("@var2", _SqlDbType.VarChar,
50));

myCommand.Parameters("@var2").Value = var2.Value;

myCommand.Parameters.Add(New SqlParameter("@var1", _SqlDbType.VarChar,
50));

myCommand.Parameters("@var3").Value = var3.Value;

myCommand.ExecuteNonQuery();

myConnection.Close();


}

}

Thanks

Roy

 

[Jon Skeet [C# MVP]]

I am trying to use parameters as you suggested but I am getting error
messages. What am I doing wrong in this code below?

<snip>

Hopefully Ron has answered your question - but in future if you're
getting error messages, *please* tell us what they are and exactly
where you're getting them. It cuts down on a lot of back-and-forth.

Jon

 

[Roy Gourgi]

Hi Jon,

Here is my code. I get the error message now that it expects a ")". I have
tried a few different things, but to no avail. What am I doing wrong???? I
can't believe that it is so difficult to add a record in a database. In
Visual Foxpro it's done in one statement. I have tried to install MSDE but
with no luck and that is why I am trying Access. I hope that VS.Net 2005
with SQL express will be easier. This has truly been a nightmare!

Roy


using System;

using System.Data;

using System.Data.OleDb;

using System.Xml.Serialization;

public class MainClass

{

public static void Main ()

{

test.xxx();


}

}



public class test

{

public static void xxx()

{

int lnvar1=10, lnvar2=20, lnvar3=30;

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=D:\\dbAccess\\db1.mdb";

OleDbConnection myConnection = new OleDbConnection( connectionString );

myConnection.Open();

OleDbCommand myCommand = new OleDbCommand("insert into emp_test (emp_code,
emp_name, emp_ext) VALUES (?, ?,?)", myConnection);

myCommand.Parameters.Add(New SqlCeParameter("emp_code", SqlDbType.NText,
50));

myCommand.Parameters.Add(New SqlCeParameter("emp_name", SqlDbType.NText,
50));

myCommand.Parameters.Add(New SqlCeParameter("emp_ext", SqlDbType.NText,
50));

myCommand.Parameters("emp_code").Value = lnvar1;

myCommand.Parameters("emp_name").Value = lnvar2;

myCommand.Parameters("emp_ext").Value = lnvar3;


myCommand.ExecuteNonQuery();

myConnection.Close();


}

}

 

[Jon Skeet [C# MVP]]

Here is my code. I get the error message now that it expects a ")".

You've mistyped "new" as "New". C# is case-sensitive.

Now, why are you trying to use SqlCeParameters for an OleDbCommand? You
should be using OleDbParameters.

I have tried a few different things, but to no avail. What am I doing
wrong???? I can't believe that it is so difficult to add a record in a
database. In Visual Foxpro it's done in one statement.

Once you've got the command set up, it's nice and easy - most of what
you've written is a "one time hit". As I understand it, FoxPro is
particularly aimed at databases, so it's not surprising that it would
be better at this.

I have tried to
install MSDE but with no luck and that is why I am trying Access. I hope that
VS.Net 2005 with SQL express will be easier. This has truly been a nightmare!

Installing MSDE shouldn't be a problem - I've never seen it fail
myself, and the product I work on installs it automatically as a pre-
requisite. What error are you getting?

 

[Mel Weaver]

Try this

using (OleDbConnection conn = new OleDbConnection())
{
conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\temp\new.mdb;User Id=admin;Password=;";
conn.Open();
using (OleDbCommand cmd = new OleDbCommand("Insert into Test
(Name,Age) values (?,?)",conn))
{
cmd.Parameters.Add("Name", OleDbType.Char).Value = "Mel";
cmd.Parameters.Add("Age", OleDbType.Numeric).Value = 50;
cmd.ExecuteNonQuery();
}
}

 

[Roy Gourgi]

GOD BLESS YOU, MEL.

Just to let you know though that your code did not work exactly as you had
it as I got an OleDb exception on the

using (OleDbCommand cmd = new OleDbCommand("Insert into Test
(Name,Age) values (?,?)",conn))

Nonetheless, I took the parameters section of your code and embeded it into
my code and it worked like a charm.

Thanks Mel, I really appreciate it.

Roy

 

[Roy Gourgi]

Hi Jon,

You've mistyped "new" as "New". C# is case-sensitive.

Now, why are you trying to use SqlCeParameters for an OleDbCommand? You
should be using OleDbParameters.

I just took it out of an example that I found on the net and tried it. At
this point I was willing to try anything because nothing else was working.

Once you've got the command set up, it's nice and easy - most of what
you've written is a "one time hit". As I understand it, FoxPro is
particularly aimed at databases, so it's not surprising that it would
be better at this.

True, but there is no reason why it should be so difficult to find an
answer. I have searched all over the net but to no avail. Finally, I tried
what Mel suggested in his post and although it did not work the way that he
said it, I copied the portion with respect to the parameter section and
embedded it into my program and it worked.

Installing MSDE shouldn't be a problem - I've never seen it fail
myself, and the product I work on installs it automatically as a pre-
requisite. What error are you getting?

I am not getting an error per se, but I do not think that it installed
properly. Well I must say that I really do not know what I am doing, so
maybe I am doing something wrong. But when I start the setup.exe program
from the command line, it just takes a few minutes to set up and it does not
ask me any questions and it says that it sucessfully installed the software.

Roy

 

[Jon Skeet [C# MVP]]

I just took it out of an example that I found on the net and tried it. At
this point I was willing to try anything because nothing else was working.

It's often worth taking a big breath and a step back at that stage.
It's worth rereading the articles you've already read, trying to
understand the code that's being presented to you, looking at the
documentation for the snippets that you've got, etc. I'm sure you could
have figured out quite a few of the problems that way.

True, but there is no reason why it should be so difficult to find an
answer. I have searched all over the net but to no avail. Finally, I tried
what Mel suggested in his post and although it did not work the way that he
said it, I copied the portion with respect to the parameter section and
embedded it into my program and it worked.

With respect, I don't think it actually *is* that hard to find an
answer. A huge number of people manage this without any problem.

I am not getting an error per se, but I do not think that it installed
properly. Well I must say that I really do not know what I am doing, so
maybe I am doing something wrong. But when I start the setup.exe program
from the command line, it just takes a few minutes to set up and it does not
ask me any questions and it says that it sucessfully installed the software.

As MSDE is meant to effectively be embedded into other applications,
that's probably what *should* happen.

Do you have any processes running which look they might be a SQL
server?