Error connecting to DB SQL 2K 1

[nowayout]

An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll

Additional information: The ConnectionString property has not been initialized.


I get this error when in try to add something to database.

Thanks.

 

[obislavu]

ConnectionString should be set as in the SqlConnection object. It can be set in the constructor or after :
string sConString ="....";

SqlConnection con = new SqlConnection();
con.ConnectionString = sConString;
or
SqlConnection con = new SqlConnection (sConString);
The error occurs when you try to open the Connection and not when you insert!
-obislavu-

 

[nowayout]

now i am getting this error
An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll

Additional information: System error.

the code is:

string Connectionstring = "Initial Catalog=EIS;Data Source=twiki;User ID=sa;password=tecumseh;";
//string Connectionstring ="Server=test;Database=test;UID=test;PWD=test;";
SqlConnection cn = new SqlConnection(Connectionstring);

cn.Open();
string query = "insert into failure_report(job_number, Asset_no, component_des) VALUES (" + jobnumber.Text.ToString().Trim() + "," + AssetNumber.Text.ToString().Trim() + "," + compdes.Text.ToString().Trim() + ")";

SqlCommand myCommand = new SqlCommand();
myCommand.CommandText = query;
myCommand.Connection = cn;
myCommand.ExecuteNonQuery();

cn.Close();

 

[obislavu]

0. Use try-catch to detect exactly where is the error
That error could be generated if:
1. the user=sa has no insert privilges in that table.
2. the insert command has errors.
As it is above , the failure_report table should have that columns declared as integers/numeric. If not there is an error.
I suspect the last column "compdes" is a string and in this case the query should be:

string query = "insert into failure_report(job_number, Asset_no, component_des) VALUES (" + jobnumber.Text.ToString().Trim() + "," + AssetNumber.Text.ToString().Trim() + ",'" + compdes.Text.ToString().Trim() + "')";

-obislavu-

 

[nowayout]

all the field is varchar. the user had previleges to update and insert data in to table!!

 

[obislavu]

If that is true then you should write:
string query = "insert into failure_report(job_number, Asset_no, component_des) VALUES ('" + jobnumber.Text.ToString().Trim() + "','" + AssetNumber.Text.ToString().Trim() + "','" + compdes.Text.ToString().Trim() + "')";
-obislavu-

 

[nowayout]

Thanks, it wroking but now, i am trying to add from dropdown list. I would like to add selected values from dropdown list to database?

How can i write code for that?? Sorry guys i am first time user for this so have some basic question!!

Thanks for helping me!!

 

[obislavu]

There is no big difference.
Assume jobnumber, AssetNumber and compdes are ComboBox controls.
Then :

string vJobNumber="";
string vAssetNumber="";
string vcompdes="";
if (jobnumber.SelectedIndex != -1)
{
  vJobNumer = jobnumber.SelectedItem.ToString().Trim();
}
if (AssetNumber.SelectedIndex != -1)
{
  vAssetNumber = AssetNumber.SelectedItem.ToString().Trim();
}
if (compdes.SelectedIndex != -1)
{
   vcompdes = compdes.SelectedItem.ToString().Trim();
}
string query = "insert into failure_report(job_number, Asset_no, component_des) VALUES ('" + vjobnumber + "','" + vAssetNumber + "','" + vcompdes + "')";

obislavu
The above code is working when there are items selected in the combox.
So you have check

 

[nowayout]

how can i make dropdown filed filled from database table!???

 

[obislavu]

Here is an example to populate the comboBox1 object with the name of provinces from the _tStates data source table;

SqlConnection oConn = new  SqlConnection(strConnection);
DataTable dt = new DataTable();
SqlDataAdapter da = new  SqlDataAdapter("SELECT Code, Province  FROM _tStates", oConn);
dt.Fill(dt);
  //..
comboBox1.DataSource = dt;
comboBox1.DisplayMember="Province";
comboBox1.ValueMember  ="Code";

// The list will show the province names
obislavu

 

[nowayout]

but it gives me error on dt.fill(dt)
also do i have to open the connectin and close it??

Thanks

 

[obislavu]

You should read da.Fill(dt)
1. Create connection OConn
2. Open connection , oConn.Open();
3. Create DataAdapter da
4. Create DataTable dt
4. Call Fill on dt
5. Close connection if no need for other purposes
6. Set DataSource, DisplayMember for the combobox control
Use try-catch block to trap any error.
In fact you should repeat most of the above steps for all combobox controls or you can write something like that:

private void InitCbx(ref System.Windows.Forms.ComboBox ctl, string strConnection, string txtSQL, string displayMember, string valMember)
{
	SqlConnection oConn							= null;
	SqlCommand workingCommand					= null;
	SqlDataAdapter workingDataAdapter			= null;
	try
	{
		oConn							= new SqlConnection(strConnection);
		workingCommand					= new SqlCommand();
		workingDataAdapter				= new SqlDataAdapter();
		oConn.Open();
		workingCommand.Connection		= oConn;
		workingCommand.CommandText = txtSQL;
		workingDataAdapter.SelectCommand = workingCommand;
		DataTable dtSource = new DataTable();
		workingDataAdapter.Fill(dtSource);
		ctl.DataSource = dtSource;
		ctl.DisplayMember = displayMember;
		ctl.ValueMember   = valMember;

	}
	catch(Exception e)
	{						
		string sMsg = "InitCbx(): " + e.GetType().Name + ":" + e.Message;
		//_LogEvent(sMsg ,_enumLogType.Error,_enumLogDestination.LogFile,false);
	}
	finally
	{
		// Free memory
		if (oConn !=null)
		{
			oConn.Close();
			oConn.Dispose();
		}
		if (workingDataAdapter !=null)
			workingDataAdapter.Dispose();
		if (workingCommand !=null)
			workingCommand.Dispose();
	}
}

