System.Web.Services.Protocols.SoapException

[VirusSpore]

Hi can anyone make any use outta this ?

 

System.Data.OleDb.OleDbException: Unspecified error at System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr) at System.Data.OleDb.OleDbConnection.InitializeProvider() at System.Data.OleDb.OleDbConnection.Open()

 

Thanks!

[Robby]

Seems that it cannot open a connection to your database. Verify your conection string and/or your connection object.

[VirusSpore]

Yup, but it still doesn't work ... basically, what i'm doing is adding additional components and functionality to the system ... Even after i've copied the original files into the folder over the altered ones, the application still cannot function, and i get the same error ...

 

Aaarrrggghhh !!

Anyone can lead me outta this abyss ?

[Robby]

not without more details, post some of your relevant code.

[Joe Mamma]

not without more details' date=' post some of your relevant code.[/quote']

I am guessing the OleDB drivers for the Target Database are not installed properly on the system.

[VirusSpore]

not without more details' date=' post some of your relevant code.[/quote']

 

Ok, here's the function in qns ...

 

<WebMethod()> Public Function GetSearchReportId(ByVal SchName As String, ByVal SchLevel As String, ByVal Month As String, ByVal Week As String) As String

Dim conn As New OleDbConnection(ConfigurationSettings.AppSettings("ConnectionString"))

Dim cmdGetReportId As OleDbCommand

Dim dtrReader As OleDbDataReader

Dim strReportId As String

Dim strSchoolSql As String

Dim strSchLevel As String

 

'Added variable

Dim strMonth As String

Dim strWeek As String

Dim strWhere As String

Dim strAnd As String

Dim strAllReport As String

strWhere = ""

strAnd = ""

 

 

strReportId = "SELECT intReportIdRT FROM tblReportRT "

 

'Obtain the Information needed for constructing the SQL query

'Convert data into SQl string format first before going through the check

'To determine how the SQl command is like

'If no school name, strSchoolSql contain nothing

 

If SchName <> "0" Then

strSchoolSql = " strSchoolNameRT = '" & SchName & "'"

End If

 

If SchLevel <> "0" Then

strSchLevel = " intSchLevelRT = " & SchLevel

End If

 

If Month <> "" Then

strMonth = " intMonthRT = " & Month '& " AND intWeekRT = " & Week

End If

 

If Week <> "" Then

strWeek = " intWeekRT = " & Week '& " AND intWeekRT = " & Week

End If

 

'If Year <> "" Then 'Added Codes

'strYear = " intYearRT = " & Year()

'End If

'Start to determine the SQL command, construction begin here onward

 

If SchName <> "0" Or SchLevel <> "0" Or Month <> "" Then 'when all 3 is nothing, capture all availiable report

If SchName <> "0" Then 'there are school name, means no need level

strReportId = strReportId & " WHERE " & strSchoolSql

If Month <> "" Then 'There are month data

strReportId = strReportId & " AND " & strMonth

If Week <> "" Then 'There are week data

strReportId = strReportId & " AND " & strWeek

' If Year <> "" Then

'strReportId = strReportId & " AND " & strYear

' Else

'Added Codes

'End If

Else

'there are school and month - whole month for the school

End If

Else

'Got school name, get all the data available for the school

End If

ElseIf SchLevel <> "0" Then 'No school name given, check if school level given

strReportId = strReportId & " WHERE " & strSchLevel

If Month <> "" Then 'There are month data

strReportId = strReportId & " AND " & strMonth

If Week <> "" Then 'There are week data

strReportId = strReportId & " AND " & strWeek

' If Year <> "" Then 'There is Year data

'strReportId = strReportId & " AND " & strYear

'End If

Else

'there are school level and month - whole month for the school level

End If

Else

'Got school level, get all the data available for the school level

End If

Else

'No School Name and School Level but got month given

strReportId = strReportId & " WHERE " & strMonth

If Week <> "" Then 'the month and specific week of all the schools

strReportId = strReportId & " AND " & strWeek

Else

'the whole month for all the schools

End If

End If

End If

 

strReportId = strReportId & " AND " & "strStatusRT = 'Submit' "

 

cmdGetReportId = New OleDbCommand(strReportId, conn)

conn.Open()

dtrReader = cmdGetReportId.ExecuteReader

While dtrReader.Read

strAllReport = strAllReport & dtrReader("intReportIdRT") & ","

End While

conn.Close()

dtrReader.Close()

 

Return strAllReport

 

=================== end of function ===

 

the comments were posted by the guy who did this before me ... the thing abr the application is that it sometimes works, or else it returns the above error, even if the input is the same for both instances ...

[VirusSpore]

I am guessing the OleDB drivers for the Target Database are not installed properly on the system.

 

Joe Mamma, how do i remedy that ?

[Joe Mamma]

Joe Mamma' date=' how do i remedy that ?[/quote']If it works sporadically, I believe the ADO drivers sre installed correctly.

 

Can you pinpoint it to a specific combination of parameters?

Does it work all the time when no selection criteria is specified.

 

Something tells me the logic in the Select string building is wrong.

I never build select strings, unless it is the only way possible (which rarely is the case)

 

Did you pay for this code??? Don't hire him again :)

 

here is how I would do it. . .

(i assume the filelds are prefixed by their datatypes)

 

before we start, first make sure tblReportRT has indexes on

SchName, intSchLevelRT, intMonthRT, intWeekRT intYearRT

 

 

 

here we go. . this is C#, I hate VB. it truely is an awful language!

 

first, if no selection is made, I would pass nulls not empty strings or zeros. . .

Tsk, tsk, bad form. If you make no selection then the selection is null!!!

 

no guarantee this is syntactically correct as I am doing it on the fly, but the approach is sound!!!

public string GetSearchReportId(object oName, object oLevel, object oMonth, object oWeek, object oYear) 
{
string strReportId = "SELECT intReportIdRT FROM tblReportRT" +
				 " where (strSchoolNameRT like ?) and" +
				 " (strSchLevel between ? and ?) and" +
				 " (intMonthRT between ? and ?) and" +
				 " (intWeekRT between ? and ?) and" +
				 " (intYearRT = between ? and ?) and" +
				 " strStatusRT = 'Submit'";
 OleDbConnection conn = new OleDbConnection(ConfigurationSettings.AppSettings("ConnectionString"));
 conn.Open()
 OleDbCommand cmd = new OleDbCommand(strReportId, conn);
 cmd.Parameters[0].Value = (oName == null) ? "%" : oSchName.ToString();
 cmd.Parameters[1].Value = (oLevel == null) ? Int32.MinValue : Convert.ToInt32(oSchLevel);
 cmd.Parameters[2].Value = (oLevel == null) ? Int32.MaxValue : Convert.ToInt32(oSchLevel);
 cmd.Parameters[3].Value = (oMonth == null) ? Int32.MinValue : Convert.ToInt32(oMonth);
 cmd.Parameters[4].Value = (oMonth == null) ? Int32.MaxValue : Convert.ToInt32(oMonth);
 cmd.Parameters[5].Value = (oWeek== null) ? Int32.MinValue : Convert.ToInt32(oYear );
 cmd.Parameters[6].Value = (oWeek== null) ? Int32.MaxValue : Convert.ToInt32(oYear);
 cmd.Parameters[7].Value = (oYear == null) ? Int32.MinValue : Convert.ToInt32(oYear );
 cmd.Parameters[8].Value = (oYear == null) ? Int32.MaxValue : Convert.ToInt32(oYear);
 string result; 
 OleDbReader rdr = cmdGetReportId.ExecuteReader;
 While (rdr.Read())
	 result += rdr.GetString(0) +",";
 rdr.Close(); 
 conn.Close()
 return result;
} 

 

god, I love C#!!!

Edited July 8, 2004 by Joe Mamma

[VirusSpore]

Hahaha, ok well, the indexes are sound and present ( the database is stocked with data and i'm using this existing data to query it ) and well, frankly i havn't tried entering null parameters ...

 

well the sucky thing is that i can't, some parameters consistently return the above error while others return another error ... To pinpoint a specific set of input combinations, i can't do that but i can tell u which will NOT definitely work though ...

 

C# ? Corr, thanks but as the whole app was done in VB and VBscript with a little JavaScript thrown in, i think i'll stick with the above. But thanks for the solution, i'll see if i can make sense of it ... :)

 

If it isn't obvious yet, well, i'm a student currently doing my internship and i havn't had much exposure to VB and VS.NET until recently ... add VBscript to that list too !

 

this project was done by another fellow intern, abt a year ago, so it was done at no cost, hence the headache i s'pose ... lol

[Joe Mamma]

the statements

 

myvalue = [argument] ? [true value] : [false value];

 

are the equivalent of VB's

 

myvalue = Iif ( [argument], [true value], [false value])

 

construct.

 

(is it Iif or Iff ??? I always forget if I am not in VB!!!)

 

Parameters are the way to go!

Learn 'em!

Love 'em!

Live 'em!

 

'The unparameterized query is not worth preparing!'

(apologies to socrates :) )