|
ChrisFarrugia wrote: I have this stored procedure on which I would like to apply if statements at the where clause. But it seems that sql is finding errors associated with the if statements. Can anyone please guide me on what is wrong. Maybe it is not possible to use if statemnts in where clauses?
You can't use IF statements in WHERE clauses. IF statements stand alone and do not form part of another statement (in this case the SELECT statement)
Your best bet is to put the IF statement around the SELECT
IF ((@category<>0) AND @brand=0)
BEGIN
SELECT equipment_id, equipment_modelNumber, equipment_description, category, brand,
link, equipment_small_image, equipment_largr_image
FROM Gen_Equipment
INNER JOIN Gen_Equipment_Categories
INNER JOIN Gen_Equipment_Brand
INNER JOIN Gen_Equipment_Link
ON (Gen_Equipment.equipment_category=Gen_Equipment_Categories.category_id)
ON (Gen_Equipment.equipment_brand=Gen_Equipment_Brand.brand)
ON (Gen_Equipment.equipment_link=Gen_Equipment_Link.link_id)
WHERE Gen_Equipment.equipment_category=@category
ORDER BY Gen_Equipment.equipment_category
END
ELSE
BEGIN
SELECT equipment_id, equipment_modelNumber, equipment_description, category, brand,
link, equipment_small_image, equipment_largr_image
FROM Gen_Equipment
INNER JOIN Gen_Equipment_Categories
INNER JOIN Gen_Equipment_Brand
INNER JOIN Gen_Equipment_Link
ON (Gen_Equipment.equipment_category=Gen_Equipment_Categories.category_id)
ON (Gen_Equipment.equipment_brand=Gen_Equipment_Brand.brand)
ON (Gen_Equipment.equipment_link=Gen_Equipment_Link.link_id)
WHERE equipment_brand=@brand
END
-- Always write code as if the maintenance programmer were an axe murderer who knows where you live.
Upcoming FREE developer events:
* Glasgow: Agile in the Enterprise Vs. ISVs, Mock Objects, SQL Server CLR Integration, Reporting Services, db4o ...
* Reading: SQL Bits
My website
|
|
|
|
|
dear every one
in vb 6 i used listbox.itemdata property to recognize the selected item and its reference data into other tables to fill my controls when i click on one.
please help me that in vb .net how i can deal with this.
i mean that i want to click on a list item for example student name and the rest record should be filled in text boxes on the form.
help plz
|
|
|
|
|
post the quetion in the appropriate forum...
Regards John
|
|
|
|
|
hi,
create following class in your application
Public Class ListItemData<br />
<br />
Dim mstrText As String<br />
Dim mintIndex As Integer<br />
<br />
Public Sub New(ByVal Text As String, ByVal Index As Integer)<br />
mstrText = Text<br />
mintIndex = Index<br />
End Sub<br />
<br />
Public ReadOnly Property Index() As Integer<br />
Get<br />
Return mintIndex<br />
End Get<br />
End Property<br />
<br />
Public Overrides Function ToString() As String<br />
Return mstrText<br />
End Function<br />
<br />
End Class<br />
<br />
now use following code to fill listbox in your app to use above class
str = "select f_id,f_name from facultylist"<br />
result = mdGlobalInstance.GetResultFromQuery(str)<br />
'here result is dataset<br />
For i = 0 To result.Tables(0).Rows.Count - 1<br />
lstFaculty.Items.Add(New ListItemData(result.Tables(0).Rows(i).Item(1), result.Tables(0).Rows(i).Item(0)))<br />
Next
now get selected item of list box as following
dim id as integer<br />
id=CType(LstFaculty.SelectedItem, ListItemData).Index
hope this helps
Rupesh Kumar Swami
Software Engineer,
Integrated Solution,
Bikaner (India)
My Company
|
|
|
|
|
Dear every one...
Hope you all will be fine and read this in good of your health.
I have a problem that what is the best way to save, edit, insert, update etc. my record using a complex form.
PICTURE IS THIS;
I have a form Students.VB
I have many controls on the form as under
Listboxes
Textboxes
Checkboxes
buttons
now i want to know following things....
1. How i will fill my listbox with Student Names using data reader object (Database School is in MS ACCESS and table Student with fields ID,NAME,AGE)
2. how i will fill my comboboxes with CITIES, STATES, COUNTRIES etc.
3. what is the alternate of listbox.itemdata property in vb.net
etc. etc. etc..
please give me a detailed help that can clear my concept.
|
|
|
|
|
in all cases, the answer is the datasource property of hte controls, which will take a datatable, a dataview, an array, you name it
Christian Graus - Microsoft MVP - C++
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
dear friend.
thanks for information.
its ok in all senses i have build. then tell me that if i have a combo box filled with Brand names and behind the scene i am saving the Brand ID for each brand name.
in vb 6, i used dataitem property of combobox to save a brandid with every brand name,
now what i should do with the same contol
thanks
|
|
|
|
|
Use the DisplayProperty and DataProperty properties ( from memory ) to set a displayed and a hidden value. This will not store them in the DB, but it will load them from the DB for you to use with the control.
Christian Graus - Microsoft MVP - C++
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
You may also set your combobox datasource to datable... and set its displaymember and valuemember....
ComboBox1.Datasource = Dataset.Table("DataTable Name")
ComboBox1.ValueMember = "Field Name" -----> Hidden Value
ComboBox1.DisplayMember = "Field Name" -----> Visible Value
|
|
|
|
|
in my appliaction i use ado.net and connect to sql server 2005 . i use the sql server authentication and to connect the db via the application i have to pass the password and uer id with connection string ,
I want to encrypt them and how to do it ..
thanks
|
|
|
|
|
String connectionString;
String user = System.Configuration.ConfigurationSettings.
AppSettings["mappedname"];
String pass = System.Configuration.ConfigurationSettings.
AppSettings["mappedkey"];
connectionString = "data source=myserver;initial catalog=northwind";
connectionString += ";user id=" + user;
connectionString += ";password=" + pass;
sqlConnection1.ConnectionString = connectionString;
String user, pass, connectionString;
user = Microsoft.Win32.Registry.LocalMachine.
OpenSubKey("Software\\Connection").
GetValue("mappedname").ToString();
pass = Microsoft.Win32.Registry.LocalMachine.
OpenSubKey("Software\\Connection").
GetValue("mappedkey").ToString();
connectionString = "data source=myserver;initial catalog=northwind";
connectionString += ";user id=" + user;
connectionString += ";password=" + pass;
sqlConnection1.ConnectionString = connectionString;
Rami Abd alhalim
|
|
|
|
|
Hi this is my query to auto generate one field value...,
select @empId=isnull(max(empId),100)+1 from tbltempemployee
This will generate the number 101,102,...etc
But i want to store the value as L101,L102,...,
It means while auto generate the value i wnat to add one more alphobet letter before that value and store...,
How to do that,
Plz any one hlp me,
Magesh
|
|
|
|
|
set @empID = 'L' + @empID
Christian Graus - Microsoft MVP - C++
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
Hi Magesh,
The case when only one alphabet is used:
select @empId='L' + cast(isnull(max(substring(empId,2,len(empId))),100)+1 as varchar) from tbltempemployee
The above query will take the max of the existing records leaving the first character and will add "L" to it.
Senthil
|
|
|
|
|
halloo ..
i'm newbie in ADO.Net.
i want to ask about trigger in sqlserver2000.
i develop ado apps with c#. my case is ...
client 1 open add employee form, client 2 open list of data employee.
client 1 add employee data. if client 2 doesnt click button refresh, so new data is doesnt appear, right ? so i want sqlserver make the trigger to the client and auto refresh the list data so that the data auto appear in list without user in client 2 click resfresh button or the application make auto refresh with function.
thanks.
thanks vian.
|
|
|
|
|
Please don't cross post.
No, you can't.
Christian Graus - Microsoft MVP - C++
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
you can not do that
Rami Abdalhalim
|
|
|
|
|
so, i still must refresh in form list data ?
|
|
|
|
|
you need to timer at 1 sec refresh data just
Rami Abdalhalim
|
|
|
|
|
hi all,
i have a value entered by user into a text box, i want to search the table for the detail of the entered text.
plz provide me with the syntax to do such thing
Regards
|
|
|
|
|
you need to use LIKE operator to search for a given string.
SELECT Column1,Column2 from Table1 where SearchColumn LIKE '%' + @SearchString + '%'
hope this helps...
Regards John
|
|
|
|
|
this solution if you use string
Select * from tableName where ColumnName = '"+textbox.text+"'
this solution if you use integer
Select * from tableName where ColumnName = '+textbox.text+'
Rami Abdalhalim
|
|
|
|
|
Hi,
Which Rights are needed to create and replace view in oracle, that user already have rights to create view, but it get the insufficient privileges while creating a view.
Best Regards,
Mushq
Mushtaque Ahmed Nizamani
Software Engineer
Ultimus Pakistan
|
|
|
|
|
hi i have java script code for uploading images or files for a joomla component and i am not able to store this images or files in data base tell me the procedure for storing the images in a data base and i don't no data base in depth please let me know its urgent..........
|
|
|
|
|
Sorry, but there is no Access forum.
In a sub-form for detail lines, I'm trying to respond to the entry of a bar-code, using the After Update event of the bar-code combobox, where I set the Product name textbox in the same detail line to the ProductName value in the combobox. I'm sure I've done this before, and it only affects the current record, but now when I update the bar-code, the text the product name textbox in ALL lines gets updated.
Private Sub ddlProduct_AfterUpdate()
txtProductName = ddlProduct.Column(2)
End Sub
I do not believe they are right who say that the defects of famous men should be ignored. I think it is better that we should know them. Then, though we are conscious of having faults as glaring as theirs, we can believe that that is no hindrance to our achieving also something of their virtues. - W. Somerset Maugham
My New Blog
|
|
|
|