I can't find columns of database

2019-09-20 01:11发布

问题:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;

namespace CSharpApp.Classes
{

  public class RegistrationClass
  {
    SqlConnection myConnection = new SqlConnection("Data Source=MOE-   PC\\SQLEXPRESS;   InitialCatalog=db_University;Integrated Security=True;Pooling=False");
    ConnectionClass con = new ConnectionClass();

    String fullName, motherName, gender, placeOfBirth, email, phone, adress,  schoolDegree, languages, highSchool, faculty, major;



    public void setValues (String fullName1,String motherName1,String gender1,String placeOfBirth1,String email1,String phone1,String adress1, String faculty1,String major1,String schoolDegree1,String languages1,String highSchool1)
    {

        fullName = fullName1;
        motherName = motherName1;
        gender = gender1;
        placeOfBirth= placeOfBirth1;
        email =email1;
        phone= phone1;
        adress =adress1;
        faculty =faculty1;
        major =major1;
        schoolDegree =schoolDegree1;
        languages =languages1;
        highSchool = highSchool1;
    }

    public void  InsertStudentInfo()
    {
        String query = "Insert into StudentInfo( fullName, motherName, gender, placeOfBirth, email, phone, adress,schoolDegree, languages, highSchool) values( fullName, motherName, gender, placeOfBirth, email, phone, adress,schoolDegree, languages, highSchool) ";
        SqlCommand myCommand = new SqlCommand(query, myConnection);
        myConnection.Open();
        myCommand.ExecuteNonQuery();
        myConnection.Close();

    }

    public void  InsertUniversityInfo()
    {

        String query = "Insert into universityInfo( faculty,major) Values (faculty, major)";
        SqlCommand myCommand = new SqlCommand(query, myConnection);
        con.openConnection();
        myCommand.ExecuteNonQuery();
        con.closeConnection();
    }
}

I can't find the columns of the database table. I don't know why

Invalid column name 'motherName'.
Invalid column name 'gender'.
Invalid column name 'placeOfBirth'.
.....

回答1:

Your INSERT query seems to be broken in two ways...

First, as the error indicates, those columns apparently don't exist on that table. Check the schema of the StudentInfo table. No matter how sure you are that it's correct, the SQL query engine is usually right about these things.

Second, what are you actually inserting? Look at your query:

Insert into StudentInfo (fullName, motherName, gender, placeOfBirth, email, phone, adress,schoolDegree, languages, highSchool)
                 values (fullName, motherName, gender, placeOfBirth, email, phone, adress,schoolDegree, languages, highSchool)

Are you trying to insert the values that are already in the table? What data are you actually trying to insert? You just reference the same columns in the VALUES clause that you did in the INTO clause. Even if this works, I guarantee it won't do what you expect it to do, whatever that is. You need to provide values to insert into the database. Without values to be inserted, what are you trying to do?

If you're trying to insert the values that are in this object, you need to add those as parameters. First, adjust the query:

Insert into StudentInfo (fullName, motherName, gender, placeOfBirth, email, phone, address, schoolDegree, languages, highSchool)
                 values (@fullName, @motherName, @gender, @placeOfBirth, @email, @phone, @adress, @schoolDegree, @languages, @highSchool)

Note the addition of the @ characters to indicate that the word is a placeholder for a parameter. Then you need to add the parameters to the command:

SqlCommand myCommand = new SqlCommand(query, myConnection);
myCommand.Parameters.AddWithValue("@fullName", fullName);
myCommand.Parameters.AddWithValue("@motherName", motherName);
// and so on for the rest of the parameters...

Finally, as a side note, you shouldn't share connection objects across multiple actions like this. It's inviting resource leaks and other errors. Instead, create (and subsequently destroy) the connection object close to where it's used, keeping it alive for as little time as possible:

using (SqlConnection myConnection = new SqlConnection("Data Source=MOE-   PC\\SQLEXPRESS;   InitialCatalog=db_University;Integrated Security=True;Pooling=False"))
{
    String query = "Insert into StudentInfo (fullName, motherName, gender, placeOfBirth, email, phone, address, schoolDegree, languages, highSchool) values (@fullName, @motherName, @gender, @placeOfBirth, @email, @phone, @adress, @schoolDegree, @languages, @highSchool)";
    SqlCommand myCommand = new SqlCommand(query, myConnection);
    myCommand.Parameters.AddWithValue("@fullName", fullName);
    myCommand.Parameters.AddWithValue("@motherName", motherName);
    // and so on for the rest of the parameters...
    myConnection.Open();
    try
    {
        myCommand.ExecuteNonQuery();
    }
    finally
    {
        myConnection.Close();
    }
}


回答2:

The INSERT command comes in two flavors:

(1) either you have all your values available, as literals or SQL Server variables - in that case, you can use the INSERT .. VALUES() approach:

INSERT INTO dbo.YourTable(Col1, Col2, ...., ColN)
VALUES(Value1, Value2, @Variable3, @Variable4, ...., ValueN)

Note: I would recommend to always explicitly specify the list of column to insert data into - that way, you won't have any nasty surprises if suddenly your table has an extra column, or if your tables has an IDENTITY or computed column. Yes - it's a tiny bit more work - once - but then you have your INSERT statement as solid as it can be and you won't have to constantly fiddle around with it if your table changes.

(2) if you don't have all your values as literals and/or variables, but instead you want to rely on another table, multiple tables, or views, to provide the values, then you can use the INSERT ... SELECT ... approach:

INSERT INTO dbo.YourTable(Col1, Col2, ...., ColN)
   SELECT
       SourceColumn1, SourceColumn2, @Variable3, @Variable4, ...., SourceColumnN
   FROM
       dbo.YourProvidingTableOrView

Here, you must define exactly as many items in the SELECT as your INSERT expects - and those can be columns from the table(s) (or view(s)), or those can be literals or variables. Again: explicitly provide the list of columns to insert into - see above.

You can use one or the other - but you cannot mix the two - you cannot use VALUES(...) and then have a SELECT query in the middle of your list of values - pick one of the two - stick with it.

So either you need to use the VALUES(...) approach and have literals or SQL Server variables (starting with a @) at hand - or you need to use the INSERT ... SELECT ... approach to refer to other columns in your database table(s). Pick one - right now, your INSERT statement is really neither of those two valid options