<<Error converting data type char to smalldatetime>>

[Gary D. Rezek]

Hi All (IIS 5, SQL 2k, Win 2k)
I'm stumped and can't find where I would be "mis-converting" data.
I receive the following error ONLY when I update to or insert a null value into a smalldatetime field in SQL.
<<Error converting data type char to smalldatetime>>
Following are the relevant bits of code. It happens with either ConnectionDate or CompletionDate (they are both setup the same)
Everything seems to line-up in order as far as the fields and stored proc variables and again if a date is used I do not receive
the error and everything updates or inserts as it should.
What am I missing?
*********************************************
If Len(Request.Form("txtConnectionDate")) <> 0 Then
strConnectionDate = FormatDateTime(Request.Form("txtConnectionDate"))
Else
strConnectionDate = "Null"
End If
****
Set rstUpdateConn = Server.CreateObject("ADODB.Recordset")
cnMain.spUpdateStudentConnectionRecord intConnectionID, StuID,
strFirstName, strLastName, strRoomCode, strEMailAddress,
strCellPhoneNumber, strConnectionDate, strConnectionTerm,
strConnectionYear, strConnectionDefinition, strCompletionDate,
strNotesOnStudent, strBestTimeToContact, boolUserAgreeSigned, rstUpdateConn
****
CREATE PROCEDURE dbo.spUpdateStudentConnectionRecord
@ConnectionID int
,@StudentIDNumber char(7)
,@FirstName varchar(50)
,@LastName varchar(50)
,@RoomCode varchar(50)
,@EmailAddress varchar(50)
,@CellPhoneNumber varchar(14)
,@ConnectionDate smalldatetime
,@ConnectionTerm varchar(6)
,@ConnectionYear varchar(4)
,@ConnectionDefinition varchar(50)
,@CompletionDate smalldatetime
,@NotesOnStudent varchar(255)
,@BestTimeToContact varchar(255)
,@UserAgreeSigned bit

AS

SET NOCOUNT ON

BEGIN TRANSACTION

UPDATE tblConnectionInformation
SET ConnectionDate = @ConnectionDate,
ConnectionTerm = @ConnectionTerm,
ConnectionYear = @ConnectionYear,
ConnectionDefinition = @ConnectionDefinition,
CompletionDate = @CompletionDate,
UserAgreeSigned = @UserAgreeSigned,
BestTimeToContact = @BestTimeToContact
WHERE ConnectionID = @ConnectionID

If @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN
END
<<2 more inserts>>
*********************************************

Thank you.
gdr
--
Gary D. Rezek
University Networking Services
South Dakota State University

[Aaron Bertrand - MVP]

Set your parameter as:

@ConnectionDate SMALLDATETIME = NULL

Then, if it's supposed to be null, don't pass that parameter.




"Gary D. Rezek" <Gary_Rezek@NOSPAMsdstate.edu> wrote in message
news:uzAlS24eDHA.3248@tk2msftngp13.phx.gbl...

> Hi All (IIS 5, SQL 2k, Win 2k)
> I'm stumped and can't find where I would be "mis-converting" data.
> I receive the following error ONLY when I update to or insert a null value

into a smalldatetime field in SQL.

> <<Error converting data type char to smalldatetime>>
> Following are the relevant bits of code. It happens with either

ConnectionDate or CompletionDate (they are both setup the same)

> Everything seems to line-up in order as far as the fields and stored proc

variables and again if a date is used I do not receive

> the error and everything updates or inserts as it should.
> What am I missing?
> *********************************************
> If Len(Request.Form("txtConnectionDate")) <> 0 Then
> strConnectionDate = FormatDateTime(Request.Form("txtConnectionDate"))
> Else
> strConnectionDate = "Null"
> End If
> ****
> Set rstUpdateConn = Server.CreateObject("ADODB.Recordset")
> cnMain.spUpdateStudentConnectionRecord intConnectionID, StuID,
> strFirstName, strLastName, strRoomCode, strEMailAddress,
> strCellPhoneNumber, strConnectionDate, strConnectionTerm,
> strConnectionYear, strConnectionDefinition, strCompletionDate,
> strNotesOnStudent, strBestTimeToContact, boolUserAgreeSigned,

rstUpdateConn

> ****
> CREATE PROCEDURE dbo.spUpdateStudentConnectionRecord
> @ConnectionID int
> ,@StudentIDNumber char(7)
> ,@FirstName varchar(50)
> ,@LastName varchar(50)
> ,@RoomCode varchar(50)
> ,@EmailAddress varchar(50)
> ,@CellPhoneNumber varchar(14)
> ,@ConnectionDate smalldatetime
> ,@ConnectionTerm varchar(6)
> ,@ConnectionYear varchar(4)
> ,@ConnectionDefinition varchar(50)
> ,@CompletionDate smalldatetime
> ,@NotesOnStudent varchar(255)
> ,@BestTimeToContact varchar(255)
> ,@UserAgreeSigned bit
>
> AS
>
> SET NOCOUNT ON
>
> BEGIN TRANSACTION
>
> UPDATE tblConnectionInformation
> SET ConnectionDate = @ConnectionDate,
> ConnectionTerm = @ConnectionTerm,
> ConnectionYear = @ConnectionYear,
> ConnectionDefinition = @ConnectionDefinition,
> CompletionDate = @CompletionDate,
> UserAgreeSigned = @UserAgreeSigned,
> BestTimeToContact = @BestTimeToContact
> WHERE ConnectionID = @ConnectionID
>
> If @@ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RETURN
> END
> <<2 more inserts>>
> *********************************************
>
> Thank you.
> gdr
> --
> Gary D. Rezek
> University Networking Services
> South Dakota State University
>
>

[Aaron Bertrand - MVP]

