Showing posts with label alli. Show all posts
Showing posts with label alli. Show all posts

Monday, March 12, 2012

Pls help!

Hi all

i am a new user to .NET comunity and i need help in the following problem

1. I have a table called student_master which has rollno a PK and various other fields which are used to get the corresponding details.

2. I also have a table called attendance with the following fields rollno an FK, date and hrs

3. what i need to do is, on selection of a day, i need to copy all the roll numbers from te student_master table and the date to the selected date and hrs as default 0 by just one transaction

example

student_master table

rollno |name

1 | xxx

2 | xxx

what i should get in attendance table is somwwhat like this

rollno|date|hrs

1 |12 |0

2 |12 |0

1 |13 |0

2 |13 |0

Thanks in advance

Hi Grietcap

Not sure I understand you completely - especiallywhy you seem to have two dates in your returned table (12th and 13th)if you are selecting by a date. But in any case can I offer thefollowing select statement which may be of help:-

"SELECT[student_master].[rollno], [attendance].[date], [attendance].[hours]FROM [student_master] INNER JOIN [attendance] ON[student_master].[rollno] = [attendance].[rollno] WHERE(([attendance].[date] = @.date) AND ([attendance].[hours] = 0))"

You can run this select in the following VB.net function

Function MyQueryMethod(ByVal [date] As Date) As System.Data.SqlClient.SqlDataReader
Dim connectionString AsString = "server='XXX'; user id='XXXX'; password='XXXX';Database='XXXX'"
Dim sqlConnection AsSystem.Data.SqlClient.SqlConnection = NewSystem.Data.SqlClient.SqlConnection(connectionString)

Dim queryString As String ="SELECT [student_master].[rollno], [attendance].[date],[attendance].[hours] FROM "& _
"[student_master] INNER JOIN [attendance] ON [student_master].[rollno]= [attendance].[rollno] WHERE (([attendance].[date] = @.date) AND([attend"& _
"ance].[hours] = 0))"
Dim sqlCommand AsSystem.Data.SqlClient.SqlCommand = NewSystem.Data.SqlClient.SqlCommand(queryString, sqlConnection)

sqlCommand.Parameters.Add("@.date", System.Data.SqlDbType.DateTime).Value = [date]

sqlConnection.Open
Dim dataReader AsSystem.Data.SqlClient.SqlDataReader =sqlCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

Return dataReader
End Function

Hope this helps

Regards

Mike

pls help

Hi All
I have table Tb1 with 2 columns ColA and Colb with rows as below:
ColA ColB
a 1
b 2
How can i query Tb1 to bring a result set of ColB only as another set of
columns as below:
Col(i) Col(ii)
1 2
Thanx in advance.http://aspfaq.com/show.asp?id=2462
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:68702DCE-1A9B-4EB8-AF73-A77912999191@.microsoft.com...
> Hi All
> I have table Tb1 with 2 columns ColA and Colb with rows as below:
> ColA ColB
> a 1
> b 2
> How can i query Tb1 to bring a result set of ColB only as another set of
> columns as below:
> Col(i) Col(ii)
> 1 2
> Thanx in advance.

Friday, March 9, 2012

Please suggest type Of Replication suitable in this scenario !

Hi All:
I have 2 sites, one here in India & one at Montreal working on same
database, hosted here (India), using ASP Application.
The link is dedicated over WAN. Now I want both sites to access their own DB
at their local ends (structure being same) & then replicate/snycronize both
Databases periodically, say every 60 mins or in worst case once every day.
Both sites receive customer inbound calls & people take orders, troubleshoot
product. The DB size is 3 GB & growing, though slowly.
What is best type of replication which i can use here?
Please suggest!
Thanks,
Anukul
anukuls@.yahoo.com
If your schema is static, and your users will never be accessing these
databases at the same time or if you can minimize conflicts using
partitioning you could use bi-directional transactional replication.
Otherwise you should look at merge replication.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Anukul" <Anukul@.discussions.microsoft.com> wrote in message
news:10746DEA-A21D-4702-A44B-B46631DC89FC@.microsoft.com...
> Hi All:
> I have 2 sites, one here in India & one at Montreal working on same
> database, hosted here (India), using ASP Application.
> The link is dedicated over WAN. Now I want both sites to access their own
DB
> at their local ends (structure being same) & then replicate/snycronize
both
> Databases periodically, say every 60 mins or in worst case once every day.
> Both sites receive customer inbound calls & people take orders,
troubleshoot
> product. The DB size is 3 GB & growing, though slowly.
> What is best type of replication which i can use here?
> Please suggest!
> Thanks,
> Anukul
> anukuls@.yahoo.com
|||Thanks Hilary!
Yes the Database structure is static, but may change once every 1 month.
Also, users will be accessing the DB simultaneously at both ends, as Inbound
customer calls will be received at both sites, so changes relating to a
customer's address, name, order may get updated. I am over looking the
scenario where same customer calls at same time at both sites, but the same
customer's data may be worked on at both sites at same time. My ASP
application will change as it uses identities right now, but will I will
create site specific ids.
Please suggest.
Thanks,
Anukul
"Hilary Cotter" wrote:

