Insertion of data in MS Access table. 2

[PavelGur]

I'm new to C# coming from C++ 6.0. I need to insert data into MS Access table. I was able to read data from table with Reader. However when I try to insert simple one item I've got from one table into another table it runs w/o errors but data is not in the table.
Here is my code:

private static string szItem;
public static void Main()
{
    OleDbConnection aConnection = new 			    OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\MyStuff\\Money\\New Market\\NSTXP.mdb");
    OleDbDataAdapter dAdapter = new OleDbDataAdapter();

    OleDbCommand aCommand = new 
       OleDbCommand("select Stock from STGEN", aConnection);
       OleDbCommand bCommand = new 
   OleDbCommand("select Symbol from Symbols", aConnection); 
try
    {
        aConnection.Open();
        OleDbDataReader aReader = aCommand.ExecuteReader();
        while(aReader.Read())
	{
	    szItem = aReader.GetString(0);
            bCommand = new
              OleDbCommand("INSERT INTO Symbols (Symbol) " +
                        "VALUES (@value)", aConnection);
            bCommand.Parameters.Add("@value",  OleDbType.Char, 5, szItem);
            dAdapter.InsertCommand = bCommand;
         }
         aReader.Close();

	 aConnection.Close();
   }

Thank you for any help, Pavel.

 

[AlexCuse]

You are trying to instantiate bCommand twice. If your second try wasn't wrapped in a try, I think it would error on you.

Try getting rid of this line (I can't see the use for it)

OleDbCommand bCommand = new 
   OleDbCommand("select Symbol from Symbols", aConnection);

You'll also need to set bCommand to null before you return to the top of your loop (or instantiate outside the loop and alter command text, or whatever).

Hope this helps,

Alex

[small]----signature below----[/small]
Now you can go where the people are one!
Now you can go where they get things done!

 

[AlexCuse]

I suppose I should add that if you're using parameters, ideally you'd create the command outside your loop, and just reset the parameter value each time you execute.

And finally, you need a bCommand.Execute() or bCommand.ExecuteNonQuery() to actually execute the command and insert the row (don't know how I missed this the first time through ). I don't know how to do this with the DataAdapter's insert command, but it shouldn't be too hard (and to tell you the truth I don't think you need to use the data adapter, command.execute or executenonquery should work fine).

Hope this helps,

Alex

[small]----signature below----[/small]
Now you can go where the people are one!
Now you can go where they get things done!

 

[PavelGur]

Alex, thank you very much for quick response. I've just tried to follow your advice. I was executing bCommand.ExecuteNonQuery() (bCommand.Execute was not in intellisense list) and I've got the exception message:
e.Errors[0].Message "Parameter @value has no default value." string.
Sorry, but I do not undrstand what it means.
Also, should I get rid of statement
dAdapter.InsertCommand = bCommand;?
I also tried dAdapter.InsertCommand.ExecuteNonQuery() and got the same error.
Thanks again, Pavel.

 

[AlexCuse]

I don't use parameters normally, but I think something like this would work?

//set parameter's value after adding it
bCommand.Parameters.Add("@value",  OleDbType.Char, 5).Value = szItem;

It looks like you were trying to set your parameter value in the constructor, which I am not sure is allowed or not. I would stick with ExecuteNonQuery(), rather than using the DataAdapter's insert command. Let me know if this works for you or not.

Hope this helps,

Alex



[small]----signature below----[/small]
Now you can go where the people are one!
Now you can go where they get things done!

 

[PavelGur]

Thanks Alex,
you gave me some ideas. Actually I do not want to use parameters as well. So I tried the following:

 bCommand = new 
    OleDbCommand("INSERT INTO Symbols (Symbol) " +
                "VALUES (szItem)", aConnection);
    bCommand.ExecuteNonQuery();

trying to use regular SQL syntax. However I've got just slightly more meaningful error:
e.Errors[0].Message "No value given for one or more required parameters." string
I understand C# does not know how to extract text value from String. I tried to find something like "GetBuffer" I used in C++ for CString but was unsuccessful. Unfortunatelly intellisense does not show anything helpful for String.
If you know how I can extract value or other syntax to get it please advise.
Thanks again, Pavel.

 

[AlexCuse]

This is an easy one (see part in bold/green for what you missed).

OleDbCommand("INSERT INTO Symbols (Symbol) " +
                "VALUES ([b][COLOR=green]'" + szItem + "'[/color][/b] )", aConnection);

You need to remember, as far as your database is concerned, szItem does not exist. THis will wrap szItem's value in single quotes and add it to your query. So if szItem's value is "ALEX", your command's text will be:

INSERT INTO Symbols (Symbol)
VALUES ('ALEX')

Makes sense?

Hope this helps,

Alex

[small]----signature below----[/small]
Now you can go where the people are one!
Now you can go where they get things done!

 

[PavelGur]

Alex, you are great. It works though as far as I concerned it is very wierd syntax. I'm still trying to understand how Parameters.Add works. It may be with the same set of quotes.
Now I will try to do it with data I receive over Internet.
Thank you very much, Pavel.

 

[AlexCuse]

Do you mean weird syntax for adding the parameters, or what I just posted? The actual query sent to the database for your first try was this:

INSERT INTO Symbols (Symbol) 
VALUES (szItem)

Which was causing Access to expect a parameter called szItem. What you want in place of szItem is szItem's value, wrapped in single quotes, so you need to build it into your string.

I'm glad you got it working, and feel free to post back if you have any other questions. This stuff is important to understand

Alex


[small]----signature below----[/small]
Now you can go where the people are one!
Now you can go where they get things done!

 

[PavelGur]

Both! I do not understand why I need extra single quotes. As to Parameters.Add I do not know where is actual value and where is Value keyword. In all examples I've seen so far they have the same code, like "Customer". Also I did not find in MS documentation exact syntax description of this statement.
Thanks again, Pavel.
BTW what is the purpose of adapter? It seems that I can do w/o it.

 

[AlexCuse]

I believe you need the adapter for your reader. You can also use it to fill data tables and such. You don't need it to execute commands though.

As far as the single quotes, that is a SQL thing. If it is not in single quotes, access will think that you mean a column (if you are selecting from a table) or a parameter if you are not. So while you can have @szItem in your query text with no single quotes (if it is a parameter) if you build the string on your own you need to put the single quotes around it.

As far as the parameters are concerned, I am not all that familiar with the overloads of the Parameters.Add method, but I think there used to be one that allowed you to supply a value. In ADO.net 2.0 this has been deprecated, but there is a Parameters.AddWithValue method.

Or as shown, you can add the parameter and set its' value immediately after.

Here is the info on the ParameterCOllection (which OledbCommand.Parameters is an example of):

http://msdn2.microsoft.com/en-us/li....oledbparametercollection_members(VS.80).aspx

Pay particular attention to the Add and AddWithValue methods.

As you can see, MSDN2 is a valuable resource. Know it. Love it

Starting to clear things up?

Hope this helps,

Alex

[small]----signature below----[/small]
Now you can go where the people are one!
Now you can go where they get things done!

 

[phinoppix]

INSERT INTO Symbols (Symbol)
VALUES (szItem)

On this SQL statement, szItem is expected to be a table column. On MS Access, if i remember correctly, if a column does not belong to any table in the current context, it is translated as a parameter. Parameters on Access are a bit different with MS-SQL. On Access, a named parameter can have a default value, which you set up in the MS Access environment. If you run this query in Access, you should get a dialog box asking for a parameter value. If you don't supply a value, it uses the configured default value. (If no default value is set, I cannot remember exactly if an error report is displayed, or if Access sets NULL for the parameter and continues)

I think this is what's happening to you now. Even if you use '@'-placeholders, you still get a similar error. Try using a '?' placeholder instead.

INSERT INTO Symbols (Symbol)
VALUES (?)

When you initialize the OleDbCommand object with this query, the order of Parameter objects when you fill the Parameters collection property is important and should correspond with the order of '?'s.

I would still recommend using parameters, because firstly, it safeguards you against SQL injection, and secondly, you don't need to bother with enclosing quotes. Here's a sample I found:

string SqlString = "Insert Into Contacts (FirstName, LastName) Values (?,?)";
using (OleDbConnection conn = new OleDbConnection(ConnString ))
{	
  using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))
  {
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text);
    cmd.Parameters.AddWithValue("LastName", txtLastName.Text);
    conn.Open();
    cmd.ExecuteNonQuery();
    conn.Close();
   }
}

Hope this helps

 

[PavelGur]

Thank you for this info. I was able to resolve my problem with Alex's advice. However your info helps to understand better DB interface C# commands. I will try them soon.
Thanks again, Pavel.