Which means you'll have to NAME your parameters instead of just listing
them...


"Gary D. Rezek" <Gary_Rezek@NOSPAMsdstate.edu> wrote in message
news:uzAlS24eDHA.3248@tk2msftngp13.phx.gbl...

> Hi All (IIS 5, SQL 2k, Win 2k)
> I'm stumped and can't find where I would be "mis-converting" data.
> I receive the following error ONLY when I update to or insert a null value

into a smalldatetime field in SQL.

> <<Error converting data type char to smalldatetime>>
> Following are the relevant bits of code. It happens with either

ConnectionDate or CompletionDate (they are both setup the same)

> Everything seems to line-up in order as far as the fields and stored proc

variables and again if a date is used I do not receive

> the error and everything updates or inserts as it should.
> What am I missing?
> *********************************************
> If Len(Request.Form("txtConnectionDate")) <> 0 Then
> strConnectionDate = FormatDateTime(Request.Form("txtConnectionDate"))
> Else
> strConnectionDate = "Null"
> End If
> ****
> Set rstUpdateConn = Server.CreateObject("ADODB.Recordset")
> cnMain.spUpdateStudentConnectionRecord intConnectionID, StuID,
> strFirstName, strLastName, strRoomCode, strEMailAddress,
> strCellPhoneNumber, strConnectionDate, strConnectionTerm,
> strConnectionYear, strConnectionDefinition, strCompletionDate,
> strNotesOnStudent, strBestTimeToContact, boolUserAgreeSigned,

rstUpdateConn

> ****
> CREATE PROCEDURE dbo.spUpdateStudentConnectionRecord
> @ConnectionID int
> ,@StudentIDNumber char(7)
> ,@FirstName varchar(50)
> ,@LastName varchar(50)
> ,@RoomCode varchar(50)
> ,@EmailAddress varchar(50)
> ,@CellPhoneNumber varchar(14)
> ,@ConnectionDate smalldatetime
> ,@ConnectionTerm varchar(6)
> ,@ConnectionYear varchar(4)
> ,@ConnectionDefinition varchar(50)
> ,@CompletionDate smalldatetime
> ,@NotesOnStudent varchar(255)
> ,@BestTimeToContact varchar(255)
> ,@UserAgreeSigned bit
>
> AS
>
> SET NOCOUNT ON
>
> BEGIN TRANSACTION
>
> UPDATE tblConnectionInformation
> SET ConnectionDate = @ConnectionDate,
> ConnectionTerm = @ConnectionTerm,
> ConnectionYear = @ConnectionYear,
> ConnectionDefinition = @ConnectionDefinition,
> CompletionDate = @CompletionDate,
> UserAgreeSigned = @UserAgreeSigned,
> BestTimeToContact = @BestTimeToContact
> WHERE ConnectionID = @ConnectionID
>
> If @@ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RETURN
> END
> <<2 more inserts>>
> *********************************************
>
> Thank you.
> gdr
> --
> Gary D. Rezek
> University Networking Services
> South Dakota State University
>
>

[Gary D. Rezek]

Hi Aaron,
Thank you.
Sorry to be a bother, but ........
Here is what I ended up doing, but now, I get an error of

<<Error converting data type char to int>>

However when strSQL (found below) is placed in the query analyzer (using the same data as from the asp page) this runs just fine
and updates as it should.
So now what am I missing?

intConnectionID= Request.Form("lngConnectionID")
StuID = Request.Form("txtStudentIDNumber")
strFirstName = Request.Form("txtStudentFirstName")
strLastName = Request.Form("txtStudentLastName")
strRoomCode = Request.Form("selRoomCode")
strEMailAddress = Request.Form("txtEmailAddress")
strCellPhoneNumber = Request.Form("txtCellPhoneNumber")
strConnectionDate = Request.Form("txtConnectionDate")
strConnectionTerm = Request.Form("radConnectionTerm")
strConnectionYear = Request.Form("selConnectionYear")
strConnectionDefinition = Request.Form("selConnectionDefinition")
strCompletionDate = Request.Form("txtCompletionDate")
strNotesOnStudent = Request.Form("txtNotesOnStudent")
strBestTimeToContact = Request.Form("txtBestTimeToContact")

If Request.Form("chkUserAgreeSigned") = "True" Then
boolUserAgreeSigned = 1
Else
boolUserAgreeSigned = 0
End If
'---------------------------------------------------------------------------------
strSQL = "EXEC spUpdateStudentConnectionRecord "
strSQL = strSQL & "@ConnectionID = " & intConnectionID
strSQL = strSQL & ", @StudentIDNumber = '" & StuID & "'"
strSQL = strSQL & ", @FirstName = '" & strFirstName & "'"
strSQL = strSQL & ", @LastName = '" & strLastName & "'"
strSQL = strSQL & ", @RoomCode = '" & strRoomCode & "'"
strSQL = strSQL & ", @EMailAddress = '" & strEMailAddress & "'"
strSQL = strSQL & ", @CellPhoneNumber = '" & strCellPhoneNumber & "'"
strSQL = strSQL & ", @ConnectionTerm = '" & strConnectionTerm & "'"
strSQL = strSQL & ", @ConnectionYear = '" & strConnectionYear & "'"
strSQL = strSQL & ", @ConnectionDefinition = '" & strConnectionDefinition & "'"
strSQL = strSQL & ", @NotesOnStudent = '" & strNotesOnStudent & "'"
strSQL = strSQL & ", @BestTimeToContact = '" & strBestTimeToContact & "'"
strSQL = strSQL & ", @UserAgreeSigned = " & boolUserAgreeSigned
If Len(strConnectionDate) <> 0 Then
strSQL = strSQL & ", @ConnectionDate = '" & strConnectionDate & "'"
Else
'do nothing
End If
If Len(strCompletionDate) <> 0 Then
strSQL = strSQL & ", @CompletionDate = '" & strCompletionDate & "'"
Else
'do nothing
End If

