Question 'Format' is not a recognized built-in function name

[tvb2727]

I have a data grid view in my .net vb application. I moved my database from access to sql server. When I try to load my data grid sql statements I have the following error below. The code is below that. How can I change this to fix the issue?

da.Fill(ds, "tblx") - Line 862

----> 7/28/2012 5:21:57 AM----> System.Data.SqlClient.SqlException (0x80131904): 'Format' is not a recognized built-in function name.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
at CR.Main.load_tickets_grid() in C:\CRsql\CR\Main.vb:line 862

Code:

  Dim conn As SqlClient.SqlConnection
            Dim strConn As String
            Dim da As SqlClient.SqlDataAdapter
            strConn = connectionstring
            conn = New SqlClient.SqlConnection(strConn)
            ds = New DataSet
            '  set_sqlstatements()
            ' MsgBox(gridview_sql)
            da = New SqlClient.SqlDataAdapter(gridview_sql, conn)
            Try
                da.Fill(ds, "tblx")
                'set_datagridwidth()
                DataGridView1.ReadOnly = True
            Catch ex As Exception
                m.errors(show_error, ex.ToString, error_l)
                MessageBox.Show(ex.ToString)
            End Try

 

[Dunfiddlin]

I moved my database from access to sql server.

How? You can't just decide that it's now SQL.

 

[tvb2727]

How? You can't just decide that it's now SQL.

I used 'Microsoft SQL Server Migration Assistant for Access' to move my tables and data. Now I'm trying to change all my connections/data adapters etc from Access to SQL. I think I figured this issue out. It had to do with the way I was formatting the time in the SQL statement.

 

[jmcilhinney]

All flavours of SQL are similar but not the same. Jet SQL, as used by Access, and T-SQL, as used by SQL Server, are different flavours. As such, not all the SQL you used in Access will be valid in SQL Server. This is one such occasion. Your SQL contains a call to a Format function that exists in Jet SQL but not in T-SQL. You need to change that SQL to something that's valid in T-SQL. If you'd like us to help then we'll need to see the SQL.