Real Simple Insert - doesn't work

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hey

Trying to do a real simple online Newsletter form - an Insert into a SQL Server DB, but when the submit button is pressed, the page refreshes, but nothing is added to the DB.

Code
Dim ConDB As SqlConnectio
Dim strINSERT As Strin
Dim cmdINSERT As SqlComman

ConDB = New SqlConnection("Persist Security Info=False;User ID=xxx;password=xxx;Initial Catalog=xxx;Data Source=xxx"
strINSERT = "INSERT Newsletter " &
"(FName, SName, Email) " &
"Values ('" & Fname.Text & "','" & Sname.Text & "','" & Email.Text & "')

cmdINSERT = New SqlCommand(strINSERT, ConDB
ConDB.Open(
cmdINSERT.ExecuteNonQuery(
ConDB.Close(

appreciate your assistance guys.
 
Hi Dazza,

Some rows below you see someone with almost the same problem. Miha did
advice to use Insert Into, I use that also in this kind of operations.

:-)

I hope this helps?

Cor
 
First:

Have you put a breakpoint in your code and verified what the actual
statement being passed is?

Second, it may not be your insert statement , but it most likely is. If
your insert statement is legit, and you are sure of it, then look to another
problem, maybe with the postback.

Third, Don't use Dynamic SQL. Especially on the Web. This is suicide
today. Every singe security and ASP.NET piece you can read today warns of
this. There's a good reason. Concatenated dynamic SQL is error prone, a huge
security vulnerability and inefficient...not to mention hard to code and
maintain. Instead use Parameters:


strINSERT = "INSERT Newsletter (FName, SName, Email) Values (@FName,
@SName, @Email)"

First, isn't this a lot easier to read and code? It's also safer, faster
etc so trust me on this. Then assuming your types are all varchar(change
them if they are not:
cmdINsert.Parameters.Add("@FName, SqlDbType.Varchar, 50).Value = FName.Text
cmdINsert.Parameters.Add("@SName, SqlDbType.Varchar, 50).Value = SName.Text
cmdInsert.Parameters.Add("@Email", SqlDbType.Varchar, 50).Value = Email.text

(I assumed the field size in your db is 50 for each field, change the size
or the datatype to match what's in your db).

Here's my blog entry about the subject with an exampel that will walk you
through it http://msmvps.com/williamryan/posts/4063.aspx

Trust me, it's a horrible habit on the desktop, but on the Web, you are
potentially risking your whole database and YES, there are a ton of people
who thought they were too insignificant for anyone to hack who've cost their
company a lot by thinking this. Security through obscurity is not a real
option any more.

HTH,

Bill

Let me know if you have any questinos, I'll be glad to help. Didn't mean to
lecture but just wanted to reinforce the point. Cheers,

Bill
dazzaLondon said:
Hey.

Trying to do a real simple online Newsletter form - an Insert into a SQL
Click to expand...
Server DB, but when the submit button is pressed, the page refreshes, but
nothing is added to the DB..
Code:
Dim ConDB As SqlConnection
Dim strINSERT As String
Dim cmdINSERT As SqlCommand

ConDB = New SqlConnection("Persist Security Info=False;User
Click to expand...
ID=xxx;password=xxx;Initial Catalog=xxx;Data Source=xxx")
 
A lot of times authors do stuff for the sake of clarity or b/c that's what
people are used to. I think if you asked Stephen Walther (the Author of
ASP.NET Unleashed) if he recommends concatenated SQL over parameterized
queries, I can't imagine him saying concatenated sql. Stored procs are the
best way to go b/c you can just give permissions to the proc and not read
write permissions to the users, then you just give the user exec
permissions. Absent that, params escape the values so they are much safer.

Google on "Injection Attack" and you'll find out why it's so dangerous.

If the param version still isn't working, then simplify the
declaration...leave out the type, just include the param name aand the
value...we can debug the rest later but lets get it working first

cmdInsert.Parameters.Add("@FName").Value = FName.Text
cmdINsert.Parameters.Add("@SName").Value = SName.Text
cmdInsert.Parameters.Add("@Email").Value = Email.text

This should work for you. Ignore the type and size for right now, we can
put them in later.

Verify that you have good values for each and make sure you don't throw an
exception. Typically, I'd wrap my open statement in a try catch block
catching SQL Exception, and do the saem with your execute. Put the Close in
the finally block. Lot's of stuff can cause the open to fail and you can't
continue without an open connection, but I'd trap them differently just so I
could respond differently.

Can you post the code you are using with the params..we'll be able tofigure
it out a lot easier then.

HTH,

BIll
dazzalondon said:
Thanks both for help.

William - I've bought 2 books: MS ADO.NET and ASP.NET Unleased and I'm
Click to expand...
following to an extent the code in the books.
I do not fully understand the security issues, but as well as you
Click to expand...
providing an example, ASP.NET unleased has an example of using the .Add way,
so shall follow either if you think that is the way to go --but I think I
tried it this morning but it failed?!!*##
My way of working involves giving a technical problem different attempts
Click to expand...
for a few hours then if it still doesn't work, I move on to other much
needed jobs whilst i wait for a response on the community website! Not
ideal, but it allows me to move forward.
 
Hi agai

Thanks for your advice a week ago Will - em...well, because of the frustration of not suceeding with this simple task, I decided to do other stuff I needed to , but I really really now need to get this real simple thing to work - I have a deadline tomorrow (yes, Sat) and after I have this sorted this INSERT, I have some other INSERTS/UPDATES which follow suit - so this has got to work

So, you asked for my code. Well, I have changed slightly, following your advice of a Sproc + I've enclosed using Param's for secuirity so, but still doesn't work. Here's all my bits

*******But Clic
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs

Dim con As SqlConnectio
Dim strINSET As Strin
Dim cmdINSERT As SqlComman

con = New SqlConnection("xxxxxxx"
cmdINSERT = New SqlCommand("spInsertNewsletter", con
cmdINSERT.CommandType = CommandType.StoredProcedur
cmdINSERT.Parameters.Add("@Title", SqlDbType.Char, 10).Value = TitleDropDown.SelectedItem.Tex
cmdINSERT.Parameters.Add("@FName", SqlDbType.NVarChar, 50).Value = txtFname.Tex
cmdINSERT.Parameters.Add("@SName", SqlDbType.NVarChar, 50).Value = txtSname.Tex
cmdINSERT.Parameters.Add("@Email", SqlDbType.NVarChar, 50).Value = txtEmail.Tex
con.Open(
cmdINSERT.ExecuteNonQuery(
con.Close(

Response.Redirect("xxxxxx"

***** Here's my stored procedure on my SQL Server
CREATE PROCEDURE dbo.spInsertNewslette


@Title Char(10)
@FName nvarChar(50)
@SName nvarChar(50)
@Email nvarChar(50

A
INSERT Newsletter (Title, FName, SName, Email
VALUES (@Title, @FName, @SName, @Email
G

*******Heres' my table in SQL - called 'Newsletter
IDCol - primary, identit
Title - cha
FName - nvarcha
SName - nvarcha
Email - nvarcha
FirstEntered - small date, default = GetDat


Is it something to do with the primary key in the DB - again, not a SQL expert, just need this to work

When I run the whole thing above, it post-backs but doesn't forward to the ack page, nor enter ANY of the data to the DB. Help. This is really a pain.
 
i think this is now sorted...

i built a mock up page and re-did the text boxes, but copied the code, and an error came up with the Title not goping through the Sproc, so that was the issue

i think its alritght now
 
just an update

discovered exactly why my page wasn't working -

Dont know why, but the 'Handles Button1.Click' was missing from the click sub routine!
 
Back
Top