Insert Query does not Work Properly

Saafia NM

10y1.5kVisual Basic .NET

protected void Button1_Click(object sender, EventArgs e)

{

string collection = "abcdefghijklmnopqrstuvwxyz123456789";

string key1 = "";

Random rnd = new Random();

for (int i = 0; i < 5; i++)

{

int k1 = rnd.Next(collection.Length);

key1 += collection.Substring(k1, 1);

}

string password = key1;

HashAlgorithm hash = new SHA256Managed();

byte[] plainTextBytes = System.Text.Encoding.UTF8.GetBytes(password);

byte[] hashBytes = hash.ComputeHash(plainTextBytes);

string hashValue = Convert.ToBase64String(hashBytes);

Label2.Text = hashValue;

SqlCommand cmd = new SqlCommand("insert into HashKey values('" + Label2.Text + "')", con);

con.Open();

cmd.ExecuteNonQuery();

con.Close();

if (FileUpload1.HasFile)

{

FileUpload1.SaveAs(Request.PhysicalApplicationPath.ToString() + @"\Files\" + FileUpload1.FileName.ToString());

FileInfo fii = new FileInfo(Request.PhysicalApplicationPath.ToString() + @"\Files\" + FileUpload1.FileName);

Label4.Text = fii.Name.ToString();

Label5.Text = fii.Length.ToString() + "Bytes";

string input = Request.PhysicalApplicationPath.ToString() + @"\Files\" + FileUpload1.FileName;

string output=Request.PhysicalApplicationPath.ToString() + @"\Encrypt\" + FileUpload1.FileName;

Encrypt(input,output);

string random = "0123456789";

string key2 = "";

Random ran = new Random();

for (int i = 0; i < 4; i++)

{

int k2 = ran.Next(random.Length);

key2 += random.Substring(k2, 1);

}

SqlCommand cmnd = new SqlCommand("insert into Upload(ID,Filename,Filesize,Keyword,Data) values('"+key2+"','" + Label4.Text + "','" + Label5.Text + "','" + TextBox1.Text + "','"+output+"')", con);

con.Open();

int k=cmnd.ExecuteNonQuery();

if (k > 0)

{

SqlCommand cmnd2 = new SqlCommand("select Keyword from Upload where Keyword=@keyword",con);

cmnd2.Parameters.AddWithValue("@keyword",TextBox1.Text);

using (SqlDataReader dr = cmnd2.ExecuteReader())

{

if (dr == null)

{

SqlCommand cmnd3 = new SqlCommand("insert into Index_list(ID,Keyword) values('"+key2+"','"+TextBox1.Text+"')", con);

cmnd3.ExecuteNonQuery();

}

else

{

SqlConnection connection = new SqlConnection(@"Data Source=gateway-PC;Initial Catalog=Blindstore;Integrated Security=True");

SqlCommand cmnd4 = new SqlCommand("UPDATE t SET ID = STUFF( (SELECT ',' + CAST(ID AS VARCHAR(100)) [text()] FROM Upload f WHERE t.Keyword = f.Keyword FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'), 1, 1, '') FROM Index_list t ", connection);

connection.Open();

cmnd4.ExecuteNonQuery();

}

Response.Write("");

}

Insert query (cmnd3) does not work , please help me!!!

23 Replies

Know the answer? Post it — somebody with the same question will find it here.

Prashant More10y ago
what error are you getting?
Rafnas T P10y ago
if you got the answer then please close this thread
Saafia NM10y ago
SqlCommand cmnd = new SqlCommand("insert into Upload(ID,Filename,Filesize,Keyword,Data) values('" + key2 + "','" + Label4.Text + "','" + Label5.Text + "','" + TextBox1.Text + "','" + output + "')", con);
con.Open();
cmnd.ExecuteNonQuery();
SqlCommand cmnd2 = new SqlCommand("select Keyword from Index_list where Keyword=@keyword", con);
cmnd2.Parameters.AddWithValue("@keyword",TextBox1.Text);
SqlDataReader dt = cmnd2.ExecuteReader();
if (!dt.Read())
{
SqlConnection conn = new SqlConnection("Data Source=gateway-PC;Initial Catalog=Blindstore;Integrated Security=True");
SqlCommand cmnd3 = new SqlCommand("insert into Index_list(ID,Keyword) values('" + key2 + "','" + TextBox1.Text + "')", conn);
conn.Open();
cmnd3.ExecuteNonQuery();
}
else
{
SqlConnection connec = new SqlConnection("Data Source=gateway-PC;Initial Catalog=Blindstore;Integrated Security=True");
SqlCommand cmnd4 = new SqlCommand("UPDATE t SET ID = STUFF( (SELECT ',' + CAST(ID AS VARCHAR(100)) [text()] FROM Upload f WHERE t.Keyword = f.Keyword FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'), 1, 1, '') FROM Index_list t ", connec);
connec.Open();
cmnd4.ExecuteNonQuery();
}
Response.Write("");
con.Close();

Yes I got the output.Thanks to all!!!
theLizard10y ago
Looking at your sql insert statements they, under normal circumstances would work so the question is why are they not working?

The problem might be in your database structure so if you want me to help you, you need to answer these questions.

What are you attempting to do.

What is ID used for in Index_list Table.
Is ID a key field in your database.

Are you using Keyword in Upload table to link with Keyword in Index_list Table.

Why are you generating an ID for Index_list Table.

Why are you generating an ID for Upload table.

Why is ID in Index_list Table an NVARCHAR and ID an INT in Upload Table

From what I can see you have a lot of code for something that could be done using a lot less code.

Sorry, but nothing of what you are doing makes any sense to me.

Explain what the tables are used for then maybe someone can help you achieve that.
Rafnas T P10y ago
try this

if (!dr.Read())

instead of
if (dr == null)
Saafia NM10y ago
yes sir!!!
Shubham Kumar10y ago
you mean your conditional statement is executing but your query for insert is not working properly right?
Saafia NM10y ago
That is after cmnd2 there is a using statement.Inside that only one condition will be execute,either if or else. Is there any pblm in my insert query(cmnd3).That is not working properly.Till now i don't have any idea what is the pblm.I tried a lot.But can't solve.
Shubham Kumar10y ago
Dont understand!! are you try to say, every time only one condition has executing in if else statement, i am right ?
Saafia NM10y ago
First of all thank you to give me a response!!!Sir i tried the code you have given,inside of the using loop(after cmnd2) only one loop will be execute.Is there any pblm to execute two loop inside the using loop???
Shubham Kumar10y ago
try this code ,
If (dr.HasRows==true)
{
connection.Close(); SqlConnection connection = new SqlConnection(@"Data Source=gateway-PC;Initial Catalog=Blindstore;Integrated Security=True");
SqlCommand cmnd4 = new SqlCommand("UPDATE t SET ID = STUFF( (SELECT ',' + CAST(ID AS VARCHAR(100)) [text()] FROM Upload f WHERE t.Keyword = f.Keyword FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'), 1, 1, '') FROM Index_list t ", connection);
connection.Open();
cmnd4.ExecuteNonQuery();
}
else
{
SqlCommand cmnd3 = new SqlCommand("insert into Index_list(ID,Keyword) values('"+key2+"','"+TextBox1.Text+"')", con);
cmnd3.ExecuteNonQuery();
}
replace existing code
Saafia NM10y ago
still it is not working!!! The loop does not entering.If the loop works then the update(cmnd4) will be ok. Why the (if loop) is not working???
ali tuncer10y ago
Does this query return anything? if it doesn't, you won't go to cmnd3 line..
SqlCommand cmnd2 = new SqlCommand("select Keyword from Upload where Keyword=@keyword",con);
cmnd2.Parameters.AddWithValue("@keyword",TextBox1.Text);
Shubham Kumar10y ago
Hi,

try to add
if(dr == null || !dr.HasRows)//on your code { }
Saafia NM10y ago
I am getting the dr value as null,but (if loop) does not work.Instead of sql command i gave a alert box query,it also not working.!!!what to do?
ali tuncer10y ago
If you are not getting any error and nothing is happening, maybe you are not even executing that line..
if (dr == null) // maybe dr is never equal to null
{
SqlCommand cmnd3 = new SqlCommand("insert into Index_list(ID,Keyword) values('"+key2+"','"+TextBox1.Text+"')", con);
cmnd3.ExecuteNonQuery();
}

There could also be a problem with parameters, write a simple sql for that line that you know is working for sure, see if it inserts data(for testing purposes, to find out the cause of the problem)..such as :

SqlCommand cmnd3 = new SqlCommand("insert into Index_list(ID,Keyword) values('343523','testdata')" ,con);
cmnd3.ExecuteNonQuery();
Rafnas T P10y ago
In this having so many insert queries,for you which one is not executing.
Shubham Kumar10y ago
Hi, saafia

here is looking all things are fine try to debug your code and check in cmnd3 what query are being made and copy the query paste on ssms and try to run it, It may help you to find the actuall problem
Saafia NM10y ago
ID nvarchar(MAX)
Keyword varchar(50)
Index_list Table

ID int
FileName varchar(50)
FileSize varchar(MAX)
Keyword varchar(50)
Upload Table
Shubham Kumar10y ago
what is the data type of Id in Index_list table ? provide the table structure
Saafia NM10y ago
i did not get any error,it does not insert any value to the table. If i manually insert any value to the table Index_list the update query is working otherwise both query is not working!!! Simply cmd is working!!!
Shubham Kumar10y ago
what error are you getting?

"Insert query (cmnd3) does not work , please help me!!! "
It's not a proper error.
ali tuncer10y ago
Do you receive any message? What does it say?
You might be inserting same value to ID column in Index_list. ( I assumed ID is column is unique)

ID	int
FileName	varchar(50)
FileSize	varchar(MAX)
Keyword	varchar(50)