|
I don't seem to be getting this right in VB. I get an error in casting the result as an Integer, in which the result may not exist.
ProductInfo_Inventory should be a join to get a count
Dim cStatus As Integer = context.ProductInfo_Inventory.Where(Function(m) m.partNumber = p_PartNumber).Select(Function(m) m.currentStatus).DefaultIfEmpty()
This is the whole thing. Not every item has a record in inventory for it's optional. So I'm not really sure how to properly handle this in VB
My new program is much better and in c#, so I seem to be doing better in that now.
pValue = context.ProductInfo.Where(Function(m) m.PartNumber = p_PartNumber).Count()
Dim cStatus = context.ProductInfo_Inventory.Where(Function(m) m.partNumber = p_PartNumber).Select(Function(m) m.currentStatus).DefaultIfEmpty()
If (cStatus = 4) Then
dV = False
Else
dV = If(pValue > 0, True, False)
End If
21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.
modified 5-Jan-17 14:27pm.
|
|
|
|
|
The first question is, what is the type of currentStatus?. If it's non-numeric or beyond the limits of integer, the cast will fail.
Another thing is that if the current status is nullable (or reference type) then the default would be null which cannot be cast to integer.
The most accurate information is in the error message, but you didn't post that.
|
|
|
|
|
I had a customer walk in while posting and I hit post without much details, sorry about that; my bad.
This just determines if an item really exist, in case someone messed with the query string and put bad chars in there, or if an item was marked for deletion.
I went with this; cStatus is an Integer.
pValue = context.ProductInfo.Where(Function(m) m.PartNumber = p_PartNumber).Count()
If (context.ProductInfo_Inventory.Any(Function(m) m.partNumber = p_PartNumber)) Then
cStatus = context.ProductInfo_Inventory.Where(Function(m) m.partNumber = p_PartNumber).Select(Function(m) m.currentStatus).FirstOrDefault()
End If
If (cStatus = 4) Then
dV = False
Else
dV = If(pValue < 4, True, False)
End If
21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.
|
|
|
|
|
No problem! So everything works now? Just notice that FirstOrDefault may also return null which wuold cause an exception since cStatus is integer so depending on the situation you may want to use integer? and check if a value is actually returned.
|
|
|
|
|
I didn't think of that, I'll add it now
21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.
|
|
|
|
|
Hi All,
I have a File System Watcher, which is monitoring file drops and renaming etc, which keeps the file information into a table, I have another application which executes the file related SSIS packages, and these two applications are Asynchronous. The problem is the Excel Connection Manager in the SSIS Package, when its reading the files, its creating another temporary file which is unnecessarily creating a record in the table. Can anybody please help me in how to overcome this scenario, I did a little bit by keeping a file exists check in the SSIS package that if the file exists or file already ran I am not going to run the package again on the File but still a record entry would be there on the monitors table which comes because of the Excel connection manager opening and reading the File.
Any help would be very helpful, a code snippet, a link or suggestion anything helps, thanks in advance 
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
|
I did but the problem is the temp files are going to be created with $ prefix but the extension is same and another thing is that the temp files is going to be renamed back to the original file I think that's the reason why, it was hard for me to catch it and fix it. I tried it in hard way but only got 90% success, the way is to not insert any record into the monitoring table if same file name and location file comes within 15 minutes of previous drop then don't insert it into the monitoring table, but still for some SSIS package may take more that 15 minutes to read, then still those files are showing up in the monitoring table.
If there is any easy and proven approach that would be very helpful, thanks for the help buddy
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
hi,
I am having doubt in logical schema of database.
A logical schema
a)is the entire database
b)is a standard way of organizing information into a accessible part
c)describe how data is actually stored on disk
d)is design-centric database structure built to meet business requirements
My answer is "a" that is the entire database.Is it correct?
Please help............
|
|
|
|
|
|
Answer: none of the above, although d is maybe closest.
|
|
|
|
|
Thanks for confirming that 
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Actually looks more like the output of one of the online buzzword generators. 
|
|
|
|
|
I have exhausted my resources. My application works fine with Access 2007. I have a windows form built with VS 2010 using a MySql database. Following is what I thought was the proper coding for MySql. Even if I run a simple query with a WHERE colmx = "string" no rows are returned. Only the SELECT clause is working. Can someone point me to a resource for a solution?
MySql 5.7.17 MySql database on remote server. (and local)
Windows C# Form VS 2010 .NET 3.5
private void txtSearchName_TextChanged(object sender, EventArgs e)
{
string srch = "Strain"; //txtSearchName.Text + "%";
this.contactsTableAdapter.FillByName(this.strain_family_databaseDataSet.contacts, srch);
}
VS 2010 DataSet Query
SELECT ID, Company, Last Name, First Name, 'etc' FROM contacts
WHERE 'Last Name' LIKE @Param1 OR 'First Name' LIKE @Param1
ORDER BY 'Last Name'
|
|
|
|
|
Errors = field names with spaces - this will drive you nuts as you now need to bracket the field names with []
try
WHERE [Last Name] like @param1 etc..
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Brackets is only on SQL Server, on MySQL you need to use the backtick `
Both supports the use of double quote " as that is the ANSI standard. You may need to SET sql_mode = 'ANSI_QUOTES' though.
|
|
|
|
|
Ah thanks, have not got used to the variations in MySQL yet and I would never find that wrinkle - spaces in field names are NEVER acceptable.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
The differences in the SQL dialects of MS Access and MySQL can be terrible. It is best to avoid field / table names with blanks. If you insist on them, you must escape the name correctly, Access uses [name with blank] while MySQL `name with blank`.
Next, a comparison with LIKE expects a wild card. Strain does not contain a wild card, use Strain% instead - and make sure that you have data starting with that in one of the columns...
|
|
|
|
|
Bernhard,
Thank you. I don't know how I missed the backtick. I kept looking for something serious. I discovered it about an hour after posting. It became obvious when I executed queries with Workbench. That is a great tool! Well, I corrected that. As to the wildcard, I had that done properly in the query I was interested in, appending the % to the string before passing to the query. The web is filled with advice on wildcards, most is incorrect. I always get answers quickly here.
|
|
|
|
|
The same project again. Yes I know it's old, and I'm not the author of it, but it's quick cash.
I have this dbf file that seems to be locked or something. In some other dbf files, I can delete a record. But in this one, even using another program called DBF Manager, I can't delete rows.
So I tried 2 methods, using 2 different drivers.
The dbf file has about say 20 rows for invoice number 28794, and in the 2nd example returns 1 in ExecuteNonQuery
The first method I tried was using Jet, which is what I used through out the program.
But I get a 0 back from ExecuteNonQuery
Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & m_path
Const queryString1 As String = _
" DELETE FROM ARTRS01H.dbf h " & _
" WHERE h.FINVNO = @FINVNO "
Const queryString2 As String = _
" DELETE FROM ARTRS01.dbf v " & _
" WHERE v.FINVNO = @FINVNO "
Using connection As New System.Data.OleDb.OleDbConnection(connString)
Using command As New System.Data.OleDb.OleDbCommand(queryString1, connection)
Dim paramFINVNO As System.Data.OleDb.OleDbParameter
paramFINVNO = New System.Data.OleDb.OleDbParameter("@FINVNO", OleDbType.VarChar)
paramFINVNO.Value = p.Trim
command.Parameters.Add(paramFINVNO)
Try
connection.Open()
Dim x As Integer = command.ExecuteNonQuery()
command.CommandText = queryString2
x += command.ExecuteNonQuery()
pValue = If(x > 0, True, False)
Catch sqlEx As SqlClient.SqlException
pValue = False
Catch exdb As OleDb.OleDbException
pValue = False
Catch ex As Exception
pValue = False
Finally
connection.Close()
End Try
The 2nd method I tried was using the Visual Fox Pro, in which I get a 1 back from ExecuteNonQuery
Dim connString As String = "Provider=VFPOLEDB; Data Source=" & m_path & ";"
Dim queryString1 As String = _
" SET EXCLUSIVE ON" & _
" DELETE FROM ARTRS01H.dbf " & _
" WHERE FINVNO = '" & p & "' " & _
" PACK "
Using connection As New System.Data.OleDb.OleDbConnection(connString)
Using command As New System.Data.OleDb.OleDbCommand(queryString1, connection)
Try
connection.Open()
command.CommandType = CommandType.StoredProcedure
command.CommandText = "ExecScript"
command.Parameters.Add("script1", OleDbType.Char).Value = queryString1
Dim x As Integer = command.ExecuteNonQuery()
pValue = If(x > 0, True, False)
Catch sqlEx As SqlClient.SqlException
pValue = False
Catch exdb As OleDb.OleDbException
pValue = False
Catch ex As Exception
pValue = False
Finally
connection.Close()
End Try
I'd thought and see if any folks out there may flash back 20 years on this.
21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.
|
|
|
|
|
This really is digging out at the very back of my memory, but I vaguely remember a similar problem. I think there was another file created - either same name as database with a different suffix, or logon id, or random numbers+letters - I don't recall which and I've seen all three of these over the years.
Any how - this file contained details of locks on the dbf and didn't always get cleared down properly - leaving things locked.
If you're using WIN7 upwards then you might be hitting issues with virtual storage depending on where the DBF file is stored - came across something similar a couple of years ago.
Good luck!
|
|
|
|
|
Thats a start for me at least, Thanks!
Strange, I deleted some funky looking row data at the bottom of the file and now I can delete records using DBF Manager.
Guess I'll give my vb code another try again now.
21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.
|
|
|
|
|
When I use DBF Manager and do a search by invoice number, it comes back search string not found.
Think it's because it a partial string or something?
This technology is before I got into programming, and I never really used back in 1997.
Dim connString As String = "Provider=VFPOLEDB; Data Source=" & m_path & "; DELETED = FALSE;"
Using connection As New System.Data.OleDb.OleDbConnection(connString)
Dim c1 As New System.Data.OleDb.OleDbCommand("SET EXCLUSIVE ON", connection)
Dim c2 As New System.Data.OleDb.OleDbCommand("SET DELETED ON", connection)
Dim c3 As New System.Data.OleDb.OleDbCommand("DELETE FROM ARTRS01H WHERE FCUSTNO = ? AND FINVNO = ?", connection)
Dim c4 As New System.Data.OleDb.OleDbCommand("PACK", connection)
Dim paramFCUSTNO As System.Data.OleDb.OleDbParameter
paramFCUSTNO = New System.Data.OleDb.OleDbParameter("@FCUSTNO", OleDbType.VarChar)
paramFCUSTNO.Value = pCustNo
c3.Parameters.Add(paramFCUSTNO)
Dim paramFINVNO As System.Data.OleDb.OleDbParameter
paramFINVNO = New System.Data.OleDb.OleDbParameter("@FINVNO", OleDbType.VarChar)
paramFINVNO.Value = pInvNo
c3.Parameters.Add(paramFINVNO)
Try
connection.Open()
c1.ExecuteNonQuery()
c2.ExecuteNonQuery()
Dim x As Integer = c3.ExecuteNonQuery()
c4.ExecuteNonQuery()
pValue = If(x > 0, True, False)
Catch sqlEx As SqlClient.SqlException
pValue = False
Catch exdb As OleDb.OleDbException
pValue = False
Catch ex As Exception
pValue = False
Finally
connection.Close()
End Try
End Using
21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.
|
|
|
|
|
Maybe I just fixed the index file and now it works. Tired of working on this, not fun!
Dim dwXCode As Integer = registry_shared.read_HKCU_dataPath(m_path)
Dim connString As String = "Provider=VFPOLEDB; Data Source=" & m_path & "; DELETED = FALSE;"
Using connection As New System.Data.OleDb.OleDbConnection(connString)
Dim c1 As New System.Data.OleDb.OleDbCommand("SET EXCLUSIVE ON", connection)
Dim c2 As New System.Data.OleDb.OleDbCommand("SET DELETED ON", connection)
Dim c3 As New System.Data.OleDb.OleDbCommand("DELETE FROM ARTRS01H WHERE FCUSTNO = ? AND FINVNO = ?", connection)
Dim c4 As New System.Data.OleDb.OleDbCommand("DELETE FROM ARTRS01 WHERE FCUSTNO = ? AND FINVNO = ?", connection)
Dim c5 As New System.Data.OleDb.OleDbCommand("PACK", connection)
c3.Parameters.Add("@FCUSTNO", OleDbType.VarChar).Value = pCustNo
c3.Parameters.Add("@FINVNO", OleDbType.VarChar).Value = pInvNo
c4.Parameters.Add("@FCUSTNO", OleDbType.VarChar).Value = pCustNo
c4.Parameters.Add("@FINVNO", OleDbType.VarChar).Value = pInvNo
Try
connection.Open()
c1.ExecuteNonQuery()
c2.ExecuteNonQuery()
Dim x As Integer = c3.ExecuteNonQuery()
x += c4.ExecuteNonQuery()
c5.ExecuteNonQuery()
pValue.Result = If(x > 0, True, False)
pValue.Message = x & " record(s) were deleted successfully"
Catch sqlEx As SqlClient.SqlException
pValue.Result = False
pValue.Message = sqlEx.Message.ToString
Catch exdb As OleDb.OleDbException
pValue.Result = False
pValue.Message = exdb.Message.ToString
Catch ex As Exception
pValue.Result = False
pValue.Message = ex.Message.ToString
Finally
connection.Close()
End Try
21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.
|
|
|
|
|
This is the first I'm trying to update a row in a FoxPro DBF file.
My code runs without error, but the row just won't update. I get a 0 back from ExecuteNonQuery
I tried hard coding the customer number but same thing.
Tried just updating 1 column FCOMPANY, same thing.
I must be missing something here, like perhaps a cursor or something.
The SELECT line was a feeble attempt to try something.
Dim m_path As String = Nothing
Dim dwXCode As Integer = registry_shared.read_HKCU_dataPath(m_path)
Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & m_path & "; Extended Properties=dBASE IV"
Const queryString As String = _
"SELECT * FROM ARCUS01.dbf WHERE FCUSTNO = @FCUSTNO " & _
" UPDATE ARCUS01.dbf " & _
" SET " & _
" FCOMPANY = 'Wiped Customer' " & _
", FADDR1 = @Empty " & _
", FADDR2 = @Empty " & _
", FCITY = @Empty " & _
", FSTATE = @Empty " & _
", FZIP = @Empty " & _
", FSADDR1 = @Empty " & _
", FSADDR2 = @Empty " & _
", FSCITY = @Empty " & _
", FSSTATE = @Empty " & _
", FSZIP = @Empty " & _
", FPHONE1 = @Empty " & _
", FFAX = @Empty " & _
", FFNAME = @Empty " & _
", FLNAME = @Empty " & _
", FTITLE = @Empty " & _
", FSALESPN = @Empty " & _
", FTERR = @Empty " & _
", FRESALENO = @Empty " & _
", FLPDATE = @Empty " & _
", FLPAMT = 0 " & _
", FATDSAMT = 0 " & _
", FYTDSAMT = 0 " & _
" WHERE FCUSTNO = @FCUSTNO "
Using connection As New System.Data.OleDb.OleDbConnection(connString)
Using command As New System.Data.OleDb.OleDbCommand(queryString, connection)
Dim paramWipe As System.Data.OleDb.OleDbParameter
paramWipe = New System.Data.OleDb.OleDbParameter("@Empty", OleDbType.VarChar)
paramWipe.Value = String.Empty
command.Parameters.Add(paramWipe)
Dim paramZero As System.Data.OleDb.OleDbParameter
paramZero = New System.Data.OleDb.OleDbParameter("@Zero", OleDbType.Decimal)
paramZero.Value = 0.0
command.Parameters.Add(paramZero)
Dim paramFCUSTNO As System.Data.OleDb.OleDbParameter
paramFCUSTNO = New System.Data.OleDb.OleDbParameter("@FCUSTNO", OleDbType.VarChar)
paramFCUSTNO.Value = p
command.Parameters.Add(paramFCUSTNO)
Try
connection.Open()
Dim x As Integer = command.ExecuteNonQuery()
pValue = If(x > 0, True, False)
Catch sqlEx As SqlClient.SqlException
pValue = False
Catch exdb As OleDb.OleDbException
pValue = False
Catch ex As Exception
pValue = False
Finally
connection.Close()
End Try
End Using
End Using
21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