'Response.Write strSQL
'Response.End

Set rstUpdateConn = Server.CreateObject("ADODB.Recordset")
cnMain.spUpdateStudentConnectionRecord strSQL, rstUpdateConn

**(pertinent stored proc lines of code)**(which agree with the values in their respective tables)
CREATE PROCEDURE dbo.spUpdateStudentConnectionRecord
@ConnectionID int
,@StudentIDNumber char(7)
,@FirstName varchar(50)
,@LastName varchar(50)
,@RoomCode varchar(50)
,@EmailAddress varchar(50)
,@CellPhoneNumber varchar(14)
,@ConnectionDate smalldatetime = NULL
,@ConnectionTerm varchar(6)
,@ConnectionYear varchar(4)
,@ConnectionDefinition varchar(50)
,@CompletionDate smalldatetime = NULL
,@NotesOnStudent varchar(255)
,@BestTimeToContact varchar(255)
,@UserAgreeSigned bit


Again thank you for your time and help.

gdr
--
Gary D. Rezek
University Networking Services
South Dakota State University

"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message news:%23N4qa%234eDHA.3284@tk2msftngp13.phx.gbl...

> Which means you'll have to NAME your parameters instead of just listing
> them...
>
>

[Aaron Bertrand - MVP]

Can you show the strSQL that you pasted into Query Analyzer? Seeing your
ASP code doesn't help me, because I have no idea what is in your
Request.Form variables...



"Gary D. Rezek" <Gary_Rezek@NOSPAMsdstate.edu> wrote in message
news:OdQa9l7eDHA.3788@tk2msftngp13.phx.gbl...

> Hi Aaron,
> Thank you.
> Sorry to be a bother, but ........
> Here is what I ended up doing, but now, I get an error of
>
> <<Error converting data type char to int>>
>
> However when strSQL (found below) is placed in the query analyzer (using

the same data as from the asp page) this runs just fine

> and updates as it should.
> So now what am I missing?
>
> intConnectionID= Request.Form("lngConnectionID")
> StuID = Request.Form("txtStudentIDNumber")
> strFirstName = Request.Form("txtStudentFirstName")
> strLastName = Request.Form("txtStudentLastName")
> strRoomCode = Request.Form("selRoomCode")
> strEMailAddress = Request.Form("txtEmailAddress")
> strCellPhoneNumber = Request.Form("txtCellPhoneNumber")
> strConnectionDate = Request.Form("txtConnectionDate")
> strConnectionTerm = Request.Form("radConnectionTerm")
> strConnectionYear = Request.Form("selConnectionYear")
> strConnectionDefinition = Request.Form("selConnectionDefinition")
> strCompletionDate = Request.Form("txtCompletionDate")
> strNotesOnStudent = Request.Form("txtNotesOnStudent")
> strBestTimeToContact = Request.Form("txtBestTimeToContact")
>
> If Request.Form("chkUserAgreeSigned") = "True" Then
> boolUserAgreeSigned = 1
> Else
> boolUserAgreeSigned = 0
> End If
>

'---------------------------------------------------------------------------
------

> strSQL = "EXEC spUpdateStudentConnectionRecord "
> strSQL = strSQL & "@ConnectionID = " & intConnectionID
> strSQL = strSQL & ", @StudentIDNumber = '" & StuID & "'"
> strSQL = strSQL & ", @FirstName = '" & strFirstName & "'"
> strSQL = strSQL & ", @LastName = '" & strLastName & "'"
> strSQL = strSQL & ", @RoomCode = '" & strRoomCode & "'"
> strSQL = strSQL & ", @EMailAddress = '" & strEMailAddress & "'"
> strSQL = strSQL & ", @CellPhoneNumber = '" & strCellPhoneNumber & "'"
> strSQL = strSQL & ", @ConnectionTerm = '" & strConnectionTerm & "'"
> strSQL = strSQL & ", @ConnectionYear = '" & strConnectionYear & "'"
> strSQL = strSQL & ", @ConnectionDefinition = '" & strConnectionDefinition

& "'"

> strSQL = strSQL & ", @NotesOnStudent = '" & strNotesOnStudent & "'"
> strSQL = strSQL & ", @BestTimeToContact = '" & strBestTimeToContact & "'"
> strSQL = strSQL & ", @UserAgreeSigned = " & boolUserAgreeSigned
> If Len(strConnectionDate) <> 0 Then
> strSQL = strSQL & ", @ConnectionDate = '" & strConnectionDate & "'"
> Else
> 'do nothing
> End If
> If Len(strCompletionDate) <> 0 Then
> strSQL = strSQL & ", @CompletionDate = '" & strCompletionDate & "'"
> Else
> 'do nothing
> End If
>
> 'Response.Write strSQL
> 'Response.End
>
> Set rstUpdateConn = Server.CreateObject("ADODB.Recordset")
> cnMain.spUpdateStudentConnectionRecord strSQL, rstUpdateConn
>
> **(pertinent stored proc lines of code)**(which agree with the values in

their respective tables)

> CREATE PROCEDURE dbo.spUpdateStudentConnectionRecord
> @ConnectionID int
> ,@StudentIDNumber char(7)
> ,@FirstName varchar(50)
> ,@LastName varchar(50)
> ,@RoomCode varchar(50)
> ,@EmailAddress varchar(50)
> ,@CellPhoneNumber varchar(14)
> ,@ConnectionDate smalldatetime = NULL
> ,@ConnectionTerm varchar(6)
> ,@ConnectionYear varchar(4)
> ,@ConnectionDefinition varchar(50)
> ,@CompletionDate smalldatetime = NULL
> ,@NotesOnStudent varchar(255)
> ,@BestTimeToContact varchar(255)
> ,@UserAgreeSigned bit
>
>
> Again thank you for your time and help.
>
> gdr
> --
> Gary D. Rezek
> University Networking Services
> South Dakota State University
>
> "Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message

