SQL server stored procedure and VB.Net

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

SQL server stored procedure and VB.Net

Author

Topic

Siam
Starting Member

5 Posts

Posted - 2013-05-17 : 06:58:43

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

5 Posts

Posted - 2013-05-17 : 07:06:39

How I can use CASE ... WHEN ... ELSE ... END statement to acomplish the above for Where Cluase with multiple conditions

Siam
Starting Member

5 Posts

Posted - 2013-05-17 : 07:09:20

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

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2013-05-17 : 07:48:39

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/

Siam
Starting Member

5 Posts

Posted - 2013-05-17 : 08:06:02

Thanks Charlie. I will try dynamic SQL route. Will post my findings soon.

Siam
Starting Member

5 Posts

Posted - 2013-05-20 : 07:39:56

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

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2013-05-20 : 11:05:58

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources