module modsql
function getintbystr(byval fieldname as string, byval tablename 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.executescalar)
catch ex as exception
throw new notsupportedexception(ex.message)
finally
sqlconnection.close()
end try
return rowsaffected
end function
function getintbyint(byval fieldname as string, byval tablename as string, byval paraname as string, byval paravalue 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.executescalar)
catch ex as exception
throw new notsupportedexception(ex.message)
finally
sqlconnection.close()
end try
return rowsaffected
end function
function getstrbystr(byval fieldname as string, byval tablename as string, byval paraname as string, byval paravalue as string) 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 ex as exception
throw new notsupportedexception(ex.message)
finally
sqlconnection.close()
end try
return trim(rowsaffected)
end function
function getstrbyint(byval fieldname as string, byval tablename as string, byval paraname 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 ex as exception
throw new notsupportedexception(ex.message)
finally
sqlconnection.close()
end try
return trim(rowsaffected)
end function
function getbitbystr(byval fieldname as string, byval tablename as string, byval paraname as string, byval paravalue as string) 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 ex as exception
throw new notsupportedexception(ex.message)
finally
sqlconnection.close()
end try
return rowsaffected
end function
function getbitbyint(byval fieldname as string, byval tablename as string, byval paraname 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 ex as exception
throw new notsupportedexception(ex.message)
finally
sqlconnection.close()
end try
return rowsaffected
end function
function getdatebyint(byval fieldname as string, byval tablename as string, byval paraname as string, byval paravalue as integer) 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 ex as exception
throw new notsupportedexception(ex.message)
finally
sqlconnection.close()
end try
return rowsaffected
end function
function getdatebystr(byval fieldname as string, byval tablename 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 ex as exception
throw new notsupportedexception(ex.message)
finally
sqlconnection.close()
end try
return rowsaffected
end function
function delbyint(byval tablename as string, byval paraname as string, byval paravalue as integer) 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 ex as exception
throw new notsupportedexception(ex.message)
finally
sqlconnection.close()
end try
return rowsaffected
end function
function delbystr(byval tablename as string, byval paraname 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 ex as 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 ex as exception
throw new notsupportedexception(ex.message)
finally
sqlconnection.close()
end try
return rowsaffected
end function
function updatebitbysql(byval tablename as string, byval datafieldname 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 ex as exception
return ex.message
finally
sqlconnection.close()
end try
end function
function updatestrbyint(byval tablename as string, byval datafieldname 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 ex as exception
throw new notsupportedexception(ex.message)
finally
sqlconnection.close()
end try
return rowsaffected
end function
function updatestrbystr(byval tablename as string, byval datafieldname 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 ex as exception
throw new notsupportedexception(ex.message)
finally
sqlconnection.close()
end try
return rowsaffected
end function
function updateintbyint(byval tablename as string, byval datafieldname 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 ex as exception
throw new notsupportedexception(ex.message)
finally
sqlconnection.close()
end try
return rowsaffected
end function
function updateintbystr(byval tablename as string, byval datafieldname 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 ex as exception
throw new notsupportedexception(ex.message)
finally
sqlconnection.close()
end try
return rowsaffected
end function
function updateintbysql(byval tablename as string, byval datafieldname 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 ex as 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 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 ex as exception
throw new notsupportedexception(ex.message)
finally
sqlconnection.close()
end try
return rowsaffected
end function
function updatebysql(byval tablename as string, byval setsql 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 ex as exception
throw new notsupportedexception(ex.message)
finally
sqlconnection.close()
end try
return rowsaffected
end function
function getdatatablebyformat(byval vrecordnumber as integer, byval itemsql as string, byval tablename as string, byval parasql as string) as system.data.datatable
dim recordnumber as string
if vrecordnumber = 0 then
recordnumber = ""
else
recordnumber = "top " & vrecordnumber
end if
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 ex as exception
throw new notsupportedexception(ex.message)
finally
sqlconnection.close()
end try
end function
function getdatatablebysql(byval supersql as string) as system.data.datatable
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 ex as 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.executescalar)
catch ex as 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.executescalar)
catch ex as exception
throw new notsupportedexception(ex.message)
finally
sqlconnection.close()
end try
return rowsaffected
end function
function getfieldcount(byval tablename as string, 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.executescalar
catch ex as exception
throw new notsupportedexception(ex.message)
finally
sqlconnection.close()
end try
return rowsaffected
end function
function getfieldsumbyint(byval tablename as string, 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.executescalar
catch ex as exception
throw new notsupportedexception(ex.message)
finally
sqlconnection.close()
end try
return rowsaffected
end function
function getfieldsumbydec(byval tablename as string, byval fieldname as string, byval parasql 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.executescalar
catch ex as exception
throw new notsupportedexception(ex.message)
finally
sqlconnection.close()
end try
return rowsaffected
end function
function chgfieldbyleftword(byval tablename as string, byval fieldname as string, byval paraword as string, byval newword as string) as integer
try
return updatebysql(tablename, fieldname & "='" & newword & "'+right(" & fieldname & ",len(" & fieldname & ")-" & len(paraword) & ")", "len(" & fieldname & ")>=" & len(paraword) & " and left(" & fieldname & "," & len(paraword) & ")='" & paraword & "'")
catch ex as exception
throw new notsupportedexception(ex.message)
end try
end function
'數據庫連接串
private function getconn() as string
return "server=localhost;database=pubs;uid=sa;pwd="
end function
end module
