|
Hi ALL,
I have two databases(SQlserver and MS Access) with same schemas (same tables). SQL server database has data but Access has no data (blank database).
My goal : when user enters a ClientId and click insert button then I need to retrive that single record from all tables in sql server database and insert into tables in MS Access Database.
Achieved: i retrived from all tables in sql server databases with client id and stored the data in Dataset.
Problem: Insert into Access tables????
i have table array and i am looping thru all tables in array and trying to insert data from above dataset into Ms Access dynamically.
Can you suggest how insert into Access for all tables dynamically in loop . i cannot wrie insert statement for each table. i need one which is generic for every table so that i will pass parametrs. Its not a bulk insert, its single record push into multiple tables.
-------This is My code-------------------------------------------------------
private void InsertMsiClientIntoTest(string ClientId)
{
SqlConnection sqlConnection = null;
SqlDataAdapter sqlDataAdapter = null;
DataSet sqlserverDataset = new DataSet();
sqlserverDataset.Tables.Add();
sqlConnection = new SqlConnection();
sqlConnection = new SqlConnection("Data Source=THINK;Initial Catalog=" + dbName + ";Integrated Security=True;");
sqlConnection.Open();
sqlDataAdapter = new SqlDataAdapter(ClientSQL.PopulateTables, sqlConnection);
sqlDataAdapter.SelectCommand.Parameters.AddWithValue("@ClientId", cmbId.Text);
sqlDataAdapter.Fill(sqlserverDataset);
GetDataFromTablesForID(sqlserverDataset);
InsertAllTableDataIntoAccess(sqlserverDataset,tableArray);
}
private DataSet GetDataFromTablesForID(DataSet dsTablesList)
{
SqlConnection sqlConnection = null;
SqlDataAdapter sqlDataAdapter = null;
string tableName = string.Empty;
string QueryText = string.Empty;
int i =0;
tableArray = new string[dsTablesList.Tables[0].Rows.Count];
DataSet sqlserverDataset = new DataSet();
sqlserverDataset.Tables.Add();
sqlConnection = new SqlConnection();
sqlConnection = new SqlConnection("Data Source=THINK;Initial Catalog=" + dbName + ";Integrated Security=True;");
sqlConnection.Open();
foreach (DataRow itemRow in dsTablesList.Tables[0].Rows)
{
tableArray[i] = itemRow[0].ToString();
i++;
}
foreach (string tableItem in tableArray)
{
tableName = tableItem;
QueryText = "select x.* from" + " " + tableName + " " +
"x inner join ClientMajor ci on ci.ClientId = x.ClientId where ci.MajorClientId =@ClientId";
}
sqlDataAdapter = new SqlDataAdapter(QueryText, sqlConnection);
sqlDataAdapter.SelectCommand.Parameters.AddWithValue("@ClientId", cmbExceedId.Text);
sqlDataAdapter.Fill(sqlserverDataset);
}
return sqlserverDataset;
}
private void InsertAllTableDataIntoAccess(DataSet Inputset, string[] tableArray)
{
string connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["mdb"].ToString();
OleDbConnection oledbConnection = null;
OleDbDataAdapter oledbDataAdapter = null;
DataSet resultSet = new DataSet();
oledbConnection = new OleDbConnection(connectionstring);
oledbConnection.Open();
foreach (string tableItem in tableArray)
{
//????????? I NEED THE FOLLOWING CODE......////////////???????
oledbDataAdapter.InsertCommand.CommandText="insert into "
oledbDataAdapter.Fill(resultSet.Tables[0]);
}
}
Thanks in Advance...
|
|
|
|
|
If you have two datasets 1) from MSSQL 2) from MS Access and all column names and table names match you should be able to iterate the datatables in the datasets and us importrow method to copy a row from one to the other and then use data adapter to execute update command and should use the assigned insert/update commands assigned to the data adapter to populate the data into the database.
|
|
|
|
|
Why would you do this  ?
|
|
|
|
|
You have no reason to have all the data in memory at once so use a DataReader, not a DataSet.
0) ExecuteReader on the SELECT command
1) Iterate the DataReader's columns to create the INSERT command and its parameters
2) Read from the DataReader
3) Set the INSERT command's parameter values
4) ExecuteNonQuery
5) Repeat from 2 as necessary
You should also use a try/catch and a transaction.
modified 30-Jul-12 11:39am.
|
|
|
|
|
Hi;
How can I data transfer between two active forms. from FORM 1 to FORM 2.
|
|
|
|
|
You can use delegates to pass data between forms - this article[^] describes this approach in some detail.
modified 29-Jul-12 6:33am.
|
|
|
|
|
Hi Abhinav, please note my comment (written today) on that article you linked to, indicating what I think is a major flaw in its implementation.
best, Bill
"Everything we call real is made of things that cannot be regarded as real." Niels Bohr
|
|
|
|
|
That depends on what you're transferring and why.
There are various methods to do so, some more appropriate than others depending on what your application is doing.
Form2 could expose properties that your Form1 code sets, or you could use delegates to pass data to a method on Form2, or both forms could be using the same data object where Form1 sets some values in this object and Form2 picks it up by some notification method the data object exposes, ...
|
|
|
|
|
Please describe in more detail exactly what you mean by "transfer data" here.
Re the comments by esteemed colleagues, Dave Kreskowiak, and Abhinav S: I don't know why, but every time I see someone advocate using delegates, in this type of context, without also using the words "and events," a kitten inside my mind dies  I'll try to get over that !
thanks, Bill
"Everything we call real is made of things that cannot be regarded as real." Niels Bohr
modified 30-Jul-12 1:07am.
|
|
|
|
|
BillWoodruff wrote: without also using the words "and events," a kitten inside my mind dies I'll try to get over tha
|
|
|
|
|
Well, they kind of go hand-in-hand in a case like this, not that I ever use them for this.
I've got a bigger problem with "esteemed colleagues". This is like calling the crew from Animal House "proper gentlemen"! 
|
|
|
|
|
hello
how do convert any Image and gif or swf to dot matrix display and scroll it.
please send me sample code with c#.
very thanks.
hosseinjavidian@gmail.com
|
|
|
|
|
Never post your email address in any forum, unless you really like spam! If anyone replies to you, you will receive an email to let you know
Ideological Purity is no substitute for being able to stick your thumb down a pipe to stop the water
|
|
|
|
|
|
You're going to have to explain exactly what you mean by "dot matrix display". Are you talking about an LED driven piece of hardware? Are you talking about some control you drop of your form? Are you talking about some LED display contorl you're creating??
Without knowing this, it's pretty much impossible to tell you "how to scroll" on this display.
|
|
|
|
|
|
You do realise that the original poster doesn't get replies to questions when you reply to someone else, don't you? I don't think Dave's particularly in need of doing this.
|
|
|
|
|
Pete is correct. Why are you telling ME this??
I already know this stuff, and, frankly, would NEVER use a parallel port to control anything as it's too easy to blow the port up. I'd use hardware dedicated to the purpose like something from Phidgets or a Netduino or Arduino.
Since the original poster never specified what they were using as an "LED display" the question is unanswerable. I really don't feel like going through every possibility of scrolling an image on every type of "LED display" as it would take a small book to describe them all.
|
|
|
|
|
Hello I am new to xml. I want to read the data from xml file. Here is my xml code :
<overseas_sales>
<overseas_sale_ad>
<country>Spain</country>
<area>Costa Blanca South</area>
<property_type>house</property_type>
<price>95000</price>
<price_type>region</price_type>
<bathroom_number>2</bathroom_number>
<bedroom_number>2</bedroom_number>
<property_status>for-sale</property_status>
<external_id>R214</external_id>
<photos>
<photo>http://www.solmarestates.com/images/propimages/R2141.jpg</photo>
<photo>http://www.solmarestates.com/images/propimages/R2142.jpg</photo>
<photo>http://www.solmarestates.com/images/propimages/R2143.jpg</photo>
<photo>http://www.solmarestates.com/images/propimages/R2144.jpg</photo>
<photo>http://www.solmarestates.com/images/propimages/R2145.jpg</photo>
<photo>http://www.solmarestates.com/images/propimages/106_1218710541.jpeg</photo>
<photo>http://www.solmarestates.com/images/propimages/106_1218710543.jpeg</photo>
<photo>http://www.solmarestates.com/images/propimages/106_1218710546.jpeg</photo>
</photos>
</overseas_sale_ad>
Now I want to get all photos in a list . I am trying with code :
XmlNode root = PropertyDocument.SelectSingleNode("//overseas_sales");
XmlNodeList nodeList = root.SelectNodes("overseas_sale_ad")
ArrayList ElementsList = new ArrayList();
foreach (XmlNode n in nodeList)
{
if (n.SelectSingleNode("external_id").InnerText == ID)
{
XmlNodeList photos = n.SelectNodes("photos");
foreach(XmlNode ph in photos)
{
ElementsList.Add(ph.SelectSingleNode("photo").InnerText);
}
}
}
But I am getting url of only first photo. I want to get them all in "ElementList". Can anyone help?
|
|
|
|
|
Well yes. That is what I would expect - you only have the one "photos" node. You need another loop:
XmlNode root = PropertyDocument.SelectSingleNode("//overseas_sales");
XmlNodeList nodeList = root.SelectNodes("overseas_sale_ad");
ArrayList ElementsList = new ArrayList();
foreach (XmlNode n in nodeList)
{
if (n.SelectSingleNode("external_id").InnerText == ID)
{
XmlNodeList photos = n.SelectNodes("photos");
foreach (XmlNode ph in photos)
{
foreach (XmlNode photo in ph.SelectNodes("photo"))
{
ElementsList.Add(photo.InnerText);
}
}
}
}
Ideological Purity is no substitute for being able to stick your thumb down a pipe to stop the water
|
|
|
|
|
Thank you so much, adding another loop solved my problem. 
|
|
|
|
|
You're welcome!
Ideological Purity is no substitute for being able to stick your thumb down a pipe to stop the water
|
|
|
|
|
You need to use SelectNodes to get them, not SelectSingleNode, and I would use one loop:
foreach(XmlNode ph in nodeList.SelectNodes("//overseas_sale_ad[external_id='R214']/photos/photo"))
{
ElementsList.Add(ph.InnerText);
}
Brush up on XPath.
I would also use a List<string> rather than an ArrayList -- no one uses ArrayList anymore.
|
|
|
|
|
'System.Xml.XmlNodeList' does not contain a definition for 'SelectNodes' and no extension method 'SelectNodes' accepting a first argument of type 'System.Xml.XmlNodeList' could be found (are you missing a using directive or an assembly reference?)
Thank you for your reply. I am getting this error. But no problem I got my solution
|
|
|
|
|
Oh, yeah, you need a node. No biggy. You should still only need one loop.
faiza.butt89 wrote: I got my solution
modified 30-Jul-12 9:51am.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
