Showing posts with label writing. Show all posts
Showing posts with label writing. Show all posts

Monday, March 12, 2012

Pls help! Delete records in VB.net

Currently i writing a program to delete Records in the SQL database using VB.net language...
BUT i not sure whether i am right?
Pls provide me with the coding using a command object to delete records in SQL database...thank...however i tried the codes below but not working ...Pls help

Dim StrConnection As String = "workstation id=""ET-T15404-PC1"";integrated security=SSPI; etc

Dim objConnection As New SqlConnection(strConnection)
Dim strSQL As String = "Delete Seller.Admin FROMSeller WHERE Seller.no=tb.no"
Dim dbComm As New SqlCommand(strSQL, objConnection)

objConnection.Open()
dbComm.ExecuteNonQuery
objConnecion.Close()Check out this Quickstart for information on how to accomplish this:Server-Side Data Access: Deleting Data in a SQL Database

Terri

Friday, March 9, 2012

Please suggest just one book - To Manivannan and others

Hi,

I need you to suggest me one book so that I can learn T-sql - beginner to advanced.

I want to start writing queries like Manivannan and others so that i can also help others Smile

Please suggest and give me a plan. I am ready to sincerely dedicate 2-3 hours every day.

thanks

Hi,

I very much happy to hear from you.

Effective Query writing will be come from experience & learning, 2 years back I used this forum like you only. Then I started learning from most of the genius from here (Kent, Arnie, AMB, Lousie & Jayachandar, OJ), frankly still I am learning from this forum (like to thank Sankar, ggcibuc, rusag2 & rich)

Learn, Practice & one day you will become as a expert.

I really like & learnt most of the things form this book,

Inside Microsoft SQL Server 2000 by Ron Soukup & Kalen Delaney

You can search like BOOK in this forum there are lot of suggestions available.

|||

thanks so much for answering..i forgot to mention i need something for Sql 2005. Smile

please suggest for 2005, specifically for t-sql programming.

currently i dont even know to write stored proc Sad

thanks.

|||

Check out the two books of the "Inside SQL Server 2005" series written by Itzik Ben-Gan.

Monday, February 20, 2012

Please help! I have lots of questions.

In case some of you have read my previous posts, you may be aware that I'm writing a webboard application as a replacement for the old one.

The old one currently have approximately 50000 topics in the database, each has on average 10 replies (I just check recently. I though it was only 7000 topics).

I need to provide paging and sorting feature to the topic list. But I can't just SELECT all of them and let GridView do the paging/sorting, right?
I have been using stored procedures to store my SQL statement for several projects now. I know how to deal with the paging feature (ROW_NUMBER), but the sorting requires me to change to change the "ORDER BY" clause.

1. Can somebody tell me how to change the ORDER BY clause in the stored procedure(s) at runtime? Or does anyone have other approach?

Currently I'm thinking about moving back from store procedures to hard-code SQL statements, and then modify/generate the SQL statement for each paging / sorting. But I've learn that stored procedures give more performance and security.

2. According to the situation I provided, is it worth moving from stored procedures to hard-code SQL?

I'm also using 3-tier architecture approach + OOP. But I reach a conflict in my thoughts.

You see, according to OOP, I'm supposed to create classes that reflect the actual objects in the real-world, right? In my case the classes are "Board, Topic, Reply, ..." According to this and 3-tier approach, I intend to use ObjectDataSource as a bridge between Presentation Logic and Business Logic. But I wonder what my datasource class should return

3. Should my data source class return data objects like

1st approach

[DataObject(True)]
pubic class TopicDataSource{
public static Topic[] GetTopicList() { }
}

or should it return DataSet / DataTable / DataReader like

2nd approach

[DataObject(True)]
public class TopicDataSource{
public static DataTable GetTopicList() {}
}

Personally I think approach 1 is more OOP and allow for more extendability, but approach 2 might be faster.

4. If I go with approach 1, how should I control which property of my data objects is read-onlyafter it's has been inserted/created?
Can I just set my data object's property to be readonly? Or do I have to set it at page level (i.e. GridView-> Columns -> BoundField -> ReadOnly=True)?
Or do I set it and the page leveland write a code to throw an exception in the rare case the application / user try to change it's value? Or else?

Please help. These questions slow me down for days now.

If there's any concepts that I misunderstood, please tell me. I'm aware that I don't know as much as some of you.

I will be extremely grateful to anyone who answer any of my questions.

Thanks a lot.

PS. For those who think my questions are stupid, I'm very, very sorry that I bother you.

Hello my friend,

First thing - do not call yourself stupid for asking questions. The stupid ones are those that never ask and then end up breaking something or building something crap. My answers are as follows: -

1) Use stored procedures for performance and then change the order by clause by using a DataView, like this: -

// instead of this
//myRepeater.DataSource = myDataSet;
//myRepeater.DataBind();

// do this (use a stored procedure to fill a dataset and then filter the dataset)
DataView dv = new DataView(myDataSet.Tables[0]);
dv.Sort = "MyField DESC";

myRepeater.DataSource = dv;
myRepeater.DataBind();

2) If all you need to change is the order by clause, use stored procedures with the above example. Only go back to hardcoded SQL if you need to specify the fields to select at runtime or if there are very complex and/or clauses at runtime and the data has a lot of records.

3) If you find approach 2 to be faster then use it. Extensibility is no substitute for performance. We do all the hard stuff so the application runs fast, as opposed to building a second class project to suit us.

4) You can have a Locked property, or whatever name you prefer, on your object. Then in the set property definition, have the following: -

Set(ByVal value As String)
If Me.Locked = False Then
MyProperty = value
End If
End Set

I hope this helps you.

Kind regards

Scotty

|||Thank you Scotty (or Scott, perhaps). That help answer question 2, 3, 4 quite clear. Although if anyone think differently, or if anyone think something should be added, don't hesitate. Please answer it here.

But I still worry about problem 1.
Does the dataset get everything after calling SQLDataAdapter.Fill? In case I SELECT everything without paging? I mean, won't it use too much memory?

Also, if I implement paging in stored procedure, the sorting won't be correct.
For example if the data is like this

Table: Test
Test1
Row 1: a
Row 2: b
Row 3: c
Row 4: d
Row 5: e

Situation : startRecord = 0 MaxRecords = 3 sortColumns = "Test1 DESC"

If I use paging, I will get "a, b, c", right?
Then if I use dataview sorting, my data will reorder from "a, b, c" to - "c, b, a" which is the final result.
But that's not what we actually want. The final data should be "e, d, c" not "c, b, a", right?

Can somebody help?|||

I just thought of something.

Even if I plan to have 2nd, 3rd ... versions, performance come first, right? Just to be sure.

As I told you above, my program intend to replace an existing one create by another company. But currently version 1's main objective is to replace the old system only. I intend to have new features added after version 1 is finished, to be version 2.