news:%23N4qa%234eDHA.3284@tk2msftngp13.phx.gbl...

> > Which means you'll have to NAME your parameters instead of just listing
> > them...
> >
> >

>
>

[Gary D. Rezek]

Hi Aaron,

EXEC spUpdateStudentConnectionRecord @ConnectionID = 11660, @StudentIDNumber = '8888886', @FirstName = 'another', @LastName =
'testentry', @RoomCode = 'YH480-1', @EMailAddress = 'anywhere@anyhow', @CellPhoneNumber = '6056666666', @ConnectionTerm =
'Fall', @ConnectionYear = '2003', @ConnectionDefinition = 'Disconnect-No Payment', @NotesOnStudent = '', @BestTimeToContact =
'', @UserAgreeSigned = 0, @ConnectionDate = '9/11/2003'

gdr

"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message news:eIptiY8eDHA.1712@TK2MSFTNGP11.phx.gbl...

> Can you show the strSQL that you pasted into Query Analyzer? Seeing your
> ASP code doesn't help me, because I have no idea what is in your
> Request.Form variables...
>
>
>
> "Gary D. Rezek" <Gary_Rezek@NOSPAMsdstate.edu> wrote in message
> news:OdQa9l7eDHA.3788@tk2msftngp13.phx.gbl...

> > Hi Aaron,
> > Thank you.
> > Sorry to be a bother, but ........
> > Here is what I ended up doing, but now, I get an error of
> >
> > <<Error converting data type char to int>>
> >
> > However when strSQL (found below) is placed in the query analyzer (using

> the same data as from the asp page) this runs just fine

> > and updates as it should.
> > So now what am I missing?
> >
> > intConnectionID= Request.Form("lngConnectionID")
> > StuID = Request.Form("txtStudentIDNumber")
> > strFirstName = Request.Form("txtStudentFirstName")
> > strLastName = Request.Form("txtStudentLastName")
> > strRoomCode = Request.Form("selRoomCode")
> > strEMailAddress = Request.Form("txtEmailAddress")
> > strCellPhoneNumber = Request.Form("txtCellPhoneNumber")
> > strConnectionDate = Request.Form("txtConnectionDate")
> > strConnectionTerm = Request.Form("radConnectionTerm")
> > strConnectionYear = Request.Form("selConnectionYear")
> > strConnectionDefinition = Request.Form("selConnectionDefinition")
> > strCompletionDate = Request.Form("txtCompletionDate")
> > strNotesOnStudent = Request.Form("txtNotesOnStudent")
> > strBestTimeToContact = Request.Form("txtBestTimeToContact")
> >
> > If Request.Form("chkUserAgreeSigned") = "True" Then
> > boolUserAgreeSigned = 1
> > Else
> > boolUserAgreeSigned = 0
> > End If
> >

> '---------------------------------------------------------------------------
> ------

> > strSQL = "EXEC spUpdateStudentConnectionRecord "
> > strSQL = strSQL & "@ConnectionID = " & intConnectionID
> > strSQL = strSQL & ", @StudentIDNumber = '" & StuID & "'"
> > strSQL = strSQL & ", @FirstName = '" & strFirstName & "'"
> > strSQL = strSQL & ", @LastName = '" & strLastName & "'"
> > strSQL = strSQL & ", @RoomCode = '" & strRoomCode & "'"
> > strSQL = strSQL & ", @EMailAddress = '" & strEMailAddress & "'"
> > strSQL = strSQL & ", @CellPhoneNumber = '" & strCellPhoneNumber & "'"
> > strSQL = strSQL & ", @ConnectionTerm = '" & strConnectionTerm & "'"
> > strSQL = strSQL & ", @ConnectionYear = '" & strConnectionYear & "'"
> > strSQL = strSQL & ", @ConnectionDefinition = '" & strConnectionDefinition

> & "'"

> > strSQL = strSQL & ", @NotesOnStudent = '" & strNotesOnStudent & "'"
> > strSQL = strSQL & ", @BestTimeToContact = '" & strBestTimeToContact & "'"
> > strSQL = strSQL & ", @UserAgreeSigned = " & boolUserAgreeSigned
> > If Len(strConnectionDate) <> 0 Then
> > strSQL = strSQL & ", @ConnectionDate = '" & strConnectionDate & "'"
> > Else
> > 'do nothing
> > End If
> > If Len(strCompletionDate) <> 0 Then
> > strSQL = strSQL & ", @CompletionDate = '" & strCompletionDate & "'"
> > Else
> > 'do nothing
> > End If
> >
> > 'Response.Write strSQL
> > 'Response.End
> >
> > Set rstUpdateConn = Server.CreateObject("ADODB.Recordset")
> > cnMain.spUpdateStudentConnectionRecord strSQL, rstUpdateConn
> >
> > **(pertinent stored proc lines of code)**(which agree with the values in

> their respective tables)

> > CREATE PROCEDURE dbo.spUpdateStudentConnectionRecord
> > @ConnectionID int
> > ,@StudentIDNumber char(7)
> > ,@FirstName varchar(50)
> > ,@LastName varchar(50)
> > ,@RoomCode varchar(50)
> > ,@EmailAddress varchar(50)
> > ,@CellPhoneNumber varchar(14)
> > ,@ConnectionDate smalldatetime = NULL
> > ,@ConnectionTerm varchar(6)
> > ,@ConnectionYear varchar(4)
> > ,@ConnectionDefinition varchar(50)
> > ,@CompletionDate smalldatetime = NULL
> > ,@NotesOnStudent varchar(255)
> > ,@BestTimeToContact varchar(255)
> > ,@UserAgreeSigned bit
> >
> >
> > Again thank you for your time and help.
> >
> > gdr
> > --
> > Gary D. Rezek
> > University Networking Services
> > South Dakota State University
> >
> > "Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message

