Custom SQL

Hi,

I have a sql statement:

SELECT [ItemName], [Startprice], [Percentreduction], [Quantityavailable], [PhotoURL], [proID] FROM [items] WHERE ([featured] = @.featured)

but I would like to add in 2 more where clauses. One is AND (aswell as) the current one, so

WHERE ([featured] = @.featured) AND ([Quantityavailable > @.Quantityavailable) ?(@.Quantity available value set to 0)(is that right?)

and also I want another AND which is taken from another column. i.e:

WHERE ([featured] = @.featured) AND ([Quantityavailable > @.Quantityavailable) AND ([numberclickedin< *the number from the numtaken column*])

So I guess my 2 questions are:

1. is the format right for my custom sql statements.

2. how do I get the number from the numtaken column to dynamically enter into the third statement?

Thanks,

Jon

SELECT [ItemName], [Startprice], [Percentreduction], [Quantityavailable], [PhotoURL], [proID] FROM [items] WHERE ([featured] = @.featured) AND ([Quantityavailable] > @.Quantityavailable) AND ([numberclickedin] < [numtakenin])

You can use a field just like you use the @.QuantityAvailable parameter value. So one field can be compared against another just fine. I don't see any problem with that query...

|||

Hi

SELECT [ItemName], [Startprice], [Percentreduction],[Quantityavailable], [PhotoURL], [proID]

FROM [items]

WHERE (

([featured]= @.featured) AND

([Quantityavailable > 0) AND -- Comment: You can also say ([Quantityavailable > @.Quantityavailable) or both using AND --

Just not sure what you mean by the last part numclickedin...? I think you probably need Count( A Field Name ) as totalNum. Can you explain a bit more please.

|||

Hi, thank you both for you help!

To bmains: So it is as simple as that, to compare columns against each other you just put them in? You dont have to use extra code or anything to retrieve their data?

To anyone:

How about using data from 2 columns to fill in a third columns value?e.g. numberproducts = ([numberclickedin] *divided by* [numtaken]) ? or adding, subtracting and multiplying?

Thanks,

Jon!

|||

Hey,

Yes, it is that simple, and you can use multiplication and such, just beware of division if the value is zero (to avoid division by zero, you could even add to the where statement "and numtaken <> 0). Actually, you would be surprised how dynamic you can get with queries, when it comes to T-SQL in SQL Server, and PL-SQL in Oracle.

|||

Hi,

Im glad something is simple!

What symbols do I use for multiplication etc? * / + - ?

Thanks,

Jon

|||

Yep, the standard arithmetic operators are the ones you use, plus ( ) in the standard math way.

The [ ] brackets you were using in your first statement were gummed up.

They are used around database names, schema names, table/view names and column names.

They are not used around expressions. Take a look back at your statement and you will see partial [ ] pairs. You have to be careful about that!

Also, they are only needed if you have spaces or weird characters in the name in question, or if you have used a keyword.

My basic rule of thumb is "Don't do that." and everyone's life is simpler.

|||

Thanks!

Jon

Custom SelectCommand

If I hard code the select statement everything works fine.

<asp:SqlDataSourceID="SqlDataSourceZip"runat="server"ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"

SelectCommand="SELECT * FROM [Station] WHERE ([ZipCode] = '80523' OR [ZipCode] = '80521' OR [ZipCode] = '80553' OR [ZipCode] = '80522' OR [ZipCode] = '80526' OR [ZipCode] = '80527' OR [ZipCode] = '80525' OR [ZipCode] = '80524' OR [ZipCode] = '80547' OR [ZipCode] = '80535' OR [ZipCode] = '80538' OR [ZipCode] = '80551' OR [ZipCode] = '80549' OR [ZipCode] = '80550' OR [ZipCode] = '80546' OR [ZipCode] = '80539' OR [ZipCode] = '80512' OR [ZipCode] = '80537' OR [ZipCode] = '80541' OR [ZipCode] = '80650' OR [ZipCode] = '80515' OR [ZipCode] = '80513' OR [ZipCode] = '80610' OR [ZipCode] = '80534' OR [ZipCode] = '80536' OR [ZipCode] = '80634' OR [ZipCode] = '80543' OR [ZipCode] = '80532' OR [ZipCode] = '80638' OR [ZipCode] = '80615' OR [ZipCode] = '80646' OR [ZipCode] = '80648' OR [ZipCode] = '80612' OR [ZipCode] = '80631' OR [ZipCode] = '80528')"></asp:SqlDataSource>

Want to use a label control to return data, but can't find anything that works.

<asp:LabelID="zipLabel"runat="server"></asp:Label>

[ZipCode] = '80523' OR [ZipCode] = '80521' OR [ZipCode] = '80553' OR [ZipCode] = '80522' OR [ZipCode] = '80526' OR [ZipCode] = '80527' OR [ZipCode] = '80525' OR [ZipCode] = '80524' OR [ZipCode] = '80547' OR [ZipCode] = '80535' OR [ZipCode] = '80538' OR [ZipCode] = '80551' OR [ZipCode] = '80549' OR [ZipCode] = '80550' OR [ZipCode] = '80546' OR [ZipCode] = '80539' OR [ZipCode] = '80512' OR [ZipCode] = '80537' OR [ZipCode] = '80541' OR [ZipCode] = '80650' OR [ZipCode] = '80515' OR [ZipCode] = '80513' OR [ZipCode] = '80610' OR [ZipCode] = '80534' OR [ZipCode] = '80536' OR [ZipCode] = '80634' OR [ZipCode] = '80543' OR [ZipCode] = '80532' OR [ZipCode] = '80638' OR [ZipCode] = '80615' OR [ZipCode] = '80646' OR [ZipCode] = '80648' OR [ZipCode] = '80612' OR [ZipCode] = '80631' OR [ZipCode] = '80528'

something like this, but it don't work.

SelectCommand="SELECT * FROM [Station] WHERE (<%=zipLabel.Text%>)"></asp:SqlDataSource>

Like this:

SelectCommand="SELECT * FROM [Station] WHERE [ZipCode]=@.ZipCode">

<SelectParameters>

<asp:ControlParameterControlID="zipLabel"Name="ZipCode"Type="String"PropertyName="Text"/>

</SelectParameters>

But it is common to use a dropdowlist to hold these zipcodes. In this case, you can try something like this:

<SelectParameters>

<asp:ControlParameterControlID="zipDropDownList"Name="ZipCode"Type="String"PropertyName="SelectedValue"/>

</SelectParameters>

|||

Thanks for the response. This is what I got...

Incorrect syntax near '='.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Incorrect syntax near '='.

I'm using a web service and getting zipcodes within X mile radius

Here is zipLabel:

ZipCodes g =newZipCodes();

AuthenticationHeader authHeader =newAuthenticationHeader();

authHeader.SessionID ="AuthNumberHere";

g.AuthenticationHeaderValue = authHeader;

zipLabel.Text ="";

errorLabel.Text ="";

ZipCodeDistances[] zipCodes = g.GetZipCodesWithin(postalCodeTextBox.Text,Convert.ToInt32(radiusTextBox.Text));for (int i = 0; i < zipCodes.Length; i++)

{

ZipCodeDistances zipCode = zipCodes[i];

zipLabel.Text = zipLabel.Text + (zipCode.ZipCode +"' OR [ZipCode] = '");

}

zipLabel.Text ="[ZipCode] = '" + zipLabel.Text + postalCodeTextBox.Text +"'";

So, postal code 02155 with a 3 mile radius displays this in zipLabel.Text

[ZipCode] = '02153' OR [ZipCode] = '02156' OR [ZipCode] = '02144' OR [ZipCode] = '02140' OR [ZipCode] = '02145' OR [ZipCode] = '02474' OR [ZipCode] = '01890' OR [ZipCode] = '02143' OR [ZipCode] = '02155'

Thanks,

|||

You may need to work with array type in your custom select. You need have customized solution for array type operation in SQL Server.

Here is a link for you to read for a through review on this issue http://www.sommarskog.se/arrays-in-sql-2005.html.

And also another one with a split function you can use:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=334436&SiteID=1

Custom Function in SQL Statement?

In MS Access, I could write a function in a module, then just call that function as part of the SQL statement. For example, "SELECT RemoveDashes([SS_No]) AS SSN FROM Employee" with "RemoveDashes" being the name of the function.

I'm trying to do the same with an asp.net page and sql server. I have a custom function in the code behind that I call in the SQL statement, but I get the error, "not a recognized function name".

What do I need to do to make this work?
All help is greatly appreciated!

Lynnetteasp.net and sql server are two different things...you cannot call a vb/c# function in your sql statement...you can write the function in a UDF in your sql server and call it in your sql statement.|||Thanks for your response. I am brand new to SQL Server, but not so new to asp.net. Any ideas how to modify this vb.net function to a UDF?

Thanks again for all your help!
lds

 Function GetWorkStatus(ByVal sU As Object, ByVal sSG As Object) As Integer
 Dim dtEdate As String = Calendar1.SelectedDate.ToShortDateString()
 Dim mydateMonth As Integer = DatePart("m", dtEdate)
 Dim thisDay As String = CStr(DatePart("d", dtEdate))
 Dim sDayOfWeek As String = CStr(DatePart("w", dtEdate))
 Dim cnn As New SqlConnection(constants.SQLConStrFLSA)
 Dim cmd As New SqlCommand("usp_CheckWorkStatus", cnn)
 Try
 With cmd
 .CommandType = CommandType.StoredProcedure
 With .Parameters.Add("@.ForThisDate", SqlDbType.DateTime)
 .Value = dtEdate
 End With
 With .Parameters.Add("@.mydateMonth", SqlDbType.Int)
 .Value = mydateMonth
 End With
 With .Parameters.Add("@.thisDay", SqlDbType.VarChar, 2)
 .Value = thisDay
 End With
 With .Parameters.Add("@.theUnion", SqlDbType.NVarChar, 2)
 .Value = sU
 'Session("sUn") = sU
 End With
 With .Parameters.Add("@.theSG", SqlDbType.NVarChar, 50)
 .Value = sSG
 'Session("strSG") = sSG
 End With
 End With
 cnn.Open()
 Dim da As New SqlDataAdapter(cmd)
 Dim ds As New DataSet
 da.Fill(ds, "SGDetails")
 'no record in FLSAScheduleGroup
 If ds.Tables("SGDetails").Rows.Count = 0 Then
 Select Case sSG
 Case "08"
 If sDayOfWeek = 1 OrElse sDayOfWeek = 7 Then 'Sunday or Saturday
 Return 0
 Else
 Return 1
 End If
 Case "09"
 If sDayOfWeek = 1 OrElse sDayOfWeek = 2 Then 'Sunday or Monday
 Return 0
 Else
 Return 1
 End If
 Case "12"
 If sDayOfWeek = 4 OrElse sDayOfWeek = 5 Then 'Wed or Thurs
 Return 0
 Else
 Return 1
 End If
 Case "13"
 If sDayOfWeek = 5 OrElse sDayOfWeek = 6 Then 'Thurs or Friday
 Return 0
 Else
 Return 1
 End If
 Case "14"
 If sDayOfWeek = 6 OrElse sDayOfWeek = 7 Then 'Friday or Saturday
 Return 0
 Else
 Return 1
 End If
 Case "31"
 If sDayOfWeek = 1 Or 7 Then
 Return 0
 Else
 Return 1
 End If
 Case Else
 Return 1
 End Select
 ElseIf ds.Tables("SGDetails").Rows.Count = 1 Then 'if there is a row in FLSAScheduleGroup
 Return 0
 End If
 Catch ex As Exception
 lblError.Text = ex.ToString
 Finally
 With cmd.Connection
 If .State = ConnectionState.Open Then
 .Close()
 End If
 End With
 Session("sUn") = Nothing
 Session("strSG") = Nothing
 End Try
 End Function

|||what you have seems to be fine although it can be fine-tuned a little bit more..i dont see where you are calling a function in a sql stmt here..|||I wrote the function for the vb.net code behind, and called it from the <ItemTemplate Text=> part of the datagrid.

I have no idea how to make it work in SQL - don't know how to write a UDF.

Thanks again for all your help. Sorry the code is so spaced out - don't know what the "POST-ing" did to it.

Lynnette|||ok you got me confused when you said you are trying to call a function in codebehind from SQL.

what you have seems to be a regular vb function that queries a database to fill a dataset. so what are you trying to do now ? does this function not work ? do you get any errors and if so at which line ? and what is the xact error message ?|||Thanks so much for sticking with me here. I'm the only one at my office that does .NET, so I'm kind of an island here.

The vb.net Function "GetWorkStatus" in the post above works fine. I get the error, "not a recognized function name" when I try to call it from another function, that is using it in the SQL statement to identify values for the specified field. Here is the calling function:

 Private Sub GetTmpRecs()
 'uses the AppSettings table to generate WHERE clause from the filter values
 Dim dtWdate As String = Calendar1.SelectedDate.ToShortDateString()
 Dim cnn As New SqlConnection(constants.SQLConStrFLSA)
 Dim wValue As String = Session("svFilterValue")
 Dim dtEdate As DateTime = Now()
 Dim strUser As String = Session("sUser")
 Dim sSQL As String = "INSERT INTO tmpFLSAEmpInfo "
 sSQL = sSQL & " (Emp_Number, PT_ID, Name_full, Division, Department, Job_Dept_Code, Dept_Mgr, "
 sSQL = sSQL & " DeptInfo, Job_Supervisor, Location, Shift, sUnion, sSG, WrkDate, WrkStatus, HrsWorked, Username, AddDate) SELECT "
 sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.Emp_Number, "
 sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.[ID], "
 sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.Name_Full, "
 sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.Division, "
 sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.Department,"
 sSQL = sSQL & "hcsoSharedTables.dbo.employee2.Job_Dept_Code, "
 sSQL = sSQL & "hcsoSharedTables.dbo.employee2.Dept_Mgr, "
 sSQL = sSQL & "hcsoSharedTables.dbo.employee2.Department + ' - ' + hcsosharedtables.dbo.employee2.Dept_Mgr + ' - ' + hcsosharedtables.dbo.employee2.job_dept_code, "
 sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.Job_Supervisor, "
 sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.Loc_Name, "
 sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.Shift, "
 sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.[Union], "
 sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.Sched_Group, "
 sSQL = sSQL & "'" & dtWdate & "', "
 sSQL = sSQL & "IIF(GetWorkStatus(hcsoSharedTables.dbo.Employee2.[Union], hcsoSharedTables.dbo.Employee2.Sched_Group)=0, 0,1) AS WrkStatus, "
 sSQL = sSQL & "IIF(GetWorkStatus(hcsoSharedTables.dbo.Employee2.[Union], hcsoSharedTables.dbo.Employee2.Sched_Group)=0, 0,1) AS HrsWorked, "
 sSQL = sSQL & "'" & strUser & "', "
 sSQL = sSQL & "'" & dtEdate & "' "
 'sSQL = sSQL & "INTO tmpFLSAEmpInfo "
 sSQL = sSQL & "FROM hcsoSharedTables.dbo.Employee2 " & wValue & ""
 Dim ds As DataSet = New DataSet
 constants.ExecuteSQL(sSQL, constants.SQLConStrFLSA)
 End Sub

|||So I got it right..you were indeed trying to call a vb function into an sql query..I can suggest moving the entire code into a stored proc..move all the logit into it..you can get it all done in one trip.

custom code: [BC30205]

I'm trying to get the following custom code to work but get the error:
"There is an error on line 3 of custom code: [BC30205] End of statement
expected."
I really don't see it, but then I am new to this, so any help would be
appreciated...
Public Shared Function SetBgColor(ByVal Value1 As String, ByVal Value2
As String) As String
If Value1 = "Overdue" And Value2 Like "*" Then
SetBgColor = "DarkGray"
ElseIf Value1 "Open" And Value2 Like "*Antivirus*" Then
SetBgColor = "Silver"
Else
SetBgColor = "White"
End If
End Function
In the Expression for BackgroundColor I have:
=Code.SetBgColor(Fields!Status.Value,Fields!Type.Value)you might try single quotes instead of double quotes
"d4" <d4mann@.gmail.com> wrote in message
news:1154530967.090464.150580@.i42g2000cwa.googlegroups.com...
> I'm trying to get the following custom code to work but get the error:
> "There is an error on line 3 of custom code: [BC30205] End of statement
> expected."
> I really don't see it, but then I am new to this, so any help would be
> appreciated...
> Public Shared Function SetBgColor(ByVal Value1 As String, ByVal Value2
> As String) As String
> If Value1 = "Overdue" And Value2 Like "*" Then
> SetBgColor = "DarkGray"
> ElseIf Value1 "Open" And Value2 Like "*Antivirus*" Then
> SetBgColor = "Silver"
> Else
> SetBgColor = "White"
> End If
> End Function
> In the Expression for BackgroundColor I have:
> =Code.SetBgColor(Fields!Status.Value,Fields!Type.Value)
>|||and a question to you. If I wanted to use something like that, where would
I put it? I am new at this.
"d4" <d4mann@.gmail.com> wrote in message
news:1154530967.090464.150580@.i42g2000cwa.googlegroups.com...
> I'm trying to get the following custom code to work but get the error:
> "There is an error on line 3 of custom code: [BC30205] End of statement
> expected."
> I really don't see it, but then I am new to this, so any help would be
> appreciated...
> Public Shared Function SetBgColor(ByVal Value1 As String, ByVal Value2
> As String) As String
> If Value1 = "Overdue" And Value2 Like "*" Then
> SetBgColor = "DarkGray"
> ElseIf Value1 "Open" And Value2 Like "*Antivirus*" Then
> SetBgColor = "Silver"
> Else
> SetBgColor = "White"
> End If
> End Function
> In the Expression for BackgroundColor I have:
> =Code.SetBgColor(Fields!Status.Value,Fields!Type.Value)
>|||No good.
Public Shared Function SetBgColor(ByVal Value1 As String, ByVal Value2
As String) As String
If Value1 = 'Overdue' And Value2 Like '*' Then
SetBgColor = 'DarkGray'
ElseIf Value1 'Open' And Value2 Like '*Antivirus*' Then
SetBgColor = 'Silver'
Else
SetBgColor = 'White'
End If
End Function
I get - There is an error on line 1 of custom code: [BC30201]
Expression expected.|||You are missing an equals sign after the elseif.
Public Shared Function SetBgColor(ByVal Value1 As String, ByVal Value2
As String) As String
If Value1 = "Overdue" And Value2 Like "*" Then
SetBgColor = "DarkGray"
ElseIf Value1 = "Open" And Value2 Like "*Antivirus*" Then
SetBgColor = "Silver"
Else
SetBgColor = "White"
End If
End Function
d4 wrote:
> I'm trying to get the following custom code to work but get the error:
> "There is an error on line 3 of custom code: [BC30205] End of statement
> expected."
> I really don't see it, but then I am new to this, so any help would be
> appreciated...
> Public Shared Function SetBgColor(ByVal Value1 As String, ByVal Value2
> As String) As String
> If Value1 = "Overdue" And Value2 Like "*" Then
> SetBgColor = "DarkGray"
> ElseIf Value1 "Open" And Value2 Like "*Antivirus*" Then
> SetBgColor = "Silver"
> Else
> SetBgColor = "White"
> End If
> End Function
> In the Expression for BackgroundColor I have:
> =Code.SetBgColor(Fields!Status.Value,Fields!Type.Value)|||Doh! Its always the simple things. Sorry I overlooked it. Hate it when
you stare at something so hard and can't see it! Works now, thanks.|||I dont know enough about these to really be giving advice, but should there
be a space between end and if? Not sure.
"d4" <d4mann@.gmail.com> wrote in message
news:1154534398.002010.56110@.m79g2000cwm.googlegroups.com...
> No good.
> Public Shared Function SetBgColor(ByVal Value1 As String, ByVal Value2
> As String) As String
> If Value1 = 'Overdue' And Value2 Like '*' Then
> SetBgColor = 'DarkGray'
> ElseIf Value1 'Open' And Value2 Like '*Antivirus*' Then
> SetBgColor = 'Silver'
> Else
> SetBgColor = 'White'
> End If
> End Function
> I get - There is an error on line 1 of custom code: [BC30201]
> Expression expected.
>|||It's no problem. I usually just paste the stuff into a VS2003 VB App
and let it tell me where things are wrong (if i can't find it).
Regards,
Dan
d4 wrote:
> Doh! Its always the simple things. Sorry I overlooked it. Hate it when
> you stare at something so hard and can't see it! Works now, thanks.|||If what you show is exactly how your custom code is written, it looks
like you're missing an equal sign between Value1 and "Open" in your
ElseIf line. If that's not it, I don't see a problem (but then again
I'm a SQL guy, not VB). Good luck
toolman
d4 wrote:
> I'm trying to get the following custom code to work but get the error:
> "There is an error on line 3 of custom code: [BC30205] End of statement
> expected."
> I really don't see it, but then I am new to this, so any help would be
> appreciated...
> Public Shared Function SetBgColor(ByVal Value1 As String, ByVal Value2
> As String) As String
> If Value1 = "Overdue" And Value2 Like "*" Then
> SetBgColor = "DarkGray"
> ElseIf Value1 "Open" And Value2 Like "*Antivirus*" Then
> SetBgColor = "Silver"
> Else
> SetBgColor = "White"
> End If
> End Function
> In the Expression for BackgroundColor I have:
> =Code.SetBgColor(Fields!Status.Value,Fields!Type.Value)

Custom code using case statement

My report contains a field which shows a '# of days on Hand' column. This
column is the result of a datediff calc. I am not familar with vb.net but I
need to add custom code logic so that if the value in '# of days on hand" is
null that it shows a hardcoded text like "inv" or "onhand". The rest of the
value are >= 0 and I would just want to show there values as-is. Can anyone
show me what the a sample of code would look like to do this and how I call
this in my report?
ANY HELP IS MUCH APPRECIATEDAn expression would work well in this situation
=iif(Fields!column1.Value < 0,"inv",Fields!column1.Value)
I haven't tested this but it should work. If the expression evaluates to
true, i.e. If the value of the field is less than zero, then the string
'inv' is returned, if false, the field's value is returned as-is.
Put this in your column and replace Fields!column1 with whatever your field
is called.
HTH
"stacey" wrote:
> My report contains a field which shows a '# of days on Hand' column. This
> column is the result of a datediff calc. I am not familar with vb.net but I
> need to add custom code logic so that if the value in '# of days on hand" is
> null that it shows a hardcoded text like "inv" or "onhand". The rest of the
> value are >= 0 and I would just want to show there values as-is. Can anyone
> show me what the a sample of code would look like to do this and how I call
> this in my report?
> ANY HELP IS MUCH APPRECIATED

Cursors vs. Queries

One query statement is much faster than the equivalent
Cursor query.
If I am correct (and please tell me if I'm wrong) its
about 20 times faster than putting the same statement in a
cursor.
However cursors are more controllable, and better for
error checking and error recovery.
My rule of thumb.
If you can get a way with using a query then use it,
however you will need cursors to do anything complex.
J

quote:

---

>--Original Message--
>Can someone give me an example of when I would use a

---

cursor instead of a query

quote:

---

>.
>

---

> however you will need cursors to do anything complex.
Could you give an example of a problem that is too complex for a query
solution?
David Portas
--
Please reply only to the newsgroup
--