ADO connections on a web form (repost)

J

Jeremy Ames

Can someone please help with this?

Unfortunately, that did not work.
"Ignacio Machin ( .NET/ C# MVP )" <ignacio.machin AT dot.state.fl.us>
wrote in message Hi Jeremy,

Please try using 127.0.0.1 instead of (local) in the connection string:

cnTask.ConnectionString="Data Source=127.0.0.1;" +

"Initial Catalog=EmpDB;" +

"User ID=WebClient;" +

"Password=WebClient";


Cheers,


--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation



Here is the code. I am using ADO.NET with new connections and
connections strings everytime.

private void BuildEmployeeDetail(int nEmpId)

{

int [] narValues = new int[15] {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0};

string [] sarInitals = new string[15];

LoadInitialLists(narValues, sarInitals);


string sSql = "SELECT T.TaskDesc, C.TaskId, C.Complete, C.Initials " +

"FROM TasksComplete C " +

"JOIN Tasks T ON (C.TaskId = T.TaskId) " +

"WHERE RemovalId = " + nEmpId;

SqlConnection cnEmployee = new SqlConnection();

cnEmployee.ConnectionString="Data Source=(local);" +

"Initial Catalog=EmpDB;" +

"User ID=guest;" +

"Password=";

SqlCommand cmdEmployee = new SqlCommand(sSql, cnEmployee);

cnEmployee.Open();

drEmployee = cmdEmployee.ExecuteReader();


int nCnt = 1;


while(drEmployee.Read())

{

// create a row to add to the existing table

TableRow rowTemplate = new TableRow();

if (nCnt % 2 != 0)

rowTemplate.BackColor = System.Drawing.Color.White;

else

rowTemplate.BackColor = System.Drawing.Color.Silver;

// create cells to add to the previously created row

TableCell cellCol1 = new TableCell();

TableCell cellCol2 = new TableCell();

TableCell cellCol3 = new TableCell();

// enter the description into the first cell

FillFirstCell(rowTemplate, cellCol1, drEmployee.GetString(0), nCnt,
drEmployee.GetBoolean(2));


// enter a hidden task id and check box to the second cell

FillSecondCell(rowTemplate, cellCol2, drEmployee.GetBoolean(2), nCnt,
drEmployee.GetInt32(1));


FillThirdCell(rowTemplate, cellCol3, nCnt, narValues, sarInitals,
drEmployee.GetInt32(3));

// add all of the cells in the row to the table


tblDetail.Rows.Add(rowTemplate);


//dlCopyInitials.Dispose();

rowTemplate.Dispose();

cellCol1.Dispose();

cellCol2.Dispose();

cellCol3.Dispose();

nCnt += 1;

}

drEmployee.Close();

cnEmployee.Close();

cnEmployee.Dispose();

}



private void LoadInitialLists(int [] narVals, string [] sarVals)

{

string sSql = "SELECT I.IsId, I.IsInitials FROM jwames.istable I ORDER
BY 2";

SqlConnection cnEmployee = new SqlConnection();

cnEmployee.ConnectionString="Data Source=(local);" +

"Initial Catalog=EmpDB;" +

"User ID=guest;" +

"Password=";

SqlCommand cmdEmployee = new SqlCommand(sSql, cnEmployee);

cnEmployee.Open();

drEmployee = cmdEmployee.ExecuteReader();

int nCnt = 0;

while(drEmployee.Read())

{

sarVals[nCnt] = drEmployee.GetString(1);

narVals[nCnt++] = drEmployee.GetInt32(0);

}

drEmployee.Close();

cnEmployee.Close();

cnEmployee.Dispose();

}



private void UpdateTask(int nEmpId, int nTaskId, bool bChecked, int
nInitials)

