Query Issues Pt.3

Query Issues Pt.3

• Dasgooch

  First step, see if anything is seen when you run profiler and watch the query being sent. It might not be seen if there is a syntax error though.

  Step two is to print out the value in cboFindClients.Text. Make sure it doesn't include any wierd characters. I usually would do something like:

  msgbox( "*" + cboFindClients.text + "*")

  Or print it. Of course I have programmed in UI land in forever :)

  If that looks fine, take the select statement from your call and print it out (others have suggested this too):

  "SELECT tblWatchInstance.WatchID,tblWatchBrands.Description,tblWatches.Serial_Number,tblWatches.OwnerFName,tblWatches.OwnerLName, tblClients.Name FROM tblWatchInstance INNER JOIN tblWatches ON tblWatchInstance.Watch_Key = tblWatches.WATCH_KEY INNER JOIN tblWatchColors ON tblWatches.COLOR_KEY = tblWatchColors.COLOR_KEY INNER JOIN tblWatchBrands ON tblWatches.BRAND_KEY = tblWatchBrands.BRAND_KEY INNER JOIN tblWatchTypes ON tblWatches.WATCHTYPE_KEY = tblWatchTypes.WatchType_KEY INNER JOIN tblClients ON tblWatchInstance.Instance_Key = tblClients.CLIENT_KEY WHERE tblWatchInstance.RepairNumber LIKE '%" + txtRepairNumber.Text + "%' AND tblWatches.OwnerLName LIKE '%" + txtOwnersName.Text + "%' AND tblWatches.Serial_Number LIKE '%" + txtSerialNo.Text + "%' AND tblWatchBrands.Description LIKE '%" + cboBrand.Text + "%' AND tblWatchColors.Description LIKE '%" + cboColor.Text + "%' AND tblWatchTypes.Description LIKE '%" + cboType.Text + "%' AND tblClients.Name LIKE '%" + cboFindClients.Text + "%'"

  Then run the output in Query Analyzer or Management Studio.

  One suggestion. Do you do each type of search alone If so, you might want to vary the where clause to only include the columns that you are actually searching on.

  
  
  

• D0minat0r

  Right now you are building the string inside the new SqlDataAdapter, instead build the string outside and then pass that string into the creation of the SqlDataAdapter instead. Look at the first response for the sample code.

  
  

• Steve1999

  Debugging suggestion. Try building the string and then displaying the sql that it creates for approval before trying to build sqlAdapter from it. That will help you narrow the problem to interface or database.

  Security suggestion. Building the SQL client side is vulnerable to sql injection. Building a stored procedure and passing your variables could provide a bit of security.

  string commandString = "SELECT tblWatchInstance.WatchID,tblWatchBrands.Description,tblWatches.Serial_Number,tblWatches.OwnerFName,tblWatches.OwnerLName, tblClients.Name FROM tblWatchInstance INNER JOIN tblWatches ON tblWatchInstance.Watch_Key = tblWatches.WATCH_KEY INNER JOIN tblWatchColors ON tblWatches.COLOR_KEY = tblWatchColors.COLOR_KEY INNER JOIN tblWatchBrands ON tblWatches.BRAND_KEY = tblWatchBrands.BRAND_KEY INNER JOIN tblWatchTypes ON tblWatches.WATCHTYPE_KEY = tblWatchTypes.WatchType_KEY INNER JOIN tblClients ON tblWatchInstance.Instance_Key = tblClients.CLIENT_KEY WHERE tblWatchInstance.RepairNumber LIKE '%" + txtRepairNumber.Text + "%' AND tblWatches.OwnerLName LIKE '%" + txtOwnersName.Text + "%' AND tblWatches.Serial_Number LIKE '%" + txtSerialNo.Text + "%' AND tblWatchBrands.Description LIKE '%" + cboBrand.Text + "%' AND tblWatchColors.Description LIKE '%" + cboColor.Text + "%' AND tblWatchTypes.Description LIKE '%" + cboType.Text + "%' AND tblClients.Name LIKE '%" + cboFindClients.Text + "%'"

  MessageBox.Show(commandString) or txtSQLCommand.Text = commandString

  then take your string into a Query window and make sure it executes then finally

  myCommand = New SqlDataAdapter(commandString, myConnection)
  

  
  

• Bad Bad Bad Bad Ben

  This may work but it seems that "myCommand" can not be converted to string Is there any other way i can do this

   

  If txtRepairNumber.Text.Trim = "" And txtSerialNo.Text.Trim = "" And txtStyleDial.Text = "" And txtOwnersName.Text.Trim = "" And cboRepairCat.Text.Trim = "" And cboBrand.Text.Trim = "" And cboType.Text.Trim = "" And cboColor.Text.Trim = "" And cboFindClients.Text = "" Then

  MessageBox.Show("You must enter search criteria.", "Watch LookUp")

  txtRepairNumber.Focus()

  Else

  Dim myConnection As SqlConnection

  Dim myCommand As SqlDataAdapter

  myConnection = New SqlConnection(ConnectionString)

  Dim ds As DataSet = New DataSet

  myCommand = New SqlDataAdapter("SELECT tblWatchInstance.WatchID,tblWatchBrands.Description,tblWatches.Serial_Number,tblWatches.OwnerFName,tblWatches.OwnerLName, tblClients.Name FROM tblWatchInstance INNER JOIN tblWatches ON tblWatchInstance.Watch_Key = tblWatches.WATCH_KEY INNER JOIN tblWatchColors ON tblWatches.COLOR_KEY = tblWatchColors.COLOR_KEY INNER JOIN tblWatchBrands ON tblWatches.BRAND_KEY = tblWatchBrands.BRAND_KEY INNER JOIN tblWatchTypes ON tblWatches.WATCHTYPE_KEY = tblWatchTypes.WatchType_KEY INNER JOIN tblClients ON tblWatchInstance.Instance_Key = tblClients.CLIENT_KEY WHERE tblWatchInstance.RepairNumber LIKE '%" + txtRepairNumber.Text + "%' AND tblWatches.OwnerLName LIKE '%" + txtOwnersName.Text + "%' AND tblWatches.Serial_Number LIKE '%" + txtSerialNo.Text + "%' AND tblWatchBrands.Description LIKE '%" + cboBrand.Text + "%' AND tblWatchColors.Description LIKE '%" + cboColor.Text + "%' AND tblWatchTypes.Description LIKE '%" + cboType.Text + "%' AND tblClients.Name LIKE '%" + cboFindClients.Text + "%'", myConnection)

  When i select a Client from cboFindClients.Text or should it be Selected....it throws a System error exception could it be the way my tables are inner joined  

  myCommand.Fill(ds)

  dgshow.Visible = True

  dgshow.DataSource = ds.Tables(0)

  End If

  
  
  
• Query Issues Pt.3