Insert into sql

[andybonse2012]

Hey guys,

Please can you tell me what

    Dim con As New SqlConnection
        Dim cmd As New SqlCommand

        Try
            con.ConnectionString = ("Data Source=ANDY-PC\LETTINGS;Initial Catalog=easylet_database;User Id=sa;Password=g3m1n1;")
            con.Open()

            cmd.Connection = con

            cmd.CommandText = "INSERT INTO Landlord (Title,FirstName,LastName,Email,Telephone,Telephone2,Fax,Source,Address1,Address2,Address3,Address4,PostCode,Occupation,Bank_Number,Bank_Sortcode,Bank_AccountName,Bank_Company,Notes,Inactive,Date) VALUES (" & Title.Text & "," & FirstName.Text & "," & Surname.Text & "," & Email.Text & "," & Phone1.Text & "," & Phone2.Text & "," & Fax.Text & "," & Source.Text & "," & Ad1.Text & "," & Ad2.Text & "," & Ad3.Text & "," & Ad4.Text & "," & Postcode.Text & "," & Occupation.Text & "," & AccNo.Text & "," & SortCode.Text & "," & AccName.Text & "," & AccCompany.Text & "," & Notes.Text & ")"
            cmd.ExecuteNonQuery()

        Catch ex As Exception
            MessageBox.Show("Error creating Landlord - Please contact Support")
        Finally
            con.Close()
        End Try

The error pops up: MessageBox.Show("Error creating Landlord - Please contact Support")

I think I have done the sql command syntax wrong.

Cheers.

[DataMiser]

Yes you must use 's around text fields

