SQL strings and distributed applications

SQL strings and distributed applications

• TonyPat

  I don't get any error message, when you say what kind kind of a statement do you execute - could you give me clue as to what you mean

  Chris

  
  

• MichaelGaertner

  I mean what kind of SQL statement is it
  
  

• kachh

  Val - I have sorted my problem -which in turn has led to another. The reason that the results I wanted from my SQL query didn't work is because the database that I originally used was not on the receipients machine, so the path was therefore meaningless.

  When asked if I want to save a copy of the database in the solution folder should I have said NO. Or should I change the path on the SQL query (on my original project) to reflect where the setup puts the database when it is distributed However this seems very strange to me. Hope this makes some sense. Any help would be appreciated.

  
  

• Manitra

  I would suggest to remove hardcoded path from the code and keep it in a config file. In this case you will not depend on it and can build your connection string on a fly.
  
  

• vurdlak

  It means that date values are not passed to the query. They are blank. You need to check if user actually put value in a textboxes first and then check if they actually put date using IsDate function
  
  

• rseeders

  Private Sub saleenter()

  Dim strConnectionString As String = _

  "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\angelbackoffice\angelbackend.mdb"

  Dim objConnection As New OleDbConnection(strConnectionString)

  Dim strSQL As String = _

  "SELECT sum(amount) as totalsalesthisFY from sales where [date] >= #" & Me.startdate.Text & "# AND [date] < #" & Me.enddate.Text & "#"

  Dim objCommand As New OleDbCommand(strSQL, objConnection)

  Dim objDataAdapter As New OleDbDataAdapter(objCommand)

  Dim objDataTable As New Data.DataTable("sales")

  Dim objDataRow As DataRow

  Try

  'Open the database connection

  objConnection.Open()

  'Fill the DataTable object

  objDataAdapter.Fill(objDataTable)

  'Load the list box on the form

  For Each objDataRow In objDataTable.Rows

  TextBox6.Text = "￡" & Format(objDataRow.Item("totalsalesthisFY"), "standard")

  Next

  Catch OleDbExceptionErr As OleDbException

  'Write the exception

  Debug.WriteLine("Sorry not enough data")

  End Try

  'Close the database connection

  objConnection.Close()

  'Clean up

  objDataRow = Nothing

  objDataTable.Dispose()

  objDataTable = Nothing

  objDataAdapter.Dispose()

  objDataAdapter = Nothing

  objCommand.Dispose()

  objCommand = Nothing

  objConnection.Dispose()

  objConnection = Nothing

  
  

• Dmitry Spitsyn

  Do you get any error message What kind of statement do you execute
  
  

• koranke

  Dear Val

  Thank you for the time you have taken to help me, my problem is that I need the end user to be able to select dates so that they can view sales from different years i.e. go back a year and so on. How would a parameterized query help

  I am sure you are right though because I get this message in the immediate window

  " A first chance exeption of type 'system.Data.OleDb.OleDbexception' occured in System.Data.dll Syntax error in date in query experssion '[date]>= ## AND [date] <##'.

  Which means nothing to me, although it doesn't prevent the sloution from running.

  
  

• CraigAP

  Are you sure that format of the passed dates is the same I would suggest to use parameterized query in this case to avoid potential formatting issues
  
  

• DineshSharma

  Thank you - its all starting to make sense now. Your help is appreciated.
  
• SQL strings and distributed applications