System.Data.SqlClient.SqlException . cant insert

MSK Afridi

Hello there,

I am a beginner to C# and trying to insert signup data in MSSQL. but getting System.Data.SqlClient.SqlException .in System.Data.dll. I have successfully inserted data in other pages but here I am facing this issue. code is as follows

void SignUpNewMember()
{
try
{
SqlConnection con = new SqlConnection(strcon);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand cmd = new SqlCommand("INSERT INTO member_master_tbl (full_name,dob,contact_no,email,state,city,pincode,full_address,member_id,password,account_status) values (@full_name,@dob,@contact_no,@email,@state,@city,@pincode,@full_address,@member_id,@password,@account_status)", con);
cmd.Parameters.AddWithValue("@full_name", TextBox1.Text.Trim());
cmd.Parameters.AddWithValue("@dob", TextBox2.Text.Trim());
cmd.Parameters.AddWithValue("@contact_no", TextBox3.Text.Trim());
cmd.Parameters.AddWithValue("@email", TextBox4.Text.Trim());
cmd.Parameters.AddWithValue("@state", DropDownList1.SelectedItem.Value);
cmd.Parameters.AddWithValue("@city", TextBox6.Text.Trim());
cmd.Parameters.AddWithValue("@pincode", TextBox7.Text.Trim());
cmd.Parameters.AddWithValue("@full_address", TextBox5.Text.Trim());
cmd.Parameters.AddWithValue("@member_id", TextBox8.Text.Trim());
cmd.Parameters.AddWithValue("@password", TextBox9.Text.Trim());
cmd.Parameters.AddWithValue("@account_status", "pending");
cmd.ExecuteNonQuery();
con.Close();
Response.Write("");
}
catch (Exception ex)
{
Response.Write("");
}
}

plz guide what is wrong with this code as the same code is working on other pages

Regards

14 Replies

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

MSK Afridi6y ago
Issue resolved. as I had asked for how to print the detailed exception and then I find this In the IDE and when i saw the detailed exception as it breaks where is the issue so it says that you are storing more char then allowed in the address filed so I took seconds to resolve it.

Thanks to all @Jignesh,Amit, Rajanikant .. see u soon guys on this forum

Regards
Amit Gupta6y ago
Hey MSK

The error you had posted is System.NullReferenceException, make sure the value (or control) passed in the parameter is not null, rest your code is good.

Also try clean and rebuild your application, sometimes this works too
MSK Afridi6y ago
@Jignesh. thank u for the input
I have checked. now all the col in the table are of type varchar(50) and the type of text boxes are text. now the textMode property is a single line, for other text Box it is a date and other email but I think it won't matter as at the end it is of type string/char. I have also used it.ToString() but no progress.

I have changed to IE and it is working there, I don't why yaar some help require I am fed up . the same code working in other pages but not here. on the front end I used BootStrap with CSS and HTML 5.0.

Regards
Vinay Singh6y ago
Try to insert by using SqlDataAdaptor chekc the sample link

http://www.aspdotnet-pools.com/2020/03/registration-form-in-c-with-sql-database.html

OR try by adding these changed in your code

com.Open()
cmd.ExecuteNonQuery();
con.Close();
Jignesh Kumar6y ago
Hi,

Could you please check data type of state Coulmn. If its string then please change your dropdown values as below. One more thing please check all column data type and change according.

Check for drop-down value and Pass as string Convert.ToString( DropDownList1.SelectedItem.Value)
MSK Afridi6y ago
hi there
i run the query with hardcoded values and it worked so this means that there is some issue with the all text boxes and dropdown list something may be related to properties of these text boxes ?

SqlCommand cmd = new SqlCommand("INSERT INTO member_master_tbl (full_name,dob,contact_no,email,state,city,pincode,full_address,member_id,password,account_status) values ('@full_name','@dob','@contact_no','@email','@state','@city','@pincode','@full_address','@member_id','@password','@account_status');", con);

even i tried fol but didnt work
cmd.CommandText = "INSERT INTO member_master_tbl (full_name,dob,contact_no,email,state,city,pincode,full_address,member_id,password) values ('" + TextBox1.Text.Trim() + "','" + TextBox2.Text.Trim() + "','"+TextBox4.Text.Trim()+"','" + TextBox3.Text.Trim() + "','" + DropDownList1.SelectedItem.Value + "','" + TextBox6.Text.Trim() + "','" + TextBox7.Text.Trim() + "','" + TextBox5.Text.Trim() + "','" + TextBox8.Text.Trim() + "','" + TextBox9.Text.Trim() + "');";

so what can be checked next?

