Member Avatar for kingofnothing

hi everybody i dont now whats wrong whith this code below when i run it debug show an error with con.open()

Dim con As New System.Data.SqlClient.SqlConnection
        con.ConnectionString = "Server=iraq\\sqlexpress ; Database=stats ; Trusted_Connection=True ;"
        Dim com As New System.Data.SqlClient.SqlCommand
        com.CommandText = "insert into users values('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "')"
        com.Connection = con
        con.Open()
        com.ExecuteNonQuery()
        con.Close ()

i well happy if there any another method :)

  • 6 Contributors
  • 10 Replies
  • 296 Views
  • 21 Hours Discussion Span
  • Latest Post Latest Post by Oxiegen

Dani AI

Generated

Quick summary of the real issues you ran into and how to avoid them in future.

  • Cause checklist (what to check when con.Open or ExecuteNonQuery fail): verify your connection string and SQL Server instance name, confirm the SQL Server service is running, make sure the account in the connection string has permission, and ensure your INSERT lists the target columns (otherwise column-count/identity errors can happen) — this is the point raised. Wrap DB calls so errors are visible; suggested Try/Catch, but Using blocks are safer and cleaner.

  • Safer pattern (use parameterized SQL and Using so resources are always closed). Put the connection string in Web.config and avoid inline concatenation to eliminate SQL injection and quoting mistakes:

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
    Dim cs As String = System.Configuration.ConfigurationManager.ConnectionStrings("Stats").ConnectionString
    Using cn As New SqlClient.SqlConnection(cs)
        Using cmd As New SqlClient.SqlCommand("INSERT INTO users (fname) VALUES (@fname);", cn)
            cmd.Parameters.Add("@fname", SqlDbType.NVarChar, 200).Value = TextBox1.Text.Trim()
            cn.Open()
            cmd.ExecuteNonQuery()
        End Using
    End Using
End Sub

  • Why your rows sometimes "disappear": Visual Studio can attach a separate copy of the .mdf at runtime (AttachDbFilename / User Instance and the project file-copy behavior). That means the web app may be writing to a different physical file than the one you inspect in Management Studio. To fix: attach the DB to your SQL Server instance and use Initial Catalog=name (no AttachDbFilename), or set the project file's "Copy to Output Directory" to "Do not copy", or use |DataDirectory| consistently and check the actual MDF path in SSMS before/after running.

  • Small clarifications: con.State is read-only (you read it to decide whether to open/close). MessageBox.Show is for desktop apps; in ASP.NET show errors on the page or log them on the server. Using blocks remove the need to manually close the connection and are less error-prone than manually calling Close in Finally.

These changes will make the code safer, make errors visible, and stop the common "I inserted a row but it does not show up" problem that experienced.

Recommended Answers

All 10 Replies

Member Avatar for kingofnothing

There are new events happen when i adjust the connection string to

con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\WebSites\WebSite2\App_Data\stats.mdf;Integrated Security=True;User Instance=True"

show me error with com.ExecuteNonQuery()

any suggestion ?

Member Avatar for kingofnothing

thank you guys i solved the problem

i thought it will work if i sort textboxes Parallel with clumins as they told me
but i must provide more to the inserting code
just like

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim con As New System.Data.SqlClient.SqlConnection
        con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\WebSites\WebSite2\App_Data\stats.mdf;Integrated Security=True;User Instance=True"
        Dim com As New System.Data.SqlClient.SqlCommand
        com.CommandText = "insert into users(fname) values('" & TextBox1.Text & "')"
        com.Connection = con
        con.Open()
        com.ExecuteNonQuery()
        con.Close ()

    End Sub
Member Avatar for Teme64

show me error with com.ExecuteNonQuery()

Could you please post the exact error message that you get. It would help a lot.

I also suggest using Try...Catch...Finally blocks when you deal with databases:

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

      Dim con As New System.Data.SqlClient.SqlConnection
      con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\WebSites\WebSite2\App_Data\stats.mdf;Integrated Security=True;User Instance=True"
      Dim com As New System.Data.SqlClient.SqlCommand
        com.CommandText = "insert into users(fname) values('" & TextBox1.Text & "')"
        Try
            com.Connection = con
            con.Open()
            com.ExecuteNonQuery()
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If con.State = ConnectionState.Open Then
                con.Close()
            End If
        End Try
End Sub

besides, now the messagebox shows the error message.

HTH

Member Avatar for kingofnothing

omg the last code dose not work again

thank you Teme64 but it seem there is a big mistake with my last good

when i run it and fill the text box it works fine but when i check the table data
nothing happen:confused:

Member Avatar for pritesh2010

see this Link this will help you for inserting data in table

Member Avatar for Holy Sage

hi everybody i dont now whats wrong whith this code below when i run it debug show an error with con__ __ .open()

Dim con As New System.Data.SqlClient.SqlConnection
        con.ConnectionString = "Server=iraq___ ___ //sqlexpress ; Database=stats ; Trusted_Connection=True ;"
        Dim com As New System.Data.SqlClient.SqlCommand
        com.CommandText = "insert into users values('" & TextBox1_ _ ___ ___ /Text & "','" & TextBox2.Text & "','" & TextBox3_ ___ ___ /Text & "')"
        com.Connection = con
        con.Open()
        com.ExecuteNonQuery()
        con.____ ____ __ __ ()

[/QUOTE__ __ ____ ____ holy___ ___sage__ __6996__ __

Member Avatar for hirenpatel53 
com.Connection = con

remove this line an then executes

Member Avatar for Oxiegen

You are probably getting an error because you are missing the field names in the INSERT query.
Try this:

Dim con As New SqlClient.SqlConnection("Server=iraq\SQLEXPRESS; Database=stats; Trusted_Connection=True;")
Dim com As New SqlClient.SqlCommand("insert into users (field1,field2,field3) values('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "')", con)

Try
   con.Open()
   com.ExecuteNonQuery()
   con.Close ()
Catch ex As Exception
   ' This will give you the exact error of what's going wrong
   MessageBox.Show(ex.ToString())
End Try
Edited by Oxiegen because: n/a
Member Avatar for kingofnothing

THANK YOU EVERYBODY ALL REPLAYS ABOVE WORKS CORRECTLY

BUT THERE IS SOME THING ELSE

WHY THIS SYNTAX DOSE NOT WORK

con.State = ConnectionState.Open


IM USING VISUAL STUDIO 2005
AND MESSEGBOX HAVE TO REPLACE WITH MSGBOX

WHAT VERSION DO YOU HAVE

thanks

Member Avatar for Oxiegen

con.State gives you the state of the connection.
You can't SET the state that way, you can only read the state.

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

I'm using VS 2008.
And there is absolutely no reason to replace MessageBox with MsgBox.
MsgBox is the VB6 way, MessageBox is the VB.NET way.

Oh, and stop yelling.
We are civilized people here, aren't we?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.