|
You can lead a horse to water ... 
|
|
|
|
|
i need to calculate timediff between time in and time out then save it to the field name totalhrs. can you help me?
|
|
|
|
|
Go back and read my previous message, where I (try to) explain that you cannot calculate differences using a string and a float. And you should not even be using those types.
|
|
|
|
|
Imports MySql.Data.MySqlClient
Public Class frmCreditSubjs
Public conn As New MySqlConnection
Public cmd As New MySqlCommand
Public cmd1 As New MySqlCommand
Public dadapter As New MySqlDataAdapter
Public datardr As MySqlDataReader
Public strSql As String
Public datardr1 As MySqlDataReader
Public strSql1 As String
Public subjectcode1 As String
Public units1 As String
Public preReq1 As String
Public semester1 As String
Public year1 As String
Public count As Integer
Private Sub InsertLoad()
conn.ConnectionString = "server=localhost;userid=root;password=;database=aes"
Try
conn.Open()
strSql = "select subjectcode, units, prereq, semester, year from tbl_subjects"
cmd = New MySqlCommand(strSql, conn)
datardr = cmd.ExecuteReader()
While datardr.Read()
count += 1
subjectcode1 = datardr("subjectcode")
units1 = datardr("units")
preReq1 = datardr("prereq")
semester1 = datardr("semester")
year1 = datardr("year")
Try
strSql = "INSERT INTO tbl_studProspectus values ('', '" & textSNumber.Text & "','" & subjectcode1 & "', '" & units1 & "', '" & preReq1 & "', '" & semester1 & "', '" & year1 & "','')"
cmd = New MySqlCommand(strSql, conn)
datardr = cmd.ExecuteReader()
Catch ex As MySqlException
MessageBox.Show(ex.Message)
Finally
conn.Dispose()
End Try
End While
Catch ex As MySqlException
MessageBox.Show(ex.Message)
Finally
conn.Dispose()
End Try
End Sub
Private Sub frmCreditSubjs_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
InsertLoad()
End Sub
End Class
|
|
|
|
|
Since the connection is being occupied by an active DataReader, it can't be used to do the INSERT queries. You have to open a separate connection to the SQL database to execute the INSERTs.
Now, since you're executing the INSERT in a try/catch block and it tried to give you an exception telling you that you're trying to reuse a connection that is already in use, you're catch block closed the connection, blocking the DataReader from executing any more.
Bottom line: You need TWO SqlConnections to the database. One for the DataReader and one for the INSERT commands.
|
|
|
|
|
sir can you give me some suggestion about it, thanks in advance.
|
|
|
|
|
sir it can be added but cannot loop .How can i make it ?
|
|
|
|
|
You already know how to do it. You already have the code to create a second connection object. You have supplied you'r own example! All you need to do is create a second connection object, surrounding your inner INSERT SqlCommand.
|
|
|
|
|
yes i know it, but i dont know how to create a second connection. will you please teach me sir?
|
|
|
|
|
Exactly like the first one, just assigning it to a separate variable.
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
ok, thanks a lot guys. Godbless
|
|
|
|
|
Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.
Also, don't store local variables as class-level fields. Declare them where you use them. And don't forget to wrap IDisposable objects in a Using block.
In this instance, there's no need to loop through the results of the query just to insert them into another table. Just use an INSERT .. SELECT statement[^]:
Dim query As String = "INSERT INTO tbl_studProspectus SELECT '', @StudentNumber, subjectcode, units, prereq, semester, year FROM tbl_subjects"
Using conn As New MySqlConnection("server=localhost;userid=root;password=;database=aes")
Using cmd As New MySqlCommand(query, conn)
cmd.Parameters.AddWithValue("@StudentNumber", textSNumber.Text)
conn.Open()
cmd.ExecuteNonQuery()
End Using
End Using
Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
i am, but it will always give me an error in my query near select. but i will try first this one, thanks
|
|
|
|
|
does all the data in tbl_subject will insert to tbl_prospectus?
|
|
|
|
|
i got it correctly with your code, thanks!
|
|
|
|
|
Good Morning
I want to loop through all the available data in a column. Within the loop I am using an if statement to check for certain conditions. I then want to calculate the weightedaverage for the corresponding columns for all the rows that meets the if statement conditions.
Please see code below:
Select Case channel
'Channel 1 Threshold Check'
Case Is = 1
If ((Cells(i, 8).Value > Range("N10")) Or (Cells(i, 8).Value < Range("N10") And Cells(i, 6) > Range("N12")) Or (Cells(i, 8).Value > Range("N11") And Cells(i, 7) > Range("N13"))) Then
Range("N21") = conc1
Cells(i, 8).Font.Color = vbRed 'Colour the cells that meets the conditions'
Cells(i, 6).Font.Color = vbRed 'Colour the cells that meets the conditions'
Cells(i, 7).Font.Color = vbRed 'Colour the cells that meets the conditions'
ElseIf ((Cells(i, 8).Value < Range("N10")) Or (Cells(i, 8).Value > Range("N10") And Cells(i, 6) < Range("N12")) Or (Cells(i, 8).Value < Range("N11") And Cells(i, 7) < Range("N13"))) Then
Disc1 = Disc1 + 1
Range("O21") = Disc1
End If
|
|
|
|
|
Ehm, what's the problem?
I would say that doing this in VSTO instead of a macro would be soooo much easier, though.
|
|
|
|
|
Hi Kenneth
Unfortunately I am new to the whole programming thing. I am busy developing something in VSTO c#, where I am connecting to SQL pulling data into a dataset and then I want to loop through it. Unfortunately I need to get this done more urgently. I am using excel for most of my excel, but the sheets are massive and processing times are slow and seldom I run out of virtual memory.
Do you know how to calculate the weighted average within a For Loop with If statement?
|
|
|
|
|
weighted average? weighted against what exactly?
Average is easy, just add all values up, and divide by the number of values.
|
|
|
|
|
Is socket fast? Process we have is just simple data lock.
A main database which contains 10,000 - 20,000 records (created daily if MDB used) that will access the server.
There will be 100+ clients that will request what data they will access/process then send the keyed data to server and the server will save that record.
Socket is a very nice way to do this but I don't know if it can handle massive request. Server is only 8G RAM with I3 processor.
- Be who you are and say what you feel because those who mind don't matter and those who matter don't mind.
- Most of the problem in your life are due to two reasons: you act without thinking, or think without acting.
|
|
|
|
|
hansoctantan wrote: Is socket fast?
How fast is your network?
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Sockets, with properly written code of course, is the fastest you're going to get.
The real bottleneck is going to be your database server hardware, database structure, quality of your queries and whatever code you write to send data back to the clients.
|
|
|
|
|
Using newtonsoft json to read a json file.
I download a sample json file with zip codes: http://jsonstudio.com/resources/
using this code:
Dim json As String = File.ReadAllText("C:\Users\Presentation\Desktop\zips.json")
Dim o As JObject = JObject.Parse(json)
But it fails with:
An unhandled exception of type 'Newtonsoft.Json.JsonReaderException' occurred in Newtonsoft.Json.dll
Additional information: Additional text encountered after finished reading JSON content: {. Path '', line 2, position 0.
Why would this default, and I assume correct data file, fail already here?
Can anyone help me? Thx
|
|
|
|
|
It doesn't work because you're not deserializing a complete JSON document. You're actually trying to is deserialize a list of JSON fragments. You have to process this one line at a time, not all at once.
Given the identifier names in the JSON file are very short, you'd probably be better off creating a strongly typed class so you can map those short names to something more friendly in your code.
The code snippets are in C#, but are EASILY converted to VB.NET. I'm just too lazy to do it for you.
private IList<ZipCodeData> ProcessFile(string filepath)
{
using (var sr = File.OpenText(filepath))
{
List<ZipCodeData> objects = new List<ZipCodeData>();
var lineContent = sr.ReadLine();
while (!string.IsNullOrEmpty(lineContent))
{
var obj = JsonConvert.DeserializeObject<ZipCodeData>(lineContent);
objects.Add(obj);
lineContent = sr.ReadLine();
}
return objects;
}
}
The ZipCodeData class looks like this:
public class ZipCodeData
{
[JsonProperty("city")]
public string City { get; set; }
[JsonProperty("loc")]
public double[] Location { get; set; }
[JsonProperty("pop")]
public int Population { get; set; }
[JsonProperty("state")]
public string State { get; set; }
[JsonProperty("_id")]
public string Zip { get; set; }
}
|
|
|
|
|
OK thanks. That explains. I was living with the impression that a json file could be read at once, because I saw that json conversion to dataset or datatable.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