> news:%23N4qa%234eDHA.3284@tk2msftngp13.phx.gbl...

> > > Which means you'll have to NAME your parameters instead of just listing
> > > them...
> > >
> > >

> >
> >

>
>

[Aaron Bertrand [MVP]]

That looks fine, and since there are no INT parameters to your stored
procedure other than connectionID (which looks fine), I think this is
happening in the body of your procedure.

Is there any more text with the error message? (e.g. stored procedure blat,
line x)?

Please show the body of the stored procedure, and the table structures for
all tables mentioned within the body of the stored procedure.

A



"Gary D. Rezek" <Gary_Rezek@NOSPAMsdstate.edu> wrote in message
news:uqzLXd8eDHA.944@TK2MSFTNGP11.phx.gbl...

> Hi Aaron,
>
> EXEC spUpdateStudentConnectionRecord @ConnectionID = 11660,

@StudentIDNumber = '8888886', @FirstName = 'another', @LastName =

> 'testentry', @RoomCode = 'YH480-1', @EMailAddress = 'anywhere@anyhow',

@CellPhoneNumber = '6056666666', @ConnectionTerm =

> 'Fall', @ConnectionYear = '2003', @ConnectionDefinition = 'Disconnect-No

Payment', @NotesOnStudent = '', @BestTimeToContact =

> '', @UserAgreeSigned = 0, @ConnectionDate = '9/11/2003'
>
> gdr
>
> "Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message

news:eIptiY8eDHA.1712@TK2MSFTNGP11.phx.gbl...

> > Can you show the strSQL that you pasted into Query Analyzer? Seeing

your

> > ASP code doesn't help me, because I have no idea what is in your
> > Request.Form variables...
> >
> >
> >
> > "Gary D. Rezek" <Gary_Rezek@NOSPAMsdstate.edu> wrote in message
> > news:OdQa9l7eDHA.3788@tk2msftngp13.phx.gbl...

> > > Hi Aaron,
> > > Thank you.
> > > Sorry to be a bother, but ........
> > > Here is what I ended up doing, but now, I get an error of
> > >
> > > <<Error converting data type char to int>>
> > >
> > > However when strSQL (found below) is placed in the query analyzer

(using

> > the same data as from the asp page) this runs just fine

> > > and updates as it should.
> > > So now what am I missing?
> > >
> > > intConnectionID= Request.Form("lngConnectionID")
> > > StuID = Request.Form("txtStudentIDNumber")
> > > strFirstName = Request.Form("txtStudentFirstName")
> > > strLastName = Request.Form("txtStudentLastName")
> > > strRoomCode = Request.Form("selRoomCode")
> > > strEMailAddress = Request.Form("txtEmailAddress")
> > > strCellPhoneNumber = Request.Form("txtCellPhoneNumber")
> > > strConnectionDate = Request.Form("txtConnectionDate")
> > > strConnectionTerm = Request.Form("radConnectionTerm")
> > > strConnectionYear = Request.Form("selConnectionYear")
> > > strConnectionDefinition = Request.Form("selConnectionDefinition")
> > > strCompletionDate = Request.Form("txtCompletionDate")
> > > strNotesOnStudent = Request.Form("txtNotesOnStudent")
> > > strBestTimeToContact = Request.Form("txtBestTimeToContact")
> > >
> > > If Request.Form("chkUserAgreeSigned") = "True" Then
> > > boolUserAgreeSigned = 1
> > > Else
> > > boolUserAgreeSigned = 0
> > > End If
> > >

> >

'---------------------------------------------------------------------------

> > ------

> > > strSQL = "EXEC spUpdateStudentConnectionRecord "
> > > strSQL = strSQL & "@ConnectionID = " & intConnectionID
> > > strSQL = strSQL & ", @StudentIDNumber = '" & StuID & "'"
> > > strSQL = strSQL & ", @FirstName = '" & strFirstName & "'"
> > > strSQL = strSQL & ", @LastName = '" & strLastName & "'"
> > > strSQL = strSQL & ", @RoomCode = '" & strRoomCode & "'"
> > > strSQL = strSQL & ", @EMailAddress = '" & strEMailAddress & "'"
> > > strSQL = strSQL & ", @CellPhoneNumber = '" & strCellPhoneNumber & "'"
> > > strSQL = strSQL & ", @ConnectionTerm = '" & strConnectionTerm & "'"
> > > strSQL = strSQL & ", @ConnectionYear = '" & strConnectionYear & "'"
> > > strSQL = strSQL & ", @ConnectionDefinition = '" &

strConnectionDefinition

> > & "'"

> > > strSQL = strSQL & ", @NotesOnStudent = '" & strNotesOnStudent & "'"
> > > strSQL = strSQL & ", @BestTimeToContact = '" & strBestTimeToContact &

"'"

> > > strSQL = strSQL & ", @UserAgreeSigned = " & boolUserAgreeSigned
> > > If Len(strConnectionDate) <> 0 Then
> > > strSQL = strSQL & ", @ConnectionDate = '" & strConnectionDate &

"'"

> > > Else
> > > 'do nothing
> > > End If
> > > If Len(strCompletionDate) <> 0 Then
> > > strSQL = strSQL & ", @CompletionDate = '" & strCompletionDate &

"'"

