11 Replies - 5378 Views - Last Post: 03 September 2013 - 10:56 AM Rate Topic: -----

#1 Inferno1674  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 24-April 12

Insert Visual studio 2012 text box to SQL Server 2012 Database

Posted 03 September 2013 - 05:20 AM

Hi.
I have one textbox called txtName.text. I need users to put in their name, click the submit button, and have it save to my SQL Server 2012 Database. I have been trying for a long time and have made no real progress, if you could help it would be excellent. I can include one of my attempts but I doubt it would be much help - I get like 20 errors each time.
Thanks
Imports System.Data.SqlClient
Imports System.Data

Partial Class _Default
    Inherits System.Web.UI.Pag

    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim con As SqlConnection
        Dim commandsql As SqlCommand
        Dim citestedate As SqlDataReader
        Dim ra As Integer
        con = New SqlConnection("Server=HTZC91C7005H;Database=FredDB;Trusted_Connection=True;")
        con.Open()
        commandsql = New SqlCommand("insert into dbo.Fred_Test([Name]) values (' " & TextBox1.Text & "')", con)
        commandsql.ExecuteNonQuery()
        MessageBox.Show("New Row Inserted" & ra)
        con.Close()
    End Sub


Is This A Good Question/Topic? 0
  • +

Replies To: Insert Visual studio 2012 text box to SQL Server 2012 Database

#2 Michael26  Icon User is offline

  • DIC-head, major DIC-head
  • member icon

Reputation: 355
  • View blog
  • Posts: 1,524
  • Joined: 08-April 09

Re: Insert Visual studio 2012 text box to SQL Server 2012 Database

Posted 03 September 2013 - 05:26 AM

Check insert query, put a breakpoint on that line 14 and see what query do you get, and please stop concatenating your sql strings like that use sql parameters

Insert query has this format
INSERT INTO table_name
VALUES (value1,value2,value3,...);
or

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);


Was This Post Helpful? 0
  • +
  • -

#3 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 461
  • View blog
  • Posts: 3,158
  • Joined: 12-January 10

Re: Insert Visual studio 2012 text box to SQL Server 2012 Database

Posted 03 September 2013 - 05:26 AM

show us your errors
Was This Post Helpful? 0
  • +
  • -

#4 Inferno1674  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 24-April 12

Re: Insert Visual studio 2012 text box to SQL Server 2012 Database

Posted 03 September 2013 - 05:37 AM