('" & Title.Text & "',"

[andybonse2012]

I seriously cant get this to work, please can you show me using my code on a few of them how its meant to be laid out.

[andybonse2012]

Based on what you said,

    Private Sub SaveButton_Click(sender As System.Object, e As System.EventArgs) Handles SaveButton.Click
        Dim con As New SqlConnection
        Dim cmd As New SqlCommand

        Try
            con.ConnectionString = ("Data Source=ANDY-PC\LETTINGS;Initial Catalog=easylet_database;User Id=sa;Password=g3m1n1;")
            con.Open()

            cmd.Connection = con

            cmd.CommandText = "INSERT INTO Landlord (Title,FirstName,LastName,Email,Telephone,Telephone2,Fax,Source,Address1,Address2,Address3,Address4,PostCode,Occupation,Bank_Number,Bank_Sortcode,Bank_AccountName,Bank_Company,Notes,Inactive,Date) VALUES ('" & Title.Text & "','" & FirstName.Text & "','" & Surname.Text & "','" & Email.Text & "','" & Phone1.Text & "','" & Phone2.Text & "','" & Fax.Text & "','" & Source.Text & "','" & Ad1.Text & "','" & Ad2.Text & "','" & Ad3.Text & "','" & Ad4.Text & "','" & Postcode.Text & "','" & Occupation.Text & "','" & AccNo.Text & "','" & SortCode.Text & "','" & AccName.Text & "','" & AccCompany.Text & "','" & Notes.Text & "')"
            cmd.ExecuteNonQuery()

        Catch ex As Exception
            MessageBox.Show("Error creating Landlord - Please contact Support")
        Finally
            con.Close()
        End Try

    End Sub

[DataMiser]

And what was the result?

You should add a couple of message boxes if you are still having a problem 1 that will show the content of commandText before you try to execute and another in your catch routine that shows what the actual error is

[andybonse2012]

Right,

heres my code, please save me before I destroy my life lol!!! Its driving me crazy,

            cmd.CommandText =  "INSERT into Landlord (Title,FirstName,LastName,Email,Telephone,Telephone2,Fax,Source,Address1,Address2,Address3,Address4,PostCode,Occupation,Bank_Number,Bank_SortCode,Bank_Accountname,Bank_Company,Notes,Inactive,Notes,AgentID,Date) VALUES ('" & Title.text &"','"& FirstName.text &"', '"& LastName.text"', '" & Email.text & "','" & Telephone & "','" & Telephone2 & "' , '" & Fax & "' , '" & Source & "' , '" & Address1 & "', '" & Address2 & "', '"  & Address3 & "' , '" & Address4 & "', '" & PostCode & "' , '" & Occupation & "', '" & Bank_Number & "', '" & Bank_SortCode & "', '" & Bank_Accountname & "' , '" & Bank_Company & "', '" & Notes & "' ,0,AgentIDNumber,GetDate())"

[DataMiser]

I can't tell much from your code
You did not say what it is doing
Did you add the message boxes to see what is actually happening and what the error is?

You really should be using parameterized queries rather than building the string that way.

Also note that the 's should only be used around Text and Date fields, never on numeric fields and they do not apply to dates with Access which uses # instead of ' for a date delimiter.

[andybonse2012]

I know,

I dont know how to create a SQL stored procedure tho.

MANNNNN lol.

[DataMiser]

Example:

Dim SQLString As String = "INSERT INTO Landlord "

        SQLString += "(Title,FirstName,LastName,Email,Telephone,Telephone2,Fax,Source,Address1,Address2,Address3,Address4,PostCode,Occupation,Bank_Number,Bank_Sortcode,Bank_AccountName,Bank_Company,Notes,Inactive,Date)"
        SQLString += " VALUES (@Title,@FirstName,@LastName,@Email,@Telephone,@Telephone2,@Fax,@Source,@Address1,@Address2,@Address3,@Address4,@PostCode,@Occupation,@Bank_Number,@Bank_Sortcode,@Bank_AccountName,@Bank_Company,@Notes,@Inactive,@Date"

        cmd.CommandText = SQLString
        CMD.Parameters.AddWithValue("@Title", Title.Text)
        CMD.Parameters.AddWithValue("@FirstName", FirstName.Text)
        CMD.Parameters.AddWithValue("@LastName", Surname.Text)
        ' continue for the rest of your enteries

        

        cmd.ExecuteNonQuery()

I notice that one of your field names is Date which is a reserved word and could lead to issues. You should change that if possible and if not then you should bracket it with []

[andybonse2012]

thanks, whats the advantage of using parameters?

[andybonse2012]

Also,

I tried like you said using paremeters and still the same error;

Private Sub SaveButton_Click(sender As System.Object, e As System.EventArgs) Handles SaveButton.Click
        Dim con As New SqlConnection
        Dim cmd As New SqlCommand
        Dim SQLString As String = "INSERT INTO Landlord "
        Dim AID As String = AgentIDNum.AgentIDNumber
        Dim GDate As String = GetDateModule.GetDate

        Try
            con.ConnectionString = ("Data Source=ANDY-PC\LETTINGS;Initial Catalog=easylet_database;User Id=sa;Password=g3m1n1;")
            con.Open()

            SQLString += "(Title,FirstName,LastName,Email,Telephone,Telephone2,Fax,Source,Address1,Address2,Address3,Address4,PostCode,Occupation,Bank_Number,Bank_Sortcode,Bank_AccountName,Bank_Company,Notes,Inactive,Date)"
            SQLString += " VALUES (@Title,@FirstName,@LastName,@Email,@Telephone,@Telephone2,@Fax,@Source,@Address1,@Address2,@Address3,@Address4,@PostCode,@Occupation,@Bank_Number,@Bank_Sortcode,@Bank_AccountName,@Bank_Company,@Notes,@Inactive,@Date"

            cmd.CommandText = SQLString
            cmd.Parameters.AddWithValue("@Title", Title.Text)
            cmd.Parameters.AddWithValue("@FirstName", FirstName.Text)
            cmd.Parameters.AddWithValue("@LastName", LastName.Text)
            cmd.Parameters.AddWithValue("@Email", Email.Text)
            cmd.Parameters.AddWithValue("@Telephone", Telephone.Text)
            cmd.Parameters.AddWithValue("@Telephone2", Telephone2.Text)
            cmd.Parameters.AddWithValue("@Fax", Fax.Text)
            cmd.Parameters.AddWithValue("@Source", Source.Text)
            cmd.Parameters.AddWithValue("@Address1", Address1.Text)
            cmd.Parameters.AddWithValue("@Address2", Address2.Text)
            cmd.Parameters.AddWithValue("@Address3", Address3.Text)
            cmd.Parameters.AddWithValue("@Address4", Address4.Text)
            cmd.Parameters.AddWithValue("@Postcode", Postcode.Text)
            cmd.Parameters.AddWithValue("@Occupation", Occupation.Text)
            cmd.Parameters.AddWithValue("@Bank_Number", Bank_Number.Text)
            cmd.Parameters.AddWithValue("@Bank_Sortcode", Bank_Sortcode.Text)
            cmd.Parameters.AddWithValue("@Bank_Accountname", Bank_Accountname.Text)
            cmd.Parameters.AddWithValue("@Bank_Company", Bank_Company.Text)
            cmd.Parameters.AddWithValue("@Notes", Notes.Text)
            cmd.Parameters.AddWithValue("@Inactive", 0)
            cmd.Parameters.AddWithValue("@AgentID", AID)
            cmd.Parameters.AddWithValue("@Date", GDate)

            cmd.ExecuteNonQuery()

        Catch ex As Exception
            MessageBox.Show("Error creating Landlord - Please contact Support")
        Finally
            con.Close()
            Me.Close()
        End Try

    End Sub

[andybonse2012]

Anyone?

[Españolita]

Include ex.Message in MessageBox.Show to tell you what exactly the error is.

[andybonse2012]

connection property not instalized?

[marniel647]

you need to initialized the connection in your sql command.

SQLString += "(Title,FirstName,LastName,Email,Telephone,Telephone2,Fax,Source,Address1,Address2,Address3,Address4,PostCode,Occupation,Bank_Number,Bank_Sortcode,Bank_AccountName,Bank_Company,Notes,Inactive,Date)"
            SQLString += " VALUES (@Title,@FirstName,@LastName,@Email,@Telephone,@Telephone2,@Fax,@Source,@Address1,@Address2,@Address3,@Address4,@PostCode,@Occupation,@Bank_Number,@Bank_Sortcode,@Bank_AccountName,@Bank_Company,@Notes,@Inactive,@Date"

you must put " con " after the end of the statement like this ("insert into ( table1) values (@table1)" ,con)

also can you just put your SQL string in one just put _ if you want to extend the sentence into nextline

[andybonse2012]

So where do I put the con in my statement, sorry I just had a go but couldnt get it in the right place :/

[Españolita]

I thought the _ was no longer necessary under .Net?

[marniel647]

you must put con at the end of the sql string

[andybonse2012]

thanks for your reply.

It is saying the syntax is wrong. End of statement required.

SQLString += " VALUES (@Title,@FirstName,@LastName,@Email,@Telephone,@Telephone2,@Username,@Password,@Commission,@Notes,@Date",con)

