Database

1-Aug-06 6:44

Hi, i'm a newby in database in c#.

What I want to do is add a new row to a microsoft access database. I dont use any tables or anything. I just want to get values from labels or whatever and then use that as values for my "Insert into.." query.

I'm using the dataset designer, where I can see my tables and the corresponding tableadapters that were created.

When I test the query on the QUERY CONFIGURATION WIZARD (using fake values), it works fine (i checked de mdb and it added the row with the fake values). What i want to do though is use the same query, but with the corresponding values from my FORM. So it would be like this:

{
INSERT INTO archivo (archivo, [ruta archivo], formato, tamaño, muestreo, bitrate, canales, duracionSeg, [tipo de audio])
VALUES (file, path, format, size, sampling, rate, channels, lenght, audiotype)
}

where (file, path, size, sampling, rate, channels, lenght & audiotype) are my variables that i get from the form. I called the query InsertarAudio, on archivoTableAdapter1.

is there any way i can do this using the wizard and put in the query my external values, so i can then use something like this:

try
{
archivoTableAdapter1.InsertarAudio();
MessageBox.Show("Update successful");
}
catch (System.Exception ex)
{
MessageBox.Show("Update failed");
}

-----------------

I also tried to use:

radioDataSet.AcceptChanges();
try
{
archivoTableAdapter1.InsertarAudio(Archivo, path, auxext, auxsize, auxmuestreo, auxbitrate, auxcanales, auxduracion, cbTipoAudio.Text);
MessageBox.Show("Update successful");
}
catch (System.Exception ex)
{
MessageBox.Show("Update failed");
}

here, where i use directly my variables, i was able to run the project, and the code worked fine (no errors), but it didn't update the mdb.

Thanks to everyone for the time!!!
Hope someone can help me out
Wink | ;)

daniel sovino

Stephen McGuire2-Aug-06 11:11

2-Aug-06 11:11

You will probably need to use an OleDbCommand object.
Set the Command.Text property and other properties and then execute the command.

Something like this:
You need to create your CommandObject and ConnectionObject first...

CommandObject = New OleDbCommand( _
"INSERT INTO archivo (archivo, [ruta archivo], formato, tamaño, muestreo, bitrate, canales, duracionSeg, [tipo de audio]) " & _
"VALUES (" & file &", " & path &", " & format &", " & size &", " & sampling &", " & rate &", " & channels &", " & lenght &", " & audiotype &")", ConnectionObject)
'Open connection
CommandObject.ExecuteNonQuery()
'Close connection

Example is VB.NET but it is similar in C#

Steve

dsovino3-Aug-06 12:41

3-Aug-06 12:41

thanks steve

i tried to use your code, but i keep getting some errors.
"Format of the initialization string does not conform to specification starting at index 92"

on index 92 (line 92??) i have this:
double auxmuestreo = Convert.ToDouble(muestreo);

i think the problem is that im not specifing the type of the variabes. When i made the tables on access, some of the variables are strings, some are doubles

i'm using this.

string source = "Provider=Microsoft.JET.OLEDB.4.0;" + "data source=D:\\tesis\\Importar Archivo\\Importando\\Importando\bin\\radio.mdb";
OleDbCommand comando = new OleDbCommand();
OleDbConnection conexion = new OleDbConnection(source);

string auxsql = "INSERT INTO archivo (archivo, [ruta archivo], formato, tamaño, muestreo, bitrate, canales, duracionSeg, [tipo de audio]) " + "VALUES (" + Archivo + ", " + path + ", desconocido" + tamano.ToString() + ", " + muestreo + ", " + bitrate + ", " + canales + ", " + duracionms + ", " + cbTipoAudio.Text + ")";

comando =new OleDbCommand(auxsql,conexion);
conexion.Open();
comando.ExecuteNonQuery();
conexion.Close();

Once again, thanks a lot. im really stuck on this...

daniel

Stephen McGuire3-Aug-06 14:23

3-Aug-06 14:23

kiweed wrote:
string source = "Provider=Microsoft.JET.OLEDB.4.0;" + "data source=D:\\tesis\\Importar Archivo\\Importando\\Importando\bin\\radio.mdb";

The above is incorrect. You have the last double quote in the wrong place. Try this:

string source = "Provider=Microsoft.JET.OLEDB.4.0;" + "data source=D:\\tesis\\Importar Archivo\\Importando\\Importando\bin\\radio.mdb;"

Do you need the '\\' double slashes? I don't think so? If not, try this:

string source = "Provider=Microsoft.JET.OLEDB.4.0;" + "data source=D:\tesis\Importar Archivo\Importando\Importando\bin\radio.mdb;"

