Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 736
  • 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
            Try
                con.ConnectionString = "Data Source=CDPC1093\CDPC1093;Initial Catalog=tba210;Integrated Security=True"
                con.Open()
                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)
                cmd.ExecuteNonQuery()

            Catch ex As Exception
                MessageBox.Show("Error while inserting record on table..." & ex.Message, "Insert Records")
            Finally
                con.Close()
            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?
0
LUISOFO
Asked:
LUISOFO
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.

try:
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
0
 
LUISOFOAuthor Commented:
Right to the point. Thanks for your input
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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