11 Replies - 801 Views - Last Post: 28 September 2010 - 01:28 AM Rate Topic: -----

#1 nazfera2  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 16-September 10

Need help with a button to save this code.

Posted 24 September 2010 - 06:36 AM

i need help with this button, im a college student im in my last year of college and im doing the internship now.

what i need is to make a program connecting the textbox to the sql table.
i already made the connection but im stuck with the button.
what code could i use to let the user save the textbox ?????
Private Sub SaveContractor_Info()
Dim sqlconnect As New SqlClient.SqlConnection
Try
' Assign Connection String
sqlconnect.ConnectionString = ("Data Source=localhost\SQLEXPRESS;Initial Catalog=Estudiantes;Integrated Security=True")

' Open the connection
sqlconnect.Open()
' Build the SQL command and assign it to the 
' Command Text property of the Command object
Dim i As Integer = 0
Dim sql As String = ""

sql = "INSERT INTO Cit_Student ("
sql &= ", Nombre"
sql &= ", Telefono"
sql &= ", Email"
sql &= ", Sexo"
sql &= ", Direccion"
sql &= ", Edad"
sql &= ", Fecha del curso"
sql &= ", Nombre de escuela) VALUES ('"
sql &= ", '" & Nombrebox.Text & "'"
sql &= ", '" & telefonoMaskedTextBox.Mask & "'"
sql &= ", '" & EmailTextBox.Text & "'"
sql &= ", '" & GenderMaskedTextBox.Mask & "'"
sql &= ", '" & DireccionTextBox.Text & "'"
sql &= ", '" & EdadMaskedTextBox.Mask & "'"
sql &= ", '" & Fecha_del_cursoMaskedTextBox.Mask & "'"
sql &= ", '" & Nombre_de_escuelaTextBox.Text & "')"

Debug.Print(sql)
Dim sqlcon As New SqlClient.SqlCommand(sql, sqlconnect)

' Execute the Command object as a non-query,
' meaning that no returned values are expected. 
sqlcon.ExecuteNonQuery()

Catch e1 As Exception
MsgBox("Problemas guardando la información del Contractor" & e1.Message, MsgBoxStyle.Critical, "Mensaje de Error")
Finally
' Always close a Connection when you are done. 
sqlconnect.Close()
End Try
End Sub


This post has been edited by macosxnerd101: 24 September 2010 - 08:19 AM
Reason for edit:: Added code tags. Please use them in the future.


Is This A Good Question/Topic? 0
  • +

Replies To: Need help with a button to save this code.

#2 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1606
  • View blog
  • Posts: 5,163
  • Joined: 25-September 09

Re: Need help with a button to save this code.

Posted 24 September 2010 - 08:29 AM

I hope I'm understanding this correctly but I'll post a solution to what I think you're wanting to do.

Add a button to the form. Double-Click on the button to enter into the buttons .Click event

call the routine

Example:
btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
  SaveContractor_Info()
End Sub


Also, you should look into using parameters to build your sql statements. Makes them easier to read, build and protects you from sql injections.

Another heads up... the field names that contain spaces need to be enclosed in square brackets (e.g. [Fecha del curso] & [Nombre de escuela])

An example of using parameters would be:
sql = "INSERT INTO Cit_Student (Nombre, Telefono) VALUES (@Nombre, @Telefono)
Dim sqlcon As New SqlClient.SqlCommand(sql, sqlconnect)
sqlcon.Parameters.AddWithValue("@Nombre", Nombrebox.Text)
sqlcon.Parameters.AddWithValue("@Telefono", Telefono.text)

sqlcon.ExecuteNonQuery

Of course, you would do this for each field in your database.

This post has been edited by CharlieMay: 24 September 2010 - 08:34 AM

Was This Post Helpful? 1
  • +
  • -

#3 nazfera2  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 16-September 10

Re: Need help with a button to save this code.

Posted 24 September 2010 - 07:27 PM

View PostCharlieMay, on 24 September 2010 - 07:29 AM, said:

I hope I'm understanding this correctly but I'll post a solution to what I think you're wanting to do.

Add a button to the form. Double-Click on the button to enter into the buttons .Click event

call the routine

Example:
btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
  SaveContractor_Info()
End Sub


Also, you should look into using parameters to build your sql statements. Makes them easier to read, build and protects you from sql injections.

