Cannot see my updated rows in the database

[holyskylark58]

Using this code i cannot insert data in the database . it gives no error but no row is created in the data base

Imports System.Data.SqlClient
Public Class Form1
    Dim con As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Initial Catalog=Database1;Integrated Security=True;User Instance=True")



    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If con.State = ConnectionState.Open Then
            con.Close()
        End If

        Try
            If RichTextBox1.Text.Length = 0 Then
                MsgBox("MemId field cannot be empty insert value")
            Else
                con.Open()

                Dim instr As String = "INSERT INTO member VALUES('" + RichTextBox1.Text + "','" + RichTextBox2.Text + "','" + RichTextBox3.Text + "','" + RichTextBox4.Text + "','" + RichTextBox5.Text + "','" + RichTextBox6.Text + "')"
                Dim cmd As New SqlCommand(instr, con)
                MsgBox("Database connected successfully")
                If cmd.ExecuteNonQuery() > 0 Then
                    Label8.Text = "*Record created successfully*"
                    cmd.Dispose()
                End If
                cmd.Dispose()
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
            con.Close()

        End Try

    End Sub

[tlhintoq]

Until someone with more experience can offer more targeted advice I can point you toward these:

Database How-to parts 1 and 2
Database tutorial Part 1
Database tutorial Part 2

[RedSon]

Well I don't have more experience but this looks a little fishy:

If con.State = ConnectionState.Open Then
            con.Close()
End If

An assignment in an if statement?

[tlhintoq]

Well I don't have more experience but this looks a little fishy:

If con.State = ConnectionState.Open Then
            con.Close()
End If

Assignment statement? Do you mean the single equal sign? Right side of equal being assigned to object on left side of equal?

This is VB. They use a single equal sign for comparrison. Where C# uses two.

'VB
if con.State = ConnectionState.Open 
    then con.Close()
End If

//C#
if (con.State == ConnectionState.Open) 
{
   con.Close();
}

[RedSon]

Then how do you assign something in VB?

[tlhintoq]

Also with a single = as in line 22 of the OP code.

It is a comparrison and an assignment, depending on whether or not it is between an 'if' and a 'then'

Don't shake your head at me... I don't use VB for reasons just like this.
68020 assembly to C, to C++ to C# and working on Objective-C

[holyskylark58]

hey please stop asking such question if can you solve my problem then please answer me. it's very urgent.
moderator i have gone through the link you have given but whatever it says i hv done those in my code but still it is not worinkg how strange when i am using select command it is prefctly returning value as desired but it can not insert data in the database.

[tlhintoq]

Hey - Terribly sorry if someone else's learning in some way interfered with your own.

You posted this in the VB.Net area, even though there is an SQL area. I'll move the thread there if you like. It might get the attention of someone with more SQL experience. Let me know if you would like me to do this.

[CroCrew]

Hello holyskylark58,

Try doing it like this. Remember to change the “BOLD” text with the correct values corresponding to your database and the fields within it.

Happy coding,
CroCrew

Imports Microsoft.VisualBasic
Imports System.Data.SqlClient

Public Class Form1 
    Private Function ConnectionString() As String
        ConnectionString = "Data Source=[B][I][U]IP to Database server[/U][/I][/B];Network Library=DBMSSOCN;Initial Catalog=[B][I][U]DatabaseName[/U][/I][/B];User ID=[B][I][U]DatabaseUserName[/U][/I][/B];Password=[B][I][U]Password[/U][/I][/B];"
    End Function
	
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click 
        Try 
            If RichTextBox1.Text.Length = 0 Then 
                MsgBox("MemId field cannot be empty insert value") 
            Else 
				Using connection As New SqlConnection(ConnectionString())
					connection.Open()

					Dim commandText As String = "INSERT INTO member VALUES ([B][I][U]Field1[/U][/I][/B], [B][I][U]Field2[/U][/I][/B], [B][I][U]Field3[/U][/I][/B], [B][I][U]Field4[/U][/I][/B], [B][I][U]Field5[/U][/I][/B], [B][I][U]Field6[/U][/I][/B]) VALUES (@Field1, @Field2, @Field3, @Field4, @Field5, @Field6)"

					Using command As New SqlCommand(commandText, connection)
						command.Parameters.AddWithValue("@Field1", RichTextBox1.Text)
						command.Parameters.AddWithValue("@Field2", RichTextBox2.Text)
						command.Parameters.AddWithValue("@Field3", RichTextBox3.Text)
						command.Parameters.AddWithValue("@Field4", RichTextBox4.Text)
						command.Parameters.AddWithValue("@Field5", RichTextBox5.Text)
						command.Parameters.AddWithValue("@Field6", RichTextBox6.Text)
						command.CommandTimeout = 300
						command.ExecuteNonQuery()
					End Using
				End Using
				
				If connection.State = Data.ConnectionState.Open Then
					connection.Close()
					connection.Dispose()
				End If	

                Label8.Text = "*Record created successfully*" 
            End If 
        Catch ex As Exception 
            MsgBox(ex.Message) 
        End Try 
    End Sub

[holyskylark58]

thank you friend. i am really greatfu to you.
plzz if you can plzz move this thread to the sql area . actually i thought as this code is written in vb.net it'll be best to be post it here.
now if you think that to move it to the sql area will be better plzz do it.

[holyskylark58]

to: CroCrew
can you please tell me what the IP to Database server; should be in line 6? i am not so familiar with this. where i can get this name. actually i get the connectin string from the app.config file. is it right to do that?
if not plzz tell me the correct way.

[CroCrew]

Where is your "SQLEXPRESS " database located? On a server? On your computer?

If it is on your local your computer use 127.0.0.1

If it is on a server then find out what the IP of that server is and use that.


CroCrew~

[RedSon]

In this case isnt SQLEXPRESS a named pipe to the SQLEXPRESS server instance running locally. If you are using SQLEXPRESS it's running as a local server.

[RedSon]

Hey - Terribly sorry if someone else's learning in some way interfered with your own.

thanks for the lulz!

[CroCrew]

Maybe I never use SQLEXPRESS. Thanks for the added help RedSon.