Error 7 Value of type 'ASP.default_aspx' cannot be converted to 'System.Web.UI.Page'. C:\Users\F.brown\AppData\Local\Temp\Temporary ASP.NET Files\root\0dcd1b8d\927c77df\App_Web_p4px10ao.0.vb
Error 8 Value of type 'ASP.default_aspx' cannot be converted to 'System.Web.UI.Page'. C:\Users\F.brown\AppData\Local\Temp\Temporary ASP.NET Files\root\0dcd1b8d\927c77df\App_Web_p4px10ao.0.vb
Error 9 Value of type 'ASP.default_aspx' cannot be converted to 'System.Web.UI.Page'. C:\Users\F.brown\AppData\Local\Temp\Temporary ASP.NET Files\root\0dcd1b8d\927c77df\App_Web_p4px10ao.0.vb
Error 10 Value of type 'ASP.default_aspx' cannot be converted to 'System.Web.UI.Page'. C:\Users\F.brown\AppData\Local\Temp\Temporary ASP.NET Files\root\0dcd1b8d\927c77df\App_Web_p4px10ao.0.vb
Warning 18 Unused local variable: 'ra'. c:\Users\f.brown\documents\visual studio 2012\WebSites\WebSite9\Default.aspx.vb 10 13 WebSite9
Error 17 Type 'System.Web.UI.Pag' is not defined. c:\Users\f.brown\documents\visual studio 2012\WebSites\WebSite9\Default.aspx.vb 5 14 WebSite9
Error 12 sub 'ProcessRequest' cannot be declared 'Overrides' because it does not override a sub in a base class. C:\Users\F.brown\AppData\Local\Temp\Temporary ASP.NET Files\root\0dcd1b8d\927c77df\App_Web_p4px10ao.0.vb
Error 6 property 'SupportAutoEvents' cannot be declared 'Overrides' because it does not override a property in a base class. C:\Users\F.brown\AppData\Local\Temp\Temporary ASP.NET Files\root\0dcd1b8d\927c77df\App_Web_p4px10ao.0.vb
Error 11 function 'GetTypeHashCode' cannot be declared 'Overrides' because it does not override a function in a base class. C:\Users\F.brown\AppData\Local\Temp\Temporary ASP.NET Files\root\0dcd1b8d\927c77df\App_Web_p4px10ao.0.vb
Error 4 Class 'default_aspx' must implement 'Sub ProcessRequest(context As HttpContext)' for interface 'System.Web.IHttpHandler'. C:\Users\F.brown\AppData\Local\Temp\Temporary ASP.NET Files\root\0dcd1b8d\927c77df\App_Web_p4px10ao.0.vb
Error 3 Class 'default_aspx' must implement 'ReadOnly Property IsReusable As Boolean' for interface 'System.Web.IHttpHandler'. Implementing property must have matching 'ReadOnly' or 'WriteOnly' specifiers. C:\Users\F.brown\AppData\Local\Temp\Temporary ASP.NET Files\root\0dcd1b8d\927c77df\App_Web_p4px10ao.0.vb
Error 13 'ProcessRequest' is not a member of '_Default'. C:\Users\F.brown\AppData\Local\Temp\Temporary ASP.NET Files\root\0dcd1b8d\927c77df\App_Web_p4px10ao.0.vb
Error 14 'InitializeCulture' is not a member of 'ASP.default_aspx'. c:\Users\f.brown\documents\visual studio 2012\WebSites\WebSite9\Default.aspx 1
Error 5 'GetWrappedFileDependencies' is not a member of 'ASP.default_aspx'. C:\Users\F.brown\AppData\Local\Temp\Temporary ASP.NET Files\root\0dcd1b8d\927c77df\App_Web_p4px10ao.0.vb
Error 1 'Context' is not a member of '_Default'. C:\Users\F.brown\AppData\Local\Temp\Temporary ASP.NET Files\root\0dcd1b8d\927c77df\App_Web_p4px10ao.0.vb
Error 2 'Context' is not a member of '_Default'. C:\Users\F.brown\AppData\Local\Temp\Temporary ASP.NET Files\root\0dcd1b8d\927c77df\App_Web_p4px10ao.0.vb
Error 15 'Context' is not a member of '_Default'. c:\Users\f.brown\documents\visual studio 2012\WebSites\WebSite9\Default.aspx 1 1 WebSite9
Error 16 'Context' is not a member of '_Default'. c:\Users\f.brown\documents\visual studio 2012\WebSites\WebSite9\Default.aspx 1 1 WebSite9


Im not sure what App_Web_p4px10ao.0.vb even is it just turns up.
Was This Post Helpful? 0
  • +
  • -

#5 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 461
  • View blog
  • Posts: 3,158
  • Joined: 12-January 10

Re: Insert Visual studio 2012 text box to SQL Server 2012 Database

Posted 03 September 2013 - 05:46 AM

try this right after your partial class--- i think you spelled it wrong:

Inherits System.Web.UI.Page

Was This Post Helpful? 1
  • +
  • -

#6 Inferno1674  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 24-April 12

Re: Insert Visual studio 2012 text box to SQL Server 2012 Database

Posted 03 September 2013 - 06:02 AM

Well that is embarrassing, thanks very much!(I seem to leave out a letter or something at least once every time I try to programme)

I remade the database and this is my current code:

Imports System.Data.SqlClient
Imports System.Data

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim con As SqlConnection
        Dim commandsql As SqlCommand
        con = New SqlConnection("Server=HTZC91RC700005H;Database=Register;Trusted_Connection=True;")
        con.Open()
        commandsql = New SqlCommand("insert into dbo.NameTable(name) values txtName.text")
        commandsql.ExecuteNonQuery()
        con.Close()
    End Sub

End Class


It doesn't stop it from debugging, but it does fail when I enter a value. It has a problem with the line

con.Open()

any more ideas?
Thanks a lot for all the help so far!
Was This Post Helpful? 0
  • +
  • -

#7 Michael26  Icon User is offline

  • DIC-head, major DIC-head
  • member icon

Reputation: 355
  • View blog
  • Posts: 1,524
  • Joined: 08-April 09

Re: Insert Visual studio 2012 text box to SQL Server 2012 Database

Posted 03 September 2013 - 06:21 AM

Is the connection state Open or close, connection state is a property of SqlConnection class.

 
'values should be in quotes
'Example 
'INSERT INTO Customers (CustomerName, City, Country)
'VALUES ('Cardinal', 'Stavanger', 'Norway');
commandsql = New SqlCommand("insert into dbo.NameTable(name) values txtName.text")

This post has been edited by Michael26: 03 September 2013 - 06:25 AM

Was This Post Helpful? 1
  • +
  • -

#8 Inferno1674  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 24-April 12

Re: Insert Visual studio 2012 text box to SQL Server 2012 Database

