|
 |
asp_databases thread: Converting to an Integer and then Inserting Into SQL Server
Message #1 by "Eoin Hayes" <Hayes_Eoin@e...> on Mon, 15 Apr 2002 17:24:57
|
|
How do I convert a string which I have obtained from a text box on a web
page to type Integer. I am using VB Script and want to enter an integer
into a SQL server DB using the INSERT keyword. The column is of type
smallint in the DB so I have to convert first don't I?
Message #2 by "Peter Foti (PeterF)" <PeterF@S...> on Mon, 15 Apr 2002 15:16:55 -0400
|
|
Your form input has the name "Name" when it should have "EmpName".
Pete
> -----Original Message-----
> From: Hayes_Eoin@e... [mailto:Hayes_Eoin@e...]
> Sent: Monday, April 15, 2002 3:10 PM
> To: ASP Databases
> Subject: [asp_databases] RE: Converting to an Integer and then Inserti
> ng I nto SQL Server
>
>
> This is what I get:
>
> INSERT INTO Employee (IDNumber, EmpName, ExtensionNo,
> MentorID, SalaryLevel,
> TeamID) VALUES (2113,'','324588',1901,1,'JT')
>
> It seems that EmpName is not being put into the DB
>
>
> -----Original Message-----
> From: Peter Foti (PeterF) [mailto:PeterF@S...]
> Sent: Monday, April 15, 2002 3:07 PM
> To: ASP Databases
> Subject: [asp_databases] RE: Converting to an Integer and then Inserti
> ng I nto SQL Server
>
>
> Ok, before you try to execute the SQL string, do a
> response.write on it
> to make sure it looks the way you think it looks (and then past the
> result into an email to the list). :)
>
> Pete
>
> > -----Original Message-----
> > From: Hayes_Eoin@e... [mailto:Hayes_Eoin@e...]
> > Sent: Monday, April 15, 2002 2:57 PM
> > To: ASP Databases
> > Subject: [asp_databases] RE: Converting to an Integer and
> then Inserti
> > ng I nto SQL Server
> >
> >
> > Cheers Pete, but I did that and I'm still getting the same
> > error. Would it
> > have something to do with the column values set in the SQL
> Server DB?
> > EmpName, ExtensionNo and TeamID are of type char.
> > IDNumber, Salarylevel and MentorID are of type smallint.
> >
> > -----Original Message-----
> > From: Peter Foti (PeterF) [mailto:PeterF@S...]
> > Sent: Monday, April 15, 2002 2:47 PM
> > To: ASP Databases
> > Subject: [asp_databases] RE: Converting to an Integer and
> then Inserti
> > ng I nto SQL Server
> >
> >
> > Yes, I see the problem. You are including single quotes in your SQL
> > string but then are also adding more single quotes with your
> > StrQuoteReplace function. If you were to Response.Write your strSQL
> > variable before calling Conn.Execute, you would see something
> > like this
> > (I'll assume all numeric entries are 0 and all strings contain FOO):
> >
> > INSERT INTO Employee (IDNumber, EmpName, ExtensionNo, MentorID,
> > SalaryLevel, TeamID) VALUES (0,''FOO'',''FOO'',0,0,''FOO'')
> >
> > Modify you strSQL to look like this:
> >
> > strSQL = "INSERT INTO Employee (IDNumber, EmpName, ExtensionNo,
> > MentorID, SalaryLevel, TeamID) VALUES (" & nIDNumber & "," &
> > StrQuoteReplace(strEmpName) & "," &
> StrQuoteReplace(strExtensionNo) &
> > "," & nMentorID & "," & nSalaryLevel & "," &
> > StrQuoteReplace(strTeamID)
> > & ")"
> >
> > (watch out for line breaks in the above example, which is
> meant to be
> > contained on 1 line)
> > Also, you might consider renaming your function
> StrQuoteReplace, only
> > because it is so long and using it multiple times takes up a lot of
> > extra characters making it harder to read. I have a function
> > that does
> > the same thing and I call it sql_quote. A matter of
> > preference though.
> > :)
> > Pete
> >
> >
> > > -----Original Message-----
> > > From: Hayes_Eoin@e... [mailto:Hayes_Eoin@e...]
> > > Sent: Monday, April 15, 2002 1:59 PM
> > > To: ASP Databases
> > > Subject: [asp_databases] RE: Converting to an Integer and
> > then Inserti
> > > ng I nto SQL Server
> > >
> > >
> > > Ok.But I still keep getting an error:
> > > Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
> > > Incorrect syntax near
> > > ','.
> > > /Eoinasp/page1.asp, line 139
> > >
> > > Here is the code to see if you can figure this out:
> > >
> > > Dim nIDNumber, strEmpName, strExtensionNo, nMentorID,
> nSalaryLevel,
> > > strTeamID, strSQL, str1
> > >
> > > if Request.QueryString<>"" then
> > > nIDNumber = Request.QueryString("IDNumber")
> > > strEmpName = Request.QueryString("EmpName")
> > > strExtensionNo = Request.QueryString("ExtensionNo")
> > > nMentorID = Request.QueryString("MentorID")
> > > nSalaryLevel = Request.QueryString("SalaryLevel")
> > > strTeamID = Request.QueryString("TeamID")
> > > end if
> > >
> > > strSQL = "INSERT INTO Employee (IDNumber, EmpName,
> > > ExtensionNo, MentorID,
> > > SalaryLevel, TeamID) VALUES (" & nIDNumber & ",'" &
> > > StrQuoteReplace(strEmpName) & "','" &
> > > StrQuoteReplace(strExtensionNo) & "',"
> > > & nMentorID & "," & nSalaryLevel & ",'" &
> > > StrQuoteReplace(strTeamID) & "')"
> > >
> > >
> > >
> > > Conn.Execute(strSQL) 'Line 139
> > >
> > >
> > > Conn.Close
> > > set Conn = nothing
> > >
> > >
> > > Function StrQuoteReplace(strValue)
> > > str1 = Replace(strValue, "'", "''")
> > > StrQuoteReplace = "'" & str1 & "'"
> > > End Function
> > >
> > > -----Original Message-----
> > > From: Peter Foti (PeterF) [mailto:PeterF@S...]
> > > Sent: Monday, April 15, 2002 1:53 PM
> > > To: ASP Databases
> > > Subject: [asp_databases] RE: Converting to an Integer and
> > > then Inserting
> > > I nto SQL Server
> > >
> > >
> > > No. You are generating a SQL string (string being the key
> > word here).
> > >
> > > For example:
> > >
> > > someint = Request.Form("intTextBox")
> > > somestr = Request.Form("strTextBox")
> > >
> > > SQLStr = "INSERT INTO myDB (myIntField, myStringField) VALUES (" &
> > > someint & ",'" & somestr & "')"
> > >
> > > The way your value appears in the SQL string will specify the
> > > data type.
> > > Values that are not surrounded with single quotes are
> > numeric, values
> > > surrounded by single quotes are characters (or Date in
> SQL Server).
> > >
> > > However, if you wanted to specify the data type, you would use:
> > > CInt()
> > > CStr()
> > > CDbl()
> > >
> > > For example:
> > >
> > > someint = CStr( Request.Form("intTextBox") )
> > >
> > > that would ensure that the value was stored as string data
> > instead of
> > > numeric data. However, if you wanted to do calculations with the
> > > value...
> > >
> > > someint = CInt( Request.Form("intTextBox") )
> > > x = someint + 2
> > >
> > > Get the picture? Hope this helps.
> > > Pete
> > >
> > >
> > >
> > >
> > > > -----Original Message-----
> > > > From: Eoin Hayes [mailto:Hayes_Eoin@e...]
> > > > Sent: Monday, April 15, 2002 5:25 PM
> > > > To: ASP Databases
> > > > Subject: [asp_databases] Converting to an Integer and
> > then Inserting
> > > > Into SQL Server
> > > >
> > > >
> > > > How do I convert a string which I have obtained from a text
> > > > box on a web
> > > > page to type Integer. I am using VB Script and want to enter
> > > > an integer
> > > > into a SQL server DB using the INSERT keyword. The column
> > > is of type
> > > > smallint in the DB so I have to convert first don't I?
> > > >
> > >
> > >
> > >
> >
> >
> >
>
>
>
Message #3 by Hayes_Eoin@e... on Mon, 15 Apr 2002 15:10:17 -0400
|
|
This is what I get:
INSERT INTO Employee (IDNumber, EmpName, ExtensionNo, MentorID, SalaryLevel,
TeamID) VALUES (2113,'','324588',1901,1,'JT')
It seems that EmpName is not being put into the DB
-----Original Message-----
From: Peter Foti (PeterF) [mailto:PeterF@S...]
Sent: Monday, April 15, 2002 3:07 PM
To: ASP Databases
Subject: [asp_databases] RE: Converting to an Integer and then Inserti
ng I nto SQL Server
Ok, before you try to execute the SQL string, do a response.write on it
to make sure it looks the way you think it looks (and then past the
result into an email to the list). :)
Pete
> -----Original Message-----
> From: Hayes_Eoin@e... [mailto:Hayes_Eoin@e...]
> Sent: Monday, April 15, 2002 2:57 PM
> To: ASP Databases
> Subject: [asp_databases] RE: Converting to an Integer and then Inserti
> ng I nto SQL Server
>
>
> Cheers Pete, but I did that and I'm still getting the same
> error. Would it
> have something to do with the column values set in the SQL Server DB?
> EmpName, ExtensionNo and TeamID are of type char.
> IDNumber, Salarylevel and MentorID are of type smallint.
>
> -----Original Message-----
> From: Peter Foti (PeterF) [mailto:PeterF@S...]
> Sent: Monday, April 15, 2002 2:47 PM
> To: ASP Databases
> Subject: [asp_databases] RE: Converting to an Integer and then Inserti
> ng I nto SQL Server
>
>
> Yes, I see the problem. You are including single quotes in your SQL
> string but then are also adding more single quotes with your
> StrQuoteReplace function. If you were to Response.Write your strSQL
> variable before calling Conn.Execute, you would see something
> like this
> (I'll assume all numeric entries are 0 and all strings contain FOO):
>
> INSERT INTO Employee (IDNumber, EmpName, ExtensionNo, MentorID,
> SalaryLevel, TeamID) VALUES (0,''FOO'',''FOO'',0,0,''FOO'')
>
> Modify you strSQL to look like this:
>
> strSQL = "INSERT INTO Employee (IDNumber, EmpName, ExtensionNo,
> MentorID, SalaryLevel, TeamID) VALUES (" & nIDNumber & "," &
> StrQuoteReplace(strEmpName) & "," & StrQuoteReplace(strExtensionNo) &
> "," & nMentorID & "," & nSalaryLevel & "," &
> StrQuoteReplace(strTeamID)
> & ")"
>
> (watch out for line breaks in the above example, which is meant to be
> contained on 1 line)
> Also, you might consider renaming your function StrQuoteReplace, only
> because it is so long and using it multiple times takes up a lot of
> extra characters making it harder to read. I have a function
> that does
> the same thing and I call it sql_quote. A matter of
> preference though.
> :)
> Pete
>
>
> > -----Original Message-----
> > From: Hayes_Eoin@e... [mailto:Hayes_Eoin@e...]
> > Sent: Monday, April 15, 2002 1:59 PM
> > To: ASP Databases
> > Subject: [asp_databases] RE: Converting to an Integer and
> then Inserti
> > ng I nto SQL Server
> >
> >
> > Ok.But I still keep getting an error:
> > Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
> > Incorrect syntax near
> > ','.
> > /Eoinasp/page1.asp, line 139
> >
> > Here is the code to see if you can figure this out:
> >
> > Dim nIDNumber, strEmpName, strExtensionNo, nMentorID, nSalaryLevel,
> > strTeamID, strSQL, str1
> >
> > if Request.QueryString<>"" then
> > nIDNumber = Request.QueryString("IDNumber")
> > strEmpName = Request.QueryString("EmpName")
> > strExtensionNo = Request.QueryString("ExtensionNo")
> > nMentorID = Request.QueryString("MentorID")
> > nSalaryLevel = Request.QueryString("SalaryLevel")
> > strTeamID = Request.QueryString("TeamID")
> > end if
> >
> > strSQL = "INSERT INTO Employee (IDNumber, EmpName,
> > ExtensionNo, MentorID,
> > SalaryLevel, TeamID) VALUES (" & nIDNumber & ",'" &
> > StrQuoteReplace(strEmpName) & "','" &
> > StrQuoteReplace(strExtensionNo) & "',"
> > & nMentorID & "," & nSalaryLevel & ",'" &
> > StrQuoteReplace(strTeamID) & "')"
> >
> >
> >
> > Conn.Execute(strSQL) 'Line 139
> >
> >
> > Conn.Close
> > set Conn = nothing
> >
> >
> > Function StrQuoteReplace(strValue)
> > str1 = Replace(strValue, "'", "''")
> > StrQuoteReplace = "'" & str1 & "'"
> > End Function
> >
> > -----Original Message-----
> > From: Peter Foti (PeterF) [mailto:PeterF@S...]
> > Sent: Monday, April 15, 2002 1:53 PM
> > To: ASP Databases
> > Subject: [asp_databases] RE: Converting to an Integer and
> > then Inserting
> > I nto SQL Server
> >
> >
> > No. You are generating a SQL string (string being the key
> word here).
> >
> > For example:
> >
> > someint = Request.Form("intTextBox")
> > somestr = Request.Form("strTextBox")
> >
> > SQLStr = "INSERT INTO myDB (myIntField, myStringField) VALUES (" &
> > someint & ",'" & somestr & "')"
> >
> > The way your value appears in the SQL string will specify the
> > data type.
> > Values that are not surrounded with single quotes are
> numeric, values
> > surrounded by single quotes are characters (or Date in SQL Server).
> >
> > However, if you wanted to specify the data type, you would use:
> > CInt()
> > CStr()
> > CDbl()
> >
> > For example:
> >
> > someint = CStr( Request.Form("intTextBox") )
> >
> > that would ensure that the value was stored as string data
> instead of
> > numeric data. However, if you wanted to do calculations with the
> > value...
> >
> > someint = CInt( Request.Form("intTextBox") )
> > x = someint + 2
> >
> > Get the picture? Hope this helps.
> > Pete
> >
> >
> >
> >
> > > -----Original Message-----
> > > From: Eoin Hayes [mailto:Hayes_Eoin@e...]
> > > Sent: Monday, April 15, 2002 5:25 PM
> > > To: ASP Databases
> > > Subject: [asp_databases] Converting to an Integer and
> then Inserting
> > > Into SQL Server
> > >
> > >
> > > How do I convert a string which I have obtained from a text
> > > box on a web
> > > page to type Integer. I am using VB Script and want to enter
> > > an integer
> > > into a SQL server DB using the INSERT keyword. The column
> > is of type
> > > smallint in the DB so I have to convert first don't I?
> > >
> >
> >
> >
>
>
>
Message #4 by Hayes_Eoin@e... on Mon, 15 Apr 2002 14:56:33 -0400
|
|
Cheers Pete, but I did that and I'm still getting the same error. Would it
have something to do with the column values set in the SQL Server DB?
EmpName, ExtensionNo and TeamID are of type char.
IDNumber, Salarylevel and MentorID are of type smallint.
-----Original Message-----
From: Peter Foti (PeterF) [mailto:PeterF@S...]
Sent: Monday, April 15, 2002 2:47 PM
To: ASP Databases
Subject: [asp_databases] RE: Converting to an Integer and then Inserti
ng I nto SQL Server
Yes, I see the problem. You are including single quotes in your SQL
string but then are also adding more single quotes with your
StrQuoteReplace function. If you were to Response.Write your strSQL
variable before calling Conn.Execute, you would see something like this
(I'll assume all numeric entries are 0 and all strings contain FOO):
INSERT INTO Employee (IDNumber, EmpName, ExtensionNo, MentorID,
SalaryLevel, TeamID) VALUES (0,''FOO'',''FOO'',0,0,''FOO'')
Modify you strSQL to look like this:
strSQL = "INSERT INTO Employee (IDNumber, EmpName, ExtensionNo,
MentorID, SalaryLevel, TeamID) VALUES (" & nIDNumber & "," &
StrQuoteReplace(strEmpName) & "," & StrQuoteReplace(strExtensionNo) &
"," & nMentorID & "," & nSalaryLevel & "," & StrQuoteReplace(strTeamID)
& ")"
(watch out for line breaks in the above example, which is meant to be
contained on 1 line)
Also, you might consider renaming your function StrQuoteReplace, only
because it is so long and using it multiple times takes up a lot of
extra characters making it harder to read. I have a function that does
the same thing and I call it sql_quote. A matter of preference though.
:)
Pete
> -----Original Message-----
> From: Hayes_Eoin@e... [mailto:Hayes_Eoin@e...]
> Sent: Monday, April 15, 2002 1:59 PM
> To: ASP Databases
> Subject: [asp_databases] RE: Converting to an Integer and then Inserti
> ng I nto SQL Server
>
>
> Ok.But I still keep getting an error:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
> Incorrect syntax near
> ','.
> /Eoinasp/page1.asp, line 139
>
> Here is the code to see if you can figure this out:
>
> Dim nIDNumber, strEmpName, strExtensionNo, nMentorID, nSalaryLevel,
> strTeamID, strSQL, str1
>
> if Request.QueryString<>"" then
> nIDNumber = Request.QueryString("IDNumber")
> strEmpName = Request.QueryString("EmpName")
> strExtensionNo = Request.QueryString("ExtensionNo")
> nMentorID = Request.QueryString("MentorID")
> nSalaryLevel = Request.QueryString("SalaryLevel")
> strTeamID = Request.QueryString("TeamID")
> end if
>
> strSQL = "INSERT INTO Employee (IDNumber, EmpName,
> ExtensionNo, MentorID,
> SalaryLevel, TeamID) VALUES (" & nIDNumber & ",'" &
> StrQuoteReplace(strEmpName) & "','" &
> StrQuoteReplace(strExtensionNo) & "',"
> & nMentorID & "," & nSalaryLevel & ",'" &
> StrQuoteReplace(strTeamID) & "')"
>
>
>
> Conn.Execute(strSQL) 'Line 139
>
>
> Conn.Close
> set Conn = nothing
>
>
> Function StrQuoteReplace(strValue)
> str1 = Replace(strValue, "'", "''")
> StrQuoteReplace = "'" & str1 & "'"
> End Function
>
> -----Original Message-----
> From: Peter Foti (PeterF) [mailto:PeterF@S...]
> Sent: Monday, April 15, 2002 1:53 PM
> To: ASP Databases
> Subject: [asp_databases] RE: Converting to an Integer and
> then Inserting
> I nto SQL Server
>
>
> No. You are generating a SQL string (string being the key word here).
>
> For example:
>
> someint = Request.Form("intTextBox")
> somestr = Request.Form("strTextBox")
>
> SQLStr = "INSERT INTO myDB (myIntField, myStringField) VALUES (" &
> someint & ",'" & somestr & "')"
>
> The way your value appears in the SQL string will specify the
> data type.
> Values that are not surrounded with single quotes are numeric, values
> surrounded by single quotes are characters (or Date in SQL Server).
>
> However, if you wanted to specify the data type, you would use:
> CInt()
> CStr()
> CDbl()
>
> For example:
>
> someint = CStr( Request.Form("intTextBox") )
>
> that would ensure that the value was stored as string data instead of
> numeric data. However, if you wanted to do calculations with the
> value...
>
> someint = CInt( Request.Form("intTextBox") )
> x = someint + 2
>
> Get the picture? Hope this helps.
> Pete
>
>
>
>
> > -----Original Message-----
> > From: Eoin Hayes [mailto:Hayes_Eoin@e...]
> > Sent: Monday, April 15, 2002 5:25 PM
> > To: ASP Databases
> > Subject: [asp_databases] Converting to an Integer and then Inserting
> > Into SQL Server
> >
> >
> > How do I convert a string which I have obtained from a text
> > box on a web
> > page to type Integer. I am using VB Script and want to enter
> > an integer
> > into a SQL server DB using the INSERT keyword. The column
> is of type
> > smallint in the DB so I have to convert first don't I?
> >
>
>
>
Message #5 by "Meinken, Joe" <Joe.Meinken@q...> on Mon, 15 Apr 2002 14:02:19 -0500
|
|
Did you perform a response.write as Peter suggested?
-----Original Message-----
From: Hayes_Eoin@e... [mailto:Hayes_Eoin@e...]
Sent: Monday, April 15, 2002 1:57 PM
To: ASP Databases
Subject: [asp_databases] RE: Converting to an Integer and then Inserti
ng I nto SQL Server
Cheers Pete, but I did that and I'm still getting the same error. Would it
have something to do with the column values set in the SQL Server DB?
EmpName, ExtensionNo and TeamID are of type char.
IDNumber, Salarylevel and MentorID are of type smallint.
-----Original Message-----
From: Peter Foti (PeterF) [mailto:PeterF@S...]
Sent: Monday, April 15, 2002 2:47 PM
To: ASP Databases
Subject: [asp_databases] RE: Converting to an Integer and then Inserti
ng I nto SQL Server
Yes, I see the problem. You are including single quotes in your SQL
string but then are also adding more single quotes with your
StrQuoteReplace function. If you were to Response.Write your strSQL
variable before calling Conn.Execute, you would see something like this
(I'll assume all numeric entries are 0 and all strings contain FOO):
INSERT INTO Employee (IDNumber, EmpName, ExtensionNo, MentorID,
SalaryLevel, TeamID) VALUES (0,''FOO'',''FOO'',0,0,''FOO'')
Modify you strSQL to look like this:
strSQL = "INSERT INTO Employee (IDNumber, EmpName, ExtensionNo,
MentorID, SalaryLevel, TeamID) VALUES (" & nIDNumber & "," &
StrQuoteReplace(strEmpName) & "," & StrQuoteReplace(strExtensionNo) &
"," & nMentorID & "," & nSalaryLevel & "," & StrQuoteReplace(strTeamID)
& ")"
(watch out for line breaks in the above example, which is meant to be
contained on 1 line)
Also, you might consider renaming your function StrQuoteReplace, only
because it is so long and using it multiple times takes up a lot of
extra characters making it harder to read. I have a function that does
the same thing and I call it sql_quote. A matter of preference though.
:)
Pete
> -----Original Message-----
> From: Hayes_Eoin@e... [mailto:Hayes_Eoin@e...]
> Sent: Monday, April 15, 2002 1:59 PM
> To: ASP Databases
> Subject: [asp_databases] RE: Converting to an Integer and then Inserti
> ng I nto SQL Server
>
>
> Ok.But I still keep getting an error:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
> Incorrect syntax near
> ','.
> /Eoinasp/page1.asp, line 139
>
> Here is the code to see if you can figure this out:
>
> Dim nIDNumber, strEmpName, strExtensionNo, nMentorID, nSalaryLevel,
> strTeamID, strSQL, str1
>
> if Request.QueryString<>"" then
> nIDNumber = Request.QueryString("IDNumber")
> strEmpName = Request.QueryString("EmpName")
> strExtensionNo = Request.QueryString("ExtensionNo")
> nMentorID = Request.QueryString("MentorID")
> nSalaryLevel = Request.QueryString("SalaryLevel")
> strTeamID = Request.QueryString("TeamID")
> end if
>
> strSQL = "INSERT INTO Employee (IDNumber, EmpName,
> ExtensionNo, MentorID,
> SalaryLevel, TeamID) VALUES (" & nIDNumber & ",'" &
> StrQuoteReplace(strEmpName) & "','" &
> StrQuoteReplace(strExtensionNo) & "',"
> & nMentorID & "," & nSalaryLevel & ",'" &
> StrQuoteReplace(strTeamID) & "')"
>
>
>
> Conn.Execute(strSQL) 'Line 139
>
>
> Conn.Close
> set Conn = nothing
>
>
> Function StrQuoteReplace(strValue)
> str1 = Replace(strValue, "'", "''")
> StrQuoteReplace = "'" & str1 & "'"
> End Function
>
> -----Original Message-----
> From: Peter Foti (PeterF) [mailto:PeterF@S...]
> Sent: Monday, April 15, 2002 1:53 PM
> To: ASP Databases
> Subject: [asp_databases] RE: Converting to an Integer and
> then Inserting
> I nto SQL Server
>
>
> No. You are generating a SQL string (string being the key word here).
>
> For example:
>
> someint = Request.Form("intTextBox")
> somestr = Request.Form("strTextBox")
>
> SQLStr = "INSERT INTO myDB (myIntField, myStringField) VALUES (" &
> someint & ",'" & somestr & "')"
>
> The way your value appears in the SQL string will specify the
> data type.
> Values that are not surrounded with single quotes are numeric, values
> surrounded by single quotes are characters (or Date in SQL Server).
>
> However, if you wanted to specify the data type, you would use:
> CInt()
> CStr()
> CDbl()
>
> For example:
>
> someint = CStr( Request.Form("intTextBox") )
>
> that would ensure that the value was stored as string data instead of
> numeric data. However, if you wanted to do calculations with the
> value...
>
> someint = CInt( Request.Form("intTextBox") )
> x = someint + 2
>
> Get the picture? Hope this helps.
> Pete
>
>
>
>
> > -----Original Message-----
> > From: Eoin Hayes [mailto:Hayes_Eoin@e...]
> > Sent: Monday, April 15, 2002 5:25 PM
> > To: ASP Databases
> > Subject: [asp_databases] Converting to an Integer and then Inserting
> > Into SQL Server
> >
> >
> > How do I convert a string which I have obtained from a text
> > box on a web
> > page to type Integer. I am using VB Script and want to enter
> > an integer
> > into a SQL server DB using the INSERT keyword. The column
> is of type
> > smallint in the DB so I have to convert first don't I?
> >
>
>
>
Message #6 by "Peter Foti (PeterF)" <PeterF@S...> on Mon, 15 Apr 2002 15:07:17 -0400
|
|
Ok, before you try to execute the SQL string, do a response.write on it
to make sure it looks the way you think it looks (and then past the
result into an email to the list). :)
Pete
> -----Original Message-----
> From: Hayes_Eoin@e... [mailto:Hayes_Eoin@e...]
> Sent: Monday, April 15, 2002 2:57 PM
> To: ASP Databases
> Subject: [asp_databases] RE: Converting to an Integer and then Inserti
> ng I nto SQL Server
>
>
> Cheers Pete, but I did that and I'm still getting the same
> error. Would it
> have something to do with the column values set in the SQL Server DB?
> EmpName, ExtensionNo and TeamID are of type char.
> IDNumber, Salarylevel and MentorID are of type smallint.
>
> -----Original Message-----
> From: Peter Foti (PeterF) [mailto:PeterF@S...]
> Sent: Monday, April 15, 2002 2:47 PM
> To: ASP Databases
> Subject: [asp_databases] RE: Converting to an Integer and then Inserti
> ng I nto SQL Server
>
>
> Yes, I see the problem. You are including single quotes in your SQL
> string but then are also adding more single quotes with your
> StrQuoteReplace function. If you were to Response.Write your strSQL
> variable before calling Conn.Execute, you would see something
> like this
> (I'll assume all numeric entries are 0 and all strings contain FOO):
>
> INSERT INTO Employee (IDNumber, EmpName, ExtensionNo, MentorID,
> SalaryLevel, TeamID) VALUES (0,''FOO'',''FOO'',0,0,''FOO'')
>
> Modify you strSQL to look like this:
>
> strSQL = "INSERT INTO Employee (IDNumber, EmpName, ExtensionNo,
> MentorID, SalaryLevel, TeamID) VALUES (" & nIDNumber & "," &
> StrQuoteReplace(strEmpName) & "," & StrQuoteReplace(strExtensionNo) &
> "," & nMentorID & "," & nSalaryLevel & "," &
> StrQuoteReplace(strTeamID)
> & ")"
>
> (watch out for line breaks in the above example, which is meant to be
> contained on 1 line)
> Also, you might consider renaming your function StrQuoteReplace, only
> because it is so long and using it multiple times takes up a lot of
> extra characters making it harder to read. I have a function
> that does
> the same thing and I call it sql_quote. A matter of
> preference though.
> :)
> Pete
>
>
> > -----Original Message-----
> > From: Hayes_Eoin@e... [mailto:Hayes_Eoin@e...]
> > Sent: Monday, April 15, 2002 1:59 PM
> > To: ASP Databases
> > Subject: [asp_databases] RE: Converting to an Integer and
> then Inserti
> > ng I nto SQL Server
> >
> >
> > Ok.But I still keep getting an error:
> > Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
> > Incorrect syntax near
> > ','.
> > /Eoinasp/page1.asp, line 139
> >
> > Here is the code to see if you can figure this out:
> >
> > Dim nIDNumber, strEmpName, strExtensionNo, nMentorID, nSalaryLevel,
> > strTeamID, strSQL, str1
> >
> > if Request.QueryString<>"" then
> > nIDNumber = Request.QueryString("IDNumber")
> > strEmpName = Request.QueryString("EmpName")
> > strExtensionNo = Request.QueryString("ExtensionNo")
> > nMentorID = Request.QueryString("MentorID")
> > nSalaryLevel = Request.QueryString("SalaryLevel")
> > strTeamID = Request.QueryString("TeamID")
> > end if
> >
> > strSQL = "INSERT INTO Employee (IDNumber, EmpName,
> > ExtensionNo, MentorID,
> > SalaryLevel, TeamID) VALUES (" & nIDNumber & ",'" &
> > StrQuoteReplace(strEmpName) & "','" &
> > StrQuoteReplace(strExtensionNo) & "',"
> > & nMentorID & "," & nSalaryLevel & ",'" &
> > StrQuoteReplace(strTeamID) & "')"
> >
> >
> >
> > Conn.Execute(strSQL) 'Line 139
> >
> >
> > Conn.Close
> > set Conn = nothing
> >
> >
> > Function StrQuoteReplace(strValue)
> > str1 = Replace(strValue, "'", "''")
> > StrQuoteReplace = "'" & str1 & "'"
> > End Function
> >
> > -----Original Message-----
> > From: Peter Foti (PeterF) [mailto:PeterF@S...]
> > Sent: Monday, April 15, 2002 1:53 PM
> > To: ASP Databases
> > Subject: [asp_databases] RE: Converting to an Integer and
> > then Inserting
> > I nto SQL Server
> >
> >
> > No. You are generating a SQL string (string being the key
> word here).
> >
> > For example:
> >
> > someint = Request.Form("intTextBox")
> > somestr = Request.Form("strTextBox")
> >
> > SQLStr = "INSERT INTO myDB (myIntField, myStringField) VALUES (" &
> > someint & ",'" & somestr & "')"
> >
> > The way your value appears in the SQL string will specify the
> > data type.
> > Values that are not surrounded with single quotes are
> numeric, values
> > surrounded by single quotes are characters (or Date in SQL Server).
> >
> > However, if you wanted to specify the data type, you would use:
> > CInt()
> > CStr()
> > CDbl()
> >
> > For example:
> >
> > someint = CStr( Request.Form("intTextBox") )
> >
> > that would ensure that the value was stored as string data
> instead of
> > numeric data. However, if you wanted to do calculations with the
> > value...
> >
> > someint = CInt( Request.Form("intTextBox") )
> > x = someint + 2
> >
> > Get the picture? Hope this helps.
> > Pete
> >
> >
> >
> >
> > > -----Original Message-----
> > > From: Eoin Hayes [mailto:Hayes_Eoin@e...]
> > > Sent: Monday, April 15, 2002 5:25 PM
> > > To: ASP Databases
> > > Subject: [asp_databases] Converting to an Integer and
> then Inserting
> > > Into SQL Server
> > >
> > >
> > > How do I convert a string which I have obtained from a text
> > > box on a web
> > > page to type Integer. I am using VB Script and want to enter
> > > an integer
> > > into a SQL server DB using the INSERT keyword. The column
> > is of type
> > > smallint in the DB so I have to convert first don't I?
> > >
> >
> >
> >
>
>
>
Message #7 by "Peter Foti (PeterF)" <PeterF@S...> on Mon, 15 Apr 2002 14:47:07 -0400
|
|
Yes, I see the problem. You are including single quotes in your SQL
string but then are also adding more single quotes with your
StrQuoteReplace function. If you were to Response.Write your strSQL
variable before calling Conn.Execute, you would see something like this
(I'll assume all numeric entries are 0 and all strings contain FOO):
INSERT INTO Employee (IDNumber, EmpName, ExtensionNo, MentorID,
SalaryLevel, TeamID) VALUES (0,''FOO'',''FOO'',0,0,''FOO'')
Modify you strSQL to look like this:
strSQL = "INSERT INTO Employee (IDNumber, EmpName, ExtensionNo,
MentorID, SalaryLevel, TeamID) VALUES (" & nIDNumber & "," &
StrQuoteReplace(strEmpName) & "," & StrQuoteReplace(strExtensionNo) &
"," & nMentorID & "," & nSalaryLevel & "," & StrQuoteReplace(strTeamID)
& ")"
(watch out for line breaks in the above example, which is meant to be
contained on 1 line)
Also, you might consider renaming your function StrQuoteReplace, only
because it is so long and using it multiple times takes up a lot of
extra characters making it harder to read. I have a function that does
the same thing and I call it sql_quote. A matter of preference though.
:)
Pete
> -----Original Message-----
> From: Hayes_Eoin@e... [mailto:Hayes_Eoin@e...]
> Sent: Monday, April 15, 2002 1:59 PM
> To: ASP Databases
> Subject: [asp_databases] RE: Converting to an Integer and then Inserti
> ng I nto SQL Server
>
>
> Ok.But I still keep getting an error:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
> Incorrect syntax near
> ','.
> /Eoinasp/page1.asp, line 139
>
> Here is the code to see if you can figure this out:
>
> Dim nIDNumber, strEmpName, strExtensionNo, nMentorID, nSalaryLevel,
> strTeamID, strSQL, str1
>
> if Request.QueryString<>"" then
> nIDNumber = Request.QueryString("IDNumber")
> strEmpName = Request.QueryString("EmpName")
> strExtensionNo = Request.QueryString("ExtensionNo")
> nMentorID = Request.QueryString("MentorID")
> nSalaryLevel = Request.QueryString("SalaryLevel")
> strTeamID = Request.QueryString("TeamID")
> end if
>
> strSQL = "INSERT INTO Employee (IDNumber, EmpName,
> ExtensionNo, MentorID,
> SalaryLevel, TeamID) VALUES (" & nIDNumber & ",'" &
> StrQuoteReplace(strEmpName) & "','" &
> StrQuoteReplace(strExtensionNo) & "',"
> & nMentorID & "," & nSalaryLevel & ",'" &
> StrQuoteReplace(strTeamID) & "')"
>
>
>
> Conn.Execute(strSQL) 'Line 139
>
>
> Conn.Close
> set Conn = nothing
>
>
> Function StrQuoteReplace(strValue)
> str1 = Replace(strValue, "'", "''")
> StrQuoteReplace = "'" & str1 & "'"
> End Function
>
> -----Original Message-----
> From: Peter Foti (PeterF) [mailto:PeterF@S...]
> Sent: Monday, April 15, 2002 1:53 PM
> To: ASP Databases
> Subject: [asp_databases] RE: Converting to an Integer and
> then Inserting
> I nto SQL Server
>
>
> No. You are generating a SQL string (string being the key word here).
>
> For example:
>
> someint = Request.Form("intTextBox")
> somestr = Request.Form("strTextBox")
>
> SQLStr = "INSERT INTO myDB (myIntField, myStringField) VALUES (" &
> someint & ",'" & somestr & "')"
>
> The way your value appears in the SQL string will specify the
> data type.
> Values that are not surrounded with single quotes are numeric, values
> surrounded by single quotes are characters (or Date in SQL Server).
>
> However, if you wanted to specify the data type, you would use:
> CInt()
> CStr()
> CDbl()
>
> For example:
>
> someint = CStr( Request.Form("intTextBox") )
>
> that would ensure that the value was stored as string data instead of
> numeric data. However, if you wanted to do calculations with the
> value...
>
> someint = CInt( Request.Form("intTextBox") )
> x = someint + 2
>
> Get the picture? Hope this helps.
> Pete
>
>
>
>
> > -----Original Message-----
> > From: Eoin Hayes [mailto:Hayes_Eoin@e...]
> > Sent: Monday, April 15, 2002 5:25 PM
> > To: ASP Databases
> > Subject: [asp_databases] Converting to an Integer and then Inserting
> > Into SQL Server
> >
> >
> > How do I convert a string which I have obtained from a text
> > box on a web
> > page to type Integer. I am using VB Script and want to enter
> > an integer
> > into a SQL server DB using the INSERT keyword. The column
> is of type
> > smallint in the DB so I have to convert first don't I?
> >
>
>
>
Message #8 by Hayes_Eoin@e... on Mon, 15 Apr 2002 13:59:23 -0400
|
|
Ok.But I still keep getting an error:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near
','.
/Eoinasp/page1.asp, line 139
Here is the code to see if you can figure this out:
Dim nIDNumber, strEmpName, strExtensionNo, nMentorID, nSalaryLevel,
strTeamID, strSQL, str1
if Request.QueryString<>"" then
nIDNumber = Request.QueryString("IDNumber")
strEmpName = Request.QueryString("EmpName")
strExtensionNo = Request.QueryString("ExtensionNo")
nMentorID = Request.QueryString("MentorID")
nSalaryLevel = Request.QueryString("SalaryLevel")
strTeamID = Request.QueryString("TeamID")
end if
strSQL = "INSERT INTO Employee (IDNumber, EmpName, ExtensionNo, MentorID,
SalaryLevel, TeamID) VALUES (" & nIDNumber & ",'" &
StrQuoteReplace(strEmpName) & "','" & StrQuoteReplace(strExtensionNo) & "',"
& nMentorID & "," & nSalaryLevel & ",'" & StrQuoteReplace(strTeamID) & "')"
Conn.Execute(strSQL) 'Line 139
Conn.Close
set Conn = nothing
Function StrQuoteReplace(strValue)
str1 = Replace(strValue, "'", "''")
StrQuoteReplace = "'" & str1 & "'"
End Function
-----Original Message-----
From: Peter Foti (PeterF) [mailto:PeterF@S...]
Sent: Monday, April 15, 2002 1:53 PM
To: ASP Databases
Subject: [asp_databases] RE: Converting to an Integer and then Inserting
I nto SQL Server
No. You are generating a SQL string (string being the key word here).
For example:
someint = Request.Form("intTextBox")
somestr = Request.Form("strTextBox")
SQLStr = "INSERT INTO myDB (myIntField, myStringField) VALUES (" &
someint & ",'" & somestr & "')"
The way your value appears in the SQL string will specify the data type.
Values that are not surrounded with single quotes are numeric, values
surrounded by single quotes are characters (or Date in SQL Server).
However, if you wanted to specify the data type, you would use:
CInt()
CStr()
CDbl()
For example:
someint = CStr( Request.Form("intTextBox") )
that would ensure that the value was stored as string data instead of
numeric data. However, if you wanted to do calculations with the
value...
someint = CInt( Request.Form("intTextBox") )
x = someint + 2
Get the picture? Hope this helps.
Pete
> -----Original Message-----
> From: Eoin Hayes [mailto:Hayes_Eoin@e...]
> Sent: Monday, April 15, 2002 5:25 PM
> To: ASP Databases
> Subject: [asp_databases] Converting to an Integer and then Inserting
> Into SQL Server
>
>
> How do I convert a string which I have obtained from a text
> box on a web
> page to type Integer. I am using VB Script and want to enter
> an integer
> into a SQL server DB using the INSERT keyword. The column is of type
> smallint in the DB so I have to convert first don't I?
>
Message #9 by "Peter Foti (PeterF)" <PeterF@S...> on Mon, 15 Apr 2002 13:52:30 -0400
|
|
No. You are generating a SQL string (string being the key word here).
For example:
someint = Request.Form("intTextBox")
somestr = Request.Form("strTextBox")
SQLStr = "INSERT INTO myDB (myIntField, myStringField) VALUES (" &
someint & ",'" & somestr & "')"
The way your value appears in the SQL string will specify the data type.
Values that are not surrounded with single quotes are numeric, values
surrounded by single quotes are characters (or Date in SQL Server).
However, if you wanted to specify the data type, you would use:
CInt()
CStr()
CDbl()
For example:
someint = CStr( Request.Form("intTextBox") )
that would ensure that the value was stored as string data instead of
numeric data. However, if you wanted to do calculations with the
value...
someint = CInt( Request.Form("intTextBox") )
x = someint + 2
Get the picture? Hope this helps.
Pete
> -----Original Message-----
> From: Eoin Hayes [mailto:Hayes_Eoin@e...]
> Sent: Monday, April 15, 2002 5:25 PM
> To: ASP Databases
> Subject: [asp_databases] Converting to an Integer and then Inserting
> Into SQL Server
>
>
> How do I convert a string which I have obtained from a text
> box on a web
> page to type Integer. I am using VB Script and want to enter
> an integer
> into a SQL server DB using the INSERT keyword. The column is of type
> smallint in the DB so I have to convert first don't I?
>
|
|
|
View Cart
