Simple concatenating textbox input to create SQL String

[JH82]

Apologies if this is in the wrong place guys.

Always struggle with concatenating textbox input for SQL Strings.


I have the follwowing:

strSales = "INSERT MortgageSales VALUES ('" & dateSub.Text & "'" & dateComp.Text & ")"


When running the program it is giving an "incorrect syntax" error for this statement.

I assume i have the speech marks and what not muddled up

Can anyone point me in the right direction?

Many thanks

 

[jmcilhinney]

Always struggle with concatenating textbox input for SQL Strings.

It should NEVER be a struggle because you should simply NEVER do it. If you do things the proper way and use parameters then all your problems go away.

First of all, all you would have had to do was view the String you created in a MessageBox or somewhere else to see the issue. That is about as simple as debugging gets. You'd have quickly seen that it was invalid syntax because it would look something like this:

INSERT MortgageSales VALUES ('XXX'XXX)

It's fairly obvious what the problem is there.

Secondly, while it's legal it is poor practice to not specify the names of the columns you're inserting into.

Thirdly, if you want the user to enter a date then you should be using a DateTimePicker, not a TextBox.

Finally, you should be using parameters rather than string concatenation, as I said.

Put all that together and you get something like:

Dim command As New SqlCommand("INSERT INTO MortgageValues (Sub, Comp) VALUES (@Sub, @Comp)", connection)

command.Parameters.AddWithValue("@Sub", subDatePicker.Value.Date)
command.Parameters.AddWithValue("@Comp", compDatePicker.Value.Date)

 

[JH82]

Aah right, thanks for that. I am using a datetimepicker but i just put down the incorrect property for it.

I will look into doing it the parameters way.

Thanks

 

[Arg81]

I will look into doing it the parameters way.

Parameters are the way to go! Im not being intrusive, but is there any reason you cannot use VS2005? Even the express version (free) is much much better than VS2003 and .Net 1.1

Data access, manipulation, display etc is so much easier in 2005. I started out with VS2003 and when I saw what 2005 could do, I upgraded immediately. It seriously has made my life so much easier when creating data-access apps!

 

[JH82]

I am on 2003 coz i have to do my qualification in 2003 unfortunately

 

[Arg81]

ah,

Well once you've passed your qualifications, get VS2005

surprised that they are still teaching VS2003!

 

[JH82]

Aye, tell me about it.

To be honest, the pace i've been going, they will be up to VS2010 by the time i get my MCSD lol.

About to take my 70-306 and struggling real bad to be fair

 

[Arg81]

(digresses away from main topic, I know )

Oh cool, didn't realise that's what you are studying towards. I thought that MS would of upgraded the exam!

I'm studying 2 MCSEs at the moment, self-taught VB.net and ADO.net - not sure whether I like the technical or programmer career yet

Who are you doing your course with? I'm studying mine through NITLC.

(goes back to main topic)

Let us know if you get any problems with putting together your SQL using parameters

 

[JH82]

I'm studying with Computeach. Signed up to do MCAD and MCSD.

Just took a mock 70-306 and only got 54% so not looking good at the moment and i'm rubbish at revising.

(back on topic)

just doing the parameters now. There are loads of columns so it's taking a while. Knocking off in a bit so will probably finish it off tomorrow.

Thanks for your help

 

[jmcilhinney]

As you're using .NET 1.1 I should point out that there is no AddWithValue method. You use Add instead in that case.

 

[JH82]

Yeah i gathered i had to use that instead.

Done all the parameters and tested it but got a problem with my connection it seems. Just trying to sort that out

 

[cjard]

On parameters, read the PQ link in my signature.. On connections.. Make a file called A.UDL on your dekstop (empty text file)
DOuble click it, set the GUI options, test your conenction, save, close.. Now open the UDL file in notepad.. there's your valid conenction string