Access/Sql Server help please (Select statement)

[Jacksparrow87]

Hi people, I was hoping someone out there could help me with a really annoying problem. I have been using Access to connect to my vb applications all my life but last week I got to the stage where I had to change my database - the only real alternative I had was SQL.

So what done was move my Access to SQL, now everything is all working and in place apart from this one select statement, its a pretty big statement so please bear with me.

The coding in Access was

            Dim conn As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=" & Application.StartupPath & "\DBSUP.mdb")
            Dim adapter As New OleDbDataAdapter

            Dim selCommand As New OleDbCommand
            With selCommand
                .CommandText = "Select log.[Company], log.[ChaCode], sum(iif(log.notenumber=1,1,0)) As NumberofProblemsLogged, SUM([tspent]/60) As SumofTimeSpent, SUM(([tspent]/60) * cc.cph )as SumofTotalCost From Log inner join [Cost] as cc on log.ChaCode = cc.[Costcode] where log.Dol BETWEEN ? AND ? And log.CompID = ? Group By log.Company,log.[ChaCode]"
                .CommandType = CommandType.Text
                .Connection = conn
                'Add Parameters 
                .Parameters.Add(New OleDbParameter("@Date1", OleDbType.Date)).Value = dtp.Value
                .Parameters.Add(New OleDbParameter("@Date2", OleDbType.Date)).Value = dtp2.Value
                .Parameters.Add(New OleDbParameter("@CompID", OleDbType.Integer)).Value = cmbcomp.SelectedValue
            End With
            'assign select command to data adapter 
            adapter.SelectCommand = selCommand
            Dim dt As New DataTable("Log")
 
                adapter.Fill(dt)
    
            dglog.DataSource() = dt
            dt.DefaultView.AllowNew = False

The above worked perfect in Access but as ive mentioned before I have changed to SQL so I tried the following:

         Dim conn As New SqlConnection("Data Source= Wk3-dbsrvr;Initial Catalog= dbsup;Integrated Security=SSPI")
            Dim adapter As New SqlDataAdapter


            Dim selCommand As New SqlCommand
            With selCommand
                .CommandText = "Select log.[Company], log.[ChaCode], sum(case log.notenumber when 1 then 1 else 0 end) As NumberofProblemsLogged, SUM([tspent]/60) As SumofTimeSpent, SUM(([tspent]/60) * cc.cph )as SumofTotalCost From Log inner join [Cost] as cc on log.ChaCode = cc.[Costcode] where log.Dol BETWEEN @Date1 AND @Date2 And log.CompID = @CompID Group By log.Company,log.[ChaCode]"
                .CommandType = CommandType.Text
                .Connection = conn
                'Add Parameters 
                .Parameters.Add(New SqlParameter("@Date1", SqlDbType.DateTime)).Value = dtp.Value
                .Parameters.Add(New SqlParameter("@Date2", SqlDbType.DateTime)).Value = dtp2.Value
                .Parameters.Add(New SqlParameter("@CompID", SqlDbType.Int)).Value = cmbcomp.SelectedValue
            End With
            'assign select command to data adapter 
            adapter.SelectCommand = selCommand
            Dim dt As New DataTable("Log")
           
                adapter.Fill(dt)
    
            dglog.DataSource() = dt
            dt.DefaultView.AllowNew = False

When I run the above it throws an error and says:

Line 1:  Incorrect syntax near '='

Ive been stuck on this problem for tiiiime so if anyone could help me out then i would appreciate it, thanks.

 

[RonRepp]

Not positive, but check this:

[Cost] as cc on log.ChaCode = cc

I think you have to put the CC in quotes like this:

'CC'

I hope this helps.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net