{

SqlConnection cnTask = new SqlConnection();

cnTask.ConnectionString="Data Source=(local);" +

"Initial Catalog=EmpDB;" +

"User ID=WebClient;" +

"Password=WebClient";


SqlCommand cmdUpdateTask = new SqlCommand("usp_UpdateTaskCompletion",
cnTask);

cmdUpdateTask.CommandType = CommandType.StoredProcedure;


SqlParameter parTask = cmdUpdateTask.Parameters.Add("@p_TaskId",
SqlDbType.Int);

parTask.Value = nTaskId;

parTask = cmdUpdateTask.Parameters.Add("@p_RemovalId", SqlDbType.Int);

parTask.Value = nEmpId;

parTask = cmdUpdateTask.Parameters.Add("@p_Initials", SqlDbType.Int);

parTask.Value = nInitials;

parTask = cmdUpdateTask.Parameters.Add("@p_Complete", SqlDbType.Bit);

parTask.Value = bChecked;

cnTask.Open();

cmdUpdateTask.ExecuteNonQuery();

cnTask.Close();

cmdUpdateTask.Dispose();

cnTask.Dispose();

}

"Ignacio Machin ( .NET/ C# MVP )" <ignacio.machin AT dot.state.fl.us>
wrote in message Hi Jeremy,

A piece of code will be nice, also, are you using ADO or ADO.NET ?
IF not these are a few hints,
1- See if you are really using the third connectionstring
2- Try to not reuse any of the previous used object, create new
Connection,
Command objects

Other than that I cannot think of a possible cause of this problem.

Cheers,
 
I

