Database

Re: how to populate empty ms access database from dataset

1-May-08 6:01

Hi!

I thought i was heading somewhere with those links Blue_Boy had sent.. but i've been stuck for quite a while now. So let me describe my problem in more detail: Plese help me Frown | :(

I have 2 datasets, with identical schema. Now i have merged them using the dataset1.merge(dataset2) method. So my dataset1 is a merge of the two datasets. Now when i try to update the database of dataset1 using dataadapter.update().. method, i dont see the database getting updated.This is the code snippet...

<br />
            OleDbCommand accessCommandDB1 = new OleDbCommand(strAccessSelect, accessConnDb1);<br />
            OleDbDataAdapter dataAdapterDB1 = new OleDbDataAdapter(accessCommandDB1);<br />
            OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(dataAdapterDB1);<br />
<br />
            dataSetDB1.Merge(dataSetDB2);<br />
<br />
            n = dataAdapterDB1.Update(dataSetDB1, "Customers");<br />
<br />

I see that no records are updated, from the value of n(n =0 after the call)
What could be the reason? Note that i dont have any primay keys to any table in my database

Also wanted to know how to polpulate a fresh empty ACCESS database, with my dataset.. Again no primary keys for any table.. i would prefer this over the update method..

Please help me at the earliest.. Confused | :confused:

Awaiting your replies.... Confused | :confused:

Shreyas

Kschuler1-May-08 7:34

Kschuler

1-May-08 7:34

I've never used the merge and there isn't very clear info on it on msdn. But I did notice that it is overloaded. What happens when you try to use dataSetDB1.Merge(dataSetDB2, True) instead?

Re: how to populate empty ms access database from dataset

Shreyas Ganesh1-May-08 8:30

Re: how to populate empty ms access database from dataset

1-May-08 8:30

I have tried that as well. It doesnt work.
But i am sure the merge has worked because i am printing certain values to confirm it.. Si i guess its some update issue.. Right?

Btw, how do i populate an empty access database with my dataset?

Kschuler1-May-08 8:41

Kschuler

1-May-08 8:41

When you say "empty" do you mean that it doesn't have any tables? Because I'm thinking you'd have to write code to build create sql statements and excute them against the database file.

Re: how to populate empty ms access database from dataset

Shreyas Ganesh1-May-08 15:35

Re: how to populate empty ms access database from dataset

1-May-08 15:35

Yes, i mean a newly created database, with no tables. So will i have to manually add all tables to it? Or is there something like setting its datasource to the new dataset?
What if i have a new database with the same schema, but no tuples in the tables?

Kschuler2-May-08 3:38

Kschuler

2-May-08 3:38

If you have a database with no tables, you will not be able to save data to it until you create those tables. You could probably write some code to dynamically build the tables by looping through the schema of your dataset object and building sql CREATE TABLE statements that you can run from a command object via the .ExecuteNonQuery function.

If you have a database with the same schema as a dataset, you should be able to use the .Update on the command object and save your data to it.

If you need more help regarding the CREATE TABLE statement, try looking for articles in Code Project or on Google[^] for more background information to get you going.

Hope this helps.

Re: how to populate empty ms access database from dataset

Blue_Boy2-May-08 5:56

Re: how to populate empty ms access database from dataset

2-May-08 5:56

Create MS Acces Tables using C# code[^]

I Love T-SQL

Don't torture yourself,let the life do it for you.

Shreyas Ganesh3-May-08 5:35

how can i get Minmam value

3-May-08 5:35

Thanks for your help! Smile | :)

Btw, dont know why the update didnt work, but i did manage to insert all rows into the database, by generating queries for each tuple..

Shreyas

asha_s1-May-08 1:38

1-May-08 1:38

how can i select the Minmam value from row that have many coulm
like
select * from room where Capacity >= SumCapacity and featuer='0000'

how can i chose the Minmam Capacity that are grater than SumCapacity
and have the featuer '0000'

please i need help Sigh | :sigh:

Blue_Boy1-May-08 2:12

1-May-08 2:12

1. Can you post your data how they are stored in table?
2. Can you post the result which you want to get?

I Love T-SQL

asha_s1-May-08 2:32

1-May-08 2:32

RoomID Capacity Feature
11 40 1100
13 35 1000
137 60 1100
138 92 1100

