• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 738
  • Last Modified:

insert into sql server vb.net

Hello there. This is my question. After searching the web I found this code for inserting a new record into an SQL Server db, from a control behind VB.NET

            Dim con As New SqlConnection
            Dim cmd As New SqlCommand
                con.ConnectionString = "Data Source=CDPC1093\CDPC1093;Initial Catalog=tba210;Integrated Security=True"
                cmd.Connection = con
                cmd.CommandText = "INSERT INTO T_USR(N_USER_NO, S_NAME,S_USER_TYPE,D_REG,D_PERIOD_ST,D_PERIOD_END) VALUES (" & Gedetineerde_systeem_ID.Text & ", '" & Achternaam.Text & "' ,2," & Today & "," & Today & "," & DateAdd(DateInterval.Year, 1, Today)

            Catch ex As Exception
                MessageBox.Show("Error while inserting record on table..." & ex.Message, "Insert Records")
            End Try

The fact is that I am always getting back the error message, meaning the insertion was not successful.

Could any of you guys give me a hand on this? What am I doing wrong or letting behind?
1 Solution
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you would need to post the error message you get, to start the troubleshooting.
I presume you get a syntax error, by inspecting the commandtext you will see that it's not correct.

cmd.CommandText = "INSERT INTO T_USR(N_USER_NO, S_NAME,S_USER_TYPE,D_REG,D_PERIOD_ST,D_PERIOD_END) VALUES (" & Gedetineerde_systeem_ID.Text & ", '" & Achternaam.Text & "' ,2,getdate(),getdate(), dateadd(year,1,getdate()) ) "

Open in new window

first, you where missing the closing ) for the VALUES() part
second, Today will be a "date", but by your " string " & " string" it will be no more a "date" for teh sql (see also this article: http://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html )
finally,  you should read up on how to create a SqlCommand with Parameters to avoid SQL injection
LUISOFOAuthor Commented:
Right to the point. Thanks for your input
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now