| Author |
Topic |
|
kenone
Starting Member
14 Posts |
 Posted - 2006-02-20 : 07:51:16
|
| I am bit new to vb.net programming,please bear with me.....I have developed an application in vb.net that retrieve records from a Sql Server.I just want to search the database using two parameters either surname or IDNumber.At the moment I could only manage to search using one of the fields ie IDNumber.How can I make it search using either IDNumber or surname???Below is my code but doesnt work out,it gives an error message with a highlight pointer on the statement "dr = selcom.ExecuteReader"[[CODE]Dim selcom As New SqlCommand() Dim dr As SqlDataReader Dim message As String Dim selected As SqlParameter selcom.Connection = SqlConnection1 selcom.CommandText = "select * from members where( checkno = " & txtCheckNo.Text & " or surname = '&txtSurname.text&' )" SqlConnection1.Open() dr = selcom.ExecuteReader If dr.Read Then txtIDNumber.Text = dr.GetValue(0) txtSurname.Text = dr.GetValue(1) txtOName.Text = dr.GetValue(2) txtEmpStatus.Text = dr.GetValue(3) txtDOB.Text = dr.GetValue(4)[/CODE]............................Note that a surname isnt a primary key hence there could be more than one records of the same surname and it should be possible to retrieve all of them.I hope I will have a promising support from this forum..Thanks & regards |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-02-20 : 16:51:03
|
Try this :"select * from members where( checkno = " & txtCheckNo.Text & " or surname = '" & txtSurname.text & "' )" ----------------------------------'KH'Time is always against us |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-02-21 : 02:10:27
|
| Also use msgbox to know if it is valid select statementMadhivananFailing to plan is Planning to fail |
|
|
|
kenone
Starting Member
14 Posts |
Posted - 2006-02-21 : 02:20:13
|
| well when I used"select * from members where( checkno = " & txtCheckNo.Text & " or surname = '" & txtSurname.text & "' )" . I could only search using check number and not surname.When I search by using a surname I got the same error as earlier .Surprisingly,I could only retrieve records using surname when I ommited the checkno part. ie without an "or" ie."select * from members where( surname = " &txtSurname.Text & " )".Is there anything wrong? |
|
|
|
solent
Starting Member
33 Posts |
Posted - 2006-02-21 : 02:48:44
|
i am not an expert myself but i would suggest you to use a stored procedure for your query with parameters and then pass those parameters from your vb.net application. This is a much better approach when executing sql queries from vb.net. i dont have a code example for vb.net but i do have for c#. it shouldn't be that difficult to convert to vb.net. public string connString = ConfigurationManager.ConnectionStrings["ConnectionStringNameFoundInYourAppConfig"].ConnectionString; //Create a SqlConnection instance using (SqlConnection myConnection = new SqlConnection(connString)) { SqlCommand Command = new SqlCommand("usp_SprocName", myConnection); Command.CommandType = CommandType.StoredProcedure; Command.Parameters.Add(new SqlParameter("@checkno", SqlDbType.int)); Command.Parameters["@checkno"].Value = txtCheckNo.Text; Command.Parameters.Add(new SqlParameter("@surname", SqlDbType.VarChar, 100)); Command.Parameters["@surname"].Value = txtSurname.Text; try { if (myConnection.State == 0) { myConnection.Open(); } Command.ExecuteNonQuery(); } catch (SqlException ex) //catch any errors and display it on a label. it can also be an msg box. { lblStatus.Text = ex.Message; } finally { if (myConnection.State == ConnectionState.Open) { myConnection.Close(); } } }your sproc may look something like this.create procedure dbo.usp_SelMembers@checkno int,@surname varchar (100)ASBEGIN SET NOCOUNT ON;select * from members cwhere c.checkno = @checkno or c.surname = @surnameEND; you can also use the like operator for the surname parameter. |
|
|
|
kenone
Starting Member
14 Posts |
Posted - 2006-02-21 : 04:30:11
|
| Thanks for your inputs....it is now working fine after putting single quotes in both search fields '"txtcheckno.text"' and '"txtsurname.text"'.....But incase of searching using surname I still need to retrieve all records, say, with the same surname and be able to display them all...is there any mechanism to do this??? |
|
|
|
kenone
Starting Member
14 Posts |
Posted - 2006-02-21 : 04:30:17
|
| Thanks for your inputs....it is now working fine after putting single quotes in both search fields '"txtcheckno.text"' and '"txtsurname.text"'.....But incase of searching using surname I still need to retrieve all records, say, with the same surname and be able to display them all...is there any mechanism to do this??? |
|
|
|
shallu1_gupta
Constraint Violating Yak Guru
394 Posts |
Posted - 2006-02-21 : 04:46:05
|
| Is this not working??"select * from members where checkno = '" & txtCheckNo.Text & " ' or surname = '" & txtSurname.text & "' " |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-02-21 : 07:08:22
|
| Use parameters! |
|
|
|
kenone
Starting Member
14 Posts |
Posted - 2006-02-21 : 07:20:56
|
| Shallu it is working now thanks.jsmith what do you mean?? |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-02-21 : 08:22:03
|
| What happens to your code when the surname of O'Brien is passed in?you should never be concatenat strings together with user input and execute them. You should be using parameterized queries (or stored procedures) and setting those parameters using a standard ADO.NET command object. You either declare the parameters in your stored procedure, or you simply put @variable placeholders in your SQL statement.See solent's post for one example.Since so many people seem to be writing code in ADO.NET these days who do not use parameters, I am going to try to put up a blog post on it within the next fews days and I'll put a link to it here. |
|
|
|
kenone
Starting Member
14 Posts |
Posted - 2006-02-21 : 08:44:19
|
| ok jsmith got your point..thanks |
|
|
|
|
parameterised query - urgent