Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Database connection to SQL Database

Status
Not open for further replies.
tmishue

tmishue

Programmer
Jun 26, 2001
35
0
0
US
Hey Everyone,
Need help with this database connection in ASP. I am getting the following error on the line that is in bold below: An exception of type 'Microsoft OLE DB Provider for ODBC Drivers:[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'calendarinfotable'.'was not handled.

My SQL database table's name is calendarinfotable and my recorded is not being added. I can successfully run the SQL Statement in SQL and I can see from writing it to the screen that all the variables are being passed correctly from my form.

This is the first time that I have connected to a database this way so I am probably doing something wrong there. Can someone please help?

Thanks tmishue


Code: 
<BODY>
<%
internala = Request.Form(&quot;internalh&quot;)
froma = Request.Form(&quot;fromh&quot;)
categorya = Request.Form(&quot;categoryh&quot;)
locationa = Request.Form(&quot;locationh&quot;)
rooma = Request.Form(&quot;roomh&quot;)'
subjecta = Request.Form(&quot;subjecth&quot;)
personala = Request.Form(&quot;personalh&quot;)
toemaila = Request.Form(&quot;toemailh&quot;)
startdatea = Request.Form(&quot;startdateh&quot;)
starttimea = Request.Form(&quot;starttimeh&quot;)
enddatea = Request.Form(&quot;enddateh&quot;)
endtimea = Request.Form(&quot;endtimeh&quot;)

Set CCSCal=Server.CreateObject(&quot;ADODB.Connection&quot;)
CCSCal.ConnectionTimeout=40
CCSCal.Open &quot;DSN=CCSCalendarSQL&quot;

sqlStatement = &quot;INSERT INTO calendarinfotable(DepartmentSchool, Category, Subject, Room, Location, StartDate, StartTime, EndDate, EndTime, Intranet, Toemail, Personal) VALUES ('&quot;& cstr(froma) & &quot;','&quot;& cstr(categorya) &&quot;', '&quot;& cstr(subjecta) &&quot;', '&quot;& cstr(rooma) &&quot;', '&quot;& cstr(locationa) &&quot;', '&quot;& cstr(startdatea) &&quot;', '&quot;& cstr(starttimea) &&quot;', '&quot;& enddatea &&quot;', '&quot;& cstr(endtimea) &&quot;', '&quot;& cstr(internala) &&quot;', '&quot;& cstr(toemaila) &&quot;', '&quot;& cstr(personala) &&quot;')&quot;
Response.Write(sqlStatement)
Set recordSet=CCSCal.Execute(sqlStatement)
Code: 
CCSCal.Close
Set CCSCal=Nothing
Response.Write(&quot;Appointment Added!&quot;)
%>
 
Sort by date Sort by votes
You can't set a recordset in that way. The reason is that your query will return nothing, so the recordset will have nothing.

Instead, since you are just doing an insert, use:
CCSCal.Execute(sqlStatement)

without the Set recordset.

Other than that, assuming that your sql statement is correct, there should be no other problems. If there are, post back...

hth
leo

oh yeah -
you can use conn.execute to open a recordset, in order to do it you would need to use a &quot;SELECT .... FROM ...&quot; query... that will work.
 
Upvote 0 Downvote
Still get the same error when running debug and my record is still not added.

Here's what I did changing the bolded line as you suggested:
Code: 
Set CCSCal=Server.CreateObject(&quot;ADODB.Connection&quot;)
CCSCal.ConnectionTimeout=40
CCSCal.Open &quot;DSN=CCSCalendarSQL&quot;

sqlStatement = &quot;INSERT INTO calendarinfotable(DepartmentSchool, Category, Subject, Room, Location, StartDate, StartTime, EndDate, EndTime, Intranet, Toemail, Personal) VALUES ('&quot;& cstr(froma) & &quot;','&quot;& cstr(categorya) &&quot;', '&quot;& cstr(subjecta) &&quot;', '&quot;& cstr(rooma) &&quot;', '&quot;& cstr(locationa) &&quot;', '&quot;& cstr(startdatea) &&quot;', '&quot;& cstr(starttimea) &&quot;', '&quot;& enddatea &&quot;', '&quot;& cstr(endtimea) &&quot;', '&quot;& cstr(internala) &&quot;', '&quot;& cstr(toemaila) &&quot;', '&quot;& cstr(personala) &&quot;')&quot;
Response.Write(sqlStatement)

CCSCal.Execute(sqlStatement)
Code: 
CCSCal.Close
Set CCSCal=Nothing
My SQL Statement works in SQL Query Analyzer. I copied the statement written out to the screen and pasted it in the Query Analyzer and ran it and the record was added so my statement works, I don't know what I'm doing wrong in the connection though.

Thanks for the prompt response and help.

tmishue
 
Upvote 0 Downvote
does your DSN work in other pages? If not, it might be a problem with your DSN. Check it to make sure that it's pointing towards the right DB and what not.

hth ---------
Leo Mendoza
lmendoza@students.depaul.edu
 
Upvote 0 Downvote
I know this is trivial, but when i copied your code into notepad, i noticed that there is no space between calendarinfotable(DepartmentSchool this, as far as I know, is wrong. it should be
calendarinfotable (DepartmentSchool

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
Parse a database field that is comma delimited and input the results into another table
Replies
0
Views
77
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
How to send HTML emails with ASP classic?
Replies
0
Views
100
leifoet
leifoet
bslintx2021
  • Locked
  • Question
How to configure IIS 10 server to allow Classic ASP to connect to MSSQL server database on a domain
Replies
1
Views
217
penguinspeaks
penguinspeaks
penguinspeaks
  • Locked
  • Question
Return to current location on page after asp processes.
Replies
2
Views
93
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
How to update the content of radio buttons in a database?
Replies
2
Views
80
leifoet
leifoet

Part and Inventory Search

Sponsor

Back
Top