Another heads up... the field names that contain spaces need to be enclosed in square brackets (e.g. [Fecha del curso] & [Nombre de escuela])

An example of using parameters would be:
sql = "INSERT INTO Cit_Student (Nombre, Telefono) VALUES (@Nombre, @Telefono)
Dim sqlcon As New SqlClient.SqlCommand(sql, sqlconnect)
sqlcon.Parameters.AddWithValue("@Nombre", Nombrebox.Text)
sqlcon.Parameters.AddWithValue("@Telefono", Telefono.text)

sqlcon.ExecuteNonQuery

Of course, you would do this for each field in your database.




alright i added the button you provided me and it dose not seem to show me the change on the data grid when i click the button.

i used parameters as you suggested and i see no change but ill stick with parameters because of what you said of security.

i changed the code to this.

Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.VisualBasic


Public Class Form1

    Dim Datadapt As SqlDataAdapter
    Dim Dset As DataSet
    Dim Drow As DataRow
    Dim sqlconnect As SqlClient.SqlConnection

    Dim Sql As String

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub
    Private Sub Savecit_Student()
        Try

            sqlconnect.ConnectionString = ("Data Source=localhost\SQLEXPRESS;Initial Catalog=Estudiantes;Integrated Security=True")

            sqlconnect.Open()


            Sql = "INSERT INTO Cit_Student (Nombre, Edad,Sexo,Telefono,Email,Direccion,Fecha del curso,Nombre de escuela,) VALUES (@Nombre,@Edad,@Sexo,@Telefono,@Email,@Direccion,@Fecha del curso,@Nombre de escuela)"
            Dim sqlcon As New SqlClient.SqlCommand(Sql, sqlconnect)
            sqlcon.Parameters.AddWithValue("@Nombre", NombreBox.Text)
            sqlcon.Parameters.AddWithValue("@Edad", EdadMTBox.Mask)
            sqlcon.Parameters.AddWithValue("@Sexo", GenderMTBox.Mask)
            sqlcon.Parameters.AddWithValue("@Telefono", TelefonoMTBox.Mask)
            sqlcon.Parameters.AddWithValue("@Email", EMAILTextBox.Text)
            sqlcon.Parameters.AddWithValue("@Direccion", DireccionTextBox.Text)
            sqlcon.Parameters.AddWithValue("@Fecha del curso", FechadelcursoMTBox.Mask)
            sqlcon.Parameters.AddWithValue("@Nombre de escuela", NombredeescuelaTextBox.Text)

            sqlcon.ExecuteNonQuery()

        Catch ex As Exception

        Finally


        End Try
    End Sub

    Private Sub Viewsqltable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Viewsqltable.Click
        Form2.Visible = True

    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        SaveCit_Student()


    End Sub

    Private Sub BindingNavigatorAddNewItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub BindingNavigator1_RefreshItems(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub FillByToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)


    End Sub
End Class


i posted it all what i had so you can see what i am aiming for.

also heres a picture to give you more detail.
also in form 2 what it has is just a datagrid showing the sqltable which is empty.
i just need to do a program that will let the user type the things in the textbox and submit it to the sql table and at the same time it will let the user know its there. i have learned alot because i never explored this part of VB.net including any sort of sql.
Was This Post Helpful? 0
  • +
  • -

#4 nazfera2  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 16-September 10

Re: Need help with a button to save this code.

Posted 24 September 2010 - 07:37 PM

Posted Image
Was This Post Helpful? 0
  • +
  • -

#5 nazfera2  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 16-September 10

Re: Need help with a button to save this code.

Posted 27 September 2010 - 06:22 AM

im clueless on VB and im learning as i go.
i need help with this button, im a college student im in my last year of college and im doing the internship now.

what i need is to make a program connecting the textbox to the sql table.
i already made the connection but im stuck with the button.
what code could i use to let the user save the textbox ?????