Regards
MSK Afridi6y ago
@Jignesh. Thank you for your input but it didn't work

@Amit. well, bro, I don't know how to put the reference in the code u mean breakpoint, pl teach me as I don't know that. I usually get the IIS error of port, saying that the port u using is used by other application but then I I used to change it and then it works but right now no such issue.

secondly why i am not getting this exception in the try-catch block? please tell me how to get it as it might show more details

@Rajanikant. I Did it even with hardcoded values but it is not working. The exception I am getting is the SQL connection issue. I think I am having trouble with the connection issue. I will post the whole code with the code of web.config.

Regards

Complete Page code is as follows please:-
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace libraryinfosystem
{
public partial class usersignup : System.Web.UI.Page
{
string strcon = ConfigurationManager.ConnectionStrings["con"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
if (CheckMemberExists())
{
Response.Write("");
}
else
{
SignUpNewMember();
}
}
// user defined method
bool CheckMemberExists()
{
try
{
SqlConnection con = new SqlConnection(strcon);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
Response.Write("");
SqlCommand cmd = new SqlCommand("SELECT * from member_master_tbl where member_id='" + TextBox8.Text.Trim() + "';", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
if (dt.Rows.Count >= 1)
{
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
Response.Write("");
return false;
}
}
void SignUpNewMember()
{
try
{
SqlConnection con = new SqlConnection(strcon);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand cmd = new SqlCommand("INSERT INTO member_master_tbl (full_name,dob,contact_no,email,state,city,pincode,full_address,member_id,password,account_status)values(@full_name,@dob,@contact_no,@email,@state,@city,@pincode,@full_address,@member_id,@password,@account_status);", con);
cmd.CommandType = CommandType.Text;//addded by Jignesh
cmd.Parameters.AddWithValue("@full_name", TextBox1.Text.Trim());
cmd.Parameters.AddWithValue("@dob", TextBox2.Text.Trim());
cmd.Parameters.AddWithValue("@contact_no", TextBox3.Text.Trim());
cmd.Parameters.AddWithValue("@email", TextBox4.Text.Trim());
cmd.Parameters.AddWithValue("@state", DropDownList1.SelectedItem.Value);
cmd.Parameters.AddWithValue("@city", TextBox6.Text.Trim());
cmd.Parameters.AddWithValue("@pincode", TextBox7.Text.Trim());
cmd.Parameters.AddWithValue("@full_address", TextBox5.Text.Trim());
cmd.Parameters.AddWithValue("@member_id", TextBox8.Text.Trim());
cmd.Parameters.AddWithValue("@password", TextBox9.Text.Trim());
cmd.Parameters.AddWithValue("@account_status","pending");
cmd.ExecuteNonQuery();
con.Close();
Response.Write("");
}
catch (SqlException ex)
{
Response.Write("");
}
}
}
}
The web.config is as follows
"con" connectionString="Data Source=DESKTOP-XXXXXX;Initial Catalog=elibraryDB;Integrated Security=true" />
providerName="System.Data.SqlClient" />
Amit Gupta6y ago
I can't understand the file mentioned in your error message of libraryinfosystem.dll, did you took any reference of it anywhere in your program. I think there is debugging issue in your iis, can you recheck deploying to other browser or ran it directly without hosting it to iis (for checking purpose)
Jignesh Kumar6y ago
Hi,
Please check this code, you are missing, We need to tell this is command type Text not store procedure,

cmd.CommandType = CommandType.Text;
void SignUpNewMember()
{
try
{
SqlConnection con = new SqlConnection(strcon);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand cmd = new SqlCommand("INSERT INTO member_master_tbl (full_name,dob,contact_no,email,state,city,pincode,full_address,member_id,password,account_status) values (@full_name,@dob,@contact_no,@email,@state,@city,@pincode,@full_address,@member_id,@password,@account_status)", con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@full_name", TextBox1.Text.Trim());
cmd.Parameters.AddWithValue("@dob", TextBox2.Text.Trim());
cmd.Parameters.AddWithValue("@contact_no", TextBox3.Text.Trim());
cmd.Parameters.AddWithValue("@email", TextBox4.Text.Trim());
cmd.Parameters.AddWithValue("@state", DropDownList1.SelectedItem.Value);
cmd.Parameters.AddWithValue("@city", TextBox6.Text.Trim());
cmd.Parameters.AddWithValue("@pincode", TextBox7.Text.Trim());
cmd.Parameters.AddWithValue("@full_address", TextBox5.Text.Trim());
cmd.Parameters.AddWithValue("@member_id", TextBox8.Text.Trim());
cmd.Parameters.AddWithValue("@password", TextBox9.Text.Trim());
cmd.Parameters.AddWithValue("@account_status", "pending");
cmd.ExecuteNonQuery();
con.Close();
Response.Write("");
}
catch (Exception ex)
{
Response.Write("");
}
}
MSK Afridi6y ago
@Rajanikant: yeah sorry I didn't get it earlier but in your suggested code where are the values? how to fetch these values? you are just referring the place holder to insert statement as values

@Amit
when I run my code that exception handler does not help me and nothing popup but when I see the output so I see the following. sorry it's messy but please check if you find anything
Thanks

Exception thrown: 'System.NullReferenceException' in libraryinfosystem.dll
'iisexpress.exe' (CLR v4.0.30319: /LM/W3SVC/4/ROOT-1-132305034123901511): Loaded 'C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\System.Drawing\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.Drawing.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'iisexpress.exe' (CLR v4.0.30319: DefaultDomain): Loaded 'C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\System.Drawing\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.Drawing.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'iisexpress.exe' (CLR v4.0.30319: /LM/W3SVC/4/ROOT-1-132305034123901511): Loaded 'C:\Program Files (x86)\Microsoft Visual Studio\2019\Professional\Common7\IDE\Extensions\Microsoft\Web Tools\Page Inspector\Microsoft.WebTools.BrowserLink.HtmlParser.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'iisexpress.exe' (CLR v4.0.30319: /LM/W3SVC/4/ROOT-1-132305034123901511): Loaded 'C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\System.Runtime.Serialization\v4.0_4.0.0.0__b77a5c561934e089\System.Runtime.Serialization.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Exception thrown: 'System.NullReferenceException' in libraryinfosystem.dll
'iisexpress.exe' (CLR v4.0.30319: /LM/W3SVC/4/ROOT-1-132305034123901511): Loaded 'C:\WINDOWS\Microsoft.Net\assembly\GAC_32\System.Data\v4.0_4.0.0.0__b77a5c561934e089\System.Data.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'iisexpress.exe' (CLR v4.0.30319: /LM/W3SVC/4/ROOT-1-132305034123901511): Loaded 'C:\WINDOWS\Microsoft.Net\assembly\GAC_32\System.Transactions\v4.0_4.0.0.0__b77a5c561934e089\System.Transactions.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'iisexpress.exe' (CLR v4.0.30319: /LM/W3SVC/4/ROOT-1-132305034123901511): Loaded 'C:\WINDOWS\Microsoft.Net\assembly\GAC_32\System.EnterpriseServices\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.EnterpriseServices.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'iisexpress.exe' (CLR v4.0.30319: /LM/W3SVC/4/ROOT-1-132305034123901511): Loaded 'C:\WINDOWS\Microsoft.Net\assembly\GAC_32\System.EnterpriseServices\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.EnterpriseServices.Wrapper.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'iisexpress.exe' (CLR v4.0.30319: /LM/W3SVC/4/ROOT-1-132305034123901511): Loaded 'C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\System.Numerics\v4.0_4.0.0.0__b77a5c561934e089\System.Numerics.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Exception thrown: 'System.Data.SqlClient.SqlException' in System.Data.dll
The program '[16052] iisexpress.exe' has exited with code -1 (0xffffffff).
Amit Gupta6y ago
Your code seems good, can you share the full error messages for better understanding
MSK Afridi6y ago
@ Jignesh Kumar.
yes it is primary key but I have not done anything for auto-generation of primary key
@ Rajanikant Hawaldar
Well, Rajanikant you paste the same code? didn't really understand.pl elaborate

Regards
Jignesh Kumar6y ago
Hi,

I think for your table has member_id is primary key. If yes thenit may be auto generated so you no need to pass in query and paramter.
Rajanikant Hawaldar6y ago
Modified in query to reduce code, please proper names to text boxes. Let me check the error you getting void SignUpNewMember() { try { SqlConnection con = new SqlConnection(strcon); if (con.State == ConnectionState.Closed) { con.Open(); } SqlCommand cmd = new SqlCommand("INSERT INTO member_master_tbl (full_name,dob,contact_no,email,state,city,pincode,full_address,password,account_status) values (TextBox1.Text.Trim(),TextBox2.Text.Trim(),TextBox3.Text.Trim(),TextBox4.Text.Trim(),TextBox5.Text.Trim(),TextBox6.Text.Trim(),TextBox7.Text.Trim(),TextBox8.Text.Trim(),TextBox9.Text.Trim(),"pending")", con);cmd.ExecuteNonQuery(); con.Close(); Response.Write(""); } catch (Exception ex) { Response.Write(""); } }