Solved

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

Posted on 2008-10-29
7
236 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 16

Accepted Solution

by:
brad2575 earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility

 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
Comment Utility
Congrats, Brad.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This video discusses moving either the default database or any database to a new volume.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now