Private Sub SaveCit_Student()
        Dim sqlconnect As New SqlClient.SqlConnection
        Try
            ' Assign Connection String
            sqlconnect.ConnectionString = ("Data Source=localhost\SQLEXPRESS;Initial Catalog=Estudiantes;Integrated Security=True")

            ' Open the connection
            sqlconnect.Open()
            ' Build the SQL command and assign it to the 
            ' Command Text property of the Command object
            Dim i As Integer = 0
            Dim sql As String = ""

            sql = "INSERT INTO Cit_Student ("
            sql &= ", Nombre"
            sql &= ", Telefono"
            sql &= ", Email"
            sql &= ", Sexo"
            sql &= ", Direccion"
            sql &= ", Edad"
            sql &= ", Fecha del curso"
            sql &= ", Nombre de escuela) VALUES ('"
            sql &= ", '" & Nombrebox.Text & "'"
            sql &= ", '" & telefonoMaskedTextBox.Mask & "'"
            sql &= ", '" & EmailTextBox.Text & "'"
            sql &= ", '" & GenderMaskedTextBox.Mask & "'"
            sql &= ", '" & DireccionTextBox.Text & "'"
            sql &= ", '" & EdadMaskedTextBox.Mask & "'"
            sql &= ", '" & Fecha_del_cursoMaskedTextBox.Mask & "'"
            sql &= ", '" & Nombre_de_escuelaTextBox.Text & "')"

            Debug.Print(sql)
            Dim sqlcon As New SqlClient.SqlCommand(sql, sqlconnect)

            ' Execute the Command object as a non-query,
            ' meaning that no returned values are expected. 
            sqlcon.ExecuteNonQuery()

        Catch e1 As Exception

        Finally
            ' Always close a Connection when you are done. 
            sqlconnect.Close()
        End Try
    End Sub



or the same thing with this code.

Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.VisualBasic


Public Class Form1

    Dim Datadapt As SqlDataAdapter
    Dim Dset As DataSet
    Dim Drow As DataRow
    Dim sqlconnect As SqlClient.SqlConnection

    Dim Sql As String

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub
    Private Sub Savecit_Student()
        Try

            sqlconnect.ConnectionString = ("Data Source=localhost\SQLEXPRESS;Initial Catalog=Estudiantes;Integrated Security=True")

            sqlconnect.Open()


            Sql = "INSERT INTO Cit_Student (Nombre, Edad,Sexo,Telefono,Email,Direccion,Fecha del curso,Nombre de escuela,) VALUES (@Nombre,@Edad,@Sexo,@Telefono,@Email,@Direccion,@Fecha del curso,@Nombre de escuela)"
            Dim sqlcon As New SqlClient.SqlCommand(Sql, sqlconnect)
            sqlcon.Parameters.AddWithValue("@Nombre", NombreBox.Text)
            sqlcon.Parameters.AddWithValue("@Edad", EdadMTBox.Mask)
            sqlcon.Parameters.AddWithValue("@Sexo", GenderMTBox.Mask)
            sqlcon.Parameters.AddWithValue("@Telefono", TelefonoMTBox.Mask)
            sqlcon.Parameters.AddWithValue("@Email", EMAILTextBox.Text)
            sqlcon.Parameters.AddWithValue("@Direccion", DireccionTextBox.Text)
            sqlcon.Parameters.AddWithValue("@Fecha del curso", FechadelcursoMTBox.Mask)
            sqlcon.Parameters.AddWithValue("@Nombre de escuela", NombredeescuelaTextBox.Text)

            sqlcon.ExecuteNonQuery()

        Catch ex As Exception

        Finally


        End Try
    End Sub

    Private Sub Viewsqltable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Viewsqltable.Click
        Form2.Visible = True

    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        SaveCit_Student()


    End Sub

    Private Sub BindingNavigatorAddNewItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub BindingNavigator1_Refre****ems(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub FillByToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)


    End Sub
End Class

Posted Image

Form 2 shows the data grid of the sql table but i see nothin when i use the submit botton. so there must be something wrong.

im really stressed out please someone keep contact with me i asked professors they told me its been ages sence i teached VB. i asked students and they told me they dont got time so i really need help from at least someone ! >_<

MOD EDIT: Changed title from "need help with this code". We KNOW you need help, it's WHY you posted. Please use descriptive titles in the future.

This post has been edited by JackOfAllTrades: 27 September 2010 - 07:26 AM

Was This Post Helpful? 0
  • +
  • -

#6 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9487
  • View blog
  • Posts: 35,771
  • Joined: 12-June 08

Re: Need help with a button to save this code.

Posted 27 September 2010 - 07:22 AM

Determine how you want the user to interact with the app to save. If you have a button called "save" then inside that event call your method for saving. If you want to have the save occur when you double click the text box then use that event.

Additionally I am not sure why you opened a new topic when you had this one:

http://www.dreaminco...1&#entry1123531
Was This Post Helpful? 0
  • +
  • -

