CODEWITHSUNDEEP
asp.netsql-server
ifaminsi
ifaminsi

Reputation: 181

insert a row based on column value in sql

I have three tables. In table one I have id, name, and two columns. In the other table I have id, name, etc.. Similarly the third one. The values of the tables are listed below

enter image description here

In the above image the column nameEEE consist of same values.

Another table has a column like nameECE consist of same values.

My issue is:

I have textbox controls. When I input eee in the textbox the values should be inserted to the respective table where the column consist of eee values. This can be done using if else condition but in the case of 20 tables how to write the query

insert query is something like this:

SqlConnection con = new SqlConnection("Data Source=DESKTOP-CEUQVES;Initial Catalog=register;Integrated Security=True;Pooling=False");
con.Open();

string projid = projectidText.Text;
string videourl = videoText.Text;


string filename = Path.GetFileName(abstractFile.PostedFile.FileName);
abstractFile.SaveAs(Server.MapPath("documents/mini/" + filename));

SqlCommand cmd = new SqlCommand("insert into ece(projectid, video, abstract) values(@id, @video, @abstract)", con);
cmd.Parameters.Add("@id", SqlDbType.NVarChar).Value = projid;
cmd.Parameters.Add("@video", SqlDbType.NVarChar).Value = videourl;
cmd.Parameters.Add("@abstract", SqlDbType.NVarChar).Value = filename;

cmd.ExecuteNonQuery();
con.Close();

By using if else it can be done but the code will be huge.

Upvotes: 0

Views: 388

Answers (2)

Dave
Dave

Reputation: 4412

You can construct your SQL query like this:

string commandText = string.Format("INSERT INTO {0} (projectID{0}, video{0}, abstract{0}, values(@id, @video, @abstract)", UserEnteredTableName);
SqlCommand cmd = new SqlCommand(commandText, con);

But you shouldn't. It leaves you critically open to SQL injection, if a user puts valid SQL into the textbox where they enter the table name.

I've done similar projects where the users create the field names and values, and I've had success mapping it like:

table CustomTables
tableID tableName
------- ---------
1       eee
2       ece

table CustomFields
FieldID tableID rowNumber fieldName fieldValue
------- ------- --------- --------- ----------
1       1       1         projectid 1
2       1       1         name      eee
3       1       1         video     ssssas
3       1       1         abs       xadads
4       1       2         projectid 2
5       1       2         name      eee
6       1       2         video     jsdksj
7       1       2         abs       jdxksk
8       1       3         projectid 4
9       1       3         name      eee
10      1       3         video     rvrge
11      1       3         abs       dtydd
12      2       1         projectid 1
13      2       1         name      ece
14      2       1         video     werwerwerw
15      2       1         abs       etertertf

(You might not need the "name" field, if all it does is store the name of the custom table, because you're storing that name in the CustomTables table)

Upvotes: 1

ErikE
ErikE

Reputation: 50271

I recommend you combine all your tables into one. So instead of this:

table EEE:
projectidEEE nameEEE videoEEE absEEE
------------ ------- -------- ------
 1            eee     blah     gorp

table ECE:
projectidECE nameECE videoECE absECE
------------ ------- -------- ------
 1            ece     blick    blork

You would add a column and combine the tables like so:

table ProjectSomething:
projectidECE SomethingId nameECE videoECE absECE
------------ ----------- ------- -------- ------
 1             1          eee     blah     gorp
 1             2          ece     blick    blork

Where SomethingId 1 is EEE and 2 is ECE, as foreign keys from a table listing each of the 20 types.

I suspect that you could drop the nameECE column if it really has the values EEE and ECE that are the same for every row in each table (it's not clear from your fake data). If the rows are different, then of course the column can stay, or be normalized if there are some duplicates but not all.

Upvotes: 0

Related Questions