Ignacio Machin \( .NET/ C# MVP \)

HI Jeremy,

I haven't forget about this :)

Two more suggestions:
1- Place a breakpoint after you assign you open your connection and see what
is the connection string, if it's the correct one then the problem may lay
in the SQL server configuration as described on suggestion 2.

I don't see any problem in your code, so now I'm thinking in the SQL server
configuration. Go to the enterprise manager/Security and make sure that
WebClient is using "SQL Server Authentication" and not "Windows
Authentication" then go to "Database Access" tab and see if the User in your
DB (empDB) is the correct one"

Cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

Jeremy Ames said:
Can someone please help with this?

Unfortunately, that did not work.
"Ignacio Machin ( .NET/ C# MVP )" <ignacio.machin AT dot.state.fl.us>
wrote in message Hi Jeremy,

Please try using 127.0.0.1 instead of (local) in the connection string:

cnTask.ConnectionString="Data Source=127.0.0.1;" +

"Initial Catalog=EmpDB;" +

"User ID=WebClient;" +

"Password=WebClient";


Cheers,


--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation



Here is the code. I am using ADO.NET with new connections and
connections strings everytime.

private void BuildEmployeeDetail(int nEmpId)

{

int [] narValues = new int[15] {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0};

string [] sarInitals = new string[15];

LoadInitialLists(narValues, sarInitals);


string sSql = "SELECT T.TaskDesc, C.TaskId, C.Complete, C.Initials " +

"FROM TasksComplete C " +

"JOIN Tasks T ON (C.TaskId = T.TaskId) " +

"WHERE RemovalId = " + nEmpId;

SqlConnection cnEmployee = new SqlConnection();

cnEmployee.ConnectionString="Data Source=(local);" +

"Initial Catalog=EmpDB;" +

"User ID=guest;" +

"Password=";

SqlCommand cmdEmployee = new SqlCommand(sSql, cnEmployee);

cnEmployee.Open();

drEmployee = cmdEmployee.ExecuteReader();


int nCnt = 1;


while(drEmployee.Read())

{

// create a row to add to the existing table

TableRow rowTemplate = new TableRow();

if (nCnt % 2 != 0)

rowTemplate.BackColor = System.Drawing.Color.White;

else

rowTemplate.BackColor = System.Drawing.Color.Silver;

// create cells to add to the previously created row

TableCell cellCol1 = new TableCell();

TableCell cellCol2 = new TableCell();

TableCell cellCol3 = new TableCell();

// enter the description into the first cell

FillFirstCell(rowTemplate, cellCol1, drEmployee.GetString(0), nCnt,
drEmployee.GetBoolean(2));


// enter a hidden task id and check box to the second cell

FillSecondCell(rowTemplate, cellCol2, drEmployee.GetBoolean(2), nCnt,
drEmployee.GetInt32(1));


FillThirdCell(rowTemplate, cellCol3, nCnt, narValues, sarInitals,
drEmployee.GetInt32(3));

// add all of the cells in the row to the table


tblDetail.Rows.Add(rowTemplate);


//dlCopyInitials.Dispose();

rowTemplate.Dispose();

cellCol1.Dispose();

cellCol2.Dispose();

cellCol3.Dispose();

nCnt += 1;

}

drEmployee.Close();

cnEmployee.Close();

cnEmployee.Dispose();

}



private void LoadInitialLists(int [] narVals, string [] sarVals)

{

string sSql = "SELECT I.IsId, I.IsInitials FROM jwames.istable I ORDER
BY 2";

SqlConnection cnEmployee = new SqlConnection();

cnEmployee.ConnectionString="Data Source=(local);" +

"Initial Catalog=EmpDB;" +

"User ID=guest;" +

"Password=";

SqlCommand cmdEmployee = new SqlCommand(sSql, cnEmployee);

cnEmployee.Open();

drEmployee = cmdEmployee.ExecuteReader();

int nCnt = 0;

while(drEmployee.Read())

{

sarVals[nCnt] = drEmployee.GetString(1);

narVals[nCnt++] = drEmployee.GetInt32(0);

}

drEmployee.Close();

cnEmployee.Close();

cnEmployee.Dispose();

}



private void UpdateTask(int nEmpId, int nTaskId, bool bChecked, int
nInitials)

{

SqlConnection cnTask = new SqlConnection();

cnTask.ConnectionString="Data Source=(local);" +

"Initial Catalog=EmpDB;" +

"User ID=WebClient;" +

"Password=WebClient";


SqlCommand cmdUpdateTask = new SqlCommand("usp_UpdateTaskCompletion",
cnTask);

cmdUpdateTask.CommandType = CommandType.StoredProcedure;


SqlParameter parTask = cmdUpdateTask.Parameters.Add("@p_TaskId",
SqlDbType.Int);

parTask.Value = nTaskId;

parTask = cmdUpdateTask.Parameters.Add("@p_RemovalId", SqlDbType.Int);

parTask.Value = nEmpId;

parTask = cmdUpdateTask.Parameters.Add("@p_Initials", SqlDbType.Int);

parTask.Value = nInitials;

parTask = cmdUpdateTask.Parameters.Add("@p_Complete", SqlDbType.Bit);

parTask.Value = bChecked;

cnTask.Open();

cmdUpdateTask.ExecuteNonQuery();

cnTask.Close();

cmdUpdateTask.Dispose();

cnTask.Dispose();

}

"Ignacio Machin ( .NET/ C# MVP )" <ignacio.machin AT dot.state.fl.us>
wrote in message Hi Jeremy,

A piece of code will be nice, also, are you using ADO or ADO.NET ?
IF not these are a few hints,
1- See if you are really using the third connectionstring
2- Try to not reuse any of the previous used object, create new
Connection,
Command objects

Other than that I cannot think of a possible cause of this problem.

Cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

Jeremy Ames said:
I have a problem with one of connections using the wrong connection string.
I have a form that has three late bound sql connections. Each connection
is
local to the function that it used in. The problem I am having is
Click to expand...
that
the
last connection uses a different username and password to sign in.
Click to expand...
By
the
time that the third connection is opened, the other two connections have
been opened, closed, and disposed. What is happening is that the third
connection is still using the connection string from the other connections
and connecting with the wrong user credentials. This particular connection
executes an update command kept in a stored procedure. I do not
Click to expand...
allow
that
particular user, the guest account, access to anything that changes data.
What could possibly be happening? I do not want to change my
Click to expand...
security
levels
to allow this. Please help!
Click to expand...
Click to expand...
 
J

Jeremy Ames

1- The information is coming back correct in the debug window for the
connection string.
2- The user is using sql authentication and it is setup with access to the
databases.

"Ignacio Machin ( .NET/ C# MVP )" <ignacio.machin AT dot.state.fl.us> wrote
in message HI Jeremy,

I haven't forget about this :)

Two more suggestions:
1- Place a breakpoint after you assign you open your connection and see what
is the connection string, if it's the correct one then the problem may lay
in the SQL server configuration as described on suggestion 2.

I don't see any problem in your code, so now I'm thinking in the SQL server
configuration. Go to the enterprise manager/Security and make sure that
WebClient is using "SQL Server Authentication" and not "Windows
Authentication" then go to "Database Access" tab and see if the User in your
DB (empDB) is the correct one"

Cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

Jeremy Ames said:
Can someone please help with this?

Unfortunately, that did not work.
"Ignacio Machin ( .NET/ C# MVP )" <ignacio.machin AT dot.state.fl.us>
wrote in message Hi Jeremy,

Please try using 127.0.0.1 instead of (local) in the connection string:

cnTask.ConnectionString="Data Source=127.0.0.1;" +

"Initial Catalog=EmpDB;" +

"User ID=WebClient;" +

"Password=WebClient";


Cheers,


--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation



Here is the code. I am using ADO.NET with new connections and
connections strings everytime.

private void BuildEmployeeDetail(int nEmpId)

{

int [] narValues = new int[15] {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0};

string [] sarInitals = new string[15];

LoadInitialLists(narValues, sarInitals);


string sSql = "SELECT T.TaskDesc, C.TaskId, C.Complete, C.Initials " +

"FROM TasksComplete C " +

"JOIN Tasks T ON (C.TaskId = T.TaskId) " +

"WHERE RemovalId = " + nEmpId;

SqlConnection cnEmployee = new SqlConnection();

cnEmployee.ConnectionString="Data Source=(local);" +

"Initial Catalog=EmpDB;" +

"User ID=guest;" +

"Password=";

SqlCommand cmdEmployee = new SqlCommand(sSql, cnEmployee);

cnEmployee.Open();

drEmployee = cmdEmployee.ExecuteReader();


int nCnt = 1;


while(drEmployee.Read())

{

// create a row to add to the existing table

TableRow rowTemplate = new TableRow();

if (nCnt % 2 != 0)

rowTemplate.BackColor = System.Drawing.Color.White;

else

rowTemplate.BackColor = System.Drawing.Color.Silver;

// create cells to add to the previously created row

TableCell cellCol1 = new TableCell();

TableCell cellCol2 = new TableCell();

TableCell cellCol3 = new TableCell();

// enter the description into the first cell

FillFirstCell(rowTemplate, cellCol1, drEmployee.GetString(0), nCnt,
drEmployee.GetBoolean(2));


// enter a hidden task id and check box to the second cell

FillSecondCell(rowTemplate, cellCol2, drEmployee.GetBoolean(2), nCnt,
drEmployee.GetInt32(1));


FillThirdCell(rowTemplate, cellCol3, nCnt, narValues, sarInitals,
drEmployee.GetInt32(3));

// add all of the cells in the row to the table


tblDetail.Rows.Add(rowTemplate);


//dlCopyInitials.Dispose();

rowTemplate.Dispose();

cellCol1.Dispose();

cellCol2.Dispose();

cellCol3.Dispose();

nCnt += 1;

}

drEmployee.Close();

cnEmployee.Close();

cnEmployee.Dispose();

}



private void LoadInitialLists(int [] narVals, string [] sarVals)

{

string sSql = "SELECT I.IsId, I.IsInitials FROM jwames.istable I ORDER
BY 2";

SqlConnection cnEmployee = new SqlConnection();

cnEmployee.ConnectionString="Data Source=(local);" +

"Initial Catalog=EmpDB;" +

"User ID=guest;" +

"Password=";

SqlCommand cmdEmployee = new SqlCommand(sSql, cnEmployee);

cnEmployee.Open();

drEmployee = cmdEmployee.ExecuteReader();

int nCnt = 0;

while(drEmployee.Read())

{

sarVals[nCnt] = drEmployee.GetString(1);

narVals[nCnt++] = drEmployee.GetInt32(0);

}

drEmployee.Close();

cnEmployee.Close();

cnEmployee.Dispose();

}



private void UpdateTask(int nEmpId, int nTaskId, bool bChecked, int
nInitials)

{

SqlConnection cnTask = new SqlConnection();

cnTask.ConnectionString="Data Source=(local);" +

"Initial Catalog=EmpDB;" +

"User ID=WebClient;" +

"Password=WebClient";


SqlCommand cmdUpdateTask = new SqlCommand("usp_UpdateTaskCompletion",
cnTask);

cmdUpdateTask.CommandType = CommandType.StoredProcedure;


SqlParameter parTask = cmdUpdateTask.Parameters.Add("@p_TaskId",
SqlDbType.Int);

parTask.Value = nTaskId;

parTask = cmdUpdateTask.Parameters.Add("@p_RemovalId", SqlDbType.Int);

parTask.Value = nEmpId;

parTask = cmdUpdateTask.Parameters.Add("@p_Initials", SqlDbType.Int);

parTask.Value = nInitials;

parTask = cmdUpdateTask.Parameters.Add("@p_Complete", SqlDbType.Bit);

parTask.Value = bChecked;

cnTask.Open();

cmdUpdateTask.ExecuteNonQuery();

cnTask.Close();

cmdUpdateTask.Dispose();

cnTask.Dispose();

}

"Ignacio Machin ( .NET/ C# MVP )" <ignacio.machin AT dot.state.fl.us>
wrote in message Hi Jeremy,

A piece of code will be nice, also, are you using ADO or ADO.NET ?
IF not these are a few hints,
1- See if you are really using the third connectionstring
2- Try to not reuse any of the previous used object, create new
Connection,
Command objects

Other than that I cannot think of a possible cause of this problem.

Cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

Jeremy Ames said:
I have a problem with one of connections using the wrong connection string.
I have a form that has three late bound sql connections. Each connection
is
local to the function that it used in. The problem I am having is
Click to expand...
that
the
last connection uses a different username and password to sign in.
Click to expand...
By
the
time that the third connection is opened, the other two connections have
been opened, closed, and disposed. What is happening is that the third
connection is still using the connection string from the other connections
and connecting with the wrong user credentials. This particular connection
executes an update command kept in a stored procedure. I do not
Click to expand...
allow
that
particular user, the guest account, access to anything that changes data.
What could possibly be happening? I do not want to change my
Click to expand...
security
levels
to allow this. Please help!
Click to expand...
Click to expand...
 
J

Jerry Negrelli

What happens if you try "host=127.0.0.1; database=EmpDB;
user=WebClient; password=WebClient"?

I really wish the connection string didn't allow so many
syntaxes...

JER
-----Original Message-----
Can someone please help with this?

Unfortunately, that did not work.
"Ignacio Machin ( .NET/ C# MVP )" <ignacio.machin AT dot.state.fl.us>
wrote in message news:uwMY% (e-mail address removed)...
Hi Jeremy,

Please try using 127.0.0.1 instead of (local) in the connection string:

cnTask.ConnectionString="Data Source=127.0.0.1;" +

"Initial Catalog=EmpDB;" +

"User ID=WebClient;" +

"Password=WebClient";


Cheers,


--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation



Here is the code. I am using ADO.NET with new connections and
connections strings everytime.

private void BuildEmployeeDetail(int nEmpId)

{

int [] narValues = new int[15] {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0};

string [] sarInitals = new string[15];

LoadInitialLists(narValues, sarInitals);


string sSql = "SELECT T.TaskDesc, C.TaskId, C.Complete, C.Initials " +

"FROM TasksComplete C " +

"JOIN Tasks T ON (C.TaskId = T.TaskId) " +

"WHERE RemovalId = " + nEmpId;

SqlConnection cnEmployee = new SqlConnection();

cnEmployee.ConnectionString="Data Source=(local);" +

"Initial Catalog=EmpDB;" +

"User ID=guest;" +

"Password=";

SqlCommand cmdEmployee = new SqlCommand(sSql, cnEmployee);

cnEmployee.Open();

drEmployee = cmdEmployee.ExecuteReader();


int nCnt = 1;


while(drEmployee.Read())

{

// create a row to add to the existing table

TableRow rowTemplate = new TableRow();

if (nCnt % 2 != 0)

rowTemplate.BackColor = System.Drawing.Color.White;

else

rowTemplate.BackColor = System.Drawing.Color.Silver;

// create cells to add to the previously created row

TableCell cellCol1 = new TableCell();

TableCell cellCol2 = new TableCell();

TableCell cellCol3 = new TableCell();

// enter the description into the first cell

FillFirstCell(rowTemplate, cellCol1, drEmployee.GetString(0), nCnt,
drEmployee.GetBoolean(2));


// enter a hidden task id and check box to the second cell

FillSecondCell(rowTemplate, cellCol2,
Click to expand...
drEmployee.GetBoolean(2), nCnt,
drEmployee.GetInt32(1));


FillThirdCell(rowTemplate, cellCol3, nCnt, narValues, sarInitals,
drEmployee.GetInt32(3));

// add all of the cells in the row to the table


tblDetail.Rows.Add(rowTemplate);


//dlCopyInitials.Dispose();

rowTemplate.Dispose();

cellCol1.Dispose();

cellCol2.Dispose();

cellCol3.Dispose();

nCnt += 1;

}

drEmployee.Close();

cnEmployee.Close();

cnEmployee.Dispose();

}



private void LoadInitialLists(int [] narVals, string [] sarVals)

{

string sSql = "SELECT I.IsId, I.IsInitials FROM jwames.istable I ORDER
BY 2";

SqlConnection cnEmployee = new SqlConnection();

cnEmployee.ConnectionString="Data Source=(local);" +

"Initial Catalog=EmpDB;" +

"User ID=guest;" +

"Password=";

SqlCommand cmdEmployee = new SqlCommand(sSql, cnEmployee);

cnEmployee.Open();

drEmployee = cmdEmployee.ExecuteReader();

int nCnt = 0;

while(drEmployee.Read())

{

sarVals[nCnt] = drEmployee.GetString(1);

narVals[nCnt++] = drEmployee.GetInt32(0);

}

drEmployee.Close();

cnEmployee.Close();

cnEmployee.Dispose();

}



private void UpdateTask(int nEmpId, int nTaskId, bool bChecked, int
nInitials)

{

SqlConnection cnTask = new SqlConnection();

cnTask.ConnectionString="Data Source=(local);" +

"Initial Catalog=EmpDB;" +

"User ID=WebClient;" +

"Password=WebClient";


SqlCommand cmdUpdateTask = new SqlCommand ("usp_UpdateTaskCompletion",
cnTask);

cmdUpdateTask.CommandType = CommandType.StoredProcedure;


SqlParameter parTask = cmdUpdateTask.Parameters.Add ("@p_TaskId",
SqlDbType.Int);

parTask.Value = nTaskId;

parTask = cmdUpdateTask.Parameters.Add
Click to expand...
("@p_RemovalId", SqlDbType.Int);
parTask.Value = nEmpId;

parTask = cmdUpdateTask.Parameters.Add
Click to expand...
("@p_Initials", SqlDbType.Int);
parTask.Value = nInitials;

parTask = cmdUpdateTask.Parameters.Add
Click to expand...
("@p_Complete", SqlDbType.Bit);
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Validators with dynamic form controls 2

Top