• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

Accessing newly converted (from Access) SQL DB for ASP.NET calculator application...

I created a new SQL database based on the old access database we can no longer use - it is a repository of three dates and a number - specifically I converted in access three "date/time" columns to SQL "shortdatetime" columns and the "long int" in access to "int" in SQL.  Then I populated (sucessfully, I assume) the new SQL DB with all of the values for each row (over 1300).

When I made changes to the code to access the new database I get the error...

Exception Details: System.Data.OleDb.OleDbException: Incorrect syntax near '#'.

Source Error:


Line 242:            dim objConnection as new oledbconnection(strConnection)
Line 243:            dim objAdapter as new oledbdataadapter(strSQL, objConnection)
Line 244:            objAdapter.fill(objDataSet, "PreCalculatedValues")
Line 245:            
Line 246:            if objDataSet.tables(0).rows.count > 0 then
 

I am using a web.config to define the connection string (which I believe is correct), and then added this to the code....
<%@ Page language="vb" debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.Oledb" %>
<%@ Import Namespace="System.Configuration" %>
<!--New Addition of Namespace System.Configuration-->
<script language="vb" runat="server">
      
      Dim database As String = "InternalConnect"

And....
'NEW FUNCTION FOR SQL CONVERSION
    ' Retrieves a connection string by name.
    ' Returns Nothing if the name is not found.
    Function GetConnectionString() As String

        ' Assume failure
        Dim returnValue As String = Nothing

        ' Look for the name in the connectionStrings section.
        Dim settings As ConnectionStringSettings = _
           ConfigurationManager.ConnectionStrings(database)

        ' If found, return the connection string.
        If Not settings Is Nothing Then
            returnValue = settings.ConnectionString
        End If

        Return returnValue
    End Function

And then changed the old connections for the Access DB to this for the functions accessing the new SQL DB...

      function GetNextContributionDate(dateProjection as datetime) as datetime
            Dim strConnection As String = GetConnectionString() 'New Code etc...


ANy ideas..?



0
theclassic
Asked:
theclassic
1 Solution
 
Daniel WilsonCommented:
Access queries wrap dates in #, whereas SQL Server queries wrap them in '

that's what I'd be looking for ...
0
 
the_bachelorCommented:
its kinda unclear where the error is happening. But clearly ther is a syntax error...
I have the feeling that it has to do with your connection string.
You should be able to identify that quick.
I'd say post the connection string to your SQL database and someone will prolly tell you whether its valid or not.
0
 
Daniel WilsonCommented:
What is the value of strSQL?

the_bachelor is right there's a syntax error in the SQL ... I suspect it's the SQL in that variable ... and it has to do w/ how you're wrapping a date.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
brad2575Commented:
I would check the data in the database first (if you have direct access) and query the date fields to make sure that "#" was not put in the database.

