|
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
daniel sovino
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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!!!!!
--------------------------------------------------------------------------------------
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
|
|
|
|
|
Great. Glad I could help!
Steve
|
|
|
|
|
I am building an app that has a local MySQL DB and a master DB on an internet server. Basically the idea is to have a native version that can be used in the field. Yet, users can also use the master.
Does anyone know of code or an article that addresses such a scenario?
Any help is appreciated.
Thanks,
RABB17
|
|
|
|
|
I have done this specific scenario a few times and I hate it. The largest problem is with updates. If you can find a natural key solution then it is just a matter of copy paste and switching data sources. Really you can go into very complicated scenarios but they are fairly straight foward. Just try a few and see which one fits your scenario the best.
A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."
-- Stephen Crane
|
|
|
|
|
Thanks for the encouragement! I was hoping it wasn't going to be as bad as I thought.
Have you found it's better to go with datetime comparisons for data?
Another avenue of thought is a seperate table specifically for changes. That way if two users update the same data before the main is updated, it leaves room for a decision. Of course with this comes the need for someone to approve changes. Easy when the data is small, but as use increases...
Thanks again for any help!
P.S. have you put any of your synch code out for public view?
RABB17
|
|
|
|
|
But it does sound like a good idea for an article.
A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."
-- Stephen Crane
|
|
|
|
|
Hi, I am having trouble in updating my dataset, I create a dataset from a select command and display the details to a datagrid. I then goto the next new line in the datagrid and insert some new details then click the insertRow button (code below) and I get the following error message :-
the exception which is caught states : - "Value cannot be null. Parameter name: dataset"
I am not up to scratch with datasets, but all examples I can find of updates are where only one line of the dataset is shown at a time in text boxes and then a blank set of textboxes are displayed to add a new row then convert the textboxes.text to the add row command, but I am creating an application to be a front end to a database so none of the columns are known so I cannot create this form of input as the amount of textboxes would never be known.
Please help
Many thanks
John
Code for updating dataset
[CODE] Private Sub btnInsertRow_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsertRow.Click
Dim dset As DataSet
tblName = (cmbInsertData.Text)
dbConn.openExistingDatabse("Data Source=" & getDBName() & ";Version=3;New=False;Compress=True;")
dbConn.createSQLCommand()
Try
dbConn.updateDataSet(dset, tblName)
Catch es As Exception
MessageBox.Show(es.Message)
End Try
End Sub[/CODE]
[CODE] Public Sub updateDataSet(ByVal dset As DataSet, ByVal tableName As String)
Dim sqlite_dataAdapter As SQLiteDataAdapter = New SQLiteDataAdapter()
sqlite_commandBuilder = New SQLiteCommandBuilder(sqlite_dataAdapter)
sqlite_dataAdapter.Update(dset, tableName)
End Sub[/CODE]
|
|
|
|
|
You could try disabling the constraints on your dataset (temporarily). DataSet.EnforceConstraints = false ;
Usually I like to create a new DataRow, and populate it with default values, and then insert the DataRow into the appropriate DataTable. If your grid is bound to the DataSet, then the new row will automatically appear in the grid.
|
|
|
|
|
Hi, thanks foryour advice, I havesince managed to get the dataset working.
Many thanks
John
|
|
|
|
|
Recently I've dropped one table which has around 100 records.
I want to restore those 100 records.
Is there any log maintained which will give me those deleted tuples of that
table (
|
|
|
|
|
You need to restore a back up of the database to a new database with a different name and copy the data from there.
how vital enterprise application are for proactive organizations leveraging collective synergy to think outside the box and formulate their key objectives into a win-win game plan with a quality-driven approach that focuses on empowering key players to drive-up their core competencies and increase expectations with an all-around initiative to drive up the bottom-line. But of course, that's all a "high level" overview of things
--thedailywtf 3/21/06
|
|
|
|
|
I can connect to it and get the column names, etc.
But it doesn't retrieve any records and I don't know why!
I have tried a few different ways, but nothing seems to work.
What am I doing wrong??
Here's my code:
// string query = @"SELECT * FROM CONNTEST\FLOW_1";
string DBConnection = @"Provider=WinCCOLEDBProvider.1;DataSource=Laptop\WinCC;Catalog=CC_Connecti_06_07_29_15_50_04R;";
string SqlStr = @"TAG:R,'CONNTEST\FLOW_1','0000-00-00 00:10:00.000','0000-00-00'";
//string SqlStr = @"TAG:R,'CONNTEST\FLOW_1','0000-00-00 00:30:00.000','0000-00-00 00:00:00.000'";
// TAG:R,'CONNTEST\FLOW_1','0000-00-00 00:30:00.000','0000-00-00 00:00:00.000'
//string SqlStr = "TAG:R,1,'0000-00-00 00:30:00.000','0000-00-00 00:00:00.000'";
//Open Recordset via Connection object.
//string SqlStr = @"TAG:R,'CONNTEST\FLOW_1','0000-00-00 00:01:00','0000-00-00 00:00:00";
// 1: Make Connection to Database
ADODB.ConnectionClass Conn = new ADODB.ConnectionClass();
Conn.ConnectionString = DBConnection;
Conn.CursorLocation = CursorLocationEnum.adUseServer;
Conn.Open(DBConnection, "", "", 0);
// Conn = Server.CreateObject("ADODB.Connection");
// 2: Use the command text query
ADODB.CommandClass OCom = new ADODB.CommandClass ();
OCom.CommandType = ADODB.CommandTypeEnum.adCmdText;
OCom.ActiveConnection = Conn;
OCom.CommandText = SqlStr;
// 3: Create the RecordSet and fill with the data
//'ADODB.RecordsetClass rs = new ADODB.RecordsetClass();
//rs.Open(SqlStr,DBConnection,ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, -1);
//'rs.Open(OCom, Type.Missing, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockBatchOptimistic, -1);
object dummy = Type.Missing;
ADODB.Recordset rs = OCom.Execute(out dummy, ref dummy, 0);
object GetName;
GetName = rs.RecordCount;
////////GetName = rs.Fields[1].Value;
|
|
|
|
|
What you need is three tables. tblRecipes, tblIngredients and tblRecipeIngredients.
tblRecipes should be keyed on RecipeID
tblIngredients should be keyed on IngredientID
tblRecipeIngredients should have a dual key of RecipeID and IngredientID
Then you can look up recipe by ingredient or ingredient by recipe.
Simple.
how vital enterprise application are for proactive organizations leveraging collective synergy to think outside the box and formulate their key objectives into a win-win game plan with a quality-driven approach that focuses on empowering key players to drive-up their core competencies and increase expectations with an all-around initiative to drive up the bottom-line. But of course, that's all a "high level" overview of things
--thedailywtf 3/21/06
|
|
|
|
|
Hello,
Thanks for your comments.
I think your advice will do the job but have problems creating the relation between two tables. I get the error "can not create a child list for field RelationName". I get this when it is trying to bind the relation to a GridBox.
Any idea what went wrong?
Do you think it might be that i am using the names column of the recipes and the ingredients tables as my key?
george
|
|
|
|
|
Hello, could somebody tell me how to configure Zone Alarm so that it would allow remote connections to SQLServer 2005 express?
R
Johan
|
|
|
|
|
I want to create a stored procedure that select certain fields from one databases, and somemore from another these databases are on 2 different servers but run the same sql server 2000. if anyone can help point me in the right direction i would much appreciate it.
|
|
|
|
|
There is no sql server 2003. It is 2000 or 2005. Check the Books on line for setting up a linked server. You can add a linked server from enterprise manager and then access the linked server via t-sql.
select * from server.database.owner.table
Make sure the MSDTC (distributed transaction coordinator) service is running on your servers.
how vital enterprise application are for proactive organizations leveraging collective synergy to think outside the box and formulate their key objectives into a win-win game plan with a quality-driven approach that focuses on empowering key players to drive-up their core competencies and increase expectations with an all-around initiative to drive up the bottom-line. But of course, that's all a "high level" overview of things
--thedailywtf 3/21/06
|
|
|
|
|
Hi frends
I have written a trigger in MS-SQL 2000 runing successfully to insert single row.....but if i need to insert multiple rows from a select query in this trigger ......I am Confused ? 
Purpose of Trigger
whenever a column will be updated in a table the data from two tables will be inserted into two tables where one table has multiple rows to insert.
I am trying to use While loop but confused that what should be the condition there to break tha loop......pls help if someone knows.
My trigger is
IF (COLUMNS_UPDATED() ) > 49
BEGIN
Declare @ServerDate datetime;
Declare @TranID numeric(18,0);
Declare @OppID numeric(18,0);
Declare @ProductID int;
Declare @Qty int;
Declare @Cost numeric(18,2);
Declare @Price numeric(18,2);
Declare @Discount numeric(18,2);
Declare @TaxAmt numeric(18,2);
Declare @UserID numeric(18,0);
Declare @ContactID numeric(18,0)
Declare @CountProduct int
SELECT @ServerDate=getdate()
SELECT @OppID=Opp.OppId, @ContactID=Opp.ContactID, @UserID=Opp.UserID FROM Opportunity Opp INNER JOIN INSERTED ON Opp.OppId = INSERTED.OppId where Inserted.StatusID=3
if (Convert(varchar(10),@OppID) != '')
BEGIN
INSERT INTO [Transaction] (OppID,TranDate, ContactID,UserID) values (@OppId, @ServerDate, @ContactID, @UserID)
SELECT @TranID=TranID from [Transaction] where TranDate=@ServerDate
SELECT @ProductID=Opp.ProductID, @Qty=Opp.Qty, @Cost=Opp.Cost, @Price=Opp.Price, @Discount=Opp.Discount, @TaxAmt=Opp.TaxAmt FROM vOpportunityList Opp INNER JOIN INSERTED ON Opp.OppId = INSERTED.OppId where Inserted.StatusID=3
--Count row
SELECT @CountProduct=count(Opp.ProductID) FROM vOpportunityList Opp INNER JOIN INSERTED ON Opp.OppId = INSERTED.OppId where Inserted.StatusID=3
--WHILE (-----------)
--BEGIN
INSERT INTO [TranProduct] (TranID,ProductID,Qty,Cost,Price,Discount,TaxAmt) values(@TranID, @ProductID, @Qty, @Cost, @Price, @Discount, @TaxAmt)
--END
END
END
Dinesh Sharma
|
|
|
|
|
I would suggest a cursor but using a cursor in a trigger is bad practice as it will be slow. Is there no way to do this update from your application inside of commitment control? If a trigger is your only option then use a cursor to do the loop. You don't need a row count variable either just loop through the results of your select statement. Check Here
for more info.
how vital enterprise application are for proactive organizations leveraging collective synergy to think outside the box and formulate their key objectives into a win-win game plan with a quality-driven approach that focuses on empowering key players to drive-up their core competencies and increase expectations with an all-around initiative to drive up the bottom-line. But of course, that's all a "high level" overview of things
--thedailywtf 3/21/06
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
