Insert into VALUES() question

  • Thread starter Thread starter Roy Gourgi
  • Start date Start date
R

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
 
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...
 
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
 
Robbe Morris said:
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...
Click to expand...

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.
 
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
Roy Gourgi said:
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
Click to expand...
 
Roy said:
I am trying to use parameters as you suggested but I am getting error
messages. What am I doing wrong in this code below?
Click to expand...

<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
 
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();


}

}
 
Roy Gourgi said:
Here is my code. I get the error message now that it expects a ")".
Click to expand...

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.
Click to expand...

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!
Click to expand...

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?
 
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();
}
}
 
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
 
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.
Click to expand...

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.
Click to expand...

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?
Click to expand...

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
 
Roy Gourgi said:
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.
:)
Click to expand...

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.
Click to expand...

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.
Click to expand...

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?
 
Back
Top