none
insert statement does not work

    Question

  • i used a lot of code in asp.net put it doesn`t work in vb i don`t know why?

    i want to insert data in sql table in vb program

    i tried a lot of codes put all does not work only one give me the error codes put i can`t solve the errors

    this is the codes that i used

    1

     Dim con As New SqlConnection
            Dim cmd As New SqlCommand
            Try
                con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
                con.Open()
                cmd.Connection = con
                cmd.CommandText = "INSERT INTO Table1(name, tel) VALUES (@name,@tel)"
               
    
                cmd.Parameters.Add("@name", SqlDbType.NVarChar, 50, "name")
                cmd.Parameters.Add("@tel", SqlDbType.SmallInt, 33)
    
    
               
    
    
                cmd.ExecuteNonQuery()
    
            Catch ex As Exception
                MessageBox.Show("Error while inserting record on table..." & ex.Message, "Insert Records")
            Finally
                con.Close()
            End Try

    2

    this one give no error but i doesn`t insert records

    Dim con As New SqlConnection
            Dim cmd As New SqlCommand
            Try
                con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
                con.Open()
                cmd.Connection = con
                cmd.CommandText = "INSERT INTO Table1(name, tel) VALUES (@name,@tel)"
                cmd.Parameters.Add(New SqlParameter("@name", Data.SqlDbType.NVarChar, 50))
                cmd.Parameters.Add(New SqlParameter("@tel", Data.SqlDbType.SmallInt))
    
                cmd.Parameters("@name").Value = TextBox1.Text.ToString()
                cmd.Parameters("@tel").Value = TextBox2.Text.ToString
    
    
                cmd.ExecuteNonQuery()
    
            Catch ex As Exception
                MessageBox.Show("Error while inserting record on table..." & ex.Message, "Insert Records")
            Finally
                con.Close()
            End Try
    

    only select by using reader and bind in datagrid works

    but any insert or update or delete statement don`t work

    any help please

    Wednesday, March 15, 2017 10:50 PM

All replies

  • Brackets () in ?
    cmd.Parameters("@tel").Value = TextBox2.Text.ToString
    Wednesday, March 15, 2017 11:21 PM
  • what do you mean?

    it`l the same i do

    do you mean i should delete brackets

    Wednesday, March 15, 2017 11:44 PM
  • what do you mean?

    it`l the same i do

    do you mean i should delete brackets

     That will not make any difference if the () brackets are on the .ToString part or not.  However,  you do not even need to use the .ToString method on a Text property because,  a Text Property is already a String type and does not need to be converted to a String.  It could be just this...

                cmd.Parameters("@name").Value = TextBox1.Text

     

     I see you have the other parameter set to a SmallInt type which in VB.Net is the equivalent to a signed Short type or Int16 which is a 2 byte integer that has a max value of 32767 which would not be long enough for a phone number.  So,  i am not so sure that looks correct to me. Even an Int type would only have a maximum of 2147483647.  A sting type would be more appropriate for a phone number.

      Although,  that is most likely not going to fix your problem,  it is just a correction to the code that you have shown.  I am sure someone will respond that is familiar with SQL,  i am not so familiar with it at all.

     8)


    If you say it can`t be done then i`ll try it

    • Edited by IronRazerz Thursday, March 16, 2017 12:33 AM
    Wednesday, March 15, 2017 11:59 PM
  • You cannot make a connectionstring in the way you do in ASP.Net. 

    Asp.Net programs run on the server and are only shown in a browser on a client. 

    You try to address a local database on the Server, but that does not go in the way you do it. 

    Try for this the Microsoft Asp.Net forums

    http://forums.asp.net


    Success
    Cor


    Thursday, March 16, 2017 12:10 AM
  • Hello,

    It's impossible to provide advice if we don't know the error message. One guess, and it's only a guess is if you can read and not insert/update or delete there is a authentication issue. With that you may want to try in the connection string Integrated security=false. 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, March 16, 2017 1:27 AM
    Moderator
  • thanx for trying to help me i understand what you say i`m trying to make a little demo to be  sure that the code will  be correct and i`ll make the real one

    sorry it can`t be done

    Thursday, March 16, 2017 5:29 AM
  • i did n`t try the connection of asp.net i make the connection that in my code i mean that the  code above worked correctly in asp.net but i want to uses it to make a vb.net program i only need to change the connection and when i do that the code does n`t work i don`t know why?
    Thursday, March 16, 2017 5:32 AM
  • Hi chikidodo85,

    I test your code, It works fine. Since you have no error message,  so I suggest you to check your database is correct, and then you can check SQL line in database and see if it works fine. If everything is ok, you can change your SQL Server connection strings and try it again.

    Best Regards,

    Cherry Bu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 16, 2017 7:21 AM
    Moderator
  • First look to the "copy to newer problem". 

    That is in Solution Explorer you click on the database. 

    Now you select the property and then I set it to "never"

    Karen has a very good page about this. 


    Success
    Cor

    Thursday, March 16, 2017 10:14 AM