VS doesn't always give the right index when reporting errors like this. Sometimes it is on the previous or next line...or even on another line!

Steve

dsovino7-Aug-06 6:16

7-Aug-06 6:16

yep, that made it work (the connection)

I still have an error when i use an INSERT on the query.

Just to make sure, i used the OleDbDataReader to make sure i was able to read the database and the connection was working. Everything ok with this:

{
OleDbConnection conexion = new OleDbConnection(source);
OleDbCommand comando = new OleDbCommand();

string auxsql = "SELECT Id, archivo, [ruta archivo], formato, tamaño, muestreo, bitrate, canales,
duracionSeg, [tipo de audio] FROM archivo";

comando =new OleDbCommand(auxsql,conexion);
conexion.Open();

OleDbDataReader reader = comando.ExecuteReader();
while (reader.Read())
{ txtSQL.Text += (reader.GetInt32(0) + ", " + reader.GetString(1)); }

reader.Close();
}

Now, when i use the code down below, i get an OledbException, "No value given for one or more required parameters."

the CommandText is:

INSERT INTO archivo (archivo, [ruta archivo], formato, tamaño, muestreo, bitrate, canales, duracionSeg, [tipo de audio]) VALUES (07-sublime-badfish-chupa.mp3, path , desconocido,4438226, 44100, 187, 2, 184839, Musica)

so i don't get where the error might be at.

{
OleDbConnection conexion = new OleDbConnection(source);
OleDbCommand comando = new OleDbCommand();

string auxsql = "INSERT INTO archivo (archivo, [ruta archivo], formato, tamaño, muestreo, bitrate,
canales, duracionSeg, [tipo de audio]) ";

auxsql += "VALUES (" + Archivo + ", " + "path "+ ", desconocido," + tamano.ToString() + ", " +
muestreo + ", " + auxbitrate + ", " + canales + ", " + duracionms + ", " + cbTipoAudio.Text + ")";

comando =new OleDbCommand(auxsql,conexion);
conexion.Open();

comando.ExecuteNonQuery();

conexion.Close();

}

once again thanks for the help!

daniel

Stephen McGuire8-Aug-06 2:43

8-Aug-06 2:43

Hi
I have had this error many times while developing my queries.
It is always caused by not supplying data for one of the 'VALUES' parameters or not defining that parameter correctly...

I notice you are using C#. I use VB.NET but I am familiar with some C#.

While creating a command the way you have, strings must be enclosed by single quotes (''), date values by octothorpes (##).

For example, if tamano.ToString() and cbTipoAudio.Text are string values that you wish to place in a field in your table, you should define them as a 'string within a string' by enclosing them in single quotes:

auxsql += "VALUES (" + Archivo + ", " + "path "+ ", desconocido, '" + tamano.ToString() + "', muestreo + ", " + auxbitrate + ", " + canales + ", " + duracionms + ", '" + cbTipoAudio.Text + "')";

For queries that involve this many parameters, you should look into creating a parameterised query. This would be much easier to construct/read.

Steve

dsovino8-Aug-06 4:01

8-Aug-06 4:01

Ooo yes. Last night while reading i found the answer as well.

I just used 'string' instead of string. Numeric values without any characters. I'll keep in mind the # for date values in the future.

thanks a lot for your help. I was stuck at least 1 week for this, and if it wasn't for your help i'll still be trying to use the dataset designer or whatever....

Here's the working code down below. I think it's the easiest way to add rows to a Access database in C#. No need for components!!

peace!!!!!

Smile | :)

--------------------------------------------------------------------------------------

try
{
string source = "Provider=Microsoft.JET.OLEDB.4.0;";
source += "data source=D:\\tesis\\Importar Archivo\\Importando\\Importando\\bin\\radio.mdb;";

OleDbConnection conexion = new OleDbConnection(source);
OleDbCommand comando = new OleDbCommand();

string auxsql = "INSERT INTO archivo (archivo, [ruta archivo], formato, tamaño, muestreo, bitrate,
canales, duracionSeg, [tipo de audio]) ";

auxsql += "VALUES ('" + Archivo + "', '" + path + "', 'desconocido'," + tamano.ToString() + ", " +
muestreo + ", " + auxbitrate + ", " + canales + ", " + duracionms + ", '" + cbTipoAudio.Text +
"')";

comando =new OleDbCommand(auxsql,conexion);
conexion.Open();

comando.ExecuteNonQuery();
conexion.Close();

MessageBox.Show("Update successful");
}

catch (System.Exception ex)
{
MessageBox.Show("Update failed:\r\n"+ex.ToString());
}

daniel kiweed

Stephen McGuire8-Aug-06 7:19