[marniel647]

thanks for your reply.

It is saying the syntax is wrong. End of statement required.

SQLString += " VALUES (@Title,@FirstName,@LastName,@Email,@Telephone,@Telephone2,@Username,@Password,@Commission,@Notes,@Date",con)

that's why i want you to combine the 2 Sqlstring just make it one SQLstring statement.

By the way i rewrite your code try this.

Private Sub SaveButton_Click(sender As System.Object, e As System.EventArgs) Handles SaveButton.Click
        Dim con As New SqlConnection				
        Dim AID As String = AgentIDNum.AgentIDNumber
        Dim GDate As String = GetDateModule.GetDate

        Try
            con.ConnectionString = ("Data Source=ANDY-PC\LETTINGS;Initial Catalog=easylet_database;User Id=sa;Password=g3m1n1;")
            con.Open()
	    Dim cmd As New SqlCommand("INSERT INTO Landlord(Title,FirstName,LastName,Email,Telephone,Telephone2,Fax,Source,Address1,Address2,Address3,Address4,PostCode,Occupation,Bank_Number,Bank_Sortcode,Bank_AccountName,Bank_Company,Notes,Inactive,Date) VALUES (@Title,@FirstName,@LastName,@Email,@Telephone,@Telephone2,@Fax,@Source,@Address1,@Address2,@Address3,@Address4,@PostCode,@Occupation,@Bank_Number,@Bank_Sortcode,@Bank_AccountName,@Bank_Company,@Notes,@Inactive,@Date)",con)
            
            cmd.Parameters.AddWithValue("@Title", Title.Text)
            cmd.Parameters.AddWithValue("@FirstName", FirstName.Text)
            cmd.Parameters.AddWithValue("@LastName", LastName.Text)
            cmd.Parameters.AddWithValue("@Email", Email.Text)
            cmd.Parameters.AddWithValue("@Telephone", Telephone.Text)
            cmd.Parameters.AddWithValue("@Telephone2", Telephone2.Text)
            cmd.Parameters.AddWithValue("@Fax", Fax.Text)
            cmd.Parameters.AddWithValue("@Source", Source.Text)
            cmd.Parameters.AddWithValue("@Address1", Address1.Text)
            cmd.Parameters.AddWithValue("@Address2", Address2.Text)
            cmd.Parameters.AddWithValue("@Address3", Address3.Text)
            cmd.Parameters.AddWithValue("@Address4", Address4.Text)
            cmd.Parameters.AddWithValue("@Postcode", Postcode.Text)
            cmd.Parameters.AddWithValue("@Occupation", Occupation.Text)
            cmd.Parameters.AddWithValue("@Bank_Number", Bank_Number.Text)
            cmd.Parameters.AddWithValue("@Bank_Sortcode", Bank_Sortcode.Text)
            cmd.Parameters.AddWithValue("@Bank_Accountname", Bank_Accountname.Text)
            cmd.Parameters.AddWithValue("@Bank_Company", Bank_Company.Text)
            cmd.Parameters.AddWithValue("@Notes", Notes.Text)
            cmd.Parameters.AddWithValue("@Inactive", 0)
            cmd.Parameters.AddWithValue("@AgentID", AID)
            cmd.Parameters.AddWithValue("@Date", GDate)

            cmd.ExecuteNonQuery()

        Catch ex As Exception
            MessageBox.Show("Error creating Landlord - Please contact Support")
        Finally
            con.Close()
            Me.Close()
        End Try

    End Sub

[DataMiser]

You don't need to make the SQL string one long string like that, just makes it hard to read.

You can easily set a Variable using multiple lines to concantonate the values as was done above then when setting up the command use

Dim cmd As New SqlCommand (SqlString,Con)

Makes for much more readable code