SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 SQL server stored procedure and VB.Net
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Siam
Starting Member

United Kingdom
5 Posts

Posted - 05/17/2013 :  06:58:43  Show Profile  Reply with Quote
I have this code written in VB.net (VS2008) where I intend to construct the WHERE Clause (in my code it is the variable GlobalWhereVariable.StrAllWhere) of Sql SELECT statement. It work fine for SELECT statement within VB but if I want to use same conditions for WHERE Clasue of Stored Procedure it gives me Syntax Errors, ANY Help to have WHERE Caluase for SP with multiple conditions as in this code:

If Me.rbtAllCentres.Checked = True Then
GlobalWhereVariable.StrAllWhere = "CentreID > 0"
ElseIf Me.rbtSelectedCentre.Checked = True Then
GlobalWhereVariable.StrAllWhere = "CentreID = " & cboCentreNames.SelectedValue
End If

'Select Case True
If Me.ckbPayDate.Checked = True Then
If Me.rbtAllDates.Checked = True Then
GlobalWhereVariable.StrAllWhere = GlobalWhereVariable.StrAllWhere
ElseIf Me.rbtSelectedDates.Checked = True Then
GlobalWhereVariable.StrAllWhere = GlobalWhereVariable.StrAllWhere & " AND PaymentDate Between '" & txtDateFrom.Text & "' And '" & txtDateTo.Text & "'"
End If
End If

If Me.ckbSemester.Checked = True Then
If Me.rbtAllSemesters.Checked = True Then
GlobalWhereVariable.StrAllWhere = GlobalWhereVariable.StrAllWhere
ElseIf Me.rbtSelectSems.Checked = True Then
GlobalWhereVariable.StrAllWhere = GlobalWhereVariable.StrAllWhere & " AND PaySemester = '" & Me.cboSemester.SelectedItem.ToString() & "'"
End If
End If

If Me.ckbPayNo.Checked = True Then
If Me.btnAllOrderNo.Checked = True Then
GlobalWhereVariable.StrAllWhere = GlobalWhereVariable.StrAllWhere
ElseIf Me.rbtSelectOrderNo.Checked = True Then
GlobalWhereVariable.StrAllWhere = GlobalWhereVariable.StrAllWhere & " AND PaymentOrderNo ='" & Me.txtOrderNo.Text & "'"
End If
End If

If Me.ckbStatus.Checked = True Then
If Me.rbtAllStatus.Checked = True Then
GlobalWhereVariable.StrAllWhere = GlobalWhereVariable.StrAllWhere
ElseIf Me.rbtSelectStatus.Checked = True Then
GlobalWhereVariable.StrAllWhere = GlobalWhereVariable.StrAllWhere & " AND PaymentStatus ='" & Me.cboStatus.SelectedItem.ToString() & "'"
End If
End If

If Me.ckbYear.Checked = True Then
If Me.rbtAllYears.Checked = True Then
GlobalWhereVariable.StrAllWhere = GlobalWhereVariable.StrAllWhere
ElseIf Me.rbtSelectYear.Checked = True Then
GlobalWhereVariable.StrAllWhere = GlobalWhereVariable.StrAllWhere & " AND PayYear = '" & Me.txtYear.Text & "'"
End If
End If

Siam
Starting Member

United Kingdom
5 Posts

Posted - 05/17/2013 :  07:06:39  Show Profile  Reply with Quote
How I can use CASE ... WHEN ... ELSE ... END statement to acomplish the above for Where Cluase with multiple conditions
Go to Top of Page

Siam
Starting Member

United Kingdom
5 Posts

Posted - 05/17/2013 :  07:09:20  Show Profile  Reply with Quote
Sorry Guys , I am new to this Forum & I hope I can find some to help me in this. I amy say I am meduim of using SQL server programing. ANY help will greatly appreciated and will encourag eme to prticipate actively in this Forum. WBREGS to ALL
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 05/17/2013 :  07:48:39  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
this is a pretty typical 'catch all type query'

Gail wrote a really good review:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

My recommendation is to go the dynamic SQL route -- done properly this gives you a good plan for each permutation of your query.

Good Luck,
Charlie.

Transact Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
http://nosqlsolution.blogspot.co.uk/
Go to Top of Page

Siam
Starting Member

United Kingdom
5 Posts

Posted - 05/17/2013 :  08:06:02  Show Profile  Reply with Quote
Thanks Charlie. I will try dynamic SQL route. Will post my findings soon.
Go to Top of Page

Siam
Starting Member

United Kingdom
5 Posts

Posted - 05/20/2013 :  07:39:56  Show Profile  Reply with Quote
Thanks Charlie

I did used the dynamic stored procedure and it works fine for me within SQL server and I can use it for VB.net as Datasource for GridView.
How can I use it as a DataSource for MS ReportViewer becuase it has more than one Parameters.
I am trying different ways but NOT succeeded yet.
ANY help will be appreciated. WBREGS
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 05/20/2013 :  11:05:58  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
Sorry -- don't have any experience with that part of the stack...

I'm sure someone will be along to help though.

Transact Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
http://nosqlsolution.blogspot.co.uk/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000