Posted 03 September 2013 - 07:00 AM

Thanks Michael

I think the only thing not working is the connectionstring. on http://www.connectio...s.com/sqlserver there are a few it gives such as:
Server=myServerAddress;Database=myDataBase;User Id=myUsername;
Password=myPassword;
Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;
Server=myServerName\myInstanceName;Database=myDataBase;User Id=myUsername;
Password=myPassword;

I'm not sure which to use. For username and password, would I use the details I use to get into the computer, or to get into SQL Server (which I never set - I used windows authentication or whatever its called)?
Was This Post Helpful? 0
  • +
  • -

#9 Michael26  Icon User is offline

  • DIC-head, major DIC-head
  • member icon

Reputation: 355
  • View blog
  • Posts: 1,524
  • Joined: 08-April 09

Re: Insert Visual studio 2012 text box to SQL Server 2012 Database

Posted 03 September 2013 - 07:45 AM

Trusted Connection uses the same credentials as your Windows user account, connection string with user Id and Password doesn't

Server=myServerAddress (Default instance)
Server=myServerName\myInstanceName (Named instance)

One is named instance and other is default instance, you can have only one default and several named instances. If you have more than one sql servers installed you have 1 default instance and others would be named instances. It is best to install only a default instance unless you have a special need to install multiple SQL Server instances on the same host, such as running differrent versions concurrently or other isolation requirements. These additional instances must be named. The difference is only in the way they connect (it's the matter of network connectivity). Clients can connect to the default instance using only the host name over the well-known 1433 port. To connect to a named instance, clients specify the host and instance name (e.g. "MyHost\My_Instance") and the SQL Server Browser service returns the port the named instance is listening on.

Server=myServerAddress;Database=myDataBase;User Id=myUsername; Password=myPassword; //Default instance with user id and password
Server=myServerAddress;Database=myDataBase;Trusted_Connection=True; //Default instance with same credentials as currently logged in Windows user
Server=myServerName\myInstanceName;Database=myDataBase;User Id=myUsername; Password=myPassword; //Named instance with user id and password

This post has been edited by Michael26: 03 September 2013 - 07:57 AM

Was This Post Helpful? 1
  • +
  • -

#10 Inferno1674  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 24-April 12

Re: Insert Visual studio 2012 text box to SQL Server 2012 Database

Posted 03 September 2013 - 08:07 AM

Thanks you once again.

This is my current code:

It doesn't work :(
No errors or anything, it just doesn't update the table.
I removed this part : commandsql.ExecuteNonQuery()
because it caused an error and I couldn't work out what it did anyway. Does anyone know what is still incorrect, or what I need to add?

forgot to paste code -_-

Imports System.Data.SqlClient
Imports System.Data

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim con As SqlConnection
        Dim commandsql As SqlCommand
        con = New SqlConnection("Server=HTZC91RC700005H;Database=Register;Trusted_Connection=True") 'not sure on correct format/server/un/pw
        con.Open() 'opens the connection
        commandsql = New SqlCommand("insert into dbo.NameTable(Name) values 'txtName.text'")
        con.Close()
    End Sub

End Class


one extra point, do I need the tablename with the dbo. at the start? (when i saved the table i just called it nametable, it added the dbo. itself)
Was This Post Helpful? 0
  • +
  • -

#11 Michael26  Icon User is offline

  • DIC-head, major DIC-head
  • member icon

Reputation: 355
  • View blog
  • Posts: 1,524
  • Joined: 08-April 09

Re: Insert Visual studio 2012 text box to SQL Server 2012 Database

Posted 03 September 2013 - 08:33 AM

ExecuteNonQuery runs the query, you need that part, make sure your insert query is correct try running it in sql server first to see what result does it give you.
Was This Post Helpful? 0
  • +
  • -

#12 rgfirefly24  Icon User is offline

  • D.I.C Lover
  • member icon


Reputation: 267
  • View blog
  • Posts: 1,476
  • Joined: 07-April 08

Re: Insert Visual studio 2012 text box to SQL Server 2012 Database

Posted 03 September 2013 - 10:56 AM

You also won't get the result you think from your INSERT statement.

The query you have right now says this:

Insert the value txtName.text into the column Name in table dbo.NameTable (note i did not say the value of txtName.text, but txtName.text as the value).

Basically to make it work change line 12 to be this:

commandsql = New SqlCommand("insert into dbo.NameTable(Name) values ('" & txtName.text & "')")



that will take the value OF txtName.text and insert it into the query.

Now because the query as above is not safe you will want to look into using parameters with your SqlCommand.

This post has been edited by rgfirefly24: 03 September 2013 - 10:58 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1