> > > Else
> > > 'do nothing
> > > End If
> > >
> > > 'Response.Write strSQL
> > > 'Response.End
> > >
> > > Set rstUpdateConn = Server.CreateObject("ADODB.Recordset")
> > > cnMain.spUpdateStudentConnectionRecord strSQL, rstUpdateConn
> > >
> > > **(pertinent stored proc lines of code)**(which agree with the values

in

> > their respective tables)

> > > CREATE PROCEDURE dbo.spUpdateStudentConnectionRecord
> > > @ConnectionID int
> > > ,@StudentIDNumber char(7)
> > > ,@FirstName varchar(50)
> > > ,@LastName varchar(50)
> > > ,@RoomCode varchar(50)
> > > ,@EmailAddress varchar(50)
> > > ,@CellPhoneNumber varchar(14)
> > > ,@ConnectionDate smalldatetime = NULL
> > > ,@ConnectionTerm varchar(6)
> > > ,@ConnectionYear varchar(4)
> > > ,@ConnectionDefinition varchar(50)
> > > ,@CompletionDate smalldatetime = NULL
> > > ,@NotesOnStudent varchar(255)
> > > ,@BestTimeToContact varchar(255)
> > > ,@UserAgreeSigned bit
> > >
> > >
> > > Again thank you for your time and help.
> > >
> > > gdr
> > > --
> > > Gary D. Rezek
> > > University Networking Services
> > > South Dakota State University
> > >
> > > "Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message

> > news:%23N4qa%234eDHA.3284@tk2msftngp13.phx.gbl...

> > > > Which means you'll have to NAME your parameters instead of just

listing

> > > > them...
> > > >
> > > >
> > >
> > >

> >
> >

>
>

[Gary D. Rezek]

Hi Aaron,

I think this is everything.

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E07)
Error converting data type char to int.
/ResNetWebReg/RCCSupervisor/StudentInformation.asp, line 184
(Line 184 is marked below)

<***code on StudentInformation.asp***>
SELECT CASE strPostAction
CASE "edit"
intConnectionID = Request.Form("lngConnectionID")
StuID = Request.Form("txtStudentIDNumber")
strFirstName = Request.Form("txtStudentFirstName")
strLastName = Request.Form("txtStudentLastName")
strRoomCode = Request.Form("selRoomCode")
strEMailAddress = Request.Form("txtEmailAddress")
strCellPhoneNumber = Request.Form("txtCellPhoneNumber")
strConnectionDate = Request.Form("txtConnectionDate")
strConnectionTerm = Request.Form("radConnectionTerm")
strConnectionYear = Request.Form("selConnectionYear")
strConnectionDefinition = Request.Form("selConnectionDefinition")
strCompletionDate = Request.Form("txtCompletionDate")
strNotesOnStudent = Request.Form("txtNotesOnStudent")
strBestTimeToContact = Request.Form("txtBestTimeToContact")
If Request.Form("chkUserAgreeSigned") = "True" Then
boolUserAgreeSigned = 1
Else
boolUserAgreeSigned = 0
End If
'---------------------------------------------------------------------------------
strSQL = "EXEC spUpdateStudentConnectionRecord "
strSQL = strSQL & "@ConnectionID = " & intConnectionID
strSQL = strSQL & ", @StudentIDNumber = '" & StuID & "'"
strSQL = strSQL & ", @FirstName = '" & strFirstName & "'"
strSQL = strSQL & ", @LastName = '" & strLastName & "'"
strSQL = strSQL & ", @RoomCode = '" & strRoomCode & "'"
strSQL = strSQL & ", @EMailAddress = '" & strEMailAddress & "'"
strSQL = strSQL & ", @CellPhoneNumber = '" & strCellPhoneNumber & "'"
strSQL = strSQL & ", @ConnectionTerm = '" & strConnectionTerm & "'"
strSQL = strSQL & ", @ConnectionYear = '" & strConnectionYear & "'"
strSQL = strSQL & ", @ConnectionDefinition = '" & strConnectionDefinition & "'"
strSQL = strSQL & ", @NotesOnStudent = '" & strNotesOnStudent & "'"
strSQL = strSQL & ", @BestTimeToContact = '" & strBestTimeToContact & "'"
strSQL = strSQL & ", @UserAgreeSigned = " & boolUserAgreeSigned
If Len(strConnectionDate) <> 0 Then
strSQL = strSQL & ", @ConnectionDate = '" & strConnectionDate & "'"
Else
'do nothing
End If
If Len(strCompletionDate) <> 0 Then
strSQL = strSQL & ", @CompletionDate = '" & strCompletionDate & "'"
Else
'do nothing
End If
'Response.Write strSQL
'Response.End
Set rstUpdateConn = Server.CreateObject("ADODB.Recordset")
cnMain.spUpdateStudentConnectionRecord strSQL, rstUpdateConn **(Line 184)**
**<CASE Add and CASE Delete follow>**

