Solved

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

Posted on 2008-10-29
7
238 Views
Last Modified: 2011-10-03
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
Comment
Question by:theclassic
7 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22833878
Access queries wrap dates in #, whereas SQL Server queries wrap them in '

that's what I'd be looking for ...
0
 
LVL 7

Expert Comment

by:the_bachelor
ID: 22833894
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
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22833950
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 16

Accepted Solution

by:
brad2575 earned 500 total points
ID: 22834305
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
 

Author Comment

by:theclassic
ID: 22834343
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
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22834499

 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
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22834658
Congrats, Brad.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question