The above code should work and here is how to call:

string strConnection="user id=xxxx;Password=blabla;Initial Catalog=mydb;Data Source=localhost;Connect Timeout=5";

string strSQL="SELECT Code, Province from _tStates";
InitCbx(ref comboBox1, strConnection,strSQL,"Province","");

strSQL = "SELECT AssetNumber from Asset";
InitCbx(ref comboBox2, strConnection,strSQL,"AssetNumber","");

strSQL = "SELECT jobnumber from Jobs";
InitCbx(ref comboBox3, strConnection,strSQL,"jobnumber","");

obislavu

 

[nowayout]

is there anything wrong witht his code?

private void SubAssembly_SelectedIndexChanged(object sender, System.EventArgs e)
{
//string strConnection = "Initial Catalog=EIS;Data Source=test;User ID=EIS;password=test;";
string strConnection = "Initial Catalog=EIS;Data Source=Test;User ID=EIS;password=test;";
SqlConnection oConn = new SqlConnection(strConnection);
oConn.Open();
DataSet ds = new DataSet("test");
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM subassembly", oConn);

da.Fill(ds,"testda");

DataTable dt = ds.Tables["testda"];

SubAssembly.DataSource = dt;

SubAssembly.DisplayMember = "name";
oConn.Close();
//da.Fill(ds);
//..
//SubAssembly.DataSource = ds;
//SubAssembly.DisplayMember="name";
//SubAssembly.ValueMember ="ds.name";

}

 

[obislavu]

0. The code seems Okay. Use always try-catch block.
1. I will not put that code in the SelectedIndexChanged handler!
2. Move it in another function and call that function when after the SubAssembly control is created.
3. Errors can occurs when:
- datasource is unknown
- user/password, datasource or initial catalog are wrong
- the subassembly table doesn't exist
- user has no privileges to do select on the subassembly table
4. Add:
if ( ds.Tables.Count >0)
{
DataTable dt = ds.Tables["testda"];
SubAssembly.DataSource = dt;
}

obislavu

 

[nowayout]

i tried that but it still not populating the field from database. what could be done now?

please help..

thanks

 

[obislavu]

If you have no other errors then use debug and check the DataTable after Fill().
Check the Columns to see the names.
Check Rows to see if you have data.
Next, check if DisplayMember is set to a column name found in the DataTable.
If it is not working put the code that you execute here.
obislavu

 

[nowayout]

This is the whole code i am using?

private void SubAssembly_SelectedIndexChanged(object sender, System.EventArgs e)
{
//string strConnection = "Initial Catalog=EIS;Data Source=test;User ID=EIS;password=test;";
string strConnection = "Initial Catalog=EIS;Data Source=test;User ID=EIS;password=test;";
SqlConnection oConn = new SqlConnection(strConnection);
oConn.Open();
try
{
DataSet ds = new DataSet("test");
SqlDataAdapter da = new SqlDataAdapter("SELECT name FROM subassembly", oConn);


da.Fill(ds,"testda");

DataTable dt = ds.Tables["testda"];

SubAssembly.DataSource = dt;

SubAssembly.DisplayMember = "name";
}

catch(Exception ex)

{

MessageBox.Show("The error is : " + ex.Message + " , The stack trace is : " + ex.StackTrace + " , The Source is : " + ex.Source );

}
oConn.Close();

 

[obislavu]

 1. I created on my localhost SQL a table named SubAssembly with the following columns:
 id -> int
 name ->varchar 50
 amount ->numeric
 2. I added 3 rows
 3. I created a form on which I put a ComboBox named SubAssembly and a buuton named btnInit.
 4. On the btnInit_Click() handler I called the following method which contains your code but my connection string:
 private void SubAssembly_Init()
	{
	   //string strConnection = "Initial Catalog=EIS;Data Source=test;User ID=EIS;password=test;";
	    string strConnection = "Initial Catalog=EIS;Data Source=test;User ID=EIS;password=test;";
	    SqlConnection oConn = new  SqlConnection(strConnection);
	    oConn.Open();
	    try
	    {
	    DataSet ds = new DataSet("test");
	    SqlDataAdapter da = new  SqlDataAdapter("SELECT name FROM subassembly", oConn);


	    da.Fill(ds,"testda");

	    DataTable dt = ds.Tables["testda"];

	    SubAssembly.DataSource = dt;

	    SubAssembly.DisplayMember = "name";
	    }

	    catch(Exception ex)

	    {    

	    MessageBox.Show("The error is : " + ex.Message + " , The stack trace is : " + ex.StackTrace + " , The Source is : " + ex.Source );

	    }
	oConn.Close();
	}
private void btnInit_Click(object sender, System.EventArgs e)
{
	SubAssembly_Init();
}

The SubAssembly combobox got all three values from the "name" column retrived from the SubAssembly source table.
I ask you to move the above code from the SelectedIndexChanged() handler.
Call the SubAssembly_Init() somewhere after the control is added to the form and before the form is displayed or on any onother handler.
obislavu

 

[cappmgr]

Obislavu:
You deserve a few more stars!
Marty

 

[nowayout]

Thanks for the help guys,
Also one more point that how can i put date on the form which can eventually added to database?