> If your schema is static, and your users will never be accessing these
> databases at the same time or if you can minimize conflicts using
> partitioning you could use bi-directional transactional replication.
> Otherwise you should look at merge replication.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Anukul" <Anukul@.discussions.microsoft.com> wrote in message
> news:10746DEA-A21D-4702-A44B-B46631DC89FC@.microsoft.com...
> DB
> both
> troubleshoot
>
>
|||merge replication will probably work best for you then.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Anukul" <Anukul@.discussions.microsoft.com> wrote in message
news:0A5E7A9E-43E7-43DC-8F45-0926FD654F0C@.microsoft.com...
> Thanks Hilary!
> Yes the Database structure is static, but may change once every 1 month.
> Also, users will be accessing the DB simultaneously at both ends, as
Inbound
> customer calls will be received at both sites, so changes relating to a
> customer's address, name, order may get updated. I am over looking the
> scenario where same customer calls at same time at both sites, but the
same[vbcol=seagreen]
> customer's data may be worked on at both sites at same time. My ASP
> application will change as it uses identities right now, but will I will
> create site specific ids.
> Please suggest.
> Thanks,
> Anukul
> "Hilary Cotter" wrote:
own[vbcol=seagreen]
day.[vbcol=seagreen]
|||Thanks Again!
Is there a Book which you can recommend to setup Merge Replication?
Are there any 3rd party tools for Conflict Resolution available?
Please guide.
Thanks,
Anukul
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eqXWnHp8EHA.2876@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> merge replication will probably work best for you then.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Anukul" <Anukul@.discussions.microsoft.com> wrote in message
> news:0A5E7A9E-43E7-43DC-8F45-0926FD654F0C@.microsoft.com...
> Inbound
> same
their[vbcol=seagreen]
> own
replicate/snycronize[vbcol=seagreen]
every
> day.
>
|||Well I am working on a merge book. There are no third party tools I know of.
The default conflict resolvers which ship with SQL Server are pretty good.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Anukul" <anukuls@.yahoo.com> wrote in message
news:u961qLp8EHA.3988@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Thanks Again!
> Is there a Book which you can recommend to setup Merge Replication?
> Are there any 3rd party tools for Conflict Resolution available?
> Please guide.
> Thanks,
> Anukul
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eqXWnHp8EHA.2876@.TK2MSFTNGP12.phx.gbl...
month.[vbcol=seagreen]
a[vbcol=seagreen]
will[vbcol=seagreen]
these[vbcol=seagreen]
replication.[vbcol=seagreen]
same
> their
> replicate/snycronize
> every
>
|||Hi Hilary:
Just another query:
As I mentioned , what would be the best way of adding / dropping columns to
an existing Database, which is published for replication (merge
replication)?
Would sp_addrplcolumn & sp_deleterplcolumn work or are there any other ways
to it ? Even datatype changes or size changes.
Thanks,
Anukul
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eu7mcWp8EHA.3236@.TK2MSFTNGP15.phx.gbl...
> Well I am working on a merge book. There are no third party tools I know
of.[vbcol=seagreen]
> The default conflict resolvers which ship with SQL Server are pretty good.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Anukul" <anukuls@.yahoo.com> wrote in message
> news:u961qLp8EHA.3988@.TK2MSFTNGP10.phx.gbl...
> month.
to[vbcol=seagreen]
> a
the[vbcol=seagreen]
the
> will
> these
> replication.
> same
>
|||sp_repladdcolumn and sp_repldropcolumn are the best ways.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Anukul" <anukuls@.yahoo.com> wrote in message
news:O59cEGA9EHA.3640@.tk2msftngp13.phx.gbl...
> Hi Hilary:
> Just another query:
> As I mentioned , what would be the best way of adding / dropping columns
to
> an existing Database, which is published for replication (merge
> replication)?
> Would sp_addrplcolumn & sp_deleterplcolumn work or are there any other
ways[vbcol=seagreen]
> to it ? Even datatype changes or size changes.
> Thanks,
> Anukul
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eu7mcWp8EHA.3236@.TK2MSFTNGP15.phx.gbl...
> of.
good.[vbcol=seagreen]
as[vbcol=seagreen]
> to
> the
> the
ASP[vbcol=seagreen]
I[vbcol=seagreen]
using[vbcol=seagreen]
access[vbcol=seagreen]
once[vbcol=seagreen]
orders,
>
|||Also, have a look at an article on
www.replicationanswers.com for methods of changing an
existing datatype, and Kestutis's post from yesterday.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)