Module Modsql
Function GetInTBystr (Byval FieldName As String, Byval Paraname As String, Byval Paravalue As String) AS Integer
DIM SQLCONNECTION As New Sqlclient.sqlConnection (GetConn)
DIM QueryString As String = "SELECT TOP 1" & FieldName & "from" & Tablename & "Where" & paraName & "= '" & paravalue & "'"
Dim SqlCommand as system.data.sqlclient.sqlcommand = new system.data.sqlclient.sqlcommand (querystring, sqlconnection)
DIM ROWSAFFECTED AS INTEGER = 0
SqlConnection.Open ()
Try
RowsAffected = IIF (Isdbnull (SqlCommand.executeScalar), 0, SQLCOMMAND.EXECUTESCALA
Catch exception
Throw new NotSupportedException (ex.Message)
Finally
SqlConnection.Close ()
END TRY
Return RowsAffected
END FUNCTION
Function getIntbyint (Byval Fieldname As String, Byval Paraname As String, BYVAL Paravalue As Integer) AS Integer AS INTEGER
DIM SQLCONNECTION As New Sqlclient.sqlConnection (GetConn)
DIM QueryString As String = "SELECT TOP 1" & FieldName & "From" & Tablename & "Where" & paraName & "=" & paravalue & ""
Dim SqlCommand as system.data.sqlclient.sqlcommand = new system.data.sqlclient.sqlcommand (querystring, sqlconnection)
DIM ROWSAFFECTED AS INTEGER = 0
SqlConnection.Open ()
Try
RowsAffected = IIF (Isdbnull (SqlCommand.executeScalar), 0, SQLCOMMAND.EXECUTESCALA
Catch exception
Throw new NotSupportedException (ex.Message)
Finally
SqlConnection.Close ()
END TRY
Return RowsAffected
END FUNCTION
Function GetStrBystr (Byval Fieldname As String, Byval Paraname As String, Byval Paravalue As String) AS STRINGDIM SQLCONNECTION As new SqlClient.sqlConnection (GetConn)
DIM QueryString As String = "SELECT TOP 1" & FieldName & "from" & Tablename & "Where" & paraName & "= '" & paravalue & "'"
Dim SqlCommand as system.data.sqlclient.sqlcommand = new system.data.sqlclient.sqlcommand (querystring, sqlconnection)
DIM ROWSAFFECTED AS STRING = ""
SqlConnection.Open ()
Try
RowsAffected = IIF (Isdbnull (SqlCommand.executescalar), "", SQLCOMMAND.EXECUTESCALAR)
Catch exception
Throw new NotSupportedException (ex.Message)
Finally
SqlConnection.Close ()
END TRY
Return Trim (RowsAffected)
END FUNCTION
Function GetStrbyint (byval fieldname as string, byval paramentame as string, byval paravalue as integer) AS STRING
DIM SQLCONNECTION As New Sqlclient.sqlConnection (GetConn)
DIM QueryString As String = "SELECT TOP 1" & FieldName & "From" & Tablename & "Where" & paraName & "=" & paravalue & ""
Dim SqlCommand as system.data.sqlclient.sqlcommand = new system.data.sqlclient.sqlcommand (querystring, sqlconnection)
DIM ROWSAFFECTED AS STRING = ""
SqlConnection.Open ()
Try
RowsAffected = IIF (Isdbnull (SqlCommand.executescalar), "", SQLCOMMAND.EXECUTESCALAR)
Catch exception
Throw new NotSupportedException (ex.Message)
Finally
SqlConnection.Close ()
END TRY
Return Trim (RowsAffected)
END FUNCTION
Function getBitBYSTR (Byval Fieldname As String, Byval Paraname As String, Byval Paravalue As String) AS Booleandim SqlConnection As New Sqlclient.sqlConnection (GetConn)
DIM QueryString As String = "SELECT TOP 1" & FieldName & "from" & Tablename & "Where" & paraName & "= '" & paravalue & "'"
Dim SqlCommand as system.data.sqlclient.sqlcommand = new system.data.sqlclient.sqlcommand (querystring, sqlconnection)
DIM ROWSAFFECTED AS BOOLEAN = FALSE
SqlConnection.Open ()
Try
RowsAffected = IIF (Isdbnull (SqlCommand.executescalar), False, SqlCommand.executescalar)
Catch exception
Throw new NotSupportedException (ex.Message)
Finally
SqlConnection.Close ()
END TRY
Return RowsAffected
END FUNCTION
Function getBitbyint (byval fieldname as string, byval paramename as string, byval paravalue as integer) AS Boolean
DIM SQLCONNECTION As New Sqlclient.sqlConnection (GetConn)
DIM QueryString As String = "SELECT TOP 1" & FieldName & "From" & Tablename & "Where" & paraName & "=" & paravalue & ""
Dim SqlCommand as system.data.sqlclient.sqlcommand = new system.data.sqlclient.sqlcommand (querystring, sqlconnection)
DIM ROWSAFFECTED AS BOOLEAN = FALSE
SqlConnection.Open ()
Try
RowsAffected = IIF (Isdbnull (SqlCommand.executescalar), False, SqlCommand.executescalar)
Catch exception
Throw new NotSupportedException (ex.Message)
Finally
SqlConnection.Close ()
END TRY
Return RowsAffected
END FUNCTION
Function getdateByint (byval fieldname as string, byval parablename as string, byval paravalue as string, byval paravalue as integer) AS datetimedim sqlconnection as new sqlclient.sqlconnection (getConn)
DIM QueryString As String = "SELECT TOP 1" & FieldName & "From" & Tablename & "Where" & paraName & "=" & paravalue & ""
Dim SqlCommand as system.data.sqlclient.sqlcommand = new system.data.sqlclient.sqlcommand (querystring, sqlconnection)
DIM ROWSAFFECTED AS dateTime
SqlConnection.Open ()
Try
Rowsaffected = IIF (isdbnull (SqlCommand.executeScalar), DateAdd (DateInterVal.Year, -100, Date.Now), SQLCommand.executescalar)
Catch exception
Throw new NotSupportedException (ex.Message)
Finally
SqlConnection.Close ()
END TRY
Return RowsAffected
END FUNCTION
Function getdateBystr (Byval Fieldname As String, Byval Paraname As String, ByVal Paravalue As String) AS DateTime
DIM SQLCONNECTION As New Sqlclient.sqlConnection (GetConn)
DIM QueryString As String = "SELECT TOP 1" & FieldName & "from" & Tablename & "Where" & paraName & "= '" & paravalue & "'"
Dim SqlCommand as system.data.sqlclient.sqlcommand = new system.data.sqlclient.sqlcommand (querystring, sqlconnection)
DIM ROWSAFFECTED AS dateTime
SqlConnection.Open ()
Try
Rowsaffected = IIF (isdbnull (SqlCommand.executeScalar), DateAdd (DateInterVal.Year, -100, Date.Now), SQLCommand.executescalar)
Catch exception
Throw new NotSupportedException (ex.Message)
Finally
SqlConnection.Close ()
END TRY
Return RowsAffected
END FUNCTION
Function Delbyint (Byval Paraname As String, Byval Paravalue As Integer) AS INTEGERDIM SQLCONNECTION As New Sqlclient.sqlConnection (GetConn)
DIM QueryString as string = "delete from" & Tablename & "Where" & paraName & "=" & paravalue
Dim SqlCommand as system.data.sqlclient.sqlcommand = new system.data.sqlclient.sqlcommand (querystring, sqlconnection)
DIM ROWSAFFECTED AS INTEGER = 0
SqlConnection.Open ()
Try
RowsAffected = SQLCommand.executenonQuery
Catch exception
Throw new NotSupportedException (ex.Message)
Finally
SqlConnection.Close ()
END TRY
Return RowsAffected
END FUNCTION
Function Delbystr (Byval Tablename As String, Byval Paravalue As String) AS Integer
DIM SQLCONNECTION As New Sqlclient.sqlConnection (GetConn)
DIM QueryString As String = "Delete from" & Tablename & "Where" & paraName & "= '" & paravalue & "'"
Dim SqlCommand as system.data.sqlclient.sqlcommand = new system.data.sqlclient.sqlcommand (querystring, sqlconnection)
DIM ROWSAFFECTED AS INTEGER = 0
SqlConnection.Open ()
Try
RowsAffected = SQLCommand.executenonQuery
Catch exception
Throw new NotSupportedException (ex.Message)
Finally
SqlConnection.Close ()
END TRY
Return RowsAffected
END FUNCTION
Function Delbysql (Byval Strsql As String) AS Integer
DIM SQLCONNECTION As New Sqlclient.sqlConnection (GetConn)
DIM QueryString as string = strsql
Dim SqlCommand as system.data.sqlclient.sqlcommand = new system.data.sqlclient.sqlcommand (querystring, sqlconnection)
DIM ROWSAFFECTED AS INTEGER = 0
SqlConnection.Open () TRY
RowsAffected = SQLCommand.executenonQuery
Catch exception
Throw new NotSupportedException (ex.Message)
Finally
SqlConnection.Close ()
END TRY
Return RowsAffected
END FUNCTION
Function updatebitBysql (Byval Tablename As String, Byval DatafieldValue As Boolean, Byval Parasql As String) AS String
DIM SQLCONNECTION As New Sqlclient.sqlConnection (GetConn)
DIM QueryString As String = "Update" & Tablename & "Set" & DatafieldName & "=" & IIF (DatafieldValue = true, 1, 0) & "where" & parasql
Dim SqlCommand as system.data.sqlclient.sqlcommand = new system.data.sqlclient.sqlcommand (querystring, sqlconnection)
SqlConnection.Open ()
Try
Sqlcommand.executenonQuery ()
Return ""
Catch exception
Return EX.MESSAGE
Finally
SqlConnection.Close ()
END TRY
END FUNCTION
Function UpdateStrByint (Byval Tablename As String, Byval DatafieldValue As String, Byval Para As String, Byval Paravalue As Integer) AS Integer
DIM SQLCONNECTION As New Sqlclient.sqlConnection (GetConn)
DIM QueryString As String = "Update" & Tablename & "Set" & DatafieldName & "= '" & DatafieldValue & "' Where" & Para & "=" & paravalue
Dim SqlCommand as system.data.sqlclient.sqlcommand = new system.data.sqlclient.sqlcommand (querystring, sqlconnection)
DIM ROWSAFFECTED AS INTEGER = 0
SqlConnection.Open ()
Try
RowsAffected = SQLCommand.executenonQuery
Catch exception
Throw new NotSupportedException (ex.Message)
Finally
SqlConnection.Close ()
End Tryreturn RowsAffected
END FUNCTION
Function UpdateStrBystr (Byval Tablename As String, Byval DatafieldValue As String, Byval Para As String, Byval Paravalue As String) AS Integer
DIM SQLCONNECTION As New Sqlclient.sqlConnection (GetConn)
Dim querystring as string = "Update" & Tablename & "Set" & datafieldname "= '" & datafieldvalue & "' Where" & para & "= '" & paravalue & "'"
Dim SqlCommand as system.data.sqlclient.sqlcommand = new system.data.sqlclient.sqlcommand (querystring, sqlconnection)
DIM ROWSAFFECTED AS INTEGER = 0
SqlConnection.Open ()
Try
RowsAffected = SQLCommand.executenonQuery
Catch exception
Throw new NotSupportedException (ex.Message)
Finally
SqlConnection.Close ()
END TRY
Return RowsAffected
END FUNCTION
Function UpdateIntbyint (byval Tablename As String, Byval DatafieldName As "AS Integer, Byval Para As String, BYVAL Para As String
DIM SQLCONNECTION As New Sqlclient.sqlConnection (GetConn)
Dim querystring as string = "Update" & Tablename & "Set" & datafieldname & "=" & datafieldvalue & "where" & para & "=" & paravalue
Dim SqlCommand as system.data.sqlclient.sqlcommand = new system.data.sqlclient.sqlcommand (querystring, sqlconnection)
DIM ROWSAFFECTED AS INTEGER = 0
SqlConnection.Open ()
Try
RowsAffected = SQLCommand.executenonQuery
Catch exception
Throw new NotSupportedException (ex.Message)
Finally
SqlConnection.Close ()
END TRY
Return RowsAffected
End FunctionFunction UpdateInTBystr (Byval Tablename As String, Byval DatafieldValue AS Integer, Byval Para As String, Byval Paravalue As String) AS Integer
DIM SQLCONNECTION As New Sqlclient.sqlConnection (GetConn)
DIM QueryString As String = "Update" & Tablename & "Set" & DatafieldName & "=" & datafieldValue & "Where" & para & "= '" & paravalue & "'"
Dim SqlCommand as system.data.sqlclient.sqlcommand = new system.data.sqlclient.sqlcommand (querystring, sqlconnection)
DIM ROWSAFFECTED AS INTEGER = 0
SqlConnection.Open ()
Try
RowsAffected = SQLCommand.executenonQuery
Catch exception
Throw new NotSupportedException (ex.Message)
Finally
SqlConnection.Close ()
END TRY
Return RowsAffected
END FUNCTION
Function updateInTBysql (Byval Tablename As String, Byval DatafieldValue As Integer, Byval Parasql AS String) AS Integer
DIM SQLCONNECTION As New Sqlclient.sqlConnection (GetConn)
DIM QueryString As String = "Update" & Tablename & "Set" & DatafieldName & "=" & DataFieldValue & "Where" & paraSQL
Dim SqlCommand as system.data.sqlclient.sqlcommand = new system.data.sqlclient.sqlcommand (querystring, sqlconnection)
DIM ROWSAFFECTED AS INTEGER = 0
SqlConnection.Open ()
Try
RowsAffected = SQLCommand.executenonQuery
Catch exception
Throw new NotSupportedException (ex.Message)
Finally
SqlConnection.Close ()
END TRY
Return RowsAffected
END FUNCTION
Function UpdateStrBySQL (ByVal TableName As String, ByVal DataFieldName As String, ByVal DataFieldValue As String, ByVal ParaSQL As String) As IntegerDim sqlConnection As New SqlClient.SqlConnection (GetConn)
DIM QueryString As String = "Update" & Tablename & "Set" & DatafieldName & "= '" & DatafieldValue & "' Where" & paraSQL
Dim SqlCommand as system.data.sqlclient.sqlcommand = new system.data.sqlclient.sqlcommand (querystring, sqlconnection)
DIM ROWSAFFECTED AS INTEGER = 0
SqlConnection.Open ()
Try
RowsAffected = SQLCommand.executenonQuery
Catch exception
Throw new NotSupportedException (ex.Message)
Finally
SqlConnection.Close ()
END TRY
Return RowsAffected
END FUNCTION
Function updateBysql (Byval Tablename As String, Byval Parasql AS String) AS Integer
DIM SQLCONNECTION As New Sqlclient.sqlConnection (GetConn)
DIM QueryString As String = "Update" & Tablename & "Set" & setsql & "where" & parasql
Dim SqlCommand as system.data.sqlclient.sqlcommand = new system.data.sqlclient.sqlcommand (querystring, sqlconnection)
DIM ROWSAFFECTED AS INTEGER = 0
SqlConnection.Open ()
Try
RowsAffected = SQLCommand.executenonQuery
Catch exception
Throw new NotSupportedException (ex.Message)
Finally
SqlConnection.Close ()
END TRY
Return RowsAffected
END FUNCTION
Function getDataTablebyformat (byval vrecordnumber as integer) as system.data.DataTable
Dim RecordNumber As String
IF VRecordNumber = 0 THEN
Recordnumber = ""
Else
RecordNumber = "TOP" & VRecordNumbendiff
DIM SQLCONNECTION As New Sqlclient.sqlConnection (GetConn)
DIM QueryString As String = "Select" & RecordNumber & "& itemsql &" from "& Tablename &" Where "& paraSQL
Dim SqlCommand as system.data.sqlclient.sqlcommand = new system.data.sqlclient.sqlcommand (querystring, sqlconnection)
Dim DataAdapter as system.data.sqlclient.sqldataadapter = new system.data.sqlclient.sqldataadapter (SqlCommand)
Dim dataset as system.data.dataset = new system.data.dataset
Try
DataAdapter.Fill (Dataset)
Return dataset.tables (0)
Catch exception
Throw new NotSupportedException (ex.Message)
Finally
SqlConnection.Close ()
END TRY
END FUNCTION
Function getDataTableBysql (Byval Supersql as string) as system.data.dataable
DIM SQLCONNECTION As New Sqlclient.sqlConnection (GetConn)
Dim sqlcommand as system.data.sqlclient.sqlcommand = new system.data.sqlclient.sqlcommand (supersql, sqlconnection)
Dim DataAdapter as system.data.sqlclient.sqldataadapter = new system.data.sqlclient.sqldataadapter (SqlCommand)
Dim dataset as system.data.dataset = new system.data.dataset
Try
DataAdapter.Fill (Dataset)
Return dataset.tables (0)
Catch exception
Throw new NotSupportedException (ex.Message)
Finally
SqlConnection.Close ()
END TRY
END FUNCTION
Function getMaxfield (Byval Tablename As String, Byval Fieldname As String) AS Integer
DIM SQLCONNECTION As New Sqlclient.sqlConnection (GetConn)
DIM QueryString as string = "SELECT MAX (" & FieldName & ") AS Maxfield from" & Tablename
Dim SqlCommand as system.data.sqlclient.sqlcommand = new system.data.sqlclient.sqlcommand (querystring, sqlconnection) DIM ROWSAFFECTED AS INTEGER = 0
SqlConnection.Open ()
Try
RowsAffected = IIF (Isdbnull (SqlCommand.executeScalar), 0, SQLCOMMAND.EXECUTESCALA
Catch exception
Throw new NotSupportedException (ex.Message)
Finally
SqlConnection.Close ()
END TRY
Return RowsAffected
END FUNCTION
Function getMinfield (Byval Tablename As String, Byval FieldName As String) AS Integer
DIM SQLCONNECTION As New Sqlclient.sqlConnection (GetConn)
DIM QueryString as string = "SELECT MIN (" & FieldName & ") AS Maxfield from" & Tablename
Dim SqlCommand as system.data.sqlclient.sqlcommand = new system.data.sqlclient.sqlcommand (querystring, sqlconnection)
DIM ROWSAFFECTED AS INTEGER = 0
SqlConnection.Open ()
Try
RowsAffected = IIF (Isdbnull (SqlCommand.executeScalar), 0, SQLCOMMAND.EXECUTESCALA
Catch exception
Throw new NotSupportedException (ex.Message)
Finally
SqlConnection.Close ()
END TRY
Return RowsAffected
END FUNCTION
Function getfieldcount (Byval Parasql As String) AS Integer
DIM SQLCONNECTION As New Sqlclient.sqlConnection (GetConn)
Dim querystring as string = "Select count (*) As FieldCount from" & Tablename & "Where" & paraSQL
Dim SqlCommand as system.data.sqlclient.sqlcommand = new system.data.sqlclient.sqlcommand (querystring, sqlconnection)
DIM ROWSAFFECTED AS INTEGER
SqlConnection.Open ()
Try
RowsAffected = SQLCOMMAND.EXECUTESCALARARARARAR
Catch exception
Throw new NotSupportedException (ex.Message)
Finally
SqlConnection.Close ()
END TRY
Return RowsAffected
End FunctionFunction GetfieldsumByint (Byval FieldName As String, Byval Parasql AS String) AS Integer
DIM SQLCONNECTION As New Sqlclient.sqlConnection (GetConn)
Dim querystring as string = "SELECT ISNULL (SUM (" & FieldName & "), 0) As CountNumber from" & Tablename & "Where" & paraSQL
Dim SqlCommand as system.data.sqlclient.sqlcommand = new system.data.sqlclient.sqlcommand (querystring, sqlconnection)
DIM ROWSAFFECTED AS INTEGER
SqlConnection.Open ()
Try
RowsAffected = SQLCOMMAND.EXECUTESCALARARARARAR
Catch exception
Throw new NotSupportedException (ex.Message)
Finally
SqlConnection.Close ()
END TRY
Return RowsAffected
END FUNCTION
Function getfieldsumbydec (Byval Tablename as string, byval fouthname as string) AS Decimal
DIM SQLCONNECTION As New Sqlclient.sqlConnection (GetConn)
Dim querystring as string = "SELECT ISNULL (SUM (" & FieldName & "), 0) As CountNumber from" & Tablename & "Where" & paraSQL
Dim SqlCommand as system.data.sqlclient.sqlcommand = new system.data.sqlclient.sqlcommand (querystring, sqlconnection)
DIM ROWSAFFECTED AS DECIMAL
SqlConnection.Open ()
Try
RowsAffected = SQLCOMMAND.EXECUTESCALARARARARAR
Catch exception
Throw new NotSupportedException (ex.Message)
Finally
SqlConnection.Close ()
END TRY
Return RowsAffected
END FUNCTION
Function ChgfieldByleftword (Byval Tablename As String, Byval Paraword As String, Byval Newword As String) AS Integer
Try
Return UpdateBysql (Tablename, FieldName & "" & NewWord & "' Right (" & FieldName & "," & Len (Paraword) & ")", "Len (" & " FieldName & ")> =" & Len ("& FieldName &") Catch EX AS Exception
Throw new NotSupportedException (ex.Message)
END TRY
END FUNCTION
'Database connection string
Private function getConn () AS String
Return "Server = localhost; database = pubs; uid = sa; pwd ="
END FUNCTION
End module