<****tblConnectionInformation****>
CREATE TABLE dbo.tblConnectionInformation (
ConnectionID int IDENTITY (1, 1) NOT NULL ,
ConnectionDate smalldatetime] NULL ,
ConnectionTerm varchar (6) NULL,
ConnectionYear varchar (4) NULL,
ConnectionDefinition varchar (50) NULL,
SetupAppointmentDate smalldatetime NULL ,
CompletionDate smalldatetime NULL ,
UserAgreeSigned bit NULL ,
BestTimeToContact varchar (255) NULL,
NotesOnConnection varchar (255) NULL
)
<****tblStudentInformation****>
CREATE TABLE dbo.tblStudentInformation (
StudentIDNumber char (7) NOT NULL ,
LastName varchar (50) NULL ,
FirstName varchar (50) NULL ,
EMailAddress varchar (50) NULL ,
CellPhoneNumber varchar (14) NULL ,
DateEntered smalldatetime NULL ,
NotesOnStudent varchar (255) NULL
)
GO
<****tblConnectionRoomDetails****(join table between room data and a connection)>
CREATE TABLE [dbo].[tblConnectionRoomDetails](
ConnectionRoomDetailID int IDENTITY (1, 1) NOT NULL ,
ConnectionID int NOT NULL ,
RoomCode varchar(50) NOT NULL
GO

<****stored procedure****>
CREATE PROCEDURE dbo.spUpdateStudentConnectionRecord
@ConnectionID int
,@StudentIDNumber char(7)
,@FirstName varchar(50)
,@LastName varchar(50)
,@RoomCode varchar(50)
,@EmailAddress varchar(50)
,@CellPhoneNumber varchar(14)
,@ConnectionDate smalldatetime = NULL
,@ConnectionTerm varchar(6)
,@ConnectionYear varchar(4)
,@ConnectionDefinition varchar(50)
,@CompletionDate smalldatetime = NULL
,@NotesOnStudent varchar(255)
,@BestTimeToContact varchar(255)
,@UserAgreeSigned bit

AS

SET NOCOUNT ON

BEGIN TRANSACTION

UPDATE tblConnectionInformation
SET ConnectionDate = @ConnectionDate,
ConnectionTerm = @ConnectionTerm,
ConnectionYear = @ConnectionYear,
ConnectionDefinition = @ConnectionDefinition,
CompletionDate = @CompletionDate,
UserAgreeSigned = @UserAgreeSigned,
BestTimeToContact = @BestTimeToContact
WHERE ConnectionID = @ConnectionID

If @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN
END


UPDATE tblStudentInformation
SET FirstName = @FirstName,
LastName = @LastName,
EmailAddress = @EmailAddress,
CellPhoneNumber = @CellPhoneNumber,
NotesOnStudent = @NotesOnStudent
WHERE StudentIDNumber = @StudentIDNumber

If @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN
END

UPDATE tblConnectionRoomDetails
SET RoomCode = @RoomCode
WHERE ConnectionID = @ConnectionID

If @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN
END

COMMIT TRANSACTION
GO
<****end stored procedure****>

Thanks

"Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message news:e2n7Px9eDHA.2344@TK2MSFTNGP10.phx.gbl...

> That looks fine, and since there are no INT parameters to your stored
> procedure other than connectionID (which looks fine), I think this is
> happening in the body of your procedure.
>
> Is there any more text with the error message? (e.g. stored procedure blat,
> line x)?
>
> Please show the body of the stored procedure, and the table structures for
> all tables mentioned within the body of the stored procedure.
>
> A

<<SNIP>>

[Aaron Bertrand - MVP]

When I create this schema, and then run the stored procedure, nothing
happens... because there is no data in the table.

However, even when I insert fake data in the table, and run the procedure
almost exactly as you had it before (only changing the connectionID value),
the update runs successfully.

So, I don't think we have enough information to reproduce the problem...






"Gary D. Rezek" <Gary_Rezek@NOSPAMsdstate.edu> wrote in message
news:OkRxk1FfDHA.2352@TK2MSFTNGP09.phx.gbl...

> Hi Aaron,
>
> I think this is everything.
>
> Error Type:
> Microsoft OLE DB Provider for SQL Server (0x80040E07)
> Error converting data type char to int.
> /ResNetWebReg/RCCSupervisor/StudentInformation.asp, line 184
> (Line 184 is marked below)
>
> <***code on StudentInformation.asp***>
> SELECT CASE strPostAction
> CASE "edit"
> intConnectionID = Request.Form("lngConnectionID")
> StuID = Request.Form("txtStudentIDNumber")
> strFirstName = Request.Form("txtStudentFirstName")
> strLastName = Request.Form("txtStudentLastName")
> strRoomCode = Request.Form("selRoomCode")
> strEMailAddress = Request.Form("txtEmailAddress")
> strCellPhoneNumber = Request.Form("txtCellPhoneNumber")
> strConnectionDate = Request.Form("txtConnectionDate")
> strConnectionTerm = Request.Form("radConnectionTerm")
> strConnectionYear = Request.Form("selConnectionYear")
> strConnectionDefinition = Request.Form("selConnectionDefinition")
> strCompletionDate = Request.Form("txtCompletionDate")
> strNotesOnStudent = Request.Form("txtNotesOnStudent")
> strBestTimeToContact = Request.Form("txtBestTimeToContact")
> If Request.Form("chkUserAgreeSigned") = "True" Then
> boolUserAgreeSigned = 1
> Else
> boolUserAgreeSigned = 0
> End If
>

'---------------------------------------------------------------------------
------

> strSQL = "EXEC spUpdateStudentConnectionRecord "
> strSQL = strSQL & "@ConnectionID = " & intConnectionID
> strSQL = strSQL & ", @StudentIDNumber = '" & StuID & "'"
> strSQL = strSQL & ", @FirstName = '" & strFirstName & "'"
> strSQL = strSQL & ", @LastName = '" & strLastName & "'"
> strSQL = strSQL & ", @RoomCode = '" & strRoomCode & "'"
> strSQL = strSQL & ", @EMailAddress = '" & strEMailAddress & "'"
> strSQL = strSQL & ", @CellPhoneNumber = '" & strCellPhoneNumber & "'"
> strSQL = strSQL & ", @ConnectionTerm = '" & strConnectionTerm & "'"
> strSQL = strSQL & ", @ConnectionYear = '" & strConnectionYear & "'"
> strSQL = strSQL & ", @ConnectionDefinition = '" & strConnectionDefinition

& "'"

> strSQL = strSQL & ", @NotesOnStudent = '" & strNotesOnStudent & "'"
> strSQL = strSQL & ", @BestTimeToContact = '" & strBestTimeToContact & "'"
> strSQL = strSQL & ", @UserAgreeSigned = " & boolUserAgreeSigned
> If Len(strConnectionDate) <> 0 Then
> strSQL = strSQL & ", @ConnectionDate = '" & strConnectionDate & "'"
> Else
> 'do nothing
> End If
> If Len(strCompletionDate) <> 0 Then
> strSQL = strSQL & ", @CompletionDate = '" & strCompletionDate & "'"
> Else
> 'do nothing
> End If
> 'Response.Write strSQL
> 'Response.End
> Set rstUpdateConn = Server.CreateObject("ADODB.Recordset")
> cnMain.spUpdateStudentConnectionRecord strSQL, rstUpdateConn **(Line

184)**

> **<CASE Add and CASE Delete follow>**
>
> <****tblConnectionInformation****>
> CREATE TABLE dbo.tblConnectionInformation (
> ConnectionID int IDENTITY (1, 1) NOT NULL ,
> ConnectionDate smalldatetime] NULL ,
> ConnectionTerm varchar (6) NULL,
> ConnectionYear varchar (4) NULL,
> ConnectionDefinition varchar (50) NULL,
> SetupAppointmentDate smalldatetime NULL ,
> CompletionDate smalldatetime NULL ,
> UserAgreeSigned bit NULL ,
> BestTimeToContact varchar (255) NULL,
> NotesOnConnection varchar (255) NULL
> )
> <****tblStudentInformation****>
> CREATE TABLE dbo.tblStudentInformation (
> StudentIDNumber char (7) NOT NULL ,
> LastName varchar (50) NULL ,
> FirstName varchar (50) NULL ,
> EMailAddress varchar (50) NULL ,
> CellPhoneNumber varchar (14) NULL ,
> DateEntered smalldatetime NULL ,
> NotesOnStudent varchar (255) NULL
> )
> GO
> <****tblConnectionRoomDetails****(join table between room data and a

connection)>

