[RESOLVED] iF...ELSEIF vs SELECT CASE would make a difference in retrieving rows into datagridvw
I have a Windows Form created with VS2010, that has a datagridview bound to SQL Server table (SQL Server 2008).
The Form1 layout is as follows, Combobox1 with hospital names. It only shows the hospitals assigned to the person that opened the application (I'm using their windows logon but no password for this, I have them in a table in the database). The Combobox2 has the doctors office addresses corresponding to each of the hospitals and lastly, Combobox3 has the patients corresponding to the selection in combobox2 (cascading comboboxes). Then there is a textbox where the user can enter the patient's diagnosis and a button to populate the datagridview below all the above.
Now my boss requested that the users have the ability to just search by
1) hospital (combobox1) only
2) hospital (combobox1) and diagnosis (textbox1)
3) hospital (combobox1) and doctor office (combobox2)
4) hospital (combobox1) and doctor office (combobox2) and diagnosis (textbox1)
5) hospital (combobox1) and doctor office (combobox2) and patient address (combobox3)
6) hospital (combobox1) and doctor office (combobox2) and patient address (combobox3) and diagnosis (textbox1)
I am using an If elsif for all the above and I have only 4 of the scenarios but found that the scenario 1) hangs for 15 and then I have to cancel it. The same occurs with scenario 3).
Scenarios 2), 5) and 6) work flawlessly.
I checked on the sql server with a sp_who and sp_who2 and saw that there was a select runnable and a few sleeping/awaiting command even after my application wasn't running.
1) Should I use a select case instead of the if....elseif? If so, how would I accommodate it to my own code?
2) Upon the deployment of this application, there will be 10 users. Do I need to do anything special for them to be able to use the application concurrently, considering that each of them will have only access to specific rows within the same table? They will also update their own rows.
I really appreciate your time in reading my questions.
Have a good evening!
Tammy
Last edited by jtammyg; Aug 26th, 2015 at 07:20 PM.
Reason: Couldn't add code because of size
Re: iF...ELSEIF vs SELECT CASE would make a difference in retrieving rows into datagr
Originally Posted by techgnome
Sigh...
just post the relevant code here... besure to put it in [code][/code] tags to preserve how it looks.
you'll get better help that way. No everyone has Word (I don't) and I don't feel like download a file that I can't open.
-tg
You can just use LibreOffice it's free and pretty much the same as ms office. His code is too big to post it here so Attached it as a text file that you can view with a notepad here. hiscode.txt
An obscure body in the SK system. The inhabitants call it Earth
Posts
7,902
Re: iF...ELSEIF vs SELECT CASE would make a difference in retrieving rows into datagr
TG is right. By posting a word doc you do yourself no favours because many of us, myself included, won't download it. 1. we may not have a suitable app to open it and, more importantly, 2. it may contain a virus. We ask you to include your code in the posts for good reasons, not just to be inconvenient.
I'm willing to bet the relevant code was short enough. We don't need to see your whole code base, just the bit that's giving you an issue. In your case that's probably just the bit that builds up the sql statement and the bit that issues it to the database.
Anyway, the choice between a Case and an If are really just stylistic and to do with readability rather than performance. They'll almost certainly produce the same IL.
If you want to handle variable paramaters into a search I find the best way to do it is to let the database handle it in the Where clause. Your query should look something like this:-
Code:
Select Blah
From SomeTable
Where (@Hospital is null or Hospital = @Hospital)
And (@DoctorOffice is null or DoctorOffice = @DoctorOffice)
And (@PatientAddress is null or PatientAddress = @PatientAddress)
And (@Diagnosis is null or Diagnosis = @Diagnosis)
Then you simply pass in values for anything the user has entered and nulls for anything they haven't.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
Re: iF...ELSEIF vs SELECT CASE would make a difference in retrieving rows into datagr
Originally Posted by Vexslasher
You can just use LibreOffice it's free and pretty much the same as ms office. His code is too big to post it here so Attached it as a text file that you can view with a notepad here. hiscode.txt
Like the OP, you've made a huge assumption. And like the original post, still doesn't do me any good. Like Funky pointed out, we don't need the whole thing... JUST the relevant parts... if we need more, we'll ask for it. Rarely though do we need the entire code base. If & when we do we'll ask for the whole PROJECT. Meanwhile the text file is as useful to me as the original document.
On a related note, if the code is so huge that it can't be posted in a post... I don't know how any one expects us to wade through it looking for their problem. The VBF doesn't pay me enough to spend that kind of time with someone's code.
Re: iF...ELSEIF vs SELECT CASE would make a difference in retrieving rows into datagr
Since the question is regarding select/case vs if/else, the reality is that there would be no difference, as long as the logical progression is the same.
If it's hanging, it's because the logic is incorrect, or, more likely, the query is incorrect and it's returning far more records than really required.
My suggestion is to step through it and see where it is hanging.
As far as concurrency, generally the database will handle all of that for you (unless you have multiple queries, in which case you would probably run a transaction).
"Ok, my response to that is pending a Google search" - Bucky Katt. "There are two types of people in the world: Those who can extrapolate from incomplete data sets." - Unk. "Before you can 'think outside the box' you need to understand where the box is."
Re: iF...ELSEIF vs SELECT CASE would make a difference in retrieving rows into datagr
Hi Gents!
It's not that it was an inconvenience to post, bu the relevant code where the if ...elseif was is rather huge, which I'm sure it's because I'm putting things that really do not belong there and should be optimized. I do really appreciate your time spent in reading my long rambling.
This is what I have, but seeing for the look of what Funky posted, is totally useless and wrong:
Code:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim connetionString2 As String
Dim connection As SqlConnection
Try
If ComboBox1.SelectedIndex > -1 And ComboBox2.SelectedIndex = -1 And ComboBox3.SelectedIndex = -1 AndTextBox8.Text.Trim.Length > 0 Then
connetionString2 = "server=SV-MYSERVER;Initial Catalog=Assigned_Sites_Calls_Registry;Integrated Security=SSPI"
connection = New SqlConnection(connetionString2)
'sql = "select Member_ID, Acronym_1, Event_Date, PCP_Name, Member_First_Name, Member_Last_Name, " _
'& "Member_DOB ,Member_Sex, member_address, Member_City, Member_State, Member_Zip, " _
'& "MemberPhone ,MemberEmail, Medical_Record___1, InitialLetterSent, MultipleLettersSent, " _
'& "Appointment_Scheduled, Appointment_Scheduled_Status, FollowUpLetterSent, " _
'& "Reschedule_Appointment, Reschedule_Appointment_Status, Comment, " _
'& "Follow_Up_Comment, Updated_By,Updated_Date from MMLIST_FINAL " _
'& "where Hosp_Comm_Name = '" & Trim(ComboBox1.Text) & "' and Acronym_1 = '" & Trim(TextBox8.Text) & "' order by member_address"
sql = "Select * From MMLIST_FINAL where Hosp_Comm_Name = '" & Trim(ComboBox1.Text) & "' and Acronym_1 = '" & Trim(TextBox8.Text) & "' order by member_address"
connection.Open()
adapter = New SqlDataAdapter(sql, connection)
If adapter.Fill(ds) Then
connection.Close()
DataGridView1.DataSource = ds.Tables(0)
DataGridView1.AllowUserToAddRows = False
'add Call Status comboboxcolumn
Dim comboBoxCallSt As New DataGridViewComboBoxColumn
comboBoxCallSt.HeaderText = "Call_Status"
comboBoxCallSt.AutoComplete = False
comboBoxCallSt.Width = 200
comboBoxCallSt.Items.Add("Outreach Done (Successful)")
comboBoxCallSt.Items.Add("Svc Found OnContact (Successful)")
comboBoxCallSt.Items.Add("Unsuccessful")
comboBoxCallSt.Items.Add("Non-Compliant")
comboBoxCallSt.Items.Add("LAB/ENC")
DataGridView1.Columns.Add(comboBoxCallSt)
'add Appointment Status comboboxcolumn
Dim comboBoxApptSt As New DataGridViewComboBoxColumn
comboBoxApptSt.HeaderText = "Appt_Scheduled_Status"
comboBoxApptSt.AutoComplete = False
comboBoxApptSt.Items.Add("Kept Appt")
comboBoxApptSt.Items.Add("No Show Appt")
comboBoxApptSt.Items.Add("Refused Appt")
comboBoxApptSt.Items.Add("Appt Scheduled By Other")
DataGridView1.Columns.Add(comboBoxApptSt)
'add Rescheduled Appointment Status comboboxcolumn
Dim comboBoxReApptSt As New DataGridViewComboBoxColumn
comboBoxReApptSt.HeaderText = "Rescheduled_Appt_Status"
comboBoxReApptSt.AutoComplete = False
comboBoxReApptSt.Items.Add("Kept")
comboBoxReApptSt.Items.Add("No Show")
comboBoxReApptSt.Items.Add("Refused")
comboBoxReApptSt.Items.Add("Scheduled by Other")
DataGridView1.Columns.Add(comboBoxReApptSt)
'resize all columns
DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
DataGridView1.RowHeadersWidthSizeMode = DataGridViewRowHeadersWidthSizeMode.AutoSizeToAllHeaders
'DataGridView1.Columns(44).HeaderText = "Date#1 (numeric)"
'DataGridView1.Columns(44).DefaultCellStyle.Format = "##/##/####"
'Make columns invisible in datagridview
Call makecolsinvisible()
'Modify columns in datagridview
Call modifycolname()
'add row number to the datagridview1
Call setRowNumber(DataGridView1)
'make certain columns writable
Call makecolsread()
'close all connections
Call closeallconn()
Else
MessageBox.Show("Measure not found for that Assigned Site. Press Clear and start again")
End If
ElseIf ComboBox1.SelectedIndex > -1 And ComboBox2.SelectedIndex = -1 And ComboBox3.SelectedIndex = -1 AndTextBox8.Text.Trim.Length = 0 Then
connetionString2 = "server=SV- MYSERVER;Initial Catalog=Assigned_Sites_Calls_Registry;Integrated Security=SSPI"
connection = New SqlConnection(connetionString2)
sql = "select * from MMLIST_FINAL where Hosp_Comm_Name = '" & Trim(ComboBox1.Text) & "' order by member_address"
connection.Open()
adapter = New SqlDataAdapter(sql, connection)
If adapter.Fill(ds) Then
connection.Close()
DataGridView1.DataSource = ds.Tables(0)
DataGridView1.AllowUserToAddRows = False
'add Call Status comboboxcolumn
Dim comboBoxCallSt As New DataGridViewComboBoxColumn
comboBoxCallSt.HeaderText = "Call_Status"
comboBoxCallSt.AutoComplete = False
comboBoxCallSt.Width = 200
comboBoxCallSt.Items.Add("Outreach Done (Successful)")
comboBoxCallSt.Items.Add("Svc Found OnContact (Successful)")
comboBoxCallSt.Items.Add("Unsuccessful")
comboBoxCallSt.Items.Add("Non-Compliant")
comboBoxCallSt.Items.Add("LAB/ENC")
DataGridView1.Columns.Add(comboBoxCallSt)
'add Appointment Status comboboxcolumn
Dim comboBoxApptSt As New DataGridViewComboBoxColumn
comboBoxApptSt.HeaderText = "Appt_Scheduled_Status"
comboBoxApptSt.AutoComplete = False
comboBoxApptSt.Items.Add("Kept Appt")
comboBoxApptSt.Items.Add("No Show Appt")
comboBoxApptSt.Items.Add("Refused Appt")
comboBoxApptSt.Items.Add("Appt Scheduled By Other")
DataGridView1.Columns.Add(comboBoxApptSt)
'add Rescheduled Appointment Status comboboxcolumn
Dim comboBoxReApptSt As New DataGridViewComboBoxColumn
comboBoxReApptSt.HeaderText = "Rescheduled_Appt_Status"
comboBoxReApptSt.AutoComplete = False
comboBoxReApptSt.Items.Add("Kept")
comboBoxReApptSt.Items.Add("No Show")
comboBoxReApptSt.Items.Add("Refused")
comboBoxReApptSt.Items.Add("Scheduled by Other")
DataGridView1.Columns.Add(comboBoxReApptSt)
'resize all columns
DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
DataGridView1.RowHeadersWidthSizeMode = DataGridViewRowHeadersWidthSizeMode.AutoSizeToAllHeaders
DataGridView1.Columns(44).HeaderText = "Date#1 (numeric)"
DataGridView1.Columns(44).DefaultCellStyle.Format = "##/##/####"
'Make columns invisible in datagridview
Call makecolsinvisible()
'Modify columns in datagridview
Call modifycolname()
'add row number to the datagridview1
Call setRowNumber(DataGridView1)
'make certain columns writable
Call makecolsread()
'close all connections
Call closeallconn()
'get current app refresh month into label
Dim strlet As String = "server=SV- MYSERVER;Initial Catalog=Assigned_Sites_Calls_Registry;Integrated Security=SSPI"
Dim conlet As New SqlConnection(strlet)
Dim strlet1 As String = "select Hosp_Comm_Name,count(InitialLetterSent) as lettercount from MMLIST_FINAL where initiallettersent <> '' and Hosp_Comm_Name = '" & ComboBox1.Text & "' group by Hosp_Comm_Name"
Dim dalet As New SqlDataAdapter(strlet1, conlet)
Dim datasetlet As New DataSet()
dalet.Fill(datasetlet, "MMLIST_FINAL")
If datasetlet.Tables(0).Rows.Count > 0 Then
' Bind Label1 to username column of the logn table
Label36.DataBindings.Add("text", datasetlet, "MMLIST_FINAL.lettercount")
Else
Label36.Text = "0"
End If
End If
ElseIf ComboBox1.SelectedIndex > -1 And ComboBox2.SelectedIndex > -1 And ComboBox3.SelectedIndex = -1 AndTextBox8.Text.Trim.Length > 0 Then
connetionString2 = "server=SV- MYSERVER;Initial Catalog=Assigned_Sites_Calls_Registry;Integrated Security=SSPI"
connection = New SqlConnection(connetionString2)
To give you an idea, each of those elseif repeats for each of the scenarios I need to perform. As you noticed, I'm a noob when it comes to programming accordingly. It looks rather messy.
I am going to re-write the query and try to re-organize the code in an appropriate manner.
Thank you so much for your time and help!!!! It's very much appreciated
Re: iF...ELSEIF vs SELECT CASE would make a difference in retrieving rows into datagr
there's a couple ways to approach this:
1) build up the SQL in two phases - first set the select- that's never going to change no matter what the where criteria is... - followed by creating the where clause, then join the two together and execute it.
Code:
Dim SQLSelect As String = "Select * from SomeTable"
Dim SQLWhere as String = String.Empty
If Condition = 2 then
SQLWhere &= " and SomeField = 'somevalue'"
end if
If Condition = 3 then
SQLWhere &= " and SomeField2 = 'anothervalue'"
end if
If Condition = 4 then
SQLWhere &= " and AnotherField = 'randomvalue'"
end if
If SQLWhere <> String.Empty then
SQLStatement = SQLSelect & " where 1=1 " & SQLWhere
else
SQLStatement = SQLSelect
End if
' that last bit could be refactored down a bit more probably
By the end of that you have a complete SQL statement ready to be executed. IT works even if you have additive clauses, say where Condition = 3 and Condition = 4 apply.
2) Use parameters with the dynamic SQL, this is a far better way to go... I notice one of your where clauses is
where Hosp_Comm_Name = '" & Trim(ComboBox1.Text) & "'
If the name is Emanuel Medical Center, you're OK... as it would end up like this:
where Hosp_Comm_Name = 'Emanuel Medical Center'
but let's say it's O'Leary Community Clinic ...
where Hosp_Comm_Name = 'O'Leary Community Clinic'
Now you have a problem because only the "O" is inside the string... the rest falls outside and you end up with an invalid syntax error in your SQL.
And then there's the possibility of SQL Injection attacks - http://xkcd.com/327/
Use parameters is a wise thing to do.
3) It looks like you're using SQL Server ... which means you can use stored procedures. This is even more ideal (in my opinion) than dynamic sql ... you pass it the parameters you have, using the technique that funky posted in post #5 and you've got it. Personally it makes debugging a lot easier because you can setup and run the query in the database directly, and not have to run it through the code first to have it build up the query and then transfer it.
Re: iF...ELSEIF vs SELECT CASE would make a difference in retrieving rows into datagr
Hi Techno!
Is it possible to combine the building up of the query like you did with each of the conditions, but also including the stored procedure like you suggested? If so, how will that look like when I pass the parameters and then view the resutls in the datagridview?
I still need the if else, depending on what the user selects in the cascading comboboxes, correct?
Sorry, but it seems all my code just became useless after reading your answer :-(
Re: iF...ELSEIF vs SELECT CASE would make a difference in retrieving rows into datagr
I suppose... by why would you want to do that?
I'm going to post an example... let's say your search criteria includes, City, State, Zip and Name ... all are optional.
Your sproc would look like this:
Code:
Create Procedure Usp_SearchHospitals
@Name varchar(100) = null,
@City varchar(100) = null,
@State varchar(2) = null,
@Zip varchar(10) = null
as
Select * -- I'd never do this, but this is an example
From Hospitals H -- force of habit, I alias tables
Where
(@Name is null or H.Name = @Name)
and
(@City is null or H.City = @City)
and
(@State is null or H.State = @State)
and
(@Zip is null or H.Zip = @Zip)
That's it...
from the code:
vb.net Code:
Dim results As DataTable = Nothing
Dim connetionString2 As String = "set your conn string here"
'Dim connection As SqlConnection
Using dbConn As New SqlClient.SqlConnection(connetionString2)
Using dbCmd As New SqlClient.SqlCommand("Usp_SearchHospitals", dbConn)
dbCmd.CommandType = CommandType.StoredProcedure ' This is important, this bit me just yesterday. by default, it uses Text as the command type
Using dbAdaptor As New SqlClient.SqlDataAdapter(dbCmd)
dbAdaptor.Fill(results)
End Using
End Using
End Using
'Now you can do what you need to with Results which will have your data in it
the only "logic" is the if operators where I either pass a value or NULL to the sproc... the sproc will then either look for a match (if a value is supplied) or skip the criteria (if NULL was passed in).
Re: iF...ELSEIF vs SELECT CASE would make a difference in retrieving rows into datagr
Hi TG!
I used you example to do what you suggested that looks absolutely great and more compact.
This is the stored proc
Code:
ALTER PROCEDURE [dbo].[sp_HF_populate_DGV_current]
@Hosp_Comm_Name nvarchar(150) = null,
@pcp_address_2 varchar(55) = null,
@member_address varchar(150) = null,
@Acronym_1 varchar(20) = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
Select * From MMLIST_FINAL
where (@Hosp_Comm_Name is null or Hosp_Comm_Name = @Hosp_Comm_Name)
and (PCP_Address_2 is null or PCP_Address_2 = @pcp_address_2)
and (member_address is null or member_address = @member_address)
and (Acronym_1 is null or Acronym_1 = @Acronym_1)
order by member_address
END
and here is my VB.net code
Code:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim results As DataTable = Nothing
Dim connetionString2 As String = "server=SV-MYSERVER;Initial Catalog=Assigned_Sites_Calls_Registry;Integrated Security=SSPI"
Using dbConn As New SqlClient.SqlConnection(connetionString2)
Using dbCmd As New SqlClient.SqlCommand("sp_HF_populate_DGV_current", dbConn)
dbCmd.CommandType = CommandType.StoredProcedure ' This is important, this bit me just yesterday. by default, it uses Text as the command type
dbCmd.Parameters.Add("Hosp_Comm_Name", SqlDbType.NVarChar, 150)
dbCmd.Parameters.Add("pcp_address_2", SqlDbType.VarChar, 55)
dbCmd.Parameters.Add("member_address", SqlDbType.VarChar, 150)
dbCmd.Parameters.Add("Acronym_1", SqlDbType.VarChar, 20)
dbCmd.Parameters("Hosp_Comm_Name").Value = If(ComboBox1.SelectedIndex > -1, ComboBox1.SelectedValue, DBNull.Value)
dbCmd.Parameters("pcp_address_2").Value = If(ComboBox2.SelectedIndex > -1, ComboBox2.SelectedValue, DBNull.Value)
dbCmd.Parameters("member_address").Value = If(ComboBox3.SelectedIndex > -1, ComboBox3.SelectedValue, DBNull.Value)
dbCmd.Parameters("Acronym_1").Value = If(TextBox8.Text.Length > 0, TextBox8.Text, DBNull.Value)
Using dbAdaptor As New SqlClient.SqlDataAdapter(dbCmd)
dbAdaptor.Fill(results)
'dbConn.Close()
ds = New DataSet()
ds.Tables.Add(results)
DataGridView1.DataSource = ds
DataGridView1.DataSource = ds.Tables(0)
DataGridView1.AllowUserToAddRows = False
'add Call Status comboboxcolumn
Dim comboBoxCallSt As New DataGridViewComboBoxColumn
comboBoxCallSt.HeaderText = "Call_Status"
comboBoxCallSt.AutoComplete = False
comboBoxCallSt.Width = 200
comboBoxCallSt.Items.Add("Outreach Done (Successful)")
comboBoxCallSt.Items.Add("Svc Found OnContact (Successful)")
comboBoxCallSt.Items.Add("Unsuccessful")
comboBoxCallSt.Items.Add("Non-Compliant")
comboBoxCallSt.Items.Add("LAB/ENC")
DataGridView1.Columns.Add(comboBoxCallSt)
'add Appointment Status comboboxcolumn
Dim comboBoxApptSt As New DataGridViewComboBoxColumn
comboBoxApptSt.HeaderText = "Appt_Scheduled_Status"
comboBoxApptSt.AutoComplete = False
comboBoxApptSt.Items.Add("Kept Appt")
comboBoxApptSt.Items.Add("No Show Appt")
comboBoxApptSt.Items.Add("Refused Appt")
comboBoxApptSt.Items.Add("Appt Scheduled By Other")
DataGridView1.Columns.Add(comboBoxApptSt)
'add Rescheduled Appointment Status comboboxcolumn
Dim comboBoxReApptSt As New DataGridViewComboBoxColumn
comboBoxReApptSt.HeaderText = "Rescheduled_Appt_Status"
comboBoxReApptSt.AutoComplete = False
comboBoxReApptSt.Items.Add("Kept")
comboBoxReApptSt.Items.Add("No Show")
comboBoxReApptSt.Items.Add("Refused")
comboBoxReApptSt.Items.Add("Scheduled by Other")
DataGridView1.Columns.Add(comboBoxReApptSt)
'resize all columns
DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
DataGridView1.RowHeadersWidthSizeMode = DataGridViewRowHeadersWidthSizeMode.AutoSizeToAllHeaders
I'm getting an error here dbAdaptor.Fill(results). I only selected the Hosp_Comm_Name and left everything blank and the error is
Value cannot be null.
Parameter name: dataTable
The Hosp_Comm_Name cannot be null, since I have it populating as soon as the Form opens, because I only show the assigned hospital to this particular user.
I'm not sure what I'm doing wrong now :-(
I follow all your instructions to the T.
Thanks a lot for your example and help! :-)
Tammy
Last edited by jtammyg; Aug 27th, 2015 at 09:13 PM.
An obscure body in the SK system. The inhabitants call it Earth
Posts
7,902
Re: iF...ELSEIF vs SELECT CASE would make a difference in retrieving rows into datagr
Yep, this is definitely the aproach I'd take. The only thing to add is that it doesn't have to be in a sproc, you can use exactly the same technique building up a direct sql query. Just include the parameter place holders in the sql string exactly as you've done in the sproc. Since you're working against SQL Server then sprocs are a sensible choice but it's worth knowing you're not bound by them.
As for the error message you're getting, it's right but somewhat miss-leading in your case. The parameter it's referring to isn't one of the sql parameters. It's the parameter you're passing to dbAdapter.fill, i.e. results. You've declared results here:-
Code:
Dim results As DataTable = Nothing
but you never actually set it to anything. Change that to:-
Code:
Dim results as DataTable = new DataTable()
and you should be good to go.
edit>also:-
totally useless and wrong
Don't be so hard on yourself. It was neither useless nor wrong. It just wasn't perfect (nothing any of us do ever is) and it needed a bit of debugging.
Last edited by FunkyDexter; Aug 28th, 2015 at 02:32 AM.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
Re: iF...ELSEIF vs SELECT CASE would make a difference in retrieving rows into datagr
Ungh... that was actually my doing... I shot form the hip... I was going to use the .Execute method, which returns a datatable, found that I couldn't switch to the adaptor and forgot to instanciate teh dt before posting the code. I use custom classes day to day, so I sometimes forget what's actually in the framework.
Re: iF...ELSEIF vs SELECT CASE would make a difference in retrieving rows into datagr
Hi TG and Funky!
I will have to revert back to the direct sql query, since the stored proc is giving me issues, i.e. when I use AND, I get no records if I pass Hosp_Comm_Name and pcp_address_2 but don't pass anything to member_address and acronym_1.
If I change it to an OR, and pass those 2 params, I get all the corresponding adresses for that particular hospital, even though I'm passing only a specific pcp_address_2 (it seems this param gets ignore for some reason, which is puzzling me).
How would I change the code to accomodate for my sql which would be
Code:
"Select * From MMLIST_FINAL where (Hosp_Comm_Name is null or Hosp_Comm_Name = @Hosp_Comm_Name) and (Acronym_1 is null or Acronym_1 = @Acronym_1) and (member_address is null or member_address = @member_address) order by member_address"
Thanks a lot again for all your posts and time in helping guys!!!!
Re: iF...ELSEIF vs SELECT CASE would make a difference in retrieving rows into datagr
Ugh... geez... I see why it doesn't work - again, my bad...
The null checks: Hosp_Comm_Name is null ... they should be against the parameters... not the fields.... @Hosp_Comm_Name is null ... @Acronym_1 is null ... @member_address is null .... apparently when I copied/pasted it in the original code I copied the field names and missed the @ on the front.... I remembered it on the equals part of the clause...
Code:
Select * From MMLIST_FINAL
where (@Hosp_Comm_Name is null or Hosp_Comm_Name = @Hosp_Comm_Name)
and (@PCP_Address_2 is null or PCP_Address_2 = @pcp_address_2)
and (@member_address is null or member_address = @member_address)
and (@Acronym_1 is null or Acronym_1 = @Acronym_1)
order by member_address
as for how to make it direct sql... the SQL is exactly the same, the difference is whether it is in the code or in a stored proc... but the logic and the selection SQL is exactly the same.
Re: iF...ELSEIF vs SELECT CASE would make a difference in retrieving rows into datagr
Hi TG!
I finally got it to work!!!! The only other thing I would like to add if that if the user enters an acronym that is not found in the db, where do I put the messagebox.show to let them know "There is no such acronym for your choices". ?
I'm also posting my final code here, in case somebody has a similar setup.
This is the stored proc:
Code:
ALTER PROCEDURE [dbo].[sp_HF_populate_DGV_current]
@Hosp_Comm_Name nvarchar(150) = null,
@pcp_address_2 varchar(55) = null,
@member_address varchar(150) = null,
@Acronym_1 varchar(20) = null
AS
BEGIN
SET NOCOUNT ON;
Select * From MMLIST_FINAL
where (@Hosp_Comm_Name is null or Hosp_Comm_Name = @Hosp_Comm_Name)
and (@PCP_Address_2 is null or PCP_Address_2 = @pcp_address_2)
and (@member_address is null or member_address = @member_address)
and (@Acronym_1 is null or Acronym_1 = @Acronym_1)
order by member_address
end
and this is the code
Code:
Dim results As DataTable = New DataTable()
Dim connetionString2 As String = "server=SV-MYSERVER;Initial Catalog=Assigned_Sites_Calls_Registry;Integrated Security=SSPI"
Using dbConn As New SqlClient.SqlConnection(connetionString2)
Using dbCmd As New SqlClient.SqlCommand("sp_HF_populate_DGV_current", dbConn)
dbCmd.CommandType = CommandType.StoredProcedure ' This is important, this bit me just yesterday. by default, it uses Text as the command type
dbCmd.Parameters.Add("Hosp_Comm_Name", SqlDbType.NVarChar, 150)
dbCmd.Parameters.Add("pcp_address_2", SqlDbType.VarChar, 55)
dbCmd.Parameters.Add("member_address", SqlDbType.VarChar, 150)
dbCmd.Parameters.Add("Acronym_1", SqlDbType.VarChar, 20)
dbCmd.Parameters("Hosp_Comm_Name").Value = If(ComboBox1.SelectedIndex > -1, ComboBox1.SelectedValue, DBNull.Value)
dbCmd.Parameters("pcp_address_2").Value = If(ComboBox2.SelectedIndex > -1, ComboBox2.SelectedValue, DBNull.Value)
dbCmd.Parameters("member_address").Value = If(ComboBox3.SelectedIndex > -1, ComboBox3.SelectedValue, DBNull.Value)
dbCmd.Parameters("Acronym_1").Value = If(TextBox8.Text.Length > 0, TextBox8.Text, DBNull.Value)
Using dbAdaptor As New SqlClient.SqlDataAdapter(dbCmd)
dbAdaptor.Fill(ds)
DataGridView1.DataSource = ds.Tables(0)
DataGridView1.AllowUserToAddRows = False
end using
end using
end using
I want to thank TG and Funky for all your help!!! This forum rocks with experts like you, guys!!!! I appreciate all the time you have put into helping me!!!
Re: iF...ELSEIF vs SELECT CASE would make a difference in retrieving rows into datagr
I wouldn't want that message at all. I would go to great lengths to not allow the user to enter an acronym that doesn't exist. If the set of acronyms is static, then I'd give them a pick list and not let them enter anything. If the set of acronyms is dynamic (new ones get added regularly), then I'd perform a DISTINCT query on the DB to get a pick list. If that list was so large that letting the user select from the list, even using an autocomplete combobox, would be painful, then I'd still hold the list in memory and validate their entry against the list before even attempting the query.
Basically, I'd want the user to know that the acronym was bad at the soonest possible opportunity. Ideally, that would mean that they'd know as they entered it, but at the very least they should know right after they entered it.
Re: iF...ELSEIF vs SELECT CASE would make a difference in retrieving rows into datagr
OK, spoke too soon!!! :-(
When I run the application, if I only select the hospital name from the combobox1, but nothing else, it just hangs. I run the query itself and it takes 3 secs.
I ran the sproc by itself and it ran instantaneously that the time counter in SSMS stayed at 00:00:00.
What else could be causing this issue? I'm really at a loss here. :-(((
Re: iF...ELSEIF vs SELECT CASE would make a difference in retrieving rows into datagr
Just for reference, roughly how many records would be in the table being queried (thousands, millions, or more?), and when you select a hospital name, roughly how many records would you expect to get back?
Re: iF...ELSEIF vs SELECT CASE would make a difference in retrieving rows into datagr
Hi Shaggy!
You are probably right that I should give them a pick list instead of the textbox, but then can I still pass what they select as a param to the sproc?
Also, currently there are 87,000 records in this table. There may be at the most 200,000 at a time, but never any more than that. I have pk in the table and also non clustered and clustered indexes. So it really shouldn't take that long.
When I select this particular hospital I should get 3079 records back.
Re: iF...ELSEIF vs SELECT CASE would make a difference in retrieving rows into datagr
That number of records isn't very many these days, so the cause of the slowdown is not just "it takes a long time to do the search." So, something else is going on. It is possible that it has to do with your connection, but that seems unlikely unless you feel that you already feel your connection is pathetic. Since you haven't mentioned that, I would guess that's not the situation. Stepping through the code would be the next thing to do. Stepping is necessarily slightly slower than normal execution (if only because it takes time to press the buttons), but you should still encounter one line that freezes the program more than the rest, or you should end up going through things over and over. Frankly, I wouldn't be surprised if the Fill took no noticeable time at all, in which case the problem will be easier to solve....but might be harder to find.
An obscure body in the SK system. The inhabitants call it Earth
Posts
7,902
Re: iF...ELSEIF vs SELECT CASE would make a difference in retrieving rows into datagr
How do I step through the code here?
Put a break point in your code. You do this by selecting a line of code, right clicking and then you should see an option that says Set Breakpoint. Now press f5 and your code will run until it hits the break point and then it will stop. You can now step over each line of code by pressing f10. If you want to drill into a line of code(ie into a function call) you press f11.
Fortuitously I noticed VS2015 currently has an Introduction To Debugging article running on the news feed. This is targeted at 2015 but should be mostly applicable to any version of Visual studio:- introduction-to-debugging
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
Re: iF...ELSEIF vs SELECT CASE would make a difference in retrieving rows into datagr
The adding of the datagridviewcomboboxes at the row level seem to be the issue, but I need to have those in order for the user to select per row.
This is the code I have after I ran the sproc :
Code:
DataGridView1.DataSource = ds.Tables(0)
DataGridView1.AllowUserToAddRows = False
''add Call Status comboboxcolumn
'Dim comboBoxCallSt As New DataGridViewComboBoxColumn
'comboBoxCallSt.HeaderText = "Call_Status"
'comboBoxCallSt.AutoComplete = False
'comboBoxCallSt.Width = 200
'comboBoxCallSt.Items.Add("Outreach Done (Successful)")
'comboBoxCallSt.Items.Add("Svc Found OnContact (Successful)")
'comboBoxCallSt.Items.Add("Unsuccessful")
'comboBoxCallSt.Items.Add("Non-Compliant")
'comboBoxCallSt.Items.Add("LAB/ENC")
'DataGridView1.Columns.Add(comboBoxCallSt)
''add Appointment Status comboboxcolumn
'Dim comboBoxApptSt As New DataGridViewComboBoxColumn
'comboBoxApptSt.HeaderText = "Appt_Scheduled_Status"
'comboBoxApptSt.AutoComplete = False
'comboBoxApptSt.Items.Add("Kept Appt")
'comboBoxApptSt.Items.Add("No Show Appt")
'comboBoxApptSt.Items.Add("Refused Appt")
'comboBoxApptSt.Items.Add("Appt Scheduled By Other")
'DataGridView1.Columns.Add(comboBoxApptSt)
''add Rescheduled Appointment Status comboboxcolumn
'Dim comboBoxReApptSt As New DataGridViewComboBoxColumn
'comboBoxReApptSt.HeaderText = "Rescheduled_Appt_Status"
'comboBoxReApptSt.AutoComplete = False
'comboBoxReApptSt.Items.Add("Kept")
'comboBoxReApptSt.Items.Add("No Show")
'comboBoxReApptSt.Items.Add("Refused")
'comboBoxReApptSt.Items.Add("Scheduled by Other")
'DataGridView1.Columns.Add(comboBoxReApptSt)
Basically the user has to choose from these 3 dgvcolumn comboboxes but sometimes they may not need to choose and then those get populated into fields so that they can be inserted into the table when I do the update.
Where should I do this? or maybe there is a better way of doing this?
Re: iF...ELSEIF vs SELECT CASE would make a difference in retrieving rows into datagr
Yes, they are fixed.
I'm putting this code right after the last end using posted above in the search button. Every search now runs between 1 sec and 4 secs for the over 10,000 records, which is great!!!!! :-)
Code:
Dim cmbcallstat As New DataGridViewComboBoxColumn()
cmbcallstat.HeaderText = "Call Status"
cmbcallstat.Name = "cmbcall"
cmbcallstat.MaxDropDownItems = 5
cmbcallstat.Items.Add("Outreach Done (Successful)")
cmbcallstat.Items.Add("Svc Found OnContact (Successful)")
cmbcallstat.Items.Add("Unsuccessful")
cmbcallstat.Items.Add("Non-Compliant")
cmbcallstat.Items.Add("LAB/ENC")
DataGridView1.Columns.Add(cmbcallstat)
Dim cmbApptSt As New DataGridViewComboBoxColumn()
cmbApptSt.HeaderText = "Appt Scheduled Status"
cmbApptSt.Name = "cmbappt"
cmbApptSt.MaxDropDownItems = 4
cmbApptSt.Items.Add("Kept Appt")
cmbApptSt.Items.Add("No Show Appt")
cmbApptSt.Items.Add("Refused Appt")
cmbApptSt.Items.Add("Appt Scheduled By Other")
DataGridView1.Columns.Add(cmbApptSt)
Dim cmbRescApptSt As New DataGridViewComboBoxColumn()
cmbRescApptSt.HeaderText = "Rescheduled Appt Status"
cmbRescApptSt.Name = "cmbappt"
cmbRescApptSt.MaxDropDownItems = 4
cmbRescApptSt.Items.Add("Kept Appt")
cmbRescApptSt.Items.Add("No Show Appt")
cmbRescApptSt.Items.Add("Refused Appt")
cmbRescApptSt.Items.Add("Appt Scheduled By Other")
DataGridView1.Columns.Add(cmbRescApptSt)
I changed them to the above and now everything is running faster, except that my update button now is messed up, with the error 'system.nullreference exception' object reference not set to an instance of an object.
Thanks so much all of you for your help and patience, really lots of patience :-)
Tammy
Last edited by jtammyg; Aug 28th, 2015 at 03:54 PM.
An obscure body in the SK system. The inhabitants call it Earth
Posts
7,902
Re: [RESOLVED] iF...ELSEIF vs SELECT CASE would make a difference in retrieving rows
the error 'system.nullreference exception'
That means that you're accessing a property or method of an object that hasn't been created yet. If you can identify the line of code that's throwing it (either walkthrough the code or set break on all errors = true in your debug settings) it's generally pretty easy to work out what's gone wrong.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
[RESOLVED] iF...ELSEIF vs SELECT CASE would make a difference in retrieving rows into datagridvw
Reply With Quote
Originally Posted by techgnome
