I'm programming something that seems so simple in PHP. It's the SQL Insert using variables (eventually, I want to use data from a Request.Form("field").

Right now, I'm writing the info to a variable...

strEmail = Request.Form("email")
strName = Request.form("name")

The problem is actually writing the sql insert statement...

strSQL_Insert = "INSERT INTO emails (email, name) VALUES (" & "'" & strEmail & "'," & "'" & strName & "'" & ");"

You can't see, but inbetween the quotes there's an apostrophe... " ' "

This works when I use one field only but tells me there are more variables under after the Values than there are columns.

I've tried everything... taking out the quote with the apostrophe to just say..,

VALUES)" & strEmail & strName & ");" - didn't work.

I also tried taking out the semicolon at the end of the statement... didn't work.

Does anyone have any ideas? I know it's all in the syntax.

Thanks
Donna

Hi All...

I figured it out. Basically I have to declare a temp variable and then write the actual variable string into this temp variable.

Ex:

strTemp = "'" & Request.Form("band_name") & "',"
strTemp = strTemp & " '" & Request.Form("email") & "' "

(You can't see the apostrophes & quotes... but it's " ' " & Request.Form("band_name") & " ', ")

Then I apply this strTemp variable into the SQL Insert...

strSQL_Insert = "INSERT INTO tablename (band_name, contactemail) values (" & strTemp & ");"

Connect.Execute strSQL_Insert

I just wanted to share this.

Boy, SQL inserts are way easier in PHP.

If anyone else has a solution, I'm open to it.

Thanks
DonnaZ

it looks like the quotes are a bit messed up

INSERT INTO emails (email, name) VALUES ('" & strEmail & "','" & strName & "');"


to debug SQL strings use

response.write strSQLString 
response.end

just before the call to execute the command, that way you will see the command as the sql server will see it

As Chris said, it looks like a string concatination issue. Nothing to do with the difference between ASP & PHP at all really.

What's even easier (and safer, as far as SQL Injection attacks are concerned), is to use parameterised queries. Such as:

INSERT INTO Table (ColumName1, ColName2) VALUES (@Value1, @Value2)

Not sure how to set the parameters up in ASP though, I'm used to doing it in ASP.NET.

Donna,

The issue you are having has nothing to do with the SQL. Dealing with those single and double quotes is just a real pain in the arse.

Minaki,

You are pretty darned close.

what you have with:

INSERT INTO Table (ColumName1, ColName2) VALUES (@Value1, @Value2)

Is the RAW SQL code you will be passing to the SQL server. you will need to declare those @Values bud. I used to use a combination of what you have here and the solution DonnaZ has.

What mine would look like:

strSQL = "DECLARE email NVARCHAR(254) SET email='"& Request.Form("email")&"'"
strSQL = strSQL + " DECLARE band_name NVARCHAR(254) SET band_name)='" & Request.Form("band_name)&"'")
strSQL = strSQL + " INSERT INTO Table (email, band_name) VALUES (@Value1, @Value2);"

All that having been said: Pull your SQL OUT of your code and you will be much happier . I know there are folks who really, really like writing dynamic SQL inside their aps, I did it for several years, but once I went cold turkey, and pulled out as much of the SQL as possible, I had so much time on my hands I was stunned.

What I would do for this is to create a stored procedure for insert, update, delete. As an example, the INSERT sp might look like this:

DROP PROCEDURE spMySite_User_INSERT
GO
CREATE PROCEDURE spMySite_User_INSERT
@userEmail NVARCHAR(254),
@userName NVARCHAR(254)
AS
INSERT INTO TableUser (email, band_name) VALUES (@userEmail , @userName )
GO

Then in your code your SQL statement looks like this:

Connect.Execute "EXEC spMySite_User_INSERT @userEmail='"&strEmail&"', @userName='"&strBand_name&"';"

Cleaner, more elegant, and you can use that same pice of code over and over again, from any page you want OR any application you want.

Now in ASP.NET the whole thing gets even cooler, because you get to remove all the crud completely.

I build a class for dealing with data including all the connection crud, etc. and in that class have the methods such as INSERT, DELETE, UPDATE, etc. (most of the times it is a single method that dynamically figures our what you aretrying to do based upon table, data passed and a switch)

In an ASP.NET (using C#) page code I use a call like this:

string m = new sqlExecuteSP().spProcessINSERT("INSERT", strEmail, strBand_Name);

Done. The string returned tells me "Success" or "Failure";

That is all the code for it I have on a page : NO SQL!

Now, you do have to do some work on your sqlExecuteSP class, but that is peanuts compared to hand coding all that SQL in the application pages.

Alright, I'll shut up.

print your variables to find out the exact query made & to track errors

Thanks to everyone!

I printed everything out (including how to catch the SQL errors) - excellent.

DonnaZ

I've got a function for this specific purpose that I created. It not only solves this issue, but (even though I never intended for it at the time), it solves the issue of SQL Injection as well.

function SQLComply (Term)
 Term = trim (Term)
 if Term <> "" then
  Term = Replace (Term, chr (39), chr (39) & chr (39))
 end if
 SQLComply = Term
end function

so strName = SQLComply (Request.Form ("name")) would work just fine. And it works with any database without the need for a stored procedure.