As DanielWilson said what is the value of the strSQL?  Are you using "#" in the query around the dates?  If so replace them with a single quote (').

If you can post the query from strSQL that would help.
0
 
theclassicAuthor Commented:
I actually had the table named wrong - I was using the name of the old one, but I am still having issues....
The database SQL Table I created in an existing database "Clients" is called Calculator, with four columns PayCalender (shrtdatetime), Cutoff (shrtdatetime), Periods (int) and PayCalender2 (shrtdatetime).
 
The WEb.config is this

<?xml version="1.0"?>
<configuration>
 <configSections> </configSections>

 <appSettings>  

  </appSettings>

 <connectionStrings>
    <add name="ClientConnect" connectionString="Provider=SQLNCLI; Server=DSN ; Database=clients;Uid=user;Pwd=pass;"/>  
  </connectionStrings>
 <system.web>
  </system.web>
</configuration>
 
The asp.net code is here :

<%@ Page language="vb" debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.Oledb" %>
<%@ Import Namespace="System.Configuration" %>
<!--New Addition of Namespace System.Configuration-->
<script language="vb" runat="server">
 
Dim database As String = "ClientConnect"
 
 dim dateProjection as datetime
 dim dateNextContribution as datetime
 dim dateBirth as datetime
 
 dim intAgeInCalendarYear as integer
 dim intPayPeriodsRemaining as integer
 
 dim dblSalary as double
 dim dblCurrentContributionAmount as double
 dim dblMaximumContributionAmount as double
 dim dblMaximumContributionAmountWithCatchUp as double  
 dim dblMaximizedPercentage as double
 dim dblMaximizedPercentageWithCatchUp as double
 dim dblPretaxPayCheckDeduction as double
 dim dblPretaxPayCheckDeductionWithCatchUp as double
 
 dim bolCatchUpAllowed as boolean
 
 sub page_load(sender as object, e as eventargs)
  if not page.isPostBack() then
   InitializeFormFields()
   hideAllPanels()
   pnlIntroduction.visible = true
  end if
 end sub
 
 sub InitializeFormFields()
  txtProjectionDate.text = now.tostring("MM/dd/yyyy")
 end sub
 
 sub onBack1Clicked(sender as object, e as eventargs)
  response.redirect("plan_rules1.html")
 end sub
 
 sub onBack2Clicked(sender as object, e as eventargs)
  hideAllPanels()
  pnlIntroduction.visible = true
 end sub
 
 sub onBack3Clicked(sender as object, e as eventargs)
  hideAllPanels()
  pnlInput.visible = true
 end sub
 
 sub onStartClicked(sender as object, e as eventargs)
  hideAllPanels()
  pnlInput.visible = true
 end sub
 
 sub onCalculateClicked(sender as object, e as eventargs)    
  if isDate(txtProjectionDate.text) then
   'Check the date format (for looks I suppose...) as per client request
   dim arrProjectionDateParts() as string = txtProjectionDate.text.split("/")
   
   if UBound(arrProjectionDateParts) = 2 then  
    dim strProjectionDateMM as string = arrProjectionDateParts(0)
    dim strProjectionDateDD as string = arrProjectionDateParts(1)
    dim strProjectionDateYYYY as string = arrProjectionDateParts(2)
   
    if (len(strProjectionDateMM) <> 2 or len(strProjectionDateDD) <> 2 or len(strProjectionDateYYYY) <> 4) then
     'Show Date of Projection Error
     pnlProjectionDateErrorMessage.visible = true
     txtProjectionDate.backcolor = system.drawing.color.yellow
     exit sub
    else
     'Hide Date of Birth Error
     pnlProjectionDateErrorMessage.visible = false
     txtProjectionDate.backcolor = nothing
    end if
   else
    'Show Date of Projection Error
    pnlProjectionDateErrorMessage.visible = true
    txtProjectionDate.backcolor = system.drawing.color.yellow
    exit sub
   end if
   
   dateProjection = CDate(txtProjectionDate.text)
   
  else
   'Show Date of Projection Error
   pnlProjectionDateErrorMessage.visible = true
   txtProjectionDate.backcolor = system.drawing.color.yellow
   exit sub
  end if
   
  if isNumeric(txtSalary.text) then
   dblSalary = txtSalary.text
   if dblSalary < 0 then
    'Show Salary Error
    pnlSalaryErrorMessage.visible = true
    txtSalary.backcolor = system.drawing.color.yellow
    exit sub
   else
    'Hide Salary Error    
    pnlSalaryErrorMessage.visible = false
    txtSalary.backcolor = nothing
   end if
  else
   'Show Salary Error
   pnlSalaryErrorMessage.visible = true
   txtSalary.backcolor = system.drawing.color.yellow
   exit sub
  end if
 
  if isDate(txtBirthdate.text) then
   'Check the date format (for looks I suppose...) as per client request
   dim arrBirthDateParts() as string = txtBirthDate.text.split("/")
   
   if UBound(arrBirthDateParts) = 2 then  
    dim strBirthDateMM as string = arrBirthDateParts(0)
    dim strBirthDateDD as string = arrBirthDateParts(1)
    dim strBirthDateYYYY as string = arrBirthDateParts(2)
   
    if (len(strBirthDateMM) <> 2 or len(strBirthDateDD) <> 2 or len(strBirthDateYYYY) <> 4) then
     'Show Date of Birth Error
     pnlBirthdateErrorMessage.visible = true
     txtBirthdate.backcolor = system.drawing.color.yellow
     exit sub
    end if
   else
    'Show Date of Birth Error
    pnlBirthdateErrorMessage.visible = true
    txtBirthdate.backcolor = system.drawing.color.yellow
    exit sub
   end if
   
   dateBirth = CDate(txtBirthdate.text)
   if dateBirth < dateProjection then
    intAgeInCalendarYear = dateProjection.year() - dateBirth.year()
    lblAgeInCalendarYear.text = intAgeInCalendarYear
    'Hide Date of Birth Error
    pnlBirthdateErrorMessage.visible = false
    txtBirthdate.backcolor = nothing
   else
    'Show Date of Birth Error
    pnlBirthdateErrorMessage.visible = true
    txtBirthdate.backcolor = system.drawing.color.yellow
    exit sub
   end if
  else
   'Show Date of Birth Error
   pnlBirthdateErrorMessage.visible = true
   txtBirthdate.backcolor = system.drawing.color.yellow
   exit sub
  end if
   
  if isNumeric(txtContributionAmount.text) then
   dblCurrentContributionAmount = txtContributionAmount.text
   if dblCurrentContributionAmount < 0 then
    'Show Contribution Amount Error
    pnlContributionAmountErrorMessage.visible = true
    txtContributionAmount.backcolor = system.drawing.color.yellow
    exit sub
   else    
    dblCurrentContributionAmount = Cdbl(txtContributionAmount.text)
    pnlContributionAmountErrorMessage.visible = false
    'Hide Contribution Amount Error
    txtContributionAmount.backcolor = nothing
   end if
  else
   'Show Contribution Amount Error
   pnlContributionAmountErrorMessage.visible = true
   txtContributionAmount.backcolor = system.drawing.color.yellow
   exit sub
  end if
   
  dateNextContribution = GetNextContributionDate(dateProjection)
  lblNextContributionDate.text = dateNextContribution.toshortdatestring()
 
  intPayPeriodsRemaining = GetPayPeriodsRemaining(dateProjection, dateNextContribution)
  lblRemainingPayPeriods.text = intPayPeriodsRemaining
 
  dblMaximumContributionAmount = GetMaximizedContributionAmount(dateProjection)
  dblMaximizedPercentage = GetMaximizedPercentage(dblSalary, dblCurrentContributionAmount, dblMaximumContributionAmount, intPayPeriodsRemaining)
 
  lblMaximizedPercentage.text = dblMaximizedPercentage * 100 & "%"
 
  dblMaximumContributionAmountWithCatchUp = GetMaximizedContributionAmountWithCatchUp(dateProjection)
  dblMaximizedPercentageWithCatchUp = GetMaximizedPercentage(dblSalary, dblCurrentContributionAmount, dblMaximumContributionAmountWithCatchUp, intPayPeriodsRemaining)
   
  if intAgeInCalendarYear >= 50 then
  'Show Amount Eligible with Catchup
   lblMaximizedPercentageWithCatchUp.text = dblMaximizedPercentageWithCatchUp * 100 & "%"
  else
   'Show Not Eligible for Catchup
   lblMaximizedPercentageWithCatchUp.text = "You are not eligible until age 50"
  end if
 
  dblPretaxPayCheckDeduction = GetPretaxPaycheckDeduction(dblSalary, dblMaximizedPercentage)
  lblPretaxPaycheckDeductionAmount.text = FormatCurrency(dblPretaxPayCheckDeduction)
 
  dblPretaxPayCheckDeductionWithCatchup = GetPretaxPaycheckDeduction(dblSalary, dblMaximizedPercentageWithCatchUp)
 
  if intAgeInCalendarYear >= 50 then
   'Show Amount Eligible with Catchup
   lblPretaxPaycheckDeductionAmountWithCatchUp.text = FormatCurrency(dblPretaxPayCheckDeductionWithCatchup)
  else
   'Show Not Eligible for Catchup
   lblPretaxPaycheckDeductionAmountWithCatchUp.text = "You are not eligible until age 50"
  end if
 
 
  hideAllPanels()
  pnlResults.visible = true
 end sub
 
'NEW FUNCTION FOR SQL CONVERSION
    ' Retrieves a connection string by name.
    ' Returns Nothing if the name is not found.
    Function GetConnectionString() As String
        ' Assume failure
        Dim returnValue As String = Nothing
        ' Look for the name in the connectionStrings section.
        Dim settings As ConnectionStringSettings = _
           ConfigurationManager.ConnectionStrings(database)
        ' If found, return the connection string.
        If Not settings Is Nothing Then
            returnValue = settings.ConnectionString
        End If
        Return returnValue
    End Function

 
 function GetNextContributionDate(dateProjection as datetime) as datetime
  Dim strConnection As String = GetConnectionString() 'New Code
 
  dim strSQL as string = "SELECT TOP 1 PayCalendar2 FROM NomuraCalculator WHERE [K-CutOff] >= #" & dateProjection.toshortdatestring() & "#"
 
  dim objDataSet as new DataSet()    
  dim objConnection as new oledbconnection(strConnection)
  dim objAdapter as new oledbdataadapter(strSQL, objConnection)
  objAdapter.fill(objDataSet, "Calculator")
 
  if objDataSet.tables(0).rows.count > 0 then
   return objDataSet.tables(0).rows(0)("PayCalendar2")
  end if  
 end function
 
 function GetPayPeriodsRemaining(dateProjection as datetime, dateNextContributionDate as datetime) as integer
  Dim strConnection As String = GetConnectionString() ' New Code
 
  dim strSQL as string = "SELECT TOP 1 PayPeriodsRemaining FROM Calculator WHERE [K-CutOff] >= #" & dateProjection.toshortdatestring() & "#"
   IS THE ABOVE LINE LOOK LIKE WHAT YOU WERE MENTIONING>>>
  dim objDataSet as new DataSet()    
  dim objConnection as new oledbconnection(strConnection)
  dim objAdapter as new oledbdataadapter(strSQL, objConnection)
  objAdapter.fill(objDataSet, "Calculator")
 
  if objDataSet.tables(0).rows.count > 0 then
   if dateProjection.toshortdatestring() <> dateNextContributionDate.toshortdatestring() then
    return objDataSet.tables(0).rows(0)("PayPeriodsRemaining") + 1
   else
    return objDataSet.tables(0).rows(0)("PayPeriodsRemaining")
   end if
  end if  
 end function
 
 function GetMaximizedContributionAmount(dateProjection as datetime) as double
  if dateProjection.addDays(14).year = 2005 then
   return 14000
  else if dateProjection.addDays(14).year = 2006 then
   return 15000
  else if dateProjection.addDays(14).year = 2007
   return 15500
  else
   return 15500
  end if
 end function
 
 function GetMaximizedContributionAmountWithCatchUp(dateProjection as datetime) as double
  if dateProjection.addDays(14).year = 2005 then
   return 18000
  else if dateProjection.addDays(14).year = 2006 then
   return 20000
  else if dateProjection.addDays(14).year = 2007
   return 20500
  else
   return 20500
  end if
 end function
 
 function GetMaximizedPercentage(dblSalary as double, dblCurrentContributionAmount as double, dblMaximumContributionAmount as double, intPayPeriodsRemaining as integer) as double
  return math.min(0.7, math.Round((((dblMaximumContributionAmount-dblCurrentContributionAmount)/intPayPeriodsRemaining)/(dblSalary/26)) + 0.005, 2))  
 end function
 
 function GetPretaxPaycheckDeduction(dblSalary as double, dblMaximizedPercentage as double) as double
  return math.round(dblSalary / 26 * dblMaximizedPercentage, 2)
 end function
 
 sub hideAllPanels()
  pnlIntroduction.visible = false  
  pnlInput.visible = false  
  pnlResults.visible = false  
 end sub
</script>
 
I highlighted in Bold some of the few changees, as well as renaming the new table name whereever the old one was mentioned....
0
 
Daniel WilsonCommented:

 dim strSQL as string = "SELECT TOP 1 PayPeriodsRemaining FROM Calculator WHERE [K-CutOff] >= #" & dateProjection.toshortdate
string() & "#"
    IS THE ABOVE LINE LOOK LIKE WHAT YOU WERE MENTIONING>>>
Yes

dim strSQL as string = "SELECT TOP 1 PayPeriodsRemaining FROM Calculator WHERE [K-CutOff] >= '" & dateProjection.toshortdate string() & "'"

Open in new window

0
 
Daniel WilsonCommented:
Congrats, Brad.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now