> CREATE TABLE [dbo].[tblConnectionRoomDetails](
> ConnectionRoomDetailID int IDENTITY (1, 1) NOT NULL ,
> ConnectionID int NOT NULL ,
> RoomCode varchar(50) NOT NULL
> GO
>
> <****stored procedure****>
> CREATE PROCEDURE dbo.spUpdateStudentConnectionRecord
> @ConnectionID int
> ,@StudentIDNumber char(7)
> ,@FirstName varchar(50)
> ,@LastName varchar(50)
> ,@RoomCode varchar(50)
> ,@EmailAddress varchar(50)
> ,@CellPhoneNumber varchar(14)
> ,@ConnectionDate smalldatetime = NULL
> ,@ConnectionTerm varchar(6)
> ,@ConnectionYear varchar(4)
> ,@ConnectionDefinition varchar(50)
> ,@CompletionDate smalldatetime = NULL
> ,@NotesOnStudent varchar(255)
> ,@BestTimeToContact varchar(255)
> ,@UserAgreeSigned bit
>
> AS
>
> SET NOCOUNT ON
>
> BEGIN TRANSACTION
>
> UPDATE tblConnectionInformation
> SET ConnectionDate = @ConnectionDate,
> ConnectionTerm = @ConnectionTerm,
> ConnectionYear = @ConnectionYear,
> ConnectionDefinition = @ConnectionDefinition,
> CompletionDate = @CompletionDate,
> UserAgreeSigned = @UserAgreeSigned,
> BestTimeToContact = @BestTimeToContact
> WHERE ConnectionID = @ConnectionID
>
> If @@ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RETURN
> END
>
>
> UPDATE tblStudentInformation
> SET FirstName = @FirstName,
> LastName = @LastName,
> EmailAddress = @EmailAddress,
> CellPhoneNumber = @CellPhoneNumber,
> NotesOnStudent = @NotesOnStudent
> WHERE StudentIDNumber = @StudentIDNumber
>
> If @@ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RETURN
> END
>
> UPDATE tblConnectionRoomDetails
> SET RoomCode = @RoomCode
> WHERE ConnectionID = @ConnectionID
>
> If @@ERROR <> 0
> BEGIN
> ROLLBACK TRAN
> RETURN
> END
>
> COMMIT TRANSACTION
> GO
> <****end stored procedure****>
>
> Thanks
>
> "Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message

news:e2n7Px9eDHA.2344@TK2MSFTNGP10.phx.gbl...

> > That looks fine, and since there are no INT parameters to your stored
> > procedure other than connectionID (which looks fine), I think this is
> > happening in the body of your procedure.
> >
> > Is there any more text with the error message? (e.g. stored procedure

blat,

> > line x)?
> >
> > Please show the body of the stored procedure, and the table structures

for

> > all tables mentioned within the body of the stored procedure.
> >
> > A

> <<SNIP>>
>
>

[Gary D. Rezek]

Hi Aaron,
Thank you very much for all your help.
Now, however, I feel very sheepish and lug-headed.
Check out the first line of strSQL:
<<strSQL = "EXEC spUpdateStudentConnectionRecord ">>
Then look at how I called it:
<<cnMain.spUpdateStudentConnectionRecord strSQL, rstUpdateConn>>

I don't know how many time I've looked this page over, replaced or commented out lines, etc. but it wasn't until I used
<<Set rstUpdateConn = cnMain.Execute (strSQL)>>
and it worked, that I saw the redundancy (it IS isn't it?) of trying to execute the stored procedure twice. My only excuse is
that I have been pushing to learn ASP and SQL, admin and development, at the same time (under the heading of other duites as
assigned) as well as keeping up my Access db's, that I have spread all over campus.
So again, thank you for your time and assistance.

gdr
--
Gary D. Rezek
University Networking Services
South Dakota State University

<snip>