i have many room that have the same featuer and i want to chose the room that are its size greater than the sumCap ( i have many room that are grater than the sumCap and i want to chose the one that have the minman capacity from them )

i hop you can help me Smile | :)

Blue_Boy1-May-08 2:46

1-May-08 2:46

I can't undestand well your question but down below is what i undestand from your question.

asha_s wrote:
i have many room that have the same featuer and i want to chose the room that are its size greater than the sumCap

I think this fits to your need.

select sum(capacity) as CapacitySum,feature,<br />
(select top 1 t1.roomid from myTable as t1 where t1.feature = myTable.feature order by t1.roomid desc) as roomID<br />
from myTable <br />
group by feature<br />
order by CapacitySum desc

asha_s wrote:
( i have many room that are grater than the sumCap and i want to chose the one that have the minman capacity from them )

the second fit for you.

select top 1 sum(capacity) as CapacitySum,feature,<br />
(select top 1 t1.roomid from myTable as t1 where t1.feature = myTable.feature order by t1.roomid desc) as roomID<br />
from myTable <br />
group by feature<br />
order by CapacitySum asc

I Love T-SQL

asha_s1-May-08 3:13

1-May-08 3:13

thanks but i do not want the sum
sumcap:is variable that contain value
i want the room than have the manmam capacity from a set of rooms that are their capacity greater than sumcap

because i have a set of room that have capacity greater than sumcap and
have the same feature and i want to select the one that have the manimam capacity

i hop you understand
please help D'Oh! | :doh:

Blue_Boy1-May-08 3:26

1-May-08 3:26

By manmam you mean minimum? (I hope I had understand you even if i didn't got it 100%)

select top 1  min(roomID) as RoomID,feature from myTable where roomid >=sumCap<br />
group by roomid,feature<br />
having (feature='1100')<br />
order by roomID asc<br />

I Love T-SQL

asha_s1-May-08 3:36

1-May-08 3:36

yes i mean minimum

can i want the minimum capicity can i write

select top 1 min(Capacity ) as Capacity,feature from myTable where Capacity >=sumCap<br />
group by roomid,feature<br />
having (feature='1100')<br />
order by Capacity asc<br />

thanks so much

Blue_Boy1-May-08 3:40

1-May-08 3:40

I guess you got right solution, right?

I Love T-SQL

asha_s1-May-08 3:44

1-May-08 3:44

yes ,thanks so much Laugh | :laugh:

Blue_Boy1-May-08 3:47

Re: SQL Server Replication

1-May-08 3:47

Cool, you are welcome. Wink | ;)

I Love T-SQL

SQL Server Replication

abdurrahman oğuz30-Apr-08 23:13

abdurrahman oğuz

30-Apr-08 23:13

i'm using SQL Server 2005, Windows XP SP2, Visual Studio .Net 2003, AVG anti-virus, no firewall, no proxy.

i configure IIS (virtual directory) and SQL Server (publication) as described in Miscrosoft web site.
i installed Microsoft SQL Server 2005 Mobile Edition and configured for web sycronization. i tried everything which mentioned in many blogs, forms and msdn but i recieve the error "Header Information is either is corrupted or missing..."

When i type "http://[Server IP]/[Virtual Directory]/sqlcesa30.dll", it return a valid result but i cannot connect to SQL Server for Pull or Push or SubmitSql process.

here is my code

string rdaOleDbConnectString = "Provider=SQLOLEDB;" +
    "Data Source=" + MainForm.ServerIP + ";" +
    "User Id=" + MainForm.DBUserID + ";" +
    "Password=" + MainForm.DBPassword + "; " +
    "Initial Catalog=" + MainForm.DBName + ";";
SqlCeRemoteDataAccess rda = null;
try {
    rda = new SqlCeRemoteDataAccess();
    rda.InternetLogin = MainForm.WebUserID;
    rda.InternetPassword = MainForm.WebPassword;
    rda.InternetUrl = "http://" + MainForm.ServerIP + "/" + MainForm.WebDir + "/sqlcesa30.dll";
    rda.SubmitSql(txtQuery.Text, rdaOleDbConnectString);
    MessageBox.Show("The query execution is successfull");
}
catch (SqlCeException exc){
    MessageBox.Show(exc.Message);
    return;
}
finally{
    rda.Dispose();
}

is there any suggestions?

Blue_Boy1-May-08 0:11