#7 motcom  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 290
  • View blog
  • Posts: 1,371
  • Joined: 16-June 08

Re: Need help with a button to save this code.

Posted 27 September 2010 - 07:27 AM

Well the second part of your code is a better option as it uses parameters and is not so prone agains sql intection.

You can use the same code and change the "INSERT" statement to an "UPDATE" statement.

The way to do it is to (i hope you have a Primary Key in your table) update the record where the ID (Primary key) is the same as your record has (same principle applies to "DELETE" statements

so basically
Sql = "UPDATE Cit_Student SET Nombre, Edad = @Edad ,Sexo = @Sexo,Telefono = @Telefono, Email = @Email,Direccion = @Direccion,[Fecha del curso] = @FechaDelCurso, [Nombre de escuela] = @NombreDeEscuela  WHERE Numbre = @Nombre"



Bearing in Mind that Numbre is your primary key....

also rather than using a parameter like "@Fecha del curso", name it "@FechaDelCurso", leave the spaces. For your table you use the [] brackets if there are spaces in between check the sql statement I wrote...
Was This Post Helpful? 0
  • +
  • -

#8 macosxnerd101  Icon User is online

  • Self-Trained Economist
  • member icon




Reputation: 10767
  • View blog
  • Posts: 40,094
  • Joined: 27-December 08

Re: Need help with a button to save this code.

Posted 27 September 2010 - 09:13 AM

Duplicate topics merged. Please avoid duplicate posting.
Was This Post Helpful? 0
  • +
  • -

#9 nazfera2  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 16-September 10

Re: Need help with a button to save this code.

Posted 27 September 2010 - 02:11 PM

View Postmotcom, on 27 September 2010 - 06:27 AM, said:

Well the second part of your code is a better option as it uses parameters and is not so prone agains sql intection.

You can use the same code and change the "INSERT" statement to an "UPDATE" statement.

The way to do it is to (i hope you have a Primary Key in your table) update the record where the ID (Primary key) is the same as your record has (same principle applies to "DELETE" statements

so basically
Sql = "UPDATE Cit_Student SET Nombre, Edad = @Edad ,Sexo = @Sexo,Telefono = @Telefono, Email = @Email,Direccion = @Direccion,[Fecha del curso] = @FechaDelCurso, [Nombre de escuela] = @NombreDeEscuela  WHERE Numbre = @Nombre"



Bearing in Mind that Numbre is your primary key....

also rather than using a parameter like "@Fecha del curso", name it "@FechaDelCurso", leave the spaces. For your table you use the [] brackets if there are spaces in between check the sql statement I wrote...

alright i did the update instead of insert.... and it dosent work, im guessing theres something wrong in the connection how can i test the connection ??

this is my entire code if im going with parameters... what could i be doing wrong ???

 Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Try

            sqlconnect.ConnectionString = ("Data Source=Osiris_Virus\SQLEXPRESS;Initial Catalog=Estudiantes;Integrated Security=True")

            sqlconnect.Open()


            Sql = "UPDATE INTO Cit_Student (Nombre, Edad,Sexo,Telefono,Email,Direccion,Fecha del curso,Nombre de escuela,) VALUES (@Nombre,@Edad,@Sexo,@Telefono,@Email,@Direccion,@Fecha del curso,@Nombre de escuela)"
            Dim sqlcon As New SqlClient.SqlCommand(Sql, sqlconnect)
            sqlcon.Parameters.AddWithValue("@Nombre", NombreBox.Text)
            sqlcon.Parameters.AddWithValue("@Edad", EdadMTBox.Mask)
            sqlcon.Parameters.AddWithValue("@Sexo", GenderMTBox.Mask)
            sqlcon.Parameters.AddWithValue("@Telefono", TelefonoMTBox.Mask)
            sqlcon.Parameters.AddWithValue("@Email", EMAILTextBox.Text)
            sqlcon.Parameters.AddWithValue("@Direccion", DireccionTextBox.Text)
            sqlcon.Parameters.AddWithValue("@Fecha del curso", FechadelcursoMTBox.Mask)
            sqlcon.Parameters.AddWithValue("@Nombre de escuela", NombredeescuelaTextBox.Text)

            sqlcon.ExecuteNonQuery()

        Catch ex As Exception

        Finally


        End Try

        End sub


This post has been edited by nazfera2: 27 September 2010 - 02:13 PM

Was This Post Helpful? 0
  • +
  • -

#10 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1606
  • View blog
  • Posts: 5,163
  • Joined: 25-September 09

Re: Need help with a button to save this code.

Posted 27 September 2010 - 02:37 PM

put MessageBox.Show(ex.Message) in your catch block of the try catch. You might be burying an error.

Also, I don't really think the Parameters can contain spaces as SQL would think they were separate commands/fields etc...

Edit:
Also does your project have the sqldatabase in the solution explorer? If so, you may need to select it in the solution explorer and check the properties to see if the "Copy to Output source" is set to Always Copy. This should be set to Copy if Newer.

This post has been edited by CharlieMay: 27 September 2010 - 02:40 PM

Was This Post Helpful? 0
  • +
  • -

#11 nazfera2  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 16-September 10

Re: Need help with a button to save this code.

Posted 27 September 2010 - 08:13 PM

very well i changed th sqldataset to copy always


also made small changes to the code.

Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.VisualBasic


Public Class Form1

    Dim Datadapt As SqlDataAdapter
    Dim Dset As DataSet
    Dim Drow As DataRow
    Dim sqlconnect As SqlClient.SqlConnection

    Dim Sql As String

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub
    Private Sub Savecit_Student()
        Try

            sqlconnect.ConnectionString = ("Data Source=Osiris_Virus\SQLEXPRESS;Initial Catalog=Estudiantes;Integrated Security=True")

            sqlconnect.Open()


            Sql = "INSERT INTO Cit_Student (Nombre, Edad,Sexo,Telefono,Email,Direccion,[Fecha del curso],[Nombre de escuela]) VALUES (@Nombre,@Edad,@Sexo,@Telefono,@Email,@Direccion,@Fecha del curso,@Nombre de escuela)"
            Dim sqlcon As New SqlClient.SqlCommand(Sql, sqlconnect)
            sqlcon.Parameters.AddWithValue("@Nombre", NombreBox.Text)
            sqlcon.Parameters.AddWithValue("@Edad", EdadMTBox.Mask)
            sqlcon.Parameters.AddWithValue("@Sexo", GenderMTBox.Mask)
            sqlcon.Parameters.AddWithValue("@Telefono", TelefonoMTBox.Mask)
            sqlcon.Parameters.AddWithValue("@Email", EMAILTextBox.Text)
            sqlcon.Parameters.AddWithValue("@Direccion", DireccionTextBox.Text)
            sqlcon.Parameters.AddWithValue("@Fecha del curso", FechadelcursoMTBox.Mask)
            sqlcon.Parameters.AddWithValue("@Nombre de escuela", NombredeescuelaTextBox.Text)

            sqlcon.ExecuteNonQuery()

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally


        End Try
    End Sub

    Private Sub Viewsqltable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Viewsqltable.Click
        Form2.Visible = True

    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Try

            sqlconnect.ConnectionString = ("Data Source=Osiris_Virus\SQLEXPRESS;Initial Catalog=Estudiantes;Integrated Security=True")

            sqlconnect.Open()


            Sql = "UPDATE INTO Cit_Student (Nombre, Edad,Sexo,Telefono,Email,Direccion,[Fecha del curso],[Nombre de escuela]) VALUES (@Nombre,@Edad,@Sexo,@Telefono,@Email,@Direccion,@Fecha del curso,@Nombre de escuela)"
            Dim sqlcon As New SqlClient.SqlCommand(Sql, sqlconnect)
            sqlcon.Parameters.AddWithValue("@Nombre", NombreBox.Text)
            sqlcon.Parameters.AddWithValue("@Edad", EdadMTBox.Mask)
            sqlcon.Parameters.AddWithValue("@Sexo", GenderMTBox.Mask)
            sqlcon.Parameters.AddWithValue("@Telefono", TelefonoMTBox.Mask)
            sqlcon.Parameters.AddWithValue("@Email", EMAILTextBox.Text)
            sqlcon.Parameters.AddWithValue("@Direccion", DireccionTextBox.Text)
            sqlcon.Parameters.AddWithValue("@Fecha del curso", FechadelcursoMTBox.Mask)
            sqlcon.Parameters.AddWithValue("@Nombre de escuela", NombredeescuelaTextBox.Text)

            sqlcon.ExecuteNonQuery()



        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally


        End Try

    End Sub

    Private Sub BindingNavigatorAddNewItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub BindingNavigator1_RefreshItems(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub FillByToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)


    End Sub
End Class



i put some "[ ]" on the parameters please tell me if i put them in the right place >_<

sence the code motcom displayed is a little different.

This post has been edited by nazfera2: 27 September 2010 - 08:16 PM

Was This Post Helpful? 0
  • +
  • -

#12 motcom  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 290
  • View blog
  • Posts: 1,371
  • Joined: 16-June 08

Re: Need help with a button to save this code.

Posted 28 September 2010 - 01:28 AM

Hi again,

Maybe you should check up on your SQL syntax.... Update statements do not work like this

UPDATE INTO .....



this is a select statement (my table is called TableA and i have Columns Named ColumnA and ColumnB)

SELECT ColumnA, ColumnB FROM TableA ORDER BY ColumnA



The order by is to add sorting

Update Statement for Above Table

UPDATE TableA SET ColumnB = 'SOME VALUE' WHERE ColumnA = 'MyPrimaryKeyValue'



so in this update statement I update an existing record, the ColumnB to 'SOME VALUE' where ColumnA = 'MyPrimaryKeyValue', there where statement is needed to identify wich row needs updating...If i was to only write this

UPDATE TableA SET ColumnB = 'SOME VALUE'



all rows will have their ColumnB set to 'SOME VALUE' wich to most of the time you do not want.

Insert Statement for the above Table

INSERT INTO TableA (ColumnA, ColumnB) VALUES ('NEW VALUE FOR COLUMNA', 'NEW VALUE FOR COLUMNB')



quite self explaining.

Delete Statement

DELETE FROM TableA WHERE ColumnA = 'SOME VALUE'



this will delte all records where ColumnA = 'SOME VALUE'... THUS ColumnA should be a Primary Key, making it unique. (Same applies for Updates)

NOW if i was to name Columnb to Column B (notice the space) I would have to use square brakets []
so the Update statement for example would look like this

UPDATE TableA SET [Column B] = 'SOME VALUE'



do you understand now what is happening?

EDIT:
Preferably do not use spaces for Table Names, Column Names AND Definitely not for Parameter Names as charlie may said.

this is not the way to do it. Read my posts...

sqlcon.Parameters.AddWithValue("@Fecha del curso", FechadelcursoMTBox.Mask)
sqlcon.Parameters.AddWithValue("@Nombre de escuela", NombredeescuelaTextBox.Text)



change the code to this

sqlcon.Parameters.AddWithValue("@FechaDelCurso", FechadelcursoMTBox.Mask)
sqlcon.Parameters.AddWithValue("@NombreDeEscuela", NombredeescuelaTextBox.Text)



so your update statement will look like this at the end

Sql = "UPDATE Cit_Student SET Edad = @Edad,Sexo = @Sexo,Telefono = @Telefono,Email = @Email,Direccion, = @Direccion,[Fecha del curso] = @FechaDelCurso,[Nombre de escuela] = @NombreDeEscuela WHERE Nombre = @Nombre"
Dim sqlcon As New SqlClient.SqlCommand(Sql, sqlconnect)
sqlcon.Parameters.AddWithValue("@Nombre", NombreBox.Text)
sqlcon.Parameters.AddWithValue("@Edad", EdadMTBox.Mask)
sqlcon.Parameters.AddWithValue("@Sexo", GenderMTBox.Mask)
sqlcon.Parameters.AddWithValue("@Telefono", TelefonoMTBox.Mask)
sqlcon.Parameters.AddWithValue("@Email", EMAILTextBox.Text)
sqlcon.Parameters.AddWithValue("@Direccion", DireccionTextBox.Text)
sqlcon.Parameters.AddWithValue("@FechaDelCurso", FechadelcursoMTBox.Mask)
sqlcon.Parameters.AddWithValue("@NombreDeEscuela", NombredeescuelaTextBox.Text)



NOTICE THIS:
[Nombre de escuela] = @NombreDeEscuela WHERE Nombre = @Nombre"



the part that is on square brackets [] is your column name in the database
the @NumbreDeEscuela is the parameter name and does not need the [] AND NO SPACES
the WHERE Nombre = @Nombre will make sure you update a record where the Nombre is equal to some value you have (Provided that Numbre is the record number in your Table)

This post has been edited by motcom: 28 September 2010 - 01:40 AM

Was This Post Helpful? 1
  • +
  • -

Page 1 of 1