inserting splitted text to ms access?

Question

How can I insert splitted texts to my ms access database? I can't seem to find the logic in it. :( Please help me.

Here are my codes:

This is where i split the texts that are inputted in textBox1, textBox2, and textBox3

string items = textBox2.Text;
string[] splittedText1 = items.Split(' ');
string quantity = textBox1.Text;
string[] splittedText2 = quantity.Split(' ');
string price = textBox3.Text;
string[] splittedText3 = price.Split(' ');

and this is the code where I will insert those texts to my database:

OleDbCommand CmdSql = new OleDbCommand("Insert into [sales] ([productname], productquantity, productprice) VALUES (splittedText1, splittedText2, splittedText3);

is this even correct? Please help!

score 2 · Accepted Answer · 2014-03-07T13:46:37.397

2

You can execute query like this.

string[] splittedText1 = textBox2.Text.Split(' ');
string[] splittedText2 = textBox1.Text.Split(' ');
string[] splittedText3 = textBox3.Text.Split(' ');

string _sql = "Insert into [sales] ([productname], productquantity, productprice) VALUES (?,?,?)";
OleDbCommand CmdSql = new OleDbCommand();
CmdSql.Connection = Cnn;

CmdSql.Parameters.Add("@productname", OleDbType.VarChar);
CmdSql.Parameters.Add("@productquantity", OleDbType.VarChar);
CmdSql.Parameters.Add("@productprice", OleDbType.VarChar);

decimal dQty = 0m;
decimal dPrice = 0m;
for (int i = 0; i < splittedText1.Length; i++)
{
    Decimal.TryParse(splittedText2[i], out dQty);
    Decimal.TryParse(splittedText3[i], out dPrice);
    CmdSql.Parameters["@productname"].Value = splittedText1[i];
    CmdSql.Parameters["@productquantity"].Value = dQty;
    CmdSql.Parameters["@productprice"].Value = dPrice;
    CmdSql.ExecuteNonQuery();
}

edited Mar 07 '14 at 13:46

answered Mar 07 '14 at 11:52

1

SQL injection, anyone? How about a parameterized one? – DonBoitnott Mar 07 '14 at 11:55
Shahrooz has already given this [Solution](http://stackoverflow.com/a/21793152/3391751) before. – Mar 07 '14 at 12:01
Then mark this question a duplicate, provide that address, and do not answer it. If you _do_ choose to answer it, then answer it properly. – DonBoitnott Mar 07 '14 at 12:02
I can't @DonBoitnott. Why don't you mark this question as duplicate? – Mar 07 '14 at 12:16
hi @Winky I've tried your answer, but it has an error and it highlights the CmdSql.ExecuteNonQuery(); part. It says: An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll Additional information: Data type mismatch in criteria expression. IDK where the mismatch occurs. – chenpai Mar 07 '14 at 12:31
What is the datatype of all of three fields? – Mar 07 '14 at 12:32
oh wait, the datatypes are text, number, and number according to ms access. so, I need to change the varchars? I've tried the OleDbType.Text but it has no syntax like that. I'm using ms-access 2007. @Winky – chenpai Mar 07 '14 at 12:41
1

You have to convert both value into decimal @chenpai. I have updated my answer please check it. – Mar 07 '14 at 13:35
@Winky it says: **Input string was not in a correct format.** :/ – chenpai Mar 07 '14 at 13:42
Make sure the values are not blank. Because, blank value or null value cannot be converted into decimal. If there is extra space in the value then remove it by unsing Trim() function. – Mar 07 '14 at 13:44
it's like, one error after another. Here's a new one: **Index was outside the bounds of the array** IDK. Are you willing to help me until this will be solved? @Winky – chenpai Mar 07 '14 at 14:05
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/49247/discussion-between-winky-and-chenpai) – Mar 07 '14 at 14:36

score 0 · Answer 2 · edited May 23 '17 at 12:03

0

Splitting gives you an array and thus you either need to write insert statement for each element of array or use something like this:

edited May 23 '17 at 12:03

Community

1
1

answered Mar 07 '14 at 10:36

Usman Waheed

555
5
14

inserting splitted text to ms access?

2 Answers2